function getExcel($fileName, $headArr, $data, $msg = '') { //对数据进行检验 if (empty($data) || !is_array($data)) { die("data must be a array"); } //检查文件名 if (empty($fileName)) { die("filename must be existed"); } //获取总列数 $totalColumn = count($headArr); if ($totalColumn > 78) { $charColumn = 'C' . chr($totalColumn - 78 + 64); } elseif ($totalColumn > 52) { $charColumn = 'B' . chr($totalColumn - 52 + 64); } elseif ($totalColumn > 26) { $charColumn = 'A' . chr($totalColumn - 26 + 64); } else { $charColumn = chr($totalColumn + 64); } // $charColumn = strtoupper(num2alpha($totalColumn)); $date = date("Y-m-d", time()); $fileName .= "_{$date}.xls"; //创建PHPExcel对象 $objPHPExcel = new \PHPExcel(); $objPHPExcel->setActiveSheetIndex(0); //设置当前的sheet 操作第一个工作表 $objActSheet = $objPHPExcel->getActiveSheet(); //添加数据 //设置表头 $key = ord("A"); $key2 = ord("@"); //@--64 // $key = foo('a'); foreach ($headArr as $v) { if ($key > ord("Z")) { $key2 += 1; $key = ord("A"); $colum = chr($key2) . chr($key); //超过26个字母时才会启用 } else { if ($key2 >= ord("A")) { $colum = chr($key2) . chr($key); //超过26个字母时才会启用 } else { $colum = chr($key); } } // $colum = chr($key); $objActSheet->setCellValue($colum . '1', $v); $objActSheet->getColumnDimension($colum)->setWidth(20); $key++; } //写入数据 if (!empty($msg)) { $column = 3; } else { $column = 2; } foreach ($data as $key => $rows) { //行写入 $span = ord("A"); $span2 = ord("@"); //@--64 foreach ($rows as $keyName => $value) { // 列写入 $judge = stristr($keyName, 'img'); if ($span > ord("Z")) { $span2 += 1; $span = ord("A"); $j = chr($span2) . chr($span); //超过26个字母时才会启用 // dump($j);die; } else { if ($span2 >= ord("A")) { $j = chr($span2) . chr($span); //超过26个字母时才会启用 } else { $j = chr($span); } } if ($judge === false) { $objActSheet->setCellValue($j . $column, $value); } else { $objActSheet->getRowDimension($column)->setRowHeight(60); //设置行高 if (is_array($value)) { $count = count($value); foreach ($value as $k => $v) { if (is_file($v)) { $m = $k + 1; $objDrawing = new \PHPExcel_Worksheet_Drawing(); //图片路径,项目目录下就行 $objDrawing->setPath($v); $objDrawing->setCoordinates($j . $column); $objDrawing->setWidth(50); $objDrawing->setHeight(50); //图片偏移距离 $objDrawing->setOffsetX($m * 10 + $k * 50); $objDrawing->setOffsetY(10); $objDrawing->setWorksheet($objPHPExcel->getActiveSheet()); } //设置列宽 } $objPHPExcel->getActiveSheet(0)->getColumnDimension($j)->setWidth(60); } else { if (is_file($value)) { $objDrawing = new \PHPExcel_Worksheet_Drawing(); $objDrawing->setPath($value); $objDrawing->setWidth(50); $objDrawing->setHeight(50); $objDrawing->setOffsetX(10); $objDrawing->setOffsetY(10); $objDrawing->setCoordinates($j . $column); $objDrawing->setWorksheet($objPHPExcel->getActiveSheet()); } else { $objActSheet->setCellValue($j . $column, $value); } } } $span++; } $column++; } $phpstyle = new \PHPExcel_Style_Color(); //表头变颜色 $objActSheet->getStyle('A1:' . $charColumn . '1')->getFont()->getColor()->setARGB($phpstyle::COLOR_BLUE); //设置颜色 //设置批注 if (!empty($msg)) { $objActSheet->getStyle('A2')->getFont()->getColor()->setARGB($phpstyle::COLOR_RED); $objActSheet->setCellValue('A2', $msg); //给单个单元格设置内容 $objActSheet->mergeCells('A2:' . $charColumn . '2'); //合并单元格 } //处理中文输出问题 $fileName = iconv("utf-8", "gb2312", $fileName); //接下来当然是下载这个表格了,在浏览器输出就好了 header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'); header("Content-Disposition: attachment;filename=\"$fileName\""); header('Cache-Control: max-age=0'); $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007'); $objWriter->save('php://output'); //文件通过浏览器下载 }
导出至excel表格
最新推荐文章于 2021-05-17 09:36:00 发布