Problem in exporting data array to xlsx file using PhpSpreadsheet

Question

Hello everyone in my project, I am trying to export data from database to an xlsx file but I am not getting correct data.I have attached image of data.

I am using the following code.

    use PhpOffice\PhpSpreadsheet\Spreadsheet;
    use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
    use PhpOffice\PhpSpreadsheet\Writer\Xls;

    $conn = new mysqli(DB_HOST, DB_USER, DB_PASS, DB_NAME);

    $exportArray = array();
    $query = mysqli_query($conn, "select * from table");

    if(mysqli_num_rows($query) > 0){

            while ($row = mysqli_fetch_assoc($query)) {

                    $exportArray[$exp]['id'] = $row['id'];
                    $exportArray[$exp]['name'] = $row['name'];
                    $exportArray[$exp]['address'] = $row['address'];

                    $exp++;

            }

    }
    $array = array();

    $spreadsheet = new Spreadsheet();
    $sheet = $spreadsheet->getActiveSheet();

    $sheet->setCellValue('A1', 'id');
    $sheet->setCellValue('B1', 'name');
    $sheet->setCellValue('C1', 'address');

    $rowCount = 2;
    foreach ($exportArray as $value) {

        $sheet->setCellValue('A' . $rowCount, $value['id']);
        $sheet->setCellValue('B' . $rowCount, $value['name']);
        $sheet->setCellValue('C' . $rowCount, $value['address']);
        $rowCount++;
    }

    $fileName = 'test123.xls';
    $writer = new \PhpOffice\PhpSpreadsheet\Writer\Xlsx($spreadsheet);
    header('Content-Type: application/vnd.ms-excel');
    header('Content-Disposition: attachment;filename="'. $fileName .'.xlsx"'); 
    header('Cache-Control: max-age=0');
    $writer->save('php://output');
    exit();

When I see the sheet data using below code

$sheetData = $sheet->toArray(null, true, true, true);

print_r($sheetData);

I am getting the right output. Everything looks fine but I don't understand, why am I getting data in wrong format in sheet?

Content-Type is wrong for XLSX - you specified one for XLS, and your filename turns out to be test123.xls.xlsx. How about saving the file first, then renaming it and then doubleclicking it? The first two PK show that is indeed a PKZIP file (XLSX), but thanks to the content type hint Excel handles it as if it was... CSV? — AmigoJack
– AmigoJack, Commented Jul 19, 2019 at 8:25
@lubosdz, I think this is not related to db. because when I comment foreach loop and use static value, I am getting the same result. By the way It is latin1_swedish_ci — Yogendra
– Yogendra, Commented Jul 19, 2019 at 10:11
@AmigoJack, that was by mistake. When I used $fileName = 'test123'; I am getting the same data in file. — Yogendra
– Yogendra, Commented Jul 19, 2019 at 10:12
The content type you should be using is application/vnd.openxmlformats-officedocument.spreadsheetml.sheet — Bram Verstraten
– Bram Verstraten, Commented Jul 19, 2019 at 10:54

Ganesan San · Accepted Answer · 2019-07-19 10:52:01Z

Try This:

<?php
require 'vendor/autoload.php';

use PhpOffice\PhpSpreadsheet\Helper\Sample;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

$conn = new mysqli("localhost","root","root","test");
$sql = "SELECT * FROM customers";
$result = $conn->query($sql);
$write_array = array();
$fileName = "excel.xlsx";
$write_array[] = array("id","name","address");
if($result->num_rows > 0) 
{
    while($row = $result->fetch_assoc()) 
    {
        $write_array[] = array($row["id"],$row["name"],$row["address"]);
    }
}
$conn->close();
$spreadsheet = new Spreadsheet();
$spreadsheet->setActiveSheetIndex(0);
$spreadsheet->getActiveSheet()->fromArray($write_array,NULL,'A1');
$spreadsheet->getActiveSheet()->setTitle("My Excel");

header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="'.$fileName.'"');
header('Cache-Control: max-age=0');
header('Cache-Control: max-age=1');
header('Expires: Mon, 26 Jul 1997 05:00:00 GMT');
header('Last-Modified: ' . gmdate('D, d M Y H:i:s') . ' GMT');
header('Cache-Control: cache, must-revalidate');
header('Pragma: public');
$writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
$writer->save('php://output');

Priyanka Giri · Accepted Answer · 2019-07-19 10:58:52Z

Add this use PhpOffice\PhpSpreadsheet\IOFactory;

Now use the following code to export data in Xlsx format

header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="test.xlsx"');
header('Cache-Control: max-age=0');
header('Cache-Control: max-age=1');
header('Cache-Control: cache, must-revalidate');
header('Pragma: public');

$writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
$writer->save('php://output');

Alejandro De Castro · Accepted Answer · 2022-03-24 11:50:57Z

0

in my case none of the above solutions worked.
I finally managed to solve it by adding this line: ob_end_clean();
just before: $writer->save('php://output');

ob_end_clean();
$writer->save('php://output');

answered Mar 24, 2022 at 11:50

Alejandro De Castro

2,3871 gold badge21 silver badges27 bronze badges

Collectives™ on Stack Overflow

Problem in exporting data array to xlsx file using PhpSpreadsheet

3 Answers 3

Comments

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Related