Apache POI Excel 处理最佳实践

代码洁癖症患者

于 2024-06-30 13:43:42 发布

阅读量971

点赞数 21

文章标签： excel

本文链接：https://blog.csdn.net/qq_38701478/article/details/139867213

版权

1、需求背景

问题的背景是在需求设计的时候，我们在业务专家的配合下设计了一些表，但是为了方便的和他们讨论我们把表结构的描述通过Excel文件的方式记录了下来，然后我们需要根据excel文件中的内容生成对应的DDL。今天就给大家分享一下我们的解决方案

2、编码实现

2.1、思路设计

我们当时定义的格式是第一页存放目录，后续的每个sheet页存放一张表的表结构定义，具体的格式如下图所示

每个sheet页内容如下：

好了，Excel文件的格式就是这样的，我们下面来读取这个文件，然后根据文件内容生成建表的语句。

具体的实现思路如下

1、读取首页(目录页)的内容，将每行数据封装成一个对象，对象的属性有表名、序号、表中文名，将每行数据生成的对象存入到一个Map中,表名作为 Key,对象作为 Value。

2、遍历第一步中得到的Map,根据表名读取对应的 Sheet 页，从第二行开始遍历行，按照指定的列读取相关的字段信息，拼接成SQL。将每个 Sheet 页对应的SQL 缓存在Map中。表名作为Key,SQL作为 Value

3、遍历第二步中获取到的Map 将Value 按行写入到文件中

2.2、编码落地

2.2.1、引入依赖

首先我们需要用到的依赖如下

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

2.2.2、定义数据表对象

public class DataCatalogue {

    //页码
    private Integer id;
    //表名
    private String tableName;
    //表中文名
    private String tableMSg;

    //TODO 
    // setter  getter  ......
}

上述对象一共三个属性，其中序号合表名都可以用来读取后续的表信息，表中文名后续会拼接到SQL中

2.2.3、读取 Excel 文件

首先我们使用 FileInputStream 读取 Excel 文件，然后包装成 POI 工具包的 XSSFWorkbook 对象，该对象就是整个Excel工作簿的抽象，里面包含了整个工作簿的内容，相关代码如下

    /**
    * @Description  根据文件路径加载Excel文件
    * @Param [filePath]
    * @return org.apache.poi.xssf.usermodel.XSSFWorkbook
    * @Date 2024/6/29 下午 21:00
    * @Author wcan
    * @Version 1.0
    */
    public static XSSFWorkbook getWorkBook(String filePath) throws IOException {
        FileInputStream fileInputStream = new FileInputStream(new File(filePath));
        return new XSSFWorkbook(fileInputStream);
    }

读取表清单内容

/**
     * @return java.util.HashMap<java.lang.Integer, org.wcan.file.pojo.DataCatalogue>
     * @Description 读取目（表清单）录内容
     * @Param [workbook]
     * @Date 2024/6/29 下午 20:59
     * @Author wcan
     * @Version 1.0
     */
    public static HashMap<Integer, DataCatalogue> readTableListFromExcel(XSSFWorkbook workbook) throws IOException {
        //获取目录
        XSSFSheet catalogueSheet = workbook.getSheetAt(0);
        //将目录内容放到Map中
        HashMap<Integer, DataCatalogue> catalogueMap = new HashMap<>();
        catalogueSheet.forEach(row -> {
            int rowNum = row.getRowNum();
            if (rowNum > 0) {
                DataCatalogue dataCatalogue = new DataCatalogue();
                Iterator<Cell> iterator = row.iterator();
                while (iterator.hasNext()) {
                    Cell cell = iterator.next();
                    int columnIndex = cell.getColumnIndex();
                    String cellValue = cell.getStringCellValue();
                    //序号
                    if (columnIndex == 0) {
                        dataCatalogue.setId(Integer.valueOf(cellValue));
                    }
                    //表名
                    if (columnIndex == 1) {
                        dataCatalogue.setTableName(cellValue);
                    }
                    //中文名
                    if (columnIndex == 2) {
                        dataCatalogue.setTableMSg(cellValue);
                    }
                }
                catalogueMap.put(rowNum, dataCatalogue);
            }
        });
        return catalogueMap;
    }

生成SQL语句，需要注意的是这个我们为了兼容MySQL中的关键词，对所有的字段名和表名都加上了反引号

 /**
     * @return java.util.HashMap<java.lang.String, java.lang.String>
     * @Description 拼接SQL语句
     * @Param [catalogueMap, workbook]
     * @Date 2024/6/29 下午 20:58
     * @Author wcan
     * @Version 1.0
     */
    public static HashMap<String, String> ddlSQLBuilder(HashMap<Integer, DataCatalogue> catalogueMap, XSSFWorkbook workbook) {
        HashMap<String, String> dataBeanHashMap = new HashMap<>();
        Iterator<Map.Entry<Integer, DataCatalogue>> iterator = catalogueMap.entrySet().iterator();
        while (iterator.hasNext()) {
            Map.Entry<Integer, DataCatalogue> next = iterator.next();
            DataCatalogue dataCatalogue = next.getValue();
            //表名
            String tableName = dataCatalogue.getTableName();
            String tableMSg = dataCatalogue.getTableMSg();
            XSSFSheet sheet = workbook.getSheet(tableName);
            StringBuilder sqlBuilder = new StringBuilder();
            String sql = "CREATE TABLE IF NOT EXISTS  ";
            sqlBuilder.append(sql);
            StringBuilder primaryKeyBuilder = new StringBuilder();
            sqlBuilder.append("`").append(tableName).append("` (");
            sheet.forEach(row -> {
                int rowNum = row.getRowNum();
                if (rowNum > 0) {
                    Cell columnCell = row.getCell(0);
                    String columnName = columnCell.getStringCellValue();
                    if (null != columnName && columnName.length() > 0) ;
                    sqlBuilder.append("`").append(columnName.trim()).append("`").append(" ");

                    Cell dataTypeCell = row.getCell(2);
                    String dataType = dataTypeCell.getStringCellValue();
                    if (null != dataType && dataType.length() > 0) ;
                    sqlBuilder.append(dataType.trim());

                    Cell columnLengthCell = row.getCell(3);
                    if (null != columnLengthCell) {
                        String columnLength = columnLengthCell.getStringCellValue();
                        if (null != columnLength && columnLength.length() > 0) {
                            sqlBuilder.append("(").append(columnLength.trim()).append(")").append(" ");
                        }
                    }

                    Cell isNotNullCell = row.getCell(6);
                    if (null != isNotNullCell) {
                        String cellValue = isNotNullCell.getStringCellValue();
                        if ("YES".equals(cellValue))
                            sqlBuilder.append(" NOT NULL");
                    }

                    Cell columnMsgCell = row.getCell(8);
                    if (null != columnMsgCell) {
                        String cellValue = columnMsgCell.getStringCellValue();
                        if (null != cellValue && cellValue.length() > 0) {
                            sqlBuilder.append(" COMMENT ").append("'").append(cellValue.trim()).append("' ,");
                        }

                    }
                    Cell primaryKeyCell = row.getCell(5);
                    if (primaryKeyCell != null) {
                        String cellValue = primaryKeyCell.getStringCellValue();
                        if ("YES".equals(cellValue))
                            primaryKeyBuilder.append("PRIMARY KEY (`").append(columnName.trim()).append("`)");
                    }
                }
                sqlBuilder.append(" \n ");

            });
            sqlBuilder.append(primaryKeyBuilder.toString()).append(" \n )").append(" ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='")
                    .append(tableMSg).append("';");
            dataBeanHashMap.put(tableName, sqlBuilder.toString());
        }
        return dataBeanHashMap;
    }

最后将SQL语句按行写入文件中

 /**
    * @Description 将生成的SQL语句写入文件
    * @Param [ddlMsgMap, filePath, fileName]
    * @return void
    * @Date 2024/6/29 下午 21:01
    * @Author wcan
    * @Version 1.0
    */
    public static void builderDDLFile(HashMap<String, String> ddlMsgMap, String filePath, String fileName) throws IOException {
        BufferedWriter writer = new BufferedWriter(new FileWriter(filePath + File.separator + fileName));
        ddlMsgMap.forEach((key, value) -> {
            try {
                writer.write(value);
                writer.newLine();//换行
                writer.newLine();
            } catch (IOException e) {
                throw new RuntimeException(e);
            }
        });
        writer.close();
    }

2.2.4、功能测试

我们编写测试方法

public static void main(String[] args) throws IOException {
        String filePath = "D:\\project\\testfile\\数据字典.xlsx";
        XSSFWorkbook workBook = getWorkBook(filePath);
        HashMap<Integer, DataCatalogue> dataCatalogueMap = readTableListFromExcel(workBook);
        HashMap<String, String> ddlSQLMap = ddlSQLBuilder(dataCatalogueMap, workBook);
        builderDDLFile(ddlSQLMap, "D:\\project\\testfile\\", "ddl2.sql");
    }

我们去指定的位置查看文件，内容如下

至此功能实现成功了，大家也可以试试。

3、POI 最佳实践

上面我们使用POI工具实现了从Excel文件中获取表字段信息，生成SQL语句的功能，从上述案例中我们可以看出获取页和获取单元格的值我都写了不同的方式读取

3.1、获取Sheet页

上述案例中在获取Sheet页的时候我用了2种不同的方式，比如获取目录页的时候我是通过页码获取的，而后面我在处理表字段的时候是通过Sheet页的名字获取的。对于这两种方式大家可以灵活的选择。相关api方法如下：

            //获取总页数
            int numberOfSheets = workbook.getNumberOfSheets();
            //根据页码获取sheet名
            String sheetName = workbook.getSheetName(1);
            //根据sheet名称 获取sheet 页
            XSSFSheet sheet1 = workbook.getSheet(sheetName);

我们不难看出通过页码去读取Sheet页肯定效率要高一些，至于通过Sheet名获取是怎么实现的大家估计心里也很清楚，肯定就是迭代整个工作簿，一次判断每个Sheet页的名字。具体实现我们可以参见相关源码

    /**
     * Get sheet with the given name (case insensitive match)
     *
     * @param name of the sheet
     * @return XSSFSheet with the name provided or {@code null} if it does not exist
     */
    @Override
    public XSSFSheet getSheet(String name) {
        for (XSSFSheet sheet : sheets) {
            if (name.equalsIgnoreCase(sheet.getSheetName())) {
                return sheet;
            }
        }
        return null;
    }

上述代码片段来自 org.apache.poi.xssf.usermodel.XSSFWorkbook文件第1167行。

3.2、获取单元格的值

同样的我们在获取单元格的值的时候也使用了不同的方式

1、我们在读取首页(表目录)的时候循环读取当前Sheet页的每一行内容，然后通过while循环去读取每一行的每一列的值。

2、我们既然都知道了每一列都存放的是什么内容，那我们完全可以指定每一列的序号去读取，所以在第二次获取具体的表字段信息的时候我们是循环当前Sheet页的每一行，对于每一行数据我们直接通过列的序号获取对应的值。

关于这两种方式都可以获取到我们想要的内容，大家可以根据自己实际的业务场景选择。

3.3、关于数据类型

在上述案例的代码中大家可以发现我每次读取的时候都是使用的字符串类型的变量接收单元格里的数据，你如果运行上述代码很有可能会报错，报错的原因就是单元格类型并不是文本类型，所以你使用 getStringCellValue 去读数据的时候就会抛出异常。所以在使用上述代码的时候一定要记得将Excel文件中全部设置成文本格式的数据。

关于数据类型POI 也提供了一组解决方案，这里给大家总结了一下相关的api方法

   //获取某个单元格的值对象
   Cell dataTypeCell = row.getCell(2);
   //获取单元格的列
   int columnIndex = columnCell.getColumnIndex();
   //获取行
   Row row = dataTypeCell.getRow();
   //获取单元格类型 返回一个枚举
   CellType cellType = columnCell.getCellType();
   //按数字类型获取值 返回一个double类型的数字
   double numericCellValue = dataTypeCell.getNumericCellValue();
   //按字符串的类型湖区值
   String cellValue = dataTypeCell.getStringCellValue();
   //读取布尔值
   boolean booleanCellValue = dataTypeCell.getBooleanCellValue();
   //读取日期格式的数据
   Date dateCellValue = dataTypeCell.getDateCellValue();

因为本次案例中我是生成SQL语句，最终肯定是要当成文本内容写入到文件中的，所以我这里一律按照字符串类型处理，那么如果你需要解析不同类型的数据就可以使用上述提供的方法去解析

关于 POI 支持的数据类型，我们可以查看 org.apache.poi.ss.usermodel.CellType 这个枚举类

package org.apache.poi.ss.usermodel;

import org.apache.poi.ss.formula.FormulaType;
import org.apache.poi.util.Internal;

/**
 * @since POI 3.15 beta 3
 */
public enum CellType {
    /**
     * Unknown type, used to represent a state prior to initialization or the
     * lack of a concrete type.
     * For internal use only.
     */
    @Internal(since="POI 3.15 beta 3")
    _NONE(-1),

    /**
     * Numeric cell type (whole numbers, fractional numbers, dates)
     */
    NUMERIC(0),
    
    /** String (text) cell type */
    STRING(1),
    
    /**
     * Formula cell type
     * @see FormulaType
     */
    FORMULA(2),
    
    /**
     * Blank cell type
     */
    BLANK(3),
    
    /**
     * Boolean cell type
     */
    BOOLEAN(4),
    
    /**
     * Error cell type
     * @see FormulaError
     */
    ERROR(5);

    /**
     * @since POI 3.15 beta 3
     * @deprecated POI 3.15 beta 3
     */
    @Deprecated
    private final int code;

    private CellType(int code) {
        this.code = code;
    }

    /**
     * @since POI 3.15 beta 3.
     * @deprecated POI 3.15 beta 3. Used to transition code from <code>int</code>s to <code>CellType</code>s.
     */
    @Deprecated
    public static CellType forInt(int code) {
        for (CellType type : values()) {
            if (type.code == code) {
                return type;
            }
        }
        throw new IllegalArgumentException("Invalid CellType code: " + code);
    }
    
    /**
     * @since POI 3.15 beta 3
     * @deprecated POI 3.15 beta 3
     */
    @Deprecated
    public int getCode() {
        return code;
    }
    
}

3.4、总结

我们在读取某个单元格数据的时候原则上先要判断该单元格数据值类型，再根据类型使用对应的方法去读取这样就可以避免发生类型不匹配的异常，例如下面这段代码

     Cell dataTypeCell = row.getCell(2);
     CellType cellType = dataTypeCell.getCellType();

我们在实际项目中这么写的话可能会存在风险，假设有人上传了一个Excel文件,数字类型的值没有设置成文本内容，那么第二行代码就会发生异常，所以我们在读数据的时候需要先判断类型

    Object dataType = null;
    if (cellType == CellType.STRING) {
        dataType = dataTypeCell.getStringCellValue();
    }
    if (cellType == CellType.NUMERIC) {
        dataType = dataTypeCell.getNumericCellValue();
    }
    // .......

同样的我们读取出来的dataTypeCell 也需要事先判空，避免发生空指针异常。