**
首先用composer在项目里安装phpexcel在composer.json require里加入 “moonlandsoft/yii2-phpexcel”,然后composer update即可
**
1.这里在项目里先创建个工具类common/utils/Exportfile.php,用该类实现导出,其他类调用该类的exportExcel方法即可,
<?php
namespace common\utils;
use Yii;
class ExportFile
{
/**
*---------------------------------------
* 导出数据为excel表格
* @param $list 数据集合二维数组
* @param $attributeLabels 字段集一维数组
* @param string $filename 文件名
* @param array $setRowHeight 行高
* @param array $setWidth
* @param $paramenterHeight 参数高度
* @param $rows 给某一个Execl中的某一行单独设置样式
* @throws \PHPExcel_Exception
* @throws \PHPExcel_Reader_Exception
* @throws \PHPExcel_Writer_Exception
*---------------------------------------
*/
public static function exportExcel($list, $attributeLabels=null, $filename = '', $setRowHeight = [], $setWidth = [], $paramenterHeight = '',$orderName='')
{
$objectPHPExcel = new \PHPExcel();//实例化通过composer安装的PHPExcel
$n = 1;//用于数据循环
//表头
$cellName = [
'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',
'AA', 'AB', 'AC', 'AD', 'AE', 'AF', 'AG', 'AH', 'AI', 'AJ', 'AK', 'AL', 'AM', 'AN', 'AO', 'AP', 'AQ', 'AR', 'AS', 'AT', 'AU', 'AV', 'AW', 'AX', 'AY', 'AZ',
'BA', 'BB', 'BC', 'BD', 'BE', 'BF', 'BG', 'BH', 'BI', 'BJ', 'BK', 'BL', 'BM', 'BN', 'BO', 'BP', 'BQ', 'BR', 'BS', 'BT', 'BU', 'BV', 'BW', 'BX', 'BY', 'BZ',
'CA', 'CB', 'CC', 'CD', 'CE', 'CF', 'CG', 'CH', 'CI', 'CJ', 'CK', 'CL', 'CM', 'CN', 'CO', 'CP', 'CQ', 'CR', 'CS', 'CT', 'CU', 'CV', 'CW', 'CX', 'CY', 'CZ'
];
$attributeLabelsKey = array_keys($attributeLabels);
$attributesLength = count($attributeLabelsKey);
//设置边框
//X表示Excel表格列,Y表示Excel表格行,A1表示第一行第一列
$x = $cellName[count($attributeLabels) - 1];
$y = count($list)+2;//多出2行 来写标题
//不同的订单 设置不同的模板
if($orderName == '衣服订单'||$orderName == '电子设备订单'){
$y = count($list)+1;
}
// \common\utils\Tool::debuglog('第一步',date('Ym').'.txt');
//Excel表格边框 设置单元格边框 从a1到最后
$objectPHPExcel->getActiveSheet()->getStyle('A1:'.$x.($y))->applyFromArray([
'borders' => [
'allborders' => [
'style' => \PHPExcel_Style_Border::BORDER_THIN,//粗的是thick
]
]
]);
//设置表格样式(背景颜色和边框线)
if(!empty($paramenterHeight)&& is_array($paramenterHeight)){
foreach ($paramenterHeight as $rownum => $value){
$rownum = $rownum + 1;
if(isset($value['border'])){
//Excel表格边框
$objectPHPExcel->getActiveSheet()->getStyle('A'.($rownum).':'.$x.($rownum))->applyFromArray([
'borders' => [
'bottom' => [//底部边框
'style' => $value['border'],//粗的是thick
]
]
]);
}
if(isset($value['backgroundcolor'])){
//excel表格背景颜色
$objectPHPExcel->getActiveSheet()->getStyle('A'.($rownum).':'.$x.($rownum))->applyFromArray([
'fill' => [
'type' => \PHPExcel_Style_Fill::FILL_SOLID,//单元格属性
'color'=> ['rgb' => $value['backgroundcolor']]//背景颜色
]
]);
}
}
}
//自定义控制高度
foreach ($setRowHeight as $k => $v) {
if ($k === 'height') {
$objectPHPExcel->getActiveSheet()->getDefaultRowDimension()->setRowHeight($v);
} elseif ($k == 'specific' && is_array($v)) {
foreach ($v as $row => $height) {
$objectPHPExcel->getActiveSheet()->getRowDimension($row)->setRowHeight($height);
}
}
}
//Excel第一行开始
//合并单元格
$nameVal = $filename;
if($orderName == '衣服订单'||$orderName == '电子设备订单'){
$nameVal = $orderName == '衣服订单'?'xxxx有限公司'."\r\n".'---':'xxxx有限公司'."\r\n".'电子设备订单';
//自动换行 对齐属性
$objectPHPExcel->getActiveSheet()->getStyle("A1:D1")->getAlignment()->setWrapText(true);
}
//设置A1名称值和样式
$objectPHPExcel->getActiveSheet()->mergeCells("A1:{$x}1");//指定要合并的单元格范围 (第一行)
$objectPHPExcel->getActiveSheet()->setCellValue('A1', $nameVal); //设置值
$objectPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);//设置垂直居中
$objectPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);//设置水平居中
$objectPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setName('Candara'); //设置字体
$objectPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setSize(14);//字体大小
$objectPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);//是否加粗
$imgPath = Yii::getAlias('@backend') . '/web/Mresources/images/logo.png';
if (file_exists($imgPath)) {
// 图片生成
$objDrawing = new \PHPExcel_Worksheet_Drawing();
$objDrawing->setPath($imgPath);
// 设置宽度高度
$objDrawing->setHeight(80);//照片高度
$objDrawing->setWidth(80); //照片宽度
//设置图片要插入的单元格
$objDrawing->setCoordinates('A1', $imgPath);
// 图片偏移距离
$objDrawing->setOffsetX(35);
$objDrawing->setOffsetY(35);
$objDrawing->setWorksheet($objectPHPExcel->getActiveSheet());
}
//Excel头部第一行设置内容和样式结束
//水平居中
$objectPHPExcel->setActiveSheetIndex(0)->getStyle("C")->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
//自动换行
$objectPHPExcel->getActiveSheet()->getStyle("C3:C$y")->getAlignment()->setWrapText(true);
if($orderName != '衣服订单'&&$orderName != '电子设备订单'){
//array_merge将两个数组的数据整合成一个数组
$list = array_merge([$attributeLabels], $list);
}
foreach ($list as $row) {
$n++; //计数
$row = (object)$row;
//$attributesLength代表属性的个数,$attributeLabelsKey代表属性的值
for ($i = 0; $i < $attributesLength; $i++) {
$key = $attributeLabelsKey[$i];
//判断$row是否存在,判断.jpg和.png
if (isset($row->$key)) {
if(is_array($row->$key) ){
if(isset($row->$key[0])){
$objectPHPExcel->getActiveSheet()->setCellValue($cellName[$i] . $n, $row->$key[0]); //设置值
}
$objectPHPExcel->getActiveSheet()->getStyle($cellName[$i] . $n)->getFont()->setBold(isset($row->$key['bold'])&&!empty($row->$key['bold'])?$row->$key['bold']: false);
}else{
if (strpos($row->$key, '.jpg') !== false || strpos($row->$key, '.png')) {
$imgPath = Yii::getAlias('@backend') . '/web/Mresources/' . $row->$key;
if (file_exists($imgPath)) {
$objDrawing = new \PHPExcel_Worksheet_Drawing();
$objDrawing->setPath($imgPath);
// 设置宽度高度
$objDrawing->setHeight(80);//照片高度
$objDrawing->setWidth(80); //照片宽度
//设置图片要插入的单元格
$objDrawing->setCoordinates($cellName[$i] . $n, $imgPath);
// 图片偏移距离
$objDrawing->setOffsetX(12);
$objDrawing->setOffsetY(12);
$objDrawing->setWorksheet($objectPHPExcel->getActiveSheet());
continue;
}
$row->$key = '';
}
$objectPHPExcel->getActiveSheet()->setCellValue($cellName[$i] . $n, $row->$key ?: ''); //设置值
}
$objectPHPExcel->getActiveSheet()->getStyle($cellName[$i] . $n)->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);//设置垂直居中
$objectPHPExcel->getActiveSheet()->getStyle($cellName[$i] . $n)->getAlignment()->setWrapText(true);
$objectPHPExcel->getActiveSheet()->getColumnDimension($cellName[$i])->setWidth(isset($setWidth[$i])&&$setWidth[$i]>0?$setWidth[$i]:25);//设置宽度
$objectPHPExcel->getActiveSheet()->getStyle($cellName[$i])->getFont()->setSize(14);
if (isset($row->$key['left'])) {
$objectPHPExcel->getActiveSheet()->getStyle($cellName[$i])->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
} else {
$objectPHPExcel->getActiveSheet()->getStyle($cellName[$i] . $n)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);//设置水平居中
}
if (isset($row->$key['merge'])&& is_array($row->$key['merge'])) { //衣服订单调整
$str = "";
foreach($row->$key['merge'] as $val){
$str = empty($str)?$val.$n:$str.":".$val.$n;
}
if(!empty($str)){
$objectPHPExcel->getActiveSheet()->mergeCells($str);// 指定第N行 B到D列合并
}
}
}
}
// 下载一个pdf文件
//header('Content-Type:application/pdf');
//header('Content-Disposition:attachment;filename="01simple.pdf"');
//header('Cache-Control:max-age=0');
//$objWriter =PHPExcel_IOFactory::createWriter($objPHPExcel,'PDF');
//$objWriter->save('php://output');
//数据结束
ob_end_clean();//清空缓存区并关闭输出缓存
ob_start();//在服务器打开一个缓存区来保存所有的数据
$excelName = iconv("utf-8", "gbk", $filename);//转码
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="' . $excelName . $date . '.xls"');
header('Cache-Control: max-age=0');
$objWriter = \PHPExcel_IOFactory::createWriter($objectPHPExcel, 'Excel5');
$objWriter->save('php://output');//输出excel文件,并自动下载
}
}
//适配多个模板
2.order.php
$f = ['cat_id' => '商品类型', 'goods_name'=>'商品名称' , 'goods_number'=>'数量'];
$fileName = $info->name.' -- 简易清单';
$setRowHeight = [
'height' => 30, //默认高度
'specific' => [ //指定高度
1 => 70 //第几行 => 高度
]
];
$setWidth = [
29,29,29
];
/*
$f = ['A'=>'', 'B' => '' , 'C' => '' , 'D'=>'' ];
$setRowHeight = [
'height' => 30, //默认高度
'specific' => [ //指定高度
1 => 70 //第几行 => 高度
]
];
$setWidth = [
20,15,25
];
//设置边框及背景颜色
$paramenterHeight = [
$num => ['border' => \PHPExcel_Style_Border::BORDER_THICK],//全边框样式
$titleRowNum => ['backgroundcolor' => 'BFBFBF'],//标题行数的背景颜色
$titleRowNum-1 => ['border' => \PHPExcel_Style_Border::BORDER_THICK]
]; */
//$list 为二维数组 字段对应 $f里面的key
ExportFile::exportExcel($list , $f , $fileName , $setRowHeight , $setWidth,'',$orderName = '简易清单');