PHP使用PHPExcel类生成excel表格下载文件的例子
一个基于使用了PHPExcel类的生成excel表格下载文件的PHP用例代码，使用了一些比如设置单元格格式、跨列跨行的功能，作一下记录：
//xls下载
$sitename = zmf::config('sitename');
$filename = zmf::time($planInfo['planTime'], 'md') . $planInfo->areaInfo->title . $planInfo->styleInfo->title . Plans::exTypes($planInfo->type).'物料'.$groupByTitle.'清单';
$charterArr = array('A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z');
//引入phpexcel相关库类文件
Yii::import('application.vendors.phpexcel.*');
require_once 'PHPExcel.php';
$objPHPExcel = new PHPExcel();
// Set document properties
$objPHPExcel->getProperties()->setCreator($sitename)
->setLastModifiedBy($sitename)
->setTitle($sitename);
if($groupBy=='userDetail'){
$attrKeys = array(
array(
'title' => $groupByTitle,
'width' => 20,
'field' => $groupByField
),
array(
'title' => '服务项目',
'width' => 40,
'field' => 'userTitle',
'color' => 'FF0000',
),
array(
'title' => '介绍',
'width' => 50,
'field' => 'userDesc'
),
array(
'title' => '数量',
'width' => 10,
'field' => 'userNum'
),
array(
'title' => '单位',
'width' => 20,
'field' => 'userUnit'
),
array(
'title' => '单价',
'width' => 20,
'field' => 'userPrice'
),
array(
'title' => '总价',
'width' => 20,
'field' => 'userPriceTotal'
),
);
}else{
$attrKeys = array(
array(
'title' => '分区',
'width' => 20,
'field' => 'zone'
),
array(
'title' => '名称',
'width' => 40,
'field' => 'title'
),
array(
'title' => '数量',
'width' => 10,
'field' => 'num'
),
array(
'title' => '单位',
'width' => 10,
'field' => 'unit'
),
// array(
// 'title' => '规格',
// 'width' => 10,
// 'field' => 'size'
// ),
// array(
// 'title' => '形状',
// 'width' => 10,
// 'field' => 'shape'
// ),
// array(
// 'title' => '颜色',
// 'width' => 10,
// 'field' => 'color'
// ),
// array(
// 'title' => '材质/种类',
// 'width' => 10,
// 'field' => 'material'
// ),
array(
'title' => '备注',
'width' => 50,
'field' => '_desc',
'horizontal_center'=>false, //水平居中
'mergeColumn'=>5, //横向合并后面单元格数量
),
);
}
$attrKeysLen = count($attrKeys);
$maxCharIdx = $attrKeysLen-1 + array_sum(array_column($attrKeys, 'mergeColumn'));
$maxChar=$charterArr[$maxCharIdx];
if($groupBy=='userDetail'){
//表头
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('A1', 'Quotation');
$objPHPExcel->setActiveSheetIndex(0)->getStyle('A1')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
$objPHPExcel->setActiveSheetIndex(0)->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->getRowDimension('1')->setRowHeight(50);
$objPHPExcel->setActiveSheetIndex(0)->mergeCells('A1:' . $charterArr[$attrKeysLen-1] . '1');
$objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setSize(20);
$objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);
$objPHPExcel->setActiveSheetIndex(0)->getStyle('A1')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
//$objPHPExcel->setActiveSheetIndex(0)->getStyle('A1')->getFill()->getStartColor()->setRGB('F2DCDB');
//简介
$str='To：'.$planInfo->userInfo->truename.PHP_EOL.'惊喜时间 Date：'.zmf::time($planInfo->planTime, 'Y/m/d').PHP_EOL.'惊喜地点 Venue: '.$planInfo->areaInfo->title.PHP_EOL.'Tel:400-000-0000'.PHP_EOL.'Email:vip@tell520.com';
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('A2', $str);
$objPHPExcel->getActiveSheet()->getRowDimension('2')->setRowHeight(120);
$objPHPExcel->setActiveSheetIndex(0)->mergeCells('A2:' . $charterArr[$attrKeysLen-1] . '2');
$objPHPExcel->getActiveSheet()->getStyle('A2')->getFont()->setSize(16);
$objPHPExcel->getActiveSheet()->getStyle('A2')->getFont()->setBold(true);
$objPHPExcel->setActiveSheetIndex(0)->getStyle('A2')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle('A2')->getAlignment()->setWrapText(true);
//第三排
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('A3', '本场惊喜，从活动策划+物料采购+制作设计+惊喜统筹+现场惊喜服务人员：预计'.$userNum.'人');
$objPHPExcel->setActiveSheetIndex(0)->getStyle('A3')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
$objPHPExcel->setActiveSheetIndex(0)->getStyle('A3')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->getRowDimension('3')->setRowHeight(50);
$objPHPExcel->getActiveSheet()->getStyle('A3')->getFont()->setSize(14);
$objPHPExcel->getActiveSheet()->getStyle('A3')->getFont()->setBold(true);
$objPHPExcel->setActiveSheetIndex(0)->mergeCells('A3:' . $charterArr[$attrKeysLen-1] . '3');
$objPHPExcel->setActiveSheetIndex(0)->getStyle('A3')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
//$objPHPExcel->setActiveSheetIndex(0)->getStyle('A3')->getFill()->getStartColor()->setRGB('F2DCDB');
}else{
//表头
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('A1', $filename);
$objPHPExcel->setActiveSheetIndex(0)->getStyle('A1')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
$objPHPExcel->setActiveSheetIndex(0)->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->getRowDimension('1')->setRowHeight(50);
$objPHPExcel->setActiveSheetIndex(0)->mergeCells('A1:' . $charterArr[$maxCharIdx] . '1');
$objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setSize(20);
$objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);
/*设置公共信息*/
//第一排
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('A2', '日期：' . zmf::time($planInfo->planTime, 'Y/m/d'));
$objPHPExcel->setActiveSheetIndex(0)->getStyle('A2')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('B2', '求婚时间：'.($qiuHunTimeInfo ? $qiuHunTimeInfo['results'] : ''));
$objPHPExcel->setActiveSheetIndex(0)->getStyle('B2')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('C2', '主角：' . $planInfo->userInfo->truename);
$objPHPExcel->setActiveSheetIndex(0)->getStyle('C2')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
$objPHPExcel->setActiveSheetIndex(0)->mergeCells('C2:D2');
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('E2', '总部统筹：' . $excUserinfo->truename . '/' . $excUserinfo->phone);
$objPHPExcel->setActiveSheetIndex(0)->getStyle('E2')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
$objPHPExcel->setActiveSheetIndex(0)->mergeCells('E2:F2');
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('G2', '紧急联系：周旭');
$objPHPExcel->setActiveSheetIndex(0)->getStyle('G2')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
$objPHPExcel->setActiveSheetIndex(0)->mergeCells('G2:H2');
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('I2', '总部监督：王沛如');
$objPHPExcel->setActiveSheetIndex(0)->getStyle('I2')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
$objPHPExcel->setActiveSheetIndex(0)->mergeCells('I2:J2');
//第二排
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('A3', '城市：' . $planInfo->areaInfo->title);
$objPHPExcel->setActiveSheetIndex(0)->getStyle('A3')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('B3', '场地：' . $placeInfo['title']);
$objPHPExcel->setActiveSheetIndex(0)->getStyle('B3')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('C3', '彩排时间：');
$objPHPExcel->setActiveSheetIndex(0)->getStyle('C3')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
$objPHPExcel->setActiveSheetIndex(0)->mergeCells('C3:D3');
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('E3', '执行策划：' . $partnerInfo['title'] . '/' . $contactInfo['name'] . '/' . $contactInfo['phone'] . ($contactInfo['otherPhone'] != '' ? '/' . $contactInfo['otherPhone'] : ''));
$objPHPExcel->setActiveSheetIndex(0)->getStyle('E3')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
$objPHPExcel->setActiveSheetIndex(0)->mergeCells('E3:F3');
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('G3', '电话：18888888888');
$objPHPExcel->setActiveSheetIndex(0)->getStyle('G3')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
$objPHPExcel->setActiveSheetIndex(0)->mergeCells('G3:H3');
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('I3', '电话：13888888888');
$objPHPExcel->setActiveSheetIndex(0)->getStyle('I3')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
$objPHPExcel->setActiveSheetIndex(0)->mergeCells('I3:J3');
$objPHPExcel->getActiveSheet()->getRowDimension('2')->setRowHeight(35);
$objPHPExcel->getActiveSheet()->getRowDimension('3')->setRowHeight(35);
}
//设置标题
$tkey=4;
$jump = 0;
foreach ($attrKeys as $k => $_attr) {
$_char = $charterArr[$k+$jump];
$_mergeColumn = $_attr['mergeColumn']??0;
$_horizontal_center = $_attr['horizontal_center']??true;
$objPHPExcel->setActiveSheetIndex(0)->setCellValue($_char . $tkey, $_attr['title']);
$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension($_char)->setWidth($_attr['width']);
//加粗
$objPHPExcel->getActiveSheet()->getStyle($_char . $tkey)->getFont()->setBold(true);
//水平垂直居中
$objPHPExcel->setActiveSheetIndex(0)->getStyle($_char . $tkey)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
if ($_horizontal_center) $objPHPExcel->setActiveSheetIndex(0)->getStyle($_char . $tkey)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
//设置背景色
$objPHPExcel->setActiveSheetIndex(0)->getStyle($_char . $tkey)->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
//$objPHPExcel->setActiveSheetIndex(0)->getStyle($_char .$tkey)->getFill()->getStartColor()->setRGB('FFFF00');
if ($_mergeColumn>0) {
//合并列
$jump+=$_mergeColumn;
$_charEnd = $charterArr[$k+$jump];
$objPHPExcel->setActiveSheetIndex(0)->mergeCells($_char . $tkey.':'.$_charEnd . $tkey);
}
}
$objPHPExcel->getActiveSheet()->getRowDimension($tkey)->setRowHeight(30);
$pk = 4;
$colorIndex = 0;
$totalPrice=0;
if($groupBy=='userDetail'){
foreach(['normal','senior','luxury'] as $ver){
$suppArr=$suppliesArr[$ver];
if(!$suppArr) continue;
self::setVersionRow($objPHPExcel,$pk,$suppArr,$ver,$attrKeys,$zoneArr,$charterArr,$versionMoney,$totalMoney,$planInfo);
}
//价格汇总
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('A'.($pk+1), '价格汇总');
$objPHPExcel->setActiveSheetIndex(0)->getStyle('A'.($pk+1))->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
$objPHPExcel->setActiveSheetIndex(0)->getStyle('A'.($pk+1))->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->getRowDimension($pk+1)->setRowHeight(40);
$objPHPExcel->getActiveSheet()->getStyle('A' . ($pk + 1))->getFont()->getColor()->setRGB('FF0000');
$objPHPExcel->setActiveSheetIndex(0)->mergeCells("A".($pk + 1).":G".($pk + 1));
$objPHPExcel->setActiveSheetIndex(0)->getStyle('A' . ($pk + 1))->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
$objPHPExcel->setActiveSheetIndex(0)->getStyle('A'. ($pk + 1))->getFill()->getStartColor()->setRGB('fdeada');
$pk++;
foreach(['normal','senior','luxury'] as $ver){
if(! $suppArr=$suppliesArr[$ver]) continue;
$versionName=PlanSupplies::exVersion($ver);
$vName=$versionName.'价格';
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('A'.($pk+1), $vName);
$objPHPExcel->setActiveSheetIndex(0)->getStyle('A'.($pk+1))->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
$objPHPExcel->setActiveSheetIndex(0)->getStyle('A'.($pk+1))->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->getRowDimension($pk+1)->setRowHeight(40);
$objPHPExcel->getActiveSheet()->getStyle('A' . ($pk + 1))->getFont()->getColor()->setRGB('FF0000');
$objPHPExcel->setActiveSheetIndex(0)->mergeCells("A".($pk + 1).":F".($pk + 1));
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('G'.($pk+1), $totalMoney[$ver]);
$objPHPExcel->setActiveSheetIndex(0)->getStyle('G'.($pk+1))->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
$objPHPExcel->setActiveSheetIndex(0)->getStyle('G' . ($pk+1))->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->getRowDimension($pk+1)->setRowHeight(40);
$objPHPExcel->getActiveSheet()->getStyle('G' . ($pk + 1))->getFont()->getColor()->setRGB('FF0000');
$pk++;
}
foreach(['self','add','wait','del'] as $ver){
$suppArr=$suppliesArr[$ver];
if(!$suppArr) continue;
self::setVersionRow($objPHPExcel,$pk,$suppArr,$ver,$attrKeys,$zoneArr,$charterArr,$versionMoney,$totalMoney,$planInfo);
}
}else{
foreach ($suppliesArr as $ver=>$suppArr) {//分类数组
$versionName=PlanSupplies::exVersion($ver);
if ($ver!='normal') {
$vName = $ver == 'normal' ? $versionName . '物料' : $versionName . '增加物料';
//版本分类
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('A' . ($pk + 1), $vName);
$objPHPExcel->setActiveSheetIndex(0)->getStyle('A' . ($pk + 1))->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
$objPHPExcel->setActiveSheetIndex(0)->getStyle('A' . ($pk + 1))->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->getRowDimension($pk + 1)->setRowHeight(40);
$objPHPExcel->getActiveSheet()->getStyle('A' . ($pk + 1))->getFont()->getColor()->setRGB('FF0000');
$objPHPExcel->setActiveSheetIndex(0)->mergeCells("A" . ($pk + 1) . ":I" . ($pk + 1));
$pk++;
}
foreach ($suppArr as $arr) {//分类数组
$pre = $pk + 1;
$_row = 0;
foreach ($arr as $val) {
if (trim($val['title']) == '') continue; //物料名称是空的行跳过
$_row++;
$jump = 0;
foreach ($attrKeys as $k => $_attr) {
if ($_attr['field'] != '') {
$_value = $val[$_attr['field']];
switch ($_attr['field']) {
case 'type':
if($_value==999999){
$_value='其他相关服务';
}elseif($_value==999998){
$_value='工具类';
}else{
$_value=$typeArr[$_value];
}
break;
case 'zone':
$_value = $_value > 0 ? $zoneArr[$_value] : '其他项目';
break;
case 'num':
if($groupBy=='userDetail'){
$_value .= Supplies::exUnits($val['unit']);
}
break;
case 'unit':
$_value = Supplies::exUnits($val['unit']);
break;
case '_desc':
$_value = $_value != '' ? $_value : ($val['size']);
break;
default:
break;
}
} else {
$_value = '';
}
//设置内容
$_char = $charterArr[$k+$jump];
$_cell = $_char . ($pk + 1);
$_mergeColumn = $_attr['mergeColumn']??0;
$_horizontal_center = $_attr['horizontal_center']??true;
$objPHPExcel->setActiveSheetIndex(0)->setCellValue($_cell, $_value);
//水平垂直居中
$objPHPExcel->setActiveSheetIndex(0)->getStyle($_cell)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
// if ($_attr['field'] != 'desc' && $_attr['field'] != 'field') {
if ($_horizontal_center) {
$objPHPExcel->setActiveSheetIndex(0)->getStyle($_cell)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
}
if ($_mergeColumn>0) {
//合并列
$jump+=$_mergeColumn;
$_charEnd = $charterArr[$k+$jump];
$objPHPExcel->setActiveSheetIndex(0)->mergeCells($_cell.':'.$_charEnd . ($pk + 1));
}
}
$totalPrice+=$val['userPrice']*$val['num'];
++$pk;
$objPHPExcel->getActiveSheet()->getRowDimension($pk)->setRowHeight(30);
}
++$colorIndex;
if ($_row > 1) {
//合并单元格
$objPHPExcel->setActiveSheetIndex(0)->mergeCells('A' . $pre . ':A' . $pk);
}
}
}
}
$styleArray = array(
'borders' => array(
'allborders' => array(
'style' => PHPExcel_Style_Border::BORDER_THIN
)
)
);
$objPHPExcel->getActiveSheet()->getStyle('A1:'.$maxChar.($pk))->applyFromArray($styleArray);
/*
// 实例化插入图片类
$objDrawing = new PHPExcel_Worksheet_Drawing();
// 设置图片路径 切记：只能是本地图片
$objDrawing->setPath('');
// 设置图片高度
$objDrawing->setWidth(200);
// 设置图片要插入的单元格
$objDrawing->setCoordinates('A18');
// 设置图片所在单元格的格式
$objDrawing->setOffsetX(10);
$objDrawing->setOffsetY(10);
$objDrawing->setRotation(0);
$objDrawing->getShadow()->setVisible(true);
$objDrawing->getShadow()->setDirection(50);
$objDrawing->setWorksheet($objPHPExcel->getActiveSheet());
*/
// Rename worksheet
$objPHPExcel->getActiveSheet()->setTitle('活动策划公司');
// Set active sheet index to the first sheet, so Excel opens this as the first sheet
$objPHPExcel->setActiveSheetIndex(0);
// Redirect output to a client’s web browser (Excel2007)
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="' . $filename . '.xlsx"');
header('Cache-Control: max-age=0');
// If you're serving to IE 9, then the following may be needed
header('Cache-Control: max-age=1');
// If you're serving to IE over SSL, then the following may be needed
header('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
header('Last-Modified: ' . gmdate('D, d M Y H:i:s') . ' GMT'); // always modified
header('Cache-Control: cache, must-revalidate'); // HTTP/1.1
header('Pragma: public'); // HTTP/1.0
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save('php://output');版权属于：Joyber
本文链接：https://blog.qqvbc.com/default/895.html
转载时须注明出处及本声明