1. Overview
1.概述
In this tutorial, we will demonstrate the use of the Apache POI and JExcel APIs for working with Excel spreadsheets.
在本教程中，我们将演示如何使用Apache POI和JExcel APIs来处理Excel电子表格。
Both libraries can be used to dynamically read, write and modify the content of an Excel spreadsheet and provide an effective way of integrating Microsoft Excel into a Java Application.
这两个库都可以用来动态地读、写和修改Excel电子表格的内容，并提供了一种将Microsoft Excel集成到Java应用程序中的有效方法。
2. Maven Dependencies
2.Maven的依赖性
To begin, we will need to add the following dependencies to our pom.xml file:
首先，我们将需要在我们的pom.xml文件中添加以下依赖项。
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>5.2.0</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>5.2.0</version>
</dependency>The latest versions of poi-ooxml and jxls-jexcel can be downloaded from Maven Central.
poi-ooxml和jxls-jexcel的最新版本可以从Maven中心下载。
3. Apache POI
3.Apache POI
The Apache POI library supports both .xls and .xlsx files and is a more complex library than other Java libraries for working with Excel files.
Apache POI库同时支持.xls和.xlsx文件，与其他处理Excel文件的Java库相比，这是一个更复杂的库。
It provides the Workbook interface for modeling an Excel file, and the Sheet, Row and Cell interfaces that model the elements of an Excel file, as well as implementations of each interface for both file formats.
它提供了用于建模Excel文件的Workbook接口，以及用于建模Excel文件元素的Sheet、Row和Cell接口，以及用于两种文件格式的每个接口的实现。
When working with the newer .xlsx file format, we would use the XSSFWorkbook, XSSFSheet, XSSFRow and XSSFCell classes.
当使用较新的.xlsx文件格式时，我们将使用XSSFWorkbook、XSSFSheet、XSSFRow和XSSFCell 类。
To work with the older .xls format, we use the HSSFWorkbook, HSSFSheet, HSSFRow and HSSFCell classes.
为了处理旧的.xls格式，我们使用HSSFWorkbook、HSSFSheet、HSSFRow和HSSFCell类。
3.1. Reading From Excel
3.1.从Excel中读取数据
Let’s create a method that opens a .xlsx file and then reads content from the first sheet of the file.
让我们创建一个方法，打开一个.xlsx文件，然后从该文件的第一张表中读取内容。
The method for reading cell content varies depending on the type of the data in the cell. The type of the cell content can be determined using the getCellType() method of the Cell interface.
读取单元格内容的方法根据单元格中数据的类型而不同。可以使用Cell接口的getCellType()方法确定单元格内容的类型。
First, let’s open the file from a given location:
首先，让我们从一个给定的位置打开该文件。
FileInputStream file = new FileInputStream(new File(fileLocation));
Workbook workbook = new XSSFWorkbook(file);Next, let’s retrieve the first sheet of the file and iterate through each row:
接下来，让我们检索该文件的第一张纸，并遍历每一行。
Sheet sheet = workbook.getSheetAt(0);
Map<Integer, List<String>> data = new HashMap<>();
int i = 0;
for (Row row : sheet) {
data.put(i, new ArrayList<String>());
for (Cell cell : row) {
switch (cell.getCellType()) {
case STRING: ... break;
case NUMERIC: ... break;
case BOOLEAN: ... break;
case FORMULA: ... break;
default: data.get(new Integer(i)).add(" ");
}
}
i++;
}Apache POI has different methods for reading each type of data. Let’s expand on the content of each switch case above.
Apache POI对每种类型的数据都有不同的读取方法。让我们对上面每个开关案例的内容进行展开。
When the cell type enum value is STRING, the content will be read using the getRichStringCellValue() method of Cell interface:
当单元格类型枚举值为STRING时，将使用Cell接口的getRichStringCellValue()方法读取内容。
data.get(new Integer(i)).add(cell.getRichStringCellValue().getString());Cells having the NUMERIC content type can contain either a date or a number and are read in the following manner:
具有NUMERIC内容类型的单元格可以包含一个日期或一个数字，并以下列方式读取。
if (DateUtil.isCellDateFormatted(cell)) {
data.get(i).add(cell.getDateCellValue() + "");
} else {
data.get(i).add(cell.getNumericCellValue() + "");
}For BOOLEAN values, we have the getBooleanCellValue() method:
对于BOOLEAN值，我们有getBooleanCellValue()方法。
data.get(i).add(cell.getBooleanCellValue() + "");And when the cell type is FORMULA, we can use the getCellFormula() method:
而当单元格类型为FORMULA时，我们可以使用getCellFormula()方法。
data.get(i).add(cell.getCellFormula() + "");3.2. Writing to Excel
3.2.写入Excel
Apache POI uses the same interfaces presented in the previous section for writing to an Excel file and has better support for styling than JExcel.
Apache POI使用与上一节介绍的相同的接口来写入Excel文件，并且比JExcel对样式的支持更好。
Let’s create a method that writes a list of persons to a sheet titled “Persons”.
让我们创建一个方法，将人员列表写入一个名为“人员”的工作表。
First, we will create and style a header row that contains “Name” and “Age” cells:
首先，我们将创建一个包含“姓名”和“年龄”单元格的标题行，并对其进行样式设计。
Workbook workbook = new XSSFWorkbook();
Sheet sheet = workbook.createSheet("Persons");
sheet.setColumnWidth(0, 6000);
sheet.setColumnWidth(1, 4000);
Row header = sheet.createRow(0);
CellStyle headerStyle = workbook.createCellStyle();
headerStyle.setFillForegroundColor(IndexedColors.LIGHT_BLUE.getIndex());
headerStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
XSSFFont font = ((XSSFWorkbook) workbook).createFont();
font.setFontName("Arial");
font.setFontHeightInPoints((short) 16);
font.setBold(true);
headerStyle.setFont(font);
Cell headerCell = header.createCell(0);
headerCell.setCellValue("Name");
headerCell.setCellStyle(headerStyle);
headerCell = header.createCell(1);
headerCell.setCellValue("Age");
headerCell.setCellStyle(headerStyle);Next, let’s write the content of the table with a different style:
接下来，让我们用不同的风格来写表格的内容。
CellStyle style = workbook.createCellStyle();
style.setWrapText(true);
Row row = sheet.createRow(2);
Cell cell = row.createCell(0);
cell.setCellValue("John Smith");
cell.setCellStyle(style);
cell = row.createCell(1);
cell.setCellValue(20);
cell.setCellStyle(style);Finally, let’s write the content to a “temp.xlsx” file in the current directory and close the workbook:
最后，让我们把内容写入当前目录下的“temp.xlsx”文件，并关闭工作簿。
File currDir = new File(".");
String path = currDir.getAbsolutePath();
String fileLocation = path.substring(0, path.length() - 1) + "temp.xlsx";
FileOutputStream outputStream = new FileOutputStream(fileLocation);
workbook.write(outputStream);
workbook.close();Let’s test the above methods in a JUnit test that writes content to the temp.xlsx file and then reads the same file to verify it contains the text we have written:
让我们在一个JUnit测试中测试上述方法，将内容写入temp.xlsx文件，然后读取同一文件以验证它包含我们所写的文本。
public class ExcelTest {
private ExcelPOIHelper excelPOIHelper;
private static String FILE_NAME = "temp.xlsx";
private String fileLocation;
@Before
public void generateExcelFile() throws IOException {
File currDir = new File(".");
String path = currDir.getAbsolutePath();
fileLocation = path.substring(0, path.length() - 1) + FILE_NAME;
excelPOIHelper = new ExcelPOIHelper();
excelPOIHelper.writeExcel();
}
@Test
public void whenParsingPOIExcelFile_thenCorrect() throws IOException {
Map<Integer, List<String>> data
= excelPOIHelper.readExcel(fileLocation);
assertEquals("Name", data.get(0).get(0));
assertEquals("Age", data.get(0).get(1));
assertEquals("John Smith", data.get(1).get(0));
assertEquals("20", data.get(1).get(1));
}
}4. JExcel
4.JExcel
The JExcel library is a lightweight library with the advantage that it’s easier to use than Apache POI, but with the disadvantage that it only provides support for processing Excel files in the .xls (1997-2003) format.
JExcel库是一个轻量级的库，优点是比Apache POI更容易使用，但缺点是它只提供对处理.xls（1997-2003）格式的Excel文件的支持。
At the moment, .xlsx files are not supported.
目前，.xlsx文件不被支持。
4.1. Reading From Excel
4.1.从Excel中读取数据
In order to work with Excel files, this library provides a series of classes that represent the different parts of an Excel file. The Workbook class represents the entire collection of sheets. The Sheet class represents a single sheet, and the Cell class represents a single cell of a spreadsheet.
为了处理Excel文件，这个库提供了一系列的类，代表了Excel文件的不同部分。Workbook类表示整个工作表的集合。Sheet类表示单个工作表，而Cell类表示电子表格的单个单元。
Let’s write a method that creates a workbook from a specified Excel file, gets the first sheet of the file and then traverses its content and adds each row in a HashMap:
让我们写一个方法，从指定的Excel文件中创建一个工作簿，获得文件的第一张纸，然后遍历其内容，并在HashMap中添加每行。
public class JExcelHelper {
public Map<Integer, List<String>> readJExcel(String fileLocation)
throws IOException, BiffException {
Map<Integer, List<String>> data = new HashMap<>();
Workbook workbook = Workbook.getWorkbook(new File(fileLocation));
Sheet sheet = workbook.getSheet(0);
int rows = sheet.getRows();
int columns = sheet.getColumns();
for (int i = 0; i < rows; i++) {
data.put(i, new ArrayList<String>());
for (int j = 0; j < columns; j++) {
data.get(i)
.add(sheet.getCell(j, i)
.getContents());
}
}
return data;
}
}4.2. Writing to Excel
4.2.写入Excel
For writing to an Excel file, the JExcel library offers classes similar to the ones used above, which model a spreadsheet file: WritableWorkbook, WritableSheet and WritableCell.
对于向Excel文件的写入，JExcel库提供了与上面使用的类似的类，这些类为电子表格文件建模。WritableWorkbook, WritableSheet 和 WritableCell。
The WritableCell class has subclasses corresponding to the different types of content that can be written: Label, DateTime, Number, Boolean, Blank and Formula.
WritableCell类有相应的子类，可以写入不同类型的内容。Label, DateTime, Number, Boolean, Blank 和Formula。
This library also provides support for basic formatting, such as controlling font, color and cell width.
这个库还提供对基本格式化的支持，如控制字体、颜色和单元格宽度。
Let’s write a method that creates a workbook called “temp.xls” in the current directory and then writes the same content we wrote in the Apache POI section.
让我们写一个方法，在当前目录下创建一个名为“temp.xls”的工作簿，然后写入我们在Apache POI部分写的同样内容。
First, let’s create the workbook:
首先，让我们创建工作簿。
File currDir = new File(".");
String path = currDir.getAbsolutePath();
String fileLocation = path.substring(0, path.length() - 1) + "temp.xls";
WritableWorkbook workbook = Workbook.createWorkbook(new File(fileLocation));Next, let’s create the first sheet and write the header of the excel file, containing “Name” and “Age” cells:
接下来，让我们创建第一个工作表，并编写excel文件的标题，包含“姓名”和“年龄”单元。
WritableSheet sheet = workbook.createSheet("Sheet 1", 0);
WritableCellFormat headerFormat = new WritableCellFormat();
WritableFont font
= new WritableFont(WritableFont.ARIAL, 16, WritableFont.BOLD);
headerFormat.setFont(font);
headerFormat.setBackground(Colour.LIGHT_BLUE);
headerFormat.setWrap(true);
Label headerLabel = new Label(0, 0, "Name", headerFormat);
sheet.setColumnView(0, 60);
sheet.addCell(headerLabel);
headerLabel = new Label(1, 0, "Age", headerFormat);
sheet.setColumnView(0, 40);
sheet.addCell(headerLabel);With a new style, let’s write the content of the table we’ve created:
有了新的样式，我们来写一下我们所创建的表格的内容。
WritableCellFormat cellFormat = new WritableCellFormat();
cellFormat.setWrap(true);
Label cellLabel = new Label(0, 2, "John Smith", cellFormat);
sheet.addCell(cellLabel);
Number cellNumber = new Number(1, 2, 20, cellFormat);
sheet.addCell(cellNumber);It’s very important to remember to write to the file and close it at the end so it can be used by other processes, using the write() and close() methods of Workbook class:
使用Workbook类的write()和close()方法，记住向文件写入并在最后关闭它，以便它可以被其他进程使用，这是非常重要的。
workbook.write();
workbook.close();5. Conclusion
5.结论
This article has illustrated how to use the Apache POI API and JExcel API to read and write an Excel file from a Java program.
本文说明了如何使用Apache POI API和JExcel API来从Java程序中读写Excel文件。
The complete source code for this article can be found in the GitHub project.
本文的完整源代码可以在GitHub项目中找到。