Create Excel .xls File using PHP Spreadsheet and Mysql

Hello friends hope you all are doing well and please be safe in this pandemic situation of Corona we all loves you. This tutorial is for those wants to create .xls file using PHP.  Most of the time we use this while we are generating reports. We use PHP Spreadsheets for creating the xls file. You can also create CSV and other files using the library.

You can download the PHP Spreadsheets from here – Github Link.

I this tutorial i will show you that how you can create excel file using PHP Spreadsheet and Mysql. We will use simple select query to get the data and add whole data to the Excel file. In this code you we will get 2 (Direct download, File in folder) options to access the generated files.




In the below we have included our Spreadsheet and our connection class for getting database result.

require 'vendor/autoload.php';
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

include_once '../functions/connectionClass.php';
$con=new connectionClass();

In below code we have added the query for generating the table values and get the whole data in array.

$list="select * from tablename";
$result=  $con->query($list);
while($row1= mysqli_fetch_array($result,1)){
    $arr[]= $row1;
}

Now by the help of array we will get the table columns for making header in the excel and the values.

//Column name of the table
$columnName = array_keys($arr[0]);
$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();
// Add columns to spreadsheet
$sheet->fromArray($columnName,NULL,'A1');
// Add values to spreadsheet
$sheet->fromArray($arr, null, 'A2');
$writer = new Xlsx($spreadsheet);
$fileName="filename.xlsx";

For download after crating use this line below the above code

header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="'.$fileName.'"');
$writer->save('php://output');

For save in folder use the below code

$writer->save($filename);

Hope you like our code and if you have any advice then please comment below we will surely consider it.

~ Stay Home Use Mask Stay Safe ~