方式1:原生PHP导出
header('Content-Disposition: attachment;filename=demo.xls');
header("Content-Type: application/vnd.ms-excel");
/* php输出表格即可 */
方式2:phpexcel
require_once "../../custom/PHPExcel.php";
require_once '../../custom/PHPExcel/IOFactory.php';
require_once '../../custom/PHPExcel/Writer/Excel5.php';
$this->app->loadLang('bug');
if ($_POST || $reportID) {
$objPHPExcel = new PHPExcel();
$objWriter = new PHPExcel_Writer_Excel5($objPHPExcel);
$sheet = 0;
$fileTitle = $_POST['fileName'];
$ids = '';
if ($reportID) {
$ids = $reportID;
} else {
$exportType = $_POST['exportType'];
if ($exportType == 'selected') {
$ids = $this->cookie->exportReportID;
} else if ($exportType == 'all') {
$require = $this->session->monthReportRequire;
$result = $this->my->getMonthReport($require->dept, $require->product, $require->project, $require->year, $require->month, $require->status, '', '', true);
foreach ($result as $val) {
$ids .= $val->id.',';
}
}
}
$reports = explode(',', trim($ids, ','));
foreach ($reports as $reportID) {
$report = $this->my->getReport($reportID);
$content = json_decode($report->content);
$bugs1 = $content->problemResolvedBugs;
$bugs2 = $content->serviceResponseBugs;
$textarea1 = $content->textarea1;
$textarea2 = $content->textarea2;
$textarea3 = $content->textarea3;
$textarea4 = $content->textarea4;
$year = substr($report->date, 0, 4);
$month = substr($report->date, 4);
$begin = $year.'-'.$month.'-01';
$begin = date('Y-m', strtotime("$begin -1 month")).'-25';
$end = $year.'-'.$month.'-25';
$typeList = $this->lang->bug->typeList;
$severityList = $this->lang->bug->severityList;
$users = $this->loadModel('user')->getPairs('noletter|withguest');
if (!$fileTitle) {
$fileTitle = $report->title."_".$users[$report->createBy];
}
/* 当前行 */
$line = 1;
/* 设置工作薄名称 */
$objPHPExcel->createSheet();
$objPHPExcel->setactivesheetindex($sheet);
$objPHPExcel->getActiveSheet()->setTitle($report->title."_".$users[$report->createBy]);
/* 设置总体样式 */
$sharedStyle1 = new PHPExcel_Style();
$sharedStyle1->applyFromArray(
array(
'borders' => array(
'left' => array(
'style' => PHPExcel_Style_Border::BORDER_THIN
),
'right' => array(
'style' => PHPExcel_Style_Border::BORDER_THIN
),
'top' => array(
'style' => PHPExcel_Style_Border::BORDER_THIN
),
'bottom' => array(
'style' => PHPExcel_Style_Border::BORDER_THIN
)
),
'alignment' => array(
'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER,
'wrap' => true,
)
)
);
$objPHPExcel->getActiveSheet()->setSharedStyle($sharedStyle1, "A1:L9");
$objPHPExcel->getActiveSheet()->setSharedStyle($sharedStyle1, "A11:L17");
$objPHPExcel->getActiveSheet()->setSharedStyle($sharedStyle1, "A19:L22");
$objPHPExcel->getActiveSheet()->getRowDimension('1')->setRowHeight(30);
$objPHPExcel->getActiveSheet()->getRowDimension('11')->setRowHeight(30);
// $objPHPExcel->getActiveSheet()->getDefaultRowDimension()->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension()->setAutoSize(true);
// $objPHPExcel->getActiveSheet()->getRowDimension('19')->setAutoSize(true);
/* ===================================表格1===================================== */
$objPHPExcel->getActiveSheet()
->setCellValue('A1', '运维问题响应情况统计('.$begin.'至'.$end.')')
->mergeCells('A'.$line.':L'.$line);
$objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setSize(20)->setBold(true);
/* 设置表头 1 */
$tableField_1 = array('项目', '迭代', '类型', '总数', /*'未确认'*/'待发布', '已挂起', '已否决', '已关闭', '已解决', '未解决', '解决数', '解决率');
$line = 2;
$letter = 65;
foreach ($tableField_1 as $val) {
$objPHPExcel->getActiveSheet()
->setCellValue(chr($letter).$line, $val);
$objPHPExcel->getActiveSheet()->getStyle(chr($letter).$line)->getFont()->setSize(12)->setBold(true);
$letter++;
}
/* 设置表数据 1 */
unset($typeList['']);
$pj_rowspan = count($typeList);/* 类型数目 */
$line++;
// $letter = 65;
$pdcount = $line;
foreach ($bugs1 as $pd => $pdArr) {
$len_pj = count($pdArr);
$pd_rowspan = $len_pj * $pj_rowspan;/* 项目跨多少行 */
$objPHPExcel->getActiveSheet()
->setCellValue('A'.$pdcount, $pd)
->mergeCells('A'.$pdcount.':A'.($pdcount + $pd_rowspan - 1));
$pjcount = $line;
foreach ($pdArr as $pj => $pjArr) {
$objPHPExcel->getActiveSheet()
->setCellValue('B'.$pjcount, $pj)
->mergeCells('B'.$pjcount.':B'.($pjcount + $pj_rowspan - 1));
$typecount = $line;
foreach ($pjArr as $type => $val) {
$objPHPExcel->getActiveSheet()
->setCellValue('C'.$typecount, $typeList[$type])
->setCellValue('D'.$typecount, $val->total)
// ->setCellValue('E'.$typecount, $val->notConfirmed)
->setCellValue('E'.$typecount, $val->toberelease)
->setCellValue('F'.$typecount, $val->hangup)
->setCellValue('G'.$typecount, $val->rejected)
->setCellValue('H'.$typecount, $val->closed)
->setCellValue('I'.$typecount, $val->resolved)
->setCellValue('J'.$typecount, $val->notResolved_new)
->setCellValue('K'.$typecount, $val->resolved_new)
->setCellValue('L'.$typecount, number_format(($val->resolved_new / $val->total) * 100, 2).'%');
$typecount++;
}
$pjcount += $pj_rowspan;
}
$pdcount += $pd_rowspan;
}
/* ===================================表格2===================================== */
$line += $pj_rowspan + 1;
$objPHPExcel->getActiveSheet()
->setCellValue('A'.$line, '运维服务响应情况统计('.$begin.'至'.$end.')')
->mergeCells('A'.$line.':L'.$line);
$objPHPExcel->getActiveSheet()->getStyle('A'.$line)->getFont()->setSize(20)->setBold(true);
/* 设置表头 1 */
$line++;
$letter = 65;
$tableField_2_1 = array(
'项目',
'迭代',
'严重程度'
);
foreach ($tableField_2_1 as $val) {
$objPHPExcel->getActiveSheet()
->setCellValue(chr($letter).$line, $val)
->getStyle(chr($letter).$line)->getFont()->setSize(12)->setBold(true);
$letter++;
}
$tableField_2_2 = array(/* 为了跟上一张表对齐,这3个字段需要合并单元格 */
'接收平均时长(分钟)',
'响应平均时长(分钟)',
'解决平均时长(分钟)'
);
foreach ($tableField_2_2 as $val) {
$objPHPExcel->getActiveSheet()
->setCellValue(chr($letter).$line, $val)
->mergeCells(chr($letter).$line.':'.chr($letter+2).$line)
->getStyle(chr($letter).$line)->getFont()->setSize(12)->setBold(true);
$letter+=3;
}
/* 设置表数据 1 */
$pj_rowspan = count($severityList);/* 严重程度数目 */
$line++;
// $letter = 65;
$pdcount = $line;
foreach ($bugs2 as $pd => $pdArr) {
$len_pj = count($pdArr);
$pd_rowspan = $len_pj * $pj_rowspan;/* 项目跨多少行 */
$objPHPExcel->getActiveSheet()
->setCellValue('A'.$pdcount, $pd)
->mergeCells('A'.$pdcount.':A'.($pdcount + $pd_rowspan - 1));
$pjcount = $line;
foreach ($pdArr as $pj => $pjArr) {
$objPHPExcel->getActiveSheet()
->setCellValue('B'.$pjcount, $pj)
->mergeCells('B'.$pjcount.':B'.($pjcount + $pj_rowspan - 1));
$severitycount = $line;
foreach ($pjArr as $severity => $val) {
$avgReceivedTime = $this->changeMinuteToDay($val->receivedTime / $val->receivedNum);
$avgBugconfirmedTime = $this->changeMinuteToDay($val->bugconfirmedTime / $val->bugconfirmedNum);
$avgResolvedTime = $this->changeMinuteToDay($val->resolvedTime / $val->resolvedNum);
$objPHPExcel->getActiveSheet()
->setCellValue('C'.$severitycount, $severityList[$severity])
->setCellValue('D'.$severitycount, $avgReceivedTime)
->mergeCells('D'.$severitycount.':F'.$severitycount)
->setCellValue('G'.$severitycount, $avgBugconfirmedTime)
->mergeCells('G'.$severitycount.':I'.$severitycount)
->setCellValue('J'.$severitycount, $avgResolvedTime)
->mergeCells('J'.$severitycount.':L'.$severitycount);
$severitycount++;
}
$pjcount += $pj_rowspan;
}
$pdcount += $pd_rowspan;
}
/* ===================================表格3===================================== */
$line += $pj_rowspan + 1;
$tableField_3 = array(
'运维服务情况总结',
'可能存在的运维风险',
'解决措施和建议',
'待沟通协调事项'
);
$tableField_3_val = array(
'textarea1',
'textarea2',
'textarea3',
'textarea4'
);
foreach ($tableField_3 as $key => $val) {
$objPHPExcel->getActiveSheet()
->setCellValue('A'.$line, $val)
->setCellValue('B'.$line, $$tableField_3_val[$key])
->mergeCells('B'.$line.':L'.$line);
$objPHPExcel->getActiveSheet()
->getStyle('A'.$line)->getFont()->setSize(12)->setBold(true);
// ->getColumnDimension()->setAutoSize(true);
$line++;
}
$sheet++;
}
header("Pragma: public");
header("Expires: 0");
header("Cache-Control:must-revalidate, post-check=0, pre-check=0");
header("Content-Type:application/force-download");
header("Content-Type:application/vnd.ms-execl");
header("Content-Type:application/octet-stream");
header("Content-Type:application/download");
header('Content-Disposition:attachment;filename='.$fileTitle.'.xls');
header("Content-Transfer-Encoding:binary");
$objWriter->save('php://output');
die();
}
方式3: Spreadsheet Excel Writer 和 OLE,
如果没有可以分别从 http://pear.php.net/package/Spreadsheet_Excel_Writer/ 和 http://pear.php.net/package/OLE/ 下载,解压放在PEAR目录下
<?php
include 'Writer.php';
/* *** 准备导出的数据 *** */
$head = 'One Week Schedule';
$data = array('Monday' => array( array('time' => '09:00', 'event' => '公司例会例会'),
array('time' => '14:00', 'event' => '部门例会')
),
'Tuesday' => array( array('time' => '09:30', 'event' => '和 Mr. Stinsen 早餐')),
'Wednesday' => array(array('time' => '12:10', 'event' => '市场中阶报告'),
array('time' => '15:30', 'event' => '市场部战略部署会议') ),
'Thursday' => array( array('time' => '', 'event' => '')),
'Friday' => array( array('time' => '16:00', 'event' => 'WoC Stock 研讨会'),
array('time' => '17:00', 'event' => '飞往华尔街'),
array('time' => '21:00', 'event' => '会见克林顿'))
);
/* *** *** */
$workbook = new Spreadsheet_Excel_Writer();
$filename = date('YmdHis').'.xls';//csv
$workbook->send($filename); // 发送 Excel 文件名供下载
$workbook->setVersion( 8 );
$sheet = &$workbook->addWorksheet("Sheet1"); // 创建工作表
$sheet->setInputEncoding('utf-8'); // 字符集
$headFormat = &$workbook->addFormat(array('Size' => 14, 'Align' => 'center','Color' => 'white', 'FgColor' => 'brown', 'Bold'=>'1', 'Border' => '1'));//定义格式
$dayFormat = &$workbook->addFormat(array('Size' => 12, 'Align' => 'center', 'VAlign' => 'vcenter', 'FgColor' => 'green', 'Color' => 'white', 'Border' => '1'));//定义格式
$dataFormat = &$workbook->addFormat(array('Size' => 10, 'Align' => 'left', 'Border' => '1', 'Color' => 'black', 'FgColor'=> 'cyan'));//定义格式
$sheet->setColumn(0, 0, 20); // 设置宽度
$sheet->setColumn(1, 1, 15); // 设置宽度
$sheet->setColumn(2, 2, 30); // 设置宽度
$r = 0;
$sheet->write(0, $r, $head, $headFormat); // 表格标题
$sheet->mergeCells(0, 0, 0, 2); // 跨列显示
$r++; // 数据从第2行开始
foreach ($data as $day => $events){
$c = 0;
$sheet->write($r, $c, $day, $dayFormat);
if (!$events){
// 当天没有计划
$r++;
} else {
$startRow = $r;
foreach ($events as $e){
$c = 1;
$sheet->write($r, $c++, $e['time'], $dataFormat); // 工作表写入数据
$sheet->write($r, $c++, $e['event'], $dataFormat); // 工作表写入数据
$r++;
}
// 合并 $day 单元格
$sheet->mergeCells($startRow, 0, $r - 1, 0);
}
}
$workbook->close(); // 完成下载
?>