phpexcel导出
<?php header("Content-type:text/html;charset=utf-8"); require('./excel/PHPExcel.class.php'); class ExcelExport { private $obj; public function __construct() { $this->obj = new PHPExcel(); } /** * EXcel导出，包含标题、时间范围、表头、 数据、总计（本方法可适用于表头多项合并，对数据超过26列进行了处理。） * @param String $fileName 导出的excel文件名 * @param Array $headArr 表头数组 如： $headArr = ['序号','日期', '收入明细(元)'=>['预定金','安放费','服务费']] * @param String $timeRange 时间范围 * @param Array $tmpFileds 用于计算数据的总计的 * @param String $startRow 从excel第几行开始导出，默认第 1 行 * @param String $startCol 从excel第几列开始导出，默认从 A 列 */ public function index($fileName, $headArr, $data, $timeRange = "", $tmpFileds = array(), $startRow = 1, $startCol = 'A') { // ini_set("memory_limit",-1); //对数据进行检验 if (empty($data) || !is_array($data)) { die("没有符合条件数据"); } //检查文件名 if (empty($fileName)) { exit('请输入文件名'); } //创建PHPExcel对象，注意，不能少了\ $objPHPExcel = $this->obj; $lieNum = $this->_headArrLength($headArr); //dump($lieNum);die; $lastCellAscii = $this->_CloumnToAscii($startCol) + $lieNum - 1; $lastCell = $this->_AsciiToCloumn($lastCellAscii); //标题 $startRow = $this->getExcelTitle($fileName, $lastCell, $startRow, $startCol, $objPHPExcel); // dump($startRow);die; //时间范围 if (!empty($timeRange)) { $startRow = $this->getExcelTimeRange($timeRange, $lastCell, $startRow, $startCol, $objPHPExcel); } //表头 $startRow = $this->getExcelHeader($headArr, $startRow, $startCol, $objPHPExcel); //表格数据 $this->getExcelData($data, $startRow, $startCol, $tmpFileds, $objPHPExcel); $date = date("Y_m_d", time()); $fileName .= "_{$date}.xls"; $fileName = iconv("utf-8", "gb2312", $fileName); //重命名表 //设置活动单指数到第一个表,所以Excel打开这是第一个表 $objPHPExcel->setActiveSheetIndex(0); ob_end_clean(); //清除缓冲区,避免乱码 header('Content-Type: application/vnd.ms-excel'); header("Content-Disposition: attachment;filename=" . $fileName); header('Cache-Control: max-age=0'); $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5'); $objWriter->save('php://output'); //文件通过浏览器下载 exit; } public function getExcelTitle($fileName, $lastCell, $startRow, $startCol, $objPHPExcel) { //标题 $objPHPExcel->getActiveSheet()->mergeCells($startCol . $startRow . ':' . $lastCell . $startRow); $objPHPExcel->setActiveSheetIndex(0)->setCellValue($startCol . $startRow, $fileName); $objPHPExcel->getActiveSheet()->getStyle($startCol . $startRow)->applyFromArray(array('font' => array('bold' => true), 'alignment' => array('horizontal' => 'center'))); return $startRow + 1; } public function getExcelTimeRange($timeRange, $lastCell, $startRow, $startCol, $objPHPExcel) { //时间范围 $objPHPExcel->getActiveSheet()->mergeCells($startCol . $startRow . ':' . $lastCell . $startRow); $objPHPExcel->setActiveSheetIndex(0)->setCellValue($startCol . $startRow, $timeRange); $objPHPExcel->getActiveSheet()->getStyle($startCol . $startRow)->applyFromArray(array('alignment' => array('horizontal' => 'right'))); return $startRow + 1; } public function getExcelData($data, $startRow, $startCol, $tmpFileds, $objPHPExcel) { $objActSheet = $objPHPExcel->getActiveSheet(); $total = array(); //设置为文本格式 foreach ($data as $key => $rows) { //行写入 $ascii = $this->_CloumnToAscii($startCol); foreach ($rows as $keyName => $value) {// 列写入 $column = $this->_AsciiToCloumn($ascii); //TODO float double if (is_int($value)) { $objActSheet->setCellValueExplicit($column . $startRow, $value, 'n'); } elseif (is_float($value)) { $objActSheet->setCellValueExplicit($column . $startRow, sprintf("%.2f", $value), 'n'); } else { $objActSheet->setCellValueExplicit($column . $startRow, $value, 's'); } //是否含有总计 if (!empty($tmpFileds) && in_array($keyName, $tmpFileds)) { $total[$column][$keyName] += $value; } $ascii++; } $startRow++; } //总计 if (!empty($tmpFileds) && !empty($total)) { $objPHPExcel->setActiveSheetIndex(0)->setCellValue($startCol . $startRow, "总计"); foreach ($total as $lie => $totalVal) { foreach ($totalVal as $k => $v) { $objPHPExcel->setActiveSheetIndex(0)->setCellValue($lie . $startRow, $v); } } } } //将Ascii值转换为列名 public function _AsciiToCloumn($accsi) { if ($accsi > ord("Z")) { $diff = $accsi - ord('Z'); $sj_1 = ord('A') + intval($diff / 26); $sj_2 = ord('A') + $diff % 26; $currentCloumn = chr($sj_1) . chr($sj_2); //超过26个字母时才会启用 } else { $currentCloumn = chr($accsi); } return $currentCloumn; } //将列名转换为Ascii值 public function _CloumnToAscii($cloumn) { if (strlen($cloumn) > 1) { $sj_1 = substr($cloumn, 0, 1); $sj_2 = substr($cloumn, 1, 1); $diff = intval((ord($sj_1) - ord('A') + 1) * 26) + intval(ord($sj_2)); $currentAcsii = $diff; //超过26个字母时才会启用 } else { $currentAcsii = ord($cloumn); } return $currentAcsii; } //获取表格表头所行数(数组深度) public function _headArrDepth($array) { $max_depth = 1; foreach ($array as $value) { if (is_array($value)) { $depth = $this->_headArrDepth($value) + 1; if ($depth > $max_depth) { $max_depth = $depth; } } } return $max_depth; } //获取表格表头合并个数（数组长度） public function _headArrLength($array) { $length = 0; foreach ($array as $value) { if (is_array($value)) { $length += $this->_headArrLength($value); } else { $length ++; } } return $length; } public function getExcelHeader($headArr = array(), $startRow = 0, $startColu = 'A', $objPHPExcel) { //求出表头一共占用多少行,即求出headArr数组的深度 $death = $this->_headArrDepth($headArr); $maxRow = $startRow + $death - 1; $this->_getExcelHeader($headArr, $startRow, $startColu, $maxRow, $objPHPExcel); return $maxRow + 1; } //获取excel表头 public function _getExcelHeader($headArr = array(), $startRow = 0, $startColu = 'A', $maxRow, $objPHPExcel) { $initAscii = $this->_CloumnToAscii($startColu); if (!empty($headArr)) { foreach ($headArr as $k => $v) { $currentCloumn = $this->_AsciiToCloumn($initAscii); if (is_array($v)) { $mergeAscii = $initAscii + $this->_headArrLength($v) - 1; $mergeCloumn = $this->_AsciiToCloumn($mergeAscii); $objPHPExcel->getActiveSheet()->mergeCells($currentCloumn . $startRow . ':' . $mergeCloumn . $startRow); //跨列合并单元格 $objPHPExcel->setActiveSheetIndex(0)->setCellValue($currentCloumn . $startRow, $k); //给单元格赋值 $objPHPExcel->getActiveSheet()->getStyle($currentCloumn . $startRow)->applyFromArray(array('font' => array('bold' => true), 'alignment' => array('horizontal' => 'center'))); //设置单元格样式 $startRow++; $this->_getExcelHeader($v, $startRow, $currentCloumn, $maxRow, $objPHPExcel); $initAscii = $mergeAscii + 1; $startRow--; } else { $objPHPExcel->getActiveSheet()->mergeCells($currentCloumn . $startRow . ':' . ($currentCloumn . $maxRow)); //跨行合并单元格 $objPHPExcel->setActiveSheetIndex(0)->setCellValue($currentCloumn . $startRow, $v); //给单元格赋值 $objPHPExcel->getActiveSheet()->getStyle($currentCloumn . $startRow)->getAlignment()->setVertical('center'); $objPHPExcel->getActiveSheet()->getStyle($currentCloumn . $startRow)->applyFromArray(array('font' => array('bold' => true), 'alignment' => array('horizontal' => 'center', 'vertical' => 'center'))); $initAscii++; } } } } } $obj = new ExcelExport(); $timeRange ='2017-8-1 至 2017-8-10'; $filename = $timeRange.'销售收费统计表'; $headArr = [ '序号', '日期', '购买人', '墓位', '合同编号', '收入明细(元)'=>[ '预定金', '墓款'=>[ '已收预定金', '尾款' ], '安放费', '服务费', '刻字费', '商品费', '瓷像费', '管理费', '墓权证', '绿化', '维修', '迁出' ], '收入金额(元)'=>[ '总金额', '折扣金额', '实际金额', '本次收费' ], '业务员', ]; $data = array(); for($i=0; $i<5; $i++){ for($j=1;$j<=23; $j++){ $data[$i][$j] = $j; } } $obj->index($filename, $headArr, $data, $timeRange = "", $tmpFileds = array(), $startRow = 1, $startCol = 'A');
所需的文件: 链接:http://pan.baidu.com/s/1jHWDVXc 密码：phnm
