Java-web实现导出Excel中多个sheet以及自定义sheet格式原理

本文基于 HSSFWorkbook 实现自定义样式及多个sheet实现导出Excel

 

代码实现：

public void testExport(HttpServletResponse response) {
    try {
        HSSFWorkbook hssfWorkbook = new HSSFWorkbook();
        for (int i = 0; i < 5; i++) {
            HSSFSheet sheet = hssfWorkbook.createSheet();
            hssfWorkbook.setSheetName(i, "sheet" + i);
            sheet.setDefaultColumnWidth(17); // 设置单元格的宽度
            HSSFFont headfont = hssfWorkbook.createFont(); // 为首行设置字体
            headfont.setFontName("宋体"); // 字体类型
            headfont.setFontHeightInPoints((short) 12); // 字体大小
            HSSFCellStyle headStyle = hssfWorkbook.createCellStyle(); // 创建单元格，并设置值表头
            headStyle.setAlignment(HorizontalAlignment.CENTER);
            headStyle.setFont(headfont); // 应用字体
            String[] headContent0 = {"序号", "名称", "时间"};
            HSSFRow titleRow = sheet.createRow(0);
            HSSFCell cell = titleRow.createCell(0);
            cell.setCellValue("sheet标题");
            cell.setCellStyle(headStyle);
            HSSFRow headRow = sheet.createRow(1);
            for (int m = 0; m < headContent0.length; m++) {
                HSSFCell cell1 = headRow.createCell(m);
                cell1.setCellValue(headContent0[m]);
                cell1.setCellStyle(headStyle);
            }
            String cellValue = "";
            HSSFCellStyle cellStyle = hssfWorkbook.createCellStyle();
            HSSFFont cellFont = hssfWorkbook.createFont();
            for (int n = 0; n < 20; n++) {
                // 填充单元格
                HSSFRow otherRow = sheet.createRow(n + 2);
                otherRow.setHeight((short) 500);
                for (int j = 0; j < headContent0.length; j++) {
                    if (j == 0) {
                        cellValue = String.valueOf(n + 1);
                    } else if (j == 1) {
                        cellValue = "sheet-"+i+"-"+ n;
                    } else if (j == 2) {
                        cellValue = String.valueOf(System.currentTimeMillis());
                    }
                    cellFont.setFontName("宋体"); // 字体类型
                    cellFont.setFontHeightInPoints((short) 11); // 字体大小
                    cellStyle.setVerticalAlignment(VerticalAlignment.CENTER); // 设置垂直居中
                    cellStyle.setWrapText(true); // 自动换行
                    cellStyle.setAlignment(HorizontalAlignment.CENTER);
                    cellStyle.setFont(cellFont); // 应用字体
                    HSSFCell cellOther = otherRow.createCell(j);
                    cellOther.setCellValue(cellValue);
                    cellOther.setCellStyle(cellStyle);
                }
            }
        }
        setResponseHeader(response, "导出测试" + ".xls");
        OutputStream os = response.getOutputStream();
        hssfWorkbook.write(os);
        os.flush();
        os.close();
    } catch (Exception e) {
        logger.error("导出异常！" + e.getMessage());
    }
}