PHPExcel导出实例,含图片导出对齐方式背景颜色

导出文件实例 output.php 。该文件在其他文件中引入的,做了防止直接运行限制,如果单独做导出文件 还需引入

PHPExcel/PHPExcel.php


<?php
//防止直接运行
$_s = $_SERVER['PHP_SELF'];
$_s = strtolower(substr($_s,strripos($_s,"/")+1,-4));
if($_s=="output"){die();}

set_time_limit(0);
$totalcount = count($data);
//创建、设置Excel
$objPHPExcel = new PHPExcel();
$objPHPExcel->getProperties()
	->setCreator("7gee.cc")
	->setLastModifiedBy("7gee.cc")
	->setTitle("PHPExcel Test Document")
	->setSubject("PHPExcel Test Document")
	->setDescription("Test document for PHPExcel, generated using PHP classes.")
	->setKeywords("office PHPExcel php")
	->setCategory("Test result file");
//设置活跃表
$objPHPExcel->setActiveSheetIndex(0);
//设置宽度
$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(10);
$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(15);
$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(15);
$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(30);
$objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(20);
$objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(50);
$objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(10);
$objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(20);
$objPHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth(20);
$objPHPExcel->getActiveSheet()->getColumnDimension('J')->setWidth(20);
$objPHPExcel->getActiveSheet()->getColumnDimension('K')->setWidth(20);
$objPHPExcel->getActiveSheet()->getColumnDimension('L')->setWidth(20);
$objPHPExcel->getActiveSheet()->getColumnDimension('M')->setWidth(15);
$objPHPExcel->getActiveSheet()->getColumnDimension('N')->setWidth(15);
$objPHPExcel->getActiveSheet()->getColumnDimension('O')->setWidth(20);
$objPHPExcel->getActiveSheet()->getColumnDimension('P')->setWidth(50);

//设置默认行高
$objPHPExcel->getActiveSheet()->getDefaultRowDimension()->setRowHeight(26);

//设置E列(电话)为文本格式
$objPHPExcel->getActiveSheet()->getStyle('E')->getNumberFormat()->setFormatCode("@");
	//->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);
	
//第一行
$objPHPExcel->getActiveSheet()->mergeCells('A1:P1');//合并单元格
$objPHPExcel->getActiveSheet()->setCellValue('A1', "订单数据");
//第二行
$objPHPExcel->getActiveSheet()
			->setCellValue('A2', '#')
			->setCellValue('B2', '业务类型')
			->setCellValue('C2', '图样')
			->setCellValue('D2', '客户')
			->setCellValue('E2', '联系电话')
			->setCellValue('F2', '收件地址')
			->setCellValue('G2', '数量')
			->setCellValue('H2', '安排打印日期')
			->setCellValue('I2', '预计发货日期')
			->setCellValue('J2', '发货日期')
			->setCellValue('K2', '单号')
			->setCellValue('L2', '快递公司')
			->setCellValue('M2', '价格')
			->setCellValue('N2', '付款情况')
			->setCellValue('O2', '商务')
			->setCellValue('P2', '备注');
//设置行高
$objPHPExcel->getActiveSheet()->getRowDimension(2)->setRowHeight(26);
//设置填充的样式和背景色
$objPHPExcel->getActiveSheet()->getStyle("A2:P2")->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
$objPHPExcel->getActiveSheet()->getStyle("A2:P2")->getFill()->getStartColor()->setARGB('FF808080');
//对齐方式
$objPHPExcel->getActiveSheet()->getStyle("A2:O2")->getAlignment()
	->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);//水平方向上两端对齐
$objPHPExcel->getActiveSheet()->getStyle("A2:O2")->getAlignment()
	->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);//垂直方向上中间居中

$index = 3;
foreach($data as $k=>$v){
	//设置行高
	$objPHPExcel->getActiveSheet()->getRowDimension($index)->setRowHeight(60);
	//对齐方式
	$objPHPExcel->getActiveSheet()->getStyle("A{$index}:O{$index}")->getAlignment()
		->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);//水平方向上两端对齐
	$objPHPExcel->getActiveSheet()->getStyle("A{$index}:P{$index}")->getAlignment()
		->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);//垂直方向上中间居中

	$objPHPExcel->getActiveSheet()->setCellValue('A'.$index, $v['id']);
	$objPHPExcel->getActiveSheet()->setCellValue('B'.$index, $v['category']);

	/*实例化excel图片处理类*/
	$objDrawing = new PHPExcel_Worksheet_Drawing();//每张图都需要实例化一个
	$objDrawing->setPath($v['img_url']);/*设置图片路径 切记:只能是本地图片,不能带http*/
	$objDrawing->setOffsetX(1);/*设置图片所在单元格的起始坐标*/
	$objDrawing->setOffsetY(1);
	$objDrawing->setWidth(60);/*设置图片宽高*/
	$objDrawing->setHeight(60);
	$objDrawing->setCoordinates("C$index");/*插入单元格*/
	$objDrawing->setWorksheet($objPHPExcel->getActiveSheet());//不可少
	
	$objPHPExcel->getActiveSheet()->setCellValue("D{$index}", $v['cname']);
	$objPHPExcel->getActiveSheet()->setCellValue("E{$index}", $v['tel']);
	$objPHPExcel->getActiveSheet()->setCellValue("F{$index}", $v['addr']);
	$objPHPExcel->getActiveSheet()->setCellValue("G{$index}", $v['amount']);
	$time_print = intval($v['time_print'])<1?'':date('Y-m-d',$v['time_print']);
	$objPHPExcel->getActiveSheet()->setCellValue("H{$index}", $time_print);
	$time_fahuo0 =intval($v['time_fahuo0'])<1?'':date('Y-m-d',$v['time_fahuo0']);
	$objPHPExcel->getActiveSheet()->setCellValue("I{$index}", $time_fahuo0);
	$time_fahuo1 =intval($v['time_fahuo1'])<1?'':date('Y-m-d',$v['time_fahuo1']);
	$objPHPExcel->getActiveSheet()->setCellValue("J{$index}", $time_fahuo1);
	$objPHPExcel->getActiveSheet()->setCellValue("K{$index}", $v['express_no']);
	$objPHPExcel->getActiveSheet()->setCellValue("L{$index}", c_getexpress($v['express_id']));
	$objPHPExcel->getActiveSheet()->setCellValue("M{$index}", $v['price']);
	$objPHPExcel->getActiveSheet()->setCellValue("N{$index}", c_getpayed($v['status_pay']));
	$objPHPExcel->getActiveSheet()->setCellValue("O{$index}", $v['username']);
	$objPHPExcel->getActiveSheet()->setCellValue("P{$index}", $v['remark']);

	$index++;
}
// 表名称
$objPHPExcel->getActiveSheet()->setTitle('订单数据');
// 设置第一张表为活跃表
$objPHPExcel->setActiveSheetIndex(0);
//弹窗式保存
ob_end_clean();//清除缓冲区,避免乱码
header("Pragma: public");
header("Expires: 0");
header("Cache-Control:must-revalidate, post-check=0, pre-check=0");
header("Content-Type:application/force-download");
header("Content-Type:application/vnd.ms-execl");
header("Content-Type:application/octet-stream");
header("Content-Type:application/download");;
header('Content-Disposition:attachment;filename="data.xlsx"');
header("Content-Transfer-Encoding:binary");
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save('php://output');//$objWriter->save('shite.xlsx');//这种方式默认保存在php文件同一文件夹下