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?
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');