Java operation Excel table
POI and easyExcel
Common information
1. Export user information to excel (export data...)
2. Input the information in the Excel table into the website database (exercise upload...)
Excel processing is often designed in development, such as exporting excel and importing excel into the database!
At present, Apache POI and Alibaba's easyExcel are more popular to operate excel!
Apache POI
Apache POI official website: https://poi.apache.org/
HSSF provides the function of reading and writing files in Microsoft Excel format.
XSSF - provides the ability to read and write files in Microsoft Excel OOXML format.
HWPF - provides the ability to read and write Microsoft Word files.
HSLF - provides the ability to read and write Microsof PowerPoint files.
HDGF - provides the ability to read and write files in Microsoft Visio format.
easyExcel
easyExcel official website: https://github.com/alibaba/easyexcel
POI excel write
Create project
1. Create an empty project
2. Import dependency
<!--xls(03)-->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.9</version>
</dependency>
<!--xlsx(07)-->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.9</version>
</dependency>
<!--Date formatting tool-->
<dependency>
<groupId>joda-time</groupId>
<artifactId>joda-time</artifactId>
<version>2.10.1</version>
</dependency>
<!--test-->
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.13</version>
<scope>test</scope>
</dependency>
03 version:
@Test
public void ExcelTset() throws IOException {
//1. Create a workbook
Workbook workbook = new HSSFWorkbook();
//2. Create a worksheet
Sheet sheet = workbook.createSheet("Statistical table of Novels");
//3. Create a row
Row row1 = sheet.createRow(0);//(1,1)
//4. Create a cell
Cell cell11 = row1.createCell(0);
cell11.setCellValue("Novel name");
//(1,2)
Cell cell12 = row1.createCell(1);
cell12.setCellValue("Cover the sky");
//Second line
Row row2 = sheet.createRow(1);
Cell cell21 = row2.createCell(0);
cell21.setCellValue("Number of novel words");
row2.createCell(1).setCellValue("200 Ten thousand words");
Row row3 = sheet.createRow(2);
Cell cell31 = row3.createCell(0);
cell31.setCellValue("date");
row3.createCell(1).setCellValue(new Date());
//Generate a table
FileOutputStream fileOutputStream = new FileOutputStream(PATH + "Novel 03.xls");
workbook.write(fileOutputStream);
//Close flow
fileOutputStream.close();
System.out.println("Table generation completed");
}
07 version:
@Test
public void ExcelTset2() throws IOException {
//1. Create a workbook
Workbook workbook = new XSSFWorkbook();
//2. Create a worksheet
Sheet sheet = workbook.createSheet("Statistical table of Novels");
//3. Create a row
Row row1 = sheet.createRow(0);//(1,1)
//4. Create a cell
Cell cell11 = row1.createCell(0);
cell11.setCellValue("Novel name");
//(1,2)
Cell cell12 = row1.createCell(1);
cell12.setCellValue("Cover the sky");
//Second line
Row row2 = sheet.createRow(1);
Cell cell21 = row2.createCell(0);
cell21.setCellValue("Number of novel words");
row2.createCell(1).setCellValue("200 Ten thousand words");
Row row3 = sheet.createRow(2);
Cell cell31 = row3.createCell(0);
cell31.setCellValue("date");
row3.createCell(1).setCellValue(new Date());
//Generate a table
FileOutputStream fileOutputStream = new FileOutputStream(PATH + "Novel 07.xlsx");
workbook.write(fileOutputStream);
//Close flow
fileOutputStream.close();
System.out.println("Table generation completed");
}
Pay attention to the difference of objects, file suffix!
Large file write HSSF
@Test
public void ExcelBig() throws IOException {
//time
long begin = System.currentTimeMillis();
Workbook workbook = new HSSFWorkbook();
Sheet sheet = workbook.createSheet();
for (int rowNum = 0; rowNum < 65536; rowNum++) {
Row row = sheet.createRow(rowNum);
for (int cellNum = 0; cellNum < 10; cellNum++) {
Cell cell = row.createCell(cellNum);
cell.setCellValue(cellNum);
}
}
System.out.println("Over");
FileOutputStream fileOutputStream = new FileOutputStream(PATH + "bigData03.xls");
workbook.write(fileOutputStream);
fileOutputStream.close();
long end = System.currentTimeMillis();
System.out.println((float) (end-begin)/1000);
}
Disadvantages: only 65536 rows can be processed at most, otherwise an exception will be thrown
java.lang.IllegalArgumentException: Invalid row number (65536) outside allowable range (0..65535)
Advantages: write cache in the process, do not operate the disk, and write to the disk at the last time, which is fast
Large file write XSSF
@Test
public void ExcelBig2() throws IOException {
//time
long begin = System.currentTimeMillis();
Workbook workbook = new XSSFWorkbook();
Sheet sheet = workbook.createSheet();
for (int rowNum = 0; rowNum < 100000; rowNum++) {
Row row = sheet.createRow(rowNum);
for (int cellNum = 0; cellNum < 10; cellNum++) {
Cell cell = row.createCell(cellNum);
cell.setCellValue(cellNum);
}
}
System.out.println("Over");
FileOutputStream fileOutputStream = new FileOutputStream(PATH + "bigData07.xlsx");
workbook.write(fileOutputStream);
fileOutputStream.close();
long end = System.currentTimeMillis();
System.out.println((float) (end-begin)/1000);
}
Disadvantages: when writing data, the speed is very slow, which consumes a lot of memory, and memory overflow will also occur, such as 1 million
Advantages: it can write a large amount of data, such as 200000 pieces
Large file write SXSSF
@Test
public void ExcelBig3S() throws IOException {
//time
long begin = System.currentTimeMillis();
Workbook workbook = new SXSSFWorkbook();
Sheet sheet = workbook.createSheet();
for (int rowNum = 0; rowNum < 100000; rowNum++) {
Row row = sheet.createRow(rowNum);
for (int cellNum = 0; cellNum < 10; cellNum++) {
Cell cell = row.createCell(cellNum);
cell.setCellValue(cellNum);
}
}
System.out.println("Over");
FileOutputStream fileOutputStream = new FileOutputStream(PATH + "bigData07S.xlsx");
workbook.write(fileOutputStream);
fileOutputStream.close();
//Clear zero hour text
((SXSSFWorkbook)workbook).dispose();
long end = System.currentTimeMillis();
System.out.println((float) (end-begin)/1000);
}
Advantages: it can write a very large amount of data, such as 1 million or more, with fast data writing speed and less memory
be careful:
Temporary files will be generated during the process and need to be cleaned up
By default, 100 records are saved in memory. If this number is exceeded, the first data is written to the temporary file
If you want to customize the amount of data in memory, you can use new SXSSFWorkbook
SXSSFWorkbook comes to the official explanation: the streaming XSSFWorkbook version that implements the BigGridDemo policy. This allows very large files to be written without running out of memory, because only configurable line portions are saved in memory at any time.
Please note that a large amount of memory may still be consumed, which is based on the functions you are using, such as merging areas. Note... It is still only stored in memory, so if it is widely used, it may require a large amount of memory
POI excel read
Version 03
@Test
public void Read03Test() throws IOException {
FileInputStream inputStream = new FileInputStream(PATH + "poi_writebigData03.xls");
Workbook workbook = new HSSFWorkbook(inputStream);
//Get table
Sheet sheetAt = workbook.getSheetAt(0);
//Get line
Row row = sheetAt.getRow(0);
//Get column
Cell cell = row.getCell(1);
System.out.println(cell.getNumericCellValue());
inputStream.close();
}
07 version
@Test
public void Read07Test() throws IOException {
FileInputStream inputStream = new FileInputStream(PATH + "poi_writebigData07.xlsx");
Workbook workbook = new XSSFWorkbook(inputStream);
//Get table
Sheet sheetAt = workbook.getSheetAt(0);
//Get line
Row row = sheetAt.getRow(0);
//Get column
Cell cell = row.getCell(1);
System.out.println(cell.getNumericCellValue());
inputStream.close();
}
Note: get data type
Read different data types
@Test
public void test3() throws IOException {
//Get file stream
FileInputStream inputStream = new FileInputStream("D:\\Sogou high speed download\\Commodity information.xlsx");
//Create a workbook
Workbook workbook = new XSSFWorkbook(inputStream);
Sheet sheet = workbook.getSheetAt(0);
//Get title content
Row rowTitle = sheet.getRow(0);
if (rowTitle != null){
//How many columns of information are there
int cellCount = rowTitle.getPhysicalNumberOfCells();
for (int cellNum = 0; cellNum < cellCount; cellNum++) {
//Get the information of each column in the first row
Cell cell = rowTitle.getCell(cellNum);
if (cell != null){
int cellType = cell.getCellType();
String stringCellValue = cell.getStringCellValue();
System.out.print(stringCellValue+" | ");
}
}
System.out.println();
}
//Get the contents of the table
int rowCount = sheet.getPhysicalNumberOfRows();
for (int rowNum = 1; rowNum < rowCount; rowNum++) {
Row rowData = sheet.getRow(rowNum);
if (rowData != null){
//Read column
int cellCount = rowTitle.getPhysicalNumberOfCells();
for (int cellNum = 0; cellNum < cellCount; cellNum++) {
System.out.print("["+(rowNum+1)+"-"+(cellNum+1)+"]");
Cell cell = rowData.getCell(cellNum);
//Data type of matching column
if (cell != null){
int cellType = cell.getCellType();
String cellValue="";
switch (cellType){
case XSSFCell.CELL_TYPE_STRING://character string
System.out.println("[String]");
cellValue = cell.getStringCellValue();
break;
case XSSFCell.CELL_TYPE_BOOLEAN://Boolean
System.out.println("[Boolean]");
boolean booleanCellValue = cell.getBooleanCellValue();
cellValue = String.valueOf(booleanCellValue);
break;
case XSSFCell.CELL_TYPE_BLANK://empty
System.out.println("[BLANK]");
break;
case XSSFCell.CELL_TYPE_NUMERIC://number
System.out.println("[NUMERIC]");
if (HSSFDateUtil.isCellDateFormatted(cell)){//date
System.out.println("date");
Date dateCellValue = cell.getDateCellValue();
cellValue = new DateTime(dateCellValue).toString("yyyy-MM-dd hh:mm:ss");
}else {
//It is not a date format to prevent the number from being too long
System.out.println("Convert to string output");
cell.setCellType(XSSFCell.CELL_TYPE_STRING);
cellValue = cell.toString();
// double numericCellValue = cell.getNumericCellValue();
// cellValue = String.valueOf(numericCellValue);
}
break;
case XSSFCell.CELL_TYPE_ERROR://error
System.out.println("[Wrong data type]");
break;
}
System.out.println(cellValue);
}
}
}
}
inputStream.close();
}
Calculation formula
@Test
public void test4() throws IOException {
FileInputStream inputStream = new FileInputStream("D:\\C Application of disk\\newly build Microsoft Excel Worksheet.xlsx");
Workbook workbook = new XSSFWorkbook(inputStream);
Sheet sheet = workbook.getSheetAt(0);
Row row = sheet.getRow(5);
Cell cell = row.getCell(0);
//Take out the calculation formula eval
XSSFFormulaEvaluator formulaEvaluator = new XSSFFormulaEvaluator((XSSFWorkbook) workbook);
//Output cell contents
int cellType = cell.getCellType();
switch (cellType){
case Cell.CELL_TYPE_FORMULA://formula
String formula = cell.getCellFormula();
System.out.println(formula);
//calculation
CellValue evaluate = formulaEvaluator.evaluate(cell);
String value = evaluate.formatAsString();
System.out.println(value);
}
inputStream.close();
}
EasyExcel operation
Import dependency
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.2.10</version>
</dependency>
Write test
/**
* The simplest way to write
* <p>1. Create entity object reference corresponding to excel {@ link DemoData}
*/
@Test
public void simpleWrite() {
// Writing method 1
String fileName = PATH+"WriteTest.xlsx";
// Here, you need to specify which class to write, and then write to the first sheet with the name of template, and then the file stream will be closed automatically
//write(fileName, format class)
//Sheet (table name)
//doWriter (data)
EasyExcel.write(fileName, DemoData.class).sheet("Template").doWrite(data());
}
Read test
/**
* The simplest reading
* <p>1. Create entity object reference corresponding to excel {@ link DemoData}
* <p>2. Since excel is read line by line by default, you need to create a callback listener for Excel line by line. Refer to {@ link DemoDataListener}
* <p>3. Just read it directly
*/
@Test
public void simpleRead() {
// A very important point is that DemoDataListener cannot be managed by spring. It needs to read new every time excel is read, and then spring can be used to construct methods to pass it in
// Writing 1:
String fileName = PATH+"WriteTest.xlsx";
// Here, you need to specify which class to read, and then read the first sheet file. The stream will close automatically
EasyExcel.read(fileName, DemoData.class, new DemoDataListener()).sheet().doRead();
}
Fixed routine:
1. Write, fixed class format write
2. Read, read according to the rules set by the listener!