java operation Excel -- from poi to EasyExcel

premise

If you want to operate an excel form, you have to figure out these things

  1. Workbook, 2. Worksheet, 3. Row, 4. Column

    Secondly, you need to make sure that there are two versions of Excel, version 03 and version 07. The biggest difference between them is that the extension name (suffix) is different. Version 03 is xls and version 07 is xlsx. Another difference is that the number of rows that can be accommodated by two versions is also different
    Version 03 is 65536 lines

    The 07 edition is theoretically infinite

    All right, we can start to work after we find out.

Create a normal maven project

stay pom.xml Add dependency inside

	<dependency>
<!--        xls03 edition-->
        <groupId>org.apache.poi</groupId>
        <artifactId>poi</artifactId>
        <version>3.9</version>
    </dependency>
<!--    xls07 edition-->
    <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>org.junit.jupiter</groupId>
        <artifactId>junit-jupiter</artifactId>
        <version>RELEASE</version>
        <scope>compile</scope>
    </dependency>

Write data to Excel

Explain:
HSSF is used to create workbooks in version 03 and XSSF is used in version 07
Version 03:

//File path
	private String path = "E:\\sa-poi\\src\\";
    @Test
    public void write03() throws IOException {
//        Create Workbook
        Workbook workbook03 = new HSSFWorkbook();
//        Create sheet
        Sheet sheet = workbook03.createSheet("Table 1");
//        Create row
        Row row = sheet.createRow(0);
//        Create column
        Cell cell = row.createCell(0);//(0,0)
        cell.setCellValue("full name");
        Cell cell1 = row.createCell(1);//(0,1)
        cell1.setCellValue("Age");
        Cell cell2 = row.createCell(2);
        cell2.setCellValue("birthday");

//Create table (IO stream), 03 version suffix is. xls
        FileOutputStream fileOutputStream = new FileOutputStream(path + "Information sheet.xls");
        workbook03.write(fileOutputStream);
        fileOutputStream.close();
        System.out.println("File generation complete");
    }

Edition 07

@Test
    public void write07() throws IOException {
//        Create Workbook
        Workbook workbook03 = new XSSFWorkbook();
//        Create sheet
        Sheet sheet = workbook03.createSheet("Table 1");
//        Create row
        Row row = sheet.createRow(0);
//        Create column
        Cell cell = row.createCell(0);//(0,0)
        cell.setCellValue("full name");
        Cell cell1 = row.createCell(1);//(0,1)
        cell1.setCellValue("Age");
        Cell cell2 = row.createCell(2);
        cell2.setCellValue("birthday");

//Create table (IO stream), 03 version suffix is. xls
        FileOutputStream fileOutputStream = new FileOutputStream(path + "Information sheet.xlsx");
        workbook03.write(fileOutputStream);
        fileOutputStream.close();
        System.out.println("File generation complete");
    }

Run our test code, result:
Two Excel files created successfully

Open it and you can see that the contents of the two files are the same except for the suffix

Differences between HSSF and XSSF

HSSF operates version 03, so it can only process 65536 lines of data at most, and throw an exception if there are too many java.lang.IllegalArgumentException : invalid row number (65536) outside allowed range (0.. 65536), but XSSF does not have this problem.
HSSF is to read all the data into the cache first, and then write them to the disk (file) at one time, which is more efficient.
XSSF consumes a lot of memory, has low efficiency, and has the risk of memory leakage, but it can operate more data rows than HSSF

performance comparison

HSSF

@Test
    public void bigData03Test() throws IOException {
        String path = "E:\\sa-poi\\src\\";
        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 < 1; cellNum++) {
                Cell cell = row.createCell(cellNum);
                cell.setCellValue(rowNum);
            }
        }
        FileOutputStream fileOutputStream = new FileOutputStream(path + "bigData03.xls");
        workbook.write(fileOutputStream);
        fileOutputStream.close();
        System.out.println("Cost"+(System.currentTimeMillis()-begin)+"ms");
    }

Operation results

XSSF

@Test
    public void bigData07Test() throws IOException {
        String path = "E:\\sa-poi\\src\\";
        long begin = System.currentTimeMillis();
        Workbook workbook = new XSSFWorkbook();
        Sheet sheet = workbook.createSheet();
        for (int rowNum = 0; rowNum < 65536; rowNum++) {
            Row row = sheet.createRow(rowNum);
            for (int cellNum = 0; cellNum < 1; cellNum++) {
                Cell cell = row.createCell(cellNum);
                cell.setCellValue(rowNum);
            }
        }
        FileOutputStream fileOutputStream = new FileOutputStream(path + "bigData03.xlsx");
        workbook.write(fileOutputStream);
        fileOutputStream.close();
        System.out.println("Cost"+(System.currentTimeMillis()-begin)+"ms");
    }

Operation results

XSSF upgrade SXSSF

As an upgraded version of XSSF, SXSSF combines the high efficiency of HSSF and the ability of XSSF to operate a large amount of data, but generates temporary files

@Test
    public void bigData07TestS() throws IOException {
        String path = "E:\\sa-poi\\src\\";
        long begin = System.currentTimeMillis();
        Workbook workbook = new SXSSFWorkbook();
        Sheet sheet = workbook.createSheet();
        for (int rowNum = 0; rowNum < 65536; rowNum++) {
            Row row = sheet.createRow(rowNum);
            for (int cellNum = 0; cellNum < 1; cellNum++) {
                Cell cell = row.createCell(cellNum);
                cell.setCellValue(rowNum);
            }
        }
        FileOutputStream fileOutputStream = new FileOutputStream(path + "bigData03S.xlsx");
        workbook.write(fileOutputStream);
        fileOutputStream.close();
//        SXSSF will generate temporary files, to be cleaned up
        ((SXSSFWorkbook) workbook).dispose();

        System.out.println("Cost"+(System.currentTimeMillis()-begin)+"ms");
    }

Operation results

Read Excel

Version 03, when reading data, it must be read by data type, otherwise an exception will be thrown

@Test
    public void read03() throws IOException {
//        Get file input stream
        FileInputStream fileInputStream = new FileInputStream(path + "Information sheet.xls");
        //  1. Get a workbook from the file stream
        Workbook workbook = new HSSFWorkbook(fileInputStream);
//        Get worksheet
        Sheet sheet = workbook.getSheetAt(0);
//        Get field name (first line)
        Row rowTitle = sheet.getRow(0);
        if (rowTitle != null) {
            int cellCount = rowTitle.getPhysicalNumberOfCells();
            for (int cellNum = 0; cellNum < cellCount; cellNum++) {
                Cell cell = rowTitle.getCell(cellNum);
                if (cell != null) {
                    System.out.print(cell.getStringCellValue() + " | ");
                }
            }
            System.out.println();
        }
//        Get total rows
        int rowCount = sheet.getPhysicalNumberOfRows();
        for (int rowNum = 1; rowNum < rowCount; rowNum++) {
            Row row = sheet.getRow(rowNum);
            if (row != null) {
//                Get total columns
                int cellCount = row.getPhysicalNumberOfCells();
                for (int cellNum = 0; cellNum < cellCount; cellNum++) {
                    Cell cell = row.getCell(cellNum);
                    if (cell != null) {
                        //Get cell data type
                        int cellType = cell.getCellType();
                        String cellValue = "";
                        switch (cellType) {
                            case HSSFCell.CELL_TYPE_STRING:   //String type
                                cellValue = cell.getStringCellValue();
                                break;
                            case HSSFCell.CELL_TYPE_NUMERIC:  //Number, date
                                //If it's a date
                                if (HSSFDateUtil.isCellDateFormatted(cell)) {
                                    Date date = cell.getDateCellValue();
                                    cellValue = new DateTime(date).toString("yyyy-MM-dd");
                                } else {
                                    //Convert to string type if not date
                                    cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                                    cellValue = cell.toString();
                                }
                                break;
                            case HSSFCell.CELL_TYPE_BOOLEAN:   //Boolean type
                                cellValue = String.valueOf(cell.getBooleanCellValue());
                                break;
                            case HSSFCell.CELL_TYPE_BLANK:     //Empty
                                cellValue = "nothing";
                                break;
                            case HSSFCell.CELL_TYPE_ERROR:     //Wrong data type
                                System.out.println("Wrong data type");
                                cellValue = "Wrong data type";
                                break;
                        }
                        System.out.print(cellValue + " | ");
                    }

                }
                System.out.println();
            }
        }
//        Close flow
        fileInputStream.close();
    }

excel table

Operation results

Encapsulate the method of reading Excel as a tool

public void read03(FileInputStream fileInputStream) throws IOException {
        //  1. Get a workbook from the file stream
        Workbook workbook = new HSSFWorkbook(fileInputStream);
//        Get worksheet
        Sheet sheet = workbook.getSheetAt(0);
//        Get field name (first line)
        Row rowTitle = sheet.getRow(0);
        if (rowTitle != null) {
            int cellCount = rowTitle.getPhysicalNumberOfCells();
            for (int cellNum = 0; cellNum < cellCount; cellNum++) {
                Cell cell = rowTitle.getCell(cellNum);
                if (cell != null) {
                    System.out.print(cell.getStringCellValue() + " | ");
                }
            }
            System.out.println();
        }
//        Get total rows
        int rowCount = sheet.getPhysicalNumberOfRows();
        for (int rowNum = 1; rowNum < rowCount; rowNum++) {
            Row row = sheet.getRow(rowNum);
            if (row != null) {
//                Get total columns
                int cellCount = row.getPhysicalNumberOfCells();
                for (int cellNum = 0; cellNum < cellCount; cellNum++) {
                    Cell cell = row.getCell(cellNum);
                    if (cell != null) {
                        //Get cell data type
                        int cellType = cell.getCellType();
                        String cellValue = "";
                        switch (cellType) {
                            case HSSFCell.CELL_TYPE_STRING:   //String type
                                cellValue = cell.getStringCellValue();
                                break;
                            case HSSFCell.CELL_TYPE_NUMERIC:  //Number, date
                                //If it's a date
                                if (HSSFDateUtil.isCellDateFormatted(cell)) {
                                    Date date = cell.getDateCellValue();
                                    cellValue = new DateTime(date).toString("yyyy-MM-dd");
                                } else {
                                    //Convert to string type if not date
                                    cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                                    cellValue = cell.toString();
                                }
                                break;
                            case HSSFCell.CELL_TYPE_BOOLEAN:   //Boolean type
                                cellValue = String.valueOf(cell.getBooleanCellValue());
                                break;
                            case HSSFCell.CELL_TYPE_BLANK:     //Empty
                                cellValue = "nothing";
                                break;
                            case HSSFCell.CELL_TYPE_ERROR:     //Wrong data type
                                System.out.println("Wrong data type");
                                cellValue = "Wrong data type";
                                break;
                        }
                        System.out.print(cellValue + " | ");
                    }

                }
                System.out.println();
            }
        }
    }

Now you just need to send in a file input stream to complete the output
Version 07 is similar, so we will not repeat it here

EasyExcel

EasyExcel is an open source framework developed by Ali. It is a simple, memory saving read-write Excel open source project based on Java. In the case of saving memory as much as possible, it supports reading and writing 100M Excel.
github address: https://github.com/alibaba/easyexcel
Introduce dependency

	<dependency>
        <groupId>com.alibaba</groupId>
        <artifactId>easyexcel</artifactId>
        <version>2.2.3</version>
    </dependency>

Click artifact ID, you will find that there are many dependencies in it, such as poi, poi OOXML, slf4j API, etc., so we need to return to our own pom.xml Note out the repeated dependency first to avoid unnecessary dependency conflicts

	<dependencies>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.17</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.17</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml-schemas</artifactId>
            <version>3.17</version>
        </dependency>
        <dependency>
            <groupId>cglib</groupId>
            <artifactId>cglib</artifactId>
            <version>3.1</version>
        </dependency>
        <dependency>
            <groupId>org.slf4j</groupId>
            <artifactId>slf4j-api</artifactId>
            <version>1.7.26</version>
        </dependency>
        <dependency>
            <groupId>org.ehcache</groupId>
            <artifactId>ehcache</artifactId>
            <version>3.4.0</version>
        </dependency>
        <!--test-->
        <dependency>
            <groupId>ch.qos.logback</groupId>
            <artifactId>logback-classic</artifactId>
            <version>1.2.3</version>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>fastjson</artifactId>
            <version>1.2.58</version>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.8</version>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot</artifactId>
            <version>1.5.21.RELEASE</version>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
            <version>1.5.21.RELEASE</version>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
            <scope>test</scope>
        </dependency>
    </dependencies>
    <distributionManagement>
        <snapshotRepository>
            <id>ossrh</id>
            <url>https://oss.sonatype.org/content/repositories/snapshots</url>
        </snapshotRepository>
        <repository>
            <id>ossrh</id>
            <url>https://oss.sonatype.org/service/local/staging/deploy/maven2/</url>
        </repository>
    </distributionManagement>

    <build>
        <plugins>
            <!-- code style -->
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-pmd-plugin</artifactId>
                <version>3.8</version>
                <configuration>
                    <printFailingErrors>true</printFailingErrors>
                    <verbose>true</verbose>
                    <rulesets>
                        <ruleset>rulesets/java/ali-comment.xml</ruleset>
                        <ruleset>rulesets/java/ali-concurrent.xml</ruleset>
                        <ruleset>rulesets/java/ali-constant.xml</ruleset>
                        <ruleset>rulesets/java/ali-exception.xml</ruleset>
                        <ruleset>rulesets/java/ali-flowcontrol.xml</ruleset>
                        <ruleset>rulesets/java/ali-naming.xml</ruleset>
                        <ruleset>rulesets/java/ali-oop.xml</ruleset>
                        <ruleset>rulesets/java/ali-orm.xml</ruleset>
                        <ruleset>rulesets/java/ali-other.xml</ruleset>
                        <ruleset>rulesets/java/ali-set.xml</ruleset>
                    </rulesets>
                    <excludes>
                        <exclude>com/alibaba/excel/event/AnalysisEventListener.java</exclude>
                        <exclude>com/alibaba/excel/metadata/DataFormatter.java</exclude>
                        <exclude>com/alibaba/excel/util/DateUtils.java</exclude>
                    </excludes>
                </configuration>
                <executions>
                    <execution>
                        <id>pmd-check-verify</id>
                        <phase>validate</phase>
                        <goals>
                            <goal>check</goal>
                        </goals>
                    </execution>
                </executions>
                <dependencies>
                    <dependency>
                        <groupId>com.alibaba.p3c</groupId>
                        <artifactId>p3c-pmd</artifactId>
                        <version>1.3.6</version>
                    </dependency>
                </dependencies>

Create entity class

Follow the official document and create an entity class first

@Data
public class DemoData {
    @ExcelProperty("String title")
    private String string;
    @ExcelProperty("Date title")
    private Date date;
    @ExcelProperty("Digital title")
    private Double doubleData;
    /**
     * Ignore this field
     */
    @ExcelIgnore
    private String ignore;
}

Create a method to generate data

private List<DemoData> data() {
        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 in

 public void simpleWrite() {
        String path = "E:\\sa-poi\\src\\";
        // Style 1
        String fileName = path+"easyExcel.xlsx";
        // Here, you need to specify which class to write to, and then write to the first sheet, whose name is template, and then the file stream will be automatically closed
        // If you want to use 03 here, just pass in the excelType parameter
        EasyExcel.write(fileName, DemoData.class).sheet("Template").doWrite(data());
  }

Operation results

Writing method 2

@Test
    public void simpleWrite() {
        String path = "E:\\sa-poi\\src\\";

        String fileName = path+"easyExcel.xlsx";
        
        // You need to specify which class to write in
        ExcelWriter excelWriter = EasyExcel.write(fileName, DemoData.class).build();
        WriteSheet writeSheet = EasyExcel.writerSheet("Template").build();
        excelWriter.write(data(), writeSheet);
        // Don't forget that finish will help close the flow
       excelWriter.finish();
    }

read

  1. Did you create entity class? It has been created above
  2. dao layer
**
 * Suppose this is yours DAO Storage. Of course, we need this class to let spring Management, of course, you don't need storage or this class.
 **/
public class DemoDAO {
    public void save(List<DemoData> list) {
        // If it is mybatis, try not to directly call multiple inserts, write a new method batchInsert in a mapper, and insert all data at once
    }
}
  1. Listener (reflection Implementation)
// There is a very important point that DemoDataListener cannot be managed by spring. You need new every time you read excel, and then you can use spring to construct methods and pass them in
public class DemoDataListener extends AnalysisEventListener<DemoData> {
    private static final Logger LOGGER = LoggerFactory.getLogger(DemoDataListener.class);
    /**
     * Every five databases can be stored. In practice, 3000 databases can be used, and then the list can be cleaned to facilitate memory recycling
     */
    private static final int BATCH_COUNT = 5;
    List<DemoData> list = new ArrayList<DemoData>();
    /**
     * Suppose this is a DAO. Of course, if there is business logic, this can also be a service. Of course, it's no use not storing this object.
     */
    private DemoDAO demoDAO;
    public DemoDataListener() {
        // This is the demo, so any new one. Actual use if spring is reached, please use the following parametered constructor
        demoDAO = new DemoDAO();
    }
    /**
     * If spring is used, use this construction method. Every time you create a Listener, you need to pass in the spring managed classes
     *
     * @param demoDAO
     */
    public DemoDataListener(DemoDAO demoDAO) {
        this.demoDAO = demoDAO;
    }
    /**
     * Every data parsing will call
     *
     * @param data
     *            one row value. Is is same as {@link AnalysisContext#readRowHolder()}
     * @param context
     */
    @Override
    public void invoke(DemoData data, AnalysisContext context) {
        System.out.println(JSON.toJSONString(data));
        list.add(data);
        // Reach BATCH_COUNT, need to store the database once, to prevent tens of thousands of data in memory, easy OOM
        if (list.size() >= BATCH_COUNT) {
            saveData();
            // Storage complete cleanup list
            list.clear();
        }
    }
    /**
     * When all data analysis is completed, it will be called
     *
     * @param context
     */
    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        // Data should also be saved here to ensure that the last legacy data is also stored in the database
        saveData();
        LOGGER.info("All data analysis completed!");
    }
    /**
     * Plus storage database
     */
    private void saveData() {
        LOGGER.info("{}Data, start to store database!", list.size());
        demoDAO.save(list);
        LOGGER.info("Storage database succeeded!");
    }


}
  1. Read data
**
     * The simplest reading
     * <p>1. establish excel Corresponding entity object reference{@link DemoData}
     */
    @Test
    public void simpleRead() {
        // There is a very important point that DemoDataListener cannot be managed by spring. You need new every time you read excel, and then you can use spring to construct methods and pass them in
        String path = "E:\\sa-poi\\src\\";
        // Style 1
        String fileName = path+"easyExcel.xlsx";
        // Here, you need to specify which class to use for reading, and then the first sheet file stream will be automatically closed after reading
        EasyExcel.read(fileName, DemoData.class, new DemoDataListener()).sheet().doRead();
    }

Operation results

It will prompt the exception of sl4j, regardless of it. It should be because we have not configured his file, and we need to use sout for output

Well, that's how excel works in java

Tags: Excel Java xml Spring

Posted on Sun, 07 Jun 2020 01:03:29 -0400 by turbocueca