Analysis of key points using PHPExcel_PHP tutorial-PHP Tutorial-php.cn

We often use it in website development

I used to prepare a general PHP program to import excel into mysql database. After it was basically completed, I left it alone. Last month, a teacher from the Academic Affairs Office asked me to help create a "National Grade Examination Registration Inquiry System", which also required importing and exporting students' Excel information and zipping photos.

I just completed this program last week. Although it is relatively simple to process it in a language like java, adding this function to PHP can only be completed in version 5.2 or above (requires the new PHP_zip extension). After all, after a month of contact, I have learned a little bit about some of the most basic usages, and I have also encountered problems that are easy to encounter when you first start. The introduction to this online is relatively brief. I would like to give a detailed introduction to its basic usage and possible encounters. to the basic questions.

First, you need to open the PHP_zip extension, find your working PHP.INI file, open this extension, find the PHP_zip.dll file in the ext folder in the PHP folder, and copy it to system32 In the system folder (specifically depends on your configuration method).

The most common problem on the Internet is that this extension is not turned on when using PHPExcel. Errors such as

Now I just want to talk about some things that need attention and are easy to make mistakes.

1. There are 2 types of excel document objects created using PHPExcel.

One is to directly create

include 'PHPExcel/Writer/Excel2007.PHP';
$objPHPExcel = new PHPExcel();

The other one The first is to read and create through the reader class

require 'PHPExcel/Reader/Excel2007.PHP';
$objReader = new PHPExcel_Reader_Excel2007;
$objPHPExcel = $objReader->load("mytest .xlsx");

What I want to explain here is that this contains excel2007.PHP, which is the created xlsx type excel, which is opened by ms office2007. If you want to create the previous xls (pre-office2007 version ), you need to include excel5.PHP instead of excel2007, whether it is the reader class or the writer class, this should be noted. For example, the two usages mentioned above should be created directly:

include 'PHPExcel/Writer/Excel5.PHP';
$objPHPExcel = new PHPExcel();

through the reader class Read create

require 'PHPExcel/Reader/Excel5.PHP';
$objReader = new PHPExcel_Reader_Excel5;
$objPHPExcel = $objReader->load("mytest.xlsx");

2. After reading the document provided using PHPExcel, you may ask, I just want to read the value of a cell in excel, why is it not given how to read it? I also encountered this at the beginning. Been here, you can do this:

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

Of course, this can be used under normal circumstances. If your excel is a formula, you should

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

3. Use PHPExcel to solve the problem of long numbers being converted into scientific notation.

And the last few digits are ignored as 0. This problem has troubled me for a long time. It is numbers such as ID card and student number. If you setValue directly, the excel output will be automatically converted into scientific There is not much information on counting methods on the Internet, and I found that most of them are wrong. I found a document that was implemented by changing the PHPexcel source code

Writer/Excel5 file, line 202,

<ol class="dp-xml">
<li class="alt"><span><span>if ($cell-</span><span class="tag">></span><span>hasHyperlink()) {  </span></span></li>
<li>
<span>$worksheet-</span><span class="tag">></span><span>writeUrl($row, $column,<br> str_replace('sheet://', 'internal:', <br>$cell-</span><span class="tag">></span><span>getHyperlink()-</span><span class="tag">></span><span>getUrl()), <br>$cell-</span><span class="tag">></span><span>getValue(), $formats[$styleHash]);  </span>
</li>
<li class="alt"><span>}  </span></li>
<li><span>else {  </span></li>
<li class="alt">
<span>$worksheet-</span><span class="tag">></span><span>write($row, $column, $cell-</span><span class="tag">><br></span><span>getValue(), $formats[$styleHash],$style-</span><span class="tag">><br></span><span>getNumberFormat()-</span><span class="tag">></span><span>getFormatCode());  </span>
</li>
<li><span>}  </span></li>
<li class="alt">
<span>改为if ($cell-</span><span class="tag">></span><span>hasHyperlink()) {  </span>
</li>
<li>
<span>$worksheet-</span><span class="tag">></span><span>writeUrl($row, $column, <br>str_replace('sheet://', 'internal:', <br>$cell-</span><span class="tag">></span><span>getHyperlink()-</span><span class="tag">></span><span>getUrl()), $cell-</span><span class="tag">><br></span><span>getValue(), $formats[$styleHash]);  </span>
</li>
<li class="alt">
<span>}else if($cell-</span><span class="tag">></span><span>getDataType() == <br></span>PHP<span>Excel_Cell_DataType::TYPE_STRING ) {  </span>
</li>
<li>
<span>$worksheet-</span><span class="tag">></span><span>writeString($row,$column,$cell-</span><span class="tag">><br></span><span>getValue(),$formats[$styleHash]);  </span>
</li>
<li class="alt"><span>}  </span></li>
<li><span>else {  </span></li>
<li class="alt">
<span>$worksheet-</span><span class="tag">></span><span>write($row,$column,$cell-</span><span class="tag">><br></span><span>getValue(),$formats[$styleHash],$style-</span><span class="tag">><br></span><span>getNumberFormat()-</span><span class="tag">></span><span>getFormatCode());} </span>
</li>
</ol>

Copy after login

and then writing it in text format when writing to excel That’s it (writing in text format without modifying the source code is also a scientific and technical method)

$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 to get the number of columns and rows in excel using PHPExcel?

This is also the case when you first start using PHPexcel and find it difficult to use, because its test sample program does not provide this. The following are questions and answers I asked on codeplex. Everyone will know it after reading it (picture at the end of the post).

5. How to get the value of each cell through loop using PHPExcel.

You will see a lot of complicated things in the print_r output of the ObjPHPExcel object. In fact, it is very simple through the built-in method. I use 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 loop variable to loop through the number of columns. I also made a little joke here. In the past, I actually wanted to convert A characters into ASC codes for looping, but failed. I looked through the PHP book and found out that converting characters into integers in PHP is not the same as C and C++. The basics are really different. So solid.

6. Problems with database and excel coding using PHPExcel.

This also needs your attention. Excel uses UTF-8 encoding, so every time you read data from the database, you should not forget to convert it.
$this->student_info[$i][$j]=iconv("gbk","UTF-8",$this->student_info[$i][$j]);But remember, The problem is not that simple. When you use PHPExcel to read data from excel, you may find that columns with Chinese characters are not read out and are empty.

Use print_r to print it out, and you will see that the cell is also empty. This is not a conversion encoding problem, because if it is encoding, garbled characters should be printed, but this is because PHPexcel did not read the Chinese characters in that column. I don't quite understand this problem. I opened the excel5.PHP file in the reader and changed $this->_defaultEncoding = 'isoXXXXX'; to $this->_defaultEncoding = 'UTF-8'; and it solved the problem. It can be read. If the code is garbled, it can be solved by converting the encoding later. I asked on the official website to no avail. If you have a better method, you can tell me, thank you.