<?php// example of exporting a large amount of data to a file. on my // computer it takes 43 seconds to write out 83mb of data, but // only uses 750kb of memory.set _time_limit (0); ini_set (' Memory_limit ', '-1 ');require ". /php-export-data.class.php "; function genrandomstring ($length = 100) { $characters = "0123456789abcdefghijklmnopqrstuvwxyz _"; $string = ""; for ($p = 0; $p < $length; $p +) { $string .= $characters [Mt_rand (0, strlen ($characters)-1)]; } return $string;} $excel = new exportdataexcel (' file '); $excel->filename = "Test_big_excel.xls "; $excel->initialize (); for ($i = 1; $i <100000; $i + +) {$row = array ($i, genrandomstring (), genrandomstring (), genrandomstring (), Genrandomstring (), genrandomstring ()); $excel->addrow ($row);} $excel->finalize ();p rint "memory used: " . number_format (Memory_get_peak_usage ()); <?php// php-export-data by eli dickinson, http://github.com/elidickinson/ php-export-data/** * exportdata is the base class for exporters To specific file formats. see other * classes below. */abstract class ExportData {protected $exportTo; // set in constructor to one of ' browser ', ' file ', ' string ' protected $stringData; // Stringdata so far, used if export string modeprotected $tempFile; // handle to temp file (For export file mode) protected $tempFilename; // temp file name and path (for export file mode) public $filename; // file mode: the output file Name; browser mode: file name for download; string mode: not usedpublic function __construct ($exportTo = "browser", $filename = " ExportData ") {if (!in_array ($exportTo, array (' Browser ', ' file ', ' string ') ) {throw new exception ("$exportTo is not a valid exportdata export type");} $this->exportto = $exportTo; $this->filename = $filename;} Public function initialize () {switch ($this->exportto) {case ' browser ': $this Sendhttpheaders ();break;case ' string ': $this->stringdata = ';break;case ' file ': $this->tempfilename = tempnam (Sys_get_temp_dir (), ' ExportData '); $this->tempfile = fopen ($this->tempfilename, "w"); $this->write ($this->generateheader ());} Public function addrow ($row) {$this->write ($this->generaterow ($row));} Public function finalize () {$this->write ($this->generatefooter ()); switch ($this- Exportto) {case ' browser ': Flush ();break;case ' string ':// do nothingbreak;case ' File ':// close temp file and move it to correct locationfclose ($ This->tempfile) Rename ($this->tempfilename, $this->filename); Public function getstring () {return $this->stringdata;} Abstract public function sendhttpheaders ();p rotected function write ($data) { Switch ($this->exportto) {case ' browser ':echo $data; BReak;case ' string ': $this->stringdata .= $data;break;case ' file ': fwrite ($this- tempfile, $data); break;}} Protected function generateheader () {// can be overridden by subclass to return any data that goes at the top of the Exported file}protected function generatefooter () {// can be overridden by subclass to return any data that goes at the bottom of the exported file}// in subclasses generaterow will take $row array and return string of it formatted for export typeabstract protected function generaterow ($row);} /** * exportdatatsv - exports to tsv (Tab separated value) Format. */class exportdatatsv&nbSp;extends exportdata {function generaterow ($row) {foreach ($row as $key => $value) {// escape inner quotes and wrap all contents in new quotes.// note that we are using \ " to escape double quote not "" $row [$key] = ' "'. str_replace ('" ', ' \ "', $ Value) . ' "';} Return implode ("\ t", $row) . "\ n";} Function sendhttpheaders () {header ("Content-type: text/tab-separated-values"); header ("Content-disposition: attachment; filename=". BaseName ($this->filename));}} /** * exportdatacsv - exports to csv (Comma separated value) format. */class exportdatacsv extends exportdata {function generaterow ($ Row) {foreach ($row as $key => $value) {// Escape inner quotes and wrap all contents in new Quotes.// note that we are using \ " to escape double quote not "" $row [$key] = ' "'. str_replace ('" ', ' \ "', $value) . '";} Return implode (",", $row) . "\ n";} Function sendhttpheaders () {header ("Content-type: text/csv"); Header ("content-disposition: Attachment; filename= ". basename ($this->filename);}} /** * exportdataexcel exports data into an xml format ( SpreadsheetML) that can be * read by ms excel 2003 and newer as well as openoffice * * creates a workbook with a single worksheet (title specified by * $title). * * note that using . xml is the "correct" file extension for these files, but it * generally isn ' t associated with excel. using . xls is tempting, but excel 2007 will * throw a scary WARNING&NBSP;THAT&NBSP;THE&NBSP;EXTENSION&NBSP;DOESN ' t match the file type. * * Based on Excel XML code from Excel_XML (http://github.com/ Oliverschwarz/php-excel) * by Oliver Schwarz */class ExportDataExcel extends exportdata {const xmlheader = "<?xml version=\" 1.0\ " encoding=\" %s\ "? \>\n<workbook xmlns=\" urn:schemas-microsoft-com:office:spreadsheet\ " 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\ ">";const xmlfooter = "</Workbook>";p ublic $encoding = ' UTF-8 '; // encoding type to specify in file. // note That you ' re on your own for making sure your data is actually encoded to this encodingpublic $title = ' Sheet1 '; // Title for worksheet function generateheader () {// workbook header$output = stripslashes (sprintf (self::xmlheader, $this->encoding)) . "\ n";// set up styles$output .= "<styles>\n", $output .= "<style ss:id=\" sdt\ " ><numberformat ss:format=\ "short date\"/></style>\n ", $output .= " </ Styles>\n ";// worksheet header$output .= sprintf (" <worksheet ss:name=\ "%s\" > \n <taBle>\n ", htmlentities ($this->title));return $output;} Function generatefooter () {$output = ';// worksheet footer$output .= " </table>\n</worksheet>\n";// workbook footer$output .= self::XmlFooter;return $output;} Function generaterow ($row) {$output = '; $output .= <row>\n ";foreach ($row as $k => $v) {$output .= $this->generatecell ($v);} $output .= " </row>\n";return $output;} Private function generatecell ($item) {$output = "; $style = ";// tell Excel to treat as a number. Note that Excel only Stores roughly 15 digits, so keep // as text if&nBsp;number is longer than that.if (Preg_match ("/^-?\d+ (?: [.,]\d+) $/", $item) & & (strlen ($item) < 15)) {$type = ' number ';} sniff for valid dates; should look something like 2010-07-14 or 7/14/2010 etc. can// also have an optional time after the date.//// note we want to be very strict in what we consider a date. there is the possibility// of really screwing up the data if we try to reformat a string that was not actually // intended to represent a Date.elseif (Preg_match ("/^ (\d{1,2}|\d{4}) [\/\-]\d{1,2}[\/\-] (\d{1,2}|\d{4}) ([^\d].+)? $/", $item) & & ($timestamp = strtotime ($item)) && ($timestamp > 0) && ($timestamp < strtotime (' +500 years ')) {$type = ' DateTime '; $item = strftime ("%y-%m-%dt%h:%m:%s", $timestamp); $style = ' SDT '; // defined in header; tells excel to format date for display}else {$type = ' String ';} $item = str_replace (' & #039; ', ' ' ', htmlspecialchars ($item, ent_quotes)); $output .= " "; $output . = $style ? "<cell ss:styleid=\" $style \ ">" : "<Cell>"; $output .= sprintf ("<data ss:type=\"%s\ ">%s</Data>", $type, $item); $output .= "</cell>\n";return $output;} Function sendhttpheaders () {header ("content-type: application/vnd.ms-excel; charset=" . $this->encoding); Header ("ContenT-disposition: inline; filename=\ "" . basename ($this->filename) . "\" ");}}
PHP class to export data in CSV, TSV, or Excel XML