Using Excel in PHP based on simple introduction _php tips

In the work need to deal with multilingual translation problems, translation is written in Excel form. I will keep both Chinese and English in order to handle the convenience.

This requires that the data be taken out of Excel, and then stored in an array of Excel, by using a looping array to put the appropriate data into the database.

So the first step in the job 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:

Copy Code code as follows:

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

Copy Code code as follows:

$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:

Copy Code code as follows:

$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.

Copy Code code as follows:

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);

The code is very simple, first initialize the related Excel write class, and then write data, and finally save as XLS file.
The effect of the output see figure