Generate Excel from PHP [duplicate]

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, "[email protected]");
// third row 
xlsWriteNumber(2, 0, 350);
xlsWriteLabel(2, 1, "Mark");
xlsWriteLabel(2, 2, "[email protected]");
// 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
– sunlight76, Commented Aug 1, 2017 at 11:16

The Punisher · Accepted Answer · 2020-06-25 13:06:19Z

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, '[email protected]');

// third row 
$oExcel->getActiveSheet()->setCellValueByColumnAndRow(0, 3, 350);
$oExcel->getActiveSheet()->setCellValueByColumnAndRow(1, 3, 'Mark');
$oExcel->getActiveSheet()->setCellValueByColumnAndRow(2, 3, '[email protected]');

$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, 2020 at 13:06

The Punisher

5721 gold badge6 silver badges14 bronze badges

answered Aug 1, 2017 at 10:31

Ivo P

1,7101 gold badge9 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Matijs Over a year ago

Yep, that is the solution I would use as well.

sunlight76 Over a year ago

I was looking for a solution to modify current code, not to change method, thanks

Ivo P Over a year ago

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)

Funk Forty Niner Over a year ago

@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.

Ivo P Over a year ago

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.

|

Collectives™ on Stack Overflow

Generate Excel from PHP [duplicate]

1 Answer 1

6 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Linked

Related