如图 最近要做一个这样的导出,一个团长对应一个sheet,然后一键导出
使用PhpOffice\PhpSpreadsheet实现 直接上代码
public function xtexport($data_array) { $name = '团长' . date("Y-m-d", time()); $spreadsheet = new Spreadsheet(); foreach ($data_array as $key => $data) { $this->opSheet($spreadsheet,$key,$data); } header('Content-Type: application/vnd.ms-excel'); header('Content-Disposition: attachment;filename="' . $name . '.xlsx"'); header('Cache-Control: max-age=0'); $writer = new Xlsx($spreadsheet); $writer->save('php://output'); //删除清空: $spreadsheet->disconnectWorksheets(); unset($spreadsheet); exit; }
然后就是处理sheet的多循环
public function opSheet($spreadsheet,$n, $data) { $spreadsheet->createSheet();//创建sheet $objActSheet = $spreadsheet->setActiveSheetIndex($n);//设置当前的活动sheet $keys = $data['rows'][0];//这是你的数据键名 $count = count($keys);//计算你所占的列数 $infoNum = ceil(count($data['info']) / 2);//求k-v值的所占行数 $infoStart = $infoNum + 2 ;//下面的详细信息的开始行数 $cellName = array('A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z', 'AA', 'AB', 'AC', 'AD', 'AE', 'AF', 'AG', 'AH', 'AI', 'AJ', 'AK', 'AL', 'AM', 'AN', 'AO', 'AP', 'AQ', 'AR', 'AS', 'AT', 'AU', 'AV', 'AW', 'AX', 'AY', 'AZ'); $sheet = $spreadsheet->getActiveSheet($n)->setTitle($data['info']['社区团长姓名:']);//设置sheet的名称 $spreadsheet->getActiveSheet($n)->mergeCells('A1:' . $cellName[$count - 1] . '1'); //合并单元格 $spreadsheet->getActiveSheet($n)->getStyle('A1')->getFont()->setSize(20); //设置title的字体大小 $spreadsheet->getActiveSheet($n)->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); //居中 $spreadsheet->getActiveSheet($n)->getStyle('A')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); //居中 $spreadsheet->getActiveSheet($n)->getStyle('B')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); //居中 $spreadsheet->getActiveSheet($n)->getStyle('C')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); //居中 $spreadsheet->getActiveSheet($n)->getStyle('D')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); //居中 $spreadsheet->getActiveSheet($n)->getStyle('E')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); //居中 $spreadsheet->getActiveSheet($n)->getStyle('F')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); //居中 $spreadsheet->getActiveSheet($n)->getStyle('G')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); //居中 $spreadsheet->getActiveSheet($n)->getStyle("$infoStart")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); //居中 $spreadsheet->getActiveSheet($n)->getStyle("$infoStart")->getFont()->setBold(true); //栏加粗 $objActSheet->setCellValue('A1', $data['title']); //设置每个sheet中的名称title foreach ($data['rows'] as $key => $item) { //循环设置单元格: //$key+$infoStart,因为第一行是表头,所以写到表格时 从第数据行开始写 for ($i = 65; $i < $count + 65; $i++) { //数字转字母从65开始: //$sheet->setCellValue(strtoupper(chr($i)) . ($key + "$infoStart"), $item[[$keys][$i - 65]]); $sheet->setCellValue(strtoupper(chr($i)) . ($key+"$infoStart"), $item[$i - 65]); $spreadsheet->getActiveSheet($n)->getColumnDimension(strtoupper(chr($i)))->setWidth(20); //固定列宽 } } $rowNumber = 1; $infoIndex = 0; foreach ($data['info'] as $key => $value) { if ($infoIndex % 2 == 0) { $rowNumber++; $infoCellName1 = 'A' . $rowNumber; $infoCellMegreRange = 'B' . $rowNumber . ':C' . $rowNumber; $infoCellName2 = 'B' . $rowNumber; } else { $infoCellName1 = 'D' . $rowNumber; $infoCellMegreRange = 'E' . $rowNumber . ':F' . $rowNumber; $infoCellName2 = 'E' . $rowNumber; } $spreadsheet->setActiveSheetIndex($n)->setCellValue($infoCellName1, $key); $spreadsheet->getActiveSheet($n)->mergeCells($infoCellMegreRange); $spreadsheet->setActiveSheetIndex($n)->setCellValue($infoCellName2, $value); $infoIndex++; } }
每个人的数据格式不一致,所以自行改一改,我的数据格式是这样可以参考下
public function xtexport() { $data = array( array( 'title' => 'AAA', 'info' => [ '社区团长店铺地址:' => 'AAA', '社区团长姓名:' => 'AAA', '收货地址:' => 'AAA', '联系方式:' => 'AAA', '配送日期:' => 'AAA', '手打阿达:' => 'AAA', '手动蝶阀:' => 'AAA' ], 'rows' => [ [ 'id', 'name', 'sex', 'phone', 'res', 'aaa', 'bbb' ], [ 1111,2222,3333,4444,5555,6666,7777 ], [ 111,222,333,444,555,666,777 ] ] ), array( 'title' => 'BBBB', 'info' => [ '社区团长店铺地址:' => 'BBBB', '社区团长姓名:' => 'BBBB', '收货地址:' => 'BBBB', '联系方式:' => 'BBBB', '配送日期:' => 'BBBB', '手打阿达:' => 'BBBB', '手动蝶阀:' => 'BBBB' ], 'rows' => [ [ 'id', 'name', 'sex', 'phone', 'res', 'aaa', 'bbb' ], [ 1,2,3,4,5,6,7 ], [ 11,22,33,44,55,66,77 ] ] ) ); model('Excel')->xtexport($data); }
导出如图:
参考地址 https://www.programminghunter.com/article/62071254396/
多表头 多sheet 示例
public function getExcelHeader($num = 100) { // 计算表格列 $col = ['A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z']; if ($num > 730) { $newCol = []; for ($j = 0; $j <= 26; $j++) { for ($h = 0; $h <= 25; $h++) { for ($i = 0; $i <= 25; $i++) { if ($j == 0 && $h == 0) {$newCol[] = $col[$i]; } elseif ($j == 0) {$newCol[] = $col[$h] . $col[$i]; } else {$newCol[] = $col[$j - 1] . $col[$h] . $col[$i]; } } } } } else { for ($j = 0; $j <= 26; $j++) { for ($i = 0; $i <= 25; $i++) { if ($j == 0) { $newCol[] = $col[$i]; } else { $newCol[] = $col[$j - 1] . $col[$i]; } } } } return $newCol; } // 多表头数据处理 , 数据结构 // $data = [ // [ // "headerData": [ // [ // "x": "A1", // "y": "B1", // "name": "yFieldNam_1" // ], // [ // "x": "A2", // "y": "B2", // "name": "yFieldNam_2" // ], // [ // "x": "C1", // "y": "D1", // "name": "首席运营官" // ], // [ // "x": "E1", // "y": "L1", // "name": "饲养员" // ], // [ // "x": "C2", // "y": "D2", // "name": "2010年3季度" // ], // [ // "x": "E2", // "y": "F2", // "name": "2012年2季度" // ], // [ // "x": "G2", // "y": "H2", // "name": "2010年2季度" // ], // [ // "x": "I2", // "y": "J2", // "name": "2009年3季度" // ], // [ // "x": "K2", // "y": "L2", // "name": "2009年2季度" // ], // [ // "x": "M1", // "y": "N2", // "name": "汇总" // ], // [ // "x": "A9", // "y": "B9", // "name": "汇总" // ] // ], // "mergeCells": [ // "A1:B1", // "A2:B2", // "C1:D1", // "E1:L1", // "C2:D2", // "E2:F2", // "G2:H2", // "I2:J2", // "K2:L2", // "M1:N2", // "A9:B9" // ], // "row": [ // [], // [] // ], // "yFields": [ // [], // [], // ] // ] // ] public function opSheet($spreadsheet, $n, $data) { $spreadsheet->createSheet(); //创建sheet $objActSheet = $spreadsheet->setActiveSheetIndex($n); //设置当前的活动sheet $keys = $data['row'][0]; //这是你的数据键名 $count = count($keys); //计算你所占的列数 $infoNum = count($data['yFields']); //求k-v值的所占行数 $infoStart = $infoNum + 1; //下面的详细信息的开始行数 // $cellName = array('A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z', 'AA', 'AB', 'AC', 'AD', 'AE', 'AF', 'AG', 'AH', 'AI', 'AJ', 'AK', 'AL', 'AM', 'AN', 'AO', 'AP', 'AQ', 'AR', 'AS', 'AT', 'AU', 'AV', 'AW', 'AX', 'AY', 'AZ'); $cellName = $this->getExcelHeader($count); $sheet = $spreadsheet->getActiveSheet($n)->setTitle('透视表导出_' . ($n + 1)); //设置sheet的名称 // $spreadsheet->getActiveSheet($n)->mergeCells('A1:' . $cellName[$count - 1] . '1'); //合并单元格 // $spreadsheet->getActiveSheet($n)->getStyle('A1')->getFont()->setSize(20); //设置title的字体大小 $spreadsheet->getActiveSheet($n)->getStyle("$infoStart")->getFont()->setBold(true); //栏加粗 // $objActSheet->setCellValue('A1', $data['title']); //设置每个sheet中的名称title foreach ($data['row'] as $key => $item) { //循环设置单元格: //$key+$infoStart,因为第一行是表头,所以写到表格时 从第数据行开始写 for ($i = 0; $i < $count; $i++) { //数字转字母从65开始: $sheet->setCellValue($cellName[$i] . ($key + "$infoStart"), $item[$i]); $spreadsheet->getActiveSheet($n)->getColumnDimension($cellName[$i])->setWidth(20); //固定列宽 } } foreach ($data['headerData'] as $key => $value) { $spreadsheet->setActiveSheetIndex($n)->setCellValue($value['x'], $value['name']); // 合并单元格 $spreadsheet->getActiveSheet($n)->mergeCells($value['x'] . ':' . $value['y']); // 设置单元格值 } }
参考地址: 使用PhpOffice\PhpSpreadsheet 实现Excel多sheet导出 - 简书
来源地址:https://blog.csdn.net/withoutfear/article/details/127901051