POI 4.1.2 操作 Excel

1. POI 簡介

【資料圖】

POI（Poor Obfuscation Implementation），直譯為“可憐的模糊實現”，利用POI接口可以通過 Java操作 Microsoft office 套件工具的讀寫功能。POI支持office的所有版本。

org.apache.poipoi4.1.2org.apache.poipoi-ooxml4.1.2

2. POI 的快速應用

（1）操作后綴為 .xls的 Excel文件

在 POI 中的幾個主要對象：

HSSFWorkbookExcel 工作簿workbook

HSSFSheetExcel 工作表 sheet

HSSFRowExcel 行

HSSFCellExcel 單元格

使用 POI 創建 Excel

/**  * 創建 Excel 測試  * @throws IOException 文件操作異常  */@Testpublic void createTest() throws IOException {    // 新建工作薄    HSSFWorkbook workbook = new HSSFWorkbook();    // 新建工作表    HSSFSheet sheet = workbook.createSheet();    // 創建行，行號為 0    HSSFRow row = sheet.createRow(0);    // 創建單元格，表示為第 3 個單元格    HSSFCell cell = row.createCell(2);    // 輸入值    cell.setCellValue("Hello World!");    // 將 Excel 進行文件導出    // 獲取文件路徑和文件    Path path = Paths.get(".").resolve("hello.xls");    // 文件已存在則刪除    if (Files.exists(path)){        Files.delete(path);    }    // 創建文件    File file = Files.createFile(path).toFile();    // 寫入數據    workbook.write(file);}

運行結果：

使用 POI 向 Excel 中追加

/**  * 向已存在的 Excel 中追加內容  * @throws IOException  */@Testpublic void appendTest() throws IOException {    // 獲取文件輸入流    String path = "./hello.xls";    File file = new File(path);    int rowIndex = 4;    HSSFWorkbook workbook = null;    HSSFSheet sheet = null;    if(file.exists()){        FileInputStream fis = new FileInputStream(file);        // 新建工作簿        workbook = new HSSFWorkbook(fis);        // 新建工作表        sheet = workbook.getSheet("sheet0");    } else{        // 新建工作簿        workbook = new HSSFWorkbook();        // 新建工作表        sheet = workbook.createSheet();    }    // 獲取行號    HSSFRow row = sheet.getRow(rowIndex);    // 判斷是否存在,不存在則創建    if (row == null){        row = sheet.createRow(rowIndex);    }    // 創建單元格，表示為第 3 個單元格    HSSFCell cell = row.createCell(5);    // 輸入值    cell.setCellValue("追加3!");    // 將 Excel 進行文件導出    // 寫入數據    workbook.write(file);}

運行結果：

使用 POI 讀取 Excel

/**  * 讀取 Excel 測試  * @throws IOException 文件操作異常  */@Testpublic void readTest() throws IOException {    // 讀取 Excel 文件    // 獲取文件路徑和文件    FileInputStream fis = new FileInputStream("./hello.xls");    // 將輸入流轉換為工作簿對象    HSSFWorkbook workbook = new HSSFWorkbook(fis);    // 獲取第一個工作表    HSSFSheet sheet = workbook.getSheet("sheet0");    // 使用索引獲取工作表    // HSSFSheet sheet = workbook.getSheetAt(0);    // 獲取指定行    HSSFRow row = sheet.getRow(0);    // 獲取指定列    HSSFCell cell = row.getCell(2);    // 打印    System.out.println(cell.getStringCellValue());}

運行結果：

（2）操作后綴為 .xlsx的 Excel文件

在 POI 中的幾個主要對象：

XSSFWorkbookExcel 工作簿workbook

XSSFSheetExcel 工作表 sheet

XSSFRowExcel 行

XSSFCellExcel 單元格

使用 POI 創建 Excel 表

/**  * 創建以 .xlsx 為后綴的 Excel 測試  * @throws IOException 文件操作異常  */@Testpublic void createXlsxTest() throws IOException {    // 新建工作簿    XSSFWorkbook workbook = new XSSFWorkbook();    // 新建工作表    XSSFSheet sheet = workbook.createSheet();    // 創建行，行號為 0    XSSFRow row = sheet.createRow(0);    // 創建單元格，表示為第 3 個單元格    XSSFCell cell = row.createCell(2);    // 輸入值    cell.setCellValue("Hello World!");    // 將 Excel 進行文件導出    // 獲取文件    File file = new File("./hello.xlsx");    // 如果文件存在則刪除    if (file.exists()){        file.delete();    }    // 創建輸出流    FileOutputStream fos = new FileOutputStream(file);    // 寫入    workbook.write(fos);    fos.close();}

運行結果：

使用 POI 追加 Excel 文件

/**  * 向已存在以 .xlsx 為后綴的 Excel 中追加內容  * @throws IOException  */@Testpublic void appendXlsxTest() throws IOException {    // 獲取文件輸入流    String path = "./hello.xlsx";    File file = new File(path);    int rowIndex = 4;    XSSFWorkbook workbook = null;    XSSFSheet sheet = null;    if(file.exists()){        FileInputStream fis = new FileInputStream(file);        // 新建工作簿        workbook = new XSSFWorkbook(fis);        // 新建工作表        sheet = workbook.getSheet("sheet0");    } else{        // 新建工作簿        workbook = new XSSFWorkbook();        // 新建工作表        sheet = workbook.createSheet();    }    // 創建行，行號為 0    XSSFRow row = sheet.getRow(rowIndex);    // 判斷是否存在,不存在則創建    if (row == null){        row = sheet.createRow(rowIndex);    }    // 創建單元格，表示為第 3 個單元格    XSSFCell cell = row.createCell(5);    // 輸入值    cell.setCellValue("追加內容1");    // 將 Excel 進行文件導出    FileOutputStream fos = new FileOutputStream(file);    // 寫入數據    workbook.write(fos);}

運行結果：

使用 POI 讀取 Excel 文件

/**  * 讀取以 .xlsx 為后綴的 Excel 測試  * @throws IOException 文件操作異常  */@Testpublic void readXlsxTest() throws IOException {    // 讀取 Excel 文件    // 獲取文件路徑和文件    FileInputStream fis = new FileInputStream("./hello.xlsx");    // 將輸入流轉換為工作簿對象    XSSFWorkbook workbook = new XSSFWorkbook(fis);    // 獲取第一個工作表    XSSFSheet sheet = workbook.getSheet("sheet0");    // 使用索引獲取工作表    // XSSFSheet sheet = workbook.getSheetAt(0);    // 獲取指定行    XSSFRow row = sheet.getRow(0);    // 獲取指定列    XSSFCell cell = row.getCell(2);    // 打印    System.out.println(cell.getStringCellValue());}

運行結果：

（3）根據后綴名讀取 Excel 文件

通過正則表達式讀取

/**  * 根據后綴名讀取 Excel 文件  */@Testpublic void readAllExcel() throws IOException {    // 兩個不同后綴名文件的路徑    String path1 = "./hello.xlsx";    String path2 = "./hello.xls";    readExcel(path1);    readExcel(path2);}/**  * 根據文件路徑讀取 Excel 文件  * @param path 文件路徑  * @throws IOException 文件操作異常  */public void readExcel(String path) throws IOException {    // 獲取后綴    String suffix = path.substring(path.lastIndexOf("."));    // 判斷后綴為 xls 還是 xlsx    if (path.matches("^.+\\.(?i)((xls)|(xlsx))$")){        // 創建輸入流        FileInputStream fis = new FileInputStream(path);        // 判斷是否為以 .xls 結尾的 Excel 文件        boolean isXlsExcel = path.matches("^.+\\.(?i)(xls)$");        // 判斷后綴生成 工作簿        Workbook workbook = isXlsExcel ? new HSSFWorkbook(fis) : new XSSFWorkbook(fis);        // 獲取工作表        Sheet sheet = workbook.getSheet("sheet0");        // 獲取指定行        Row row = sheet.getRow(0);        // 獲取指定列        Cell cell = row.getCell(2);        // 輸出值        System.out.println(suffix + " :\t" + cell.getStringCellValue());    }}

運行結果：

通過 WorkbookFactory 讀取

/** * 通用讀取的方法 */@Testpublic void generalRead() throws IOException {    String path1 = "./hello.xls";    String path2 = "./hello.xlsx";    generalRead(path1);    generalRead(path2);}public void generalRead(String path) throws IOException {    File file = new File(path);    Workbook workbook = WorkbookFactory.create(file);    Sheet sheet = workbook.getSheet("sheet0");    String value = sheet.getRow(0).getCell(2).getStringCellValue();    String suffix = path.substring(path.lastIndexOf(".")+1);    System.out.println(suffix + " :\t" + value);}

運行結果：

3. POI 的高級操作

（1）合并單元格

/**  * 合并單元格  * CellRangeAddress(firstRow,lastRow,firstCol,lastCol)  * 四個參數分別為 起使行號,終止行號,起使列,終止列  *  * @throws IOException 文件操作異常  */@Testpublic void mergeCells() throws IOException {    // 新建工作簿    HSSFWorkbook workbook = new HSSFWorkbook();    // 新建工作表    HSSFSheet sheet1 = workbook.createSheet("sheet1");    // 設置要合并的單元格范圍，合并第二行的第二列到第五列為一個單元格    CellRangeAddress rangeAddress = new CellRangeAddress(1, 1, 1, 4);    sheet1.addMergedRegion(rangeAddress);    // 設置合并后的單元格內容    HSSFRow row = sheet1.createRow(1);    HSSFCell cell = row.createCell(1);    cell.setCellValue("我是合并后的單元格");    FileOutputStream outputStream = new FileOutputStream("./工作簿.xls");    workbook.write(outputStream);    outputStream.close();}

運行結果：

注：上圖中合并單元格后，單元格的名稱是第一個單元格；即上面中合并了第二行的第二列到第五列，合并后的單元格叫 B2，而其它被合并的單元格已經無效了，不能對無效單元格設置值。如果進行了設置將不顯示。

（2）設置單元格樣式

/**  * 設置單元格樣式  * @throws IOException 文件操作異常  */@Testpublic void cellStyle() throws IOException {    // 新建工作簿    HSSFWorkbook workbook = new HSSFWorkbook();    // 新建工作表    HSSFSheet sheet = workbook.createSheet("工作表1");    // 創建行    HSSFRow row = sheet.createRow(3);    // 創建單元格    HSSFCell cell = row.createCell(3);    cell.setCellValue("單元格內容");    // 設置sheet的第四列的寬度, 列寬單位是字符的1 / 256.    sheet.setColumnWidth(3, 20 * 256);    // 創建單元格樣式    HSSFCellStyle style = workbook.createCellStyle();    // 設置單元格內容水平居中    style.setAlignment(HorizontalAlignment.CENTER);    // 設置單元格內容垂直居中    style.setVerticalAlignment(VerticalAlignment.CENTER);    // 創建字體    HSSFFont font = workbook.createFont();    font.setFontName(HSSFFont.FONT_ARIAL);//字體為Arial    font.setColor(HSSFFont.COLOR_RED);//字體顏色為紅色    font.setBold(true);//設置為粗體    font.setFontHeightInPoints((short) 16);//設置字體大小    style.setFont(font);    // 設置填充模式，模式為全部前景色    style.setFillPattern(FillPatternType.SOLID_FOREGROUND);    // 設置前景色為綠色    style.setFillForegroundColor(HSSFColorPredefined.GREEN.getColor().getIndex());    // 設置背景色，如果填充模式為其它填充模式，這個前景和背景色將相互交映顯示    style.setFillBackgroundColor(HSSFColorPredefined.RED.getColor().getIndex());    // 為特定單元格設置樣式    cell.setCellStyle(style);    FileOutputStream fos = new FileOutputStream("./工.xls");    workbook.write(fos);    fos.close();}

運行結果：

使用 POI 導入 Excel 文件到 MySQL 中

// 使用 Spring Test 測試，數據庫操作可更改為普通的 JDBC 操作@RunWith(SpringJUnit4ClassRunner.class)@ContextConfiguration(locations = "classpath:applicationContext.xml")public class TestPoi {    @Autowired    private DataSource dataSource;    @Test    public void test() throws IOException, SQLException {        // Excel 路徑        String path = "./xxx.xlsx";        File file = new File(path);        // 獲取數據庫連接        Connection conn = dataSource.getConnection();        // 插入的 SQL 語句        String sql = "insert into sheet1 values(null,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";        PreparedStatement ps = conn.prepareStatement(sql);        // 獲取工作簿        Workbook workbook = WorkbookFactory.create(file);        // 獲取當前表        Sheet sheet = workbook.getSheetAt(0);        // 獲取總行數        int totalRows = sheet.getPhysicalNumberOfRows();        // 獲取表頭的總列數        int totalCols = sheet.getRow(0).getPhysicalNumberOfCells();        // 遍歷行        for (int i = 1; i < totalRows; i++) {            // 遍歷列            for (int j = 0; j < totalCols; j++) {                // 獲取 i 行 j 列                Cell cell = sheet.getRow(i).getCell(j);                // 判斷該列是否為 null                if (cell == null || cell.getCellType() == CellType.BLANK){                    ps.setString(j+1,null);                    continue;                }                // 由于測試的 Excel 此處的最后一位為日期類型,這里使用日期輸出                if (j==totalCols-1){                    cell.setCellType(CellType.NUMERIC);                    System.out.println(new Date(cell.getDateCellValue().getTime()));                    ps.setDate(j+1,new Date(cell.getDateCellValue().getTime()));                }else {                    // 判斷是否是字符類型                    if(cell.getCellType() == CellType.STRING){                        ps.setString(j+1,cell.getStringCellValue());                    }else{                        // 單元格為數值類型                        cell.setCellType(CellType.NUMERIC);                        ps.setInt(j+1, (int) cell.getNumericCellValue());                    }                }            }            // 執行 SQL            ps.executeUpdate();        }        ps.close();        conn.close();    }}