PHP Read and write Excel form program code

The first step in working is to get the data out of Excel.  Here I use an open source PHP processing Excel class: Phpexcel. The details of the project are http://phpexcel.codeplex.com/.

I am currently using the phpexcel1.7.3 version, after decompression, there is a phpexcel and phpexcel.php files.
We mainly use that PHP file. See below figure file directory structure

This version is said to be able to support excel2007, but I use the 2007 edit xlsx is unable to get the library support. So I converted it to 2003. It feels good to be supportive.

Here are some specific uses:

The code is as follows	Copy Code
Require_once ('./phpexcel1.7.3/phpexcel.php '); $php _excel_obj = new Phpexcel (); $php _reader = newphpexcel_reader_excel2007 (); if (! $php _reader->canread ($file _name)) { $php _reader= new Phpexcel_reader_excel5 (); if (! $php _reader->canread ($file _name)) { Echo ' NO excel! '; } } $php _excel_obj = $php _reader->load ($file _name); $current _sheet = $php _excel_obj->getsheet (0);

The main function above is to initialize the related Excel class and load the Excel first sheet

The code is as follows	Copy Code
$all _column = $current _sheet->gethighestcolumn (); $all _row = $current _sheet->gethighestrow ();

The above table is given the maximum column value (the letter is like: ' G '), and the maximum number of rows (numeric representation)

Here's how to read the data in Excel using a loop:

The code is as follows

Copy Code

$all _arr = Array (); $c _arr = Array (); Character comparison table for ($r _i = 1; $r _i<= $all _row; $r _i++) { $c _arr= Array (); for ($c _i= ' A '; $c _i<= ' B '; $c _i++) { $adr = $c _i. $r _i; $value = $current _sheet->getcell ($ADR)->getvalue (); if ($c _i== ' A ' && Empty ($value)) break; if (Is_object ($value)) $value = $value->__tostring (); $c _arr[$c _i]= $value; } $c _arr&& $all _arr[] = $c _arr; }

The following is a brief introduction to the Phpexcel write operation, which is often used to import data from the database into Excel, which is easy to show and make more beautiful effects.

The code is as follows

Copy Code

Require_once ('./phpexcel1.7.3/phpexcel.php '); $excel _obj = new Phpexcel (); $objWriter = Newphpexcel_writer_excel5 ($excel _obj); $excel _obj->setactivesheetindex (0); $act _sheet_obj= $excel _obj->getactivesheet (); $act _sheet_obj->settitle (' sheet '); $act _sheet_obj->setcellvalue (' A1 ', ' string content '); $act _sheet_obj->setcellvalue (' A2 ', 26); $file _name = "Output.xls"; $objWriter->save ($file _name);

Used to export MySQL data to Excel files in PHP, perhaps as a crash material:

<?php
function To_excel ($sql, $excel _name)//contains parameters for SQL statements, exported Excel file names
{
Include_once (' phpexcel/classes/phpexcel.php ');//Include class file
Include_once (' phpexcel/classes/phpexcel/writer/excel2007.php ');//Include class file
$objPHPExcel =new Phpexcel ()//Create a new Excel file class
$re =mysql_query ($sql);//Execute SQL Get DataSet
$i = 1;
while ($list =mysql_fetch_row ($re))/loop fetch data in one line
{
$objPHPExcel->getactivesheet ()->setcellvalue (' A '. $i, $list [0]);//excel column A, line I, is written to $list[0]
$objPHPExcel->getactivesheet ()->setcellvalue (' B '. $i, $list [1]);
$objPHPExcel->getactivesheet ()->setcellvalue (' C '. $i, $list [2]);
$objPHPExcel->getactivesheet ()->setcellvalue (' D '. $i, $list [3]);
$objPHPExcel->getactivesheet ()->setcellvalue (' E '. $i, $list [4]);
$objPHPExcel->getactivesheet ()->setcellvalue (' F '. $i, $list [5]);
$objPHPExcel->getactivesheet ()->setcellvalue (' G '. $i, $list [6]);
$objPHPExcel->getactivesheet ()->setcellvalue (' H '. $i, $list [7]);
$objPHPExcel->getactivesheet ()->setcellvalue (' I ' $i, $list [8]);
$i + +;
}
$objWriter = new phpexcel_writer_excel2007 ($objPHPExcel);//To instantiate an Excel data object as an Excel file object
$objWriter->save ($excel _name). Xlsx ");//export and write to current directory, named according to $excel_name
echo "ok! has been exported as". $excel _name;
}
?>