java poi 生成 Xlsx格式的Excel工具类

java poi 生成 Xlsx格式的Excel工具类

目的工具类的功能功能逻辑代码实现具体使用测试1：将数据集生成Excel测试2：将多个文件合并为一个文件

目的

应对Excel生成的不同需求，使用一套Excel生成工具，避免重复造轮子。

工具类的功能

1、生成Xlsx格式的Excel文件 2、解的Xlsx格式的Excel文件 3、合并多个Xlsx格式的Excel文件

功能逻辑

设计思路： 1、先将数据集合转换为ExcelSheet 对象 2、再将ExcelSheet 对象生成Excel的字节流对象

类图如下： 类具有的功能： AbstractGenerateExcelSheet 抽象类 1、将数据集合生成ExcelSheet对象（generateSheet方法） 2、ExcelSheet 对象转换（convertExcelSheet方法），将其他 ExcelSheet 转换为自己需要的 ExcelSheet

AbstractExportXlsxExcel 抽象类 1、将ExcelSheet 对象生成Xlsx格式的Excel文件字节流（exportExcel方法） 2、将多个Xlsx格式的Excel文件合并为一个ExcelSheet对象（mergeFirstSheetExcel方法） 3、生成Excel文件的特殊逻辑（addHook方法+hook方法）

代码实现

maven依赖：

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

注解类ExcelCellType 用途：用于标记单元格的数据类型

/** * @author huijing xu * @date 2020/8/24 */ @Target(ElementType.FIELD) @Retention(RetentionPolicy.RUNTIME) public @interface ExcelCellType { enum Type { NUMBER_DOUBLE, NUMBER_INTEGER, STRING } Type cellTyp() default Type.STRING; }

实体类ExcelSheet 用途：承载Excel单元格的数据，并根据此对象，生成Excel

/** * @author huijing xu * @date 2020/8/24 */ public class ExcelSheet { // Excel中Sheet的name private String sheetName; // 标题行 private LinkedList<String> rowNameList; // 数据行 private List<Map<String, CellEntity>> rowValueList; public String getSheetName() { return sheetName; } public void setSheetName(String sheetName) { this.sheetName = sheetName; } public LinkedList<String> getRowNameList() { return rowNameList; } public void setRowNameList(LinkedList<String> rowNameList) { this.rowNameList = rowNameList; } public List<Map<String, CellEntity>> getRowValueList() { return rowValueList; } public void setRowValueList(List<Map<String, CellEntity>> rowValueList) { this.rowValueList = rowValueList; } // Excel中单元格对象 public class CellEntity { // 单元格的值 private Object cellValue; // 单元格的类型 private ExcelCellType.Type type; public Object getCellValue() { return cellValue; } public void setCellValue(Object cellValue) { this.cellValue = cellValue; } public ExcelCellType.Type getType() { return type; } public void setType(ExcelCellType.Type type) { this.type = type; } } }

生成Excel的抽象类 用途：生成Excel，解析Excel

/** * @author huijing xu * @date 2020/8/24 */ @Slf4j public abstract class AbstractExportXlsxExcel { protected Map<ExcelCellType.Type, XSSFCellStyle> cellStyleMap; /** * 将ExcelSheet，生成Xlsx格式的Excel文件字节流 * * @param excelSheet * @return */ public ByteArrayInputStream exportExcel(ExcelSheet excelSheet) { if (excelSheet == null) { return null; } return exportExcel(Arrays.asList(excelSheet)); } /** * 将多个ExcelSheet，生成对应多个Sheet的Xlsx格式的Excel文件字节流 * * @param sheetList * @return */ public ByteArrayInputStream exportExcel(List<ExcelSheet> sheetList) { if (CollectionUtils.isEmpty(sheetList)) { return null; } try { XSSFWorkbook workbook = new XSSFWorkbook(); generateCellStyle(workbook); for (ExcelSheet excelSheet : sheetList) { createSheet(workbook, excelSheet); } ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream(); workbook.write(byteArrayOutputStream); ByteArrayInputStream inputStream = new ByteArrayInputStream(byteArrayOutputStream.toByteArray()); return inputStream; } catch (Exception e) { log.error("export excel of xlsx error:{}", e); throw new DataOperateException(e); } } /** * 将多个Xlsx格式的Excel文件合并为一个ExcelSheet对象 * 合并后的标题取自第一个文件 * 合并后的单元格对象，不具有单元格格式 * * @param byteList * @return */ public ExcelSheet mergeFirstSheetExcel(List<byte[]> byteList) { ExcelSheet excelSheet = new ExcelSheet(); if (CollectionUtils.isEmpty(byteList)) { return excelSheet; } LinkedList<String> titleList = new LinkedList<>(); List<Map<String, ExcelSheet.CellEntity>> excelSheetList = new ArrayList<>(); for (int i = 0; i < byteList.size(); i++) { byte[] bytes = byteList.get(i); // 第一个文件 if (i == 0) { titleList = parseTitle(bytes); } List<Map<String, ExcelSheet.CellEntity>> excelSheetMap = parseContent(bytes, titleList); excelSheetList.addAll(excelSheetMap); } excelSheet.setRowNameList(titleList); excelSheet.setRowValueList(excelSheetList); return excelSheet; } /** * 根据标题列表，解析文件的内容 * 从第二行开始，默认第一行为标题行 * * @param bytes * @param titleList * @return */ private List<Map<String, ExcelSheet.CellEntity>> parseContent(byte[] bytes, LinkedList<String> titleList) { List<Map<String, ExcelSheet.CellEntity>> rowValueList = new ArrayList<>(); if (bytes == null || bytes.length == 0 || CollectionUtils.isEmpty(titleList)) { return rowValueList; } try { ByteArrayInputStream byteArrayInputStream = new ByteArrayInputStream(bytes); XSSFWorkbook workbook = new XSSFWorkbook(byteArrayInputStream); // 获取第一个sheet XSSFSheet sheet = workbook.getSheetAt(0); // 获取此文件title映射的位置 HashMap<String, Integer> titleIndexMap = generateTitleIndexMap(sheet); // 这个sheet最大的行 int rowMax = sheet.getLastRowNum() + 1; // 内容行，从第二行开始 for (int i = 1; i < rowMax; i++) { Row row = sheet.getRow(i); Map<String, ExcelSheet.CellEntity> cellEntityMap = new HashMap<>(); for (String title : titleList) { ExcelSheet.CellEntity cellEntity = generateCellEntity(title, titleIndexMap, row); cellEntityMap.put(title, cellEntity); } rowValueList.add(cellEntityMap); } return rowValueList; } catch (Exception e) { log.error("parse Byte xlsx error:{}", e); throw new DataOperateException(e); } } /** * 根据标题，设置对应此标题的文本内容映射到第几列 * * @param sheet * @return */ private HashMap<String, Integer> generateTitleIndexMap(XSSFSheet sheet) { HashMap<String, Integer> titleIndexMap = new HashMap<>(); Row titleRow = sheet.getRow(0); short titleRowSize = titleRow.getLastCellNum(); for (int j = 0; j < titleRowSize; j++) { Cell cell = titleRow.getCell(j); if (cell != null) { String title = cell.getStringCellValue(); titleIndexMap.put(title, j); } } return titleIndexMap; } private ExcelSheet.CellEntity generateCellEntity(String title, HashMap<String, Integer> titleIndexMap, Row row) { ExcelSheet.CellEntity cellEntity = null; Integer integer = titleIndexMap.get(title); if (integer != null) { Cell cell = row.getCell(integer); if (cell != null) { cellEntity = new ExcelSheet().new CellEntity(); CellType cellType = cell.getCellType(); switch (cellType) { case NUMERIC: cellEntity.setCellValue(cell.getNumericCellValue()); break; case STRING: cellEntity.setCellValue(cell.getStringCellValue()); break; case BOOLEAN: cellEntity.setCellValue(cell.getBooleanCellValue()); break; case FORMULA: cellEntity.setCellValue(cell.getCellFormula()); break; } } } return cellEntity; } /** * 解析出excel的title，以第一个文件的title为准 * * @return */ private LinkedList<String> parseTitle(byte[] bytes) { try { ByteArrayInputStream byteArrayInputStream = new ByteArrayInputStream(bytes); XSSFWorkbook workbook = new XSSFWorkbook(byteArrayInputStream); // 获取第一个sheet XSSFSheet sheet = workbook.getSheetAt(0); LinkedList<String> titleNameList = new LinkedList<>(); Row titleRow = sheet.getRow(0); short titleRowSize = titleRow.getLastCellNum(); for (int j = 0; j < titleRowSize; j++) { Cell cell = titleRow.getCell(j); if (cell != null) { String title = cell.getStringCellValue(); titleNameList.add(title); } } return titleNameList; } catch (Exception e) { log.error("parse Byte xlsx error:{}", e); throw new DataOperateException(e); } } /** * 创建sheet * * @param workbook * @param excelSheet */ private void createSheet(XSSFWorkbook workbook, ExcelSheet excelSheet) { XSSFSheet xssfSheet; if (StringUtils.isNotEmpty(excelSheet.getSheetName())) { xssfSheet = workbook.createSheet(excelSheet.getSheetName()); } else { xssfSheet = workbook.createSheet(); } createHeadRow(xssfSheet, excelSheet); createContentRow(xssfSheet, excelSheet); } /** * 创建表头行 * * @param xssfSheet * @param excelSheet */ private void createHeadRow(XSSFSheet xssfSheet, ExcelSheet excelSheet) { XSSFRow headRow = xssfSheet.createRow(0); int columnIndex = 0; for (String title : excelSheet.getRowNameList()) { XSSFCell headCell = headRow.createCell(columnIndex++); headCell.setCellStyle(cellStyleMap.get(ExcelCellType.Type.STRING)); headCell.setCellValue(title); } } /** * 创建内容行 * * @param xssfSheet * @param excelSheet */ private void createContentRow(XSSFSheet xssfSheet, ExcelSheet excelSheet) { // 文本行开始位置，从第二行开始 int index = 1; List<Map<String, ExcelSheet.CellEntity>> rowValueList = excelSheet.getRowValueList(); if (CollectionUtils.isEmpty(rowValueList)) { return; } LinkedList<String> rowNameList = excelSheet.getRowNameList(); for (Map<String, ExcelSheet.CellEntity> row : rowValueList) { XSSFRow textRow = xssfSheet.createRow(index++); int j = 0; for (String title : rowNameList) { Object cellValue = null; ExcelCellType.Type cellType = null; ExcelSheet.CellEntity cellEntity = row.get(title); if (cellEntity != null) { cellValue = row.get(title).getCellValue(); cellType = row.get(title).getType(); } XSSFCell cell = textRow.createCell(j++); setContentCellValue(cell, cellValue, cellType); } } if (addHook(excelSheet)) { hook(index, xssfSheet, excelSheet); } } /** * 是否增加钩子方法，增加特殊逻辑 * * @return */ protected abstract boolean addHook(ExcelSheet excelSheet); /** * 钩子方法，实现特殊逻辑 * * @param startRow * @param xssfSheet * @param excelSheet */ protected abstract void hook(int startRow, XSSFSheet xssfSheet, ExcelSheet excelSheet); /** * 设置单元格格式以及单元格的内容 * * @param cell * @param cellValue * @param cellType */ private void setContentCellValue(XSSFCell cell, Object cellValue, ExcelCellType.Type cellType) { if (cellValue != null && cellType != null) { switch (cellType) { case NUMBER_DOUBLE: XSSFCellStyle number = cellStyleMap.get(ExcelCellType.Type.NUMBER_DOUBLE); cell.setCellStyle(number); cell.setCellValue(cellValue != null ? Double.parseDouble(cellValue.toString()) : 0); break; case NUMBER_INTEGER: XSSFCellStyle integer = cellStyleMap.get(ExcelCellType.Type.NUMBER_INTEGER); cell.setCellStyle(integer); cell.setCellValue(cellValue != null ? Double.parseDouble(cellValue.toString()) : 0); break; case STRING: XSSFCellStyle stringStyle = cellStyleMap.get(ExcelCellType.Type.STRING); cell.setCellStyle(stringStyle); cell.setCellValue(cellValue != null ? cellValue.toString() : ""); } } } /** * 生成cell的格式 * * @param workbook */ private void generateCellStyle(XSSFWorkbook workbook) { cellStyleMap = new HashMap<>(); XSSFCellStyle numberStyle = generateNumberStyle(workbook); cellStyleMap.put(ExcelCellType.Type.NUMBER_DOUBLE, numberStyle); XSSFCellStyle integerStyle = generateIntegerStyle(workbook); cellStyleMap.put(ExcelCellType.Type.NUMBER_INTEGER, integerStyle); XSSFCellStyle stringStyle = generateStringStyle(workbook); cellStyleMap.put(ExcelCellType.Type.STRING, stringStyle); } /** * 统一的具有小数点精度（两位）数字类型cell格式 * * @param workbook * @return */ private XSSFCellStyle generateNumberStyle(XSSFWorkbook workbook) { XSSFCellStyle cellStyle = workbook.createCellStyle(); XSSFDataFormat df = workbook.createDataFormat(); // 保留两位小数点 short format = df.getFormat("#,##0.00"); cellStyle.setDataFormat(format); XSSFFont font = workbook.createFont(); font.setFontName("Calibri"); cellStyle.setFont(font); return cellStyle; } /** * 统一的不具有小数点精度数字类型cell格式 * * @param workbook * @return */ private XSSFCellStyle generateIntegerStyle(XSSFWorkbook workbook) { XSSFCellStyle cellStyle = workbook.createCellStyle(); XSSFDataFormat df = workbook.createDataFormat(); // 数字类型，不保留小数 short format = df.getFormat("#,#0"); cellStyle.setDataFormat(format); XSSFFont font = workbook.createFont(); font.setFontName("Calibri"); cellStyle.setFont(font); return cellStyle; } /** * 统一的字符串类型cell格式 * * @param workbook * @return */ private XSSFCellStyle generateStringStyle(XSSFWorkbook workbook) { XSSFCellStyle cellStyle = workbook.createCellStyle(); XSSFFont font = workbook.createFont(); font.setFontName("Calibri"); cellStyle.setFont(font); return cellStyle; } }

生成ExcelSheet对象的抽象类 用途：将数据集转为ExcelSheet对象，用于Excel的生成

/** * @author huijing xu * @date 2020/8/24 */ public abstract class AbstractGenerateExcelSheet<T> { private LinkedHashMap<String, Field> fieldNameMap = new LinkedHashMap<>(); private Map<String, ExcelCellType.Type> fieldCellTypeMap = new HashMap<>(); @PostConstruct public void init() { Field[] fields = initFieldList(); for (int i = 0; i < fields.length; i++) { Column column = fields[i].getAnnotation(Column.class); if (column != null) { fields[i].setAccessible(true); fieldNameMap.put(column.name(), fields[i]); ExcelCellType type = fields[i].getAnnotation(ExcelCellType.class); if (type != null) { fieldCellTypeMap.put(column.name(), type.cellTyp()); } } } } abstract Field[] initFieldList(); /** * 根据数据集，生成ExcelSheet对象 * * @param data * @param <T> * @return */ public <T> ExcelSheet generateSheet(List<T> data) { return generateSheet(data, null); } public <T> ExcelSheet generateSheet(List<T> data, String sheetName) { ExcelSheet excelSheet = new ExcelSheet(); excelSheet.setSheetName(sheetName); LinkedList<String> rowNameList = generateRowNameList(); excelSheet.setRowNameList(rowNameList); List<Map<String, ExcelSheet.CellEntity>> rowValueList = new ArrayList<>(); if (CollectionUtils.isNotEmpty(data)) { rowValueList = generateRowValueList(rowNameList, data); } excelSheet.setRowValueList(rowValueList); return excelSheet; } public ExcelSheet convertExcelSheet(ExcelSheet otherSheet) { return convertExcelSheet(otherSheet, null); } /** * 将其他 ExcelSheet 转换为自己需要的 ExcelSheet * * @param otherSheet 需要转换的ExcelSheet * @param sheetName sheet的名称 * @return */ public ExcelSheet convertExcelSheet(ExcelSheet otherSheet, String sheetName) { ExcelSheet excelSheet = new ExcelSheet(); excelSheet.setSheetName(sheetName); LinkedList<String> rowNameList = generateRowNameList(); excelSheet.setRowNameList(rowNameList); List<Map<String, ExcelSheet.CellEntity>> rowValueList = new ArrayList<>(); if (otherSheet != null && CollectionUtils.isNotEmpty(otherSheet.getRowValueList())) { otherSheet.getRowValueList().stream().forEach(v -> { Map<String, ExcelSheet.CellEntity> cellEntityMap = new HashMap<>(); for (String fieldName : rowNameList) { ExcelSheet.CellEntity cellEntity = v.get(fieldName); if (cellEntity != null && cellEntity.getCellValue() != null) { ExcelCellType.Type type = fieldCellTypeMap.get(fieldName); cellEntity.setType(type); } cellEntityMap.put(fieldName, cellEntity); } rowValueList.add(cellEntityMap); }); } excelSheet.setRowValueList(rowValueList); return excelSheet; } /** * 生成excel各列的名称 * * @return */ private LinkedList<String> generateRowNameList() { LinkedList<String> rowNameList = new LinkedList<>(); if (MapUtils.isNotEmpty(fieldNameMap)) { for (String fieldName : fieldNameMap.keySet()) { rowNameList.add(fieldName); } } return rowNameList; } /** * 根据列名以及数据集，生成行数据 List<Map<String, ExcelSheet.CellEntity>> * 每行中列数据 Map<String, ExcelSheet.CellEntity> ,key为列名，value为ExcelSheet.CellEntity对象 * * @param rowNameList * @param data * @param <T> * @return */ private <T> List<Map<String, ExcelSheet.CellEntity>> generateRowValueList(LinkedList<String> rowNameList, List<T> data) { List<Map<String, ExcelSheet.CellEntity>> rowValueList = new ArrayList<>(); if (CollectionUtils.isNotEmpty(data)) { for (T t : data) { Map<String, ExcelSheet.CellEntity> cellEntityMap = new HashMap<>(); for (String rowName : rowNameList) { ExcelSheet.CellEntity cellEntity = new ExcelSheet().new CellEntity(); Field field = fieldNameMap.get(rowName); ExcelCellType.Type type = fieldCellTypeMap.get(rowName); try { cellEntity.setCellValue(field.get(t)); cellEntity.setType(type); cellEntityMap.put(rowName, cellEntity); } catch (IllegalAccessException e) { throw new DataOperateException(e); } cellEntityMap.put(rowName, cellEntity); } rowValueList.add(cellEntityMap); } } return rowValueList; } }

具体使用

1、实现 AbstractExportXlsxExcel 抽象类

/** * @author huijing xu * @date 2020/8/25 */ @Component public class NormalExportXlsxExcel extends AbstractExportXlsxExcel { @Override protected boolean addHook(ExcelSheet excelSheet) { return false; } @Override protected void hook(int startRow, XSSFSheet xssfSheet, ExcelSheet excelSheet) { } }

2、定义数据实体

** * @author huijing xu * @date 2020/9/30 */ public class OrderDetails { @Column(name = "OrderId") @ExcelCellType(cellTyp = ExcelCellType.Type.NUMBER_INTEGER) private Long orderId; @Column(name = "OrderAmount") @ExcelCellType(cellTyp = ExcelCellType.Type.NUMBER_DOUBLE) private BigDecimal orderAmount; @Column(name = "OrderName") @ExcelCellType(cellTyp = ExcelCellType.Type.STRING) private String orderName; public Long getOrderId() { return orderId; } public void setOrderId(Long orderId) { this.orderId = orderId; } public BigDecimal getOrderAmount() { return orderAmount; } public void setOrderAmount(BigDecimal orderAmount) { this.orderAmount = orderAmount; } public String getOrderName() { return orderName; } public void setOrderName(String orderName) { this.orderName = orderName; } }

3、实现AbstractGenerateExcelSheet 抽象类

/** * @author huijing xu * @date 2020/9/30 */ @Component public class OrderExcelSheet extends AbstractGenerateExcelSheet<OrderDetails> { @Override Field[] initFieldList() { return OrderDetails.class.getDeclaredFields(); } }

测试1：将数据集生成Excel

/** * @author huijing xu * @date 2020/9/30 */ public class TestExcel { @Autowired private OrderExcelSheet orderExcelSheet; @Autowired private NormalExportXlsxExcel normalExportXlsxExcel; @Test public void test() { // 填充数据 OrderDetails order1 = new OrderDetails(); order1.setOrderId(123456L); order1.setOrderAmount(new BigDecimal("2.5")); order1.setOrderName("棒棒糖"); OrderDetails order2 = new OrderDetails(); order2.setOrderId(666666L); order2.setOrderAmount(new BigDecimal("6.3")); order2.setOrderName("雪糕"); List<OrderDetails> orderDetails = Arrays.asList(order1, order2); // 生成ExcelSheet对象 ExcelSheet excelSheet = orderExcelSheet.generateSheet(orderDetails, "orders"); // 生成Xlsx格式的Excel文件 ByteArrayInputStream byteArrayInputStream = normalExportXlsxExcel.exportExcel(excelSheet); byte[] bytes = generateByte(byteArrayInputStream); FileOutputStream fileOutputStream = new FileOutputStream(new File("D:\\order.xlsx")); fileOutputStream.write(bytes); } private static byte[] generateByte(InputStream is) throws IOException { ByteArrayOutputStream output = new ByteArrayOutputStream(); byte[] buffer = new byte[1024]; int n = 0; while (-1 != (n = is.read(buffer))) { output.write(buffer, 0, n); } return output.toByteArray(); }

生成的效果：

测试2：将多个文件合并为一个文件

注意：标题行必须一致，否则取不到这列的值

文件order_other：

文件order_other_2：

@Test public void test2() throws IOException { byte[] bytes = readFromByteFile("D:\\order_other.xlsx"); byte[] bytes2 = readFromByteFile("D:\\order_other_2.xlsx"); List<byte[]> list = Arrays.asList(bytes, bytes2); // 将文件转换为ExcelSheet对象 ExcelSheet orderOtherSheet = normalExportXlsxExcel.mergeFirstSheetExcel(list); // 转换为自己所需要的ExcelSheet对象 ExcelSheet excelSheet = orderExcelSheet.convertExcelSheet(orderOtherSheet, "orders2"); // 生成Xlsx格式的Excel文件 ByteArrayInputStream byteArrayInputStream = normalExportXlsxExcel.exportExcel(excelSheet); byte[] bytesOut = generateByte(byteArrayInputStream); FileOutputStream fileOutputStream = new FileOutputStream(new File("D:\\order2.xlsx")); fileOutputStream.write(bytesOut); } public byte[] readFromByteFile(String pathname) throws IOException { File filename = new File(pathname); BufferedInputStream in = new BufferedInputStream(new FileInputStream(filename)); ByteArrayOutputStream out = new ByteArrayOutputStream(1024); byte[] temp = new byte[1024]; int size = 0; while ((size = in.read(temp)) != -1) { out.write(temp, 0, size); } in.close(); byte[] content = out.toByteArray(); return content; } private static byte[] generateByte(InputStream is) throws IOException { ByteArrayOutputStream output = new ByteArrayOutputStream(); byte[] buffer = new byte[1024]; int n = 0; while (-1 != (n = is.read(buffer))) { output.write(buffer, 0, n); } return output.toByteArray(); }

生成的效果：