// 将base64图片保存为图片 $imglist = []; for($i = 1; $i <= 3; $i++){ if($info['piclist'.$i]){ $imglist[$i] = './uploads/jiaodi/imgs/'.date("His",time())."_".rand(1111,9999).'.png'; $base64_string = $info['piclist'.$i]; $base64_string= explode(',', $base64_string); $data= base64_decode($base64_string[1]); file_put_contents($imglist[$i], $data); } } // 导出excel $fileName = "技术交底.xls"; import('PHPExcel/PHPExcel', EXTEND_PATH); $objPHPExcel = new PHPExcel(); $objPHPExcel->getProperties(); // 设置Excel的属性； $objPHPExcel->getActiveSheet()->getDefaultRowDimension()->setRowHeight(20); // 设置默认行高为 70 $objPHPExcel->getActiveSheet()->getDefaultColumnDimension()->setWidth(4); // 设置默认列宽 $objPHPExcel->getActiveSheet()->getDefaultStyle()->getAlignment()->setWrapText(TRUE);// 设置默认自动换行 $objPHPExcel->getActiveSheet()->getDefaultStyle()->getFont()->setName('宋体')->setSize(10);// // 设置默认样式水平居中 $objPHPExcel->getActiveSheet()->getDefaultStyle()->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); // 设置默认样式垂直居中 $objPHPExcel->getActiveSheet()->getDefaultStyle()->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER); // 设置行高 $objPHPExcel->getActiveSheet()->getRowDimension('1')->setRowHeight(25); $objPHPExcel->getActiveSheet()->getRowDimension('2')->setRowHeight(25); $objPHPExcel->getActiveSheet()->getRowDimension('4')->setRowHeight(35.5); $objPHPExcel->getActiveSheet()->getRowDimension('5')->setRowHeight(35.5); // 设置样式 $objPHPExcel->getActiveSheet()->getStyle('A2:Z2')->getFont()->setSize(19); $objPHPExcel->getActiveSheet()->getStyle('A2:Z2')->getFont()->setBold(true); $objPHPExcel->getActiveSheet()->getStyle('A1:Z1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT); // 单元格赋值 $objPHPExcel->getActiveSheet()->mergeCells('A1:Z1')->setCellValue('A1', 'LJA-C4-1-1'); $objPHPExcel->getActiveSheet()->mergeCells('A2:Z2')->setCellValue('A2', '____________安全技术交底'); $objPHPExcel->getActiveSheet()->mergeCells('A3:Z3')->setCellValue('A3', ''); $objPHPExcel->getActiveSheet()->mergeCells('A4:D4')->setCellValue('A4', '单位工程名称'); $objPHPExcel->getActiveSheet()->mergeCells('E4:J4')->setCellValue('E4', ''); $objPHPExcel->getActiveSheet()->mergeCells('K4:N4')->setCellValue('K4', '施工单位'); $objPHPExcel->getActiveSheet()->mergeCells('O4:T4')->setCellValue('O4', ''); $objPHPExcel->getActiveSheet()->mergeCells('U4:V4')->setCellValue('U4', '日期'); $objPHPExcel->getActiveSheet()->mergeCells('W4:Z4')->setCellValue('W4', ' 年 月 日'); $objPHPExcel->getActiveSheet()->mergeCells('A5:D5')->setCellValue('A5', '施工部位'); $objPHPExcel->getActiveSheet()->mergeCells('E5:J5')->setCellValue('E5', ''); $objPHPExcel->getActiveSheet()->mergeCells('K5:N5')->setCellValue('K5', '施工内容'); $objPHPExcel->getActiveSheet()->mergeCells('O5:Z5')->setCellValue('O5', ''); $objPHPExcel->getActiveSheet()->mergeCells('A6:B19')->setCellValue('A6', '安全技术交底内容'); $objPHPExcel->getActiveSheet()->mergeCells('C6:Z19')->setCellValue('C6', '交底内容:'.$info['content']); $objPHPExcel->getActiveSheet()->mergeCells('A20:E21')->setCellValue('A20', '总承包单位有关技术人员签名'); $objPHPExcel->getActiveSheet()->mergeCells('F20:M21')->setCellValue('F20', ''); $objPHPExcel->getActiveSheet()->mergeCells('N20:R21')->setCellValue('N20', '总承包单位专职安全生产管理人员签名'); $objPHPExcel->getActiveSheet()->mergeCells('S20:Z21')->setCellValue('S20', $info['bname']); $objPHPExcel->getActiveSheet()->mergeCells('A22:B28')->setCellValue('A22', '分包单位工程项目相关技术人员签名'); $objPHPExcel->getActiveSheet()->mergeCells('C22:Z28')->setCellValue('C22', $info['sname']); $objPHPExcel->getActiveSheet()->mergeCells('A29:Z35')->setCellValue('A29', ''); // 绘制图像 foreach($imglist as $key=>$vals){ $wz = ord("B")+$key*5; $objDrawing = new PHPExcel_Worksheet_Drawing(); $objDrawing->setPath($vals); $objDrawing->setHeight(80); $objDrawing->setWidth(80); $objDrawing->setCoordinates(chr($wz).'30'); $objDrawing->setOffsetX(10); $objDrawing->setOffsetY(10); $objDrawing->setWorksheet($objPHPExcel->getActiveSheet()); } $fileName = iconv("utf-8", "gb2312", $fileName); // 重命名表 $objPHPExcel->setActiveSheetIndex(0); // 设置活动单指数到第一个表,所以Excel打开这是第一个表 ob_end_clean(); //会清除缓冲区的内容，并将缓冲区关闭，但不会输出内容。 ob_start(); //在服务器打开一个缓冲区来保存所有的输出。所以在任何时候使用echo ，输出都将被加入缓冲区中，直到程序运行结束或者使用ob_flush()来结束。然后在服务器中缓冲区的内容才会发送到浏览器，由浏览器来解析显示。 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'); // 文件通过浏览器下载
// 删除生成的图片
foreach($imglist as $key=>$vals){
unlink($vals);
}
exit();
下面是总结的几个使用方法 include 'PHPExcel.php'; include 'PHPExcel/Writer/Excel2007.php'; //或者include 'PHPExcel/Writer/Excel5.php'; 用于输出.xls的创建一个excel $objPHPExcel = new PHPExcel(); 保存excel—2007格式$objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel); //或者$objWriter = new PHPExcel_Writer_Excel5($objPHPExcel); 非2007格式 $objWriter->save("xxx.xlsx"); 直接输出到浏览器 $objWriter = new PHPExcel_Writer_Excel5($objPHPExcel); 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="resume.xls"'); header("Content-Transfer-Encoding:binary"); $objWriter->save('php://output'); ——————————————————————————————————————– 设置excel的属性： 创建人 $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"); ——————————————————————————————————————– 设置当前的sheet $objPHPExcel->setActiveSheetIndex(0); 设置sheet的name $objPHPExcel->getActiveSheet()->setTitle('Simple'); 设置单元格的值 $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('A18:E22'); 分离单元格 $objPHPExcel->getActiveSheet()->unmergeCells('A28:B28'); 保护 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'); 设置格式 // Set cell number formatsecho date('H:i:s') . " Set cell number formats "; $objPHPExcel->getActiveSheet()->getStyle('E4')->getNumberFormat() ->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_CURRENCY_EUR_SIMPLE); $objPHPExcel->getActiveSheet()->duplicateStyle( $objPHPExcel->getActiveSheet()->getStyle('E4'), 'E5:E13' ); 设置宽width// Set column widths$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); }//从数据库输出数据处理方式从数据库读取数据如： $db = new Mysql($dbconfig); $sql = "SELECT * FROM 表名"; $row = $db->GetAll($sql); // $row 为二维数组 $count = count($row); for ($i = 2; $i <= $count+1; $i++) { $objPHPExcel->getActiveSheet()->setCellValue('A' . $i, convertUTF8($row[$i-2][1])); $objPHPExcel->getActiveSheet()->setCellValue('B' . $i, convertUTF8($row[$i-2][2])); $objPHPExcel->getActiveSheet()->setCellValue('C' . $i, convertUTF8($row[$i-2][3])); $objPHPExcel->getActiveSheet()->setCellValue('D' . $i, convertUTF8($row[$i-2][4])); $objPHPExcel->getActiveSheet()->setCellValue('E' . $i, convertUTF8(date("Y-m-d", $row[$i-2][5]))); $objPHPExcel->getActiveSheet()->setCellValue('F' . $i, convertUTF8($row[$i-2][6])); $objPHPExcel->getActiveSheet()->setCellValue('G' . $i, convertUTF8($row[$i-2][7])); $objPHPExcel->getActiveSheet()->setCellValue('H' . $i, convertUTF8($row[$i-2][8])); } 在默认sheet后，创建一个worksheetecho date('H:i:s') . " Create new Worksheet object "; $objPHPExcel->createSheet(); $objWriter = PHPExcel_IOFactory::createWriter($objExcel, 'Excel5'); $objWriter-save('php://output');
现在php已经使用：
composer中使用PhpSpreadsheet，原来的phpexcel已经不再更新；
1 <?php 2 3 use PhpOfficePhpSpreadsheetSpreadsheet; 4 use PhpOfficePhpSpreadsheetIOFactory; 5 use PhpOfficePhpSpreadsheetReaderXls; 6 7 /** 8 * 导出函数excel 9 */ 10 protected function exported($fileName = '', $headArr = [], $data = []){ 11 $spreadsheet = new Spreadsheet(); 12 $sheet = $spreadsheet->getActiveSheet(); 13 14 // 数据处理 15 $key = ord("A"); // 设置表头 16 foreach ($headArr as $v) { 17 $colum = chr($key); 18 $sheet->setCellValue($colum . '1', $v); 19 $key += 1; 20 } 21 $column = 2; 22 23 foreach ($data as $key => $rows) { // 行写入 24 $span = ord("A"); 25 foreach ($rows as $keyName => $value) { // 列写入 26 $sheet->setCellValue(chr($span) . $column, $value); 27 $span++; 28 } 29 $column++; 30 } 31 32 // 输出下载xls文件 33 $fileName .= "_" . date("Y_m_d", time()) . ".xls"; 34 $fileName = iconv("utf-8", "gb2312", $fileName); // 重命名表 35 ob_end_clean(); 36 ob_start(); 37 header('Content-Type: application/vnd.ms-excel'); 38 header("Content-Disposition: attachment;filename=$fileName"); 39 header('Cache-Control: max-age=0'); 40 41 $objWriter = IOFactory::createWriter($spreadsheet, 'Xls'); 42 $objWriter->save('php://output'); // 文件通过浏览器下载 43 } 44 45 /** 46 * 导入excel 47 */ 48 public function imports(){ 49 if(request()->file('file')){ 50 $file = request()->file('file')->getInfo(); 51 }else{ 52 $res['code']=1; 53 $res['msg']='没有上传文件'; 54 return json($res); 55 } 56 57 $reader = new Xls(); 58 $spreadsheet = $reader->load($file['tmp_name']); 59 $sheet = $spreadsheet->getActiveSheet(); 60 $highestRow = $sheet->getHighestRow(); //获取总行数 61 62 $data = []; 63 64 for($i = 2; $i <=$highestRow; $i++){ 65 $arr = []; 66 $arr['name'] = $sheet->getCell('A'.$i)->getValue(); 67 $zbget = $sheet->getCell('B'.$i)->getValue(); 68 $arr['gszhw'] = $sheet->getCell('C'.$i)->getValue(); 69 $sexget = $sheet->getCell('D'.$i)->getValue(); 70 $arr['phone'] = $sheet->getCell('E'.$i)->getValue(); 71 $arr['gname'] = $sheet->getCell('F'.$i)->getValue(); 72 $arr['address'] = $sheet->getCell('G'.$i)->getValue(); 73 $hyeget = $sheet->getCell('H'.$i)->getValue(); 74 $zhzmmget = $sheet->getCell('I'.$i)->getValue(); 75 $shhzhwget = $sheet->getCell('J'.$i)->getValue(); 76 $bzhangget = $sheet->getCell('K'.$i)->getValue(); 77 $rhtimeget = $sheet->getCell('L'.$i)->getValue(); 78 $arr['rhtime'] = strtotime($rhtimeget); 79 $data[] = $arr; 80 } 81 82 // var_dump($data); 83 $res = db('users')->insertAll($data); 84 }