基于ApachePOI导入Excel数据

最近在项目中用到了Apache POI插件，用来读取Excel中的数据，现做以下记录，方便后续使用。

Apache POI是一款用Java编写的免费开源的跨平台的Java API，其主要用于对Microsoft Office办公软件的读写。

Apache POI API组件

---

在使用Apache POI的组件时需要引入相关JAR包（本项目使用Maven构建，因此在pom.xml文件中引入相关JAR包）。

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

        <dependency>
            <groupId>commons-logging</groupId>
            <artifactId>commons-logging</artifactId>
            <version>1.2</version>
        </dependency>
        <dependency>
            <groupId>commons-codec</groupId>
            <artifactId>commons-codec</artifactId>
            <version>1.11</version>
        </dependency>
        <dependency>
            <groupId>org.apache.commons</groupId>
            <artifactId>commons-math3</artifactId>
            <version>3.6.1</version>
        </dependency>
        <dependency>
            <groupId>org.apache.commons</groupId>
            <artifactId>commons-collections4</artifactId>
            <version>4.2</version>
        </dependency>
        <dependency>
            <groupId>org.apache.commons</groupId>
            <artifactId>commons-compress</artifactId>
            <version>1.18</version>
        </dependency>
        <dependency>
            <groupId>org.apache.xmlbeans</groupId>
            <artifactId>xmlbeans</artifactId>
            <version>3.0.2</version>
        </dependency>
        <dependency>
            <groupId>log4j</groupId>
            <artifactId>log4j</artifactId>
            <version>1.2.17</version>
        </dependency>

1. 判断文件是否是Excel格式

    /*
     * 判断文件是否是Excel
     */
    public static void checkExcelValid(File file) throws Exception {
        if(!file.exists()) {
            throw new Exception("文件不存在");
        }
        if(!(file.isFile() && (file.getName().endsWith(EXCEL_XLS) 
                || file.getName().endsWith(EXCEL_XLSX)))) {
            throw new Exception("文件不是Excel");
        }
    }

    private static final String EXCEL_XLS= "xls";
    private static final String EXCEL_XLSX = "xlsx";

2. 根据Excel版本调用POI API获取Workbook(工作簿)

    /*
     * 根据Excel版本，获取WorkBook
     */
    public static Workbook getWorkBoox(InputStream is, 
            File file) throws IOException {
        Workbook wb = null;
        if(file.getName().endsWith(EXCEL_XLS)) {
            wb = new HSSFWorkbook(is);
        } else if(file.getName().endsWith(EXCEL_XLSX)) {
            wb = new XSSFWorkbook(is);
        }
        return wb;
    }

3. 使用文件流读取文件，并验证文件类型

    //创建文件对象
    File excelFile = new File("D:/docs/模板文件.xlsx");
    //文件流
    FileInputStream fis = new FileInputStream(excelFile);
    checkExcelValid(excelFile);

4. 获取Workbook

Workbook wb = getWorkBoox(fis, excelFile);

5. 设置需要读取的表格下标，从0开始，即第一个表格

Sheet sheet = wb.getSheetAt(0);
//工作表最后一行
int lastRow = sheet.getLastRowNum();

6. 获取表头信息

Row rowz = sheet.getRow(1);//表头
int[] arrInt = new int[18];
//rowz.getLastCellNum()工作表最后一列
for(int y = 0; y < rowz.getLastCellNum(); y++) {
    Cell cellz = rowz.getCell(y, MissingCellPolicy.RETURN_BLANK_AS_NULL);
    switchAppendArr(cellz, arrInt);
}

7. 循环行，并取单元格的值

//循环行
for(int m = 2; m < lastRow; m++) {
    Row row = sheet.getRow(m);
    if(row == null) {
        continue;
    }
    for(int i = 0; i < arrInt.length; i++) {
        Cell cell = row.getCell(arrInt[i], MissingCellPolicy.RETURN_BLANK_AS_NULL);
        System.out.print(returnTrueCellValue(cell) + " | ");
    }
    System.out.println();
}

8. 值类型

private static String returnTrueCellValue(Cell cell) {
    DecimalFormat dfz = new DecimalFormat("0");
    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
    DecimalFormat dfn = new DecimalFormat("0.00");
    String value = "";
    if(cell == null) {
        return "";
    }
    CellType ct = cell.getCellType();
    if(CellType.STRING.equals(ct)) {
        value = cell.getStringCellValue();
    } else if(CellType.NUMERIC.equals(ct)) {
        if("General".equals(cell.getCellStyle().getDataFormatString())) {
            value = dfz.format(cell.getNumericCellValue());
        } else if("m/d/yy".equals(cell.getCellStyle().getDataFormatString())) {
            value = sdf.format(cell.getDateCellValue());
        } else {
            value = dfn.format(cell.getNumericCellValue());
        }
    } else if(CellType.BOOLEAN.equals(ct)) {
        value = String.valueOf(cell.getBooleanCellValue());
    } else if(CellType.BLANK.equals(ct)) {
        value = "";
    }
    return value;
}

注意：最后将循环中取到的单元格值放入Bean中，然后将值插入数据库。
如果是上传形式的导入功能，则需要在spring-mvc.xml中配置multipartResolver

<!-- 多部分文件上传 -->
<bean id="multipartResolver" class="org.springframework.web.multipart.commons.CommonsMultipartResolver">
    <property name="maxUploadSize" value="104857600" />
    <property name="maxInMemorySize" value="4096" />
    <property name="defaultEncoding" value="UTF-8"></property>
</bean>