How to Create and Edit Spreadsheets in PHP?

March 9, 2016

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

Spreadsheets in PHP

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');

$phpExcel = new PHPExcel;

// Set default font to Arial
$phpExcel->getDefaultStyle()->getFont()->setName('Arial');

// Set default font size to 12
$phpExcel->getDefaultStyle()->getFont()->setSize(12);

// Set spreadsheet properties – title, creator and description
$phpExcel ->getProperties()->setTitle("Product list");
$phpExcel ->getProperties()->setCreator("Voja Janjic");
$phpExcel ->getProperties()->setDescription("PHP Excel spreadsheet testing.");

// Create the PHPExcel spreadsheet writer object
// We will create xlsx file (Excel 2007 and above)
$writer = PHPExcel_IOFactory::createWriter($phpExcel, "Excel2007");

// When creating the writer object, the first sheet is also created
// We will get the already created sheet
$sheet = $phpExcel ->getActiveSheet();

// Set sheet title
$sheet->setTitle('My product list');

// 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');

// Load an existing spreadsheet
$phpExcel = PHPExcel_IOFactory::load('products.xlsx');

// Get the first sheet
$sheet = $phpExcel ->getActiveSheet();

// Remove 2 rows starting from the row 2
$sheet ->removeRow(2,2);

// Insert one new row before row 2
$sheet->insertNewRowBefore(2, 1);

// 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.

Share on

Hari Patel

I am the Managing Director of Softqube Technologies Pvt. Ltd., a modern-day digital transformation, design and development service provider. We provide services to businesses of all verticals across the globe. I believe and live by a mission that I help more entrepreneurs to build, launch and grow profitable businesses.

Let’s Work together!

"*" indicates required fields

Drop files here or
Max. file size: 5 MB, Max. files: 2.
    This field is for validation purposes and should be left unchanged.