phpExcel——PHP导出excel方法
1、实例
public function export() { //1.加载PHPExcel类库 vendor("PHPExcel.PHPExcel"); vendor("PHPExcel.PHPExcel.Writer.Excel5"); vendor("PHPExcel.PHPExcel.Writer.Excel2007"); vendor("PHPExcel.PHPExcel.IOFactory"); //2.实例化PHPExcel类 $objPHPExcel = new \PHPExcel(); //3.激活当前的sheet表 $objPHPExcel->setActiveSheetIndex(0); //---------------------------------------------------------------------------------------------------------------------------------------- //中间填充表格内容 $objPHPExcel->getDefaultStyle()->getFont()->setSize(14); //设置默认字体大小 $objPHPExcel->getDefaultStyle()->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER); //设置水平居中 $objPHPExcel->getDefaultStyle()->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER); //设置垂直居中 $styleArray = array( 'borders' => array( 'allborders' => array( 'style' => \PHPExcel_Style_Border::BORDER_THIN,//细边框 ), ), ); //边框样式 $nn = 65+5; $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(5); $objPHPExcel->getActiveSheet()->mergeCells('B1:'.chr($nn).'1'); //合并单元格(第一行) $objPHPExcel->getActiveSheet()->getRowDimension('1')->setRowHeight(30); //设置第一行行高 $objPHPExcel->getActiveSheet()->getStyle( 'B1')->getFont()->setSize(16); //设置B1字体大小 $objPHPExcel->getActiveSheet()->mergeCells('B2:B3'); //合并单元格 $objPHPExcel->getActiveSheet()->mergeCells('C2:C3'); $objPHPExcel->getActiveSheet()->mergeCells('D2:D3'); $objPHPExcel->getActiveSheet()->mergeCells('E2:E3'); $objPHPExcel->getActiveSheet()->mergeCells('F2:F3'); $objPHPExcel->getActiveSheet()->setCellValue('B2', '机台'); $objPHPExcel->getActiveSheet()->setCellValue('C2', '班次'); $objPHPExcel->getActiveSheet()->setCellValue('D2', '运行时间'); $objPHPExcel->getActiveSheet()->setCellValue('E2', '产量'); $objPHPExcel->getActiveSheet()->setCellValue('F2', '正常生产时间'); $objPHPExcel->getActiveSheet()->getStyle('B2')->getFont()->setSize(11); $objPHPExcel->getActiveSheet()->getStyle('C2')->getFont()->setSize(11); $objPHPExcel->getActiveSheet()->getStyle('D2')->getFont()->setSize(11); $objPHPExcel->getActiveSheet()->getStyle('E2')->getFont()->setSize(11); $objPHPExcel->getActiveSheet()->getStyle('F2')->getFont()->setSize(11); $objPHPExcel->getActiveSheet()->getStyle( 'B2:B3')->applyFromArray($styleArray); //设置边框 $objPHPExcel->getActiveSheet()->getStyle( 'C2:C3')->applyFromArray($styleArray); $objPHPExcel->getActiveSheet()->getStyle( 'D2:D3')->applyFromArray($styleArray); $objPHPExcel->getActiveSheet()->getStyle( 'E2:E3')->applyFromArray($styleArray); $objPHPExcel->getActiveSheet()->getStyle( 'F2:F3')->applyFromArray($styleArray); //-------------------------------------------------------------------------------------------------------------------------------- $objPHPExcel->getActiveSheet()->setTitle('报表1'); //设置sheet的名称 $objPHPExcel->setActiveSheetIndex(0); //设置sheet的起始位置 $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5'); //通过PHPExcel_IOFactory的写函数将上面数据写出来 $PHPWriter = \PHPExcel_IOFactory::createWriter( $objPHPExcel,"Excel2007"); $name = '报表'; header('Content-Disposition: attachment;filename="'.$name.'.xls"'); //设置excel文件名称 header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'); return json($objWriter->save("php://output")); //表示在$path路径下面生成demo.xlsx文件 }
2、phpExcel常用操作
vendor("PHPExcel.PHPExcel"); //加载PHPExcel类库
objPHPExcel = new \PHPExcel(); //实例化PHPExcel类 $objPHPExcel->setActiveSheetIndex(0); //激活当前的sheet表 $objPHPExcel->getDefaultStyle()->getFont()->setSize(15); //设置默认字体大小 $objPHPExcel->getActiveSheet()->getRowDimension()->setRowHeight(15); //设置默认行高 $objPHPExcel->getActiveSheet()->getRowDimension('1')->setRowHeight(30); //设置第一行行高 $objPHPExcel->getActiveSheet()->getStyle( 'B1')->getFont()->setSize(20); //设置B1单元格字体大小 $objPHPExcel->getActiveSheet()->getStyle( 'B1')->getFont()->setBold(true); //字体加粗 //设置水平居中 $objPHPExcel->getDefaultStyle()->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER); //设置垂直居中 $objPHPExcel->getDefaultStyle()->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER); $objPHPExcel->getActiveSheet()->mergeCells('B1:D1'); //合并单元格 $objPHPExcel->getActiveSheet()->unmergeCells('A28:B28'); //拆分单元格 $objPHPExcel->getActiveSheet()->setCellValue('B1', '日生产异常报表'); //设置指定单元格的值 $styleArray = array( 'borders' => array( 'allborders' => array( 'style' => \PHPExcel_Style_Border::BORDER_THIN,//细边框 ), ), ); $objPHPExcel->getActiveSheet()->getStyle( 'B1:C1')->applyFromArray($styleArray); //设置边框 $objPHPExcel->getActiveSheet()->setTitle('报表1'); //设置sheet的名称 $objPHPExcel->setActiveSheetIndex(0); //设置sheet的起始位置 $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5'); //通过PHPExcel_IOFactory的写函数将上面数据写出来 $PHPWriter = \PHPExcel_IOFactory::createWriter( $objPHPExcel,"Excel2007");
$name = '报表';
header('Content-Disposition: attachment;filename="'.$name.'.xlsx"'); //设置excel文件的名称
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
return json($objWriter->save("php://output")); //表示在$path路径下面生成demo.xlsx文件
=======================================================================================================================================================
<?php error_reporting(E_ALL); //date_default_timezone_set('Europe/London'); /** PHPExcel */ require_once './classes/PHPExcel.php'; // Create new PHPExcel object $objPHPExcel = new PHPExcel(); // // 设置属性 // $objPHPExcel->getProperties()->setCreator("Maarten Balliauw") //创建人 // $objPHPExcel->getProperties()->setLastModifiedBy("Maarten Balliauw") //最后修改人 // $objPHPExcel->getProperties()->setTitle("Office 2007 XLSX Test Document") //标题 // $objPHPExcel->getProperties()->setSubject("Office 2007 XLSX Test Document") //题目 // $objPHPExcel->getProperties()->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.") //描述 // $objPHPExcel->getProperties()->setKeywords("office 2007 openxml php") //关键字 // $objPHPExcel->getProperties()->setCategory("Test result file"); //种类 // // // $objPHPExcel->setActiveSheetIndex(0); //设置当前的sheet // $objPHPExcel->getActiveSheet()->setTitle('Simple'); //设置sheet的name // //设置单元格的值 // $objPHPExcel->getActiveSheet()->setCellValue('A1', 'String'); // $objPHPExcel->getActiveSheet()->setCellValue('A2', 12); // $objPHPExcel->getActiveSheet()->setCellValue('A3', true); // $objPHPExcel->getActiveSheet()->setCellValue('C5', '=SUM(C2:C4)'); // $objPHPExcel->getActiveSheet()->setCellValue('B8', '=MIN(B2:C5)'); // $objPHPExcel->getActiveSheet()->mergeCells('A1:O1'); //合并单元格 // $objPHPExcel->getActiveSheet()->unmergeCells('A28:B28'); //分离单元格 // $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); // $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(5); // $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(20); // $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(10); // $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(8); // $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(8); // //保护cell // $objPHPExcel->getActiveSheet()->getProtection()->setSheet(true); // Needs to be set to true in order to enable any worksheet protection! // $objPHPExcel->getActiveSheet()->protectCells('A3:E13', 'PHPExcel'); // //设置格式 // echo date('H:i:s') . " Set cell number formats\n"; // $objPHPExcel->getActiveSheet()->getStyle('E4')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_CURRENCY_EUR_SIMPLE); // $objPHPExcel->getActiveSheet()->duplicateStyle( $objPHPExcel->getActiveSheet()->getStyle('E4'), 'E5:E13' ); // //设置宽width // $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setAutoSize(true); // $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(12); // //设置font // $objPHPExcel->getActiveSheet()->getStyle('B1')->getFont()->setName('Candara'); // $objPHPExcel->getActiveSheet()->getStyle('B1')->getFont()->setSize(20); // $objPHPExcel->getActiveSheet()->getStyle('B1')->getFont()->setBold(true); // $objPHPExcel->getActiveSheet()->getStyle('B1')->getFont()->setUnderline(PHPExcel_Style_Font::UNDERLINE_SINGLE); // $objPHPExcel->getActiveSheet()->getStyle('B1')->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_WHITE); // $objPHPExcel->getActiveSheet()->getStyle('E1')->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_WHITE); // $objPHPExcel->getActiveSheet()->getStyle('D13')->getFont()->setBold(true); // $objPHPExcel->getActiveSheet()->getStyle('E13')->getFont()->setBold(true); // //设置align // $objPHPExcel->getActiveSheet()->getStyle('D11')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT); // $objPHPExcel->getActiveSheet()->getStyle('D12')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT); // $objPHPExcel->getActiveSheet()->getStyle('D13')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT); // $objPHPExcel->getActiveSheet()->getStyle('A18')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_JUSTIFY); // //垂直居中 // $objPHPExcel->getActiveSheet()->getStyle('A18')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER); // //设置column的border // $objPHPExcel->getActiveSheet()->getStyle('A4')->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN); // $objPHPExcel->getActiveSheet()->getStyle('B4')->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN); // $objPHPExcel->getActiveSheet()->getStyle('C4')->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN); // $objPHPExcel->getActiveSheet()->getStyle('D4')->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN); // $objPHPExcel->getActiveSheet()->getStyle('E4')->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN); // //设置border的color // $objPHPExcel->getActiveSheet()->getStyle('D13')->getBorders()->getLeft()->getColor()->setARGB('FF993300'); // $objPHPExcel->getActiveSheet()->getStyle('D13')->getBorders()->getTop()->getColor()->setARGB('FF993300'); // $objPHPExcel->getActiveSheet()->getStyle('D13')->getBorders()->getBottom()->getColor()->setARGB('FF993300'); // $objPHPExcel->getActiveSheet()->getStyle('E13')->getBorders()->getTop()->getColor()->setARGB('FF993300'); // $objPHPExcel->getActiveSheet()->getStyle('E13')->getBorders()->getBottom()->getColor()->setARGB('FF993300'); // $objPHPExcel->getActiveSheet()->getStyle('E13')->getBorders()->getRight()->getColor()->setARGB('FF993300'); // //设置填充颜色 // $objPHPExcel->getActiveSheet()->getStyle('A1')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID); // $objPHPExcel->getActiveSheet()->getStyle('A1')->getFill()->getStartColor()->setARGB('FF808080'); // $objPHPExcel->getActiveSheet()->getStyle('B1')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID); // $objPHPExcel->getActiveSheet()->getStyle('B1')->getFill()->getStartColor()->setARGB('FF808080'); // //加图片 // $objDrawing = new PHPExcel_Worksheet_Drawing(); // $objDrawing->setName('Logo'); // $objDrawing->setDescription('Logo'); // $objDrawing->setPath('./images/officelogo.jpg'); // $objDrawing->setHeight(36); // $objDrawing->setWorksheet($objPHPExcel->getActiveSheet()); // $objDrawing = new PHPExcel_Worksheet_Drawing(); // $objDrawing->setName('Paid'); // $objDrawing->setDescription('Paid'); // $objDrawing->setPath('./images/paid.png'); // $objDrawing->setCoordinates('B15'); // $objDrawing->setOffsetX(110); // $objDrawing->setRotation(25); // $objDrawing->getShadow()->setVisible(true); // $objDrawing->getShadow()->setDirection(45); // $objDrawing->setWorksheet($objPHPExcel->getActiveSheet()); //处理中文输出问题 //需要将字符串转化为UTF-8编码，才能正常输出，否则中文字符将输出为空白，如下处理： // $str = iconv('gb2312', 'utf-8', $str); // //或者你可以写一个函数专门处理中文字符串： // function convertUTF8($str) // { // if(empty($str)) return ''; // return iconv('gb2312', 'utf-8', $str); // } $objWriter = new \PHPExcel_Writer_Excel5($objPHPExcel);//设置保存版本格式 //获取输出的文件 $list = [ 0=>[ 'name'=>'xiaoming', 'age'=>18 ], 1=>[ 'name'=>'xiaohong', 'age'=>17 ], 2=>[ 'name'=>'xiaohuang', 'age'=>20 ] ]; foreach ($list as $key => $value) { $i = $key+1;//表格是从1开始的 $objPHPExcel->getActiveSheet()->setCellValue('A'.$i, $value['name']);//这里是设置A1单元格的内容 $objPHPExcel->getActiveSheet()->setCellValue('B'.$i, $value['age']);////这里是设置B1单元格的内容 //以此类推，可以设置C D E F G看你需要了。 } //接下来当然是下载这个表格了，在浏览器输出就好了 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=文件名称.xls'); header("Content-Transfer-Encoding:binary"); $objWriter->save('文件名称.xls'); $objWriter->save('php://output'); exit;
/** * 导出 */ public function export(){ $id = input('id'); // $sbbh = 'JY05#'; vendor("PHPExcel.PHPExcel"); vendor("PHPExcel.PHPExcel.Writer.Excel5"); vendor("PHPExcel.PHPExcel.Writer.Excel2007"); vendor("PHPExcel.PHPExcel.IOFactory"); $objPHPExcel = new \PHPExcel(); $work_order = Db::name('sc_workorder') ->alias('a') ->field('a.id,a.work_order_no,a.process_num,a.product_id,b.leftaddress,b.rightaddress,b.name') ->join('sc_product b','a.product_id=b.id') ->find($id); $objPHPExcel->getDefaultStyle()->getFont()->setSize(10); $objPHPExcel->getDefaultStyle()->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(15); $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(6); $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(6); $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(6); $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(6); $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(6); $objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(6); $objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(6); $objPHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth(6); $objPHPExcel->getActiveSheet()->getColumnDimension('J')->setWidth(6); $objPHPExcel->getActiveSheet()->getColumnDimension('K')->setWidth(6); $objPHPExcel->getActiveSheet()->getColumnDimension('L')->setWidth(6); $objPHPExcel->getActiveSheet()->getColumnDimension('M')->setWidth(6); $objPHPExcel->getActiveSheet()->getColumnDimension('N')->setWidth(6); $objPHPExcel->getActiveSheet()->getColumnDimension('O')->setWidth(6); $objPHPExcel->getActiveSheet()->getColumnDimension('P')->setWidth(6); $objPHPExcel->getActiveSheet()->getColumnDimension('Q')->setWidth(6); $objPHPExcel->getActiveSheet()->getColumnDimension('R')->setWidth(6); $objPHPExcel->getActiveSheet()->getColumnDimension('R')->setWidth(6); $objPHPExcel->getActiveSheet()->getColumnDimension('S')->setWidth(6); $objPHPExcel->getActiveSheet()->getColumnDimension('T')->setWidth(6); $objPHPExcel->getActiveSheet()->mergeCells('A1:R1');//合并单元格（如果要拆分单元格是需要先合并再拆分的，否则程序会报错） $objPHPExcel->getActiveSheet()->mergeCells('S1:T1'); $objPHPExcel->getActiveSheet()->mergeCells('B2:E2'); $objPHPExcel->getActiveSheet()->mergeCells('F2:G2'); $objPHPExcel->getActiveSheet()->mergeCells('H2:M2'); $objPHPExcel->getActiveSheet()->mergeCells('O2:T2'); $objPHPExcel->getActiveSheet()->mergeCells('C3:E3'); $objPHPExcel->getActiveSheet()->mergeCells('G3:I3'); $objPHPExcel->getActiveSheet()->mergeCells('I3:T3'); $objPHPExcel->getActiveSheet()->mergeCells('B4:E4'); $objPHPExcel->getActiveSheet()->mergeCells('F4:I4'); $objPHPExcel->getActiveSheet()->mergeCells('J4:M4'); $objPHPExcel->getActiveSheet()->mergeCells('N4:Q4'); $objPHPExcel->getActiveSheet()->mergeCells('R4:T4'); $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true); $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setSize(20); $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $objPHPExcel->getActiveSheet()->getRowDimension('A1')->setRowHeight(60); $objPHPExcel->setActiveSheetIndex(0) ->setCellValue('A1','1111') ->setCellValue('A2','2222') ->setCellValue('F2','3333') ->setCellValue('N2','4444') ->setCellValue('A3','5555') ->setCellValue('B3','6666：') ->setCellValue('F3','7777：') ->setCellValue('A4','8888') ->setCellValue('A5','9999') ->setCellValue('A6','0000') ->setCellValue('B6','1111') ->setCellValue('C6','2222') ->setCellValue('D6','3333') ->setCellValue('E6','4444') ->setCellValue('F6','5555') ->setCellValue('G6','6666') ->setCellValue('H6','7777') ->setCellValue('I6','8888') ->setCellValue('J6','9999') ->setCellValue('K6','0000') ->setCellValue('L6','1111') ->setCellValue('M6','2222') ->setCellValue('N6','3333') ->setCellValue('O6','4444') ->setCellValue('P6','5555') ->setCellValue('Q6','6666') ->setCellValue('R6','7777') ->setCellValue('S6','8888') ->setCellValue('T6','9999'); $objPHPExcel->setActiveSheetIndex(0)->setCellValue('B2',$work_order['name']); $objPHPExcel->setActiveSheetIndex(0)->setCellValue('G2',datetime(time(),'Y-m-d')); $objPHPExcel->setActiveSheetIndex(0)->setCellValue('H2',$work_order['work_order_no']); $objPHPExcel->setActiveSheetIndex(0)->setCellValue('O2',datetime(time(),'Y-m-d')); $objPHPExcel->setActiveSheetIndex(0)->setCellValue('C2',''); $objPHPExcel->setActiveSheetIndex(0)->setCellValue('G2',''); // $objDrawing = new \PHPExcel_Worksheet_Drawing(); //必须每次重新实例化 // $objDrawing->setPath('./qrcode.png');//这里是相对路径 // $objDrawing->setHeight(60);//照片高度 // $objDrawing->setWidth(60); // $objDrawing->setCoordinates('S1'); // 图片偏移距离 // $objDrawing->setOffsetX(12); // $objDrawing->setOffsetY(0); // $objDrawing->setWorksheet($objPHPExcel->getActiveSheet()); $standard = db('sc_standard') ->where(['product_id'=>$work_order['product_id']]) ->select(); // $sampling = db('sc_sampling') // ->where([ // 'workorder_id'=>$id, // 'process_no_order'=>1, // ]) // ->order(['create_time'=>'asc']) // ->select(); // \think\Log::record($sampling); // // return 0; for ($j = 0;$j <count($standard);$j++){ $where_sampling = [ 'workorder_id'=>$id, // 'process_no_order'=>1, 'address_no'=>$j+1+($work_order['leftaddress']-1)*count($standard), // 'sbbh'=>$sbbh, 'standard_id'=>$standard[$j]['id'], ]; $sampling = db('sc_sampling') ->where($where_sampling) ->order(['process_no'=>'asc','process_no_order'=>'asc','address_no'=>'asc']) ->select(); // halt($sampling); if ($j == 0){ $DL = 'B'; $Da = 'C'; $Db = 'D'; $DE = 'E'; }else{ $DL = 'J'; $Da = 'K'; $Db = 'L'; $DE = 'M'; } $objPHPExcel->setActiveSheetIndex(0) ->setCellValue($DL.'4',$standard[$j]['color'].' 左') ->setCellValue($DL.'5','L:'.$standard[$j]['L']) ->setCellValue($Da.'5','a:'.$standard[$j]['a']) ->setCellValue($Db.'5','b:'.$standard[$j]['b']) ->setCellValue($DE.'5','/'); for($i=7;$i<count($sampling)+7;$i++){ $objPHPExcel->getActiveSheet()->setCellValue('A' . $i, $work_order['process_num'].'-'.$sampling[$i-7]['process_no'].'-'.$sampling[$i-7]['process_no_order'].' '.$sampling[$i-7]['bzbh']); $objPHPExcel->getActiveSheet()->setCellValue($DL . $i, $sampling[$i-7]['DL']); $objPHPExcel->getActiveSheet()->setCellValue($Da . $i, $sampling[$i-7]['Da']); $objPHPExcel->getActiveSheet()->setCellValue($Db . $i, $sampling[$i-7]['Db']); $objPHPExcel->getActiveSheet()->setCellValue($DE . $i, $sampling[$i-7]['DE']); } $where_sampling = [ 'workorder_id'=>$id, // 'process_no_order'=>1, 'address_no'=>$j+1+($work_order['rightaddress']-1)*count($standard), // 'sbbh'=>$sbbh, 'standard_id'=>$standard[$j]['id'], ]; $sampling = db('sc_sampling') ->where($where_sampling) ->order(['process_no'=>'asc','process_no_order'=>'asc','address_no'=>'asc']) ->select(); // halt($sampling); if ($j == 0){ $DL = 'F'; $Da = 'G'; $Db = 'H'; $DE = 'I'; }else{ $DL = 'N'; $Da = 'O'; $Db = 'P'; $DE = 'Q'; } $objPHPExcel->setActiveSheetIndex(0) ->setCellValue($DL.'4',$standard[$j]['color'].' 右') ->setCellValue($DL.'5','L:'.$standard[$j]['L']) ->setCellValue($Da.'5','a:'.$standard[$j]['a']) ->setCellValue($Db.'5','b:'.$standard[$j]['b']) ->setCellValue($DE.'5','/'); for($i=7;$i<count($sampling)+7;$i++){ $objPHPExcel->getActiveSheet()->setCellValue($DL . $i, $sampling[$i-7]['DL']); $objPHPExcel->getActiveSheet()->setCellValue($Da . $i, $sampling[$i-7]['Da']); $objPHPExcel->getActiveSheet()->setCellValue($Db . $i, $sampling[$i-7]['Db']); $objPHPExcel->getActiveSheet()->setCellValue($DE . $i, $sampling[$i-7]['DE']); } } /*--------------下面是设置其他信息------------------*/ $objPHPExcel->getActiveSheet()->setTitle('色差'); //设置sheet的名称 $objPHPExcel->setActiveSheetIndex(0); //设置sheet的起始位置 $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5'); //通过PHPExcel_IOFactory的写函数将上面数据写出来 $PHPWriter = \PHPExcel_IOFactory::createWriter( $objPHPExcel,"Excel2007"); header('Content-Disposition: attachment;filename="'.$work_order['work_order_no'].'.xlsx"'); header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'); $PHPWriter->save("php://output"); //表示在$path路径下面生成demo.xlsx文件 }
