PHP read and write excel table program code_PHP tutorial

This article introduces to students how to use the phpexcel plug-in to quickly implement the operation code for excel tables. Friends who need to know more about it are welcome to refer to it.

The first step is to extract the data from 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 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:

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

require_once('./phpexcel1.7.3/PHPExcel.php');

$php_excel_obj = new PHPExcel();

代码如下	复制代码
$all_column =$current_sheet->getHighestColumn(); $all_row =$current_sheet->getHighestRow();

$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!';

代码如下

复制代码

$all_arr = array(); $c_arr = array(); //字符对照表 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; }

} } $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 the first excel sheet

The code is as follows	Copy code
$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:

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 write operation of phpexcel. This operation is often used to import data from the database into excel for easy display and more beautiful effects.

$objPHPExcel->getActiveSheet()->setCellValue('F'.$i,$list[5]); $objPHPExcel->getActiveSheet()->setCellValue('G'.$i,$list[6]);

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', '字符串内容'); $act_sheet_obj->setCellValue('A2', 26); $file_name = "output.xls"; $objWriter->save($file_name);

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 using php, maybe it can be used as quick material:



function to_excel($sql,$excel_name)//The parameters are SQL statements and the exported excel file name
{
include_once('PHPExcel/Classes/PHPExcel.php');//Include class files
include_once('PHPExcel/Classes/PHPExcel/Writer/Excel2007.php');//Include class files
$objPHPExcel=new PHPExcel();//Create a new excel file class
$re=mysql_query($sql);//Execute SQL to get the data set
$i=1;

while($list=mysql_fetch_row($re))//Loop to get data row by row

{

$objPHPExcel->getActiveSheet()->setCellValue('A'.$i,$list[0]);//Write $list[0]

in column A and row i of excel $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('H'.$i,$list[7]); $objPHPExcel->getActiveSheet()->setCellValue('I'.$i,$list[8]);

$i++;

} $objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel);//Instantiate the excel data object into an excel file object $objWriter->save($excel_name.".xlsx");//Export and write to the current directory, named according to $excel_name echo "OK! Exported as ".$excel_name; } ?>

http://www.bkjia.com/PHPjc/630694.htmlwww.bkjia.comtruehttp: //www.bkjia.com/PHPjc/630694.htmlTechArticleThis article introduces to students how to use the phpexcel plug-in to quickly implement the operation code for excel tables. Friends who need to know more about it Do not prevent entering the reference. The first step in the work is to convert the data...