EasyXLS

How to export List to Excel in PHP

<?php

/*========================================================================
| Tutorial 01
|
| This code sample shows how to export list to Excel file in PHP. (1)
| The list contains data from a SQL database.
| The cells are formatted using a predefined format (2).
* ========================================================================*/
Click here to see the Excel file

include("Styles.inc");

header("Content-Type: text/html");

echo "Tutorial 01<br>";
echo "----------<br>";

// Create an instance of the class that exports Excel files (1)
$workbook = new COM("EasyXLS.ExcelDocument");

// Create the database connection
$serverName = "(local)";
$connectionInfo = array ("Database"=>"northwind", "UID"=>"sa", "PWD"=>"");  

$db_conn = sqlsrv_connect($serverName, $connectionInfo); 
if ($db_conn === false)
{
    echo "Unable to connect.";
    die(print_r(sqlsrv_errors(), true));
}

// Query the database
$query_result = sqlsrv_query( "SELECT TOP 100 CAST(Month(ord.OrderDate) AS varchar) + '/' + 
                                CAST(Day(ord.OrderDate) AS varchar) + '/' + 
                                CAST(year(ord.OrderDate) AS varchar) AS 'Order Date', 
                                P.ProductName AS 'Product Name', O.UnitPrice AS Price, 
                                CAST(O.Quantity AS varchar) AS Quantity, 
                                O.UnitPrice * O. Quantity AS Value 
                                FROM Orders AS ord, [Order Details] AS O, Products AS P 
                                WHERE O.ProductID = P.ProductID AND O.OrderID = ord.OrderID", $db_conn )
    or die( "<strong>ERROR: Query failed</strong>" );

// Create the list that stores the query values
$lstRows = new COM("EasyXLS.Util.List");

// Add the report header row to the list
$lstHeaderRow = new COM("EasyXLS.Util.List");
$lstHeaderRow->addElement("Order Date");
$lstHeaderRow->addElement("Product Name");
$lstHeaderRow->addElement("Price");
$lstHeaderRow->addElement("Quantity");
$lstHeaderRow->addElement("Value");
$lstRows->addElement($lstHeaderRow);

// Add the query values from the database to the list
while ($row=sqlsrv_fetch_array($query_result))
{
    $RowList = new COM("EasyXLS.Util.List");
    $RowList->addElement("" . $row['Order Date']);
    $RowList->addElement("" . $row["Product Name"]);
    $RowList->addElement("" . $row["Price"]);
    $RowList->addElement("" . $row["Quantity"]);
    $RowList->addElement("" . $row["Value"]);
    $lstRows->addElement($RowList);
}

// Create an instance of the class used to format the cells (2)
$xlsAutoFormat = new COM("EasyXLS.ExcelAutoFormat");
$xlsAutoFormat->InitAs($AUTOFORMAT_EASYXLS1);

// Export list to Excel file
echo "Writing file: C:\Samples\Tutorial01.xls<br>";
$workbook->easy_WriteXLSFile_FromList_2("C:\Samples\Tutorial01.xls", $lstRows, $xlsAutoFormat, "Sheet1");

// Confirm export of Excel file
if ($workbook->easy_getError() == "")
    echo "File successfully created.";
else
    echo "Error encountered: " . $workbook->easy_getError();

// Free the memory associated with the query
sqlsrv_free_stmt( $query_result );

// Close database connection
sqlsrv_close( $db_conn );           

// Dispose memory
$workbook->Dispose();
$workbook = null;
$lstRows = null;
$lstHeaderRow = null;
$RowList = null;
$xlsAutoFormat = null;

?>

Overloaded methods
For methods with same name but different parameters, only the first method overload retains the original name. Subsequent overloads are uniquely renamed by appending to the method name '_2', '_3', etc (method, method_2, method_3), an integer that corresponds to the order of declaration that can be found in EasyXLS.h, a file that comes with EasyXLS installation.

Linux and PHP
This code sample works just the way it is for PHP on Windows, but PHP under Linux requires also PHP/Java Bridge and a similar code sample in Java.

EasyXLS Excel libraries:

.NET
Excel Library for PHP
full COM+ version to import, export or convert Excel files
Excel Writer for PHP
COM+ version to create and export Excel files
Express Excel Writer for PHP
limited COM+ version to create and export Excel files in predefined formats
Download EasyXLS™ Excel Library for PHP

File formats:

MS Excel 97 - 2003
MS Excel 2007 - 2010
MS Excel 2013
MS Excel 2016
MS Excel 2019
XLSX XLSM XLSB XLS
XML HTML CSV TXT