关于报表导出,phpexcel的使用
一、方法一
控制器
调用的公共方法
/* * excel导出配置 */ public function excelAction($filename, $title, $expCellName, $expTableData) { $xlsTitle = iconv('utf-8', 'gb2312', '');#编码转换 $fileName = date($filename . '_Ymd'); #文件名称可根据自己情况设定 $cellNum = count($expCellName); $dataNum = count($expTableData); #引入phpexcel类 require_once(SKIHAT_PATH_VENDOR . '/php-excel/PHPExcel/IOFactory.php'); #实例化 $objPHPExcel = new \PHPExcel(); $cellName = array(); foreach ($expCellName as $key => $exp) { $cellName[$key] = $exp[0]; } #合并单元格 $objPHPExcel->setActiveSheetIndex(0)->mergeCells('A1:' . $cellName[$cellNum - 1] . '1'); $objPHPExcel->setActiveSheetIndex(0)->setCellValue('A1', $title); #设置font $objPHPExcel->setActiveSheetIndex(0)->getStyle('A1')->getFont()->setSize(16); $objPHPExcel->setActiveSheetIndex(0)->getStyle('A1')->getFont()->setBold(true); #设置居中(上下左右) $objPHPExcel->setActiveSheetIndex(0)->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $objPHPExcel->setActiveSheetIndex(0)->getStyle('A1')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER); for ($i = 0; $i < $cellNum; $i++) { #居中 $objPHPExcel->setActiveSheetIndex(0)->getStyle($cellName[$i])->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $objPHPExcel->setActiveSheetIndex(0)->getStyle($cellName[$i])->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER); #给字段标题赋值 $objPHPExcel->setActiveSheetIndex(0)->setCellValue($cellName[$i] . '2', $expCellName[$i][1]); #设置宽度 $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension($cellName[$i])->setWidth(15); #设置font $objPHPExcel->setActiveSheetIndex(0)->getStyle($cellName[$i] . '2')->getFont()->setSize(12); $objPHPExcel->setActiveSheetIndex(0)->getStyle($cellName[$i] . '2')->getFont()->setBold(true); } if ($dataNum > 0) { for ($i = 0; $i < $dataNum; $i++) { for ($j = 0; $j < $cellNum; $j++) { $objPHPExcel->setActiveSheetIndex(0)->setCellValue($cellName[$j] . ($i + 3), ' ' . $expTableData[$i][$expCellName[$j][2]]); } } } ob_end_clean(); header('pragma:public'); header('Content-type:application/vnd.ms-excel;charset=utf-8;name="' . $xlsTitle . '.xls"'); header("Content-Disposition:attachment;filename=$fileName.xls");#attachment新窗口打印inline本窗口打印 $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5'); $objWriter->save('php://output'); exit; }
二、方法二
1.控制器获取数据 进行数据重组
/** * 库存报表的excel导出 */ public function excelAction() { $trackingReports = $this->indexAction(); #重组数据 foreach ($trackingReports as $key => $trackingReport) { $trackingReports[$key]['createdTime'] = date('Y-m-d H:i:s', $trackingReport['created']); $trackingReports[$key]['orderStatus'] = SaleOrder::statusWithName($trackingReport['order_status']); $trackingReports[$key]['salePrice'] = number_format($trackingReport['sale_price'] / 100, 2); $trackingReports[$key]['price'] = number_format($trackingReport['sale_price'] * $trackingReport['sale_num'] / 100, 2); $trackingReports[$key]['unOutBatchNum'] = $trackingReport['sale_num'] - $trackingReport['packageSkuNum']; $trackingReports[$key]['unOutBatchPrice'] = number_format(($trackingReport['sale_num'] - $trackingReport['packageSkuNum']) * $trackingReport['sale_price'] / 100, 2); if ($trackingReport['shipped'] != 0) { $trackingReports[$key]['outedTime'] = date('Y-m-d', $trackingReport['shipped']); } } $this['trackingReports'] = $trackingReports; }
2.view页面代码
<?php $this->helpers('core'); require_once(SKIHAT_PATH_VENDOR . '/php-excel/PHPExcel.php'); $objExcel = new PHPExcel(); $objExcel->getProperties() ->setCreator('jlt-erp') ->setLastModifiedBy('jlt-erp') ->setTitle('销售订单跟踪报表信息') ->setKeywords('销售订单跟踪报表信息') ->setDescription('销售订单跟踪报表信息') ->setSubject('销售订单跟踪报表信息'); $title = '销售订单跟踪报表信息'; //文件标题 $fileName = date('销售订单跟踪报表信息 _Ymd'); $sheet = $objExcel->setActiveSheetIndex(0); $sheet->setCellValue('A2', '商品编号'); $sheet->setCellValue('B2', '商品名称'); $sheet->setCellValue('C2', '单位'); $sheet->setCellValue('D2', '订单日期'); $sheet->setCellValue('E2', '订单编号'); $sheet->setCellValue('F2', '客户姓名'); $sheet->setCellValue('G2', '客户电话'); $sheet->setCellValue('H2', '订单状态'); $sheet->setCellValue('I2', '商品数量'); $sheet->setCellValue('J2', '含税单价'); $sheet->setCellValue('K2', '销售额'); $sheet->setCellValue('L2', '未出库数量'); $sheet->setCellValue('M2', '未出库金额'); $sheet->setCellValue('N2', '出库单号'); $sheet->setCellValue('O2', '出库时间'); $sheet->setCellValue('P2', '备注'); #合并单元格 $objExcel->setActiveSheetIndex(0)->mergeCells('A1:P1' ); $objExcel->setActiveSheetIndex(0)->setCellValue('A1', $title); #设置font $objExcel->setActiveSheetIndex(0)->getStyle('A1')->getFont()->setSize(16); #设置居中(上下左右) $objExcel->setActiveSheetIndex(0)->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $objExcel->setActiveSheetIndex(0)->getStyle('A1')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER); ?> <?php $rows = 3; foreach ($this['trackingReports'] as $item) { $sheet->setCellValue("A{$rows}", " ".$item['sku_id']); $sheet->setCellValue("B{$rows}", " ".$item['sku_name']); $sheet->setCellValue("C{$rows}", " ".$item['sale_unit']); $sheet->setCellValue("D{$rows}", " ".$item['createdTime']); $sheet->setCellValue("E{$rows}", " ".$item['order_sn']); $sheet->setCellValue("F{$rows}", " ".$item['cust_name']); $sheet->setCellValue("G{$rows}", " ".$item['cust_phone']); $sheet->setCellValue("H{$rows}", " ".$item['orderStatus']); $sheet->setCellValue("I{$rows}", " ".$item['sale_num']); $sheet->setCellValue("J{$rows}", $item['salePrice']); $sheet->setCellValue("K{$rows}", $item['price']); $sheet->setCellValue("L{$rows}", " ".$item['unOutBatchNum'] ); $sheet->setCellValue("M{$rows}", $item['unOutBatchPrice']); $sheet->setCellValue("N{$rows}", " ".$item['wms_order_sn']); $sheet->setCellValue("O{$rows}", " ".$item['outedTime']); $sheet->setCellValue("P{$rows}", " ".$item['note']); ++$rows; } ob_end_clean(); header('pragma:public'); header("Content-Disposition:attachment;filename=".$fileName.".xls");#attachment新窗口打印inline本窗口打印 $objWriter = PHPExcel_IOFactory::createWriter($objExcel, 'Excel5'); $objWriter->save('php://output'); exit;