前言

在數(shù)據(jù)倉庫中坟漱，ETL最基礎(chǔ)的步驟就是從數(shù)據(jù)源抽取所需的數(shù)據(jù)购笆，這里所說的數(shù)據(jù)源并非僅僅是指數(shù)據(jù)庫髓帽，還包括excel叶雹、csv瞻鹏、xml等各種類型的數(shù)據(jù)接口文件规哲，而這些文件中的數(shù)據(jù)不一定是結(jié)構(gòu)化存儲的撩笆，比如各種各樣的報表文件唆貌，往往是一些復(fù)雜的表格結(jié)構(gòu)谣蠢，其中不僅有我們需要的數(shù)據(jù)粟耻，還有一些冗余的查近、無價值的數(shù)據(jù)，這時我們就無法直接用一般數(shù)據(jù)加載工具直接讀取入庫了挤忙。也許你會想霜威，數(shù)據(jù)源導(dǎo)出文件前先處理好數(shù)據(jù)就行了。然而册烈，實際開發(fā)中數(shù)據(jù)源往往是多個的戈泼，而且涉及到不同的部門甚至公司，這其間難免會出現(xiàn)各種麻煩赏僧，甚至有些數(shù)據(jù)文件還是純手工處理的大猛，不一定能給到你滿意的數(shù)據(jù)格式。所以我們不討論誰該負責(zé)轉(zhuǎn)換的問題淀零，這里主要介紹如何使用Apache POI來從Excel數(shù)據(jù)文件中讀取我們想要的數(shù)據(jù)挽绩，以及用Bean Validation對數(shù)據(jù)內(nèi)容按照預(yù)定的規(guī)則進行校驗。

文章要點：

• Apache POI是什么
• 如何使用Apache POI讀取Excel文件
• 使用Bean Validation進行數(shù)據(jù)校驗
• Excel讀取工具類
• 使用實例

Apache POI是什么

Apache POI是用Java編寫的免費開源的跨平臺的Java API窑滞，提供API給Java程式對Microsoft Office格式檔案進行讀和寫的操作琼牧。

如何使用Apache POI處理Excel文件

1、導(dǎo)入Maven依賴

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

2哀卫、創(chuàng)建Workbook實例

這里需要注意的是Excel文檔的版本問題巨坊，Excel2003及以前版本的文檔使用HSSFWorkbook對象，Excel2007及之后版本使用HSSFWorkbook對象

// Excel2003及以前版本
Workbook workbook = new XSSFWorkbook(new FileInputStream(path));
// Excel2007及之后版本
Workbook workbook = new HSSFWorkbook(new FileInputStream(path));

3此改、獲取Sheet表格頁對象

Sheet是Excel文檔中的工作簿即表格頁面趾撵，讀取前要先找到數(shù)據(jù)所在頁面，可以通過標簽名或者索引的方式獲取指定Sheet對象

// 按索引獲取
Sheet sheet = workbook.getSheetAt(index);
// 按標簽名獲取
Sheet sheet = workbook.getSheet(label);

4共啃、獲取Cell單元格對象

// 行索引row和列索引col都是以 0 起始
Cell cell = sheet.getRow(row).getCell(col);

5占调、獲取單元格內(nèi)容

獲取單元格的值之前首先要獲知單元格內(nèi)容的類型，在Excel中單元格有6種類型：

1. CELL_TYPE_BLANK ：空值
2. CELL_TYPE_BOOLEAN ：布爾型
3. CELL_TYPE_ERROR ： 錯誤
4. CELL_TYPE_FORMULA ：公式型
5. CELL_TYPE_STRING：字符串型
6. CELL_TYPE_NUMERIC：數(shù)值型

各種類型的內(nèi)容還需要進一步判斷其數(shù)據(jù)格式移剪，例如單元格的Type為CELL_TYPE_NUMERIC時究珊，它有可能是Date類型，在Excel中的Date類型是以Double類型的數(shù)字存儲的纵苛，不同類型的值要調(diào)用cell對象相應(yīng)的方法去獲取剿涮，具體情況具體分析

public Object getCellValue(Cell cell) {
    if(cell == null) {
        return null;
    }
    switch (cell.getCellType()) {
    case Cell.CELL_TYPE_STRING:
        return cell.getRichStringCellValue().getString();
    case Cell.CELL_TYPE_NUMERIC:
        if (DateUtil.isCellDateFormatted(cell)) {
            return cell.getDateCellValue();
        } else {
            return cell.getNumericCellValue();
        }
    case Cell.CELL_TYPE_BOOLEAN:
        return cell.getBooleanCellValue();
    case Cell.CELL_TYPE_FORMULA:
        return formula.evaluate(cell).getNumberValue();
    default:
        return null;
    }
}

6、關(guān)閉Workbook對象

workbook.close();

使用Bean Validation進行數(shù)據(jù)校驗

當你要處理一個業(yè)務(wù)邏輯時攻人，數(shù)據(jù)校驗是你不得不考慮和面對的事情取试，程序必須通過某種手段來確保輸入進來的數(shù)據(jù)從語義上來講是正確的或者符合預(yù)定義的格式，一個Java程序一般是分層設(shè)計的怀吻，而不同的層可能是不同的開發(fā)人員來完成瞬浓，這樣就很容易出現(xiàn)不同的層重復(fù)進行數(shù)據(jù)驗證邏輯，導(dǎo)致代碼冗余等問題蓬坡。為了避免這樣的情況發(fā)生猿棉，最好是將驗證邏輯與相應(yīng)的模型進行綁定磅叛。

Bean Validation 規(guī)范的目標就是避免多層驗證的重復(fù)性，它提供了對 Java EE 和 Java SE 中的 Java Bean 進行驗證的方式铺根。該規(guī)范主要使用注解的方式來實現(xiàn)對 Java Bean 的驗證功能宪躯，從而使驗證邏輯從業(yè)務(wù)代碼中分離出來。

Hibernate Validator 是 Bean Validation 規(guī)范的參考實現(xiàn)位迂，我們可以用它來實現(xiàn)數(shù)據(jù)驗證邏輯，其Maven依賴如下：

<dependency>
    <groupId>org.hibernate</groupId>
    <artifactId>hibernate-validator</artifactId>
    <version>5.1.3.Final</version>
</dependency>
<dependency>
    <groupId>javax.el</groupId>
    <artifactId>javax.el-api</artifactId>
    <version>2.2.4</version>
</dependency>

關(guān)于Bean Validation的詳細介紹可參考以下文章：
JSR 303 - Bean Validation 介紹及最佳實踐
Bean Validation 技術(shù)規(guī)范特性概述

Excel讀取工具類

我們要達到的效果是详瑞，模擬游標的方式構(gòu)建一個Excel讀取工具類ExcelReadHelper掂林，然后加載Excel文件流來創(chuàng)建工具類實例，通過這個實例我們可以像游標一樣設(shè)置當前的行和列坝橡，定好位置之后讀取出單元格的值并進行校驗泻帮，完成對Excel文件的讀取校驗操作。既然是讀取還有校驗數(shù)據(jù)计寇，異常處理和提示當然是至關(guān)重要的锣杂，所以還要有人性化的異常處理方式，方便程序使用者發(fā)現(xiàn)Excel中格式或內(nèi)容有誤的地方番宁，具體到哪一行哪一項元莫，出現(xiàn)的問題是什么。

ExcelReadHelper工具類主體

public class ExcelReadHelper {
    private static ValidatorFactory factory = Validation.buildDefaultValidatorFactory();
    //文件絕對路徑
    private String excelUrl;
    private Workbook workbook;
    private Sheet sheet;
    //Sheet總數(shù)
    private int sheetCount;
    //當前行
    private Row row;
    private Validator validator;
    
    public ExcelReadHelper(File excelFile) throws ExcelException {
        validator = factory.getValidator();
        excelUrl = excelFile.getAbsolutePath();
        //判斷工作簿版本
        String fileName = excelFile.getName();
        String suffix = fileName.substring(fileName.lastIndexOf("."));
        try {
            if(suffix.equals(".xlsx")) {
                workbook = new XSSFWorkbook(new FileInputStream(excelFile));
            } else if(suffix.equals(".xls")) {
                workbook = new HSSFWorkbook(new FileInputStream(excelFile));
            } else {
                throw new ExcelException("Malformed excel file");
            }
        } catch(Exception e) {
            throw new ExcelException(excelUrl, e);
        }
        sheetCount = workbook.getNumberOfSheets();
    }
    
    /**
     * 關(guān)閉工作簿
     * @throws ExcelException 
     * @throws IOException
     */
    public void close() throws ExcelException {
        if (workbook != null) {
            try {
                workbook.close();
            } catch (IOException e) {
                throw new ExcelException(excelUrl, e);
            }
        }
    }
    
    /**
     * 獲取單元格真實位置
     * @param row 行索引
     * @param col 列索引
     * @return [行,列]
     */
    public String getCellLoc(Integer row, Integer col) {
        return String.format("[%s,%s]", row + 1, CellReference.convertNumToColString(col));     
    }
    
    /**
     * 根據(jù)標簽設(shè)置Sheet
     * @param labels
     * @throws ExcelException
     */
    public void setSheetByLabel(String... labels) throws ExcelException {
        Sheet sheet = null;
        for(String label : labels) {
            sheet = workbook.getSheet(label);
            if(sheet != null) {
                break;
            }
        }
        if(sheet == null) {
            StringBuilder sheetStr = new StringBuilder();
            for (String label : labels) {
                sheetStr.append(label).append(",");
            }
            sheetStr.deleteCharAt(sheetStr.lastIndexOf(","));
            throw new ExcelException(excelUrl, sheetStr.toString(), "Sheet does not exist");
        }
        this.sheet = sheet;
    }
    
    /**
     * 根據(jù)索引設(shè)置Sheet
     * @param index
     * @throws ExcelException 
     */
    public void setSheetAt(Integer index) throws ExcelException {
        Sheet sheet = workbook.getSheetAt(index);
        if(sheet == null) {
            throw new ExcelException(excelUrl, index + "", "Sheet does not exist");
        }
        this.sheet = sheet;
    }

    /**
     * 獲取單元格內(nèi)容并轉(zhuǎn)為String類型
     * @param row 行索引
     * @param col 列索引
     * @return
     */
    @SuppressWarnings("deprecation")
    public String getValueAt(Integer row, Integer col) {
        Cell cell = sheet.getRow(row).getCell(col);
        String value = null;
        if (cell != null) {
          switch (cell.getCellType()) {
            case Cell.CELL_TYPE_STRING:
                value = cell.getStringCellValue() + "";
                break;
            case Cell.CELL_TYPE_NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    value = cell.getDateCellValue().getTime() + "";
                } else {
                    double num = cell.getNumericCellValue();
                    if(num % 1 == 0) {
                        value = Double.valueOf(num).intValue() + "";
                    } else {
                        value = num + "";
                    }
                }
                break;
            case Cell.CELL_TYPE_FORMULA:
                value = cell.getNumericCellValue() + "";
                break;
            case Cell.CELL_TYPE_BOOLEAN:
                value = String.valueOf(cell.getBooleanCellValue()) + "";
                break;
          }
        }
        return (value == null || value.isEmpty()) ? null : value.trim();
    }
    
    /**
     * 獲取當前行指定列內(nèi)容
     * @param col 列索引
     * @return
     */
    public String getValue(Integer col) {
        return getValueAt(row.getRowNum(), col);
    }
    
    /**
     * 獲取Sheet名稱
     * @return
     */
    public String getSheetLabel() {
        String label = null;
        if(sheet != null) {
            label = sheet.getSheetName();
        }
        return label;
    }
    
    /**
     * 行偏移
     * @param offset 偏移量
     * @return
     */
    public Boolean offsetRow(Integer offset) {
        Boolean state = true;
        if(row == null) {
            row = sheet.getRow(offset-1);
        } else {
            row = sheet.getRow(row.getRowNum() + offset);
            if(row == null) {
                state = false;
            }
        }
        return state;
    }
    
    /**
     * 設(shè)置行
     * @param index 索引
     * @return
     */
    public Boolean setRow(Integer index) {
        row = sheet.getRow(index);
        return row != null;
    }
    
    /**
     * 偏移一行
     * @return
     */
    public Boolean nextRow() {
        return offsetRow(1);
    }
    
    /**
     * 偏移到下一個Sheet
     * @return
     */
    public Boolean nextSheet() {
        Boolean state = true;
        if(sheet == null) {
            sheet = workbook.getSheetAt(0);
        } else {
            int index = workbook.getSheetIndex(sheet) + 1;
            if(index >= sheetCount) {
                sheet = null;
            } else {
                sheet = workbook.getSheetAt(index);
            }
            
            if(sheet == null) {
                state = false;
            }
        }
        row = null;
        return state;
    }
    
    /**
     * 數(shù)據(jù)校驗
     * @param obj 校驗對象
     * @throws ExcelException
     */
    public <T> void validate(T obj) throws ExcelException {
        Set<ConstraintViolation<T>> constraintViolations = validator.validate(obj);
        if(constraintViolations.size() > 0) {
            Iterator<ConstraintViolation<T>> iterable = constraintViolations.iterator();
            ConstraintViolation<T> cv = iterable.next();
            throw new ExcelException(excelUrl, sheet.getSheetName(), row.getRowNum() + 1 + "", 
                    String.format("%s=%s:%s", cv.getPropertyPath(), cv.getInvalidValue(), cv.getMessage()));
        }
    }

    /**
     * 拋出當前Sheet指定行異常
     * @param row 異常發(fā)生行索引
     * @param message 異常信息
     * @return
     */
    public ExcelException excelRowException(Integer row, String message) {
        return new ExcelException(excelUrl, sheet.getSheetName(), row + 1 + "", message);
    }

    /**
     * 拋出當前行異常
     * @param message 異常信息
     * @return
     */
    public ExcelException excelCurRowException(String message) {
        return new ExcelException(excelUrl, sheet.getSheetName(), row.getRowNum() + 1 + "", message);
    }

    /**
     * 拋出自定義異常
     * @param message 異常信息
     * @return
     */
    public ExcelException excelException(String message) {
        return new ExcelException(excelUrl, message);
    }
}

ExcelException異常類

public class ExcelException extends Exception {

    public ExcelException() {
        super();
    }

    public ExcelException(String message) {
        super(message);
    }

    public ExcelException(String url, String message) {
        super(String.format("EXCEL[%s]：%s", url, message));
    }
    
    public ExcelException(String url, String sheet, String message) {
        super(String.format("EXCEL[%s],SHEET[%s]：%s", url, sheet, message));
    }
    
    public ExcelException(String url, String sheet, String row, String message) {
        super(String.format("EXCEL[%s],SHEET[%s],ROW[%s]：%s", url, sheet, row, message));
    }
    
    public ExcelException(String url, Throwable cause) {
        super(String.format("EXCEL[%s]", url), cause);
    }
    
}

使用實例

// 使用Excel文件對象初始化ExcelReadHelper
ExcelReadHelper excel = new ExcelReadHelper(file);
        
// 第一頁
excel.setSheetAt(0);
        
// “Sheet1”頁
excel.setSheetByLabel("Sheet1");
        
// 下一頁
excel.nextSheet();
        
// 第一行（以 0 起始）
excel.setRow(0);
        
// 下一行
excel.nextRow();
        
// 偏移兩行
excel.offsetRow(2);
        
// 當前行第一列的值
String value1 = excel.getValue(0);
        
// 第一行第一列的值
String value2 = excel.getValueAt(0,0);
        
// 獲取單元格真實位置（如索引都為0時結(jié)果為[1,A]）
String location = excel.getCellLoc(0,0);
        
// 當前頁標題（如“Sheet1”）
String label = excel.getSheetLabel();
        
// 校驗讀取的數(shù)據(jù)
try {
    excel.validate(obj);
} catch (ExcelException e) {
    // 錯誤信息中包含具體錯誤位置以及原因
    e.printStackTrace();
}

//拋出異常蝶押，結(jié)果自動包含出現(xiàn)異常的Excel路徑
throw excel.excelException(message);

//拋出指定行異常踱蠢，結(jié)果自動包含出現(xiàn)錯誤的Excel路徑、當前頁位置
throw excel.excelRowException(0, message);

//拋出當前行異常棋电，結(jié)果自動包含出現(xiàn)錯誤的Excel路徑茎截、當前頁、當前行位置
throw excel.excelCurRowException(message);

//關(guān)閉工作簿W(wǎng)orkbook對象
excel.close();

本文為作者kMacro原創(chuàng)赶盔，轉(zhuǎn)載請注明來源：https://zkhdev.github.io/2018/10/14/java-dev6/