Introduce a,
In the usual business system development, it is necessary to use the export, import Excel function, today to sum up, if you are confused by this demand, then after reading this article, you will gain!
This paper compares Apache POI, EasyPOI and easyExecl, and gives their basic application methods.
Second, the apache poi
Apache POI
Apache POI is a popular API that allows programmers to create, modify, and display MS Office files using Java programs. This is an open source library developed by the Apache Software Foundation that uses Java for distributed design or modification of Microsoft Office files. It contains classes and methods to decode user input data or files into MS Office documents.
About a long time ago, Microsoft’s spreadsheet software Excel is simple to operate, intuitive and convenient to store data, and also supports printing statements. At the beginning of its birth, it can be said that it was favored by white-collar workers in the office, which greatly improved the work efficiency. Before long, it became a necessary tool in the office.
As more new languages emerged, such as the familiar Java, teams began developing tools that worked seamlessly with Excel!
One of these is apache’s POI, which started as the PoI Project in Jakarta and has since been open-source to the Apache Foundation!
Of course, there are many third-party tools in the Java ecosystem that can seamlessly work with Excel. Apache POI is the most widely used tool in the industry, so I won’t talk too much about other tools!
2.1. Maven dependencies for Apache POI are first introduced
<dependencies>
<! --xls(03)-->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.1.2</version>
</dependency>
<! --xlsx(07)-->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.1.2</version>
</dependency>
<! -- Time formatting tool -->
<dependency>
<groupId>joda-time</groupId>
<artifactId>joda-time</artifactId>
<version>2.10.6</version>
</dependency>
</dependencies>
Copy the code2.2 export Execl
Export operation, that is, use Java to write data into Excel. In a common scenario, export the data on the page, such as financial data or commodity data, to generate Excel and return it to users for downloading.
There are three ways to export an API from the POI library
- HSSF way: Files exported in this mode are in office 2003 format, that is
.xls, the advantage is fast data export, but a maximum of 65536 lines of data - XSSF way: Files exported in this mode are in office 2007 format, that is
.xlsx, the exported data is not limited by the number of rows, but the exported data is slow - SXSSF: SXSSF is a compatible streaming extension of THE XSSF API. It solves the problem that memory overflow occurs when a large amount of data is exported using XSSF and supports the export of large amounts of Excel data
2.2.1 Export through HSSF
A maximum of 65536 pieces of data can be exported in HSSF mode.
public class ExcelWrite2003Test {
public static String PATH = "D:/Desktop/";
public static void main(String[] args) throws Exception {
/ / time
long begin = System.currentTimeMillis();
// Create a workbook
Workbook workbook = new HSSFWorkbook();
/ / create a table
Sheet sheet = workbook.createSheet();
// Write data
for (int rowNumber = 0; rowNumber < 65536; rowNumber++) {
/ / create a line
Row row = sheet.createRow(rowNumber);
for (int cellNumber = 0; cellNumber < 10; cellNumber++) {
/ / create the columns
Cell cell = row.createCell(cellNumber);
cell.setCellValue(cellNumber);
}
}
System.out.println("ExcelWrite2003 export completed......");
FileOutputStream fileOutputStream = new FileOutputStream(PATH + "User Information Sheet 03.xls");
workbook.write(fileOutputStream);
fileOutputStream.close();
long end = System.currentTimeMillis();
// Output time (s)
System.out.println("Unavailable" + (double) (end - begin) / 1000 + "Seconds"); }}// ExcelWrite2003 export completed......
// It took 1.387 seconds
Copy the code2.2.2 XSSF Export
XSSF supports mass data export. All data is written to the memory first and then exported, which may cause memory overflow.
public class ExcelWrite2007Test {
public static String PATH = "D:/Desktop/";
public static void main(String[] args) throws Exception {
/ / time
long begin = System.currentTimeMillis();
// Create a workbook
Workbook workbook = new XSSFWorkbook();
/ / create a table
Sheet sheet = workbook.createSheet();
// Write data
for (int rowNumber = 0; rowNumber < 65537; rowNumber++) {
Row row = sheet.createRow(rowNumber);
for (int cellNumber = 0; cellNumber < 10; cellNumber++) {
Cell cell = row.createCell(cellNumber);
cell.setCellValue(cellNumber);
}
}
System.out.println("ExcelWrite2007 export completed......");
FileOutputStream fileOutputStream = new FileOutputStream(PATH + "User Information Sheet 07.xlsx");
workbook.write(fileOutputStream);
fileOutputStream.close();
long end = System.currentTimeMillis();
System.out.println("Unavailable" + (double) (end - begin) / 1000 + "Seconds"); }}// ExcelWrite2007 export completed......
// 5.382 seconds
Copy the code2.2.3 Export by SXSSF
The SXSSF mode is an extension of the XSSF mode. The SXSSF mode has low memory. When exporting data, write data to disks first and then export data to avoid insufficient memory, which leads to abnormal program running.
public class ExcelWriteSXSSFTest {
public static String PATH = "D:/Desktop/";
public static void main(String[] args) throws Exception {
/ / time
long begin = System.currentTimeMillis();
// Create a workbook
Workbook workbook = new SXSSFWorkbook();
/ / create a table
Sheet sheet = workbook.createSheet();
// Write data
for (int rowNumber = 0; rowNumber < 100000; rowNumber++) {
Row row = sheet.createRow(rowNumber);
for (int cellNumber = 0; cellNumber < 10; cellNumber++) {
Cell cell = row.createCell(cellNumber);
cell.setCellValue(cellNumber);
}
}
System.out.println("ExcelWriteSXSSFTest export completed......");
FileOutputStream fileOutputStream = new FileOutputStream(PATH + "User Information Sheet 2007.xlsx");
workbook.write(fileOutputStream);
fileOutputStream.close();
long end = System.currentTimeMillis();
System.out.println("Unavailable" + (double) (end - begin) / 1000 + "Seconds"); }}// ExcelWriteSXSSFTest export completed......
// Take 2.621 seconds
Copy the code2.3 import Execl
Import operation, that is, excel data using Java tool library to parse it out, and then write excel data into the database!
Similarly, in the POI library, there are three ways to import the API, corresponding to the export above!
2.3.1 HSSF import
public class ExcelRead2003Test {
public static String PATH = "D:/Desktop/";
public static void main(String[] args) throws Exception {
// Get the file stream
FileInputStream inputStream = new FileInputStream(PATH + "User information sheet.xls");
//1. Create a workbook and see what you can do with Excel
Workbook workbook = new HSSFWorkbook(inputStream);
/ / 2. Get the table
Sheet sheet = workbook.getSheetAt(0);
/ / 3. Get the line
Row row = sheet.getRow(0);
/ / 4 columns
Cell cell = row.getCell(0);
getValue(cell);
inputStream.close();
}
public static void getValue(Cell cell){
// Match type data
if(cell ! =null) {
CellType cellType = cell.getCellType();
String cellValue = "";
switch (cellType) {
case STRING: / / string
System.out.print("[String]");
cellValue = cell.getStringCellValue();
break;
case BOOLEAN: // Boolean type
System.out.print("[a Boolean type]");
cellValue = String.valueOf(cell.getBooleanCellValue());
break;
case BLANK: / / empty
System.out.print("[BLANK type]");
break;
case NUMERIC: // Number (date, common number)
System.out.print("[the] NUMERIC types");
if (HSSFDateUtil.isCellDateFormatted(cell)) { / / date
System.out.print("[date].");
Date date = cell.getDateCellValue();
cellValue = new DateTime(date).toString("yyyy-MM-dd");
} else {
// It is not a date format to prevent long numbers
System.out.print("[Convert to string output]");
cell.setCellType(CellType.STRING);
cellValue = cell.toString();
}
break;
case ERROR:
System.out.print("[data type error]");
break; } System.out.println(cellValue); }}}Copy the code2.3.2 XSSF import
public class ExcelRead2007Test {
public static String PATH = "D:/Desktop/";
public static void main(String[] args) throws Exception {
// Get the file stream
FileInputStream inputStream = new FileInputStream(PATH + "User information table.xlsx");
//1. Create a workbook and see what you can do with Excel
Workbook workbook = new XSSFWorkbook(inputStream);
/ / 2. Get the table
Sheet sheet = workbook.getSheetAt(0);
/ / 3. Get the line
Row row = sheet.getRow(0);
/ / 4 columns
Cell cell = row.getCell(0);
getValue(cell);
inputStream.close();
}
public static void getValue(Cell cell){
// Match type data
if(cell ! =null) {
CellType cellType = cell.getCellType();
String cellValue = "";
switch (cellType) {
case STRING: / / string
System.out.print("[String]");
cellValue = cell.getStringCellValue();
break;
case BOOLEAN: // Boolean type
System.out.print("[a Boolean type]");
cellValue = String.valueOf(cell.getBooleanCellValue());
break;
case BLANK: / / empty
System.out.print("[BLANK type]");
break;
case NUMERIC: // Number (date, common number)
System.out.print("[the] NUMERIC types");
if (HSSFDateUtil.isCellDateFormatted(cell)) { / / date
System.out.print("[date].");
Date date = cell.getDateCellValue();
cellValue = new DateTime(date).toString("yyyy-MM-dd");
} else {
// It is not a date format to prevent long numbers
System.out.print("[Convert to string output]");
cell.setCellType(CellType.STRING);
cellValue = cell.toString();
}
break;
case ERROR:
System.out.print("[data type error]");
break; } System.out.println(cellValue); }}}Copy the code2.3.3 Import by SXSSF
public class ExcelReadSXSSFTest {
public static String PATH = "D:/Desktop/";
public static void main(String[] args) throws Exception {
// Get the file stream
//1. Create a workbook and see what you can do with Excel
OPCPackage opcPackage = OPCPackage.open(PATH + "User Information Sheet 2007.xlsx");
XSSFReader xssfReader = new XSSFReader(opcPackage);
StylesTable stylesTable = xssfReader.getStylesTable();
ReadOnlySharedStringsTable sharedStringsTable = new ReadOnlySharedStringsTable(opcPackage);
// Create XMLReader and set ContentHandler
XMLReader xmlReader = SAXHelper.newXMLReader();
xmlReader.setContentHandler(new XSSFSheetXMLHandler(stylesTable, sharedStringsTable, new SimpleSheetContentsHandler(), false));
// Parse each Sheet data
Iterator<InputStream> sheetsData = xssfReader.getSheetsData();
while (sheetsData.hasNext()) {
try (InputStream inputStream = sheetsData.next();) {
xmlReader.parse(newInputSource(inputStream)); }}}/**
* 内容处理器
*/
public static class SimpleSheetContentsHandler implements XSSFSheetXMLHandler.SheetContentsHandler {
protected List<String> row;
/**
* A row with the (zero based) row number has started
* @param rowNum
*/
@Override
public void startRow(int rowNum) {
row = new ArrayList<>();
}
/**
* A row with the (zero based) row number has ended
* @param rowNum
*/
@Override
public void endRow(int rowNum) {
if (row.isEmpty()) {
return;
}
// Process data
System.out.println(row.stream().collect(Collectors.joining("")));
}
/**
* A cell, with the given formatted value (may be null),
* and possibly a comment (may be null), was encountered
* @param cellReference
* @param formattedValue
* @param comment
*/
@Override
public void cell(String cellReference, String formattedValue, XSSFComment comment) {
row.add(formattedValue);
}
/**
* A header or footer has been encountered
* @param text
* @param isHeader
* @param tagName
*/
@Override
public void headerFooter(String text, boolean isHeader, String tagName) {}}}Copy the codeThird, easypoi
easypoi
Ago, there was a big programmer, to a company after they talk to business people, and the salesman has many requirements on excel, want to have a report, for example, his header is more than a row header, in a few days later, he wanted to add style to the header, key data such as the red, a few more days, He wants to add a total at the end, and so on!
At first, it was all copy and copy, but then I found a lot of repeated code in the system, so one day I really couldn’t stand it, so I used annotations to fix these customized logic into a high degree, and pulled out the public, so easyPOI was born!
Easypoi’s bottom layer is also based on apache POI in-depth development, its main characteristics is more repetitive work, simplify all, avoid writing repetitive code!
Let’s take a look at easyPOI, an open source tool
3.1. Add dependency packages first
<dependencies>
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-base</artifactId>
<version>4.1.0</version>
</dependency>
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-web</artifactId>
<version>4.1.0</version>
</dependency>
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-annotation</artifactId>
<version>4.1.0</version>
</dependency>
</dependencies>
Copy the code3.2. Import and export with annotations
Easypoi’s biggest advantage is that it is easy to export and import Excel based on annotation entity class.
First, we create an entity class, UserEntity, where the @excel annotation represents the header information for the exported file.
public class UserEntity {
@excel (name = "name ")
private String name;
@excel (name = "age ")
private int age;
@excel (name = "yyyY-MM-DD HH: MM :ss", width = 20.0) @excel (name =" yyyY-MM-DD HH: MM :ss", width = 20.0)
private Date time;
// omit the get/set method
}
Copy the codeNext, let’s write the export service!
public class EasypoiExcelExport {
public static void main(String[] args) throws IOException {
List<UserEntity> dataList = new ArrayList<>();
for (int i = 0; i < 10; i++) {
UserEntity userEntity = new UserEntity();
userEntity.setName("Zhang" + i);
userEntity.setAge(20 + i);
userEntity.setTime(new Date(System.currentTimeMillis() + i));
dataList.add(userEntity);
}
// Generate an Excel document
Workbook workbook = ExcelExportUtil.exportExcel(new ExportParams("User"."User Information"),
UserEntity.class, dataList);
FileOutputStream fos = new FileOutputStream("D:/Desktop/easypoi-user1.xls"); workbook.write(fos); fos.close(); }}Copy the codeResults preview:
[img-rtxaemfe-1618049073510] (E: TyporaWork easypoi1.png)
The corresponding import operation is also very simple, the source code is as follows:
public class importEasypoi {
public static void main(String[] args) {
ImportParams params = new ImportParams();
params.setTitleRows(1);
params.setHeadRows(1);
long start = new Date().getTime();
List<UserEntity> list = ExcelImportUtil.importExcel(new File("D:/Desktop/easypoi-user1.xls"),UserEntity.class, params);
System.out.println(newDate().getTime() - start); }}Copy the code3.3 custom data structure export and import
Easypoi also supports custom data structure export into Excel.
- Custom data export excel
public class CustomExport {
public static void main(String[] args) throws Exception {
// Encapsulate the header
List<ExcelExportEntity> entityList = new ArrayList<ExcelExportEntity>();
entityList.add(new ExcelExportEntity("Name"."name"));
entityList.add(new ExcelExportEntity("Age"."age"));
ExcelExportEntity entityTime = new ExcelExportEntity("Operation time"."time");
entityTime.setFormat("yyyy-MM-dd HH:mm:ss");
entityTime.setWidth(20.0);
entityList.add(entityTime);
// Encapsulate the data body
List<Map<String, Object>> dataList = new ArrayList<>();
for (int i = 0; i < 10; i++) {
Map<String, Object> userEntityMap = new HashMap<>();
userEntityMap.put("name"."Zhang" + i);
userEntityMap.put("age".20 + i);
userEntityMap.put("time".new Date(System.currentTimeMillis() + i));
dataList.add(userEntityMap);
}
// Generate an Excel document
Workbook workbook = ExcelExportUtil.exportExcel(new ExportParams("Students"."User Information"), entityList, dataList);
FileOutputStream fos = new FileOutputStream("D:/Desktop/user2.xls");
workbook.write(fos);
fos.close();
}
Copy the code- Import excel
public class CustomImport {
public static void main(String[] args) {
ImportParams params = new ImportParams();
params.setTitleRows(1);
params.setHeadRows(1);
long start = new Date().getTime();
List<Map<String, Object>> list = ExcelImportUtil.importExcel(new File("D:/Desktop/user2.xls"),Map.class, params);
System.out.println(newDate().getTime() - start); }}Copy the codeFour, easyexcel
EasyExecl
Easyexcel is an excel parsing tool of Alibaba open source. The underlying logic is also developed based on Apache POI. The difference is that when reading and writing data, the SAX mode is used to parse the data line by line. In the case of a large amount of concurrency, it can still run stably!
Below, let’s take a look at this new show!
4.1. Add dependency packages first
<dependencies>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.2.6</version>
</dependency>
<! -- Common tools library -->
<dependency>
<groupId>com.google.guava</groupId>
<artifactId>guava</artifactId>
<version>29.0 the jre</version>
</dependency>
</dependencies>
Copy the code4.2. Export and import with annotations
Easyexcel also supports export and import with annotations!
First, we create an entity class, UserEntity, where the @ExcelProperty annotation represents the header information for the exported file.
public class UserEntity {
@excelProperty (value = "name ")
private String name;
@excelProperty (value = "age ")
private int age;
@DateTimeFormat("yyyy-MM-dd HH:mm:ss")
@excelProperty (value = "operation time ")
private Date time;
// omit the get/set method
}
Copy the codeNext, let’s write the export service!
public class EasyEmport {
public static void main(String[] args) {
List<UserEntity> dataList = new ArrayList<>();
for (int i = 0; i < 10; i++) {
UserEntity userEntity = new UserEntity();
userEntity.setName("Zhang" + i);
userEntity.setAge(20 + i);
userEntity.setTime(new Date(System.currentTimeMillis() + i));
dataList.add(userEntity);
}
EasyExcel.write("D:/Desktop/easyexcel-user1.xls", UserEntity.class).sheet("User Information").doWrite(dataList); }}Copy the codeThe corresponding import operation is also very simple, the source code is as follows:
public class EasyImport {
public static void main(String[] args) {
String filePath = "D:/Desktop/easyexcel-user1.xls"; List<UserEntity> list = EasyExcel.read(filePath).head(UserEntity.class).sheet().doReadSync(); System.out.println(JSONArray.toJSONString(list)); }}Copy the code4.3 Custom data structure export and import
Easyexcel also supports custom data structure export into Excel.
- Custom data export excel
public static void main(String[] args) {
/ / headers
List<List<String>> headList = new ArrayList<>();
headList.add(Lists.newArrayList("Name"));
headList.add(Lists.newArrayList("Age"));
headList.add(Lists.newArrayList("Operation time"));
/ / data volume
List<List<Object>> dataList = new ArrayList<>();
for (int i = 0; i < 10; i++) {
List<Object> data = new ArrayList<>();
data.add("Zhang" + i);
data.add(20 + i);
data.add(new Date(System.currentTimeMillis() + i));
dataList.add(data);
}
EasyExcel.write("D:/Desktop/easyexcel-user2.xls").head(headList).sheet("User Information").doWrite(dataList);
}
Copy the code- Import excel
public static void main(String[] args) {
String filePath = "D:/Desktop/easyexcel-user2.xls";
UserDataListener userDataListener = new UserDataListener();
EasyExcel.read(filePath, userDataListener).sheet().doRead();
System.out.println("Header:" + JSONArray.toJSONString(userDataListener.getHeadList()));
System.out.println("Data body:" + JSONArray.toJSONString(userDataListener.getDataList()));
}
Copy the codeFive, the summary
In general, easyPOI and EasyExcel are based on Apache POI for secondary development.
The differences are:
1. When easyPOI reads and writes data, it gives priority to writing data into memory first. The advantage is that the read and write performance is very high, but OOM will appear when the data is large.
2, EasyExcel based on SAX mode for reading and writing data, there will be no OOM situation, the program has high concurrent scene verification, so the program runs more stable, compared with easyPOI, read and write performance is slightly slower!
Easypoi and EasyExcel is also a point of difference, easyPOI for customized export support is very rich, if the current project needs, concurrency is not large, the amount of data is not large, but the need to export Excel file styles vary greatly, then I recommend you to use easyPOI; Instead, use EasyExcel!
Six, reference
1. Apache PoI-interface documentation
2, EasyPoi – interface documentation
3. Easyexcel – Interface documentation