1、先下载 /PHPOffice/PHPExcel
2、将SDK解压之后的Class文件拷贝到自己的项目。
一、数据导入
/*** 数据导入* @param string $file excel文件* @param string $sheet* @return string 返回解析数据* @throws PHPExcel_Exception* @throws PHPExcel_Reader_Exception*/function importExecl($file='', $sheet=0){$file = iconv("utf-8", "gb2312", $file); //转码if(empty($file) OR !file_exists($file)) {die('file not exists!');}include(dirname(__FILE__) . '/includes/Classes/PHPExcel.php'); //引入PHP EXCEL类$objRead = new PHPExcel_Reader_Excel(); //建立reader对象if(!$objRead->canRead($file)){$objRead = new PHPExcel_Reader_Excel5();if(!$objRead->canRead($file)){die('No Excel!');}}$cellName = 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', 'AA', 'AB', 'AC', 'AD', 'AE', 'AF', 'AG', 'AH', 'AI', 'AJ', 'AK', 'AL', 'AM', 'AN', 'AO', 'AP', 'AQ', 'AR', 'AS', 'AT', 'AU', 'AV', 'AW', 'AX', 'AY', 'AZ');$obj = $objRead->load($file); //建立excel对象$currSheet = $obj->getSheet($sheet); //获取指定的sheet表$columnH = $currSheet->getHighestColumn(); //取得最大的列号$columnCnt = array_search($columnH, $cellName);$rowCnt = $currSheet->getHighestRow(); //获取总行数$data = array();for($_row=1; $_row<=$rowCnt; $_row++){//读取内容for($_column=0; $_column<=$columnCnt; $_column++){$cellId = $cellName[$_column].$_row;$cellValue = $currSheet->getCell($cellId)->getValue();//$cellValue = $currSheet->getCell($cellId)->getCalculatedValue(); #获取公式计算的值if($cellValue instanceof PHPExcel_RichText){//富文本转换字符串$cellValue = $cellValue->__toString();}$data[$_row][$cellName[$_column]] = $cellValue;}}array_shift($data);return $data;}
↑↑↑↑↑↑这是我用的方法
以下是另外两种方法
1、
1. function excelToArray(){2. require_once dirname(__FILE__) . '/Lib/Classes/PHPExcel/IOFactory.php'; 4. //加载excel文件 5. $filename = dirname(__FILE__).'/result.xlsx'; 6. $objPHPExcelReader = PHPExcel_IOFactory::load($filename); 8. $sheet = $objPHPExcelReader->getSheet(0); // 读取第一个工作表(编号从 0 开始) 9. $highestRow = $sheet->getHighestRow(); // 取得总行数 10. $highestColumn = $sheet->getHighestColumn();// 取得总列数 12. $arr = 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'); 13. // 一次读取一列 14. $res_arr = array(); 15. for ($row = 2; $row <= $highestRow; $row++) {16. $row_arr = array(); 17. for ($column = 0; $arr[$column] != 'F'; $column++) {18. $val = $sheet->getCellByColumnAndRow($column, $row)->getValue(); 19. $row_arr[] = $val; 20. } 22. $res_arr[] = $row_arr; 23. } 25. return $res_arr; 26. }
2、
1. function excelToArray(){2. require_once dirname(__FILE__) . '/Lib/Classes/PHPExcel/IOFactory.php'; 4. //加载excel文件 5. $filename = dirname(__FILE__).'/result.xlsx'; 6. $objPHPExcelReader = PHPExcel_IOFactory::load($filename); 8. $reader = $objPHPExcelReader->getWorksheetIterator(); 9. //循环读取sheet 10. foreach($reader as $sheet) {11. //读取表内容 12. $content = $sheet->getRowIterator(); 13. //逐行处理 14. $res_arr = array(); 15. foreach($content as $key => $items) {17. $rows = $items->getRowIndex(); //行 18. $columns = $items->getCellIterator(); //列 19. $row_arr = array(); 20. //确定从哪一行开始读取 21. if($rows < 2){22. continue; 23. } 24. //逐列读取 25. foreach($columns as $head => $cell) {26. //获取cell中数据 27. $data = $cell->getValue(); 28. $row_arr[] = $data; 29. } 30. $res_arr[] = $row_arr; 31. } 33. } 35. return $res_arr; 36. }
二、Excel文件导出
1. /** 2. * 创建(导出)Excel数据表格 3. * @param array $list 要导出的数组格式的数据 4. * @param string $filename 导出的Excel表格数据表的文件名 5. * @param array $indexKey $list数组中与Excel表格表头$header中每个项目对应的字段的名字(key值) 6. * @param array $startRow 第一条数据在Excel表格中起始行 7. * @param [bool] $excel 是否生成Excel(.xlsx)以上兼容的数据表 8. * 比如: $indexKey与$list数组对应关系如下: 9. *$indexKey = array('id','username','sex','age'); 10. *$list = array(array('id'=>1,'username'=>'YQJ','sex'=>'男','age'=>24)); 11. */ 12. function exportExcel($list,$filename,$indexKey,$startRow=1,$excel=false){13. //文件引入 14. require_once APP_ROOT.'/Api/excel/PHPExcel.php'; 15. require_once APP_ROOT.'/Api/excel/PHPExcel/Writer/Excel.php'; 17. if(empty($filename)) $filename = time(); 18. if( !is_array($indexKey)) return false; 20. $header_arr = 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'); 21. //初始化PHPExcel() 22. $objPHPExcel = new PHPExcel(); 24. //设置保存版本格式 25. if($excel){26. $objWriter = new PHPExcel_Writer_Excel($objPHPExcel); 27. $filename = $filename.'.xlsx'; 28. }else{29. $objWriter = new PHPExcel_Writer_Excel5($objPHPExcel); 30. $filename = $filename.'.xls'; 31. } 33. //接下来就是写数据到表格里面去 34. $objActSheet = $objPHPExcel->getActiveSheet(); 35. //$startRow = 1; 36. foreach ($list as $row) {37. foreach ($indexKey as $key => $value){38. //这里是设置单元格的内容 39. $objActSheet->setCellValue($header_arr[$key].$startRow,$row[$value]); 40. } 41. $startRow++; 42. } 44. // 下载这个表格，在浏览器输出 45. header("Pragma: public"); 46. header("Expires: 0"); 47. header("Cache-Control:must-revalidate, post-check=0, pre-check=0"); 48. header("Content-Type:application/force-download"); 49. header("Content-Type:application/vnd.ms-execl"); 50. header("Content-Type:application/octet-stream"); 51. header("Content-Type:application/download");; 52. header('Content-Disposition:attachment;filename='.$filename.''); 53. header("Content-Transfer-Encoding:binary"); 54. $objWriter->save('php://output'); 55. }
导出文件，还可以设置模板
1. function exportExcel($list,$filename,$indexKey=array()){2. require_once dirname(__FILE__) . '/Lib/Classes/PHPExcel/IOFactory.php'; 3. require_once dirname(__FILE__) . '/Lib/Classes/PHPExcel.php'; 4. require_once dirname(__FILE__) . '/Lib/Classes/PHPExcel/Writer/Excel.php'; 6. $header_arr = 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'); 8. //$objPHPExcel = new PHPExcel(); //初始化PHPExcel(),不使用模板 9. $template = dirname(__FILE__).'/template.xls';//使用模板 10. $objPHPExcel = PHPExcel_IOFactory::load($template);//加载excel文件,设置模板 12. $objWriter = new PHPExcel_Writer_Excel5($objPHPExcel); //设置保存版本格式 14. //接下来就是写数据到表格里面去 15. $objActSheet = $objPHPExcel->getActiveSheet(); 16. $objActSheet->setCellValue('A2', "活动名称：江南极客"); 17. $objActSheet->setCellValue('C2', "导出时间：".date('Y-m-d H:i:s')); 18. $i = 4; 19. foreach ($list as $row) {20. foreach ($indexKey as $key => $value){21. //这里是设置单元格的内容 22. $objActSheet->setCellValue($header_arr[$key].$i,$row[$value]); 23. } 24. $i++; 25. } 27. // 1.保存至本地Excel表格 28. //$objWriter->save($filename.'.xls'); 30. // 2.接下来当然是下载这个表格了，在浏览器输出就好了 31. header("Pragma: public"); 32. header("Expires: 0"); 33. header("Cache-Control:must-revalidate, post-check=0, pre-check=0"); 34. header("Content-Type:application/force-download"); 35. header("Content-Type:application/vnd.ms-execl"); 36. header("Content-Type:application/octet-stream"); 37. header("Content-Type:application/download");; 38. header('Content-Disposition:attachment;filename="'.$filename.'.xls"'); 39. header("Content-Transfer-Encoding:binary"); 40. $objWriter->save('php://output'); 41. }
先弄一个excel模板
然后使用该模板导出数据
转自：/p/f7c07763a16e
