导出excel-拓冰建站

点击导出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;}// 创建新的CellStyleCellStyle 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>