Instantly share code, notes, and snippets.

Embed
What would you like to do?
Generate the most basic Excel XLSX and XML files in PHP, see https://kaspars.net/blog/wordpress/excel-xlsx-xml-php
<?php
// Note: there are some references to WordPress methods and functions
class cf7_export_excel {
protected $doc_prop = array(
'Author' => 'Name',
'Company' => 'Company',
'Created' => '',
);
protected $rows = array();
protected $shared_strings = array();
function __construct( $doc_prop = array() ) {
$this->doc_prop = array_merge( $this->doc_prop, $doc_prop );
}
function can_xlsx() {
return class_exists( 'ZipArchive', false );
}
function add_row( $fields ) {
$this->rows[] = $fields;
}
function add_rows( $rows ) {
$this->rows = array_merge( $this->rows, $rows );
}
function xml_save() {
$xml_filename = tempnam( sys_get_temp_dir(), 'cf7-export-xml' );
$this->doc_prop['Created'] = gmdate( 'Y-m-d\TH:i:s\Z' );
$doc_prop_fields = array();
foreach ( $this->doc_prop as $prop_key => $prop_value ) {
$doc_prop_fields[] = sprintf(
'<%1$s>%2$s</%1$s>',
$prop_key,
filter_var( $prop_value, FILTER_SANITIZE_SPECIAL_CHARS )
);
}
$xml = sprintf(
'<?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">
<DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">
%s
</DocumentProperties>
<Worksheet ss:Name="Sheet1">
<Table>%s</Table>
</Worksheet>
</Workbook>',
implode( "\n", $doc_prop_fields ),
implode( "\n", $this->xml_get_rows() )
);
$save_xml = file_put_contents( $xml_filename, $xml );
if ( $save_xml ) {
return $xml_filename;
}
return new WP_Error(
'export-xml-fail',
__( 'Failed to save the exported Excel XML file.', 'cf7-storage' )
);
}
function xlsx_save() {
if ( ! $this->can_xlsx() ) {
return new WP_Error(
'missing-zip-extension',
__( 'You version of PHP doesn\'t support creating ZIP files which is required for creating XLSX files.', 'cf7-storage' )
);
}
$zip_filename = tempnam( sys_get_temp_dir(), 'cf7-export-xlsx' );
$zip = new ZipArchive();
$create_zip = $zip->open( $zip_filename, ZipArchive::CREATE );
if ( ! $create_zip ) {
return new WP_Error(
'create-zip-fail',
__( 'Failed to create the ZIP file required for XLSX.', 'cf7-storage' )
);
}
/**
* Build the XLSX file and directory tree
*/
$zip->addEmptyDir( 'docProps' );
$zip->addFromString( 'docProps/app.xml', $this->xlsx_get_app_xml() );
$zip->addFromString( 'docProps/core.xml', $this->xlsx_get_core_xml() );
$zip->addEmptyDir( '_rels' );
$zip->addFromString( '_rels/.rels', $this->xlsx_get_rels_xml() );
$zip->addEmptyDir( 'xl/worksheets' );
$zip->addFromString( 'xl/worksheets/sheet1.xml', $this->xlsx_get_sheet_xml() );
$zip->addFromString( 'xl/workbook.xml', $this->xlsx_get_workbook_xml() );
$zip->addFromString( 'xl/sharedStrings.xml', $this->xlsx_get_shared_strings_xml() );
$zip->addEmptyDir( 'xl/_rels' );
$zip->addFromString( 'xl/_rels/workbook.xml.rels', self::xlsx_get_workbook_rels_xml() );
$zip->addFromString( '[Content_Types].xml', $this->xlsx_get_content_types_xml() );
$zip->close();
return $zip_filename;
}
function xlsx_get_shared_string_no( $string ) {
static $string_pos = array();
if ( isset( $this->shared_strings[ $string ] ) ) {
$this->shared_strings[ $string ] += 1;
} else {
$this->shared_strings[ $string ] = 1;
}
if ( ! isset( $string_pos[ $string ] ) ) {
$string_pos[ $string ] = array_search( $string, array_keys( $this->shared_strings ) );
}
return $string_pos[ $string ];
}
function xlsx_cell_name( $row_no, $column_no ) {
$n = $column_no;
for ( $r = ''; $n >= 0; $n = intval( $n / 26 ) - 1 ) {
$r = chr( $n % 26 + 0x41 ) . $r;
}
return $r . ( $row_no + 1 );
}
function xml_get_rows() {
$rows = array();
foreach ( $this->rows as $row ) {
$cells = array();
foreach ( $row as $field_value ) {
$field_value = filter_var( $field_value, FILTER_SANITIZE_SPECIAL_CHARS );
$field_type = 'String';
if ( is_numeric( $field_value ) ) {
$field_type = 'Number';
}
$cells[] = sprintf(
'<Cell><Data ss:Type="%s">%s</Data></Cell>',
$field_type,
$field_value
);
}
$rows[] = sprintf(
'<Row>%s</Row>',
implode( "\n", $cells )
);
}
return $rows;
}
function xlsx_get_sheet_xml() {
$rows = array();
foreach ( $this->rows as $row_no => $row ) {
$cells = array();
$row = array_values( $row );
foreach ( $row as $col_no => $field_value ) {
$field_type = 's';
if ( is_numeric( $field_value ) ) {
$field_type = 'n';
}
$field_value_no = $this->xlsx_get_shared_string_no( $field_value );
$cells[] = sprintf(
'<c r="%s" t="%s"><v>%d</v></c>',
$this->xlsx_cell_name( $row_no, $col_no ),
$field_type,
$field_value_no
);
}
$rows[] = sprintf(
'<row r="%s">
%s
</row>',
$row_no + 1,
implode( "\n", $cells )
);
}
return sprintf(
'<?xml version="1.0" encoding="utf-8" standalone="yes"?>
<worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships">
<sheetData>
%s
</sheetData>
</worksheet>',
implode( "\n", $rows )
);
}
function xlsx_get_shared_strings_xml() {
$shared_strings = array();
foreach ( $this->shared_strings as $string => $string_count ) {
$shared_strings[] = sprintf(
'<si><t>%s</t></si>',
filter_var( $string, FILTER_SANITIZE_SPECIAL_CHARS )
);
}
return sprintf(
'<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<sst count="%d" uniqueCount="%d" xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
%s
</sst>',
array_sum( $this->shared_strings ),
count( $this->shared_strings ),
implode( "\n", $shared_strings )
);
}
function xlsx_get_workbook_xml() {
return sprintf(
'<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<workbook xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships">
<sheets>
<sheet name="Sheet1" sheetId="1" r:id="rId1" />
</sheets>
</workbook>'
);
}
function xlsx_get_content_types_xml() {
return sprintf(
'<?xml version="1.0" encoding="UTF-8"?>
<Types xmlns="http://schemas.openxmlformats.org/package/2006/content-types">
<Default Extension="rels" ContentType="application/vnd.openxmlformats-package.relationships+xml"/>
<Default Extension="xml" ContentType="application/xml"/>
<Override PartName="/xl/workbook.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.main+xml"/>
<Override PartName="/xl/worksheets/sheet1.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml"/>
<Override PartName="/xl/sharedStrings.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.sharedStrings+xml"/>
<Override PartName="/docProps/core.xml" ContentType="application/vnd.openxmlformats-package.core-properties+xml"/>
<Override PartName="/docProps/app.xml" ContentType="application/vnd.openxmlformats-officedocument.extended-properties+xml"/>
</Types>'
);
}
function xlsx_get_workbook_rels_xml() {
return sprintf(
'<?xml version="1.0" encoding="UTF-8"?>
<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">
<Relationship Id="rId1" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet" Target="worksheets/sheet1.xml"/>
<Relationship Id="rId4" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/sharedStrings" Target="sharedStrings.xml"/>
</Relationships>'
);
}
function xlsx_get_app_xml() {
return sprintf(
'<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<Properties xmlns="http://schemas.openxmlformats.org/officeDocument/2006/extended-properties" xmlns:vt="http://schemas.openxmlformats.org/officeDocument/2006/docPropsVTypes">
<Application>Microsoft Excel</Application>
</Properties>'
);
}
function xlsx_get_core_xml() {
return sprintf(
'<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<cp:coreProperties xmlns:cp="http://schemas.openxmlformats.org/package/2006/metadata/core-properties" xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:dcterms="http://purl.org/dc/terms/" xmlns:dcmitype="http://purl.org/dc/dcmitype/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<dcterms:created xsi:type="dcterms:W3CDTF">%s</dcterms:created>
<dc:creator>Preseto</dc:creator>
</cp:coreProperties>',
date( 'Y-m-d\TH:i:s.00\Z' )
);
}
function xlsx_get_rels_xml() {
return sprintf(
'<?xml version="1.0" encoding="UTF-8"?>
<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">
<Relationship Id="rId1" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument" Target="xl/workbook.xml"/>
<Relationship Id="rId2" Type="http://schemas.openxmlformats.org/package/2006/relationships/metadata/core-properties" Target="docProps/core.xml"/>
<Relationship Id="rId3" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/extended-properties" Target="docProps/app.xml"/>
</Relationships>'
);
}
}
@mastadelmann

This comment has been minimized.

Copy link

mastadelmann commented Apr 14, 2018

Good stuff! Short and gets the job done. I found a bug when it comes to integers in the input array. You save save integers in the shared strings file instead of directly inserting them. This broke the integer cell as well as cells following it. My fix looks like this:

$field_value = htmlspecialchars($field_value, ENT_XML1 | ENT_QUOTES, 'UTF-8');

if ( is_numeric( $field_value ) ) {
	$field_type = 'n';
} else {
	$field_type = 's';
	$field_value = $this->xlsx_get_shared_string_no( $field_value );
}


$cells[] = sprintf(
	'<c r="%s" t="%s"><v>%d</v></c>',
	$this->xlsx_cell_name( $row_no, $col_no ),
	$field_type,
	$field_value
);

I then ran into issues with floats and had to modify it further. This is what I ended up with:

$field_value = htmlspecialchars($field_value, ENT_XML1 | ENT_QUOTES, 'UTF-8');
$field_type_print = '%d';

if ( is_numeric( $row[$col_no] ) ) {
	$field_type = 'n';
	if ( is_float( $row[$col_no] ) ) {
		$field_type_print = '%.3f';
	}
} else {
	$field_type = 's';
	$field_value = $this->xlsx_get_shared_string_no( $field_value );
}

$cells[] = sprintf(
	'<c r="%s" t="%s"><v>' . $field_type_print . '</v></c>',
	$this->xlsx_cell_name( $row_no, $col_no ),
	$field_type,
	$field_value
);

This works even if the original input array contains floats. I also modified the character escaping for my particular project.

Thanks for releasing this. Didn't find anything nearly as short.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment