Generate Excel from PHP

Question

I use this code to generate an Excel file from PHP. It works correctly but the file generated is an obsolete Excel version 2.0 and latest Excel clients open it in read-only.

Could you suggest a solution?

I'm looking for a solution to adjust this code, not using other methods like PHPExcel

function xlsBOF() {
    echo pack("ssssss", 0x809, 0x8, 0x0, 0x10, 0x0, 0x0);
}
function xlsEOF() {
    echo pack("ss", 0x0A, 0x00);
}
function xlsWriteNumber($Row, $Col, $Value) {
    echo pack("sssss", 0x203, 14, $Row, $Col, 0x0);
    echo pack("d", $Value);
}
function xlsWriteLabel($Row, $Col, $Value) {
    $L = strlen($Value);
    echo pack("ssssss", 0x204, 8 + $L, $Row, $Col, 0x0, $L);
    echo $Value;
} 
// prepare headers information
header("Content-Type: application/force-download");
header("Content-Type: application/octet-stream");
header("Content-Type: application/download");
header("Content-Disposition: attachment; filename=\"export_".date("Y-m-d").".xls\"");
header("Content-Transfer-Encoding: binary");
header("Pragma: no-cache");
header("Expires: 0");
// start exporting
xlsBOF();
// first row 
xlsWriteLabel(0, 0, "id");
xlsWriteLabel(0, 1, "name");
xlsWriteLabel(0, 2, "email");
// second row 
xlsWriteNumber(1, 0, 230);
xlsWriteLabel(1, 1, "John");
xlsWriteLabel(1, 2, "john@yahoo.com");
// third row 
xlsWriteNumber(2, 0, 350);
xlsWriteLabel(2, 1, "Mark");
xlsWriteLabel(2, 2, "mark@yahoo.com");
// end exporting
xlsEOF();

I'm looking for a solution to adjust this code, not using other methods like PHPExcel. So this is not a duplicate. — sunlight76, Aug 01 '17 at 11:16

score 6 · Accepted Answer · edited Jun 25 '20 at 13:06

6

I use the class PHPExcel ( https://github.com/PHPOffice/PHPExcel ) to create Excelsheets of various versions.

works very well for me.

edit

Although you meanwhile changed the question, not wanting to use phpexcel, I will put an example below how the code would look like, if you would use PHPExcel. Only add the class files from the link above to get it to work:

<?php

$oExcel = new PHPExcel();

// first row
$oExcel->getActiveSheet()->setCellValueByColumnAndRow(0, 1, 'id');
$oExcel->getActiveSheet()->setCellValueByColumnAndRow(1, 1, 'name');
$oExcel->getActiveSheet()->setCellValueByColumnAndRow(2, 1, 'email');

// second row 
$oExcel->getActiveSheet()->setCellValueByColumnAndRow(0, 2, 230);
$oExcel->getActiveSheet()->setCellValueByColumnAndRow(1, 2, 'John');
$oExcel->getActiveSheet()->setCellValueByColumnAndRow(2, 2, 'john@yahoo.com');

// third row 
$oExcel->getActiveSheet()->setCellValueByColumnAndRow(0, 3, 350);
$oExcel->getActiveSheet()->setCellValueByColumnAndRow(1, 3, 'Mark');
$oExcel->getActiveSheet()->setCellValueByColumnAndRow(2, 3, 'mark@yahoo.com');

$objWriter = PHPExcel_IOFactory::createWriter($oExcel, 'Excel5');

header('Content-type: application/vnd.ms-excel');
header('Content-Disposition: attachment; filename="export_'. date('Y-m-d').'.xls"');
header("Pragma: no-cache");
header("Expires: 0");

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

edited Jun 25 '20 at 13:06

Sherlock Bourne

490
1
5
10

answered Aug 01 '17 at 10:31

Ivo P

1,722
1
7
18

Yep, that is the solution I would use as well. – Matijs Aug 01 '17 at 10:34
1

I was looking for a solution to modify current code, not to change method, thanks – sunlight76 Aug 01 '17 at 11:01
could be, but XLS format is much changed since Excel5. You can of course try to compensate for that in your code. But downloading the PHPExcel files and adapt one of the many examples should not cost more than 15 minutes (at the most) – Ivo P Aug 01 '17 at 11:26
@IvoP Your answer does not reflect what the question is about. The OP states in an edit: *"I'm looking for a solution to adjust this code, not using other methods like PHPExcel"* - Either you provide them with a solution or delete the answer. It's also a link only and [has been discussed on meta](https://meta.stackexchange.com/q/8231/231583). – Funk Forty Niner Aug 02 '17 at 11:29
I am not constantly monitoring all questions I gave an answer to, just to check if someone changed the question in a way to exclude my answer. – Ivo P Aug 02 '17 at 12:47
nevertheless, I will edit my answer to add some code – Ivo P Aug 02 '17 at 12:48

Generate Excel from PHP

1 Answers1