poi解析Excel內容

2020 年 8 月 26 日
筆記
Java操作Excel, POI

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————————————

Tags: Java操作Excel POI

poi解析Excel內容

VirMach 便宜 VPS

QNews

poi解析Excel內容

分享此文：

Related Posts

前端測試題:module模塊中，對下列語句的描述，錯誤的是?

Flink從入門到入土（詳細教程）

Keras 教程：BERT 文本摘要

JavaSE基礎–part1

VirMach 便宜 VPS

QNews

熱門搜尋