Creating and Editing Excel Spreadsheets in PHP

To create or edit Excel spreadsheets using pure PHP, we will use the PHPExcel library, which can read and write many spreadsheet formats, including xls, xlsx, ods and csv. Before we continue, double-check that you have PHP 5.2 or higher on your server and that the following PHP extensions are installed: php_zip, php_xml and php_gd2.

Creating a Spreadsheet

Creating a spreadsheet is one of the most common use cases in PHP applications and is used for exporting data to Excel spreadsheet. Look at the following code to see how to create a sample Excel spreadsheet with PHPExcel:

// 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
$sheet ->getCell('A1')->setValue('Product');
$sheet ->getCell('B1')->setValue('Quanity');
$sheet ->getCell('C1')->setValue('Price');

// Make the header text bold and larger
$sheet->getStyle('A1:D1')->getFont()->setBold(true)->setSize(14);

// Insert product data


// Autosize the columns
$sheet->getColumnDimension('A')->setAutoSize(true);
$sheet->getColumnDimension('B')->setAutoSize(true);
$sheet->getColumnDimension('C')->setAutoSize(true);

// Save the spreadsheet
$writer->save('products.xlsx'); 

If you want to download the spreadsheet instead of saving it to the server, do the following:

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

Editing an Existing Spreadsheet

Editing spreadsheets in PHP is similar to creating them:

// 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)
$writer = PHPExcel_IOFactory::createWriter($phpExcel, "Excel2007");

// Save the spreadsheet
$writer->save('products.xlsx'); 

Preparing the Spreadsheet for Printing

To prepare the spreadsheet for printing, we will set paper orientation, size and margins:

$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);
$sheet ->getPageMargins()->setBottom(1); 

Using PHPExcel with Laravel

The PHPExcel library can also be used in the Laravel framework. Check out the following PHP package (here) and install it through Composer. After completing the installation steps, you could use the following code to export the data from the database into Excel spreadsheet:

Excel::create('Products', function($excel) {

                // Set the title
                $excel->setTitle('Product list');
   
                // Set the creator
               $excel->setCreator('Voja Janjic');
   
               // Set description
               $excel->setDescription('PHP Excel spreadsheet testing');
   
               $excel->sheet('Products', function($sheet) {
     
                                // Get data from the database
                                $products = Product::all(); 
    
                               // Generate header row
                               $sheet->row(1, array(
                                                'ID',
                                                'Product',
                                                'Price',
                                                'Quantity',         
                                ));
    
                                // Generate data rows 
                               $i = 2; 
                               foreach($products as $product) {        
                                                $sheet->row($i, array(
                                                                     $product->product_id,
                                                                     $product->product_name,
                                                                     $product->price,
                                                                     $variety->quantity,        
                                                ));
     
                                                $i++;
                               }

               });

})->export('xlsx'); 

Next Steps

PHPExcel offers many features that are not mentioned in this tutorial, such as functions, calculations and charts. Read the PHPExcel documentation to learn more about these.