Home > Backend Development > PHP Tutorial > PHP实现Excel文档导入导出方法总结

PHP实现Excel文档导入导出方法总结

WBOY
Release: 2016-06-20 13:01:41
Original
1105 people have browsed it

最近因项目需要,需要开发一个模块,用PHP把系统中的一些数据导出成Excel,修改后再导回系统。就趁机对这个研究了一番,下面进行一些总结。

基本上导出的文件分为两种:

1:类Excel格式,这个其实不是传统意义上的Excel文件,只是因为Excel的兼容能力强,能够正确打开而已。修改这种文件后再保存,通常会提示你是否要转换成Excel文件。

优点:简单。

缺点:难以生成格式,如果用来导入需要自己分别编写相应的程序。

2:Excel格式,与类Excel相对应,这种方法生成的文件更接近于真正的Excel格式。

如果导出中文时出现乱码,可以尝试将字符串转换成gb2312,例如下面就把$yourStr从utf-8转换成了gb2312:

$yourStr = mb_convert_encoding(”gb2312″, “UTF-8″, $yourStr);

下面详细列举几种方法。

一、PHP导出Excel

1:第一推荐无比风骚的PHPExcel,官方网站: http://www.codeplex.com/PHPExcel

导入导出都成,可以导出office2007格式,同时兼容2003。

下载下来的包中有文档和例子,大家可以自行研究。

抄段例子出来:

<?php /**  
* PHPExcel  
*  
* Copyright (C) 2006 - 2007 PHPExcel  
*  
* This library is free software; you can redistribute it and/or  
* modify it under the terms of the GNU Lesser General Public  
* License as published by the Free Software Foundation; either  
* version 2.1 of the License, or (at your option) any later version.  
*  
* This library is distributed in the hope that it will be useful,  
* but WITHOUT ANY WARRANTY; without even the implied warranty of  
* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU  
* Lesser General Public License for more details.  
*  
* You should have received a copy of the GNU Lesser General Public  
* License along with this library; if not, write to the Free Software  
* Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301  USA  
*  
* @category   PHPExcel  
* @package    PHPExcel  
* @copyright  Copyright (c) 2006 - 2007 PHPExcel (http://www.codeplex.com/PHPExcel)  
* @license    http://www.gnu.org/licenses/lgpl.txt    LGPL  
* @version    1.5.0, 2007-10-23  
*/
                         
/** Error reporting */
error_reporting(E_ALL);   
                         
/** Include path **/
set_include_path(get_include_path() . PATH_SEPARATOR . ‘../Classes/’);   
                         
/** PHPExcel */
include ‘PHPExcel.php’;   
                         
/** PHPExcel_Writer_Excel2007 */
include ‘PHPExcel/Writer/Excel2007.php’;   
                         
// Create new PHPExcel object   
echo date(’H:i:s’) . ” Create new PHPExcel object\n”;   
$objPHPExcel = new PHPExcel();   
                         
// Set properties   
echo date(’H:i:s’) . ” Set properties\n”;   
$objPHPExcel->getProperties()->setCreator(”Maarten Balliauw”);   
$objPHPExcel->getProperties()->setLastModifiedBy(”Maarten Balliauw”);   
$objPHPExcel->getProperties()->setTitle(”Office 2007 XLSX Test Document”);   
$objPHPExcel->getProperties()->setSubject(”Office 2007 XLSX Test Document”);   
$objPHPExcel->getProperties()->setDescrīption(”Test document for Office 2007 XLSX, generated using PHP classes.”);   
$objPHPExcel->getProperties()->setKeywords(”office 2007 openxml php”);   
$objPHPExcel->getProperties()->setCategory(”Test result file”);   
                         
// Add some data   
echo date(’H:i:s’) . ” Add some data\n”;   
$objPHPExcel->setActiveSheetIndex(0);   
$objPHPExcel->getActiveSheet()->setCellValue(’A1′, ‘Hello’);   
$objPHPExcel->getActiveSheet()->setCellValue(’B2′, ‘world!’);   
$objPHPExcel->getActiveSheet()->setCellValue(’C1′, ‘Hello’);   
$objPHPExcel->getActiveSheet()->setCellValue(’D2′, ‘world!’);   
                         
// Rename sheet   
echo date(’H:i:s’) . ” Rename sheet\n”;   
$objPHPExcel->getActiveSheet()->setTitle(’Simple’);   
                         
// Set active sheet index to the first sheet, so Excel opens this as the first sheet   
$objPHPExcel->setActiveSheetIndex(0);   
                         
// Save Excel 2007 file   
echo date(’H:i:s’) . ” Write to Excel2007 format\n”;   
$objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel);   
$objWriter->save(str_replace(’.php’, ‘.xlsx’, __FILE__));   
                         
// Echo done   
echo date(’H:i:s’) . ” Done writing file.\r\n”;
Copy after login

2、使用pear的Spreadsheet_Excel_Writer类

下载地址: http://pear.php.net/package/Spreadsheet_Excel_Writer

此类依赖于OLE,下载地址:http://pear.php.net/package/OLE

需要注意的是导出的Excel文件格式比较老,修改后保存会提示是否转换成更新的格式。

不过可以设定格式,很强大。

<?php require_once ‘Spreadsheet/Excel/Writer.php’;   
                         
// Creating a workbook   
$workbook = new Spreadsheet_Excel_Writer();   
                         
// sending HTTP headers   
$workbook->send(’test.xls’);   
                         
// Creating a worksheet   
$worksheet =& $workbook->addWorksheet(’My first worksheet’);   
                         
// The actual data   
$worksheet->write(0, 0, ‘Name’);   
$worksheet->write(0, 1, ‘Age’);   
$worksheet->write(1, 0, ‘John Smith’);   
$worksheet->write(1, 1, 30);   
$worksheet->write(2, 0, ‘Johann Schmidt’);   
$worksheet->write(2, 1, 31);   
$worksheet->write(3, 0, ‘Juan Herrera’);   
$worksheet->write(3, 1, 32);   
                         
// Let’s send the file   
$workbook->close();   
?>
Copy after login

3、利用smarty,生成符合Excel规范的XML或HTML文件

支持格式,非常完美的导出方案。不过导出来的的本质上还是XML文件,如果用来导入就需要另外处理了。

 

需要注意的是如果导出的表格行数不确定时,最好在模板中把”ss:ExpandedColumnCount=”5″ ss:ExpandedRowCount=”21″”之类的东西删掉。

 

4、利用pack函数打印出模拟Excel格式的断句符号,这种更接近于Excel标准格式,用office2003修改后保存,还不会弹出提示,推荐用这种方法。

缺点是无格式。

<?php // Send Header   
header(”Pragma: public”);   
header(”Expires: 0″);   
header(”Cache-Control: must-revalidate, post-check=0, pre-check=0″);   
header(”Content-Type: application/force-download”);   
header(”Content-Type: application/octet-stream”);   
header(”Content-Type: application/download”);;   
header(”Content-Disposition: attachment;filename=test.xls “);   
header(”Content-Transfer-Encoding: binary “);   
// XLS Data Cell   
                       
xlsBOF();   
xlsWriteLabel(1,0,”My excel line one”);   
xlsWriteLabel(2,0,”My excel line two : “);   
xlsWriteLabel(2,1,”Hello everybody”);   
                       
xlsEOF();   
                       
function xlsBOF() {   
echo pack(”ssssss”, 0×809, 0×8, 0×0, 0×10, 0×0, 0×0);   
return;   
}   
function xlsEOF() {   
echo pack(”ss”, 0×0A, 0×00);   
return;   
}   
function xlsWriteNumber($Row, $Col, $Value) {   
echo pack(”sssss”, 0×203, 14, $Row, $Col, 0×0);   
echo pack(”d”, $Value);   
return;   
}   
function xlsWriteLabel($Row, $Col, $Value ) {   
$L = strlen($Value);   
echo pack(”ssssss”, 0×204, 8 + $L, $Row, $Col, 0×0, $L);   
echo $Value;   
return;   
}   
?>
Copy after login

不过笔者在64位linux系统中使用时失败了,断句符号全部变成了乱码。

5、使用制表符、换行符的方法   

制表符”\t”用户分割同一行中的列,换行符”\t\n”可以开启下一行。   

<?php header(”Content-Type: application/vnd.ms-execl”);   
header(”Content-Disposition: attachment; filename=myExcel.xls”);   
header(”Pragma: no-cache”);   
header(”Expires: 0″);   
/*first line*/
echo “hello”.”\t”;   
echo “world”.”\t”;   
echo “\t\n”;   
                    
/*start of second line*/
echo “this is second line”.”\t”;   
echo “Hi,pretty girl”.”\t”;   
echo “\t\n”;   
?>
Copy after login

6、使用com

如果你的PHP可以开启com模块,就可以用它来导出Excel文件

<?PHP $filename = “c:/spreadhseet/test.xls”;   
$sheet1 = 1;   
$sheet2 = “sheet2″;   
$excel_app = new COM(”Excel.application”) or Die (”Did not connect”);   
print “Application name: {$excel_app->Application->value}\n” ;   
print “Loaded version: {$excel_app->Application->version}\n”;   
$Workbook = $excel_app->Workbooks->Open(”$filename”) or Die(”Did not open $filename $Workbook”);   
$Worksheet = $Workbook->Worksheets($sheet1);   
$Worksheet->activate;   
$excel_cell = $Worksheet->Range(”C4″);   
$excel_cell->activate;   
$excel_result = $excel_cell->value;   
print “$excel_result\n”;   
$Worksheet = $Workbook->Worksheets($sheet2);   
$Worksheet->activate;   
$excel_cell = $Worksheet->Range(”C4″);   
$excel_cell->activate;   
$excel_result = $excel_cell->value;   
print “$excel_result\n”;   
#To close all instances of excel:   
$Workbook->Close;   
unset($Worksheet);   
unset($Workbook);   
$excel_app->Workbooks->Close();   
$excel_app->Quit();   
unset($excel_app);   
?>
Copy after login

一个更好的例子: http://blog.chinaunix.net/u/16928/showart_387171.html

二、PHP导入Excel

1:还是用PHPExcel,官方网站: http://www.codeplex.com/PHPExcel。

2:使用PHP-ExcelReader,下载地址: http://sourceforge.net/projects/phpexcelreader

举例:

<?php require_once ‘Excel/reader.php’;   
                 
// ExcelFile($filename, $encoding);   
$data = new Spreadsheet_Excel_Reader();   
                 
// Set output Encoding.   
$data->setOutputEncoding(’utf8′);   
                 
$data->read(’ jxlrwtest.xls’);   
                 
error_reporting(E_ALL ^ E_NOTICE);   
                 
for ($i = 1; $i sheets[0]['numRows']; $i++) {   
for ($j = 1; $j sheets[0]['numCols']; $j++) {   
echo “\”".$data->sheets[0]['cells'][$i][$j].”\”,”;   
}   
echo “\n”;   
}   
                 
?>
Copy after login


Related labels:
php
source:php.cn
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template