Spring Boot 系统中常常会用到一些表格,那么如何将这些表格导出为 Excel 格式的呢?

maven 引入 jar 包:

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>4.1.0</version>
</dependency>

编写工具类 ExcelUtil.java:

@Component
public class ExcelUtil {
    /**
     * 导出表格
     *
     * @param response http response
     * @param fileName file name
     * @param data     excel data
     * @throws Exception
     */
    public static void exportExcel(HttpServletResponse response, String fileName, ExcelData data) throws Exception {
        fileName += DateUtil.dateFormat(null, "yyyyMMddHHmmss") + ".xlsx";
        // 告诉浏览器用什么软件可以打开此文件
        response.setHeader("content-Type", "application/vnd.ms-excel");
        // 下载文件的默认名称
        response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "utf-8"));
        exportExcel(data, response.getOutputStream());
    }

    public static void exportExcel(ExcelData data, OutputStream out) throws Exception {

        XSSFWorkbook wb = new XSSFWorkbook();
        try {
            String sheetName = data.getName();
            if (null == sheetName) {
                sheetName = "Sheet1";
            }
            XSSFSheet sheet = wb.createSheet(sheetName);
            writeExcel(wb, sheet, data);

            wb.write(out);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            // 此处需要关闭 wb 变量
            out.close();
        }
    }

    private static void writeExcel(XSSFWorkbook wb, Sheet sheet, ExcelData data) {

        int rowIndex = 0;

        rowIndex = writeTitlesToExcel(wb, sheet, data.getTitles());
        writeRowsToExcel(wb, sheet, data.getRows(), rowIndex);
        writeTimestampToExcel(wb, sheet, data);
        autoSizeColumns(sheet, data.getTitles().size() + 1);

    }

    private static int writeTitlesToExcel(XSSFWorkbook wb, Sheet sheet, List<String> titles) {
        int rowIndex = 0;
        int colIndex = 0;

        Font titleFont = wb.createFont();
        titleFont.setFontName("simsun");
        // titleFont.setBoldweight(Short.MAX_VALUE);
        // titleFont.setFontHeightInPoints((short) 14);
        titleFont.setColor(IndexedColors.BLACK.index);

        XSSFCellStyle titleStyle = wb.createCellStyle();
        titleStyle.setAlignment(HorizontalAlignment.CENTER);
        titleStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        titleStyle.setFillForegroundColor(new XSSFColor(new Color(182, 184, 192), new DefaultIndexedColorMap()));
        titleStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        titleStyle.setFont(titleFont);
        setBorder(titleStyle, BorderStyle.THIN, new XSSFColor(new Color(0, 0, 0), new DefaultIndexedColorMap()));

        Row titleRow = sheet.createRow(rowIndex);
        // titleRow.setHeightInPoints(25);
        colIndex = 0;

        for (String field : titles) {
            Cell cell = titleRow.createCell(colIndex);
            cell.setCellValue(field);
            cell.setCellStyle(titleStyle);
            colIndex++;
        }

        rowIndex++;
        return rowIndex;
    }

    private static int writeRowsToExcel(XSSFWorkbook wb, Sheet sheet, List<List<Object>> rows, int rowIndex) {
        int colIndex = 0;

        Font dataFont = wb.createFont();
        dataFont.setFontName("simsun");
        // dataFont.setFontHeightInPoints((short) 14);
        dataFont.setColor(IndexedColors.BLACK.index);

        XSSFCellStyle dataStyle = wb.createCellStyle();
        dataStyle.setAlignment(HorizontalAlignment.CENTER);
        dataStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        dataStyle.setFont(dataFont);
        setBorder(dataStyle, BorderStyle.THIN, new XSSFColor(new Color(0, 0, 0), new DefaultIndexedColorMap()));

        for (List<Object> rowData : rows) {
            Row dataRow = sheet.createRow(rowIndex);
            // dataRow.setHeightInPoints(25);
            colIndex = 0;

            for (Object cellData : rowData) {
                Cell cell = dataRow.createCell(colIndex);
                if (cellData != null) {
                    cell.setCellValue(cellData.toString());
                } else {
                    cell.setCellValue("");
                }

                cell.setCellStyle(dataStyle);
                colIndex++;
            }
            rowIndex++;
        }
        return rowIndex;
    }

    private static void writeTimestampToExcel(XSSFWorkbook wb, Sheet sheet, ExcelData data) {
        CellRangeAddress region = new CellRangeAddress(data.getRows().size(), data.getRows().size(), 0, data.getTitles().size() - 1);
        sheet.addMergedRegion(region);
    }

    private static void autoSizeColumns(Sheet sheet, int columnNumber) {

        for (int i = 0; i < columnNumber; i++) {
            int orgWidth = sheet.getColumnWidth(i);
            sheet.autoSizeColumn(i, true);
            int newWidth = (int) (sheet.getColumnWidth(i) + 100);
            if (newWidth > orgWidth) {
                sheet.setColumnWidth(i, newWidth);
            } else {
                sheet.setColumnWidth(i, orgWidth);
            }
        }
    }

    private static void setBorder(XSSFCellStyle style, BorderStyle border, XSSFColor color) {
        style.setBorderTop(border);
        style.setBorderLeft(border);
        style.setBorderRight(border);
        style.setBorderBottom(border);
        style.setBorderColor(BorderSide.TOP, color);
        style.setBorderColor(BorderSide.LEFT, color);
        style.setBorderColor(BorderSide.RIGHT, color);
        style.setBorderColor(BorderSide.BOTTOM, color);
    }
}

Excel.java 中我添加了一些自定义的细节,比如表头颜色,表格边框,表格生成时间等等。代码结构比较清晰,需要调整表格主题的可以自行修改调整。

编写控制层,例如我们希望导出 B 站的用户表格:

@GetMapping(value = "common/excel")
@ApiOperation(value = "表格测试")
public String excelTest(HttpServletResponse response) throws Exception {
    String fileName = "B站用户";
    String excelName = "Sheet1";
    String excelTitles = "MID,昵称,粉丝数,日期";

    List<Bilibili> data = new LinkedList<>();
    data.add(new Bilibili().setMid(1).setName("bishi").setFollower(39462).setDt(LocalDateTime.now()));
    data.add(new Bilibili().setMid(2).setName("碧诗").setFollower(658918).setDt(LocalDateTime.now()));
    data.add(new Bilibili().setMid(3).setName("囧囧倉").setFollower(1962).setDt(LocalDateTime.now()));
    data.add(new Bilibili().setMid(4).setName("枢木朱雀").setFollower(851).setDt(LocalDateTime.now()));
    data.add(new Bilibili().setMid(5).setName("幻想乡").setFollower(1318).setDt(LocalDateTime.now()));

    ExcelUtil.exportExcel(response, fileName, excelName, excelTitles, data);
    return "yes";
}

其中,日期字段是 LocalDateTime 格式的,poi 并不能识别,所以我们在 Excel 工具类中添加方法帮助解析参数。

/**
 * 导出表格
 *
 * @param response    http response
 * @param fileName    file name
 * @param excelName   excel name
 * @param excelTitles excel titles
 * @param data        excel data
 * @param <T>         class
 * @throws Exception
 */
public static <T> void exportExcel(HttpServletResponse response, String fileName, String excelName, String excelTitles, List<T> data) throws Exception {
    ExcelData excelData = new ExcelData();
    excelData.setName(excelName);

    List<String> titles = Arrays.asList(excelTitles.split(","));
    excelData.setTitles(titles);

    List<List<Object>> rows = new ArrayList<>();
    List<Object> row;
    if (data.size() != 0) {
        Field[] fields = data.get(0).getClass().getDeclaredFields();

        for (T datum : data) {
            row = new ArrayList<>();
            for (Field field : fields) {
                field.setAccessible(true);

                if (field.get(datum) == null) {
                    row.add("");
                } else if ("java.time.LocalDateTime".equals(field.getType().getName())) {
                    row.add(field.get(datum).toString().replace('T', ' ').substring(0, 16));
                } else if ("java.lang.Boolean".equals(field.getType().getName())) {
                    row.add((Boolean) field.get(datum) ? "是" : "否");
                } else {
                    row.add(field.get(datum).toString());
                }
            }
            rows.add(row);
        }
    }

    String[] rowArr = new String[excelData.getTitles().size()];
    rowArr[0] = "表格生成时间:" + DateUtil.dateFormat(null, DateUtil.DATE_TIME_PATTERN);
    rows.add(Arrays.asList(rowArr));
    excelData.setRows(rows);

    exportExcel(response, fileName, excelData);
}

这样,我们借助 Field 将空字段转成空的字符串,将 LocalDateTime 转成 "yyyy-MM-dd HH:mm" 格式,将布尔类型转成 是或否,其余类型直接 toString。

运行。在 Swagger Ui 下,这个接口是这样的。


点击 Download file 即可下载。

表格内容如下:



VUE

​在 Vue.js 框架中,如何下载表格呢?

在服务端创建文件并返回文件链接,这样每次请求都会生成新文件,并不是我们所希望的。
在 Vue 中获取服务器传回的 blob二进制对象 即可生成并下载文件。具体方法如下:
在 methods 中添加以下方法:
blobExport({ filename, res }) {
    const aLink = document.createElement("a");
    let blob = new Blob([res], { type: "application/vnd.ms-excel" });
    aLink.href = URL.createObjectURL(blob);
    aLink.download = filename + ".xlsx";
document.body.appendChild(aLink); aLink.click(); document.body.removeChild(aLink); }

其中 filename 是保存文件的文件名,res 是服务器返回的二进制对象。

函数会创建一个 <a> 标签,并制定二进制对象的格式为 application/vnd.ms-excel ,随后将 <a> 标签的链接指向 blob ,然后模拟点击事件即可下载文件。

需要注意的是点击后需要移除这个标签,虽然在 Chrome 下无影响,但在 Firefox 浏览器中可能会出现不能下载的 bug,所以标签使用前创建,使用后删除是最好的选择。