Java导出模板（工具类在最后）

一丶编写导出接口

二丶根据接口设置表数据一行header如下excel

三丶设置表头数据

 

 

五丶公共循环添加列

 

六丶查询数据并组装 

 

 七丶表头数据加粗

 八丶合并单元格

九丶格式居左还是居右等 

 

十丶调用导出方法并处理异常 

 

看着步骤多  其实工具类调用就很快得，最后结果展示就是这样，根据情况自己设置格式。 

 

 最后工具类添加到后面：

@Slf4j
public class ExcelUtils {

    /**
     * 保存到文件
     *
     * @param writer
     * @param filePath 文件路径附带文件名
     * @throws IOException
     */
    public static void saveToFile(BigExcelWriter writer, String filePath) throws IOException {
        FileOutputStream outputStream = new FileOutputStream(filePath);
        writer.flush(outputStream, true);
        outputStream.close();
        writer.close();
    }

    public static void exportRequest(BigExcelWriter writer, String fileName,
                                     HttpServletResponse response) throws UnsupportedEncodingException {
        if (StringUtils.isBlank(fileName)) {
            fileName = "导出";
        }
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8");
        response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(
                fileName + ".xlsx", "UTF-8"));
        ServletOutputStream out = null;
        try {
            out = response.getOutputStream();
            writer.flush(out);
        } catch (IOException e) {
            log.error("stream error", e);
        } finally {
            writer.close();
            if (out != null) {
                IoUtil.close(out);
            }

        }
    }

    /**
     * @param sheetName sheet名称
     * @param titles    标题
     * @param datas     数据
     * @return
     */
    public static BigExcelWriter export(String sheetName, List<String> titles,
                                        List<List<String>> datas) {
        if (StringUtils.isBlank(sheetName)) {
            sheetName = "导出";
        }
        BigExcelWriter writer = new BigExcelWriter(3, sheetName);
        addSheet(writer, sheetName, titles, datas);
        return writer;
    }

    /**
     * 新增sheet页
     *
     * @param writer
     * @param sheetName sheet名称【名称不能重复】
     * @param titles
     * @param datas
     */
    public static void addSheet(BigExcelWriter writer, String sheetName, List<String> titles,
                                List<List<String>> datas) {
        List<List<String>> rowsDatas = Lists.newArrayList(datas);
        if (StringUtils.isBlank(sheetName)) {
            sheetName = "导出";
        }
        writer.setSheet(sheetName);
        //将标题页添加到第一行
        if (CollectionUtils.isNotEmpty(titles)) {
            rowsDatas.add(0, titles);
        }
        //解决Attempting to write a row[0] in the range [0,0] that is already written to disk异常
        SXSSFSheet sheet = (SXSSFSheet) writer.getSheet();
        sheet.setRandomAccessWindowSize(-1);
        writer.write(rowsDatas);

        // 列宽自适应
        autoColumnSize(writer);
    }

    /**
     * 设置行样式
     *
     * @param writer
     * @param sheetIndex     sheet标记 从0开始
     * @param fontColor      字体颜色
     * @param backgroudColor 背景颜色
     * @param bold           是否加粗
     * @param rowNo          是否加粗
     */
    public static void setRowStyle(BigExcelWriter writer, int sheetIndex, IndexedColors fontColor,
                                   IndexedColors backgroudColor, Boolean bold, int rowNo) {
        if (Objects.isNull(fontColor)) {
            fontColor = IndexedColors.BLACK;
        }
        if (Objects.isNull(backgroudColor)) {
            backgroudColor = IndexedColors.GREY_25_PERCENT;
        }
        if (Objects.isNull(bold)) {
            bold = Boolean.FALSE;
        }
        //设置操作的sheet
        writer.setSheet(sheetIndex);
        // 标题行样式
        SXSSFWorkbook workbook = (SXSSFWorkbook) writer.getWorkbook();
        //创建样式
        CellStyle cellStyle = createCellStyle(workbook, fontColor, bold, backgroudColor);
        //设置样式
        setRowCellStyle(writer, cellStyle, rowNo);
    }

    /**
     * @param writer
     * @param sheetIndex     sheet标记 从0开始
     * @param fontColor      字体颜色
     * @param backgroudColor 背景颜色
     * @param bold           是否加粗
     * @param rowNo          是否加粗
     */
    public static void setColStyle(BigExcelWriter writer, int sheetIndex, IndexedColors fontColor,
                                   IndexedColors backgroudColor, Boolean bold, int rowNo,
                                   int colNo) {
        if (Objects.isNull(fontColor)) {
            fontColor = IndexedColors.BLACK;
        }
        if (Objects.isNull(backgroudColor)) {
            backgroudColor = IndexedColors.GREY_25_PERCENT;
        }
        if (Objects.isNull(bold)) {
            bold = Boolean.FALSE;
        }
        //设置操作的sheet
        writer.setSheet(sheetIndex);
        // 标题行样式
        SXSSFWorkbook workbook = (SXSSFWorkbook) writer.getWorkbook();
        //创建样式
        CellStyle cellStyle = createCellStyle(workbook, fontColor, bold, backgroudColor);
        //设置样式
        setColCellStyle(writer, cellStyle, rowNo, colNo);
    }


    /**
     * 合并单元格
     * 需要在设置数据之后进行操作
     *
     * @param writer
     * @param firstRow 起始行 从0开始
     * @param lastRow  结束行 从0开始
     * @param firstCol 起始列 从0开始
     * @param lastCol  结束列 从0开始
     */
    public static void merge(BigExcelWriter writer, int firstRow, int lastRow, int firstCol,
                             int lastCol) {
        //获取当前正在操作的sheet
        Sheet sheet = writer.getSheet();
        merge(sheet, firstRow, lastRow, firstCol, lastCol);
    }

    /**
     * 合并单元格
     * 需要在设置数据之后进行操作
     *
     * @param writer
     * @param sheetIndex 从0开始第几个sheet
     * @param firstRow   起始行 从0开始
     * @param lastRow    结束行 从0开始
     * @param firstCol   起始列 从0开始
     * @param lastCol    结束列 从0开始
     */
    public static void merge(BigExcelWriter writer, int sheetIndex, int firstRow, int lastRow,
                             int firstCol, int lastCol) {
        //设置操作的sheet
        writer.setSheet(sheetIndex);
        //获取当前正在操作的sheet
        Sheet sheet = writer.getSheet();
        merge(sheet, firstRow, lastRow, firstCol, lastCol);
    }


    private static void merge(Sheet sheet, int firstRow, int lastRow, int firstCol,
                              int lastCol) {
        sheet.addMergedRegion(new CellRangeAddress(firstRow, lastRow, firstCol, lastCol));
    }


    /**
     * 创建列样式
     *
     * @param workbook
     * @param fontColor       字体颜色
     * @param bold            是否加粗
     * @param backGroundColor 背景颜色
     * @return
     */
    private static CellStyle createCellStyle(SXSSFWorkbook workbook, IndexedColors fontColor,
                                             Boolean bold, IndexedColors backGroundColor) {
        //颜色
        Font font = workbook.createFont();
        font.setColor(fontColor.getIndex());
        font.setBold(bold);
        CellStyle style = workbook.createCellStyle();
        style.setFont(font);
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setFillForegroundColor(backGroundColor.getIndex());
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        style.setBorderTop(BorderStyle.THIN);
        style.setBorderBottom(BorderStyle.THIN);
        style.setBorderRight(BorderStyle.THIN);
        style.setBorderLeft(BorderStyle.THIN);
        return style;
    }

    /**
     * 设置行样式
     *
     * @param writer
     * @param cellStyle
     * @param rowNo
     */
    private static void setRowCellStyle(BigExcelWriter writer, CellStyle cellStyle, int rowNo) {
        Row row = writer.getOrCreateRow(rowNo);
        short lastCellNum = row.getLastCellNum();
        for (int i = 0; i < lastCellNum; i++) {
            setColCellStyle(writer, cellStyle, rowNo, i);
        }
    }

    /**
     * 设置单元格样式
     *
     * @param writer
     * @param cellStyle
     * @param rowNo
     * @param col
     */
    private static void setColCellStyle(BigExcelWriter writer, CellStyle cellStyle, int rowNo,
                                        int col) {
        Row row = writer.getOrCreateRow(rowNo);
        row.getCell(col).setCellStyle(cellStyle);
    }

    /**
     * 设置单个格自动大小
     *
     * @param writer
     */
    private static void autoColumnSize(BigExcelWriter writer) {
        SXSSFSheet sheet = (SXSSFSheet) writer.getSheet();
        sheet.trackAllColumnsForAutoSizing();
        writer.autoSizeColumnAll();
        for (int i = 0; i < sheet.getRow(sheet.getLastRowNum()).getPhysicalNumberOfCells(); i++) {
             解决自动设置列宽中文失效的问题
            sheet.setColumnWidth(i, sheet.getColumnWidth(i) * 17 / 10);
        }
        sheet.untrackAllColumnsForAutoSizing();
    }

    public static void main(String[] args) {
        String filePath = "F:\\test\\test.xlsx";
        String sheetName = "test";
        List<String> titles = Lists.newArrayList("t1", "t2", "t3");

        List<List<String>> datas = Lists.newArrayList();
        for (int i = 0; i < 3; i++) {
            List<String> data = Lists.newArrayList("d" + i + "1", "d" + i + "2", "d" + i + "3");
            datas.add(data);
        }

        try {
            BigExcelWriter export = export(sheetName, titles, datas);
            //创建标题样式
            addSheet(export, sheetName + "1", titles, datas);
            merge(export, 0, 1, 1, 0, 2);
            setRowStyle(export, 1, IndexedColors.BLACK, IndexedColors.GREY_25_PERCENT, true, 0);
            saveToFile(export, filePath);
        } catch (IOException e) {
            e.printStackTrace();
        }

        //response 使用实例
//        ExcelWriter writer = ;
//        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml" +
//                ".sheet;charset=utf-8");
//        response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(
//                "导出.xlsx", "UTF-8"));
//        try (ServletOutputStream out = response.getOutputStream();) {
//            writer.flush(out);
//            writer.close();
//            IoUtil.close(out);
//        } catch (IOException e) {
//            log.error("stream error", e);
//        }
//        return null;
    }
}