PHP generates and reads Excel file _php instance

In the Web site will often generate tables, CSV and Excel are commonly used in the report format, CSV relatively simple, if you have questions I will release some examples of CSV, here mainly introduced in PHP to generate and read Excel files.

To perform the following function, first introduce a class library: Phpexcel,phpexcel is a powerful PHP class library for reading and writing different file formats, such as Excel 2007,pdf format, HTML format, and so on, which is based on Microsoft ' s OpenXML and PHP based on the strong support for Excel, such as setting workbooks, font styles, pictures, and borders, and so on, look at how it reads and writes Excel files:

First, if you build an Excel file:

The function Arraytoexcel function in the following 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 (10);
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 after the output file, you can add the following code, and do not use 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 Exceltoarray function 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);
 
Date_default_timezone_set (' Asia/shanghai '); /** phpexcel_iofactory * * require_once '. 
/classes/phpexcel/iofactory.php '; Check Prerequisites if (!file_exists ("31excel5.xls")) {exit ("not found 31EXCEL5.XLS.N");} $reader = Phpexcel_iofact Ory::createreader (' Excel5 '); Set in EXCEL5 format (excel97-2003 workbook) $PHPExcel = $reader->load ("31excel5.xls"); Load Excel File $sheet = $PHPExcel->getsheet (0); Read first?? Worksheet $highestRow = $sheet->gethighestrow (); Get the total number $highestColumm = $sheet->gethighestcolumn (); Gets the total number of columns/** iterate through the data for each cell/for ($row = 1; $row <= $highestRow; $row + +) {//Line number starts with line 1th for ($column = ' A '; $column &l T;= $highestColumm;
    $column + +) {//The number of columns is starting with column A $dataset [] = $sheet->getcell ($column. $row)->getvalue (); Echo $column. $row. ":" $sheet->getcell ($column. $row)->getvalue ().
  <br/> ";
 }}?>

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