if(!function_exists('exportExcel')){
//导出excel文件
function exportExcel($datas, $save_method='http', $save_path=''){
set_time_limit(0);
error_reporting(E_ALL);
ini_set('display_errors', TRUE);
ini_set('display_startup_errors', TRUE);
$excel_title = '箱子详情列表'.date('YmdHis',time());
$code = $datas['code'];
$totalBox = $datas['totalBox'];
$totalPackages = $datas['totalPackages'];
$totalWeight = $datas['totalWeight'];
$data = $datas['data'];
require_once ROOT_PATH.'/Classes/PHPExcel.php';
$objPHPExcel = new PHPExcel();
$objPHPExcel->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");
$row = array('A','B','C','D','E','F','G','H');
// 设置列宽
$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(12);
$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(23);
$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(10);
$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(18);
$objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(22);
$objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(23);
$objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(12);
$objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(15);
$styleThinBlackBorderOutline = array(
'borders' => array (
'outline' => array (
'style' => PHPExcel_Style_Border::BORDER_THIN,
'color' => array ('argb' => 'FF000000'), ),
),
);
for($i=0;$i<count($row);$i++){
//设置边框
for ($a = 1; $a < 4; $a++) {
$objPHPExcel->getActiveSheet()->getStyle($row[$i] . $a)->applyFromArray($styleThinBlackBorderOutline);
}
//设置居中
for($j=1;$j<500;$j++){
$objPHPExcel->getActiveSheet()->getStyle($row[$i].$j)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::VERTICAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle($row[$i].$j)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
}
//设置背景色
$objPHPExcel->getActiveSheet(0)->getStyle($row[$i].'1')->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID);
$objPHPExcel->getActiveSheet(0)->getStyle($row[$i].'1')->getFill()->getStartColor()->setARGB('00FFFF00');
$objPHPExcel->getActiveSheet(0)->getStyle($row[$i].'2:'.$row[$i].'3')->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID);
$objPHPExcel->getActiveSheet(0)->getStyle($row[$i].'2:'.$row[$i].'3')->getFill()->getStartColor()->setARGB('00DBE5F1');
//设置换行
$objPHPExcel->getActiveSheet()->getStyle($row[$i].'2')->getAlignment()->setWrapText(true);
}
// 设置行高
$objPHPExcel->getActiveSheet()->getDefaultRowDimension()->setRowHeight(28);
$objPHPExcel->getActiveSheet()->getRowDimension('2')->setRowHeight(66);
//定义表的前三行
$objPHPExcel->getActiveSheet()
->mergeCells('A1:B1')
->mergeCells('C1:D1')
->mergeCells('E1:F1')
->mergeCells('G1:H1')
->mergeCells('B2:B3')
->mergeCells('C2:C3')
->mergeCells('H2:H3')
->mergeCells('G2:G3');
$objPHPExcel->setActiveSheetIndex(0)
->setCellValue('A1', '客户代码 Code:')
->setCellValue('C1', $code)
->setCellValue('E1', '日期 Date:')
->setCellValue('G1', date('Y-m-d H:i:s',time()))
->setCellValue('A2', '总箱数:'.$totalBox."\n".'Total box')
->setCellValue('A3', '箱号')
->setCellValue('B2', '转运单号'."\n".'Tracking Number')
->setCellValue('C2', '订单号')
->setCellValue('D2', '总包裹数:'.$totalPackages."\n".'Total Packages')
->setCellValue('D3', '包裹重量(g)')
->setCellValue('E2', '总箱重(kg):'.$totalWeight."\n".'Total Weight')
->setCellValue('E3', '箱重(kg)')
->setCellValue('F2', '总箱体积重'."\n".'(长*宽*高cm/5000)')
->setCellValue('F3', '箱体积(长*宽*高cm)')
->setCellValue('G2', '头程'."\n".'发货方式')
->setCellValue('H2', '跟踪号'."\n".'Tracking Number');
//处理后面的逻辑
//当前所在的行数
$num = 4;
foreach($data as $k => $v){
//具体订单个数
$len = count($v);
$mergeLen = $num + $len-4;
$objPHPExcel->getActiveSheet()
->mergeCells('E'.$num.':E'.$mergeLen)
->mergeCells('F'.$num.':F'.$mergeLen)
->mergeCells('G'.$num.':G'.$mergeLen)
->mergeCells('H'.$num.':H'.$mergeLen);
$objPHPExcel->setActiveSheetIndex()
->setCellValue('A'.$num, $v['boxId'])
->setCellValue('E'.$num, $v['weightss'])
->setCellValue('G'.$num, $v['carrier_company']);
for($i=0;$i<$len-3;$i++){
$objPHPExcel->setActiveSheetIndex()
->setCellValue('A'.$num, $v['boxId'])
->setCellValueExplicit('B'.$num, $v[$i]['tracknumber'],PHPExcel_Cell_DataType::TYPE_STRING)
->setCellValue('C'.$num, $v[$i]['ebay_id'])
->setCellValue('D'.$num, $v[$i]['weight']);
$num++;
}
}
$objPHPExcel->getActiveSheet()->setTitle($excel_title);
$objPHPExcel->setActiveSheetIndex(0);
$filename = $excel_title.'.xls';
if($save_method == 'file'){
//保存到文件
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$full_path = $save_path.$filename;
//本地使用,线上注释
//$full_path = iconv('utf-8', 'gbk//IGNORE', $full_path);
if(is_file($full_path)) unlink($full_path);
$objWriter->save($full_path);
return $full_path;
}else{
ob_end_clean();
//直接在浏览器输出
header('Content-Type: application/vnd.ms-excel');
header("Content-Disposition: attachment;filename={$filename}");
header('Cache-Control: max-age=0');
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('php://output');
}
}
}