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文件同一文件夹下