How to use PHPExcel to parse key points _ PHP Tutorial

Use PHPExcel to parse key points. In website development, we often use a general program used to prepare a PHP file to import excel to the mysql database. after the basic process is completed, we don't care about it. Last month, the Office of Academic Affairs teacher told us to use it frequently in website development.

I used to prepare a general program for importing excel files into the mysql database in PHP. after that, I did not care about it. Last month, a teacher from the academic affairs office asked me to help create a "national rank examination registration system". I also needed to import and export the excel information of the students, and zip the photos to import and export them.

Although the program was just completed last week, it was relatively simple to process it in a language like java, but PHP added this function only in versions above 5.2 (the new PHP_zip extension is required ). After all, I have been familiar with some of the most basic usage for a month, and I have encountered a problem that is easy to come up with. the introduction on the Internet is relatively simple, I want to introduce the basic usage and possible basic problems.

First, you need to open the PHP_zip extension and find your PHP. ini file, open the extension, find the PHP_zip.dll file in the ext folder in the PHP folder, and copy it to the system32 system folder (depending on your configuration ).

The most common problem on the internet is that this extension is not enabled when PHPExcel is used. The

Next I just want to talk about some notes and errors.

1. you can use PHPExcel to create two types of excel document objects.

One is to directly create

Include 'phpexcel/Writer/excel2007.php ';
$ ObjPHPExcel = new PHPExcel ();

The other method is to read and create data through the reader class.

Require 'phpexcel/Reader/excel2007.php ';
$ ObjReader = new PHPExcel_Reader_Excel2007;
$ ObjPHPExcel = $ objReader-> load ("mytest.xlsx ");

Here I want to explain that the excel file excel2007.PHP is created in xlsx, that is, opened in ms office2007. if you want to create a previous xls file (earlier than office2007 ), you need to include excel5.PHP instead of excel2007, whether it is a reader class or a writer class. For example, the two usage methods mentioned above should be directly created:

Include 'phpexcel/Writer/excel5.php ';
$ ObjPHPExcel = new PHPExcel ();

Read and create through reader class

Require 'phpexcel/Reader/excel5.php ';
$ ObjReader = new PHPExcel_Reader_Excel5;
$ ObjPHPExcel = $ objReader-> load ("mytest.xlsx ");

2. after reading the documents in PHPExcel, I may ask, I just want to read the value of a cell in excel. how can I not read it? I have met you at the beginning. you can do this:

$ Sheet = $ objPHPExcel-> getActiveSheet ();
$ String = $ sheet-> getCell ('F2')-> getValue ();

Of course, this can also be used normally. if your excel is a formula, you should

$ Sheet = $ objPHPExcel-> getActiveSheet ();
$ String = $ sheet-> getCell ('F2')-> getCalculatedValue ();

3. use PHPExcel to convert long numbers into scientific notation.

And the last few digits are ignored as 0. This problem has plagued me for a long time, that is, numbers such as ID card and student ID. if you setValue directly, the excel files are automatically converted into scientific notation. there is less such information on the Internet, and I find that most of them are still incorrect. I found a document which was implemented by modifying the PHPexcel source code.

Writer/Excel5 file, 202nd rows,

 
 

  
  
If ($ cell-> hasHyperlink ()){
  
  
$ Worksheet-> writeUrl ($ row, $ column,
Str_replace ('sheet: // ', 'internal :',
$ Cell-> getHyperlink ()-> getUrl ()),
$ Cell-> getValue (), $ formats [$ styleHash]);
  
  
}
  
  
Else {
  
  
$ Worksheet-> write ($ row, $ column, $ cell->
GetValue (), $ formats [$ styleHash], $ style->
GetNumberFormat ()-> getFormatCode ());
  
  
}
  
  
Change to if ($ cell-> hasHyperlink ()){
  
  
$ Worksheet-> writeUrl ($ row, $ column,
Str_replace ('sheet: // ', 'internal :',
$ Cell-> getHyperlink ()-> getUrl (), $ cell->
GetValue (), $ formats [$ styleHash]);
  
  
} Else if ($ cell-> getDataType () =
PHPExcel_Cell_DataType: TYPE_STRING ){
  
  
$ Worksheet-> writeString ($ row, $ column, $ cell->
GetValue (), $ formats [$ styleHash]);
  
  
}
  
  
Else {
  
  
$ Worksheet-> write ($ row, $ column, $ cell->
GetValue (), $ formats [$ styleHash], $ style->
GetNumberFormat ()-> getFormatCode ());}
 
 

Then, you can write data in the text format when writing data to excel (it is also a scientific and technical method to write the source code in the text format without modifying the source code)

$ ObjPHPExcel-> getActiveSheet ()-> setCellValueExplicit ($ letters_arr [$ j + 1]. ($ I + 1), $ this-> student_info [$ I] [$ j], PHPExcel_Cell_DataType: TYPE_STRING );
$ ObjPHPExcel-> getActiveSheet ()-> getStyle ($ letters_arr [$ j + 1]. ($ I + 1)-> getNumberFormat ()-> setFormatCode ("@");

4. how can I obtain the number of columns and number of rows in excel using PHPExcel?

This is often the case when PHPexcel is used, because it is not provided in the test sample program. The following are my questions and answers on codeplex. After reading this article, you will be able to understand it ).

5. how to use PHPExcel to obtain the value of each cell through a loop.

Your print_r output of the object ObjPHPExcel will see a lot of complicated things. In fact, it will be very easy to use the built-in method. I used this method.

$ Letters_arr = array (1 => 'A', 2 => 'B', 3 => 'C', 4 => 'D', 5 => 'e ', 6 => 'F', 7 => 'G', 8 => 'h', 9 => 'I', 10 => 'J ', 11 => 'K', 12 => 'L', 13 => 'm', 14 => 'N', 15 => 'O ', 16 => 'P', 17 => 'Q', 18 => 'R', 19 => 's', 20 => 'T ', 21 => 'u', 22 => 'V', 23 => 'W', 24 => 'X', 25 => 'y ', 26 => 'Z ');

By setting an array of characters, you can use the cyclic variable to loop the number of columns. Here I made A joke. I used to convert the character to the ASC code for loop, after I flipped through the PHP book, I realized that the conversion of PHP characters into an integer is not the same as that of C ++. The Foundation is not solid.

6. use PHPExcel for database and excel encoding.

This also needs everyone's attention, excel is the use of UTF-8 encoding, so every time you read data from the database, should not forget to convert once.
$ This-> student_info [$ I] [$ j] = iconv ("gbk", "UTF-8", $ this-> student_info [$ I] [$ j]); but remember, the problem is not so simple. when you use PHPExcel to read data from excel, you may find that the columns with Chinese characters are not read and empty.

Print it out with print_r. the cell is also empty. this is not a conversion encoding problem, because if it is encoding, garbled characters should be printed, however, PHPexcel does not read the Chinese characters in that column. I am not very clear about this problem. I open the reader's excel5.PHP file and change $ this-> _ defaultEncoding = 'isoxxxx' to $ this-> _ defaultEncoding = 'utf-8 '; then, the garbled characters can be read, and then the code can be converted. I have not answered my questions on the official website. if you have any better methods, please tell me. thank you.

I used to prepare a general program for importing excel files into the mysql database using PHP. after that, I did not care about it. Last month, the Office of Academic Affairs teacher called...