Apache POI读取Excel工具类

1
2
3
4
5
6
7
8
9
10
11
12
13
14
    <!-- POI -->
    <dependency>
      <groupId>org.apache.poi</groupId>
      <artifactId>poi</artifactId>
      <version>4.1.2</version>
    </dependency>

    <!-- POI-ooxml -->
    <dependency>
      <groupId>org.apache.poi</groupId>
      <artifactId>poi-ooxml</artifactId>
      <version>4.1.2</version>
    </dependency>

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
/**
 * 封装Apache的POI工具类
 * <p>
 * 主要功能：读取Excel，获取文件内容
 * </p>
 *
 * @author ACE_GJH
 * @date 2020/11/20
 */
public class ApachePoiUtil {

    private static final Logger log = LoggerFactory.getLogger(ApachePoiUtil.class);

    /**
     * 读取Excel内容
     *
     * @param fileName 文件名
     * @param inputStream 文件输入流
     * @param sheetName 工作表名
     * @param startRow 开始行数
     * @param endRow 结束行数
     * @param startCol 开始列数
     * @param endCol 结束列数
     * @return 行列集合
     * @throws IOException
     */
    public static List<String[]> readExcel(String fileName, InputStream inputStream, String sheetName, Integer startRow, Integer endRow, Integer startCol, Integer endCol) throws IOException {
        //Excel文件类型检验，并获取工作簿对象
        Workbook workbook = getWorkbook(fileName, inputStream);
        if (workbook == null) {
            inputStream.close();
            throw new IOException("The file name suffix is not .xls or .XLSX or .xls or .XLSX");
        }
        //读取工作表
        Sheet sheet = workbook.getSheet(sheetName);
        //确定行数
        int lastRowNum = sheet.getLastRowNum();
        if (startRow == null || startRow < 0) {
            startRow = 0;
        }
        if (startRow > lastRowNum) {
            workbook.close();
            return null;
        }

        if (endRow == null) {
            endRow = lastRowNum;
        }
        if(endRow < startRow) {
            workbook.close();
            return null;
        }else {
            endRow = Math.min(lastRowNum, endRow);
        }
        //确定列数
        if (startCol == null || startCol < 0) {
            startCol = 0;
        }
        if (endCol == null || endCol < startCol) {
            workbook.close();
            throw new IOException("endCol is illegal");
        }

        int length = endCol - startCol + 1;
        //获取数据
        ArrayList<String[]> rows = new ArrayList<>(length * 4 / 3);
        for (Integer i = startRow; i <= endRow; i++) {
            //获取第i行数据
            Row row = sheet.getRow(i);
            String[] cols = new String[length];
            for (int j = startCol; j <= endCol; j++) {
                Cell cell = row.getCell(j);
                cols[j] = getContentByCellType(cell);
            }
            rows.add(cols);
        }

        workbook.close();
        return rows;
    }

    /**
     * 根据单元格类型获取单元格内容
     *
     * @param cell Excel单元格
     * @return
     */
    private static String getContentByCellType(Cell cell) {
        String value = null;
        switch (cell.getCellType()) {
            //数字
            case NUMERIC:
                //如果为时间格式的内容
                if (DateUtil.isCellDateFormatted(cell)) {
                    //注：format格式 yyyy-MM-dd hh:mm:ss 中小时为12小时制，若要24小时制，则把小h变为H即可，yyyy-MM-dd HH:mm:ss
                    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                    value = sdf.format(DateUtil.getJavaDate(cell.getNumericCellValue()));
                    break;
                } else {
                    value = Double.toString(cell.getNumericCellValue());
                }
                break;
                //字符串
            case STRING:
                value = cell.getStringCellValue();
                break;
                //Boolean
            case BOOLEAN:
                value = Boolean.toString(cell.getBooleanCellValue());
                break;
                //公式
            case FORMULA:
                value = cell.getCellFormula();
                break;
                //空值
            case BLANK:
                break;
                //非法字符
            case ERROR:
                break;
                //未知字符
            default:
                break;
        }
        return value;
    }

    /**
     * 判断是否是03的excel: .xls .XLS
     */
    private static boolean isExcel2003(String filePath) {
        return filePath.matches("^.+\.(?i)(xls)$");
    }

    /**
     * 判断是否是07的excel: .xlsx .XLSX
     */
    private static boolean isExcel2007(String filePath) {
        return filePath.matches("^.+\.(?i)(xlsx)$");
    }

    /**
     * 获取工作簿对象
     */
    private static Workbook getWorkbook(String fileName, InputStream inputStream) throws IOException {
        if (isExcel2007(fileName)) {
            return new XSSFWorkbook(inputStream);
        }
        if (isExcel2003(fileName)) {
            return new HSSFWorkbook(inputStream);
        }
        return null;
    }

}