我要导出这个表格,导出到excel,怎么做?先创建一个模板,如下
模板如下
在里面设置好单元格的样式,这样就不用我们在代码中设置样式了,很方便
有个地方要注意,我们的模板中的商品只有一行,但是导出的时候有可能会有多个产品的,所以这里我们要插入新行,如下
但是这样的话,导出的效果如下
我们只是创建了一个新行,还没有对单元格就行合并,我们还要合并这些单元格,如下
可以了,但是内容没有自动换行,我们直接在模板中设置自动换行,如下
再来看看效果,发现还是不行,我们再来设置顶部对齐看看,如下
再来看看效果,如下
还是不行,可能是新插入的行,没有这个样式,我们就代码中设置样式,如下
再来看看效果, 如下
看,可以了,产品名称完整显示出来了,自动换行了
我们设置产品名称顶部向左对齐,如下
$excel->getActiveSheet()->getStyle('D'.$ind)->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_TOP)->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
再来看看效果,如下
看,可以
其实可以合并为一行,如下
最后导出代码如下
set_time_limit(0);
ini_set('memory_limit', '512M');
$fill_array = array('fill' => array('type' => \PHPExcel_Style_Fill::FILL_SOLID,'color' => array('rgb' => 'FFFFFF')));
$excel = \PHPExcel_IOFactory::load(public_path().'/excel_template/purchase_explorer_template.xlsx');
$excel->setActiveSheetIndex(0);
$excel->getActiveSheet()->setCellValue('I1', '采购单号:'.$purchase_num);
$excel->getActiveSheet()->setCellValue('I2', '创建时间:'.$create_time);
$excel->getActiveSheet()->setCellValue('I3', '采购员:'.$creator);
$excel->getActiveSheet()->setCellValue('A3', $purchase_num);
$excel->getActiveSheet()->setCellValue('E3', $company_name);
if(file_exists($img_url)){
$objDrawing = new \PHPExcel_Worksheet_Drawing();
$objDrawing->setPath($img_url);
$objDrawing->setHeight(85);
$objDrawing->setWidth(300);
$objDrawing->setCoordinates('A1');
$excel->getActiveSheet()->getStyle('A1')->applyFromArray($fill_array);
$objDrawing->setOffsetX(18);//偏移量
$objDrawing->setOffsetY(18);
$objDrawing->setRotation(15);
$objDrawing->getShadow()->setVisible(true);
$objDrawing->getShadow()->setDirection(36);
$objDrawing->setWorksheet($excel->getActiveSheet());//$sheet为当前工作表
$excel->getActiveSheet()->getRowDimension(1)->setRowHeight(80);
}
$excel->getActiveSheet()->setCellValue('A4', '采购方:'.$company_name);
$excel->getActiveSheet()->setCellValue('A5', '联系人:'.$contact);
$excel->getActiveSheet()->setCellValue('A6', '电话:');
$excel->getActiveSheet()->setCellValue('G4', '供货方:'.$supplier_name);
$excel->getActiveSheet()->setCellValue('G5', '联系人:'.$supply_contact);
$excel->getActiveSheet()->setCellValue('G6', '电话:'.$tel);
$excel->getActiveSheet()->setCellValue('G7', '地址:'.$address);
$excel->getActiveSheet()->setCellValue('A8', '订单交期:'.$eta);
/*$excel->getActiveSheet()->setCellValue('B13', $excel_data['fba_ticket']['amazon_fbaid']);
$excel->getActiveSheet()->setCellValue('G8', (isset($excel_data['company_tax_number']['eori']) ?' '.$excel_data['company_tax_number']['eori'] :''));
$excel->getActiveSheet()->setCellValue('G9', (isset($excel_data['company_tax_number']['eori']) ? $excel_data['company_tax_number']['eori'] : ''));*/
$ind = 10;
foreach($purchase_info->goods_info as $k=>$v){
$excel->getActiveSheet()->insertNewRowBefore($ind,1); //在操作行的号前加一空行,这空行的行号就变成了当前的行号
$excel->getActiveSheet()->mergeCells('B'.$ind.':C'.$ind);
$excel->getActiveSheet()->mergeCells('D'.$ind.':E'.$ind);
$excel->getActiveSheet()->mergeCells('I'.$ind.':L'.$ind);
$excel->getActiveSheet()->getStyle('D'.$ind)->getAlignment()->setWrapText(true)->setVertical(\PHPExcel_Style_Alignment::VERTICAL_TOP)->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
$excel->getActiveSheet()->getStyle('I'.$ind)->getAlignment()->setWrapText(true)->setVertical(\PHPExcel_Style_Alignment::VERTICAL_TOP)->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
$good_img_url = get_good_info_by_sku($v->sku,'image');
$excel->getActiveSheet()->setCellValue('A'.$ind, ($k+1));
$excel->getActiveSheet()->setCellValue('B'.$ind, $v->sku);
//插入图片
if(strtolower(substr($good_img_url, 0, 4)) != 'http'){ //本地图片
$url = public_path('/storage/'.$good_img_url);
if(file_exists($url)){
$objDrawing = new \PHPExcel_Worksheet_Drawing();
$objDrawing->setPath($url);
$objDrawing->setHeight(60);
$objDrawing->setWidth(60);
$objDrawing->setCoordinates('B'.$ind);
$excel->getActiveSheet()->getStyle('B'.$ind)->applyFromArray($fill_array);
$objDrawing->setOffsetX(8);//偏移量
$objDrawing->setOffsetY(8);
$objDrawing->setRotation(15);
$objDrawing->getShadow()->setVisible(true);
$objDrawing->getShadow()->setDirection(36);
$objDrawing->setWorksheet($excel->getActiveSheet());//$sheet为当前工作表
$excel->getActiveSheet()->getRowDimension($ind)->setRowHeight(80);
}
}else{
//网络图片
$objDrawing = new \PHPExcel_Worksheet_MemoryDrawing();
$img_res = getimagesize($good_img_url);
//$extension = pathinfo($v['image'],PATHINFO_EXTENSION);
$extension = substr($img_res['mime'],strripos($img_res['mime'],"/")+1);
$img = false;
if(strtolower($extension) == 'jpg' || strtolower($extension) == 'jpeg'){
$img = @imagecreatefromjpeg($good_img_url);
}elseif(strtolower($extension) == 'png'){
$img = @imagecreatefrompng($good_img_url);
}elseif(strtolower($extension) == 'gif'){
$img = @imagecreatefromgif($good_img_url);
}
if($img !== false){
$objDrawing->setImageResource($img);
$objDrawing->setRenderingFunction(\PHPExcel_Worksheet_MemoryDrawing::RENDERING_DEFAULT);//渲染方法
$objDrawing->setMimeType(\PHPExcel_Worksheet_MemoryDrawing::MIMETYPE_DEFAULT);
$objDrawing->setHeight(60);//照片高度
$objDrawing->setWidth(60); //照片宽度
$objDrawing->setCoordinates('B'.$ind);
$excel->getActiveSheet()->getStyle('B'.$ind)->applyFromArray($fill_array);
$objDrawing->setOffsetX(8);
$objDrawing->setOffsetY(8);
$objDrawing->setWorksheet($excel->getActiveSheet());
$excel->getActiveSheet()->getRowDimension($ind)->setRowHeight(80);
}
}
$excel->getActiveSheet()->setCellValue('D'.$ind, get_good_info_by_sku($v->sku,'name_cn'));
$excel->getActiveSheet()->setCellValue('F'.$ind, $v->num);
$excel->getActiveSheet()->setCellValue('G'.$ind, $v->price);
$excel->getActiveSheet()->setCellValue('H'.$ind, bcmul($v->num,$v->price,4));
$excel->getActiveSheet()->setCellValue('I'.$ind, $v->remark);
$ind++;
}
$excel->getActiveSheet()->setCellValue('A'.($ind+1), '合计体积:'.$area.'m³');
$excel->getActiveSheet()->setCellValue('G'.($ind+1), '合计重量:'.$weight.'kg');
$excel->getActiveSheet()->setCellValue('A'.($ind+2), '运费:'.$freight);
$excel->getActiveSheet()->setCellValue('G'.($ind+2), '支付方式:'.$pay_method);
$excel->getActiveSheet()->setCellValue('A'.($ind+3), '订单总金额(大写):'.$total_money);
$excel->getActiveSheet()->setCellValue('G'.($ind+3), '订单总金额(小写):'.$total_money_for_num.'RMB');
$excel->getActiveSheet()->setCellValue('A'.($ind+4), strip_tags($remark));
ob_end_clean();//清除缓冲区,避免乱码
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="'.$purchase_num.'.xls"');
header('Cache-Control: max-age=0');
// 如果是在ie9浏览器下,需要用到这个
header('Cache-Control: max-age=1');
// 如果你是在ie浏览器或者https下,需要用到这个
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 = new \PHPExcel_Writer_Excel5($excel);
$objWriter->save('php://output');
exit;
多出一行,怎么删除?如果我们立即删除的话,如下
看看效果, 如下
没有删除,我们把那个删除语句放在后面就行了,如下
放到下面一点,不要立即删除,放在后面一点,如下
再来导出看看,如下
看,删除了,可以,把那个删除语句放到后面一点就行了,哈哈哈