PHP Import Export Excel Step

Source: Internet
Author: User
Tags ole
PHP Import Export Excel method
Original Author: A podcast on the iceberg
See this article, is very surprised the original author's patience, although we have some in peacetime, but not the author listed the whole, when writing Excel, I used Pear Library, also used the pack pressure pack head, also those who use smarty and other simple substitution of XML also used, The CSV doesn't have to be talked about. Oh. (COM does not speak, this can be read too much, I also wrote the use of WPS, such as word generation and other articles)
But in the reading, only used one, specifically what forget, to go back to the code. Because of the use of take doctrine, can't remember.
Original address: http://xinsync.xju.edu.cn/index.php/archives/3858
Original content:

Recently, due to project needs, a module needs to be developed to export some of the data in the system to excel, and then back to the system after modification. Take the opportunity to study this, some of the following summary.
Basically the exported files are divided into two types:
1: Excel format, this is not in the traditional sense of Excel file, just because Excel compatibility ability, can be opened correctly. After you modify this file and then save it, you will usually be prompted whether you want to convert to an Excel file.
Pros: Simple.
Disadvantage: It is difficult to generate a format, if you need to write the appropriate program for the import.
The 2:excel format, which corresponds to class Excel, produces files that are closer to the real Excel format.

If garbled when exporting Chinese, you can try to convert the string to gb2312,
For example, the following converts $yourstr from Utf-8 to gb2312:
$YOURSTR = mb_convert_encoding ("Gb2312″," Utf-8″, $YOURSTR);

Several methods are listed below.

First, PHP export Excel
1: First recommendation Matchless Phpexcel, official website: http://www.codeplex.com/PHPExcel
Import and export, you can export office2007 format, and compatible with 2003.
The downloaded package has documentation and examples that you can study on your own.
To copy the paragraph example out:


PHP code
 GetProperties ()->setcreator ("Maarten Balliauw");   $objPHPExcel->getproperties ()->setlastmodifiedby ("Maarten Balliauw");   $objPHPExcel->getproperties ()->settitle ("Office" XLSX Test Document ");   $objPHPExcel->getproperties ()->setsubject ("Office" XLSX Test Document ");   $objPHPExcel->getproperties ()->setdescrīption ("Test document for Office-XLSX, generated using PHP classes.");   $objPHPExcel->getproperties ()->setkeywords ("Office openxml PHP");      $objPHPExcel->getproperties ()->setcategory ("Test result file"); ADD Some data echo date (' H:i:s ').   "Add some data\n";   $objPHPExcel->setactivesheetindex (0);   $objPHPExcel->getactivesheet ()->setcellvalue (' a1′, ' Hello ');   $objPHPExcel->getactivesheet ()->setcellvalue (' b2′, ' world! ');   $objPHPExcel->getactivesheet ()->setcellvalue (' c1′, ' Hello ');      $objPHPExcel->getactivesheet ()->setcellvalue (' d2′, ' world! '); Rename sheet echo date (' H:i:s ').   "Rename sheet\n";      $objPHPExcel->getactivesheet ()->settitle (' simple '); Set Active sheet Index to the first sheet, so Excel opens this as the first sheet $objPHPExcel->setactivesheetinde      X (0); Save Excel file echo date (' H:i:s ').   "Write to Excel2007 format\n";   $objWriter = new phpexcel_writer_excel2007 ($objPHPExcel);      $objWriter->save (Str_replace ('. php ', '. xlsx ', __file__)); echo Done echo date (' H:i:s ').  "Done writing file.\r\n";




2, the use of Pear Spreadsheet_excel_writer class
Download Address: Http://pear.php.net/package/Spreadsheet_Excel_Writer
This class relies on OLE, download address: Http://pear.php.net/package/OLE
It is important to note that the exported Excel file format is older, and the modified save will prompt you to convert to an updated format.
But you can set the format, very powerful.

 
  Send (' Test.xls ');      Creating a worksheet   $worksheet =& $workbook->addworksheet (' My first worksheet ');      The actual data   $worksheet->write (0, 0, ' Name ');   $worksheet->write (0, 1, ' age ');   $worksheet->write (1, 0, ' John Smith ');   $worksheet->write (1, 1,);   $worksheet->write (2, 0, ' Johann Schmidt ');   $worksheet->write (2, 1, to);   $worksheet->write (3, 0, ' Juan Herrera ');   $worksheet->write (3, 1, +);      Let ' s send the file   $workbook->close ();   


3: Generate an XML or HTML file that conforms to the Excel specification using Smarty
Support format, very perfect export scheme. However, the nature of the derivative is the XML file, if used to import it needs to be processed separately.
For more information, please see Rardge's post: http://bbs.chinaunix.net/viewthread.php?tid=745757

It is important to note that if the number of table rows exported is uncertain, it is best to delete "ss:expandedcolumncount=" 5″ss:expandedrowcount= "21″" in the template.

4. Use the pack function to print out the segmentation symbols in Excel format

This is closer to the standard format of Excel, with office2003 modified after the save, not pop-up hints, recommended in this way.
The disadvantage is no format.

   

However, the author in the 64-bit Linux system used in the failure, the segmentation symbol has all become garbled.

5, the use of tabs, line break method
The tab "\ T" user splits the column in the same row, and the line break "\t\n" opens the next line.
 
    



6. Using COM
If your PHP can open a COM module, you can use it to export Excel files
 Application->value}\n ";   Print "Loaded version: {$excel _app->application->version}\n";   $Workbook = $excel _app->workbooks->open ("$filename") or Die ("Do not Open $filename $Workbook");   $Worksheet = $Workbook->worksheets ($sheet 1);   $Worksheet->activate;   $excel _cell = $Worksheet->range ("C4″");   $excel _cell->activate;   $excel _result = $excel _cell->value;   Print "$excel _result\n";   $Worksheet = $Workbook->worksheets ($sheet 2);   $Worksheet->activate;   $excel _cell = $Worksheet->range ("C4″");   $excel _cell->activate;   $excel _result = $excel _cell->value;   Print "$excel _result\n";   #To Close all instances of Excel: $Workbook->close;   Unset ($Worksheet);   Unset ($Workbook);   $excel _app->workbooks->close ();   $excel _app->quit ();   unset ($excel _app); ?>

A better example: http://blog.chinaunix.net/u/16928/showart_387171.html

First, PHP import Excel

1: Still use Phpexcel, official website: http://www.codeplex.com/PHPExcel.

2: Use Php-excelreader, download address: Http://sourceforge.net/projects/phpexcelreader
Example:


 
  Setoutputencoding (' utf8′);      $data->read (' Jxlrwtest.xls ');      Error_reporting (e_all ^ e_notice);      for ($i = 1; $i <= $data->sheets[0][' numrows '); $i + +) {for   ($j = 1; $j <= $data->sheets[0][' numcols '); $j + +) {   echo "\" ". $data->sheets[0][' cells ' [$i] [$j]." \ ",";   }   echo "\ n";   }      ? >   

  • Related Article

    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.