java生成excel及下载 poi

//此为实现类方法

//导出符合查询条件的excelpublic void exportExcel(HttpServletResponse response, String syb, String model, String country, String tag, String deadline, String life, String imei) throws ParseException, IOException {    //处理参数    Date startTime = null;    Date endTime = null;    Integer lifeStart = null;    Integer lifeEnd = null;    String modelArr[] = null;    String countryArr[] = null;    Integer start = 0;    //sql每次查询5000条 不然一次全查出来 超过百万会内存溢出    Integer size = 5000;    if (model != null && !model.equals("")) {        modelArr = model.split(",");    }    if (country != null && !country.equals("")) {        countryArr = country.split(",");    }    if (deadline != null && !deadline.equals("")) {        String timeArr[] = deadline.split(",");        SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");        startTime = simpleDateFormat.parse(simpleDateFormat.format(new Date(Long.parseLong(timeArr[0]))));        endTime = simpleDateFormat.parse(simpleDateFormat.format(new Date(Long.parseLong(timeArr[1]))));    }    if (life != null && !life.equals("")) {        String lifeArr[] = life.split(",");        lifeStart = Integer.parseInt(lifeArr[0]);        lifeEnd = Integer.parseInt(lifeArr[1]);    }    List<PhoneInfo> phoneInfoList = new ArrayList<>();    List<CountryInfo> countryInfos = countryMapper.getAllCountry();    List<AreaInfo> areaInfos = areaMapper.getAllArea();    //总页数计算方式    //int totalPageNum = (totalRecord  +  pageSize  - 1) / pageSize;    try {        //第一步，创建一个webbook，对应一个Excel文件        SXSSFWorkbook wb = new SXSSFWorkbook(10000);        //第二步，在webbook中添加一个sheet，对应Excel文件中的 sheet        SXSSFSheet sheet1 = wb.createSheet("1");        //cell标题行        SXSSFRow row = sheet1.createRow(0);        row.createCell(0).setCellValue("imei");        row.createCell(1).setCellValue("机型");        row.createCell(2).setCellValue("区域");        row.createCell(3).setCellValue("国家");        row.createCell(4).setCellValue("Province");        row.createCell(5).setCellValue("City");        row.createCell(6).setCellValue("事业部");        row.createCell(7).setCellValue("公司");        row.createCell(8).setCellValue("se版本");        row.createCell(9).setCellValue("安卓版本");        row.createCell(10).setCellValue("PhoneType");        row.createCell(11).setCellValue("Channel_no");        row.createCell(12).setCellValue("机龄");        row.createCell(13).setCellValue("标签");        row.createCell(14).setCellValue("首次登录时间");        row.createCell(15).setCellValue("最后登录时间");        SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");        //用于计算分页limit 起始index        int c = 0;        boolean b = true;        //excel的行数        int rowNum = 1;        //记录cell行数        int num = 0;        while (b) {            start = c * size;

            phoneInfoList =  phoneInfoMapper.exportExcel(syb, countryArr, modelArr, tag, imei, startTime, endTime, lifeStart, lifeEnd, start, size);            if (phoneInfoList.size() != 0) {                for (PhoneInfo phoneInfo: phoneInfoList) {                    for (AreaInfo areaInfo : areaInfos) {                        if (phoneInfo.getArea() != null &&  !phoneInfo.getArea().equals("") && !phoneInfo.getArea().equals("null")) {                            if (Integer.parseInt(phoneInfo.getArea()) == areaInfo.getId()) {                                phoneInfo.setArea(areaInfo.getArea());                                break;                            }                        }                    }                    row = sheet1.createRow(rowNum - num);                    row.createCell(0).setCellValue(phoneInfo.getImei());                    row.createCell(1).setCellValue(phoneInfo.getModel());                    row.createCell(2).setCellValue(phoneInfo.getArea());                    row.createCell(3).setCellValue(phoneInfo.getCountry());                    row.createCell(4).setCellValue(phoneInfo.getProvince());                    row.createCell(5).setCellValue(phoneInfo.getCity());                    row.createCell(6).setCellValue(phoneInfo.getSyb());                    row.createCell(7).setCellValue(phoneInfo.getCompany());                    row.createCell(8).setCellValue(phoneInfo.getSe_version());                    row.createCell(9).setCellValue(phoneInfo.getAndroid_version());                    row.createCell(10).setCellValue(phoneInfo.getPhonetype());                    row.createCell(11).setCellValue(phoneInfo.getChannel_no());                    row.createCell(12).setCellValue(Integer.toString(phoneInfo.getAge()));                    row.createCell(13).setCellValue(phoneInfo.getTag());                    row.createCell(14).setCellValue(formatter.format( phoneInfo.getCtime()));                    row.createCell(15).setCellValue(formatter.format( phoneInfo.getUtime()));

    //如果超过 65530行 新建sheet 保存数据                    if (rowNum % 65530 == 0 && rowNum != 0) {                        num = rowNum ;                        sheet1 =  wb.createSheet(""+rowNum+"");                        row = sheet1.createRow(0);                        row.createCell(0).setCellValue("imei");                        row.createCell(1).setCellValue("机型");                        row.createCell(2).setCellValue("区域");                        row.createCell(3).setCellValue("国家");                        row.createCell(4).setCellValue("Province");                        row.createCell(5).setCellValue("City");                        row.createCell(6).setCellValue("事业部");                        row.createCell(7).setCellValue("公司");                        row.createCell(8).setCellValue("se版本");                        row.createCell(9).setCellValue("安卓版本");                        row.createCell(10).setCellValue("PhoneType");                        row.createCell(11).setCellValue("Channel_no");                        row.createCell(12).setCellValue("机龄");                        row.createCell(13).setCellValue("标签");                        row.createCell(14).setCellValue("首次登录时间");                        row.createCell(15).setCellValue("最后登录时间");                    }                    rowNum ++;                }            }else {                b = false;            }            c ++;        }

//读写完毕前端响应下载        response.setHeader("content-disposition", "attachment;filename=" + URLEncoder.encode("table.xls", "UTF-8"));        wb.write(response.getOutputStream());

//保存到本地

/*if(!new File("C:/exceltest").exists()) {        new File("C:/exceltest").mkdirs();      }      wb.write(new FileOutputStream("C:/exceltest/table.xlsx"));*/    }    catch(Exception e) {        e.printStackTrace();    }}

js: 不能用ajax方式请求

  var downExcel = request_url+"/user/exportExcel.json?page=1&size=20&syb="+syb+"&country="+country+"&model="      + model + "&tag="+tag +"&deadline="+deadline+"&life="+ life+ "&imei=" +imei;  $("#exportInfo").attr("href",downExcel);
jar包
    <!-- https://mvnrepository.com/artifact/org.apache.poi/poi-->   <dependency>      <groupId>org.apache.poi</groupId>      <artifactId>poi</artifactId>      <version>3.16</version>   </dependency>   <!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml-->   <dependency>      <groupId>org.apache.poi</groupId>      <artifactId>poi-ooxml</artifactId>      <version>3.16</version>   </dependency>

外连  （后来改成单表查了 area country在代码里查出所有在遍历比三表查直接查出结果要快 数据量多的情况下）select t.id,t.ctime,t.utime,t.imei,t.model,t.province,t.city,t.syb,t.company,t.se_version,        t.android_version,t.phonetype,t.channel_no,t.`status`,t.age,t.tag,a.area,c.country        from (t_phone_info t LEFT JOIN t_country c on t.country = c.`code`)left join t_area a on a.id = t.area where 1=1（area为空的 country也会为空显示不出来）连表 select t.id,t.ctime,t.utime,t.imei,t.model,t.province,t.city,t.syb,t.company,t.se_version,        t.android_version,t.phonetype,t.channel_no,t.`status`,t.age,t.tag,a.area,c.country        from t_phone_info t,t_country c, t_area a  where 1=1  and t.country = c.`code` and a.id = t.area (area为空的会被筛选掉)