Java excel导出完整代码示例，仅供参考

素颜马尾好姑娘i 2022-12-04 01:10 75阅读 0赞

> 主要是懒得删代码，就全部提出来，具体某个模块其他文章里细分

*  代码示例
    
        public void exportEvents(EventExportQuery query, RedisUser user, HttpServletResponse response, String token) { 
              List<EvtToDoListVO> toDoEventLists = null;
              ServletOutputStream out = null;
              FileInputStream ips = null;
              jxl.write.WritableWorkbook wwb = null;
              try { 
        
                  //查询次数
                  int queryTimes = 0;
                  EvtToDoQuery evtToDoQuery = new EvtToDoQuery();
                  //ids 不为空，根据ids 查询
                  if (!StringUtil.isEmpty(query.getIds())){ 
                      List<Long> ids = StringUtil.getIdList(query.getIds());
                      toDoEventLists = eventReadMapper.findEventToDoByIds(ids);
                  }else { 
                      EvtInfoVO info = null;
                      BeanUtils.copyProperties(query, evtToDoQuery);
                      //否则根据条件查询
                      if(user.isAdmin()){ 
                          //管理员不做过滤
                          evtToDoQuery.setIsAdmin(1);
                      }else { 
                          if (!StringUtil.isInvalid(user.getRoleIds())) { 
                              evtToDoQuery.setRoleIdList(StringUtil.stringToLongList(user.getRoleIds()));
                          }
                          if (!StringUtil.isInvalid(user.getDeptIds())) { 
                              evtToDoQuery.setDeptIdList(StringUtil.stringToLongList(user.getDeptIds()));
                          }else if (user.getDeptId() != null){ 
                              List<Long> idList = new ArrayList<>();
                              idList.add(user.getDeptId());
                              evtToDoQuery.setDeptIdList(idList);
                          }
                      }
                      //先查2000条
                      PageHelper.startPage(1, 2000);
                      Page<EvtToDoListVO> toDoLists = eventReadMapper.findEvtToDoList(evtToDoQuery);
        
                      if (null != toDoLists.getResult() && toDoLists.getResult().size() > 0){ 
                          if (toDoLists.getTotal() > 2000) { 
                              double total = toDoLists.getTotal();
                              double temp = total / 2000;
                              queryTimes = new Double(Math.ceil(temp)).intValue();
                          }
                          toDoEventLists = toDoLists.getResult();
                      }
                  }
                  //获取当前项目路径
                  String sourcePath = System.getProperty("user.dir");
                  String savePath = sourcePath + "/" + TimeUtil.formatDateyyyyMMdd(TimeUtil.now());
                  File path = new File(savePath);
                  if(!path.exists()){ //如果文件夹不存在
                      path.mkdir();//创建文件夹
                  }
                  String fileName = new Date().getTime() + "" + Math.random() + ".xls";
                  File file = new File(savePath, fileName);
                  wwb = Workbook.createWorkbook(file);
                  jxl.write.Label lb = null;
                  jxl.write.WritableSheet ws = wwb.createSheet("第1页", 0);// 工作薄
                  jxl.write.WritableFont wf = new jxl.write.WritableFont(WritableFont.ARIAL, 18, WritableFont.BOLD, false);// 样式
                  jxl.write.WritableCellFormat wcfF = new jxl.write.WritableCellFormat(wf);
                  wcfF.setAlignment(jxl.format.Alignment.CENTRE);// 设置为居中
                  ws.mergeCells(0, 0, 7, 0);// 合并每一行的1-11列
                  lb = new jxl.write.Label(0, 0, "案件列表", wcfF);
                  wf = new jxl.write.WritableFont(WritableFont.ARIAL, 10,WritableFont.BOLD, false);// 样式
                  wcfF = new jxl.write.WritableCellFormat(wf);
                  wcfF.setAlignment(jxl.format.Alignment.CENTRE);// 设置为居中
                  ws.addCell(lb);
                  String[] title = { "剩余时间","案件编号","来件时间","案件来源", "案件类别", "所属区域","案发位置", "问题描述","上报图片","上报图片",
                          "上报图片","上报图片","处理图片","处理图片","处理图片","处理图片","核查图片","核查图片","核查图片","核查图片"};
        
                  for (int i = 0; i < title.length; i++) { // 设置标题
                      ws.setColumnView(i, 20);// 设置列的宽度
                      lb = new jxl.write.Label(i, 1, title[i], wcfF);
                      ws.addCell(lb);
                  }
                  int count = 2;
                  if (toDoEventLists != null && toDoEventLists.size() > 0) { 
                      for (EvtToDoListVO evt : toDoEventLists) { 
                          List<EvtRuTaskVO> taskList = evtAcceptFlowService.findRuTaskInfoByEvtId(evt.getId());
                          if (taskList != null && taskList.size() > 0) { 
                              for (EvtRuTaskVO ruTaskVO : taskList) { 
                                  if (evt.getWorkTache().equals(ruTaskVO.getCurTacheCode())) { 
                                      evt.setCurTacheName(ruTaskVO.getCurTacheName());
                                      evt.setOverTimePoint(ruTaskVO.getOverTimePoint());
                                  }
                              }
                          }
        
                          ws.setRowView(count,900,false);//设置列的高度
        
                          // 是否是申请不设时限
                          if (evt.getOverTimePoint() == null) { 
                              lb = new jxl.write.Label(0, count, "视情况而定");
                          } else { 
                              lb = new jxl.write.Label(0, count, EventTimeUtils.getRemainingTime(evt.getReceiveTime(), evt.getOverTimePoint()));
                          }
                          lb.setCellFormat(setLabCell());
                          ws.addCell(lb);
                          lb = new jxl.write.Label(1, count, evt.getEvtCodeStr());
                          lb.setCellFormat(setLabCell());
                          ws.addCell(lb);
                          lb = new jxl.write.Label(2, count, evt.getReceiveTime().toString());
                          lb.setCellFormat(setLabCell());
                          ws.addCell(lb);
                          //获取案件来源字符串
                          String evtSource = null;
                          ResultInfo<String> sourceStr = dictService.findByCodeAndValue(BusinessConstants.EVENT_SOURCE_DICT_CODE,
                                  evt.getEvtResource(), token);
                          if (sourceStr.getCode().equals(ResultCode.SUCCESS.code())) { 
                              evtSource = sourceStr.getData();
                          }
        
                          lb = new jxl.write.Label(3, count, evtSource);
                          lb.setCellFormat(setLabCell());
                          ws.addCell(lb);
                          lb = new jxl.write.Label(4, count, evt.getEvtClassName());
                          lb.setCellFormat(setLabCell());
                          ws.addCell(lb);
                          lb = new jxl.write.Label(5, count, evt.getAreaNameStr());
                          lb.setCellFormat(setLabCell());
                          ws.addCell(lb);
                          lb = new jxl.write.Label(6, count, evt.getEvtLocation());
                          lb.setCellFormat(setLabCell());
                          ws.addCell(lb);
                          lb = new jxl.write.Label(7, count, evt.getContent());
                          lb.setCellFormat(setLabCell());
                          ws.addCell(lb);
        
                          //写入图片
                          List<EvtAnnexe> beforeList = evtAnnexeService.findEvtImgByBsType(evt.getId(),BusinessConstants.eventPicBeforeType);
                          if (!CollectionUtils.isEmpty(beforeList)){ 
                              int cellNum = 8;
                              if (beforeList.size() > 4){ 
                                  beforeList.subList(0,3);
                              }
                              for (EvtAnnexe item:beforeList) { 
                                  String imgPath = filePath +item.getFilePath();
                                  File imgFile =getFile(imgPath);
                                  WritableImage image = new WritableImage(cellNum, count, 1, 1, imgFile);
                                  ws.addImage(image);
                                  cellNum ++;
                              }
                          }
                          List<EvtAnnexe> afterList = evtAnnexeService.findEvtImgByBsType(evt.getId(),BusinessConstants.eventPicAfterType);
                          if (!CollectionUtils.isEmpty(afterList)){ 
                              int cellNum = 12;
                              if (afterList.size() > 4){ 
                                  afterList.subList(0,3);
                              }
                              for (EvtAnnexe item:afterList) { 
                                  String imgPath = filePath +item.getFilePath();
                                  File imgFile =getFile(imgPath);
                                  WritableImage image = new WritableImage(cellNum, count, 1, 1, imgFile);
                                  ws.addImage(image);
                                  cellNum ++;
                              }
                          }
                          List<EvtAnnexe> heChaList = evtAnnexeService.findEvtImgByBsType(evt.getId(),BusinessConstants.ONE_FLOW_TYPE);
                          if (!CollectionUtils.isEmpty(heChaList)){ 
                              int cellNum = 16;
                              if(heChaList.size() > 4){ 
                                  heChaList.subList(0,3);
                              }
                              for (EvtAnnexe item:heChaList) { 
                                  String imgPath = filePath +item.getFilePath();
                                  File imgFile =getFile(imgPath);
                                  WritableImage image = new WritableImage(cellNum, count, 1, 1, imgFile);
                                  ws.addImage(image);
                                  cellNum ++;
                              }
                          }
                          count++;
                      }
                  }
                  // 写入Exel工作表到文件
                  wwb.write();
                  wwb.close();
                  if (queryTimes > 1){ 
                      AssembleData(file, evtToDoQuery, count, queryTimes, 2, token);
                  }
        
                  response.reset();
                  response.setCharacterEncoding("utf-8");
                  response.setContentType("application/vnd.ms-excel");
                  response.setHeader("Content-Disposition", "attachment;filename="+ new String("案件列表.xls".getBytes("gbk"), "ISO-8859-1"));
                  out = response.getOutputStream();
                  ips = new FileInputStream(file);
                  //读取文件流
                  int len = 0;
                  //设置字节数组大小为10MB
                  byte[] buffer = new byte[1024 * 1024 * 10];
                  while ((len = ips.read(buffer)) != -1){ 
                      out.write(buffer, 0, len);
                  }
                  out.flush();
              }catch (Exception e) { 
                  e.printStackTrace();
              }finally { 
                  // 关闭流
                  try { 
                      if (ips != null) { 
                          ips.close();
                      }
                      if (out != null) { 
                          out.close();
                      }
                  }catch(Exception e) { 
                      e.printStackTrace();
                  }
              }
          }
 *  循环方法
    
        private void AssembleData(File file,  EvtToDoQuery query,  int count,
                                 int queryTimes, int times, String token){ 
              Workbook wb = null;
              jxl.write.WritableWorkbook wwb = null;
              WritableFont wf = null;
              WritableCellFormat wcfF = null;
              try { 
                  wb = Workbook.getWorkbook(file);
                  wwb = Workbook.createWorkbook(file, wb);
                  jxl.write.Label lb = null;
                  jxl.write.WritableSheet ws = null;
                  //因为每次查询2000条，所以每5次查询后，新增一个sheet
                  int sheet = (times - 1) / 5;
                  if ((times - 1) % 5 == 0) { 
                      ws = wwb.createSheet("第" + (sheet + 1) + "页", sheet);// 工作薄
                      wf = new jxl.write.WritableFont(WritableFont.ARIAL, 18, WritableFont.BOLD, false);// 样式
                      wcfF = new jxl.write.WritableCellFormat(wf);
                      wcfF.setAlignment(jxl.format.Alignment.CENTRE);// 设置为居中
                      ws.mergeCells(0, 0, 7, 0);// 合并每一行的1-11列
                      lb = new jxl.write.Label(0, 0, "案件列表", wcfF);
                      wf = new jxl.write.WritableFont(WritableFont.ARIAL, 10, WritableFont.BOLD, false);// 样式
                      wcfF = new jxl.write.WritableCellFormat(wf);
                      wcfF.setAlignment(jxl.format.Alignment.CENTRE);// 设置为居中
                      ws.addCell(lb);
                      String[] title = { "剩余时间","案件编号","来件时间","案件来源", "案件类别", "所属区域","案发位置", "问题描述","上报图片","上报图片",
                              "上报图片","上报图片","处理图片","处理图片","处理图片","处理图片","核查图片","核查图片","核查图片","核查图片"};
        
                      for (int i = 0; i < title.length; i++) { // 设置标题
                          ws.setColumnView(i, 20);// 设置列的宽度
                          lb = new jxl.write.Label(i, 1, title[i], wcfF);
                          ws.addCell(lb);
                      }
                      count = 2;
                  }else { 
                      ws = wwb.getSheet(sheet);
                  }
        
                  wf = new WritableFont(WritableFont.ARIAL, 10,  WritableFont.BOLD, false);
                  wcfF = new WritableCellFormat(wf);
                  wcfF.setAlignment(Alignment.CENTRE);
                  PageHelper.startPage(times, 2000);
                  Page<EvtToDoListVO> toDoLists = eventReadMapper.findEvtToDoList(query);
                  List<EvtToDoListVO> toDoEventLists = toDoLists.getResult();
                  if (toDoEventLists != null && toDoEventLists.size() > 0) { 
                      for (EvtToDoListVO evt : toDoEventLists) { 
                          ws.setRowView(count,900,false);//设置列的高度
                          // 是否是申请不设时限
                          if (evt.getOverTimePoint() == null) { 
                              lb = new jxl.write.Label(0, count, "视情况而定");
                          } else { 
                              lb = new jxl.write.Label(0, count, EventTimeUtils.getRemainingTime(evt.getReceiveTime(), evt.getOverTimePoint()));
                          }
                          lb.setCellFormat(setLabCell());
                          ws.addCell(lb);
                          lb = new jxl.write.Label(1, count, evt.getEvtCodeStr());
                          lb.setCellFormat(setLabCell());
                          ws.addCell(lb);
                          lb = new jxl.write.Label(2, count, evt.getReceiveTime().toString());
                          lb.setCellFormat(setLabCell());
                          ws.addCell(lb);
                          //获取案件来源字符串
                          String evtSource = null;
                          ResultInfo<String> sourceStr = dictService.findByCodeAndValue(BusinessConstants.EVENT_SOURCE_DICT_CODE,
                                  evt.getEvtResource(), token);
                          if (sourceStr.getCode().equals(ResultCode.SUCCESS.code())) { 
                              evtSource = sourceStr.getData();
                          }
                          lb = new jxl.write.Label(3, count, evtSource);
                          lb.setCellFormat(setLabCell());
                          ws.addCell(lb);
                          lb = new jxl.write.Label(4, count, evt.getEvtClassName());
                          lb.setCellFormat(setLabCell());
                          ws.addCell(lb);
                          lb = new jxl.write.Label(5, count, evt.getAreaNameStr());
                          lb.setCellFormat(setLabCell());
                          ws.addCell(lb);
                          lb = new jxl.write.Label(6, count, evt.getEvtLocation());
                          lb.setCellFormat(setLabCell());
                          ws.addCell(lb);
                          lb = new jxl.write.Label(7, count, evt.getContent());
                          lb.setCellFormat(setLabCell());
                          ws.addCell(lb);
        
                          List<EvtAnnexe> beforeList = evtAnnexeService.findEvtImgByBsType(evt.getId(),BusinessConstants.eventPicBeforeType);
                          if (!CollectionUtils.isEmpty(beforeList)){ 
                              int cellNum = 8;
                              if (beforeList.size() > 4){ 
                                  beforeList.subList(0,3);
                              }
                              for (EvtAnnexe item:beforeList) { 
                                  String imgPath = filePath +item.getFilePath();
                                  File imgFile =getFile(imgPath);
                                  WritableImage image = new WritableImage(cellNum, count, 1, 1, imgFile);
                                  ws.addImage(image);
                                  cellNum ++;
                              }
                          }
                          List<EvtAnnexe> afterList = evtAnnexeService.findEvtImgByBsType(evt.getId(),BusinessConstants.eventPicAfterType);
                          if (!CollectionUtils.isEmpty(afterList)){ 
                              int cellNum = 12;
                              if (afterList.size() > 4){ 
                                  afterList.subList(0,3);
                              }
                              for (EvtAnnexe item:afterList) { 
                                  String imgPath = filePath +item.getFilePath();
                                  File imgFile =getFile(imgPath);
                                  WritableImage image = new WritableImage(cellNum, count, 1, 1, imgFile);
                                  ws.addImage(image);
                                  cellNum ++;
                              }
                          }
                          List<EvtAnnexe> heChaList = evtAnnexeService.findEvtImgByBsType(evt.getId(),BusinessConstants.ONE_FLOW_TYPE);
                          if (!CollectionUtils.isEmpty(heChaList)){ 
                              int cellNum = 16;
                              if (heChaList.size() > 4){ 
                                  heChaList.subList(0,3);
                              }
                              for (EvtAnnexe item:heChaList) { 
                                  String imgPath = filePath +item.getFilePath();
                                  File imgFile =getFile(imgPath);
                                  WritableImage image = new WritableImage(cellNum, count, 1, 1, imgFile);
                                  ws.addImage(image);
                                  cellNum ++;
                              }
                          }
                          count++;
                      }
                  }
                  // 写入Exel工作表文件
                  wwb.write();
                  // 关闭Excel工作薄对象
                  wwb.close();
                  ++times;
                  if (times <= queryTimes) { 
                      AssembleData(file, query, count, queryTimes, times, token);
                  }
              }catch (Exception e) { 
                  e.printStackTrace();
              }
          }