自定义Excel导出工具

一、概述

苍穹平台提供了引入引出功能实现了绝大部分的导出需求，但是在二次开发的过程中发现也存在一些对Excel的格式、样式有特殊需要的定制化需求。本文由日常二次开发需求整理而来，对定制化的Excel导出提供通用的工具处理方法，实现对Excel表头、标题行、数据行的定制化导出，及其对应的常见样式的控制。

二、设计思路：

1、通过注解方式实现表头、标题行属性的配置。

2、通过注解方式实现java对象属性跟Excel单元的自动映射。

3、定义数据行基础类，提供通用的字段定义（例如：行号，导入校验结果、错误原因等）。

4、定义样式参数类，用于生成表头、标题行、数据行等的样式信息。

5、开发Excel导出的助手类(ExcelExportHelper)，实现通用的Excel核心写入功能及样式设置。

6、开发导出处理器ExcelExportHandler，实现样式的定义和创建，并调用ExcelExportHelper实现数据的写入和样式的设置。

三、实现代码

1、excel导入导出文件标题头部配置注解

/**
 * excel导入导出文件标题头部配置注解
 */
@Documented
@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.TYPE)
public @interface ExcelHeadProperty {
    /**
     * @return 起始行
     */
    int firstRow() default 0;

    /**
     * @return 截止行
     */
    int lastRow() default 0;

    /**
     * @return 起始列
     */
    int firstCol() default 0;

    /**
     * @return 截止列
     */
    int lastCol() default 0;

    /**
     * @return 标题行高
     */
    short height() default (short) (24 * 20);

    /**
     * @return 标题文本
     */
    String text() default "";
}

2、excel导入导出行标题配置注解

/**
 * excel导入导出行标题配置注解
 */
@Documented
@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.TYPE)
public @interface ExcelRowProperty {
    /**
     * @return 默认标题行高
     */
    short titleHeight() default (short) (16.5 * 20);

    /**
     * @return 默认数据行高
     */
    short height() default (short) (16.5 * 20);

    /**
     * @return 列宽自适应
     */
    boolean autoFitCol() default true;
}

3、excel导入导出属性配置注解

/**
 * excel导入导出属性配置注解
 */
@Documented
@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.FIELD)
public @interface ExcelProperty {

    /**
     * @return 属性列索引位置，从0开始
     */
    int index() default -1;

    /**
     * @return 列标题，用于导出时生成列标题及导入时校验列标题
     */
    String title() default "";

    /**
     * @return 默认的最新列宽度
     */
    int minWidth() default 10;
}

4、Excel导入导出行数据基础信息类，用于定义导入导出的行数据类型

/**
 * Excel导入导出行数据基础信息类，用于定义导入导出的行数据类型
 */
public class ExcelRowBaseData<T> implements Serializable {

    private static final long serialVersionUID = 8649711415082247228L;

    /**
     * 行号
     */
    private Integer rowNum;

    /**
     * 数据状态
     */
    private boolean success = true;

    /**
     * 失败原因
     */
    private String failMsg;

    /**
     * 行数据
     */
    private T rowData;


    public ExcelRowBaseData(T rowData) {
        this.rowData = rowData;
    }

    public Integer getRowNum() {
        return rowNum;
    }

    public void setRowNum(Integer rowNum) {
        this.rowNum = rowNum;
    }

    public boolean isSuccess() {
        return success;
    }

    public void setSuccess(boolean success) {
        this.success = success;
    }

    public String getFailMsg() {
        return failMsg;
    }

    public void setFailMsg(String failMsg) {
        this.failMsg = failMsg;
    }

    public T getRowData() {
        return rowData;
    }

    public void setRowData(T rowData) {
        this.rowData = rowData;
    }
}

5、单元格样式

/**
 * 单元格样式
 */
public class CellStyleParam {
    /**
     * 水平对齐方式，默认居中
     */
    private HorizontalAlignment horizontalAlignment = HorizontalAlignment.CENTER;

    /**
     * 垂直对齐方式，默认居中
     */
    private VerticalAlignment verticalAlignment = VerticalAlignment.CENTER;

    /**
     * 填充类型
     */
    private FillPatternType fillPatternType = FillPatternType.SOLID_FOREGROUND;

    /**
     * 填充颜色
     */
    private XSSFColor foregroundColor = new XSSFColor(Color.WHITE);

    /**
     * 填充颜色
     */
    private XSSFColor fontColor = new XSSFColor(new Color(31, 31, 31));

    /**
     * 字体类型,默认微软雅黑
     */
    private String fontName = "微软雅黑";

    /**
     * 字体大小,默认11号
     */
    private short fontHeight = 11;

    /**
     * 是否加粗
     */
    private boolean bold = false;

    /**
     * 是否斜体
     */
    private boolean italic = false;

    /**
     * 构造函数
     */
    public CellStyleParam() {
    }

    /**
     * 构造函数
     */
    public CellStyleParam(XSSFColor fontColor, short fontHeight, boolean bold) {
        this.fontColor = fontColor;
        this.fontHeight = fontHeight;
        this.bold = bold;
    }

    /**
     * 构造函数
     */
    public CellStyleParam(XSSFColor foregroundColor, XSSFColor fontColor, short fontHeight, boolean bold) {
        this.foregroundColor = foregroundColor;
        this.fontColor = fontColor;
        this.fontHeight = fontHeight;
        this.bold = bold;
    }

    public HorizontalAlignment getHorizontalAlignment() {
        return horizontalAlignment;
    }

    public void setHorizontalAlignment(HorizontalAlignment horizontalAlignment) {
        this.horizontalAlignment = horizontalAlignment;
    }

    public VerticalAlignment getVerticalAlignment() {
        return verticalAlignment;
    }

    public void setVerticalAlignment(VerticalAlignment verticalAlignment) {
        this.verticalAlignment = verticalAlignment;
    }

    public FillPatternType getFillPatternType() {
        return fillPatternType;
    }

    public void setFillPatternType(FillPatternType fillPatternType) {
        this.fillPatternType = fillPatternType;
    }

    public XSSFColor getForegroundColor() {
        return foregroundColor;
    }

    public void setForegroundColor(XSSFColor foregroundColor) {
        this.foregroundColor = foregroundColor;
    }

    public XSSFColor getFontColor() {
        return fontColor;
    }

    public void setFontColor(XSSFColor fontColor) {
        this.fontColor = fontColor;
    }

    public String getFontName() {
        return fontName;
    }

    public void setFontName(String fontName) {
        this.fontName = fontName;
    }

    public short getFontHeight() {
        return fontHeight;
    }

    public void setFontHeight(short fontHeight) {
        this.fontHeight = fontHeight;
    }

    public boolean isBold() {
        return bold;
    }

    public void setBold(boolean bold) {
        this.bold = bold;
    }

    public boolean isItalic() {
        return italic;
    }

    public void setItalic(boolean italic) {
        this.italic = italic;
    }
}

6、Excel导出助手工具类

/**
 * Excel导出助手工具类
 */
public class ExcelExportHelper {


    /**
     * 创建工作簿并写入数据
     *
     * @param os                输出流
     * @param xssfWorkbook      工作簿
     * @param sheetName         sheet名称
     * @param dataList          需要导出的数据集
     * @param dataClass         数据类型
     * @param headCellStyle     头部标题单元格的样式
     * @param titleCellStyle    数据标题行单元格样式
     * @param dataCellStyleList 数据列样式集，多个样式可循环遍历使用实现斑马纹
     * @param writeFailMsg      是否写入错误信息列
     * @param <T>               数据内容泛型对象
     */
    public static <T> void write(OutputStream os, XSSFWorkbook xssfWorkbook, String sheetName, List<ExcelRowBaseData<T>> dataList, Class<T> dataClass, CellStyle headCellStyle, CellStyle titleCellStyle, List<CellStyle> dataCellStyleList, boolean writeFailMsg) throws IllegalAccessException, IOException {
        //创建工作表sheet
        XSSFSheet sheet = xssfWorkbook.createSheet(sheetName);
        //写入头部标题
        createHeadCell(sheet, headCellStyle, dataClass);
        //写入行标题
        writeTitleRow(sheet, dataClass, sheet.getLastRowNum() + 1, headCellStyle, writeFailMsg);
        //写入数据行
        writeDataRow(sheet, dataList, sheet.getLastRowNum() + 1, dataCellStyleList, writeFailMsg);
        //设置边框
        setBorder(sheet);
        //写入输出流
        xssfWorkbook.write(os);
    }


    /**
     * 创建头部标题单元格
     *
     * @param xssfSheet     sheet页
     * @param headCellStyle 头部标题单元格的样式
     * @param dataClass     数据类型
     * @param <T>
     */
    private static <T> void createHeadCell(XSSFSheet xssfSheet, CellStyle headCellStyle, Class<T> dataClass) {
        //不需要创建
        if (dataClass == null) {
            return;
        }
        //获取头部配置信息注解
        ExcelHeadProperty headProperty = dataClass.getAnnotation(ExcelHeadProperty.class);
        //如果存在注解信息，根据注解进行头部标题创建
        if (headProperty != null) {
            //循环创建单元格
            for (int i = headProperty.firstRow(); i <= headProperty.lastRow(); i++) {
                XSSFRow row = xssfSheet.createRow(i);
                row.setHeight(headProperty.height());
                for (int j = headProperty.firstCol(); j <= headProperty.lastCol(); j++) {
                    XSSFCell cell = row.createCell(j);
                    cell.setCellStyle(headCellStyle);
                    if (i == headProperty.firstRow() && j == headProperty.firstCol()) {
                        cell.setCellValue(headProperty.text());
                    }
                }
            }
            //合并单元格
            CellRangeAddress region = new CellRangeAddress(headProperty.firstRow(), headProperty.lastRow(), headProperty.firstCol(), headProperty.lastCol());
            xssfSheet.addMergedRegion(region);
        }
    }

    /**
     * 写入标题行
     *
     * @param xssfSheet      sheet
     * @param dataClass      数据类型
     * @param startRowIndex  起始行号
     * @param titleCellStyle 单元格样式
     * @param writeFailMsg   是否写入错误信息列
     * @param <T>            数据内容泛型对象
     */
    private static <T> void writeTitleRow(XSSFSheet xssfSheet, Class<T> dataClass, int startRowIndex, CellStyle titleCellStyle, boolean writeFailMsg) {
        //没有数据或者入参为空，直接返回
        if (dataClass == null || xssfSheet == null) {
            return;
        }
        //创建标题行
        XSSFRow titleRow = xssfSheet.createRow(startRowIndex);
        //设置行高
        ExcelRowProperty rowProperty = dataClass.getAnnotation(ExcelRowProperty.class);
        if (rowProperty != null) {
            titleRow.setHeight(rowProperty.titleHeight());
        }
        //获取声明的属性及其注解信息，并创建标题行
        Field[] fields = dataClass.getDeclaredFields();
        for (Field field : fields) {
            ExcelProperty property = field.getAnnotation(ExcelProperty.class);
            if (property != null && property.index() >= 0) {
                //创建单元格
                XSSFCell cell = titleRow.createCell(property.index());
                cell.setCellValue(property.title());
                cell.setCellStyle(titleCellStyle);
                xssfSheet.setColumnWidth(property.index(), property.minWidth() * 256);
            }
        }
        //错误原因列
        if (writeFailMsg) {
            XSSFCell cell = titleRow.createCell(titleRow.getLastCellNum());
            cell.setCellValue("错误原因");
        }
    }


    /**
     * 写入数据行
     *
     * @param xssfSheet         工作簿sheet页
     * @param dataList          需要导出的数据集
     * @param startRowIndex     起始行号
     * @param dataCellStyleList 数据列样式集，多个样式可循环遍历使用实现斑马纹
     * @param writeFailMsg      是否写入错误信息列
     * @param <T>               数据内容泛型对象
     */
    private static <T> void writeDataRow(XSSFSheet xssfSheet, List<ExcelRowBaseData<T>> dataList, int startRowIndex, List<CellStyle> dataCellStyleList, boolean writeFailMsg) throws IllegalAccessException {
        //数据为空，直接返回
        if (CollectionUtils.isEmpty(dataList)) {
            return;
        }
        //根据行号进行排序
        dataList.sort((o1, o2) -> o1.getRowNum() == null || o2.getRowNum() == null ? 0 : o1.getRowNum() - o2.getRowNum());
        //获取声明的属性及其注解信息，并创建标题行
        Field[] fields = dataList.get(0).getRowData().getClass().getDeclaredFields();
        ExcelRowProperty rowProperty = dataList.get(0).getRowData().getClass().getAnnotation(ExcelRowProperty.class);
        //样式数量，循环交替实现斑马纹
        int styleSize = CollectionUtils.isEmpty(dataCellStyleList) ? 0 : dataCellStyleList.size();
        //初始化列宽
        Map<Integer, Integer> colWidthMap = new HashMap<>();
        if (rowProperty != null && rowProperty.autoFitCol()) {
            for (Field field : fields) {
                ExcelProperty property = field.getAnnotation(ExcelProperty.class);
                if (property != null) {
                    int length = property.title().getBytes(Charset.forName("GBK")).length;
                    colWidthMap.put(property.index(), length > property.minWidth() ? length : property.minWidth());
                }
            }
        }
        //写入数据
        for (ExcelRowBaseData<T> data : dataList) {
            //创建行,并设置行高
            XSSFRow row = xssfSheet.createRow(startRowIndex++);
            if (rowProperty != null) {
                row.setHeight(rowProperty.height());
            }
            //写入cell值
            for (Field field : fields) {
                ExcelProperty property = field.getAnnotation(ExcelProperty.class);
                if (property != null && property.index() >= 0) {
                    //创建单元格
                    XSSFCell cell = row.createCell(property.index());
                    //写入cell值
                    field.setAccessible(true);
                    cell.setCellValue((String) field.get(data.getRowData()));
                    //设置样式
                    if (styleSize > 0) {
                        cell.setCellStyle(dataCellStyleList.get(startRowIndex % styleSize));
                    }
                    //列宽自适应
                    if (rowProperty != null && rowProperty.autoFitCol()) {
                        setCellWidth(colWidthMap, property.index(), cell.getStringCellValue());
                    }
                }
            }
            //错误原因列
            if (writeFailMsg) {
                XSSFCell cell = row.createCell(row.getLastCellNum());
                cell.setCellValue(data.getFailMsg());
                setCellWidth(colWidthMap, cell.getColumnIndex(), cell.getStringCellValue());
            }
        }
        //设置宽度
        Iterator<Map.Entry<Integer, Integer>> iterator = colWidthMap.entrySet().iterator();
        while (iterator.hasNext()) {
            Map.Entry<Integer, Integer> next = iterator.next();
            xssfSheet.getColumnHelper().setColBestFit(next.getKey(), true);
            xssfSheet.setColumnWidth(next.getKey(), next.getValue() * 256);
        }
    }

    /**
     * 边框设置
     *
     * @param sheet 工作簿sheet页
     */
    private static void setBorder(XSSFSheet sheet) {
        //获取最大行号
        int rowNum = sheet.getLastRowNum();
        //线性颜色
        XSSFColor lineColor = new XSSFColor(new Color(161, 161, 161));
        //循环设置cell的线框
        for (int i = 0; i <= rowNum; i++) {
            int cellNum = sheet.getRow(i).getPhysicalNumberOfCells();
            for (int j = 0; j <= cellNum; j++) {
                XSSFCell cell = sheet.getRow(i).getCell(j);
                if (cell != null) {
                    XSSFCellStyle style = cell.getCellStyle();
                    style.setBorderBottom(BorderStyle.THIN);
                    style.setBorderTop(BorderStyle.THIN);
                    style.setBorderLeft(BorderStyle.THIN);
                    style.setBorderRight(BorderStyle.THIN);
                    style.setBottomBorderColor(lineColor);
                    style.setTopBorderColor(lineColor);
                    style.setLeftBorderColor(lineColor);
                    style.setRightBorderColor(lineColor);
                    cell.setCellStyle(style);
                }
            }
        }
    }


    /**
     * 初始化列宽
     *
     * @param colWidthMap 列宽信息Map
     * @param colIndex    列小标
     * @param cellValue   单元格值
     */
    private static void setCellWidth(Map<Integer, Integer> colWidthMap, int colIndex, String cellValue) {
        int length = cellValue.trim().getBytes(Charset.forName("GBK")).length;
        Integer width = colWidthMap.get(colIndex);
        if (width == null || width < length) {
            colWidthMap.put(colIndex, length);
        }
    }

}

7、Excel 导出处理器

/**
 * Excel 导出处理器
 */
public class ExcelExportHandler<E> {

    /**
     * 头部单元格样式
     */
    private CellStyleParam headStyleParam = new CellStyleParam(new XSSFColor(Color.BLACK), (short) 14, true);

    /**
     * 标题列样式
     */
    private CellStyleParam titleStyleParam = new CellStyleParam(new XSSFColor(new Color(236, 238, 242)), new XSSFColor(new Color(31, 31, 31)), (short) 11, true);

    /**
     * 数据列样式
     */
    private List<CellStyleParam> cellStyleParamList = Arrays.asList(new CellStyleParam());

    /**
     * 是否写入错误信息
     */
    private boolean writeFailMsg = false;


    /**
     * 创建工作簿并写入数据
     *
     * @param os        输出流
     * @param sheetName sheet名称
     * @param dataList  需要导出的数据集
     * @param dataClass 数据类型
     */
    public void write(OutputStream os, String sheetName, List<ExcelRowBaseData<E>> dataList, Class<E> dataClass) throws IOException, IllegalAccessException {
        //创建工作簿
        XSSFWorkbook workbook = new XSSFWorkbook();
        //头部样式
        CellStyle headCellStyle = this.createCellStyle(workbook, headStyleParam);
        //行标题样式
        CellStyle titleCellStyle = this.createCellStyle(workbook, titleStyleParam);
        //数据行样式
        List<CellStyle> cellStyleList = new ArrayList<>();
        if (!CollectionUtils.isEmpty(cellStyleList)) {
            for (CellStyleParam styleParam : cellStyleParamList) {
                cellStyleList.add(this.createCellStyle(workbook, styleParam));
            }
        }
        //调用助手类，执行写入操作
        ExcelExportHelper.write(os, workbook, sheetName, dataList, dataClass, headCellStyle, titleCellStyle, cellStyleList, writeFailMsg);
    }


    /**
     * 创建样式
     *
     * @param workbook   工作簿
     * @param styleParam 样式参数
     * @return
     */
    private static CellStyle createCellStyle(XSSFWorkbook workbook, CellStyleParam styleParam) {
        XSSFCellStyle style = workbook.createCellStyle();
        if (styleParam != null) {
            //设置水平垂直对齐方式
            style.setAlignment(styleParam.getHorizontalAlignment());
            style.setVerticalAlignment(styleParam.getVerticalAlignment());
            //设置背景颜色
            style.setFillPattern(styleParam.getFillPatternType());
            style.setFillForegroundColor(styleParam.getForegroundColor());
            //设置字体
            XSSFFont font = workbook.createFont();
            font.setColor(styleParam.getFontColor());
            font.setFontName(styleParam.getFontName());
            font.setFontHeightInPoints(styleParam.getFontHeight());
            font.setItalic(styleParam.isItalic());
            font.setBold(styleParam.isBold());
            style.setFont(font);
        }
        return style;
    }


    public void setHeadStyleParam(CellStyleParam headStyleParam) {
        this.headStyleParam = headStyleParam;
    }

    public void setTitleStyleParam(CellStyleParam titleStyleParam) {
        this.titleStyleParam = titleStyleParam;
    }

    public void setCellStyleParamList(List<CellStyleParam> cellStyleParamList) {
        this.cellStyleParamList = cellStyleParamList;
    }

    public void setWriteFailMsg(boolean writeFailMsg) {
        this.writeFailMsg = writeFailMsg;
    }
}

四、运用示例（表单插件中）

1、导出实体

@ExcelHeadProperty(text = "学生名单", lastCol = 2)
@ExcelRowProperty
public class StudentDTO {

    @ExcelProperty(index = 0, title = "学号")
    private String no;

    @ExcelProperty(index = 1, title = "姓名")
    private String name;

    @ExcelProperty(index = 2, title = "年龄")
    private String age;


    public StudentDTO(String no, String name, String age) {
        this.no = no;
        this.name = name;
        this.age = age;
    }
}

2、导出代码

List<ExcelRowBaseData<StudentDTO>> list = new ArrayList<>();
list.add(new ExcelRowBaseData(new StudentDTO("1001","张三","18")));
list.add(new ExcelRowBaseData(new StudentDTO("1002","李四","19")));
list.add(new ExcelRowBaseData(new StudentDTO("1003","王五","20")));
String fileName = "学生名单.xls";
String filePath = String.format("%s%s","/student/",fileName);
//输出流
OutputStream os = new ByteArrayOutputStream();
//写入数据
new ExcelExportHandler<StudentDTO>().write(os, sheetName, list, StudentDTO.class);
//转换为输入流
InputStream is = new ByteArrayInputStream(((ByteArrayOutputStream) os).toByteArray());
//文件服务
FileService fs = FileServiceFactory.getAttachmentFileService();
String path = fs.upload(new FileItem(fileName, filePath, is));
//下载
this.getView().openUrl(RequestContext.get().getClientFullContextPath() + "/attachment/download.do?path=" + path);