java实现excel导出和导入

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();
        }
    }

本文主要表达了固定模板以及动态模板导出的方式