A brief introduction to using excel in PHP_PHP tutorial-PHP Tutorial-php.cn

A brief introduction to using excel in PHP_PHP tutorial

WBOY

Release： 2016-07-21 15:00:06

Original

840 people have browsed it

I need to deal with multi-language translation issues at work, and the translations are written in excel sheets. For the convenience of processing, I will keep the Chinese and English columns.

In this way, you need to take the data out of excel, then save it in the array of excel, and put the corresponding data into the database by using the loop array.

So the first step is to get the data out of excel. Here I use an open source PHP excel class: phpexcel. Details of the project http://phpexcel.codeplex.com/.

I am currently using version phpexcel1.7.3. After decompression, there are a PHPExcel and PHPExcel.php file inside.
We mainly use that PHP file. See the file directory structure in the picture below

This version is said to support excel2007, but the xlsx I edited using 2007 cannot get support from this library. So I converted it to 2003. It feels very supportive.

The specific usage is introduced below:

Copy the code The code is 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)){
 🎜>$php_excel_obj = $php_reader->load($file_name);
$current_sheet =$php_excel_obj->getSheet(0);


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

Copy code

The code is as follows:$all_column =$current_sheet->getHighestColumn ();

$all_row =$current_sheet->getHighestRow();

The above respectively obtains the maximum column value of the table (letter representation such as: 'G'), and the maximum row Number (numeric representation) 

The following will use a loop to read the data in excel into excel:

Copy the code

The code is 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(); value)) $value= $value->__toString();
 $c_arr[$c_i]= $value;
 }
 $c_arr&& $all_arr[] = $c_arr;
}


The following is a brief introduction to the writing operation of phpexcel. This operation is often used to import data from the database into excel for easy display and more beautiful effects. 

Copy code

The code is 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 relevant excel writing class, then write the data, and finally save it as an xls file.
The output effect is shown in the picture