Creating a Downloadable Spreadsheet from PHP

January 16, 2009 | PHP, Time Savers | 0 comments

I have written several systems that show data on a page but also provide a CSV download file either through a textarea with formatted data inside to copy and paste or via a download link and creates a file on the fly.

I recently decided that this wasn’t the best way to do it seeing as the people will want to be using Microsoft Excel to view the data. It is possible to write a spreadsheet using PHP and using an example from AppServ I started to do it this way.

The original link is included above but here is my simplified version with smaller more understandable function naming:

Download the example script here: [download#6#size#nohits]

<?php

function xls_BOF() {
	echo pack("ssssss", 0x809, 0x8, 0x0, 0x10, 0x0, 0x0);
}

function xls_EOF() {
	echo pack("ss", 0x0A, 0x00);
}

function xls_write_cell($row, $col, $value='') {
	if (is_numeric($value)) {
		echo pack("sssss", 0x203, 14, $row, $col, 0x0);
		$value = pack("d", $value);
	} else {
		$l = strlen($value);
		echo pack("ssssss", 0x204, 8 + $l, $row, $col, 0x0, $l);
	}

	echo $value;
}

// Send Headers to set the Filename and force a download of the contents of the script
header("Pragma: public");
header("Expires: 0");
header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
header("Content-Type: application/force-download");
header("Content-Type: application/octet-stream");
header("Content-Type: application/download");

 // Put the filename in \"'s if you want it to include spaces
header("Content-Disposition: attachment;filename=" . $filename . ".xls");
header("Content-Transfer-Encoding: binary ");

xlsBOF(); //Start the Spreadsheet

xls_write_cell(0, 1, 'Column 1');
xls_write_cell(0, 2, 'Column 2');
xls_write_cell(0, 3, 'Column 3');

$row = 1;
for ($i=0; $i<2; $i++) {
	xls_write_cell($row, 0, 'Row ' . $row . ' Column ' . $i);
	xls_write_cell($row, 1, 'Row ' . $row . ' Column ' . $i);
	xls_write_cell($row, 2, 'Row ' . $row . ' Column ' . $i);

	$row++;
}

xlsEOF(); //End the Spreadsheet

exit(); //Exit the script after download

?>

A Donate Button!

0 Comments

Submit a Comment

Your email address will not be published. Required fields are marked *

CommentLuv badge

Stay in touch!

Page Builder Cloud

Page Builder Cloud

A truly universal template library for WordPress Page Builders.

Page Builder Recommendation

Elementor Banner

We are BIG fans of the Elementor page builder. Give it a look!

About this site and Sean Barton

Picture of Sean
Sean Barton is a Freelance PHP Website Developer in Crewe, Cheshire. He is a Wordpress and CMS/Framework specialist and Co-Founder of Page Builder Cloud.
This site was set up in 2008 as a tutorial and scripting resource for the PHP language and Wordpress.
Find out more about Sean on the About Me page or use the Hire Me page to get in touch. For more information about Sean's work take a look at the Portfolio

Our Services

  • Wordpress plugin/theme development
  • Divi specialist
  • Ecommerce (Woocommerce, WPSC, Shopify, Magento)
  • PSD to Wordpress theme conversion (Responsive)
  • Website design work (Banners, Logos, Full Site, etc)
  • Website analysis (security, usability, SEO)
  • API Integrations (InfusionSoft, SalesForce, Ontraport, Customer Thermometer, etc..)
  • Wordpress consultancy & expert advice
  • Crisis support
  • Website hosting

The main services offered are Wordpress based although we do a great deal of technical programming for bespoke systems. From troubleshooting, extending frameworks, finding bugs to writing them from scratch.

Find out more by looking through our past projects or get a quote.

Be the first to hear about new products/updates!

This is a mailing list for those people interested in being told when we release a new product (Divi plugin or Theme).

We shall also use this list to let you know about product updates and releases.

You have Successfully Subscribed!