一、方法一
控制器
调用的公共方法
/*
* 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;