Php excel file operations based on phpexcel

In my recent work, I have to deal with the problem of multi-language translation. the translation is written in an excel worksheet. For ease of processing, I keep the Chinese and English columns. In this way, you need to extract the data from the excel file, save it in the excel array, and put the corresponding data into the database by using the loop array. Therefore, the first step 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

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

The code is very simple. first, initialize the relevant excel writing class, then write data, and finally save it as an xls file.
The output result is shown in the figure below.