java导出excel，边查询边导出

导出数据的时候，数据量如果特别大，比如超过了100万条数据，如果等到查询结束，全部返回结果后再导出，往往会导致计算机内存溢出。
下面介绍一种边查询，边导出的方式。

public void writeToFile(String path, final List<Map<String, Object>> titles,
            String sql) throws Exception {
        // TODO Auto-generated method stub
        final int colNumber = titles.size();     // 获得列数
        String totalNumber = getCount(sql);      // 获得数据集行数
        List<File> fileList = new ArrayList<File>();
        int rowNumber = Integer.parseInt(totalNumber);
        int limit = 100000;                      //每个excel行数
        int offset = rowNumber%limit == 0? rowNumber/limit : rowNumber/limit + 1;  //生成的excel个数
        try{
            for(int n=0; n<offset; n++){
                // 这里1000是在内存中的数量，如果大于此数量时，会写到硬盘，以避免在内存导致内存溢出
                SXSSFWorkbook wb = new SXSSFWorkbook(1000);
                //构造表体
                final Sheet sh = wb.createSheet();
                //设置表格样式
                final CellStyle style = createStyle(wb);
                //创建表头信息
                crRow(colNumber,titles,wb,sh);
                String temp = sql + " limit " + limit + " offset " + n*limit; 
                //获取数据集
                jdbcTemplate.query(temp, new RowCallbackHandler() {
                    int i = 1;
                    @Override
                    public void processRow(ResultSet rs) throws SQLException {
                        Row row = sh.createRow(i);
                        sh.autoSizeColumn(i);
                        for(int j = 0; j < colNumber; j++){
                            Cell cell = row.createCell(j);
                            String key = "";
                            String value = "";
                            Map<String,Object> titleMap = titles.get(j);
                            Iterator it = titleMap.entrySet().iterator();
                            while(it.hasNext()){
                                Entry entry = (Entry) it.next();
                                key = (String) entry.getKey();
                                Object temp = rs.getString(key);
                                if(temp != null){
                                    value = temp.toString();
                                }else{
                                    value = "";
                                }
                            }
                            if(j == 0){
                                int cellLength = value.getBytes().length;
                                sh.setColumnWidth(j,cellLength*256);
                            }
                            cell.setCellStyle(style);
                            cell.setCellValue(value);
                        }
                        i++;
                    }
                });
                //导出临时excel
                String path1 =path.split(".zip")[0] + n + ".xlsx";
                File file = new File(path1);
                file.createNewFile();
                fileList.add(file);
                FileOutputStream out = new FileOutputStream(file);
                wb.write(out);
                out.close();
            }
            //打包压缩文件
            if(fileList!=null && fileList.size() != 0){
                FileOutputStream out = new FileOutputStream(path);
                doZip(out,fileList);
                out.close();
            }
        }catch (Exception e) {
            e.printStackTrace();
        } finally {
            // 清空临时文件
            cleanTempFile(fileList);
            fileList.clear();
            fileList = null;
        }
    }