1. Products
  2.   Spreadsheet
  3.   PHP
  4.   PHP_XLSXWriter
 
  

 Open Source PHP API for XLSX Excel Spreadsheet Handling

Create, Edit, Read and Manage Excel-Compatible Spreadsheet in XLSX Format Online via Open Source PHP Library.

What is PHP_XLSXWriter?

The PHP_XLSXWriter is a very powerful open source API that gives software developers the capability to create and manage Excel spreadsheets online inside their own PHP applications. The library provides support for generating Microsoft Excel-compatible spreadsheets in (Office 2007+) XLSX format. The library is very lightweight and is designed to use a very lesser amount of memory.

The PHP_XLSXWriter library has included support for very valuable operations related to Excel XLSX files creation and management, such as accessing and reading existing XLSX files, writing huge 100K+ row spreadsheets, accessing a specific row or column of a worksheet, modifying the existing spreadsheet, add new rows or columns, manage multiple worksheets, adding new worksheets to a workbook, currency support, using date, numeric cell formatting support, simple formulas, basic cell styling support, and many more.

The library is very stable and produces high performance and lesser cost as compared to several other available spreadsheets libraries. It has the capability of writing large XLSX spreadsheet files with high speed and less memory usage

Previous Next

Getting Started with PHP_XLSXWriter

The recommended way to install PHP_XLSXWriter library is by using GitHub. Please use the following command for smooth installation.

Install PHP_XLSXWriter via Git

gh repo clone mk-j/PHP_XLSXWriter

Create Excel Spreadsheet via PHP

The open source PHP_XLSXWriter enables software developers to create web apps with the capability to generate a new XLSX spreadsheet with just a couple of lines of PHP code. Developers can also easily insert a new worksheet with all basic features. The library allows to add new rows or columns, insert data into existing sheets, apply basic formulas, modify data in the cells, apply formatting to the new cells, delete an existing worksheet, and so on. The following example shows how software developers can create Excel Spreadsheet file inside PHP applications using PHP commands.

How to Create Simple Excel Spreadsheet inside PHP Applications?

set_include_path( get_include_path().PATH_SEPARATOR."..");
include_once("xlsxwriter.class.php");

$header = array(
    'year'=>'string',
    'month'=>'string',
    'amount'=>'price',
    'first_event'=>'datetime',
    'second_event'=>'date',
);
$data1 = array(
    array('2003','1','-50.5','2010-01-01 23:00:00','2012-12-31 23:00:00'),
    array('2003','=B2', '23.5','2010-01-01 00:00:00','2012-12-31 00:00:00'),
    array('2003',"'=B2", '23.5','2010-01-01 00:00:00','2012-12-31 00:00:00'),
);
$data2 = array(
    array('2003','01','343.12','4000000000'),
    array('2003','02','345.12','2000000000'),
);
$writer = new XLSXWriter();
$writer->writeSheetHeader('Sheet1', $header);
foreach($data1 as $row)
	$writer->writeSheetRow('Sheet1', $row);
foreach($data2 as $row)
	$writer->writeSheetRow('Sheet2', $row);

$writer->writeToFile('xlsx-sheets.xlsx');
//$writer->writeToStdOut();
//echo $writer->writeToString();

exit(0);

Read or Modify Existing Spreadsheets

The PHP_XLSXWriter library has included support for accessing as well as modifying the existing spreadsheet documents using PHP commands. To modify an existing spreadsheet file first you need to open it and apply the changes according to your needs. You can apply basic formatting to your cells, insert a new row or columns, merge rows, enhance borders, change font size, and much more. The following example shows how software developers can manage width of rows and columns of an Excel spreadsheet inside PHP applications.

How to Modify Width of Rows/Columns of an Excel Spreadsheet inside PHP Applications?

set_include_path( get_include_path().PATH_SEPARATOR."..");
include_once("xlsxwriter.class.php");

$header = array(
	"col1"=>"string",
	"col2"=>"string",
	"col3"=>"string",
	"col4"=>"string",
);

$writer = new XLSXWriter();
$writer->writeSheetHeader('Sheet1', $header, $col_options = ['widths'=>[10,20,30,40]] );
$writer->writeSheetRow('Sheet1', $rowdata = array(300,234,456,789), $row_options = ['height'=>20] );
$writer->writeSheetRow('Sheet1', $rowdata = array(300,234,456,789), $row_options = ['height'=>30] );
$writer->writeSheetRow('Sheet1', $rowdata = array(300,234,456,789), $row_options = ['height'=>40] );
$writer->writeToFile('xlsx-widths.xlsx');

Manage Cell Formatting and Styles via PHP

The open source PHP_XLSXWriter library gives software developers the capability to apply different kinds of cell formatting in their Excel XLSX document. The library has provided support for basic as well as advanced cell formats such as string, integer, date, datetime, price, time, dollar, and more. The basic cell styles such as font, font-size, border style, border-color, fill, halign and valign have been available from the earlier versions. The following example shows how software developers can apply styles and formatting to Excel spreadsheet inside PHP applications using PHP commands.

How to Apply Styles to an Excel Worksheet inside PHP Applications?

set_include_path( get_include_path().PATH_SEPARATOR."..");
include_once("xlsxwriter.class.php");


$writer = new XLSXWriter();
$styles1 = array( 'font'=>'Arial','font-size'=>10,'font-style'=>'bold', 'fill'=>'#eee', 'halign'=>'center', 'border'=>'left,right,top,bottom');
$styles2 = array( ['font-size'=>6],['font-size'=>8],['font-size'=>10],['font-size'=>16] );
$styles3 = array( ['font'=>'Arial'],['font'=>'Courier New'],['font'=>'Times New Roman'],['font'=>'Comic Sans MS']);


$writer->writeSheetRow('Sheet1', $rowdata = array(300,234,456,789), $styles1 );
$writer->writeSheetRow('Sheet1', $rowdata = array(300,234,456,789), $styles2 );
$writer->writeSheetRow('Sheet1', $rowdata = array(300,234,456,789), $styles3 );
$writer->writeToFile('xlsx-styles.xlsx');
 English