Create and read Excel sheets with styles and dynamic data - PHP & MySQL

We will be using PHPSpreadsheet library which provides all the functionality to create and read the Excel sheets in PHP. You can download it via composer, make sure you have downloaded and install the composer in your system. You can download the composer from here. Right now there is no standalone version of this library that is why you must have composer installed in your system. Open command prompt in your project folder, if you are working directly on cPanel then you might need to open terminal from cPanel home page like the screenshot below:

While in your project folder in command prompt run the following command to install the library. If you are using Mac or Linux make sure you have folder permissions enabled because this library will create a lot of files and folders.

composer require phpoffice/phpspreadsheet

Create excel file

Open your file from where you want to run the function to create Excel file and paste the following code in it. We will be using file named index.php:

<?php

// Including all files from library
require "vendor/autoload.php";

// Creating a new sheet in Excel file
$spreadsheet = new PhpOffice\PhpSpreadsheet\Spreadsheet();

// Create a write object to save the file and pass spreadsheet instance as parameter
$writer = new PhpOffice\PhpSpreadsheet\Writer\Xlsx($spreadsheet);

// Save the file named "Test.xlsx"
$writer->save("Test.xlsx");

?>

Create Table Row

We will be creating a simple table that will display the data from database. As Excel sheets use row and column address to save the values, also known as Cells. So paste the following code that will create 5 columns in first row of first sheet:

// Get first sheet from file and set value in specified columns respectively
$spreadsheet->getSheet(0)->setCellValue("A1", "Customer Name");
$spreadsheet->getSheet(0)->setCellValue("B1", "Phone");
$spreadsheet->getSheet(0)->setCellValue("C1", "Address Line");
$spreadsheet->getSheet(0)->setCellValue("D1", "Check number");
$spreadsheet->getSheet(0)->setCellValue("E1", "Amount");

Adjust the width of cells

If you run the code now you will see a new Excel file will be created in your project root folder. But all cells will have equal width which makes it difficult to see full content of cell. We need to find a way to make it dynamic so that it will automatically adjust the cell size according to its content. We will be using setAutoSize(bool) function for this purpose as following:

$spreadsheet->getSheet(0)
	->getColumnDimension("A")
	->setAutoSize(true);

$spreadsheet->getSheet(0)
	->getColumnDimension("B")
	->setAutoSize(true);

$spreadsheet->getSheet(0)
	->getColumnDimension("C")
	->setAutoSize(true);

$spreadsheet->getSheet(0)
	->getColumnDimension("D")
	->setAutoSize(true);

$spreadsheet->getSheet(0)
	->getColumnDimension("E")
	->setAutoSize(true);

Styling the cells

Now we need to highlight the cells of header so they will be prominent than the others. We will be creating an array that will hold all the styles. For simplicity, we will be setting the background color (fill color) as red and font color as white.

$styles = [
	"fill" => [
		"fillType" => PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID,
		"startColor" => [ "rgb" => "FF0000" ]
	],
	"font" => [
		"color" => [ "rgb" => "FFFFFF" ]
	]
];
// Set the range of cells where this style should be applied
$spreadsheet->getSheet(0)
	->getStyle("A1:E1")
	->applyFromArray($styles);

Add data in cells from database

We will be using a sample database named classicmodels of course you will be using your own. We will connect with database and run the required query. Then we will loop through all the records returned from that query and create 5 cells in each iteration and after that move to the next row.

// Connecting with database
$conn = mysqli_connect("localhost", "root", "", "classicmodels");

// Executing the required query
$result = mysqli_query($conn, "SELECT * FROM payments INNER JOIN customers ON customers.customerNumber = payments.customerNumber");

// Start from second row
$count = 2;
while ($row = mysqli_fetch_object($result))
{
	// Add required data
	$spreadsheet
		->getSheet(0)
		->setCellValue("A" . $count, $row->customerName);

	$spreadsheet
		->getSheet(0)
		->setCellValue("B" . $count, $row->phone);
	
	$spreadsheet
		->getSheet(0)
		->setCellValue("C" . $count, $row->addressLine1);
	
	$spreadsheet
		->getSheet(0)
		->setCellValue("D" . $count, $row->checkNumber);
	
	$spreadsheet
		->getSheet(0)
		->setCellValue("E" . $count, $row->amount);

	// Move to next row
	$count++;
}

Reading data from cells

You can read the data from any Excel file using IOFactory class and loop through rowIterator. Each row will return all cells in it and you can loop through all cells using cellIterator. To get the cell value you can either call getValue() or getCalculatedValue(). If you are using some formula in your cells then you need to use getCalculatedValue() and it will return the calculated value after performing that function. But you can also use this for normal cells too.

<?php

// Get Excel file
$reader = PhpOffice\PhpSpreadsheet\IOFactory::load("Test.xlsx");

// Get all rows in first sheet
$rows = $reader->getSheet(0)->getRowIterator();

?>

<table>
	<?php
		// Loop through all rows
		// $count will be use to make first table row as heading
		$count = 1; foreach ($rows as $row) {
	?>

		<tr>
			<?php
				// Get all cells in each row
				$columns = $row->getCellIterator();
			?>

			<?php
				// Loop through all cells
				foreach ($columns as $column) {
			?>

				<?php if ($count == 1) { ?>

					<th>
						<?php echo $column->getCalculatedValue(); ?>
					</th>

				<?php } else { ?>

					<td>
						<?php echo $column->getCalculatedValue(); ?>
					</td>

				<?php } ?>

			<?php } ?>
		</tr>

	<?php $count++; } ?>
</table>

Downloading the excel file

In some cases you want to give users the ability to download the Excel file in his computer. After HTML5 it became so easy that you just have to create an anchor tag, set the path of file that needs to be downloaded and give the attribute download.

<a href="Test.xlsx" download="Test.xlsx">Download</a>

So that’s it. Hope you find it super simple to create and read Excel files using PHP and it might help if you ever came across with some project that involve the use of spreadsheets. If you face any problems or have any suggestions, please feel free to tell in the comments section below.

Download with progress bar

However, there is another approach to download the file. You can display a progress bar to know how much of the file has been downloaded. Learn more from here.

[wpdm_package id=’187′]

Post Views: 334

Create and read Excel sheets with styles and dynamic data – PHP & MySQL