phpexcel导出大量数据合并单元格_【PHP】通过PHPExcel导出比较复杂的表格

[PHP] 纯文本查看 复制代码/**

* excel文件导出

*

* [url=home.php?mod=space&uid=952169]@Param[/url] array $data 需要生成excel文件的数组

* $data = [

* [NULL, 2010, 2011, 2012],//列名

* ['Q1', 12, 15, 21],

* ['Q2', 56, 73, 86],

* ['Q3', 52, 61, 69],

* ['Q4', 30, 32, 0],

* ];

* @param string $filename 生成的excel文件名,示例数据：

* @param array $config 配置选项

* $config = [

* 'title_size' //标题字体大小

* 'title_height' //标题高度

* 'row_height' //行高

* 'form_pend' //从第几行填充数据，默认2

* 'free' => 'A4' //冻结窗口位置 A4

* 'iscell' => true //是否合并列

* 'cell_init_row' => //初始从第几行合并 1

* 'cell_init_col' => //合并列初始 A

* 'cell' => [] //合并列数 ['title'=>1,'title'=>2,'title'=>3,'title'=>4,'title'=>5...];

* ]

*/

function export_excel($data, $title = '', $filename = '*.xls', $config = []){

set_time_limit(0);

Vendor('PHPExcel.PHPExcel');

$filename = ($filename ?: $title) . '.xls';

$phpexcel = new PHPExcel();

$phpexcel->getProperties()->setCreator("Maarten Balliauw")->setLastModifiedBy("Maarten Balliauw")->setTitle("Office 2007 XLSX Test Document")->setSubject("Office 2007 XLSX Test Document")->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.")->setKeywords("office 2007 openxml php")->setCategory("Test result file");

$phpexcel->getActiveSheet()->setTitle('Sheet1');

// 默认表格样式居中

$phpexcel->getActiveSheet()->getDefaultStyle()->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

//生成字母

for ($i = 0, $len = count($data[0]); $i<= $len; $i++){

$y = $i / 26;

if($y >= 1){

$y = intval($y);

$cellKey[] = chr($y+64) . chr($i-$y*26 + 65);

}else{

$cellKey[] = chr($i+65);

}

}

$len = [];

foreach($data[0] as $val){

array_push($len, strlen($val)+1);

}

//从第几行开始换行

$rows = $config['form_pend'] ? $config['form_pend'] : 2;

for($i = 0; $i < count($data[0]); $i ++){

$phpexcel->getActiveSheet()->getColumnDimension($cellKey[$i])

//->setAutoSize(true); //设置列宽自适应

->setWidth($len[$i]);

//单元格内自动换行

$phpexcel->getActiveSheet()->getStyle($cellKey[$i])->getAlignment()->setWrapText(true);

$phpexcel->getActiveSheet()->getStyle($cellKey[$i])->getAlignment()

->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER) // 水平居中

->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER); // 垂直居中

$phpexcel->getActiveSheet()->getStyle($cellKey[$i].$rows)->getFont()->setBold(true)->setSize(15);

}

$phpexcel->getActiveSheet()->getRowDimension($rows)->setRowHeight(20); // 设置第n的行高

// 标题设置

if(! empty($title)){

$phpexcel->getDefaultStyle()->getFont()->setName('宋体');

$phpexcel->getDefaultStyle()->getFont()->setSize(12);

$phpexcel->getActiveSheet()->mergeCells('A1:' . $cellKey[count($data[0]) - 1] . '1')// 合并指定列

->getStyle('A1')->getFont()->setBold(true)->setSize($config['title_size']?:12);

$phpexcel->getActiveSheet()->freezePane($config['free']?:'A3'); // 冻结窗口

if($config['iscell']){

$init = 0;

$cell_start = $config['cell_init_col'].$config['cell_init_row'];

static $len = 1;

static $col = null;

foreach($config['cell'] as $key => $num){

$start = $init == 0 ? $cell_start : $cellKey[$len].$config['cell_init_row'];

$end = $cellKey[$len+$num-1].$config['cell_init_row'];

$col = $cellKey[$len].'2';

$len += $num;

++$init;

//合并指定列

$phpexcel->getActiveSheet()->mergeCells($start.':'.$end)->setCellValue($col,$key);

$phpexcel->getActiveSheet()->getStyle($col)->getFont()->setBold(true)->setSize(15);

}

//exit();

//当存在多级行表格，最多3行

$phpexcel->getActiveSheet()->getRowDimension(2)->setRowHeight(40); // 设置第2行高

$phpexcel->getActiveSheet()->getStyle('A3')->getAlignment()

->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER) // 水平居中

->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER); // 垂直居中

}

$phpexcel->setActiveSheetIndex(0)->setCellValue('A1', $title); // 设置标题

$phpexcel->getActiveSheet()->getRowDimension(1)->setRowHeight($config['title_height']?:50); // 设置标题行高

$phpexcel->getActiveSheet()->getStyle('A1')->getAlignment()

->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER) // 水平居中

->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER); // 垂直居中

// formArray(填充的数据,列值为空默认填充空,从第几行开始)

$phpexcel->getActiveSheet()->fromArray($data, null, 'A'.($config['form_pend']?:2));

}else{

$phpexcel->getActiveSheet()->fromArray($data);

}

// 合并指定列

// $phpexcel->getActiveSheet()->mergeCells('A1:R1');

// $phpexcel->setActiveSheetIndex(0);

header('Content-Type: application/vnd.ms-excel');

header('Content-Disposition: attachment;filename=' . $filename);

header('Cache-Control: max-age=0');

header('Cache-Control: max-age=1');

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($phpexcel, 'Excel5');

$objwriter->save('php://output');

exit();

}