加载需要的类文件
vendor("PHPExcel.PHPExcel");
vendor("PHPExcel.PHPExcel.IOFactory");
vendor("PHPExcel.PHPExcel.Writer.CSV");
$excel = new \PHPExcel();
表头:
$column = array(
'部门',
'采购单号', '采购内部单号', '采购快递单号', '采购渠道订单号', '内部采购时间','仓库', '供应商','广告员', '产品名', 'SKU', '采购单价','系统计算单价',
'采购数量', '采购金额', '总金额','应付金额','优惠金额','总数量','运费', '采购渠道','状态', '阿里巴巴账号','创建人', '创建时间', '内部采购时间','财务付款时间','备注'
);
导出的列超过26列:
//导出超过26列的问题解决
$key = ord("A");//A--65
$key2 = ord("@");//@--64
$j = 65;
foreach ($column as $col) {
if($key>ord("Z")){
$key2 += 1;
$key = ord("A");
$colum = chr($key2).chr($key);//超过26个字母时才会启用
}else{
if($key2>=ord("A")){
$colum = chr($key2).chr($key);//超过26个字母时才会启用
}else{
$colum = chr($key);
}
}
$excel->getActiveSheet()->setCellValue($colum . '1', $col);
++$j; $key += 1;
}
导出的列没有超过26列:
//导出没有超过26列
$j = 65;
foreach ($column as $col) {
$excel->getActiveSheet()->setCellValue(chr($j). '1', $col);
++$j;
}
组装写入excel的数组
foreach($list as $p){
$data[] = array(
$p['department_name'],
$p['purchase_no'],$p['inner_purchase_no'],$p['shipping_no'],$p['alibaba_no'],$p['inner_purchase_time'],$p['ware_name'],$p['sup_name'],'','',$p['product'],'','','','',$p['price'],$p['price']-$p['promotion_info'],$p['promotion_info'],$p['total'],$p['price_shipping'],$p['pur_channel_name'],$p['status_name'],$p['alibaba_name'],$p['user_nicename'],$p['created_at'],$p['inner_purchase_time'],empty($p['bank_time'])?'':date("Y-m-d",$p['bank_time']),$p['remark']
);
}
合并行:
$excel->getActiveSheet()->mergeCells("A" . ($num ? $num : $idx).":"."A" . (($num ? $num : $idx)+$count-1));
设置颜色:
$excel->getActiveSheet()->getStyle( 'A1:E1')->getFill()->getStartColor()->setARGB('FF808080');
整个导出excel代码:
public function export_search2() {
vendor("PHPExcel.PHPExcel");
vendor("PHPExcel.PHPExcel.IOFactory");
vendor("PHPExcel.PHPExcel.Writer.CSV");
$excel = new \PHPExcel();
$idx = 2;
$column = array(
'部门',
'采购单号', '采购内部单号', '采购快递单号', '采购渠道订单号', '内部采购时间','仓库', '供应商','广告员', '产品名', 'SKU', '采购单价','系统计算单价',
'采购数量', '采购金额', '总金额','应付金额','优惠金额','总数量','运费', '采购渠道','状态', '阿里巴巴账号','创建人', '创建时间', '内部采购时间','财务付款时间','备注'
);
//导出超过26列的问题解决 -- Lily 2017-11-16
$key = ord("A");//A--65
$key2 = ord("@");//@--64
$j = 65;
foreach ($column as $col) {
if($key>ord("Z")){
$key2 += 1;
$key = ord("A");
$colum = chr($key2).chr($key);//超过26个字母时才会启用
}else{
if($key2>=ord("A")){
$colum = chr($key2).chr($key);//超过26个字母时才会启用
}else{
$colum = chr($key);
}
}
$excel->getActiveSheet()->setCellValue($colum . '1', $col);
++$j; $key += 1;
}
//$list为要导出的数据数组
foreach($list as $p){
$data[] = array(
$p['a'],
$p['b'],$p['c'],$p['d'],$p['e'],$p['f'],$p['g'],$p['h'],'','',$p['k'],'','','','',$p['p'],$p['q']-$p['r'],$p['s'],$p['t'],$p['u'],$p['v'],$p['w'],$p['x'],$p['y'],$p['z'],$p['aa'],empty($p['ab'])?'':date("Y-m-d",$p['ab']),$p['ac']
);
}
if ($data) {
$k = 2;
$num = 2;
$sum = 2;
foreach ($data as $kk => $items) {
$j = 65;
$count = count($items[10]);
if ($count > 1) {
$excel->getActiveSheet()->mergeCells("A" . ($num ? $num : $idx).":"."A" . (($num ? $num : $idx)+$count-1));
$excel->getActiveSheet()->mergeCells("B" . ($num ? $num : $idx).":"."B" . (($num ? $num : $idx)+$count-1));
$excel->getActiveSheet()->mergeCells("C" . ($num ? $num : $idx).":"."C" . (($num ? $num : $idx)+$count-1));
$excel->getActiveSheet()->mergeCells("D" . ($num ? $num : $idx).":"."D" . (($num ? $num : $idx)+$count-1));
$excel->getActiveSheet()->mergeCells("E" . ($num ? $num : $idx).":"."E" . (($num ? $num : $idx)+$count-1));
$excel->getActiveSheet()->mergeCells("F" . ($num ? $num : $idx).":"."F" . (($num ? $num : $idx)+$count-1));
$excel->getActiveSheet()->mergeCells("G" . ($num ? $num : $idx).":"."G" . (($num ? $num : $idx)+$count-1));
$excel->getActiveSheet()->mergeCells("H" . ($num ? $num : $idx).":"."H" . (($num ? $num : $idx)+$count-1));
// $excel->getActiveSheet()->mergeCells("O" . ($num ? $num : $idx).":"."O" . (($num ? $num : $idx)+$count-1));
$excel->getActiveSheet()->mergeCells("P" . ($num ? $num : $idx).":"."P" . (($num ? $num : $idx)+$count-1));
$excel->getActiveSheet()->mergeCells("Q" . ($num ? $num : $idx).":"."Q" . (($num ? $num : $idx)+$count-1));
$excel->getActiveSheet()->mergeCells("R" . ($num ? $num : $idx).":"."R" . (($num ? $num : $idx)+$count-1));
$excel->getActiveSheet()->mergeCells("S" . ($num ? $num : $idx).":"."S" . (($num ? $num : $idx)
+$count-1));
$excel->getActiveSheet()->mergeCells("T" . ($num ? $num : $idx).":"."T" . (($num ? $num : $idx)
+$count-1));
$excel->getActiveSheet()->mergeCells("U" . ($num ? $num : $idx).":"."U" . (($num ? $num : $idx)
+$count-1));
$excel->getActiveSheet()->mergeCells("V" . ($num ? $num : $idx).":"."V" . (($num ? $num : $idx)
+$count-1));
$excel->getActiveSheet()->mergeCells("W" . ($num ? $num : $idx).":"."W" . (($num ? $num : $idx)
+$count-1));
$excel->getActiveSheet()->mergeCells("X" . ($num ? $num : $idx).":"."X" . (($num ? $num : $idx)
+$count-1));
$excel->getActiveSheet()->mergeCells("Y" . ($num ? $num : $idx).":"."Y" . (($num ? $num : $idx)
+$count-1));
$excel->getActiveSheet()->mergeCells("Z" . ($num ? $num : $idx).":"."Z" . (($num ? $num : $idx)
+$count-1));
$excel->getActiveSheet()->mergeCells("AA" . ($num ? $num : $idx).":"."AA" . (($num ? $num : $idx)
+$count-1));
$excel->getActiveSheet()->mergeCells("AB" . ($num ? $num : $idx).":"."AB" . (($num ? $num : $idx)
+$count-1));
$num = (($num ? $num : $idx)+$count);
} else {
$num += 1;
}
$j = 65;
$key1 = ord("A");//A--65
$key2 = ord("@");//@--64
foreach ($items as $key => $col) {
if($key1>ord("Z")){
$key2 += 1;
$key1 = ord("A");
$colum = chr($key2).chr($key1);//超过26个字母时才会启用
}else{
if($key2>=ord("A")){
$colum = chr($key2).chr($key1);//超过26个字母时才会启用
}else{
$colum = chr($key1);
}
}
if (is_array ($col)) {
$a = 0;
foreach($col as $c) {
$excel->getActiveSheet()->setCellValue("I" . $sum, $users[$c['a']]);
$excel->getActiveSheet()->setCellValue("J" . $sum, $c['b']);
$excel->getActiveSheet()->setCellValue("K" . $sum, $c['c']);
$excel->getActiveSheet()->setCellValueExplicit("L". $sum, $c['d']);
$excel->getActiveSheet()->setCellValueExplicit("M". $sum, $c['e']);
$excel->getActiveSheet()->setCellValueExplicit("N". $sum, $c['f']);
$excel->getActiveSheet()->setCellValueExplicit("O". $sum, $c['g']);
$a++;
$sum = $sum+1;
}
} else {
$bb = $sum;
if ($key > 14) {
$bb = $sum - $count;
}
if (!in_array($key,[11,12,13,14])){
if(in_array($key, array(1,4))){
$excel->getActiveSheet()->setCellValueExplicit($colum . $bb, $col);
}else{
$excel->getActiveSheet()->setCellValue($colum . $bb, $col);
}
}
}
$key1 += 1;
++$j;//横
}
++$idx;//列
++$k;
}
}
$excel->getActiveSheet()->setTitle(date('Y-m-d') . 'excel表名称.xlsx');
$excel->setActiveSheetIndex(0);
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="' . date('Y-m-d') . 'excel表名称.xlsx"');
header('Cache-Control: max-age=0');
$writer = \PHPExcel_IOFactory::createWriter($excel, 'Excel2007');
$writer->save('php://output');
exit();
}