How to Create and Edit Spreadsheets in PHP?

There are various development tasks that can be done using PHP. Being the easiest programming language, it is preferred by all. Here, in this blog post; we will learn the use of PHP for creating and editing excel sheets.

We will be using PHPExcel Library which is used to read and write various types of spreadsheet formats such as XLS, XLSX, ODS and CSV. So, before we begin; make sure that you have PHP 5.2 or higher on your server and these PHP extensions are already installed: php_zip, php_xml, php_gd2

First of all, Let’s learn How to Create a Spreadsheet:

Creating a spreadsheet is one of the common tasks that can be done using PHP application. It is used for exporting data to Excel spreadsheet. Here’s the code that can be used to create a sample Excel spreadsheet with PHP Excel:


// Include PHPExcel library and create its object
require('PHPExcel.php');

// Create spreadsheet header

To download the spreadsheet instead of saving it, you can use this code:

header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="file.xlsx"');
header('Cache-Control: max-age=0');
$writer->save('php://output');

So, with this code; one can create a completely new spreadsheet. Now, let’s take a look at editing an existing spreadsheet.

Editing the Existing Spreadsheet:

In PHP, editing spreadsheets is similar to creating them. This below mentioned code can be used to edit the spreadsheet:

// Include PHPExcel library and create its object
require('PHPExcel.php');

// Create the PHPExcel spreadsheet writer object
// We will create xlsx file (Excel 2007 and above)

Once the editing and creating tasks are over then it is time to print the spreadsheet. With this code, we will print the spreadsheet:

$sheet->getPageSetup()->setOrientation(PHPExcel_Worksheet_PageSetup::ORIENTATION_LANDSCAPE);
$sheet -> getPageSetup()->setPaperSize(PHPExcel_Worksheet_PageSetup::PAPERSIZE_A4);
$sheet->getPageMargins()->setTop(1);
$sheet ->getPageMargins()->setRight(0.75);
$sheet ->getPageMargins()->setLeft(0.75);

Take Away:

PHPExcel comes with several features which we have not discussed here. Let’s say; PHP can be used for functions, calculations and charts. We will discuss about these very soon in our upcoming blogs.

Till then, stay tuned with Softqube Technologies – one of the leaders in PHP development India.