java生成excel及下载 poi

最新推荐文章于 2022-05-09 09:49:10 发布

qq_17152035

最新推荐文章于 2022-05-09 09:49:10 发布

阅读量379

点赞数

CC 4.0 BY-SA版权

本文链接：https://blog.youkuaiyun.com/qq_17152035/article/details/75670257

本文介绍了一种通过Java实现从数据库导出大量数据至Excel的方法，包括参数处理、数据分页及导出流程。该方法使用Apache POI库来创建和操作Excel文件，并通过分批查询减少内存占用。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

//此为实现类方法

//导出符合查询条件的excel
public 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为空的会被筛选掉)