Apache Poi 简明教程

Apache POI - Spreadsheets

本章介绍了如何创建电子表格以及使用 Java 操纵它。电子表格是 Excel 文件中的一页；它包含具有特定名称的行和列。

This chapter explains how to create a spreadsheet and manipulate it using Java. Spreadsheet is a page in an Excel file; it contains rows and columns with specific names.

完成本章学习后，您将能够创建电子表格并对其执行读取操作。

After completing this chapter, you will be able to create a spreadsheet and perform read operations on it.

Create a Spreadsheet

首先，我们使用前面章节中讨论的引用类创建电子表格。按照上一章节，首先创建一个工作簿，然后再创建一张工作表。

First of all, let us create a spreadsheet using the referenced classes discussed in the earlier chapters. By following the previous chapter, create a workbook first and then we can go on and create a sheet.

以下代码片段用于创建电子表格。

The following code snippet is used to create a spreadsheet.

//Create Blank workbook
XSSFWorkbook workbook = new XSSFWorkbook();

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

Rows on Spreadsheet

电子表格具有网格布局。行和列使用特定名称进行标识。列使用字母标识，行使用数字标识。

Spreadsheets have a grid layout. The rows and columns are identified with specific names. The columns are identified with alphabets and rows with numbers.

以下代码片段用于创建行。

The following code snippet is used to create a row.

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

Write into a Spreadsheet

让我们考虑一个员工数据示例。此处，员工数据采用表格形式给出。

Let us consider an example of employee data. Here the employee data is given in a tabular form.

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

以下代码用于将上述数据写入电子表格。

The following code is used to write the above data into a spreadsheet.

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 ，然后在命令提示符中将其编译并运行，如下所示：

Save the above Java code as Writesheet.java, and then compile and run it from the command prompt as follows −

$javac Writesheet.java
$java Writesheet

它将编译和执行以在当前目录中生成一个名为 Writesheet.xlsx 的 Excel 文件，您将在命令提示符中获得以下输出。

It will compile and execute to generate an Excel file named Writesheet.xlsx in your current directory and you will get the following output in the command prompt.

Writesheet.xlsx written successfully

Writesheet.xlsx 文件如下所示：

The Writesheet.xlsx file looks as follows −

Read from a Spreadsheet

让我们将上述名为 Writesheet.xslx 的 Excel 文件视为输入。观察以下代码；它用于读取电子表格中的数据。

Let us consider the above excel file named Writesheet.xslx as input. Observe the following code; it is used for reading the data from a spreadsheet.

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 NUMERIC:
                  System.out.print(cell.getNumericCellValue() + " \t\t ");
                  break;

               case STRING:
                  System.out.print(
                  cell.getStringCellValue() + " \t\t ");
                  break;
            }
         }
         System.out.println();
      }
      fis.close();
   }
}

让我们将上述代码保存在 Readsheet.java 文件中，然后在命令提示符中将其编译并运行，如下所示：

Let us keep the above code in Readsheet.java file, and then compile and run it from the command prompt as follows −

$javac Readsheet.java
$java Readsheet

如果系统环境配置了 POI 库，它将编译和执行以在命令提示符中生成以下输出。

If your system environment is configured with the POI library, it will compile and execute to generate the following output in the command prompt.

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