PHP generates and reads Excel files

In the site often generated tables, CSV and Excel are commonly used in the report format, CSV is relatively simple, if you have questions I will publish some examples of CSV, here is the main introduction of PHP to generate and read Excel files.

To execute the following function, first introduce a class library: Phpexcel,phpexcel is a powerful PHP class library to read and write different file formats, such as Excel 2007,pdf format, HTML format, etc., this class library is built in Microsoft's OpenXML and PHP, based on the strong support for Excel, such as setting the workbook, font style, pictures and borders, and so on, see how it reads and writes Excel files:

First, if you generate an Excel file:

The function of Arraytoexcel in this code is to generate an Excel file of data from a two-dimensional array and save it on the server.

require_once ' classes/phpexcel/reader/excel2007.php '; require_once ' Classes/ phpexcel/reader/excel5.php '; include ' classes/phpexcel/iofactory.php '; function Arraytoexcel ($data) {$objPHPExcel = New Phpexcel (); $objPHPExcel->setactivesheetindex (0); $objPHPExcel->getactivesheet ()->settitle (' Firstsheet '), $objPHPExcel->getdefaultstyle ()->getfont ()->setname (' Arial '); $objPHPExcel Getdefaultstyle ()->getfont ()->setsize ()//add data$i = 2;foreach ($data as $line) {$objPHPExcel Getactivesheet ()->setcellvalue (' a '. $i, $line [' from ']; $objPHPExcel->getactivesheet ()->getcell (' a '. $i)- >setdatatype (' n '); $objPHPExcel->getactivesheet ()->setcellvalue (' B ' $i, $line [' to ']); $objPHPExcel Getactivesheet ()->getcell (' B '. $i)->setdatatype (' n '); $i + +;} $objWriter = Phpexcel_iofactory::createwriter ($objPHPExcel, ' Excel5 '); $file = ' Excel.xls '; $objWriter->save ($file );}

If you do not want to save on the server, you want to generate a direct download to the client, you can output the file by adding the following code, instead of using the $objWriter->save ($file);

The code is as follows:

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/vnd.ms-execl"); Header (" Content-type:application/octet-stream "); Header (" Content-type:application/download "); Header (' Content-disposition:attachment;filename= "Excel.xls"); Header ("Content-transfer-encoding:binary"); $objWriter- >save (' php://output ');

Next look at an instance that reads the contents of an Excel file:

The function of Exceltoarray in the following code is to rearrange the contents of an Excel into an array.

The code is as follows:

Require_once ' classes/phpexcel.php '; require_once ' classes/phpexcel/iofactory.php '; function Exceltoarray ($file) {$ objreader = Phpexcel_iofactory::createreader (' Excel5 '); $objReader->setreaddataonly (true); $objPHPExcel = $ Objreader->load ($file); $objWorksheet = $objPHPExcel->getactivesheet (); $highestRow = $objWorksheet Gethighestrow (); $highestColumn = $objWorksheet->gethighestcolumn (); $highestColumnIndex = Phpexcel_cell:: Columnindexfromstring ($highestColumn); $excelData = Array (); for ($row = 2; $row <= $highestRow; + + $row) {for ($col = 0; $col <= $highestColumnIndex; + + $col) {$excelData [$row] = $objWorksheet->getcellbycolumnandrow ($col, $row)->getvalue ();}} return $excelData;}

Streamlined approach

The code is as follows:

<?php/** * * @copyright 2007-2012 Xiaoqiang. * @author Xiaoqiang.wu <jamblues@gmail.com> * @version 1.01 */error_reporting (e_all);d Ate_default_timezone_set (  ' Asia/shanghai '); /** phpexcel_iofactory */require_once '. /classes/phpexcel/iofactory.php '; Check Prerequisitesif (!file_exists ("31excel5.xls")) {exit ("not found 31EXCEL5.XLS.N");} $reader = Phpexcel_iofactory::createreader (' Excel5 '); Set in EXCEL5 format (excel97-2003 workbook) $PHPExcel = $reader->load ("31excel5.xls"); Load Excel File $sheet = $PHPExcel->getsheet (0); Read the first?? Worksheet $highestrow = $sheet->gethighestrow (); Total number of rows $highestcolumm = $sheet->gethighestcolumn ();  Gets the total number of columns/** iterates through each cell's data */for ($row = 1; $row <= $highestRow; $row + +) {//lines start with line 1th for ($column = ' A '; $column <= $highestColumm;    $column + +) {//Number of columns starts with column A $dataset [] = $sheet->getcell ($column. $row)->getvalue (); Echo $column. $row. ":". $sheet->getcell ($column. $row)->getvalue (). "  <br/> "; }}?>

I hope this article is helpful to you, PHP generated and read the contents of Excel file to introduce you here. I hope you will continue to follow our website! Want to learn PHP can continue to focus on this site.

