自定义Excel导出工具原创
14人赞赏了该文章
1,911次浏览
编辑于2023年01月05日 18:54:12
一、概述
苍穹平台提供了引入引出功能实现了绝大部分的导出需求,但是在二次开发的过程中发现也存在一些对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);
推荐阅读