读取、回收和重用：使用 Excel、XML 和 Java 技术轻松搞定报告使用 Java 和 XML 技术读取 Excel 文件并写入新文件(1)

读取、回收和重用：使用 Excel、XML 和 Java 技术轻松搞定报告使用 Java 和 XML 技术读取 Excel 文件并写入新文件

Shaene M Siders, 作家和顾问, Dragon Under Glass

Shaene Siders 是一位技术、培训和娱乐方面的顾问兼作家。她在 1996 年创立了一个写作和 Web 开发公司，并从 2003 年起成为一名 IBM Rational Certified Instructor for ClearCase and ClearQuest。她是培训课件系列Java for Mad Scientists 的作者和制作人，还是一名 IBM Rational Certified Instructor for BuildForge, Rational Functional Tester, Rational Performance Tester, and Rational Manual Tester。

简介： 提取业务数据是每个公司都面临的一个挑战。探索使用 Java™ 技术从 Excel 提取数据以及在 Excel 和 XML 之间转换数据的秘密。

简介

创新理由编号 432：报告不利于环境。

图表和报告消耗树木。电子版本使用电能和化石燃料。也许，公司能够 “变得绿色” 并停止撰写报告。

为高级职员开发报告可能会让 Java 程序员感到焦虑，特别是当程序员喜欢开发人员友好的输出（比如 XML ）、但高层管理人员只讲支持 GUI 的电子表格语言时。

常用缩写词

• API：应用程序编程接口
• GUI：图形用户界面
• IDE：集成开发环境
• PNG：可移植网络图形
• SQL：结构化查询语言
• XML：可扩展标记语言

焦虑可能会增加牵涉的程序员的温室气体 “输出”，特别是当他们开始喘息时。尽管报告消耗时间和资源，但是向高层管理人员提供项目可见性是很关键的。管理层可能会怀疑努力工作的团队，仅仅因为高层领导看不见他们的团队正在进行的工作。

因此，报告对工作环境很重要。

Java 程序员处理来自电子表格爱好者的报告时，利用几个 Java 技巧可以提高他们的工作效率。完成本文介绍的几个步骤之后，中级 Java 程序员应该能够理解以编程方式在 Microsoft® Excel® 和 XML 之间转换数据背后的基本原理。

---

回页首

Excel 到 Java API

本文使用的工具

完成本文中的示例需要以下工具：

• XML Object Model (XOM) API 1.2.1 版
• Apache POI 3.6 版（针对 Microsoft Office 的 Java API）
• Eclipse Classic 3.5.1 版(内置 Java Platform, Standard Edition [Java SE] 版本 6 的 IDE）
• Microsoft Excel

要获取关于这些产品的链接和更多信息，请参见 参考资料。

下面有几个 Java API 用于操作 Excel 文件。哪个最好？这取决于您的个人需求和经验水平。

Andy Khan 的 Java Excel API

Andy Khan 创建了一个称为 Java Excel API（或 JExcel API）的 API（参见 参考资料）。这个开源 API 能够读写 Excel 电子表格。另外，由于它是轻量级的，因此它是初级 Java 开发人员的一个不错选择。这个 API 有一个方便的 Unified Modeling Language (UML) 图表和支持社区，使其简单易用。

这个 API 也有一些缺点。尽管能够读取 Microsoft Excel 95、Excel 97、Excel 2000、Excel 2002 和 Excel 2003 文件格式，但它目前不能处理较新的 Excel 格式。不能创建图表、曲线图和宏，而且只支持 PNG 图像文件。

xlSQL Excel JDBC Driver

另一个 Excel-to-Java API 是 xlSQL Excel Java Database Connectivity (JDBC) 驱动程序（参见 参考资料），这是一个开源 API，用于将 Excel 文件当作数据库一样进行查询。如果您熟悉 SQL 和 JDBC，这可能是最简单的数据提取方法。您还可以通过使用 SQL 命令insert 来添加数据。遗憾的是，xlSQL Excel JDBC 驱动程序目前似乎没有得到积极支持。

OpenXLS

OpenXLS（参见 参考资料）是名为 ExtenXLS 的商业产品 Extentech 的开源版本。OpenXLS 拥有广泛的功能，它能以编程方式修改公式，并且使用大量格式化选项。与一些开源产品不同，它可以处理复杂对象，包括命名范围、数据透视表、拆分框架和图表。另外，与类似的产品相比，OpenXLS 拥有更详尽的、关于哪些特性可用的前期文档。遗憾的是，尽管这个开源版本支持从 97 版到 2003 版的 Excel 格式，但只有商业版本支持 Excel 2007。

Apache POI

Apache POI 是一组用于处理旧版和新版 Microsoft 标准文档的 Java API。除了支持 Excel 97 以后的版本之外，Apache POI 还可以处理 Microsoft Word 和 PowerPoint® 文件。您可以利用处理 Excel 文件的知识来更快地学习如何处理这些额外的文件类型。还有一个活跃的社区支持这个 API。但是，由于 Apache POI 拥有如此众多的功能且可以处理其他文件，它可能超出了只想处理 Excel 文件的开发人员需要学习的内容。

由于 Apache POI 的支持社区和丰富功能，本文使用 Apache POI。

---

回页首

Java XML API

XML 是一种流行的数据格式，有几种方法可以在 Java 技术中处理 XML。您可以选择最适合自己项目的任何 XML API。但是，本文使用 Elliotte Rusty Harold 的优雅 XML API：XOM。要了解关于 XML API 的更多信息，请参见参考资料。

---

回页首

样例应用程序

本文的样例应用程序从一个虚构的公司 Planet Power 的人力资源部门提供的一个 Excel 电子表格文件开始。这个电子表格名为 Employee_List.xls。

本文演示如何使用 Java 技术和 Apache POI 来读取 Employee_List.xls。这个演示使用的类文件为 ExcelReader.java，包含在一个 Eclipse 项目中。在下载 部分下载包含样例电子表格和 Eclipse 项目的 .zip 文件。Eclipse 项目中的一个 Readme.txt 文件解释了 “下载” 部分中包含的其他样例代码。

这个文章系列的第 2 部分将演示如何将信息转换为 XML 并创建一个对原始数据进行了一些修改的新电子表格。

设置

要准备计算机以运行本文中的样例，需完成以下步骤：

1. 下载 Excel 电子表格和样例代码。
2. 创建目录 C:\Planet Power 并将文件解压到该目录。
3. 使用 参考资料 部分中的链接下载 Eclipse。下载完成后，将它解压到 C:\Program Files\Eclipse 目录。
4. 从 参考资料 部分中的链接，使用 XOM 站点上的Complete zip 链接下载 XOM。然后，将文件解压到 C:\Program Files\Eclipse\lib 目录。
5. 使用 参考资料 部分中的链接下载 Apache POI。将文件解压到 C:\Program Files\Eclipse\lib（您需要创建 lib 目录）。

现在已经准备好在 Eclipse 中开始工作了。

启动 Eclipse

要在 Eclipse IDE 中开始工作，需完成以下步骤：

1. 导航到 C:\Program Files\Eclipse\eclipse 并双击 eclipse.exe 启动 Eclipse。如果 Windows® 显示一个安全警告，请单击Run。
2. 在 Workspace Launcher 窗口中，使用 C:\Eclipse_Projects 替换标为Workspace 的路径，然后单击 OK。
3. Eclipse 装载完成后，单击窗口右边的 Workbench 图标（参见 图 1）。
   
   图 1. Workbench 图标
   
   
   
4. 在 Package Explorer 窗格中右键单击，然后单击 Import。
5. 展开 General，选择 Existing Projects into Workspace。单击Next（参见 图 2）。
   
   图 2. 在工作空间中导入一个现有项目
   
   
   
6. 单击 Browse（位于 Select root directory 旁边）并导航到 C:\Planet Power\Employees。选择 Employees 文件夹，然后单击OK。
7. 单击 Finish，如 图 3 所示。
   
   图 3. 将一个项目导入 Eclipse 中
   
   
   

Employees 文件夹现在应该出现在 Package Explorer 窗格中。

使 XOM 和 Apache POI 对 Eclipse 可用

从技术上讲，本小节中的步骤已经在导入的 Employees Eclipse 项目中执行完了。但是，如果您从头开始自己的项目，则需要告知 Eclipse 项目使用新的 XOM 和 Apache POI 下载。完成以下步骤：

1. 在 Package Explorer 中右键单击 Employees 文件夹，然后单击 Properties。
2. 单击左边窗格中的 Java Build Path。
3. 单击 Libraries 选项卡。
4. 单击 Add External JARs，如 图 4 所示。
   
   图 4. 添加外部 JAR 文件到构建路径
   
   
   
5. 选择包含您将在本样例中使用的 Apache POI 部分的 Java 归档（JAR）文件。（如果您使用的 POI 的版本与本文相同，这个路径将是 C:\Program Files\Eclipse\lib\poi-3.6\poi-3.6-20091214.jar）。单击Open。
6. 再次单击 Add External JARs。
7. 选择包含 XOM 的 JAR 文件（如果您使用的 XOM 的版本与本文相同，这个路径将是 C:\Program Files\Eclipse\lib\XOM\xom-1.2.1.jar）。单击Open。
8. 单击 OK。

使用 ExcelReader.java 文件

对于本文而言，使用文件 ExcelReader.java，该文件驻留在 src\（默认包）下的 Employees 项目文件夹中。图 5 展示了这个文件。

图 5. 打开 Employees 项目

要运行这个文件，单击屏幕顶部的 Run 箭头按钮，如 图 6 所示。

图 6. 运行 Java 文件

运行 ExcelReader.java 将从 Employee_List.xls 电子表格中的单元格读取信息，并使用 Eclipse 的 Console 选项卡显示该信息，如图 7 所示。

图 7. Eclipse 的 Console 中的 Java 输出

---

回页首

开始

理解 Java 技术的关键是熟悉处理对象并实例化（即创建）那些对象的概念。创建要处理的对象的标准格式是：

class objectName = new class();

objectName 是新创建的对象的名称，它类似于一个变量，该变量标识并提供一种方法来处理那个特定对象。另外，信息（通常采用其他现有对象的形式）可能位于圆括号（class 后面的()）中。圆括号中的信息用于创建这个新对象。

处理文件

每当在 Java 环境中使用文件时，都可能会遇到文件问题。例如，文件可能缺失。因此，试图读取一个文件可能会出错。捕获操作文件可能导致的任何异常。

为了处理 Excel 文件，本文使用 FileInputStream 类（java.io.FileInputStream）。FileInputStream 表示一个不由常规文本构成的文件。由于 Excel 文件包含二进制数据，所以这里使用FileInputStream 而不是 FileReader 类，后者读取只包含文本字符的文件。

开始编程

读取 Excel 工作簿的第一步是准备使用 Apache POI 和其他必要的类。ExcelReader.java 中必要的类包括一些 Apache POI 类、一些异常（错误）类以及一些文件处理类。清单 1 展示了 ExcelReader.java 顶端的代码，作用是导入这些类以使它们可用。

清单 1. 导入类（ExcelReader.java）

import java.io.FileInputStream;import java.io.IOException;import org.apache.poi.hssf.usermodel.HSSFCell;import org.apache.poi.hssf.usermodel.HSSFRow;import org.apache.poi.hssf.usermodel.HSSFSheet;import org.apache.poi.hssf.usermodel.HSSFWorkbook;

导入相关类之后，就可以开始使用 Apache POI 在主方法的主体内编程了。

---

回页首

Apache POI 中的 HSSF 是什么意思？

一些用于读取 Excel 电子表格的类

以下是一些用于读取 Excel 电子表格的类：

• java.io.FileInputStream
• java.io.IOException
• org.apache.poi.hssf.usermodel.HSSFWorkbook
• org.apache.poi.hssf.usermodel.HSSFSheet
• org.apache.poi.hssf.usermodel.HSSFRow
• org.apache.poi.hssf.usermodel.HSSFCell

Apache POI API 程序员为他们的那些涉及 Excel 工作簿的类选择了一个不寻常的命名惯例：他们使用前缀 HSSF。根据它的 JavaDocs，这个前缀实际代表Horrible Spread Sheet Format。实际上，根据 Wikipedia，POI 最初代表 Poor Obfuscation Implementation。谁说程序员没有幽默感？

HSSF 类用于处理 2007 版之前的 Excel（即 .xls 文件）。另一组类 — XSSF — 用于处理 Excel 2007 或更高版本（即 .xlsx 文件）。但是，还有一组类 — SS 类 — 用于处理上述两种版本。为简单起见，本文使用 HSSF 类。您下载的 Eclipse 项目中的 Readme.txt 文件中指出了使用其他类的代码样例。

---

回页首

工作簿

在 Apache POI 中表示 Excel 工作簿的 HSSF 类是 org.apache.poi.hssf.usermodel.HSSFWorkbook。将一个FileInputStream 传递到 HSSFWorkbook 的构造函数中，构造函数表示 FileInputStream 所基于的文件。

但是请等一等！在 Apache POI 的 JavaDocs 中，没有哪个 HSSFWorkbook 的构造函数表明它可以接受 FileInputStream。难道使用 FileInputStream 是一个没有记录在文档中的特性？答案是否定的。的确有一个构造函数接受InputStream。

由于 FileInputStream 是 InputStream 的一个子类，从技术上讲，它也是一个 InputStream，因此它可以被传递到构造函数中。实际上，InputStream 是一个抽象类，因此某种类型的子类是必要的。因此，FileInputStream 完全可被接受。

在实例化 FileInputStream 时，向它传递一个 String，用于描述要读取的 Excel 文件的路径。对于 Windows® 文件，转义文件路径中的任何特殊字符，特别是作为目录分隔符的反斜杠（\），在文件路径字符串中使用双反斜杠（\\）来创建一个转义反斜杠。

清单 2 中的代码实例化一个新的FileInputStream，然后基于新 FileInputStream 实例化一个新的 HSSFworkbook。

清单 2. 读取 Excel 文件（ExcelReader.java）

public static void main(String[] args) { // Create a FileInputStream to hold the file. // Use double back-slashes to create one "escaped" slash. // Use error handling (try/catch) around its creation in case // the file to read does not exist. // Be sure to import java.io.FileNotFoundException and java.io.IOException, or use  // the superclass IOException to handle both.  try {    FileInputStream excelFIS = new FileInputStream("C:\\Planet Power\\Employee_List.xls");    // Create an Excel Workbook Object using the FileInputStream created above    // (which contains the file).    // Use error handling around its creation in case of Input/Output Exception    HSSFWorkbook excelWB = new HSSFWorkbook(excelFIS); } catch (IOException e) {    System.out.println("Input/Output Exception!"); }//End Main Method}

现在，处于 try 错误处理语句中，继续从 Excel 工作簿收集信息，我们先从它的工作表开始。

---

回页首

工作表和行

一个工作簿可以拥有几层页面，这些页面称为工作表。工作表对象由 HSSFSheet 类 org.apache.poi.hssf.usermodel.HSSFSheet 表示。

一个工作簿有多少工作表？要找出这个信息，可以在工作簿上使用 getNumberOfSheets() 方法。但是对于本练习来说，工作簿中只有一个工作表，以便使用它的编号更简单。第一个工作表的编号是 0（计算机喜欢从 0 而不是 1 开始计数）。代码如清单 3 所示。

清单 3. 获取工作表（ExcelReader.java）

      // Start by getting the Spreadsheet (Excel books can have several       // sheets). Assuming there is just one sheet, it's the zero sheet.      HSSFSheet topSheet = excelWB.getSheetAt(0); 

获取工作表对象后，遍历工作表并处理它的数据。以下是一些有用的方法和属性，它们的名称表明了各自的用途。

• HSSFSheet.getFirstRowNum() 和 getLastRowNum()
• HSSFSheet.getHeader() 和 getFooter()
• HSSFSheet.getRow()
• HSSFSheet.getPhysicalNumberOfRows()

要处理工作表上的数据，首先要获取一个 HSSFRow（org.apache.poi.hssf.usermodel.HSSFRow）对象，它表示工作表中的一行。获取一行的一种方法是在工作表上使用getRow()，并通过行号请求一行，如 清单 4 所示。

清单 4. 获取行（ExcelReader.java）

      // getRow() returns an HSSFRow object, but the numbering      // system is logical, not physical, and zero based.      // for example, use getRow(2) to get the third row.      HSSFRow thirdRow = topSheet.getRow(2);

还记得吗，topSheet 是此前在 清单 3 中获取的工作表。

从工作表获取一行后，使用该行来向下钻取到单元格级别。

---

回页首

单元格

要挖取一个单元格的数据，使用该行来获取一个表示该单元格的 HSSFCell 对象（org.apache.poi.hssf.usermodel.HSSFCell）。要获取String 格式的单元格信息，在 HSSFCell 上使用 getStringCellValue() 方法，如清单 5 所示。

清单 5. 获取单元格和其中的字符串（ExcelReader.java）

      // Get the first two cells in the row      HSSFCell lastnameCell = thirdRow.getCell(0);      HSSFCell firstnameCell = thirdRow.getCell(1);      // Get the string information in the cells      String firstName = firstnameCell.getStringCellValue();      String lastName = lastnameCell.getStringCellValue();      // Print out the value of the cells      System.out.println(firstName + " " + lastName);

要获取工作簿中的所有信息，只需遍历所有工作表，每个工作表的每一行，每一行中的每个单元格。但有一个问题：尝试运行下面的代码，它对某些单元格有效。但是，当它试图提取单元值并打印该值时，就会产生错误并退出（参见清单 6 中的注释）。原因何在？

清单 6. 循环所有单元格并打印值。断开了！

// Traverse the sheets by looping through sheets, rows, and cells.// Remember, excelWB is the workbook object obtained earlier.// Outer Loop: Loop through each sheetfor (int sheetNumber = 0; sheetNumber < excelWB.getNumberOfSheets(); sheetNumber++) {   HSSFSheet oneSheet = excelWB.getSheetAt(sheetNumber);// Now get the number of rows in the sheet   int rows = oneSheet.getPhysicalNumberOfRows();   // Middle Loop: Loop through rows in the sheet   for (int rowNumber = 0; rowNumber < rows; rowNumber++) {      HSSFRow oneRow = oneSheet.getRow(rowNumber);      // Skip empty (null) rows.      if (oneRow == null) {         continue;      }      // Get the number of cells in the row      int cells = oneRow.getPhysicalNumberOfCells();      // Inner Loop: Loop through each cell in the row      for (int cellNumber = 0; cellNumber < cells; cellNumber++) {         HSSFCell oneCell = oneRow.getCell(cellNumber);         // Get the value of the string in the cell.         // Print out the String value of the Cell          // This section will result in an error. Why?         String cellValue = oneCell.getStringCellValue();         System.out.println(cellValue + ", ");      // End Inner Loop      }   // End Middle Loop   }// End Outer Loop}

出了什么问题？getStringCellValue() 方法只适用于 Strings，这也是它的名称的由来。

有些单元格包含数值。要避免错误，应测试单元格的数据类型并使用适当的方法来从该单元格提取数据类型。使用 getCellType() 来确定单元格包含的数据类型。数据的类型作为一个整数返回，该整数表示数据类型。以下静态字段（常量）表示数据类型：

• HSSFCELL.CELL_TYPE_STRING。使用 getStringCellValue()。
• HSSFCELL.CELL_TYPE_FORMULA。使用 getCellFormula()。
• HSSFCELL.CELL_TYPE_NUMERIC。使用 getNumericCellValue()。
• HSSFCELL.CELL_TYPE_BOOLEAN。使用 getBooleanCellValue()。

单元格也可能包含 Excel 错误。如果这样，getCellType() 将返回整数 HSSFCELL.CELL_TYPE_ERROR。

在遍历单元格时，测试它们的数据类型，如 清单 7所示。

清单 7. 测试单元格值类型（ExcelReader.java）

      // Inner Loop: Loop through each cell in the row      for (int cellNumber = 0; cellNumber < cells; cellNumber++) {         HSSFCell oneCell = oneRow.getCell(cellNumber);         // Test the value of the cell.         // Based on the value type, use the proper          // method for working with the value.         // If the cell is blank, the cell object is null, so don't          // try to use it. It will cause errors.         // Use continue to skip it and just keep going.        if (oneCell == null) {            continue;         }         switch (oneCell.getCellType()) {         case HSSFCell.CELL_TYPE_STRING:            System.out.println(oneCell.getStringCellValue());            break;         case HSSFCell.CELL_TYPE_FORMULA:            System.out.println(oneCell.getCellFormula());            break;         case HSSFCell.CELL_TYPE_NUMERIC:            System.out.println(oneCell.getNumericCellValue());            break;         case HSSFCell.CELL_TYPE_ERROR:            System.out.println("Error!");            break;         }      // End Inner Loop      }

当代码运行时，注意日期显示为数字，而不是日期，那是因为日期的格式没有存储在它的值中。这是单元格的一种格式化选择。本文章系列的第 2 部分将讨论如何为日期保留格式。

还有一个重点需要注意：在 清单 7 中的以下代码行（在下面的 清单 8 中断开）中，这段代码测试每个单元格，确保它们不为空。

清单 8. 不要忘记测试空值（ExcelReader.java）

// If the cell is blank, the cell object is null, so don't // try to use it. It will cause errors.// Use continue to skip it and just keep going.if (oneCell == null) {   continue;}

在一个 Java 环境中，如果一个对象为空，试图操作它将导致错误。在使用行和单元格这样的对象之前，一定要测试它们，确保它们不为空。

---