Apache POI – Reading and Writing Excel file in Java
来源于:https://www.mkyong.com/java/apache-poi-reading-and-writing-excel-file-in-java/
In this article, we will discuss about how to read and write an excel file using Apache POI
1. Basic definitions for Apache POI library
This section briefly describe about basic classes used during Excel Read and Write.
HSSF
is prefixed before the class name to indicate operations related to a Microsoft Excel 2003 file.XSSF
is prefixed before the class name to indicate operations related to a Microsoft Excel 2007 file or later.XSSFWorkbook
andHSSFWorkbook
are classes which act as an Excel WorkbookHSSFSheet
andXSSFSheet
are classes which act as an Excel WorksheetRow
defines an Excel rowCell
defines an Excel cell addressed in reference to a row.
2. Download Apache POI
Apache POI library is easily available using Maven Dependencies.
pom.xml
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.15</version>
</dependency>
3. Apache POI library – Writing a Simple Excel
The below code shows how to write a simple Excel file using Apache POI libraries. The code uses a 2 dimensional data array to hold the data. The data is written to a XSSFWorkbook
object. XSSFSheet
is the work sheet being worked on. The code is as shown below:
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