poi解析Excel內容

南海比翼發表於2020-08-26
Excel

poi可以將指定目錄下的Excel中的內容解析、讀取到java程式中。下面是一個Demo：

使用poi需要導下包，如下：

首先是準備讀取的Excel表，存放在“E:\programming\備份資料\工單分析結果_2020年7月.xlsx”下：
其他的sheet頁（一共9個可見Sheet頁），和下面圖中的內容也都類似，只是資料稍微不同。

為了將Excel表中的資料儲存在java程式中，下面建立了一個WorkOrder工單的實體類:

先大概看下主執行緒中的程式碼：

poi解析Excel內容，是需要先建立一個工作簿物件的。
但是因為Microsoft Excel的版本不同，所以檔案字尾名也有所不同，例如.xls和.xlsx。
所以我們需要根據解析的Excel的字尾名，自動建立出對應型別的工作簿物件，程式碼如下：

/**
   * HSSF讀寫.xls格式檔案
   * XSSF讀寫.xlsx格式檔案
   * SXSSF讀寫.xlsx格式檔案
   * HWPE讀寫doc格式檔案
   * HSLF讀寫PowerPoint檔案
   * HDGF讀寫visio格式檔案
   * HPBF讀寫oublisher格式檔案
   * HSMF讀寫outlook檔案
*/

/**
 * 根據字尾名建立對應的工作簿物件
 * @param fileType
 * @param is
 * @return WorkBook
 * @throws IOException
 */
public static Workbook getWorkBook(String fileType,InputStream is) throws IOException {
    //根據檔案字尾名確定需要建立的工作簿物件（這裡只解析.xls和.xlsx字尾名的Excel）
    if(fileType.equalsIgnoreCase("xls")){
        workbook = new HSSFWorkbook(is);
    }else if(fileType.equalsIgnoreCase("xlsx")){
        workbook = new XSSFWorkbook(is);
    }
    return workbook;
}

有了自動建立工作簿物件型別的方法之後，我們開始編寫讀取Excel的方法readExcel()，如下：

/**
 * 讀取Excel
 * @param FileUrl
 * @return String
 */
public static List<WorkOrder> readExcel(String FileUrl){
    //Workbook workbook = null;
    FileInputStream fis = null;

    try {
        String fileType = FileUrl.substring(FileUrl.lastIndexOf(".")+1,FileUrl.length()); //獲取檔案字尾名
        File file = new File(FileUrl); //獲取Excel物件
        if(!file.exists()){ //判斷路徑是否正確
            System.out.println("檔案不存在！");
            return null;
        }

        fis = new FileInputStream(file); //將檔案物件寫入流
        workbook = getWorkBook(fileType,fis); //根據檔案字尾名，自動建立對應型別的工作簿物件

        List<WorkOrder> resulet = parseExcel(workbook); //解析Excel，獲取工作簿中的值

        return resulet;
    } catch (FileNotFoundException e) {
        e.printStackTrace();
    } catch (IOException e) {
        e.printStackTrace();
    } finally {
        if(null != fis){
            try {
                fis.close();    //關閉流物件
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }
    return null;
}

解析Ecxel的方法需要我們自己編寫，如下：

/**
 * 解析Excel中的內容
 * @param workbook
 * @return List<WorkOrder>
 */
public static List<WorkOrder> parseExcel(Workbook workbook){
    List<WorkOrder> resultList = new ArrayList<>(); //建立返回值接收物件

    //解析Sheet數量，確定迴圈解析次數（這裡不做Sheet的隱蔽性校驗）
    for (int sheetNum=0; sheetNum<workbook.getNumberOfSheets(); sheetNum++){
        sheet = workbook.getSheetAt(sheetNum); //根據下標獲取每個Sheet頁物件（下標從0開始）
        if(sheet==null){ //檢驗sheet合法性
            continue;
        }

        Row firstRow = sheet.getRow(sheet.getFirstRowNum()); //獲取第一行資料
        if(null == firstRow){ //判斷第一行資料是否為空
            System.out.println("第一行沒有讀取到任何資料！");
        }

        //解析每行資料，構造資料物件
        int rowStart = sheet.getFirstRowNum() + 2; //根據模板確定開始解析內容的行標
        int rowEnd = sheet.getPhysicalNumberOfRows(); //結束的行標
        for(int rowNum=rowStart; rowNum<rowEnd; rowNum++){
            Row row = sheet.getRow(rowNum); //根據下標逐次獲取行物件
            //判斷該行物件（及第一個單元格）是否為空
            if(row==null || "".equals(convertCellValueToString(row.getCell(0))) || null==convertCellValueToString(row.getCell(0))){
                continue;
            }

            WorkOrder workOrder = convertRowToData(row); //自定義方法，用於解析行中單元格內容，並將每行內容封裝為一個WorkOrder物件
            if(workbook == null){
                System.out.println("獲取的資料為空！");
            }
            resultList.add(workOrder);
        }

    }
    return resultList;
}

Excel的單元格中，填寫的內容可能是日期、數字、字串、布林值等，也有可能是其他的意想不到的型別。
為了能夠順利解析內容，我們需要自定義一個單元格內容型別轉換為String型別的方法——convertRowToData()，如下：

/**
 * 型別轉換（無論什麼型別的值都將轉換為String型別）
 * @param cell
 * @return String
 */
public static String convertCellValueToString(Cell cell){
    FormulaEvaluator formulaEvaluator = workbook.getCreationHelper().createFormulaEvaluator();  //用於解析和處理Cell公式的介面物件
    if(cell==null){ //判斷單元格物件是否為空（這裡其實可以不做判斷，因為poi原始碼中有對null進行處理的程式碼）
        return null;
    }
    String returnValue = null;
    switch (cell.getCellType()){
        case Cell.CELL_TYPE_BLANK:   //空值
            break;
        case Cell.CELL_TYPE_BOOLEAN: //布林
            returnValue = String.valueOf(cell.getBooleanCellValue());
            break;
        case Cell.CELL_TYPE_ERROR:   //異常
            returnValue = "非法字元";
            break;
        case Cell.CELL_TYPE_NUMERIC: //數值和日期
            if(HSSFDateUtil.isCellDateFormatted(cell)){      //處理日期、時間格式
                SimpleDateFormat sdf = null;
                if(cell.getCellStyle().getDataFormat()==14){
                    sdf = new SimpleDateFormat("yyyy/MM/dd");
                }else if(cell.getCellStyle().getDataFormat()==21){
                    sdf = new SimpleDateFormat("HH:mm:ss");
                }else if(cell.getCellStyle().getDataFormat()==22){
                    sdf = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
                }else{
                    throw new RuntimeException("日期格式錯誤!");
                }
                Date date = cell.getDateCellValue();
                returnValue = sdf.format(date);
            }else if(cell.getCellStyle().getDataFormat()==0){ //處理數值格式
                cell.setCellType(Cell.CELL_TYPE_STRING);
                returnValue = String.valueOf(cell.getRichStringCellValue().getString());
            }
            break;
        case Cell.CELL_TYPE_FORMULA: //公式————這個公式的處理方式需要特別注意一下，文末我會特意再說明一下這個公式的特殊
            if(cell.getCachedFormulaResultType()==Cell.CELL_TYPE_NUMERIC){
                returnValue = String.valueOf(cell.getNumericCellValue());
            }else if(cell.getCachedFormulaResultType()==Cell.CELL_TYPE_STRING){
                returnValue = String.valueOf(cell.getStringCellValue());
            }
            break;
        case Cell.CELL_TYPE_STRING: //字串
            returnValue = String.valueOf(cell.getStringCellValue());
            break;
        default:    //其他
            returnValue="未知型別";
            break;
    }
    return returnValue;
}

最後就是用來封裝每行資料為一個WorkOrder物件的方法了，這個很簡單，如下：

/**
 * 將單元格中的內容轉換為String並封裝為WorkOrder物件
 * @param row
 * @return WorkOrder
 */
public static WorkOrder convertRowToData(Row row){
    WorkOrder workOrder = new WorkOrder();
    int cellNum = 0;
    Cell cell = null;

    workOrder.setSheetName(sheet.getSheetName());                           //獲取Sheet名字
    workOrder.setMonth(convertCellValueToString(row.getCell(cellNum++)));   //獲取月份資訊——cellNum++是因為Excel是固定的模板，模板的前兩行內容是固定格式，沒必要讀取
    workOrder.setMonth1(convertCellValueToString(row.getCell(cellNum++)));  //1月
    workOrder.setMonth2(convertCellValueToString(row.getCell(cellNum++)));  //2月
    workOrder.setMonth3(convertCellValueToString(row.getCell(cellNum++)));  //3月
    workOrder.setMonth4(convertCellValueToString(row.getCell(cellNum++)));  //4月
    workOrder.setMonth5(convertCellValueToString(row.getCell(cellNum++)));  //5月
    workOrder.setMonth6(convertCellValueToString(row.getCell(cellNum++)));  //6月
    workOrder.setMonth7(convertCellValueToString(row.getCell(cellNum++)));  //7月
    workOrder.setMonth8(convertCellValueToString(row.getCell(cellNum++)));  //8月
    workOrder.setMonth9(convertCellValueToString(row.getCell(cellNum++)));  //9月
    workOrder.setMonth10(convertCellValueToString(row.getCell(cellNum++))); //10月
    workOrder.setMonth11(convertCellValueToString(row.getCell(cellNum++))); //11月
    workOrder.setMonth12(convertCellValueToString(row.getCell(cellNum++))); //12月

    return workOrder;
}

程式編寫到這裡，已經差不多了，執行一下，我們看下結果：

一開始看到執行結果，我很蒙，前9個Sheet頁的資料很正常。
但是明明沒有什麼“基礎資料”的Sheet，那這些資料到底是從哪兒解析出來的（當初沒考office計算機證，現在覺得腦殼痛...）？
難道是解析過程出錯了？於是開始了漫長的問題排查過程...
最後在需要解析的Ecxel表本身中找到了答案：Sheet可以被隱藏，隱藏後是看不到的（內心毫無波瀾...），如下：

根據Excel的Sheet頁可以被隱藏的特性，加上剛剛程式執行出來的結果來一起推理，我們可以知道：
poi讀取Excel內容的時候，即便是Sheet頁被隱藏，內容依舊是可以被讀取出來的！
但是我們不妨想一下，既然Sheet頁被隱藏，那說明隱藏者認為該Sheet頁的資料不再使用、或不希望被讀取，
那麼，我們就需要在程式中做處理，即被隱藏的Sheet頁的資料不再進行解析，這部分處理程式碼我寫在parseExcel()中，如下：

/**
 * 解析Excel中的內容
 * @param workbook
 * @return List<WorkOrder>
 */
public static List<WorkOrder> parseExcel(Workbook workbook){
    List<WorkOrder> resultList = new ArrayList<>(); //建立返回值接收物件

    //解析Sheet數量，確定迴圈解析次數（這裡不做Sheet的隱蔽性校驗）
    for (int sheetNum=0; sheetNum<workbook.getNumberOfSheets(); sheetNum++){
        //如果Sheet是隱蔽的或非常隱蔽的，則不解析Sheet內容
        if(workbook.isSheetHidden(sheetNum) || workbook.isSheetVeryHidden(sheetNum)){
            continue;
        }

        sheet = workbook.getSheetAt(sheetNum);
        if(sheet==null){ //檢驗sheet合法性
            continue;
        }

        Row firstRow = sheet.getRow(sheet.getFirstRowNum()); //獲取第一行資料
        if(null == firstRow){ //判斷第一行資料是否為空
            System.out.println("第一行沒有讀取到任何資料！");
        }

        //解析每行資料，構造資料物件
        int rowStart = sheet.getFirstRowNum() + 2;
        int rowEnd = sheet.getPhysicalNumberOfRows();
        for(int rowNum=rowStart; rowNum<rowEnd; rowNum++){
            Row row = sheet.getRow(rowNum); //根據下標逐次獲取行物件
            //判斷該行物件是否為空
            if(row==null || "".equals(convertCellValueToString(row.getCell(0))) || null==convertCellValueToString(row.getCell(0))){
                continue;
            }

            WorkOrder workOrder = convertRowToData(row); //解析行中單元格內容
            if(workbook == null){
                System.out.println("獲取的資料為空！");
            }
            resultList.add(workOrder);
        }

    }
    return resultList;
}

這裡用到的isSheetHidden()和isSheetVeryHidden()是我在poi原始碼中找到的，
主要是用於判斷Sheet頁是否是Hidden（隱蔽）或VeryHidden（非常隱蔽），它們的返回值都是Boolean型別的，
具體可參見Excel中對於Sheet頁的Visible設定，更能幫助理解，如下：

我在百度這部分內容的時候，發現很多人都在尋找poi中對於Sheet頁隱蔽性的判斷和設定的方法，
設定的方法很容易百度到，但是判斷隱蔽性的方法基本上沒有人去回答，所以這裡我做個總結：

方法名 作用 呼叫物件 補充說明
isSheetHidden(int) 判斷Sheet是否隱蔽 Wookbook 返回值為Boolean型別
isSheetVeryHidden(int) 判斷Sheet是否非常隱蔽 Wookbook 設定後需要重新生成Excel檔案覆蓋原來的檔案才能生效
setSheetHidden(int,boolean) 設定Sheet隱蔽性 Wookbook 設定後需要重新生成Excel檔案覆蓋原來的檔案才能生效
setSheetHidden(int,int) 設定Sheet隱蔽性 Wookbook 設定後需要重新生成Excel檔案覆蓋原來的檔案才能生效
removeSheetAt(int) 刪除指定Sheet頁 Wookbook 設定後需要重新生成Excel檔案覆蓋原來的檔案才能生效

增加對於隱蔽性處理的程式碼後，再來執行程式碼，看下結果：

最後補充一下關於Excel中的公式的相關內容：
如果對於Excel用的比較粗淺的人（比如我自己...），一開始聽到公式，你是不是以為是這個？

或者是這個？

如果你覺得是這樣的，那我們倆就是同志了.......
其實Excel中單元格的公式，更多是指的用公式計算出來結果的單元格內容。
這麼一聽是不是很繞？那舉個例子，比如大家都會用的選中幾個單元格，然後點下求和按鈕自動計算出來和，這就是公式處理。
那怎麼判斷哪個單元格中的值是用公式計算出來的呢？很簡單(對我這個Excel渣渣來說，可能一輩子也不會注意到這點，簡單個錘子...)，如下：

如果只看Excel中的內容，其實單元格是否使用公式計算值，我們看的並沒什麼差別，
But，放到Java中用poi讀取後，處理與不處理的差別可就大了去了（剛開始的時候被這個公式坑慘了...），看以下執行結果：

剛開始對poi不熟悉，各種百度poi對於公式的處理，找了很多，也走了很多彎路，最終確定這個方式靠譜，
這個方式的好處在於，處理公式的結果很穩~就算是原本用於公式計算的單元格被刪掉，它也可以正常獲取單元格的值！主要程式碼如下：

FormulaEvaluator formulaEvaluator = workbook.getCreationHelper().createFormulaEvaluator();  //用於解析和處理Cell公式的介面物件

case Cell.CELL_TYPE_FORMULA: //公式
    if(cell.getCachedFormulaResultType()==Cell.CELL_TYPE_NUMERIC){
        returnValue = String.valueOf(cell.getNumericCellValue());
    }else if(cell.getCachedFormulaResultType()==Cell.CELL_TYPE_STRING){
        returnValue = String.valueOf(cell.getStringCellValue());
    }
    break;

順帶補充一下，我用的是poi-3.9版本，目前已經更新到4.X版本了，但是很多使用者反應不穩定，還是建議使用比較老的，穩定~
以上就是目前我對poi讀取Excel內容的理解和實踐，如果以後還有更多的理解，再回來補充！
有問題歡迎留言，看到必回！

------------------------------------時間分割線 2020/8/26------------------------------------

相關文章