PHP operates excel files based on phpexcel_PHP tutorial-PHP Tutorial-php.cn

PHP operates excel files based on phpexcel_PHP tutorial

WBOY

Release： 2016-07-21 15:36:47

Original

978 people have browsed it

So the first step in the work 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
PHP operates excel files based on phpexcel_PHP tutorial

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)) 
{ 
echo 'NO Excel!'; 
} 
} 
$php_excel_obj = $php_reader->load($file_name); 
$current_sheet =$php_excel_obj->getSheet(0); 

The main function above is initialization Relevant excel class and load the first excel sheet

$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 number of rows (numeric representation)

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

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

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 write 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 of all 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
PHP operates excel files based on phpexcel_PHP tutorial

PHP operates excel files based on phpexcel_PHP tutorial