封装excel导出方法
封装读取excel内容方法
/** * 获取Excel内容 * @param type $filename * @return type */ public function getExcelContent($filename) { //读取内容 $objPHPExcel = \PHPExcel_IOFactory::load($filename); $objPHPExcel->setActiveSheetIndex(0); $sheet1 = $objPHPExcel->getSheet(0); //获取行数,并把数据读取出来$data数组 $rowCount = $sheet1->getHighestRow(); //excel行数 $data = array(); $this->column = 100; //最大列数 for ($i = 1; $i <= $rowCount; $i++) { for ($j = 0; $j < $this->column; $j++) { $value = (string)$sheet1->getCellByColumnAndRow($j, $i)->getValue(); if ($i == 1 && empty($value)) { $this->column = $j; //当第一列遇到空的时候替换最大列数 } else { $data[$i - 1][$j] = $value; //获取每一列的值 } } } return $data; }
封装excel导出方法
<?php /* * To change this license header, choose License Headers in Project Properties. * To change this template file, choose Tools | Templates * and open the template in the editor. */ namespace app\common\model; use think\Model; use think\Db; /** * Description of OrderSourceController * * @author zhangjx <zhangjx882@sina.com> * @date 2017-9-8 */ class Excel extends Model { /** * Excel列数getExcelContent()专用 */ protected $column; private function checkExcel($fileName, $fileAddress, $loadFileAddress){ if (empty($fileName)) return false; set_time_limit(0); ini_set('memory_limit', '512M'); // 检测文件夹如果不存在就生成 Directory($fileAddress); vendor('excel.PHPExcel'); // $cacheMethod = \PHPExcel_CachedObjectStorageFactory::cache_in_memory; // $cacheSettings = array('memoryCacheSize'=>'50MB'); // \PHPExcel_Settings::setCacheStorageMethod($cacheMethod, $cacheSettings); if ($loadFileAddress == null) { $objPHPExcel = new \PHPExcel(); } else { $objReader = \PHPExcel_IOFactory::createReader("Excel2007"); $objReader->setIncludeCharts(TRUE); $objPHPExcel = $objReader->load($loadFileAddress); } return $objPHPExcel; } /** * 导出excel 公共方法 若表头一致可采用 * @param array $xlsCellName excel表列名 * @param array $xlsCellValue excel表列值 * @param string $fileName 文件名称 * @param string $fileAddress 文件保存地址 * @param integer $beginNum 开始写数据的行数 * @param null $loadFileAddress 是否读取其他文件作为模板配置 * @return string */ public function exportExcel($xlsCellName, $xlsCellValue, $fileName, $fileAddress, $beginNum = 1, $loadFileAddress = null) { $objPHPExcel = $this->checkExcel($fileName, $fileAddress, $loadFileAddress); // 合并数据 $xlsData = array_merge(array($xlsCellName), $xlsCellValue); foreach ($xlsData as $num => $data) { $row = $num + $beginNum; $chr = 0; foreach ($data as $val) { $col = self::IntToChr($chr); $chr++; $objPHPExcel->getActiveSheet(0)->setCellValue($col . $row, $val); } } header('pragma:public'); header('Content-type:application/vnd.ms-excel;charset=utf-8;name="' . $fileName . '.xls"'); header("Content-Disposition:attachment;filename=$fileName.xls"); //attachment新窗口打印inline本窗口打印 $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5'); // $objWriter->save('php://output'); $objWriter->save($fileAddress . $fileName . '.xls'); return $_SERVER['REQUEST_SCHEME'] . "://" . $_SERVER['HTTP_HOST'] . ltrim($fileAddress, '.') . $fileName . '.xls'; //增加后缀 } /** * 数字转字母 (类似于Excel列标) * @param integer $index 索引值 * @param integer $start 字母起始值 * @return String 返回字母 */ public static function IntToChr($index, $start = 65) { $str = ''; if (floor($index / 26) > 0) { $str .= self::IntToChr(floor($index / 26) - 1); } return $str . chr($index % 26 + $start); }
实际业务中,会有多处列表需要用到导出和导入功能,如果不是特殊表头情况下可以考虑采用统一的方法,需要使用的地方直接调用
这样可以节省很多代码,而且使用起来会很方便