本文实例讲述了 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 . 'http://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 程序设计有所帮助
来源: https://www.php1.cn/detail/php-fee83af62c.html