JAVA POI HSSFWorkbook 导出Excel表格超大数据量解决方案

清疚 2022-06-06 00:39 508阅读 0赞

Java实现导出excel表格功能，大部分都会使用apache poi， [apache poi API 地址][apache poi API]  
POI之前的版本不支持大数据量处理，如果数据过多则经常报OOM错误，有时候调整JVM大小效果也不是太好。3.8版本的POI新出来了SXSSFWorkbook,可以支持大数据量的操作，只是SXSSFWorkbook只支持.xlsx格式，不支持.xls格式。  
3.8版本的POI对excel的导出操作，一般只使用HSSFWorkbook以及SXSSFWorkbook，HSSFWorkbook用来处理较少的数据量，SXSSFWorkbook用来处理大数据量以及超大数据量的导出。  
代码：

[git地址 有3.9jar包][git_ _3.9jar]

package qs.test;
    
    import java.io.FileNotFoundException;
    import java.io.FileOutputStream;
    import java.io.IOException;
    
    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.util.CellReference;
    import org.apache.poi.xssf.streaming.SXSSFWorkbook;
    
    /**
     * ClassName: SXSSFTest
     * @Description: TODO
     * @author qiaoshuai
     */
    public class SXSSFTest {
        
    
        public static void main(String[] args) throws IOException {
            // 创建基于stream的工作薄对象的
            SXSSFWorkbook wb = new SXSSFWorkbook(100); // keep 100 rows in memory,
                                                        // exceeding rows will be
                                                        // flushed to disk
            // SXSSFWorkbook wb = new SXSSFWorkbook();
            // wb.setCompressTempFiles(true); // temp files will be gzipped
            Sheet sh = wb.createSheet();
            // 使用createRow将信息写在内存中。
            for (int rownum = 0; rownum < 1000; rownum++) {
                Row row = sh.createRow(rownum);
                for (int cellnum = 0; cellnum < 10; cellnum++) {
                    Cell cell = row.createCell(cellnum);
                    String address = new CellReference(cell).formatAsString();
                    cell.setCellValue(address);
                }
    
            }
    
            // Rows with rownum < 900 are flushed and not accessible
            // 当使用getRow方法访问的时候，将内存中的信息刷新到硬盘中去。
            for (int rownum = 0; rownum < 900; rownum++) {
                System.out.println(sh.getRow(rownum));
            }
    
            // ther last 100 rows are still in memory
            for (int rownum = 900; rownum < 1000; rownum++) {
                System.out.println(sh.getRow(rownum));
            }
            // 写入文件中
            FileOutputStream out = new FileOutputStream("G://sxssf.xlsx");
            wb.write(out);
            // 关闭文件流对象
            out.close();
            System.out.println("基于流写入执行完毕!");
        }
    
    }

在此基础上再优化的方案是导出的Excel表格生成多个工作表即生成多个sheet。  
代码：

import java.io.IOException;
    import java.io.UnsupportedEncodingException;
    import java.net.URLEncoder;
    import java.util.ArrayList;
    import java.util.Date;
    import java.util.LinkedHashMap;
    import java.util.List;
    
    import org.springframework.http.HttpHeaders;
    import org.springframework.http.HttpStatus;
    import org.springframework.http.MediaType;
    import org.springframework.http.ResponseEntity;
    
    import com.common.DateFormatUtil;
    
    public class ExlUtil2 {
        
    
        /**
         * @param excelHeader
         *            表头信息
         * @param list
         *            要导出到excel的数据源,List类型
         * @param sheetName
         *            表名
         * @return
         */
        public static ResponseEntity<byte[]> getDataStream(ExcelHeader excelHeader,
                List list, String sheetName) {
            LinkedHashMap<String, List> map = new LinkedHashMap<String, List>();
            List<String[]> headNames = new ArrayList<String[]>();
            List<String[]> fieldNames = new ArrayList<String[]>();
            String[] sheetNames = new String[100];
            //处理Excel生成多个工作表 
            //定义为每个工作表数据为50000条
            if (list.size() > 50000) {
                int k = (list.size() + 50000) / 50000;
                for (int i = 1; i <= k; i++) {
                    if (i < k) {
                        map.put(sheetName + i,
                                list.subList((i - 1) * 50000, i * 50000));
                    } else {
                        map.put(sheetName + i,
                                list.subList((i - 1) * 50000, list.size()));
                    }
    
                    headNames.add(excelHeader.getHeadNames().get(0));
                    fieldNames.add(excelHeader.getFieldNames().get(0));
                    sheetNames[i - 1] = sheetName;
                }
    
            } else {
                map.put(sheetName, list);
                headNames.add(excelHeader.getHeadNames().get(0));
                fieldNames.add(excelHeader.getFieldNames().get(0));
                sheetNames[0] = sheetName;
            }
    
            byte[] buffer = null;
    
            try {
                buffer = ExcelUtil2.output(headNames, fieldNames, sheetNames, map);
    
            } catch (IllegalArgumentException | IllegalAccessException
                    | IOException e) {
                e.printStackTrace();
            }
            HttpHeaders headers = new HttpHeaders();
            headers.setContentType(MediaType.APPLICATION_OCTET_STREAM);
    
            /*
             * try { sheetName=URLEncoder.encode(sheetName,"UTF8"); } catch
             * (UnsupportedEncodingException e) { e.printStackTrace(); }
             */
            try {
                sheetName = new String(sheetName.getBytes("gbk"), "iso-8859-1");
            } catch (UnsupportedEncodingException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
            String fileGenerateTime = DateFormatUtil.toStr(new Date());
            headers.setContentDispositionFormData("attachment", sheetName
                    + fileGenerateTime + ".xlsx");
            return new ResponseEntity<byte[]>(buffer, headers, HttpStatus.CREATED);
        };
    
    }

[apache poi API]: https://poi.apache.org/
[git_ _3.9jar]: https://code.csdn.net/qiaoshuai0920/testsxssf/tree/master