How to read excel files in php

There are three commonly used methods to read EXCEL with PHP, each with its own advantages and disadvantages. I personally recommend the third method because it can be used across platforms.

1. Read in .csv format

Convert .xls to .csv text format, and then use PHP to analyze the file, which is no different from PHP analyzing text.

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

Disadvantages: You can only use .csv files directly. If you often accept .xls binary files, you need to convert them manually and cannot be automated. There is only one SHEET for a file.

PHP has its own analysis .csv function: fgetcsv

array fgetcsv ( int $handle [, int $length [, string $delimiter [, string $enclosure]]] )

handle is composed of fopen(), popen () or a valid file pointer produced by 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 PHP 5.0.4 and later versions), there is no limit to the length, but execution efficiency may be affected.

delimiter (optional) Set the field delimiter (only one character allowed), the default value is comma.

enclosure (optional) Set the field wrapping character (only one character allowed), the default value is double quotes. This parameter was added in PHP 4.3.0. Similar to fgets(), except fgetcsv() parses the read line and finds the fields in CSV format and returns an array containing those fields.

fgetcsv() returns FALSE when an error occurs, including when the end of the file is encountered.

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

Of course, you can also manually analyze the string yourself.



You can also use the fputcsv function to format the rows into CSV and write the file pointer.

2. ODBC linked data source

Advantages: Supports multiple formats, cvs, xls, etc. Supports reading and writing, uses standard SQL language, and is almost identical to SQLSERVER and MYSQL databases.

Disadvantages: Supports Windows servers

3. PHP custom classes

Advantages: Cross-platform. Some classes support write operations. Supports .xls binary files

Commonly used classes include phpExcelReader and PHPExcel. The latter supports reading and writing, but requires php5.2 or above.

phpExcelReader is specially used to read files. Returns an array containing all contents of the table.

For the method used by this class, you can refer to example.php in the compressed file downloaded from the website.

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

reader.php The following line needs to be modified

Change require_once 'Spreadsheet/Excel/Reader/OLERead.php';

Change to require_once 'oleread.inc';

example.php in

Modify $data->setOutputEncoding('CP1251′);

Change to $data->setOutputEncoding('CP936′);

Modify nl2br(htmlentities($data->sheets[$sheet]['cells'][$row][$col]));

is $table_output[$sheet] .= nl2br(htmlspecialchars($data-> sheets[$sheet]['cells'][$row][$col]));

Otherwise there will be problems with Chinese.

For traditional Chinese, it can be modified to CP950, and for Japanese, it is CP932. For details, please refer to the codepage instructions.

Modify $data->read(’jxlrwtest.xls’) to your own excel file name. The jxlrwtest.xls attached in the zip file should be broken.

This is the download address:

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

PHPExcel: http://www.codeplex.com/PHPExcel/Wiki/View.aspx?title=Documents&referringTitle=Home

Sometimes when writing a program, the background requires a large amount of data to be imported into the database. For example, computer test score inquiries, phone book data, etc. are generally stored in excel. At this time, we can export the data into a csv file, and then use the following The program can batch import data into the database in the background.

　The following is just the main program part:


//Define the get time function
function getmicrotime(){
list($usec, $sec) = explode(" ",microtime());
return ((float)$usec + (float)$sec);
}
?>
$time_start = getmicrotime();
include("db.inc.php");//Connect to the database
$db=new testcsv;
?>
$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 "
"; 
echo $sql."
"; 
$db->query($sql); 
echo "sql语句执行成功！
"; 
$sql="insert into scores(idcard,names,num,sex,nation,score) values("; 
} 
fclose ($handle); 
$time_end = getmicrotime(); 
$time = $time_end - $time_start; 
echo "程序执行时间：".$time."秒"; 
?