Php code for reading and writing excel tables _ PHP Tutorial

Php reads and writes the excel table program code. This article describes how to use the phpexcel plug-in to quickly implement the operation code for excel tables. The first step of work is to introduce the data in this article to you, using the phpexcel plug-in to quickly implement the operation code for excel tables. if you need to know more about it, please do not go to the reference.

The first step to work is to extract the data from the excel file. Here I used an open source php processing excel class: phpexcel. detailed information for this project http://phpexcel.codeplex.com /.

I currently use phpexcel1.7.3. after decompression, there is a PHPExcel and PHPExcel. php file.
We mainly use the php file. See the file directory structure.

This version is said to support excel2007, but xlsx edited by 2007 cannot be supported by this library. So I will convert it to 2003. I feel very supportive.

The following describes the specific usage:

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 to initialize the relevant excel class and load the first sheet in excel

The code is as follows:
$ All_column = $ current_sheet-> getHighestColumn (); $ All_row = $ current_sheet-> getHighestRow ();

Obtain the maximum column values (for example, 'g') and the maximum number of rows (for numeric values) of the table)

Here we will read the data in excel in a loop:

The code is as follows:

$ All_arr = array (); $ C_arr = array (); // Character 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 operations in phpexcel. this operation is often used to import the data in the database to excel for easy display and more beautiful results.

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

It is used to export mysql data to an excel file using php. it may be used as a quick material:

Function to_excel ($ SQL, $ excel_name) // contains the SQL statement and exported excel file name.
{
Include_once ('phpexcel/Classes/PHPExcel. php'); // contains class files
Include_once ('phpexcel/Classes/PHPExcel/Writer/excel2007.php'); // contains class files
$ ObjPHPExcel = new PHPExcel (); // Create an excel file class
$ Re = mysql_query ($ SQL); // execute SQL to obtain the dataset
$ I = 1;
While ($ list = mysql_fetch_row ($ re) // loop through a row to obtain data
{
$ ObjPHPExcel-> getActiveSheet ()-> setCellValue ('A '. $ I, $ list [0]); // write $ list [0] to row I in column A 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 ('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 data to the current directory, named after $ excel_name
Echo "OK! Exported as ". $ excel_name;
}
?>

Bytes. The first step to work is to import data...