Share
Preview
We are going to use the PhpSpreadsheet library to create an XLS spreadsheet with PHP, in just 5 minutes.
 ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌

ALEX WEB DEVELOP
Alex
Create an XLS Spreadsheet in 5 Minutes with PHP

XLS


Hey,
I'm Alex, from Alex Web Develop. I hope you are having a great day.

In this email you'll see how to create an XLS spreadsheet with PHP, in just 5 minutes.
We are going to do that using the excellent PhpSpreadsheet library.

First, let's install PhpSpreadsheet with Composer:
composer require phpoffice/phpspreadsheet


And make sure the Composer autoload file is included:
include '/path/to/Composer/vendor/autoload.php';


Next, it is a good idea to alias the PhpOffice\PhpSpreadsheet\Spreadsheet class, as well as the class for the specific file format that you want to create.
For this example, we are going to create an Excel Xlsx file, so the class we need is PhpOffice\PhpSpreadsheet\Writer\Xlsx:
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;



Now we are ready to create our spreadsheet.
This is as simple as creating a new Spreadsheet object:
$spreadsheet = new Spreadsheet();



Now, let's populate our new spreadsheet with data from a database table.
For example, let's use a table with a list of priced items.
Each row of the spreadsheet will have the item's name in the first column, and the item's price in the second column.

First, let's add the column titles in the first row of the spreadsheet.
PhpSpreadsheet offers a few different methods to set a cell's value. A simple way to do it is by using the setCellValue() method:
$sheet = $spreadsheet->getActiveSheet();

$sheet->setCellValue("A1", "Name");
$sheet->setCellValue("B1", "Price");


As you probably have guessed, "A1" and "B1" are the spreadsheet's cells where to write the "Name" and "Price" text strings.


Next, we are going to iterate through a database query result and insert each result row in a new spreadsheet's row.
We need to keep track of the current row number, so we are going to use a counter starting from 2 (since row 1 is for the column titles).

This time we are going to use the setCellValueByColumnAndRow() method, where you can specify both column and row as numbers.
Here's how it works:
$row = 2;

while ($dbRow = mysqli_fetch_assoc($dbConn, $dbRes)) {
  $name = $dbRow['name'];
  $price = $dbRow['price'];
  $sheet->setCellValueByColumnAndRow(1, $row, $name);
  $sheet->setCellValueByColumnAndRow(2, $row, $price);
  $row++;
}


Finally, close the spreadsheet and output it to the remote browser:
$writer = new Xlsx($spreadsheet);
$writer->save('php://output');



And that's it.
Of course, this is just the tip of the iceberg.
PhpSpreadsheet has TONS of functionalities such as writing to file in different formats, cell formatting (including conditional formatting), reading from files and much more.

Links:
the cool bit

THE COOL BIT


Among the many things you can do with PhpSpreadsheet, you can also add formulas to cells.
It is as simple as writing the formula as a text string with a "=" sign before it.

For example, adding the following code after the while loop will include a "total price sum" in the cell below the last price:
$range = "B2:B" . ($row - 1);
$sheet->setCellValue(2, $row, "=SUM(" . $range . ")");
tip of the week

TIP OF THE WEEK


You can use the range() function to create sequences.
It is a faster and more readable alternative to having an incrementing variable.
The cool thing about range() is that it works with numbers and with alphabetic characters.
For example:
$numbers = range(0, 100, 10);
// $numbers = [0, 10, 20, 30, ..., 100];

$chars = range("d", "p", 2);
// $chars = ["d", "f", "h", ..., "p"];
the challenge

WEEKLY CHALLENGE


Here's a new coding challenge for you.

Print all the possible combinations of two alphabetic characters: "aa", "ab", "ac", ..., "cc", "cd", .. "zz".
Note: do not repeat combinations with the same letters. For example, if you print "ac" then you should not print "ca".
Hint: range() can help.


Let me know if you can solve it or if you need a hint :-)
next steps

NEXT STEPS


Want to take your PHP skills to the next level?
Take a look at my professional PHP courses:


If you have any questions just reply to this email. I'm happy to help.


Before you go:
  • If you enjoyed this email, click here to share it with your friends.
  • Add my address (alex@alexwebdevelop.com) to your Contacts to make sure my next emails will reach you.
  • Send me a reply and let me know what you think. I read all the emails.

All the best,
Alex



Thanks to Freepik for the images used in this email.


You are receiving this newsletter because you subscribed to Alex Web Develop.

If you unsubscribe, you will not get any more emails from me.

Alessandro Castellano, P.IVA (VAT ID): 07012140484, via Luigi Morandi 32, 50141 Firenze FI, Italy

Email Marketing by ActiveCampaign