Apache POI電子表格/Spreadsheet

本章將介紹如何創建一個電子表格，並使用Java操縱它。電子表格是在Excel文件中的頁面;它包含具有特定名稱的行和列。

讀完本章後，將能夠創建一個電子表格，並能在其上執行讀取操作。

創建電子表格

首先，讓我們創建一個使用在前面的章節中討論的引用的類的電子表格。按照前面的章節中，首先創建一個工作簿，然後我們就可以去，並創建一個表。

下面的代碼片段用於創建電子表格。

//Create Blank workbook XSSFWorkbook workbook = new XSSFWorkbook(); //Create a blank spreadsheet XSSFSheet spreadsheet = workbook.createSheet("Sheet Name");

在電子表格的行

電子表格有一個網格佈局。行和列被標識與特定的名稱。該列標識字母和行用數字。

下面的代碼片段用於創建一個行。

XSSFRow row = spreadsheet.createRow((short)1);

寫入到電子表格

讓我們考慮僱員數據的一個例子。這裏的僱員數據給出以表格形式。

Emp Id

Emp Name

稱號

Tp01

Gopal

Technical Manager

TP02

Manisha

Proof Reader

Tp03

Masthan

Technical Writer

Tp04

Satish

Technical Writer

Tp05

Krishna

Technical Writer

以下代碼是用來寫上述數據到電子表格。

import java.io.File; import java.io.FileOutputStream; import java.util.Map; import java.util.Set; import java.util.TreeMap; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.xssf.usermodel.XSSFRow; import org.apache.poi.xssf.usermodel.XSSFSheet; import org.apache.poi.xssf.usermodel.XSSFWorkbook; public class Writesheet { public static void main(String[] args) throws Exception { //Create blank workbook XSSFWorkbook workbook = new XSSFWorkbook(); //Create a blank sheet XSSFSheet spreadsheet = workbook.createSheet( " Employee Info "); //Create row object XSSFRow row; //This data needs to be written (Object[]) Map < String, Object[] > empinfo = new TreeMap < String, Object[] >(); empinfo.put( "1", new Object[] { "EMP ID", "EMP NAME", "DESIGNATION" }); empinfo.put( "2", new Object[] { "tp01", "Gopal", "Technical Manager" }); empinfo.put( "3", new Object[] { "tp02", "Manisha", "Proof Reader" }); empinfo.put( "4", new Object[] { "tp03", "Masthan", "Technical Writer" }); empinfo.put( "5", new Object[] { "tp04", "Satish", "Technical Writer" }); empinfo.put( "6", new Object[] { "tp05", "Krishna", "Technical Writer" }); //Iterate over data and write to sheet Set < String > keyid = empinfo.keySet(); int rowid = 0; for (String key : keyid) { row = spreadsheet.createRow(rowid++); Object [] objectArr = empinfo.get(key); int cellid = 0; for (Object obj : objectArr) { Cell cell = row.createCell(cellid++); cell.setCellValue((String)obj); } } //Write the workbook in file system FileOutputStream out = new FileOutputStream( new File("Writesheet.xlsx")); workbook.write(out); out.close(); System.out.println( "Writesheet.xlsx written successfully" ); } }

上面的Java代碼保存爲Writesheet.java，然後並在命令提示符下編譯運行，如下所示：

$javac Writesheet.java
$java Writesheet

這將編譯和執行來生成一個Excel文件名爲Writesheet.xlsx在當前目錄中，在命令提示符處鍵入以下輸出。

Writesheet.xlsx written successfully

Writesheet.xlsx文件的內容如下所示。

從電子表格讀取數據

讓我們考慮上述excel文件命名Writesheet.xslx作爲輸入文件。注意下面的代碼;它是用於從電子表格中讀取數據。

import java.io.File; import java.io.FileInputStream; import java.util.Iterator; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.xssf.usermodel.XSSFRow; import org.apache.poi.xssf.usermodel.XSSFSheet; import org.apache.poi.xssf.usermodel.XSSFWorkbook; public class Readsheet { static XSSFRow row; public static void main(String[] args) throws Exception { FileInputStream fis = new FileInputStream( new File("WriteSheet.xlsx")); XSSFWorkbook workbook = new XSSFWorkbook(fis); XSSFSheet spreadsheet = workbook.getSheetAt(0); Iterator < Row > rowIterator = spreadsheet.iterator(); while (rowIterator.hasNext()) { row = (XSSFRow) rowIterator.next(); Iterator < Cell > cellIterator = row.cellIterator(); while ( cellIterator.hasNext()) { Cell cell = cellIterator.next(); switch (cell.getCellType()) { case Cell.CELL_TYPE_NUMERIC: System.out.print( cell.getNumericCellValue() + " \t\t " ); break; case Cell.CELL_TYPE_STRING: System.out.print( cell.getStringCellValue() + " \t\t " ); break; } } System.out.println(); } fis.close(); } }

讓我們把上面的代碼保存在Readsheet.java文件，然後編譯並在命令提示符下運行，如下所示：

$javac Readsheet.java
$java Readsheet

如果您的系統環境配置了POI庫，它會編譯和執行產生在命令提示符處鍵入以下輸出。

EMP ID EMP NAME DESIGNATION
tp01 Gopal Technical Manager tp02 Manisha Proof Reader tp03 Masthan Technical Writer tp04 Satish Technical Writer tp05 Krishna Technical Writer