A simple way to transfer tabular data from PHP to Excel

By writing this topic I pushed one note about generating xls in PHP .

The method presented in that note is really very simple, but may not always be convenient.

There are many other ways to transfer tabular data from PHP to Excel, I will describe the one that seemed to me the most simple and functional. It should be especially noted that I’m not talking about generating the xls file, but only inviting the user to open the data with Excel so that users who are not experienced in programming will not notice fraud.



So, the first thing to do is to place on our page a link to the script that generates the following headings:

header("Pragma: public");

header("Expires: 0");

header("Cache-Control: must-revalidate, post-check=0, pre-check=0");

header("Cache-Control: private", false);

header("Content-Type: application/x-msexcel");

header("Content-Disposition: attachment; filename=\"" . iconv('UTF-8', 'CP1251', $object->getFileName()) . "\";");

header("Content-Transfer-Encoding: binary");

header("Content-Length: " . $object->getFileSize());



$ object is a spherical object in a vacuum that each reader realizes in the way he likes. The purpose of getFileName () and getFileSize () getters is clear from the names. It is worth highlighting one non-obvious nuance (thanks to savostin for reminding about this) - getFileName () can of course return any file name, but if you want the browser to suggest opening the received content in Excel, then the file extension should be xls.

I haven’t told anything new yet, all of this has been invented before me, however, as well as what will be described below.

As was rightly noted in the comments on the note about xls generation in PHP , Excel works much faster with XML. But the most important advantage, perhaps, is still not in speed, but in much wider possibilities. I will not go deeper into the wilds, but will only give a simple example, and a link to a detailed description of all the tags.



So, after the headers are generated, we need to give the user the actual data. I usually wrap the table generation in a separate method:

echo $object->getContent();



And I generate the table using Smarty:

 <? xml version = "1.0"?>
 <? mso-application progid = "Excel.Sheet"?>
 <Workbook xmlns = "urn: schemas-microsoft-com: office: spreadsheet" xmlns: o = "urn: schemas-microsoft-com: office: office" xmlns: x = "urn: schemas-microsoft-com: office: excel "xmlns: ss =" urn: schemas-microsoft-com: office: spreadsheet "xmlns: html =" http://www.w3.org/TR/REC-html40 ">
	 <Styles>
  		 <Style ss: ID = "bold">
			 <Font ss: Bold = "1" />
		 </ Style>
 	 </ Styles> 
	 <Worksheet ss: Name = "WorksheetName">
		 <Table>
			 <Row>
				 {foreach from = $ data.header item = caption}
					 <Cell ss: StyleID = "bold"> <Data ss: Type = "String"> {$ caption.columnName} </ Data> </ Cell>
				 {/ foreach}
			 </ Row> 
			 {foreach from = $ data.content item = row}	
				 <Row>
					 {foreach from = $ row item = col}
						 <Cell> <Data ss: Type = "String"> {$ col} </ Data> </ Cell>
					 {/ foreach}
				 </ Row>
			 {/ foreach}
		 </ Table>
	 </ Worksheet>
 </ Workbook>

As can be seen from the code, an array of $ data containing two arrays is passed to the template - the table header row and the data itself.

It is worth noting that using a template engine only for generating XML is somewhat expensive, and you can get XML in many other ways. In my particular case, the generation of XML is just a small bun in a large project, where you can’t do without a template engine.



As an example, I gave a simple table, if you wish, you can manipulate a much larger number of attributes. This is especially nice if you consider that no third-party libraries are required for implementation.

The described method has been working on one project for several years and no user has yet suspected that the data it opens is not an MS Office document.

')

You can read more about the XML structure used in MS Excel in MSDN XML Spreadsheet Reference