PHP class to export data in CSV, TSV, or Excel XML

Source: Internet
Author: User
Tags php class sprintf

<?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; ',  ' &apos; ',  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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.