Generate the most basic Excel XLSX and XML files in PHP, see https://kaspars.net/blog/wordpress/excel-xlsx-xml-php

php-excel-xlsx-xml.php

<?php

/**
 * UPDATE: This has been converted into a proper PHP library,
 * see https://github.com/kasparsd/mini-sheets-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>'
		);

	}

}

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.

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.

you make my day thanks 👍

This is great, thank you @mastadelmann! I'll probably convert this into a proper PHP package so that it can be installed via Composer and also accept pull requests for fixes like you just did.

@mastadelmann Here is the new library https://github.com/kasparsd/mini-sheets-php -- feel free to open a pull request with the suggested changes!

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.

@kasparsd Thanks a tonnes for the library and @oteroweb for this