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

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

代码示例 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(); } }