When exporting Excel with JavaPOI, we take into account the Excel version and the amount of data. Use different utility classes for different versions of Excel. HSSFWorkbook: is a version that operates before Excel2003. The extension is.xls. XSSFWorkbook: a version of Excel2007 with the extension. XLSX.

As anyone who has used POI knows, previous versions of the POI do not support handling large amounts of data, and it is not a good strategy to adjust JVM configuration parameters if there is an OOM error. The JDK supports no more than 2 gigabytes of memory on 32-bit systems, and there is no limit on 64-bit systems, but performance is not very good on 64-bit systems. However, SXSSFWorkbook supports only. XLSX format, not. XLS format Excel files.

For general purposes, when using HSSF objects in POI, Excel 2003 only allows to store 65536 pieces of data at most, which is generally used to process a small amount of data. At this time, excel certainly cannot accommodate millions of pieces of data, and it is easy to cause heap overflow when testing on a machine with lower computer performance. When I upgraded to an XSSF object, it directly supported excel2007 and above because it was in ooxml format. At this time, Excel can support 1,048,576 pieces of data, and a single sheet can support nearly 1.04 million pieces of data. Although exporting 1 million pieces of data can meet the requirements at this time, heap overflow still occurs occasionally after using XSSF test, so it is not suitable for exporting millions of pieces of data.

Now we know that Excel 2007 and above can easily store millions of levels of data, but how to quickly and accurately import a large amount of data in the system into Excel seems to be a difficult problem. For general Web systems, we basically use the entry level Web server Tomcat to solve the cost. Since we don’t recommend resizing the JVM, we’ll have to address the issues we need to address in our code. A new class, SXSSFWorkbook, was added after POI3.8 to control the amount of memory used by Excel data when processing objects that are not similar to previous versions. It manages resources by controlling the number of rows in memory, i.e., when creating objects that exceed the set number of rows, it automatically refreshes the memory. Write data to a file, so that when you print, you use very little CPU, very little memory. But someone will say, well, I’ve used this class before, and it doesn’t seem to be able to completely solve this problem, because when the amount of data exceeds a certain amount, it will still run out of memory for a long time. You’re using this class, but you’re not designing it for your needs, you’re just using it, so the next question I’m going to talk about is how to use SXSSFWorkbook and the corresponding write design to achieve millions of fast data writes.

V modified pom. XML

<dependency> <groupId>org.apache. Poi </groupId> <artifactId>poi</artifactId> <version>3.14</version> </dependency>Copy the code

V add controller

package com.demo.controller; import com.demo.pojo.UserDetails; import com.demo.service.UserService; import org.apache.poi.hssf.usermodel.*; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.stereotype.Controller; import org.springframework.web.bind.annotation.RequestMapping; import javax.servlet.http.HttpServletResponse; import java.io.IOException; import java.util.Date; import java.util.List; /** * Created by toutou on 2018/11/3. */ @Controller public class FileController { @Autowired UserService userService; @RequestMapping(value = "export") public void Export(HttpServletResponse response) throws IOException { HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = workbook.createsheet (" information sheet "); List<UserDetails> classmateList = userService.getUserDetails(); String fileName = "users" + new Date() + ".xls"; // Set the name of the file to be exported. Int rowNum = 1; String[] headers = {"id", "uid", "address "," city "}; // headers = {"id", "uid", "address "," city "} HSSFRow row = sheet.createRow(0); for(int i=0; i<headers.length; i++){ HSSFCell cell = row.createCell(i); HSSFRichTextString text = new HSSFRichTextString(headers[i]); cell.setCellValue(text); For (UserDetails item: classmateList) {HSSFRow row1 = sheet.createrow (rowNum); row1.createCell(0).setCellValue(item.getId()); row1.createCell(1).setCellValue(item.getUid()); row1.createCell(2).setCellValue(item.getAddress()); row1.createCell(3).setCellValue(item.getCity()); rowNum++; } response.setContentType("application/octet-stream"); response.setHeader("Content-disposition", "attachment; filename=" + fileName); response.flushBuffer(); workbook.write(response.getOutputStream()); }}Copy the code

V Running and debugging

V Source code address

Github.com/toutouge/ja…

Other references:

  • HSSFWorkbook API Documentation (POI) poi.apache.org/apidocs/dev…
  • What do you export million level data blog.csdn.net/happyljw/ar JAVA USES POI…

About the author: Focus on basic platform project development. If you have any questions or suggestions, please feel free to comment! Copyright notice: The copyright of this article belongs to the author and the blog garden, welcome to reprint, but without the consent of the author must retain this statement, and give the original text link in a prominent place on the page of the article. For the record: all comments and messages will be answered as soon as possible. You are welcome to correct your mistakes and make progress together. Or direct private message I support the blogger: if you think the article is helpful to you, you can click on the lower right corner of the article [recommendation]. Your encouragement is the author to adhere to the original and continuous writing of the biggest power! \