使用hutool导出excel文件

一、导包

compile 'cn.hutool:hutool-all:4.5.15'
compile 'org.apache.poi:poi-ooxml:3.17'

 

导出示例

        OutputStream out = new FileOutputStream("D:\\aa.xlsx");

        List<String[]> titleAliasList = new ArrayList<>();
        titleAliasList.add(new String[]{"startDate", "开始时间"});
        titleAliasList.add(new String[]{"endDate", "结束时间"});
        titleAliasList.add(new String[]{"page", "分页索引"});
        titleAliasList.add(new String[]{"size", "页元素大小"});


        List<DateQuery> rows = new ArrayList<>();
        rows.add(new DateQuery("ha1", "he5", 1, 5));
        rows.add(new DateQuery("ha2", "he4", 2, 4));
        rows.add(new DateQuery("ha3", "he3", 3, 3));
        rows.add(new DateQuery("ha4", "he2", 4, 2));
        rows.add(new DateQuery("ha5", "he1", 5, 1));

        String title = "myTitle";

        // 生成xlsx
        ExcelWriter writer = ExcelUtil.getWriter(true);
        writer.renameSheet("mySheet");

        titleAliasList.forEach(arr -> writer.addHeaderAlias(arr[0], arr[1]));

        //合并单元格后的标题行，使用默认标题样式
        writer.merge(titleAliasList.size() - 1, title);

        // 设置标题样式
        StyleSet style = writer.getStyleSet();
        CellStyle headCellStyle = style.getHeadCellStyle();
        //水平居中
        headCellStyle.setAlignment(HorizontalAlignment.CENTER);
        //垂直居中
        headCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);

        //设置内容字体
        Font font = writer.createFont();
        //加粗
        font.setBold(true);
        //设置标题字体大小
        font.setFontHeightInPoints((short)12);

        headCellStyle.setFont(font);


        // 一次性写出内容，使用默认样式，强制输出标题
        writer.write(rows, true);

        //out为OutputStream，需要写出到的目标流
        writer.flush(out);
        // 关闭writer，释放内存
        writer.close();

返回给前端下载

    public static void excelExport(String fileName, String sheetName, String title, List<HeaderTitle> titleAliasList, List<?> rows, HttpServletResponse response) throws IOException {
        // 生成xlsx
        ExcelWriter writer = ExcelUtil.getWriter(true);
        writer.renameSheet(sheetName);

        titleAliasList.forEach(h -> writer.addHeaderAlias(h.getKey(), h.getName()));

        //合并单元格后的标题行，使用默认标题样式
        writer.merge(titleAliasList.size() - 1, title);

        // 设置标题样式
        StyleSet style = writer.getStyleSet();
        CellStyle headCellStyle = style.getHeadCellStyle();
        //水平居中
        headCellStyle.setAlignment(HorizontalAlignment.CENTER);
        //垂直居中
        headCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);

        //设置内容字体
        Font font = writer.createFont();
        //加粗
        font.setBold(true);
        //设置标题字体大小
        font.setFontHeightInPoints((short)12);

        headCellStyle.setFont(font);

        // 一次性写出内容，使用默认样式，强制输出标题
        writer.write(rows, true);


        //导出数据
        OutputStream out = null;
        try {
            //设置Http响应头告诉浏览器下载这个附件
            response.setHeader("Content-Disposition", "attachment;Filename=" + URLEncoder.encode(fileName, "UTF-8") + ".xlsx");
            response.setHeader("Content-Transfer-Encoding", "binary");
            out = response.getOutputStream();
            writer.flush(out, true);
            // 关闭writer，释放内存
            writer.close();
        } catch (Exception ex) {
            log.info("导出Excel异常，异常信息");
            throw new IOException("导出Excel异常，异常信息：" + ex.getMessage());
        } finally {
            //清理资源
            try {
                if (out != null) {
                    out.close();
                }
            } catch (IOException e) {
                log.info("清理资源错误");
            }
        }

    }