1. 固定化模板导入导出
/** * 注意:需要指定列宽 * @createTime 2022/4/22 16:59 * @param: filename 文件名 * @param: columnList 列名 别名 宽度 * @param: orderList 数据源 * 示例 * ArrayList<String[]> columnList = new ArrayList<String[]>() {{ * add(new String[]{"name", "名称", "25"}); * }}; * List<Map<String, Object>> lists=new ArrayList<>(){{ * put("name","测试") * }} */ public static void mapDataToExcelStr(String filename, ArrayList<String[]> columnList, List<Map<String, String>> rows) { ExcelWriter writer = cn.hutool.poi.excel.ExcelUtil.getWriter(true); writer.renameSheet(filename); writer.getSheet().createFreezePane(0, 1);//固定第一行十五个列 IntStream.range(0, columnList.size()).forEach(i -> { writer.addHeaderAlias(columnList.get(i)[0], columnList.get(i)[1]); writer.setColumnWidth(i, Integer.parseInt(columnList.get(i)[2])); }); if (rows.isEmpty()) { List<String> head = new ArrayList<>(); columnList.forEach(n ->head.add(n[1])); writer.writeHeadRow(head); }else { for (int i = 0, rowsSize = rows.size(); i < rowsSize; i++) { Map<String, String> row = rows.get(i); LinkedHashMap<String, Object> row = new LinkedHashMap<>(); for (String[] columnArr : columnList) { row.put(columnArr[0], row.get(columnArr[0])); } writer.writeRow(row, 0 == i); } } final HttpServletRequest request = HttpServletUtil.getRequest(); final HttpServletResponse response = HttpServletUtil.getResponse(); assert response != null; response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8"); try (ServletOutputStream out = response.getOutputStream()) { response.setHeader("Content-Disposition", request.getHeader("USER-AGENT").contains("Safari") ? "attachment;filename=" + StrUtil.str(StrUtil.bytes(filename + ".xlsx", CharsetUtil.UTF_8), CharsetUtil.ISO_8859_1) : "attachment;filename*=utf-8'zh_cn'" + URLEncoder.encode(filename + ".xlsx", CharsetUtil.UTF_8)); writer.flush(out, true); writer.close();// 关闭writer,释放内存 } catch (IOException e) { e.printStackTrace(); } }
二、自定义模板
/** * 动态导出 * * @param list 数据 * @param response */ public void writeExcel(List<Object> list, HttpServletResponse response) throws ExecutionException, InterruptedException, IOException { ExcelWriter writer = ExcelUtil.getWriter(true); try { writer.renameSheet(0, "示例1"); writer.setSheet(0); writer.merge(0, 3, 0, 0, "序号", true); writer.merge(0, 3, 1, 1, "行政区划", true); writer.merge(0, 3, 2, 2, "项目名称", true); //所有列宽度设为18 writer.setColumnWidth(-1, 18); //设置较宽列的宽度 writer.setColumnWidth(11, 24); final List<ActualRepairAreaVOExcelDTO> actualDataList = new ArrayList<>(); if (CollectionUtils.isNotEmpty(list)) { writer.setCurrentRow(4); writer.write(list, false); } writer.setSheet(1); writer.renameSheet("示例2"); writer.merge(0, 3, 0, 0, "序号", true); writer.merge(0, 3, 1, 1, "行政区划", true); writer.merge(0, 3, 2, 2, "项目名称", true); //动态指定标题 //标题集合 List<String> titleList = new ArrayList<>(); int f = 1; for (String title : titleList) { writer.merge(0, 3, f, f, title, true); f = f + 1; } int h = 1; //所有列宽度设为18 writer.setColumnWidth(-1, 24); if (CollectionUtils.isNotEmpty(list)) { writer.setCurrentRow(4); writer.write(actualDataList, false); } //动态填充数据 for (int i = 0, rowsSize = list.size(); i < rowsSize; i++) { Object obj = list.get(i); writer.writeCellValue(h , i + 4, obj); } response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8"); response.setHeader("Content-Disposition", "attachment;filename=" + filename); writer.flush(response.getOutputStream()); // } catch (Exception e) { // throw new RRException(e.getMessage()); } finally { writer.close(); } }
本文主要表达了固定模板以及动态模板导出的方式
标签:java,writer,excel,filename,导入,columnList,new,true,response From: https://blog.csdn.net/qq_45132989/article/details/140272881