php数据导入导出,PHP使用PHPexcel导入导出数据的方法

╰+攻爆jí腚メ 2022-11-19 04:04 268阅读 0赞

本文实例讲述了PHP使用PHPexcel导入导出数据的方法。分享给大家供大家参考，具体如下：

导入数据：

error\_reporting(E\_ALL); //开启错误

set\_time\_limit(0); //脚本不超时

date\_default\_timezone\_set('Europe/London'); //设置时间

/\*\* Include path \*\*/

set\_include\_path(get\_include\_path() . PATH\_SEPARATOR . 'https://www.jb51.net/../Classes/');//设置环境变量

/\*\* PHPExcel\_IOFactory \*/

include 'PHPExcel/IOFactory.php';

//$inputFileType = 'Excel5'; //这个是读 xls的

$inputFileType = 'Excel2007';//这个是计xlsx的

//$inputFileName = './sampleData/example2.xls';

$inputFileName = './sampleData/book.xlsx';

echo 'Loading file ',pathinfo($inputFileName,PATHINFO\_BASENAME),' using IOFactory with a defined reader type of ',$inputFileType,'  
';

$objReader = PHPExcel\_IOFactory::createReader($inputFileType);

$objPHPExcel = $objReader->load($inputFileName);

/\*

$sheet = $objPHPExcel->getSheet(0);

$highestRow = $sheet->getHighestRow(); //取得总行数

$highestColumn = $sheet->getHighestColumn(); //取得总列

\*/

$objWorksheet = $objPHPExcel->getActiveSheet();//取得总行数

$highestRow = $objWorksheet->getHighestRow();//取得总列数

echo 'highestRow='.$highestRow;

echo "  
";

$highestColumn = $objWorksheet->getHighestColumn();

$highestColumnIndex = PHPExcel\_Cell::columnIndexFromString($highestColumn);//总列数

echo 'highestColumnIndex='.$highestColumnIndex;

echo "  
";

$headtitle=array();

for ($row = 1;$row <= $highestRow;$row++)

$strs=array();

//注意highestColumnIndex的列数索引从0开始

for ($col = 0;$col < $highestColumnIndex;$col++)

$strs\[$col\] =$objWorksheet->getCellByColumnAndRow($col, $row)->getValue();

$info = array(

'word1'=>"$strs\[0\]",

'word2'=>"$strs\[1\]",

'word3'=>"$strs\[2\]",

'word4'=>"$strs\[3\]",

);

//在这儿，你可以连接，你的数据库，写入数据库了

print\_r($info);

echo '  
';

导出数据：

(如果有特殊的字符串 = 麻烦　　str\_replace(array('='),'',$val\['roleName'\]);)

private function \_export\_data($data = array())

error\_reporting(E\_ALL); //开启错误

set\_time\_limit(0); //脚本不超时

date\_default\_timezone\_set('Europe/London'); //设置时间

/\*\* Include path \*\*/

set\_include\_path(FCPATH.APPPATH.'/libraries/Classes/');//设置环境变量

// Create new PHPExcel object

Include 'PHPExcel.php';

$objPHPExcel = new PHPExcel();

// Set document properties

$objPHPExcel->getProperties()->setCreator("Maarten Balliauw")

\->setLastModifiedBy("Maarten Balliauw")

\->setTitle("Office 2007 XLSX Test Document")

\->setSubject("Office 2007 XLSX Test Document")

\->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.")

\->setKeywords("office 2007 openxml php")

\->setCategory("Test result file");

// Add some data

$letter = array('A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z');

if($data)\{

$i = 1;

foreach ($data as $key => $value) \{

$newobj = $objPHPExcel->setActiveSheetIndex(0);

$j = 0;

foreach ($value as $k => $val) \{

$index = $letter\[$j\]."$i";

$objPHPExcel->setActiveSheetIndex(0)->setCellValue($index, $val);

$j++;

$i++;

$date = date('Y-m-d',time());

// Rename worksheet

$objPHPExcel->getActiveSheet()->setTitle($date);

$objPHPExcel->setActiveSheetIndex(0);

// Redirect output to a client's web browser (Excel2007)

header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');

header('Content-Disposition: attachment;filename="'.$date.'.xlsx"');

header('Cache-Control: max-age=0');

$objWriter = PHPExcel\_IOFactory::createWriter($objPHPExcel, 'Excel2007');

$objWriter->save('php://output');

exit;

直接上代码：

public function export\_data($data = array())

\# code...

include\_once(APP\_PATH.'Tools/PHPExcel/Classes/PHPExcel/Writer/IWriter.php') ;

include\_once(APP\_PATH.'Tools/PHPExcel/Classes/PHPExcel/Writer/Excel5.php') ;

include\_once(APP\_PATH.'Tools/PHPExcel/Classes/PHPExcel.php') ;

include\_once(APP\_PATH.'Tools/PHPExcel/Classes/PHPExcel/IOFactory.php') ;

$obj\_phpexcel = new PHPExcel();

$obj\_phpexcel->getActiveSheet()->setCellValue('a1','Key');

$obj\_phpexcel->getActiveSheet()->setCellValue('b1','Value');

if($data)\{

$i =2;

foreach ($data as $key => $value) \{

\# code...

$obj\_phpexcel->getActiveSheet()->setCellValue('a'.$i,$value);

$i++;

$obj\_Writer = PHPExcel\_IOFactory::createWriter($obj\_phpexcel,'Excel5');

$filename = "outexcel.xls";

header("Content-Type: application/force-download");

header("Content-Type: application/octet-stream");

header("Content-Type: application/download");

header('Content-Disposition:inline;filename="'.$filename.'"');

header("Content-Transfer-Encoding: binary");

header("Last-Modified: " . gmdate("D, d M Y H:i:s") . " GMT");

header("Cache-Control: must-revalidate, post-check=0, pre-check=0");

header("Pragma: no-cache");

$obj\_Writer->save('php://output');

希望本文所述对大家php程序设计有所帮助。

php数据导入导出,PHP使用PHPexcel导入导出数据的方法

发表评论取消回复

还没有评论，来说两句吧...

相关阅读

相关使用PHP导入导出Excel实例

相关 php excel导出导入,PHPExcel实现导入导出功能

相关 php数据导入导出,PHP使用PHPexcel导入导出数据的方法

相关 phpexcel的导入导出源码

相关 php导出导入 mysql数据库_PHP 使用文件方式导入导出整个MYSQL数据库的实现方法...

相关 ThinkPHP+PHPExcel[导入][导出]实现方法

相关 Java 数据导入导出

相关 phpexcel导入导出

相关 ThinkPHP5.1使用PHPExcel进行数据的导入导出

相关 phpexcel 导入导出