The spreadsheet is a very popular data organizing format used for personal and business data because it proposes a way to store tabular data in a systematized way. The spreadsheet provides a format for storing information in the form of rows and columns. Hence, spreadsheets have become an indispensable part of keeping, organizing, and analyzing data. Automated solutions are more trending in business nowadays, the inclination of creating and manipulating Excel documents (XLS/XLSX) has emerged and budding at a fast pace. In this article, we’re going to explain how to create and edit Excel XLS or XLSX files in PHP-based apps. Basically, there are two ways to create Excel spreadsheets in PHP: (i) by using a PHP library or (ii) without using any library. The main focus will be on creating or manipulating an excel sheet by using a library.

PHP Libraries

Excel files can be difficult to work with from the command line or outside of Microsoft office software suites, that’s why PHP libraries were introduced to deal with excel files and make this task easier. There are many libraries that work efficiently like PHP spreadsheet, Spout, PHP excel template, Aspose. Cells for PHP via Java (it’s a powerful API) and PHPExcel library etc. PHP excel library allows you to read and create spreadsheets in various formats including ODS, XLSX, CSV, and XLS. Make sure that you have PHP’s upgraded version and not older than PHP 5.2. Also, it’s better if you install these extensions:  php_qd2, php_zip and php_xml.

Creating the spreadsheet from Scratch

In PHP development, spreadsheet creation is common as it is used to export data to an Excel spreadsheet. The following code can be used to create a spreadsheet from scratch using the PHPExcel library:

1. Include PHPExcel library and creation of its object:

require(‘PHPExcel.php’);

$phpExcel=new PHPExcel;

2. Set the font to Arial Black:

$phpExcel->getDefaultStyle()->getFont()->setName(‘Arial Black’);

3. Now set the Font size to 12

$phpExcel->getDefaultStyle()->getFont()->setSize(12);

4. Now set title, description, and creator

$phpExcel->getProperties()->setTitle(“Purchase List”);

$phpExcel->getProperties()->setCreator(“Sebastian”);

$phpExcel->getProperties()->setDescription(“Excel Spreadsheet in PHP”);

5. We will create writer object and XLSX file in Excel 2007 and above

$writer=PHPExcelIOfactory::createWriter($phpExcel. “Excel2007”);

6. Along with creating the writer object, the first sheet is also created. So we will get the already created sheet

$sheet->setTitle(‘My Products List’);

7. Create the spreadsheet header

$sheet->getCell(‘A1’)->setValue(‘Product1’);

$sheet->getCell(‘B1’)->setValue(‘Quantity’);

$sheet->getCell(‘C1’)->setValue(‘Price’);

8. Make the header text bold and larger

$sheet->getStyle(‘A1:D1’)->getFont()->setBold(true)->setSize(14);

9. Insert product data and auto size the columns

$sheet->getColumnDimension(‘A’)->setAutosaveSize(true);

$sheet->getColumnDimension(‘B’)->setAutosaveSize(true);

$sheet->getColumnDimension(‘C’)->setAutosaveSize(true);

10.                         Now save the spreadsheet

$writer->save(‘productslist.xlsx’);

Editing an Existing Spreadsheet in PHP

Editing a spreadsheet is quite similar to creating a new spreadsheet process. The following code can be used to edit:

1. Include the PHPExcel library and creates its object

require(‘PHPExcel.PHP);

2. Open an existing spreadsheet

$phpExcel=PHPExcel_IOFactory::load(‘productslist.xlsx’);

3. Get the first spreadsheet

$sheet=$phpExcel->getActiveSheet();

4. Now Remove two rows, starting from the row 2

$sheet->removeRow(2,2);

5. Insert a new row before row 2

$sheet->insertNewRowBefore(2,1);

6. We will create writer object and XLSX file in Excel 2007 and above

$writer=PHPExcel_IOFactory::createWriter($phpExcel,”Excel2007”);

7. Now save it

$writer->save(‘productslist.xlsx’);

 Preparing a Spreadsheet for Printing

To prepare a spreadsheet for printing, you need to set size, paper orientation and margins with the help of the following coding:

1.     $sheet->getPageSetup()->setOrientation(PHPExcel_Worksheet_PageSetup::ORIENTATION_LANDSCAPE);

2.     $sheet->getPageSetup()->setPaperSize(PHPExcel_Wroksheet_PaperSetup::PAPERSIZE_A4);

3.     $sheet->getPageMargins()->setTop(1);

4.     $sheet-getPageMargins()->setRight(0.80);

5.     $sheet-getPageMargins()->setLeft(0.80);

6.     $sheet-getPageMargins()->setBottom(1);

 Making a spreadsheet in PHP can be done easily by using the above guide. 

Credit: Laura Jimenez, Ishine365