PHP使用PHPexcel导入导出数据的方法
发布:smiling 来源: PHP粉丝网 添加日期:2021-06-26 19:39:20 浏览: 评论:0
这篇文章主要介绍了PHP使用PHPexcel导入导出数据的方法,以实例形式较为详细的分析了PHP使用PHPexcel实现数据的导入与导出操作相关技巧,需要的朋友可以参考下
本文实例讲述了PHP使用PHPexcel导入导出数据的方法。分享给大家供大家参考,具体如下:
导入数据:
- <?php
- 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,'<br />';
- $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 "<br>";
- $highestColumn = $objWorksheet->getHighestColumn();
- $highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn);//总列数
- echo 'highestColumnIndex='.$highestColumnIndex;
- echo "<br />";
- $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 '<br />';
- }
- ?>
导出数据:
(如果有特殊的字符串 = 麻烦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程序设计有所帮助。
Tags: PHPexcel导入 PHPexcel导出
- 上一篇:PHP实现的一致性哈希算法完整实例
- 下一篇:PHP常用的小程序代码段
相关文章
- ·PHPExcel导入Excel文件并对其日期单元格处理的代码示例(2019-12-25)
- ·PHPExcel导出时列过大问题解决办法(2015-04-13)
推荐文章
热门文章
最新评论文章
- 写给考虑创业的年轻程序员(10)
- PHP新手上路(一)(7)
- 惹恼程序员的十件事(5)
- PHP邮件发送例子,已测试成功(5)
- 致初学者:PHP比ASP优秀的七个理由(4)
- PHP会被淘汰吗?(4)
- PHP新手上路(四)(4)
- 如何去学习PHP?(2)
- 简单入门级php分页代码(2)
- php中邮箱email 电话等格式的验证(2)