How to give path to the file?

Question

Hi I want to save the file in excel format in my local folder. For this I am creating an api which will be scheduled to call at every night and save the data in file every night.

My attempt is :

    class SaveExcel
{

    function SaveExcel()
    {
    }

    function saveData()
    {
        $file_result = "";
        $database = new Database(Constants::DBHOST, Constants::DBUSER, Constants::DBPASS, Constants::DBNAME);
        $dbConnection = $database->getDB();

        date_default_timezone_set('Asia/Kolkata');
        $date = date('m/d/Y h:i:s a', time());

        $DB_TBLName = "location"; //MySQL Table Name
        $filename = "LocationHistory(" . $date . ")";         //File Name

//create MySQL connection
        $stmt = $dbConnection->prepare("Select * from $DB_TBLName");
        $stmt->execute();

        $columnHeader = '';
        $columnHeader = "loc_id" . "\t" . "user_id" . "\t" . "address" . "\t" . "date_time" . "\t";

        $setData = '';

        while ($rec = $stmt->FETCH(PDO::FETCH_ASSOC)) {
            $rowData = '';
            foreach ($rec as $value) {
                $value = '"' . $value . '"' . "\t";
                $rowData .= $value;
            }
            $setData .= trim($rowData) . "\n";
        }

          //  file_put_contents('history/' . $filename, $setData);

       return ucwords($columnHeader)."\n".$setData."\n";

        }
    }
?>

By this I am getting the output as :

"Loc_id\tUser_id\tAddress\tDate_time\t\n\"118\"\t\"1\"\t\"19.166488899999997\"\t\"72.8510805\"\t\"16, Aarey Rd, Jay Prakash Nagar, Goregaon East, Mumbai, Maharashtra 400063, India\"\t\"2018-03-03 18:05:45\"\n\"119\"\t\"1\"\t\"19.165215999999997\"\t\"72.8509167\"\t\"MU Chambers, Jay Prakash Nagar, Goregaon West, Mumbai, Maharashtra 400063, India\"\t\"2018-03-03 18:22:14\"\n\"120\"\t\"1\"\t\"19.1651942\"\t\"72.85087469999999\"

now I want to save this data in excel format in local folder.

How can I do this?

Please help.. Thank you.

EDIT :

This code is working well.. Now I just want to give the path to the file. Now its getting saved under the project directory I want to get it saved in another directory. How Can I?

$objPHPExcel = new PHPExcel();
$objPHPExcel->setActiveSheetIndex(0);
$rowCount = 1;
$objPHPExcel->getActiveSheet()->SetCellValue('A'.$rowCount, 'Employee');
$objPHPExcel->getActiveSheet()->SetCellValue('B'.$rowCount, 'Address');
$objPHPExcel->getActiveSheet()->SetCellValue('C'.$rowCount, 'Date Time');

$rowCount++;

while($row = $stmt->FETCH(PDO::FETCH_ASSOC)){
    $objPHPExcel->getActiveSheet()->SetCellValue('A'.$rowCount, $user_name);
    $objPHPExcel->getActiveSheet()->SetCellValue('B'.$rowCount, $row['address']);
    $objPHPExcel->getActiveSheet()->SetCellValue('C'.$rowCount, $row['date_time']);
    $rowCount++;
}


$current_date = preg_replace('/\s+/', '', $date);
$objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel);
$name = '/path/to/folder/xyz.xlsx';
$objWriter->save($user_name.$current_date.'.xlsx');

return 'File Saved';

score 1 · Answer 1 · answered Mar 05 '18 at 10:42

1

What you are showing here is a TSV (tab-separated values) string, which is readable by Excel, but it's not a full Excel spreadsheet (e.g. you can't have colors etc.). This is good enough for many applications, and it can also be read by any app that reads TSVs (from Notepad++ to LibreOffice), which is an advantage.

However, if you want to create actual Excel files, it's much more difficult. There are libraries out there that do it, like this open-source one: https://github.com/PHPOffice/PhpSpreadsheet

answered Mar 05 '18 at 10:42

Ynhockey

3,845
5
33
51

Someone asked a similar question here, with one of the related libraries: https://stackoverflow.com/questions/40897666/ziparchiveclose-failure-to-create-temporary-file-permission-denied-nginx – Ynhockey Mar 05 '18 at 15:31
Hi, you should avoid altering question details fundamentally after you have some answers, it would really confuse anyone viewing this page later. If you have an unrelated question you can ask another one. In any case, I think what you want now is to modify the path in the `save` function, i.e. you need to put the entire path you want, not just the file name. – Ynhockey Mar 06 '18 at 08:17
I tried to add path like this : $objWriter->save("/history/$user_name."(".$date.")".'.xlsx'); but this dosent work @Ynhockey – Sid Mar 06 '18 at 08:32
What exactly doesn't work? /history looks like a strange place to store things, and it's likely your app doesn't have permissions to write to that folder anyway. Try using a relative path, or get your absolute path first and then append to it (this is how it's done in most large projects). If it doesn't work please ask a separate question. – Ynhockey Mar 06 '18 at 08:42

Tobok Sitanggang · Answer 2 · 2018-03-05T11:08:55.180

0

you need to add header of type excel on your code.

$filename = date('m/d/Y h:i:s a', time());

header("Content-Type: application/xls");    
header("Content-Disposition: attachment; filename=$filename.xls");

application/xls work on < excel 2007. so if get error on excel, you can change that to application/vnd.openxmlformats-officedocument.spreadsheetml.sheet

in other way, PHPExcel is a good plugin to customize your output excel

hope this help you.

edited Mar 05 '18 at 11:08

answered Mar 05 '18 at 11:03

Tobok Sitanggang

607
5
15

1

this statements, should be in your function `header("Content-Type: application/xls");` `header("Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");` – Tobok Sitanggang Mar 05 '18 at 15:29
why? becacuse you have to set type content that you want to download. and your code will not download the file. and why again? the browser didnt know what file will be downloaded. so need to add `header("Content-Disposition: attachment; filename=$filename.xlsx"); ` its like on my first comment, put it on your function before `$objWriter->save($filename.'.xlsx');` and i think, you should change this `$objWriter->save($filename.'.xlsx');` to ` $objWriter->save('php://output');`. sorry for my bad language. – Tobok Sitanggang Mar 05 '18 at 15:56
try changing the `$objWriter->save()` code to the following: `$objWriter->save(str_replace(__FILE__,'folder_path/'.$user_name.$current_date.'.xlsx',__FILE__));` You may change the `folder_path` depending on what you need – Tobok Sitanggang Mar 06 '18 at 09:23

How to give path to the file?

2 Answers2