PHP Read and write Excel form program code _php tutorial

This article to the students to introduce the use of Phpexcel plug-ins to quickly implement the operation of the Excel table code, there is a need to understand learning friends do not enter the reference.

The first step to work is to take 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, there is a phpexcel and phpexcel.php file after decompression.
We mainly use that PHP file. See File directory structure

This version is said to support excel2007, but I can't get support for this library with the 2007 edition of Xlsx. So I turned it into 2003. Feeling supportive is very good.

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 relevant Excel class and load Excel first sheet

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

The maximum column values of the table are obtained (the letters are denoted as: ' G '), and the maximum number of rows (numeric representation)

The following will use loops to read the data in Excel in Excel:

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 Phpexcel's write operation, which is often used to import data from a database into Excel for easy presentation and more aesthetic results.

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 an Excel file in PHP, perhaps as a crash material:

function To_excel ($sql, $excel _name)//SQL statement with parameters, exported Excel file name
{
Include_once (' phpexcel/classes/phpexcel.php ');//contains class file
Include_once (' phpexcel/classes/phpexcel/writer/excel2007.php ');//contains class file
$objPHPExcel =new phpexcel ();//Create a new Excel file class
$re =mysql_query ($sql);//Execute SQL to get data set
$i = 1;
while ($list =mysql_fetch_row ($re))//loop row to get data
{
$objPHPExcel->getactivesheet ()->setcellvalue (' A ' $i, $list [0]),//excel, column A, line I, write $list[0]
$objPHPExcel->getactivesheet ()->setcellvalue (' B '. $i, $list [1]);//And so on
$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);//Instantiate an Excel data object as an Excel file object
$objWriter->save ($excel _name. ". Xlsx ");//export and write to the current directory, named according to $excel_name
echo "ok! has been exported as". $excel _name;
}
?>

http://www.bkjia.com/PHPjc/630694.html www.bkjia.com true http://www.bkjia.com/PHPjc/630694.html techarticle This article to the students to introduce the use of Phpexcel plug-ins to quickly implement the operation of the Excel table code, there is a need to understand learning friends do not enter the reference. The first step to work is to get the data ...

