Which Is the Best Way to Generate Excel Output in PHP

Which is the best way to generate excel output in PHP?

There is some class that generates PHP Excel files (real excel files, not that .csv thing).
I use (DEPRICATED) See edit 2:

https://github.com/PHPOffice/PHPExcel

BUT: I have had a problem when trying to read these generated excel files with the java excel READER, so there might be bugs in this PHP script.

1. EDIT: Nice one: http://www.phpclasses.org/package/2037-PHP-Generate-spreadsheet-files-Excel-xls-XML-format.html

2. PhpSpreadsheet is the next version of PHPExcel. It breaks compatibility to dramatically improve the code base quality (namespaces, PSR compliance, use of latest PHP language features, etc.). https://github.com/PHPOffice/PhpSpreadsheet

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

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

How to export this table of data to Excel file from php

I suggest you to use javascript library, it work for me
Install excell javascript and FileSaver for saving file
Adding 2 library

<script src="https://cdnjs.cloudflare.com/ajax/libs/FileSaver.js/1.3.8/FileSaver.min.js"></script>

XLX Js

<script src="https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.14.1/xlsx.full.min.js"></script>

The script

<script>
  $('#download-btn').on('click', function(){
    var wb = XLSX.utils.table_to_book(document.getElementById('my-table'),{sheet: "Sheet name"})

    var wbout = XLSX.write(wb, {bookType: 'xlsx', bookSST: true, type: 'binary'});

    function s2ab(s) {
      var buf = new ArrayBuffer(s.length);
      var view = new Uint8Array(buf);
      for (var i = 0; i < s.length; i++) {
        view[i] = s.charCodeAt(i) & 0xFF;
      }
      return buf;
    }

    saveAs(new Blob([s2ab(wbout)], {type:"application/octet-stream"}), 'test.xlsx');
  })
</script>

Export MySQL data to Excel in PHP

Just Try With The Following :

PHP Part :

<?php
/*******EDIT LINES 3-8*******/
$DB_Server = "localhost"; //MySQL Server    
$DB_Username = "username"; //MySQL Username     
$DB_Password = "password";             //MySQL Password     
$DB_DBName = "databasename";         //MySQL Database Name  
$DB_TBLName = "tablename"; //MySQL Table Name   
$filename = "excelfilename";         //File Name
/*******YOU DO NOT NEED TO EDIT ANYTHING BELOW THIS LINE*******/    
//create MySQL connection   
$sql = "Select * from $DB_TBLName";
$Connect = @mysql_connect($DB_Server, $DB_Username, $DB_Password) or die("Couldn't connect to MySQL:<br>" . mysql_error() . "<br>" . mysql_errno());
//select database   
$Db = @mysql_select_db($DB_DBName, $Connect) or die("Couldn't select database:<br>" . mysql_error(). "<br>" . mysql_errno());   
//execute query 
$result = @mysql_query($sql,$Connect) or die("Couldn't execute query:<br>" . mysql_error(). "<br>" . mysql_errno());    
$file_ending = "xls";
//header info for browser
header("Content-Type: application/xls");    
header("Content-Disposition: attachment; filename=$filename.xls");  
header("Pragma: no-cache"); 
header("Expires: 0");
/*******Start of Formatting for Excel*******/   
//define separator (defines columns in excel & tabs in word)
$sep = "\t"; //tabbed character
//start of printing column names as names of MySQL fields
for ($i = 0; $i < mysql_num_fields($result); $i++) {
echo mysql_field_name($result,$i) . "\t";
}
print("\n");    
//end of printing column names  
//start while loop to get data
    while($row = mysql_fetch_row($result))
    {
        $schema_insert = "";
        for($j=0; $j<mysql_num_fields($result);$j++)
        {
            if(!isset($row[$j]))
                $schema_insert .= "NULL".$sep;
            elseif ($row[$j] != "")
                $schema_insert .= "$row[$j]".$sep;
            else
                $schema_insert .= "".$sep;
        }
        $schema_insert = str_replace($sep."$", "", $schema_insert);
        $schema_insert = preg_replace("/\r\n|\n\r|\n|\r/", " ", $schema_insert);
        $schema_insert .= "\t";
        print(trim($schema_insert));
        print "\n";
    }   
?>

I think this may help you to resolve your problem.

---

Related Topics