How to read Excel files in PHP

Commonly used PHP to read excel in the following three ways, each has its own advantages and disadvantages. A third method is recommended because it can be used across platforms.

1. Read in. csv format

Converting. xls to a. csv text format, and then parsing the file with PHP is no different than parsing the text in PHP.

Advantages: Cross-platform, high efficiency, can read and write.

Cons: You can only use. csv files directly, if you often accept. xls binaries, you need to convert them manually, not automatically. A file has only one sheet.

PHP has its own analysis. csv function: fgetcsv

Array fgetcsv (int $handle [, int $length [, String $delimiter [, String $enclosure]])

Handle a valid file pointer produced by fopen (), Popen (), or fsockopen ().

Length (optional) must be greater than the longest line in the CVS file. This parameter is optional in PHP 5. If this parameter is omitted (set to 0 in a later version of PHP 5.0.4), then there is no limit to the length, but it may affect execution efficiency.

Delimiter (optional) Sets the field delimiter (one character only), and the default value is a comma.

Enclosure (optional) Sets the field wrapping character (only one character is allowed), and the default value is double quotation marks. This parameter is added in PHP 4.3.0. Similar to Fgets (), only fgetcsv () resolves the read-in rows and finds fields in CSV format and returns an array containing those fields.

Fgetcsv () returns FALSE when an error occurs, including when the file ends.

Note: Empty rows in the CSV file are returned as an array containing a single null field and will not be treated as an error.

Of course, you can parse the string yourself manually.

<?php$row = 1; $handle = fopen ("Test.csv", "R"), while ($data = Fgetcsv ($handle, +, ",")) {$num = count ($data); echo " $num fields $row: \ n "; $row + +; for ($c =0; $c < $num; $c + +) {echo $data [$c]. "\ n"; }}fclose ($handle);? >

You can also use the FPUTCSV function to format a row as CSV and write a file pointer.

2. ODBC Linked data sources

Pros: Supports multiple formats, CVS, XLS, and more. Supports read and write, using the standard SQL language, almost identical to SQL Server, MySQL database.

Disadvantage: The value supports Windows Server

3. PHP Custom Classes

Pros: Cross-platform. Some classes support write operations. Support for. xls binaries

The common classes are phpexcelreader and Phpexcel. The latter supports read and write, but requires php5.2 or more.

The Phpexcelreader is designed to read files. Returns an array that contains all the contents of the table.

The method used by this class can refer to the example.php in compressed files downloaded from the website.

But I downloaded back (version 2009-03-30), there are two points to note:

The following line in the reader.php to be modified

Will require_once ' spreadsheet/excel/reader/oleread.php ';

Instead of require_once ' oleread.inc ';

In example.php

Modified $data->setoutputencoding (' cp1251′);

For $data->setoutputencoding (' cp936′);

Modification of the NL2BR (htmlentities ($data->sheets[$sheet [' cells '] [$row] [$col]);

For $table _output[$sheet]. = NL2BR (Htmlspecialchars ($data->sheets[$sheet [' cells '] [$row] [$col]));

Otherwise the Chinese will have a problem.

Traditional words can be modified to CP950, Japanese is CP932, specifically refer to codepage instructions.

Modify $data->read (' Jxlrwtest.xls ') for your own Excel file name, the zip file attached to the Jxlrwtest.xls should be broken.

Here is the download address:

phpexcelreader:http://sourceforge.net/projects/phpexcelreader/

Phpexcel:http://www.codeplex.com/phpexcel/wiki/view.aspx?title=documents&referringtitle=home

Sometimes write a program when the background requirements to import large amounts of data into the database, such as computer test results of the query, phone book data, etc. are generally stored in Excel, then we can export data into a CSV file, and then through the following procedures can be in the background batch import data into the database.

The following are just the main program sections:

<?php

Defining the Get Time function
function Getmicrotime () {
List ($usec, $sec) = Explode ("", Microtime ());
return (float) $usec + (float) $sec);
}
?>
<?php
$time _start = Getmicrotime ();
Include ("db.inc.php");//Connect to Database
$db =new testcsv;
?>
<?php
$handle = fopen ("Test.csv", "R");
$sql = "INSERT into scores (Idcard,names,num,sex,nation,score) values (";
while ($data = Fgetcsv ($handle, 1000, ",")) {

$num = count ($data);
for ($c =0; $c < $num; $c + +) {
if ($c = = $num-1) {$sql = $sql. $data [$c]. ")"; break;}
$sql = $sql. $data [$c]. ",";
}
print "<br>";
echo $sql. " <br> ";
$db->query ($sql);
echo "SQL statement executed successfully! <br> ";
$sql = "INSERT into scores (Idcard,names,num,sex,nation,score) values (";
}
Fclose ($handle);
$time _end = Getmicrotime ();
$time = $time _end-$time _start;
echo "Program execution Time:". $time. " Seconds ";
?

