Page 2
9 Mar
Admin

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:
Admin
Hari Patel
He is 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. He believe and live by a mission that he help more entrepreneurs to build, launch and grow profitable businesses.
contact
Are you looking for real talent for your dream projects?
Inquiry
We will respond you in within 24 hours
Do you have a project in mind?
Let's start your project
Quick Contact
We can give you a fast response via email, phone call or skype.
For business inquiries
Call us on
Let's Skype
Close
Inquire
Let’s make something awesome together!
Drop us a line, or give us a heads up if you’re interested in visiting us.
  • Accepted file types: docx, xlsx, ppt, pdf, jpg, gif, png.
  • 7 − 4 =
  • This field is for validation purposes and should be left unchanged.
100% secure and private