The original author of the PHP Excel import and export method: I was surprised when I saw this article on the iceberg. although we usually use some of the original methods, however, I did not list all of them by the Author. when I was writing an excel file, I used the pear Library and the pack header, similarly, some simple xml replacement methods using smarty and so on have also been used. csv PHP is used to import and export Excel files.
Original Author: podcasts on the iceberg
When I saw this article, I was surprised by the patience of the original author. Although we used some of them at ordinary times, they did not list all of them. when I was writing an excel file, I have used pear libraries and pack headers, and I have also used simple xml replacement by using smarty and so on. I don't need to talk about csv. Haha. (Don't talk about the COM method. this is too readable. I have also written articles such as generating word using wps)
However, I only used one method when I was reading it. I forgot what it was, and I had to go back to the code. This is because the app is a tailism and cannot be remembered.
Address: http://xinsync.xju.edu.cn/index.php/archives/3858
Original article content:
Recently, due to project requirements, a module needs to be developed to export some data in the system to Excel, and then export the data back to the system after modification. We will take the opportunity to make a summary of this study.
Basically, the exported files are divided into two types:
1: Excel-like format. this is not an Excel file in the traditional sense. it is only because of its strong compatibility and ability to open it correctly. After you modify the file and save it, you are usually prompted to convert it into an Excel file.
Advantage: simple.
Disadvantage: it is difficult to generate a format. if it is used for import, you need to write the corresponding program separately.
2: Excel format, which corresponds to Excel-like files. the files generated by this method are closer to the real Excel format.
If garbled characters appear during Chinese export, you can try to convert the string to gb2312,
For example, convert $ yourStr from UTF-8 to gb2312:
$ YourStr = mb_convert_encoding ("gb2312", "UTF-8", $ yourStr );
The following describes several methods.
1. export Excel in PHP
1: The first recommended cool PHPExcel, official website: http://www.codeplex.com/PHPExcel
Both import and export are supported. the office2007 format can be exported and 2003 compatible.
The downloaded package contains documents and examples, which you can study on your own.
Example:
PHP code
getProperties()->setCreator(”Maarten Balliauw”); $objPHPExcel->getProperties()->setLastModifiedBy(”Maarten Balliauw”); $objPHPExcel->getProperties()->setTitle(”Office 2007 XLSX Test Document”); $objPHPExcel->getProperties()->setSubject(”Office 2007 XLSX Test Document”); $objPHPExcel->getProperties()->setDescrīption(”Test document for Office 2007 XLSX, generated using PHP classes.”); $objPHPExcel->getProperties()->setKeywords(”office 2007 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->setActiveSheetIndex(0); // Save Excel 2007 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. use the Spreadsheet_Excel_Writer class of pear.
: Http://pear.php.net/package/Spreadsheet_Excel_Writer
This class depends on OLE,: http://pear.php.net/package/OLE
Note that the exported Excel file format is relatively old. after modification, the system will prompt whether to convert it to the updated format.
However, you can set the format, which is 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, 30); $worksheet->write(2, 0, ‘Johann Schmidt’); $worksheet->write(2, 1, 31); $worksheet->write(3, 0, ‘Juan Herrera’); $worksheet->write(3, 1, 32); // Let’s send the file $workbook->close(); ?>
3: Use smarty to generate XML or HTML files conforming to Excel specifications
Supported formats, perfect export solution. However, the imported XML file is essentially an XML file. if it is used for import, additional processing is required.
For more information, see rardge's post: http://bbs.chinaunix.net/viewthread.php? Tid = 745757
Note that if the number of rows of the exported table is unknown, you are advised to delete "ss: ExpandedColumnCount =" 5 "ss: ExpandedRowCount =" 21 "" in the template.
4. use the pack function to print the sentence symbols in the Excel format.
This method is more similar to the standard Excel format. if you save it after modification with office2003, no prompt is displayed. this method is recommended.
The disadvantage is that there is no format.
However, I failed to use it in a 64-bit linux system, and all the broken sentence symbols became garbled characters.
5. use tabs and line breaks
The tab character "\ t" is used to separate columns in the same row. The line break "\ t \ n" can be used to open the next row.
6. use com
If your PHP can enable the 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(”Did not open $filename $Workbook”); $Worksheet = $Workbook->Worksheets($sheet1); $Worksheet->activate; $excel_cell = $Worksheet->Range(”C4″); $excel_cell->activate; $excel_result = $excel_cell->value; print “$excel_result\n”; $Worksheet = $Workbook->Worksheets($sheet2); $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
I. import Excel files in PHP
1: PHPExcel is still used. official website: http://www.codeplex.com/phpexcel.
2: use PHP-ExcelReader,: 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”; } ?>
