【无标题】

点击导出excel，会传到后端相关参数 

打印日志 

后端接收的 参数

    @PreAuthorize("@ss.hasPermi('wn:wn:export')")
    @Log(title = "微脑列表", businessType = BusinessType.EXPORT)
    @PostMapping("/batchExport")
    public void batchExport(@RequestParam Map<String, Object> params, HttpServletResponse response)
    {    //拿到对应的数据
           {
        Object wnList = params.get("wnList");
        // 将获取到的wnList转换为Wn数组
        Wn[] wnList1 = JSON.parseObject(wnList.toString(), Wn[].class);
        // 将Wn数组转换为List集合
        List<Wn> wnList2 = Arrays.asList(wnList1);
        // 调用服务层方法导出Excel文件
        wnService.exportWnExcel(response, wnList2);
    }
    }

 解析为一个集合 集合里面有wn对象

 调用  new xssfworkbook生成工作铺对象，调用相关方法生成列头和数据页头

public void exportWnExcel(HttpServletResponse response, List<Wn> wnList) {
        if (CollectionUtils.isEmpty(wnList)) {
            throw new IllegalArgumentException("导出数据不能为空");
        }
         try (Workbook wb = new XSSFWorkbook()) {
            Sheet sheet = wb.createSheet("微脑列表");
            // 创建表头
            String[] headers = { "微脑编号","微脑Sn", "微脑类型ID","区域id"};
            Row headerRow = sheet.createRow(0);
            for (int i = 0; i < headers.length; i++) {
                headerRow.createCell(i).setCellValue(headers[i]);
                //设置宽度
                sheet.setColumnWidth(i, 5000);
            }
    
            // 填充数据
            for (int i = 0; i < wnList.size(); i++) {
                Wn wn = wnList.get(i);
                Row row = sheet.createRow(i + 1);
                row.createCell(0).setCellValue(wn.getWnNum());
                row.createCell(1).setCellValue(wn.getWnSn());
                row.createCell(2).setCellValue(wn.getWnType());
                row.createCell(3).setCellValue(wn.getAreaId());
            }
 
             createDeviceTypeSheet(wb);
             try (OutputStream os = response.getOutputStream();){//从response中获取输出流
                String fileName = new String("微脑数据表.xls".getBytes(), "ISO8859-1");//准备好文件名
                response.setHeader("Content-Disposition", "attachment;filename=" + fileName);//设置响应头
                response.addHeader("Param", "no-cache");
                response.addHeader("Cache-Control", "no-cache");
                response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
                response.setCharacterEncoding("utf-8");
                wb.write(os);//写出到浏览器
                os.close();//*-*关闭流
            } catch (IOException e) {
                e.printStackTrace();
            }
    } catch (IOException e) {
    throw new RuntimeException(e);
}
    }

 wb.write(os)

 创建多个表

    public void exportExcel(HttpServletResponse response) {
        Workbook wb = new XSSFWorkbook();
        // 创建第一个工作表：微脑导入模板
        createWnDataSheet(wb);
        // 创建第二个工作表：城市表
        createCitySheet(wb);
        // 创建第三个工作表：微脑类型表
        createDeviceTypeSheet(wb);
        //输出文件到浏览器
        try (OutputStream os = response.getOutputStream();){//从response中获取输出流
            String fileName = new String("台站数据表.xls".getBytes(), "ISO8859-1");//准备好文件名
            response.setContentType("application/octet-stream;charset=ISO8859-1");//返回类型
            response.setHeader("Content-Disposition", "attachment;filename=" + fileName);//设置响应头
            response.addHeader("Param", "no-cache");
            response.addHeader("Cache-Control", "no-cache");
            wb.write(os);//写出到浏览器
            os.close();//*-*关闭流
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    private void createWnDataSheet(Workbook wb) {
        // 创建sheet对象（excel的表单）
        Sheet sheet = wb.createSheet("微脑列表");
        // 创建表头
        Row headerRow = sheet.createRow(0);
        headerRow.createCell(0).setCellValue("序号");
        headerRow.createCell(1).setCellValue("微脑编号");
        headerRow.createCell(2).setCellValue("微脑类型ID");
        headerRow.createCell(3).setCellValue("固件版本");
        headerRow.createCell(4).setCellValue("区域ID（由大到小多级存储，以/分割，比如2/44/302/02)");

    }
    private void createCitySheet(Workbook wb) {
        try {
            // 从配置中获取文件路径
            String filePath = uploadPath + "/微脑导入模版.xlsx";
            // 读取固定的城市表Excel文件
            FileInputStream file = new FileInputStream(new File(filePath));
            Workbook cityWorkbook = new XSSFWorkbook(file);
            Sheet citySheet = cityWorkbook.getSheet("城市编码表"); // 获取第一个工作表

            // 在新工作簿中创建城市表
            Sheet newCitySheet = wb.createSheet("城市编码表");

            // 复制整个工作表
            copySheet(citySheet, newCitySheet);

            // 关闭城市表工作簿
            cityWorkbook.close();
            file.close();
        } catch (Exception e) {
            e.printStackTrace();
            //如果已经创建城市编码表，移除该表
            int sheetIndex = wb.getSheetIndex("城市编码表");
            if (sheetIndex != -1) {
                wb.removeSheetAt(sheetIndex);
            }
            log.info("城市表加载失败，请检查文件路径是否正确");
        }
    }

    private void createDeviceTypeSheet(Workbook wb) {
        // 创建微脑类型表
        Sheet sheet = wb.createSheet("微脑模版编码表");

        // 创建表头
        Row headerRow = sheet.createRow(0);
        headerRow.createCell(0).setCellValue("微脑模板ID");
        headerRow.createCell(1).setCellValue("微脑模板名称");
        headerRow.createCell(2).setCellValue("微脑模板包含设备");
        // 从数据库获取微脑类型数据
        List<WnTemplate> wnTemplateList = wnTemplateService.selectWnTemplateList(null);

        // 填充数据
        int rowNum = 1;
        for (WnTemplate wnTemplate : wnTemplateList) {
            Row row = sheet.createRow(rowNum++);
            row.createCell(0).setCellValue(wnTemplate.getId());
            row.createCell(1).setCellValue(wnTemplate.getName());
            String sensorsList="";
            for(WnTemplateSensors wnTemplateSensors : wnTemplate.getSensorsList()) {
                sensorsList+= SensorConstants.getSensorName(wnTemplateSensors.getSensorType())+"("+wnTemplateSensors.getSensorNid()+");";
            }
            row.createCell(2).setCellValue(sensorsList);
        }
    }


    // 复制工作表的辅助方法
    private void copySheet(Sheet source, Sheet destination) {
        Workbook destWorkbook = destination.getWorkbook();
        // 缓存已创建的CellStyle，避免重复创建相同样式
        Map<CellStyle, CellStyle> styleMap = new HashMap<>();
        DataFormatter formatter = new DataFormatter();
        // 复制所有行
        for (int i = 0; i <= source.getLastRowNum(); i++) {
            Row sourceRow = source.getRow(i);
            if (sourceRow != null) {
                Row newRow = destination.createRow(i);
                // 复制所有单元格
                for (int j = 0; j < sourceRow.getLastCellNum(); j++) {
                    Cell sourceCell = sourceRow.getCell(j);
                    if (sourceCell != null) {
                        Cell newCell = newRow.createCell(j);
                        // 复制单元格值
                        newCell.setCellValue(formatter.formatCellValue(sourceCell));
                        // 复制单元格样式
                        copyCellStyle(sourceCell.getCellStyle(), newCell, destWorkbook, styleMap);
                    }
                }
            }
        }
    }
    // 复制单元格样式的辅助方法
    private void copyCellStyle(CellStyle sourceStyle, Cell newCell, Workbook destWorkbook,
                               Map<CellStyle, CellStyle> styleMap) {
        // 检查是否已经为这个源样式创建了目标样式
        if (styleMap.containsKey(sourceStyle)) {
            newCell.setCellStyle(styleMap.get(sourceStyle));
            return;
        }

        // 创建新的CellStyle
        CellStyle newStyle = destWorkbook.createCellStyle();

        // 复制基本样式属性
        newStyle.cloneStyleFrom(sourceStyle);

        // 处理特殊样式属性（如果有需要）
        // 例如：处理自定义数据格式、字体等

        // 将新样式存入缓存
        styleMap.put(sourceStyle, newStyle);

        // 应用新样式
        newCell.setCellStyle(newStyle);
    }

表格代码 

     <vxe-table :data="batchImportParams.wnList" max-height="500px" :virtual-y-config="{enabled: true, gt: 0}" :cell-config="{height: 54}">
          <vxe-column field="wnNum" title="编号"></vxe-column>
          <vxe-column field="wnType" title="微脑模板">
            <template #default="{ row, $index }">
              <vxe-select v-model="row.wnType" placeholder="请选择模板">
                <vxe-option v-for="item in templateList" :key="item.value" :label="item.label" :value="item.value" />
              </vxe-select>
            </template>
          </vxe-column>
        </vxe-table>