每日一課 | Apache POI –用Java讀寫Excel文件

在本文中，我們將討論如何使用Apache POI讀寫Excel文件

1. Apache POI庫的基本定義

本節簡要介紹有關Excel讀寫期間使用的基本類。

HSSF在類名之前添加前綴，以指示與Microsoft Excel 2003文件相關的操作。

XSSF在類名之前添加前綴，以指示與Microsoft Excel 2007文件或更高版本相關的操作。

XSSFWorkbook和HSSFWorkbook是充當Excel工作簿的類

HSSFSheet和XSSFSheet是充當Excel工作表的類

Row定義一個Excel行

Cell定義參照行尋址的Excel單元格。 

2.下載Apache POI

使用Maven依賴關係可以輕鬆獲得Apache POI庫。

pom.xml 

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

3. Apache POI庫–編寫簡單的Excel

以下代碼顯示了如何使用Apache POI庫編寫一個簡單的Excel文件。該代碼使用二維數據數組來保存數據。數據被寫入XSSFWorkbook對象。XSSFSheet是正在處理的工作表。代碼如下所示：

ApachePOIExcelWrite.java 

package com.mkyong;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
public class ApachePOIExcelWrite {
    private static final String FILE_NAME = "/tmp/MyFirstExcel.xlsx";
    public static void main(String[] args) {
        XSSFWorkbook workbook = new XSSFWorkbook();
        XSSFSheet sheet = workbook.createSheet("Datatypes in Java");
        Object[][] datatypes = {
                {"Datatype", "Type", "Size(in bytes)"},
                {"int", "Primitive", 2},
                {"float", "Primitive", 4},
                {"double", "Primitive", 8},
                {"char", "Primitive", 1},
                {"String", "Non-Primitive", "No fixed size"}
        };
        int rowNum = 0;
        System.out.println("Creating excel");
        for (Object[] datatype : datatypes) {
            Row row = sheet.createRow(rowNum++);
            int colNum = 0;
            for (Object field : datatype) {
                Cell cell = row.createCell(colNum++);
                if (field instanceof String) {
                    cell.setCellValue((String) field);
                } else if (field instanceof Integer) {
                    cell.setCellValue((Integer) field);
                }
            }
        }
        try {
            FileOutputStream outputStream = new FileOutputStream(FILE_NAME);
            workbook.write(outputStream);
            workbook.close();
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
        System.out.println("Done");
    }
}

執行上述代碼後，您將獲得以下excel作為輸出。

4. Apache POI庫–讀取Excel文件

以下代碼說明了如何使用Apache POI庫讀取Excel文件。函數getCellTypeEnum在版本3.15中已棄用，並且將從版本4.0 getCellType命名為getCellType 。

ApachePOIExcelRead.java 

package com.mkyong;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.util.Iterator;
public class ApachePOIExcelRead {
    private static final String FILE_NAME = "/tmp/MyFirstExcel.xlsx";
    public static void main(String[] args) {
        try {
            FileInputStream excelFile = new FileInputStream(new File(FILE_NAME));
            Workbook workbook = new XSSFWorkbook(excelFile);
            Sheet datatypeSheet = workbook.getSheetAt(0);
            Iterator<Row> iterator = datatypeSheet.iterator();
            while (iterator.hasNext()) {
                Row currentRow = iterator.next();
                Iterator<Cell> cellIterator = currentRow.iterator();
                while (cellIterator.hasNext()) {
                    Cell currentCell = cellIterator.next();
                    //getCellTypeEnum shown as deprecated for version 3.15
                    //getCellTypeEnum ill be renamed to getCellType starting from version 4.0
                    if (currentCell.getCellTypeEnum() == CellType.STRING) {
                        System.out.print(currentCell.getStringCellValue() + "--");
                    } else if (currentCell.getCellTypeEnum() == CellType.NUMERIC) {
                        System.out.print(currentCell.getNumericCellValue() + "--");
                    }
                }
                System.out.println();
            }
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}

執行上面的代碼後，您將獲得以下輸出。 

Datatype--Type--Size(in bytes)--
int--Primitive--2.0--
float--Primitive--4.0--
double--Primitive--8.0--
char--Primitive--1.0--
String--Non-Primitive--No fixed size--

參考文獻：

有關棄用getCellTypeEnum的詳細信息

關於棄用的Apache POI參考

Apache POI Maven回購

Apache POI API文檔

翻譯自: https://mkyong.com/java/apache-poi-reading-and-writing-excel-file-in-java/

推薦閱讀--