Exporting reports displayed on Web pages to Excel files is a common requirement. However, the problem of Excel itself supporting up to 65535 lines of data becomes apparent when the data volume is large. The following is a solution for exporting large data to Excel.
First of all, for data more than 65535 lines of the problem, it is natural to think of the whole data block, the use of Excel multi-sheet page function, will exceed 65535 lines of data written to the next sheet page, that is, through the way of multi-sheet page, break through the maximum 65535 lines of data limit.
Specific approach is to do a separate link, using JSP export, in JSP through the program to judge the number of lines, more than 65535 lines after the SHEET write. That would solve the problem.
Furthermore, in the case of generating and exporting reports with large amounts of data, a large amount of memory is used, especially in the case of TOMCAT, the JVM can only support up to 2G memory, and memory overflow will occur. At this time, the memory overhead is mainly two parts, one is the overhead when the report is generated, the other is the overhead when the report is generated and an EXCEL is written. Since the JVM’s GC mechanism does not enforce collection, we offer a workaround for this situation.
First, set the start row and end row parameters of the report. During the API report generation, the report is calculated step by step (the main performance cost is in the query report generation). For example, a report with 200,000 rows of data can be generated four to five times through the start row and end row. In this way, the memory footprint of report generation is reduced. During the later report generation, if the memory is found to be insufficient, the JVM’s GC mechanism can be automatically started to reclaim the memory of the previous report.
The process of exporting EXCEL is carried out immediately after the report generation in each section. Multiple sheets are changed into multiple EXCEL pages. That is, EXCEL is generated step by step while the report is generated step by step, so the memory consumption of EXCEL generation through POI package is also reduced. Through multiple builds, you can also effectively reclaim the memory occupied by the previous EXCEL generation when the memory needed for the later EXCEL generation is insufficient.
Then file operation is used to generate a unique temporary directory on the server side according to SESSIONID and login time for each client’s export request, which is used to place the generated multiple EXCEL files. Then system console is used to package multiple EXCEL files into RAR or JAR mode, and finally a RAR package or JAR package is fed back to the user. In response to the client request, the console is called again to delete the temporary directory.
Using this method, first of all, through segmenting operation and generation, effectively reduces the memory overhead of the report from generating results to generating EXCEL. Secondly, through the use of compressed package, the volume of generated files for users is greatly reduced, reducing the burden of downloading files on the server when multiple users concurrently access, effectively reducing the traffic on the server when multiple users export and download, so as to further reduce the load on the server.
- Create a system global thread pool
final int numOfCpuCores = Runtime.getRuntime().availableProcessors(); Final double blockingCoefficient = 0.9; Final int maximumPoolSize = (int)(numOfCpuCores/(1-Blockingcoefficient)); ExecutorService threadPool = new ThreadPoolExecutor(numOfCpuCores, maximumPoolSize, 0L, TimeUnit.MILLISECONDS, new LinkedBlockingQueue <Runnable>(), Executors.privilegedThreadFactory(), new ThreadPoolExecutor.DiscardOldestPolicy());Copy the code- Multi-thread segmented query is used to generate reports, Excel is generated synchronously, and Zip files are compressed at last
// 1. Here, 60,000 pieces of data (divided into 6 sheets, 10,000 pieces for each) are placed in each Excel. When the amount of data exceeds 60,000 pieces, the data is segmented to query // transfer (start line, end line) parameters. Int SINGLE_EXCEPORT_EXCEL_MAX_NUM = 60000; int count = bo.getTotalRecord(); final String fileNameWithTimestamp = fileName +"_" + DateUtil.getNowDateminStr();
if(count > SINGLE_EXCEPORT_EXCEL_MAX_NUM ) { int excelCount = count / SINGLE_EXCEPORT_EXCEL_MAX_NUM + (count % SINGLE_EXCEPORT_EXCEL_MAX_NUM ! = 0? 1:0); final CountDownLatch latch = new CountDownLatch(excelCount); final Long userId = user.getUserId();for(int i = 1; i <= excelCount; i++){ bo.setPageNo(i); bo.setPageSize(SINGLE_EXCEPORT_EXCEL_MAX_NUM); final ParkRecordQryBO itemBo = new ParkRecordQryBO(bo); final int index = i; Threadpool.execute (new)Runnable(){
@Override
public void run() { Page page = service.getParkRecord(itemBo); List<ParkRecordQryBO> records = page.getResults(); Try {/ / 2. Generate single excel ExportExcelUtil. CreateOneExcel (fileNameWithTimestamp, index, expRowsList, records, userId); } catch (Exception e) { e.printStackTrace(); } latch.countDown(); }}); } // 3. Zip the excel file and export latch.await(); ExportExcelUtil.createZipExport(request, response, fileNameWithTimestamp, userId);Copy the code- Generate an Excel file and store it in the local directory
/** * @Description: * @param fileNameWithTimestamp * @param index * @param excelHeader * @param dataList * @param <T> * @param userId */ public static <T> void createOneExcel(final String fileNameWithTimestamp, int index, final String[] excelHeader, final List<T> dataList, Long userId ) { final StringlocalRelativePath = "" + userId + "/"+ fileNameWithTimestamp ; Workbook wb = null; FileOutputStream fos = null; // Create a Workbook with an Excel file wb = writeExcel(dataList, excelHeader); FileInfo = new HashMap<String, Object>(); // Generate local Excel initial file Map<String, Object> fileInfo = new HashMap<String, Object>(); FileUtil.createFile(localRelativePath, fileNameWithTimestamp +
"_" + index + "_.xls", fileInfo);
fos = new FileOutputStream(fileInfo.get("realPath").toString() );
wb.write(fos);
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
if(wb ! = null) wb.close();if(fos ! = null) fos.close(); } catch (IOException e) { e.printStackTrace(); }}}Copy the code- Zip and package all Excel files and export them
/**
* @param request
* @param response
* @param fileNameWithTimestamp
* @param userId
*/
public static void createZipExport(HttpServletRequest request,
HttpServletResponse response,
final String fileNameWithTimestamp,
Long userId) throws Exception{
final String localRelativePath = "" + userId + "/"+ fileNameWithTimestamp; // Create a folder and save the generated excel file to the local directory on the server.'/app/file/[userId]/[fileNameWithTimestamp]/[fileNameWithTimestamp_i].xlS'
String excelFold = FileUtil.getFileRootPath() + localRelativePath; // Zip file path:"/app/file/userId/fileNameWithTimestamp.zip"String zipFold = FileUtil.getFileRootPath() + userId; Zip file final String zipFileName = fileNameWithTimestamp +".zip"; FileUtil.createZipFile(excelFold, zipFold, zipFileName); InputStream is = null; try{ is = new FileInputStream(new File(zipFold + File.separator + zipFileName)); } catch(IOException e){ e.printStackTrace(); } BufferedInputStream bis = new BufferedInputStream(is); // ServletOutputStream out = response.getOutputStream(); BufferedOutputStream bos = new BufferedOutputStream(response.getOutputStream()); String zipName = handleFileName(request, zipFileName); Response.reset (); response.setContentType("application/vnd.ms-excel; charset=utf-8");
response.setHeader("Content-Disposition"."attachment; filename=" + zipName);
byte[] buff = new byte[2048];
int bytesRead;
// Simple read/write loop.
while((bytesRead = bis.read(buff, 0, buff.length)) ! = -1 ) { bos.write(buff, 0, bytesRead); } bis.close(); bos.close(); // Delete the folder used to temporarily save Excel and zip File fileutil.deletedir (new File(zipFold)); }Copy the code