How to Create Xls File in PHP

What is the best way to create XLS file in PHP

Depends if you want a CSV file or an XLS file. An XLS file can include formatting information for the cells, as well as row/column locking, protections and other features that are impossible in a CSV file. Also, keep in mind that Excel does not correctly support UTF-8 encoded content when opening CSV files.

If you want a formatted XLS file, then you need a library such as PhpSpreadsheet that can write a file with that formatting, or COM if you're server is running on Windows with Excel installed

PHP create xls from array

Please try :
As per official documentation, you first need to save the file with the object writer

Please let me know if this is what you wanted

<?php
date_default_timezone_set('America/Los_Angeles');

require_once('PHPExcel.php');

$sheet = array(
    array(
      'a1 data',
      'b1 data',
      'c1 data',
      'd1 data',
    )
  );

  $doc = new PHPExcel();
  $doc->setActiveSheetIndex(0);

  $doc->getActiveSheet()->fromArray($sheet, null, 'A1');
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="your_name.xls"');
header('Cache-Control: max-age=0');

  // Do your stuff here
  $writer = PHPExcel_IOFactory::createWriter($doc, 'Excel5');

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

Create xls file and save to a folder

HTTP headers are part of the HTTP requests and responses that are sent when communicating across a network. They hold information about the client, the server, the information being sent, and more.

If a script creates a file and sends it to a client, the script should create the appropriate headers to notify the client what the file type is (Content-type: application/vnd.ms-excel), whether to download it directly (Content-Disposition: attachment) or view it in a web browser, and so on.

If the script creates a file and saves it to the server (e.g. using file_put_contents($file_name, $data)), no headers need to be set as the file is not being sent across the network. If someone subsequently wants to download the file, e.g. using an FTP client or using another script, that script or FTP program on the server will set the appropriate headers when the transfer occurs.

Generate Excel from PHP

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

---

Related Topics