Getting started with POI and easyExcel

Use scenarios and versions

Use scenarios

Export information to excel
Enter information from excel table into website database (exercise upload.)

excel version

Version 03 xls rows Max 65535 rows
Version 07 xlsx has no limit on the number of rows

One serious problem with POI is that it consumes a lot of memory

EasyExcel is an open source excel processing framework for Alibaba known for its simplicity and memory saving

EasyExcel can significantly reduce memory usage, mainly because file data is not fully loaded into memory at one time when parsing Excel, but read data from a row on disk and parse it one by one

POI

rely on

<!--xLs(03)-->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version >3.17</version>
</dependency>
<!--xLsx(07)-->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>3.17</version>
</dependency>
<!--Date Formatting Tool-->
<dependency>
    <groupId>joda-time</groupId>
    <artifactId>joda-time</artifactId>
    <version>2.9.9</version>
</dependency>

03 and 07

The difference is 03 is HSSF, 07 is XSSF

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.joda.time.DateTime;

import java.io.FileOutputStream;
import java.io.IOException;

public class ExcelTest {

    public static void test03() throws IOException {
        // 03
        // workbook
        Workbook workbook = new HSSFWorkbook();
        // Create a worksheet
        Sheet sheet = workbook.createSheet("Statistical table");
        // That's ok
        Row row1 = sheet.createRow(0);
        // column
        Cell cell1 = row1.createCell(0);
        cell1.setCellValue("Number of people");
        Cell cell2 = row1.createCell(1);
        cell2.setCellValue("time");

        Row row2 = sheet.createRow(1);
        Cell row2Cell1 = row2.createCell(0);
        row2Cell1 .setCellValue("666");
        Cell row2Cell2 = row2.createCell(1);
        row2Cell2.setCellValue(new DateTime().toString("yyyy-MM-dd HH:mm:ss"));

        FileOutputStream fileOutputStream = new FileOutputStream("D:\\Download\\" + sheet.getSheetName() + "03.xls");
        workbook.write(fileOutputStream);
        fileOutputStream.close();
        System.out.println("Success");
    }


    public static void test07() throws IOException {
        // 07
        // workbook
        Workbook workbook = new XSSFWorkbook();
        // Create a worksheet
        Sheet sheet = workbook.createSheet("Statistical table");
        // That's ok
        Row row1 = sheet.createRow(0);
        // column
        Cell cell1 = row1.createCell(0);
        cell1.setCellValue("Number of people");
        Cell cell2 = row1.createCell(1);
        cell2.setCellValue("time");

        Row row2 = sheet.createRow(1);
        Cell row2Cell1 = row2.createCell(0);
        row2Cell1 .setCellValue("666");
        Cell row2Cell2 = row2.createCell(1);
        row2Cell2.setCellValue(new DateTime().toString("yyyy-MM-dd HH:mm:ss"));

        FileOutputStream fileOutputStream = new FileOutputStream("D:\\Download\\" + sheet.getSheetName() + "07.xlsx");
        workbook.write(fileOutputStream);
        fileOutputStream.close();
        System.out.println("Success");
    }


    public static void main(String[] args) throws Exception {
        test03();
        test07();
    }

}

Large file write HSSF
Disadvantage: Only 65536 rows can be processed, otherwise an exception will be reported
Advantages: write to cache during the process, do not operate the disk, write to disk for the last time, fast

@Test
    public void test1() throws Exception {
        //time
        long begin = System.currentTimeMillis();
        //Create a thin
        Workbook workbook = new HSSFWorkbook();
        //Create Table
        Sheet sheet = workbook.createSheet();
        //Write data
        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);
            }
        }
        FileOutputStream fos = new FileOutputStream("D:\\Download\\" + "03 Edition Excel Lots of data testing.xls");
        workbook.write(fos);
        fos.close();
        System.out.println("over");
        long end = System.currentTimeMillis();
        System.out.println((double) (end - begin) / 1000);

    }

Upper File Write XSSF

Disadvantages: Writing data is very slow and memory overflows can occur, such as 1 million pieces of data
Advantages: You can write a large amount of data, such as 200,000 pieces of data

@Test
    public void test2() throws Exception {
        //time
        long begin = System.currentTimeMillis();
        //Create a thin
        Workbook workbook = new XSSFWorkbook();
        //Create Table
        Sheet sheet = workbook.createSheet();
        //Write data
        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);
            }
        }
        FileOutputStream fos = new FileOutputStream("D:\\Download\\" + "07 Edition Excel Lots of data testing.xlsx");
        workbook.write(fos);
        fos.close();
        System.out.println("over");
        long end = System.currentTimeMillis();
        System.out.println((double) (end - begin) / 1000);

    }

Upper File Write SXSSF

Advantages: can write a very large number of databases, such as 1 million or more, write data quickly, occupy less memory
Be careful:

Temporary files are generated and need to be cleaned up
By default, 100 records are saved in memory, and if this number is exceeded, the first data is written to a temporary file
If you want to customize the amount of data in memory, you can use new SXSSFWorkbook.

@Test
    public void testwrite07_S_BigData() throws IOException {
        //time
        long begin = System.currentTimeMillis();
        //Create a thin
        Workbook workbook = new SXSSFWorkbook(100);
        //Create Table
        Sheet sheet = workbook.createSheet();
        //Write data
        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);
            }
        }
        FileOutputStream fos = new FileOutputStream("D:\\Download\\" + "07_S_Edition Excel Lots of data testing.xlsx");
        workbook.write(fos);
        fos.close();
        //Clear Temporary Cache
        ((SXSSFworkbook.dispose();
        System.out.println("over");
        long end = System.currentTimeMillis();
        System.out.println((double) (end - begin) / 1000);
    }

03 Read

@Test
    public void testSpace() throws IOException{
        FileInputStream fileInputStream = new FileInputStream("D:\\Download\\07_S_Edition Excel Lots of data testing.xlsx");
        Workbook workbook = new HSSFWorkbook(fileInputStream);
        Sheet sheet = workbook.getSheetAt(0);
        Row row = sheet.getRow(0);
        Cell cell = row.getCell(0);
        String value = cell.getStringCellValue();
        System.out.println(value);
        fileInputStream.close();
    }

Note the data type

07 Read

@Test
    public void testSpace() throws IOException{
        FileInputStream fileInputStream = new FileInputStream("D:\\Download\\07_S_Edition Excel Lots of data testing.xlsx");
        Workbook workbook = new XSSFWorkbook(fileInputStream);
        Sheet sheet = workbook.getSheetAt(0);
        Row row = sheet.getRow(0);
        Cell cell = row.getCell(0);
        String value = cell.getStringCellValue();
        System.out.println(value);
        fileInputStream.close();
    }

Reading data of different data types

@Test
    public void testType() throws Exception {
        FileInputStream fileInputStream = new FileInputStream("D:\\Download\\07_S_Edition Excel Lots of data testing.xlsx");
        Workbook workbook = new XSSFWorkbook(fileInputStream);
        Sheet sheet1 = workbook.getSheetAt(0);
        Row row1 = sheet1.getRow(0);

        if (!Objects.isNull(row1)) {
            int cellCounts = row1.getPhysicalNumberOfCells();
            for (int i = 0; i < cellCounts; i++) {
                Optional.ofNullable(row1.getCell(i)).ifPresent(cell -> {
                    cell.getCellTypeEnum();
                    System.out.print(cell.getStringCellValue() + " | ");
                });
            }
            System.out.println();
        }
        int rowCounts = sheet1.getPhysicalNumberOfRows();
        for (int i = 1; i < rowCounts; i++) {
            Row row2 = sheet1.getRow(i);
            Optional.ofNullable(row2).ifPresent(row -> {
                int cellCount = row.getPhysicalNumberOfCells();
                for (int j = 0; j < cellCount; j++) {
                    Cell cell = row2.getCell(j);
                    // Processing data types
                    CellType cellTypeEnum = cell.getCellTypeEnum();
                    switch (cellTypeEnum) {
                        case STRING:
                            System.out.print(cell.getStringCellValue() + " ");
                            break;
                        case BOOLEAN:
                            System.out.print(cell.getBooleanCellValue() + " ");
                            break;
                        case NUMERIC:
                            // Integer, decimal or date
                            if (DateUtil.isCellDateFormatted(cell)) {
                                System.out.print(new DateTime(cell.getDateCellValue()).toString("yyyy-MM-dd") + " ");
                            } else {
                                cell.setCellType(CellType.STRING);
                                System.out.println(cell.toString());
                            }
                            break;
                        case FORMULA:
                            System.out.print(cell.getCellFormula() + " ");
                            break;
                        case _NONE:
                            System.out.print("No value but possible cell style  ");
                            break;
                        case ERROR:
                            System.out.print("Data type error  ");
                            break;
                    }
                }
            });


        }
        fileInputStream.close();
    }

There is a problem with the accuracy of the above decimals, and the formula does not have a calculated value

CellType

_NONE(-1), // Unknown type, for internal use only
NUMERIC(0), // Integer decimal date
STRING(1),// Character string
FORMULA(2),// formula
BLANK(3),// Empty Cell - No value, but with cell style
BOOLEAN(4),// Boolean
ERROR(5);// Error Cell

Calculation Formula

easyExcel

easyExcel website:

https://github.com/alibaba/easyexcel

https://alibaba-easyexcel.github.io/quickstart/write.html

rely on

poi and easyexcel conflict, only one can be imported

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>fastjson</artifactId>
    <version >1.2.62</version>
</dependency>

<!--easyexcel-->
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version >2.2.6</version>
</dependency>
<!--lombok-->
<dependency>
    <groupId>org.projectlombok</groupId>
    <artifactId>lombok</artifactId>
    <version >1.18.12</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.12</version>
</dependency>

@Data
public class DemoData {
    @ExcelProperty("String Title")
    private String string;
    
    @ExcelProperty("Date Title")
    private Date date;
    
    @ExcelProperty("Number Title")
    private Double doubleData;

    //Ignore this field
    @ExcelIgnore
    private String ignore;
}

public class EasyExcelTest {

        String PATH = "D:\\";

        //Analog Write Data
        private List<DemoData> data() {
            java.util.List<DemoData> list = new ArrayList<DemoData>();
            for (int i = 0; i < 10; i++) {
                DemoData data = new DemoData();
                data.setString("Character string" + i);
                data.setDate(new Date());
                data.setDoubleData(0.56);
                list.add(data);
            }
            return list;
        }

        //Write excel based on ist
        @Test
        public void simplewrite() {
            String fileName = this.PATH + "EasyTest.xlsx";
            //Here you need to specify which class to write to, then write to the first sheet, named Template, and the file stream will automatically close
            //write(fileName, format class)
            //sheet (table name)
            //doWrite (data)
            EasyExcel.write(fileName, DemoData.class).sheet("Template").doWrite(this.data());
        }

}

For more details, just look at easyExcel's official website

Posted by kwilder on Thu, 04 Nov 2021 14:29:56 -0700

Programmer Group

Getting started with POI and easyExcel

Use scenarios and versions

Use scenarios

excel version

POI

rely on

03 and 07

Large file write HSSF

Upper File Write XSSF

Upper File Write SXSSF

03 Read

07 Read

Reading data of different data types

Calculation Formula

easyExcel

rely on

Hot Keywords