Java Excel导入导出（实战）
一、批量导入（将excel文件转成list）
1. 前台代码逻辑
1）首先在html页面加入下面的代码（可以忽略界面的样式）
<label for="uploadFile" class="label-btn"> <img id="uploadbtn" src="../img/upload-on.png" > <input type="file" name="" id="uploadFileWhite" onchange="personUpload('uploadFileWhite')"> </label>
2）其次在引入的js里加入以下代码
uploadData = { // 导入是否正在上传文件 personUploading:false, //后台返回描述信息 returnStr: "", } //导入文件上传 function personUpload(id){ if( uploadData.personUploading === true ){ alerFunc("文件正在上传中，请稍候"); return false; } if($('#'+id)[0].files.length!=1){ //alert("请先选择文件"); return false; } var file = $('#'+id)[0].files[0]; var arr = file.name.split('.'); if(arr[1]!='xls'){ alerFunc("文件类型不匹配，请使用xls类型的excel导入"); return false; } var sendData = new FormData(); sendData.append('uploadFile', file); sendData.append('cutFileName', file.name); console.log("ajax请求批量上传人员信息"); var sendDate = new Date(); $.ajax({ url: "uploadImportAction!personUpload.action", type: 'POST', cache: false, async:true, data: sendData, processData: false, contentType: false, dataType:"json", beforeSend: function(){ uploadData.personUploading = true; $('#uploadWait').show();//展示等待上传页面 }, success : function(response) { var receiveDate = new Date(); var costTime = receiveDate.getTime() - sendDate.getTime(); console.log("批量上传成功" + ",花费时间为：" + costTime + "毫秒"); closeUploadDiv(); uploadData.returnStr = response.data.desc; if(uploadData.returnStr=='ok'){ alerFunc("导入成功"); }else{ //显示返回信息div show(); } }, complete : function(){ uploadData.personUploading = false; } }); } function onTrackUpload(response){ uploadData.returnStr = response.data.desc; show(); } function show(){ $('#tipDiv').show(); $('#tipContent').html(uploadData.returnStr); }
2. 后台java导入代码逻辑
1）action 层
public class UploadAction extends BaseAction<Person>{ private static final long serialVersionUID = 1L; private static Logger logger = Logger.getLogger(UploadAction.class); private UploadService uploadService; private File uploadFile; private String cutFileName; public String personUpload(){ logger.info("接收批量上传的请求"); Date start = new Date();try { String desc = uploadService.personUpload(uploadFile); result.addData("desc", desc); logger.info("批量上传成功"); } catch (Exception e) { result.setResult("error"); e.printStackTrace(); logger.error("批量上传失败！" + e.getMessage()); } Date end = new Date(); long time = end.getTime() - start.getTime(); logger.info("录批量上传总耗时：" + time + "毫秒"); return SUCCESS; } public String getCutFileName() { return cutFileName; } public void setCutFileName(String cutFileName) { this.cutFileName = cutFileName; } public File getUploadFile() { return uploadFile; } public void setUploadFile(File uploadFile) { this.uploadFile = uploadFile; } public UploadService getUploadService() { return uploadService; } public void setUploadService(UploadService uploadService) { this.uploadService = uploadService; } public UploadAction() { } }
2）service层
@Override public String personUpload(File uploadFile) { // 1,读取Excel FileInputStream inFile = null; try { inFile = new FileInputStream(uploadFile); } catch (FileNotFoundException e) { logger.error("读取excel表格失败：" + e); } HSSFWorkbook readbook = null; try { readbook = new HSSFWorkbook(inFile); } catch (IOException e) { logger.error("创建HSSFWorkbook失败：" + e); } // 定义执行结果 String returnStr=""; int successNum = 0;// 成功条数 int failNum = 0;// 失败条数 int nSheetNum = readbook.getNumberOfSheets(); if (nSheetNum > 0) { HSSFSheet readSheet = null; // 读取第一页 readSheet = readbook.getSheetAt(0); // 从第二行开始读取 int first = readSheet.getFirstRowNum() + 1; int last = readSheet.getLastRowNum(); HSSFRow getRow = null; HSSFCell getCell = null; for (int curIndex = first; curIndex <= last; curIndex++) { // 把excel单元格的内容保存到person对象 Person person = new Person(); getRow = readSheet.getRow(curIndex); // 姓名 getCell = getRow.getCell(0); if (getCell != null) { getCell.setCellType(Cell.CELL_TYPE_STRING); person.setName(getCell.getStringCellValue()); } // 性别 getCell = getRow.getCell(1); if (getCell != null) { // 先设置Cell的类型，然后就可以把纯数字作为String类型读进来了，否则报错：Cannot get a text // value from a numeric cell getCell.setCellType(Cell.CELL_TYPE_STRING); person.setGender(getCell.getStringCellValue()); } // 描述 getCell = getRow.getCell(2); if (getCell != null) { getCell.setCellType(Cell.CELL_TYPE_STRING); person.setDes(getCell.getStringCellValue()); } String currentTime = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(new Date()); person.setLastEnterTime(currentTime);// 入库时间 logger.info("第"+curIndex+"行加载完毕"); // 保存到person表 if (person.getName() == null || person.getName() == "") { failNum = failNum + 1; returnStr = returnStr + "在" + curIndex + "行发生错误[姓名不能为空]<br>"; continue; } if (person.getIdNumber() == null || person.getIdNumber() == ""){ failNum = failNum + 1; returnStr = returnStr + "在" + curIndex + "行发生错误[证件号码不能为空]<br>"; continue; } person.setUpdateTime(currentTime); if (personDAO.saveOrUpdate(person)) { successNum = successNum + 1; } else { failNum = failNum + 1; returnStr = returnStr + "在" + curIndex + "行发生错误[数据库执行失败]<br>"; } } } if(failNum>0){ return "导入成功["+successNum+"]条数据，导入失败["+failNum+"]条数据<br>" + returnStr; }else{ return "ok"; } }
二、导出（将list转成excel文件）
导出可以分为全部导出和批量导出，下面以批量导出为例，直接上后台代码。
1）action层
public String exportByPage(){ result.setResult(JsonResult.SUCCESS); logger.info("接收导出单页Excel请求==start=="+model.toString()); long start = System. currentTimeMillis();//毫秒数 try{ if (model.getStartDate() == null || "".equals(model.getStartDate()) || model.getEndDate() == null || "".equals(model.getEndDate())) { result.setResult(JsonResult.ERROR); result.setErrorMessage("开始时间和结束时间不能为空"); return SUCCESS; } if ("1".equals(model.getIsMatchCamera()) && (model.getMatchCameraList() == null || "".equals(model.getMatchCameraList()))) { result.setResult(JsonResult.ERROR); result.setErrorMessage("匹配的相机列表不能为空"); return SUCCESS; } String basePath = ServletActionContext.getServletContext().getRealPath("/download"); System.out.println(basePath); List<String> cameraIdList = JSONArray.parseArray(model.getMatchCameraList(), String.class); pageInfo.setPageSize(pageSize); pageInfo.setPageNumber(pageNumber); ExportResult exportResult = warningsService.getExcelOutPut(cameraIdList, model, pageInfo, basePath); result.setExportNum(exportResult.getExportNum()); result.setExportTaskID(exportResult.getExportTaskID()); result.setFileURL(exportResult.getFileURL()); result.setResult(exportResult.getResult()); result.setErrorMessage(exportResult.getErrorMessage()); }catch(Exception e){ logger.error("导出单页Excel失败"); result.setResult(JsonResult.ERROR); result.setErrorMessage("导出失败"); e.printStackTrace(); } long end = System. currentTimeMillis();//毫秒数 long time = end - start; logger.info("导出单页Excel==end==所需毫秒数为："+time); return SUCCESS; }
2）service层
@Override public ExportResult getExcelOutPut(List<String> cameraIdList, SearchBean search, PageInfo pageInfo, String path) { ExportResult rm = new ExportResult(); rm.setResult(ResultMessage.SUCCESS); FileOutputStream bos = null; try { // 创建工作簿的实例 HSSFWorkbook workbook = new HSSFWorkbook();// 每个工作表格最多可以存储的条数 int mus = 50000; // 查询出要导出的数据 List<Warnings> datalist = warningsDao.getHistoryWarnings(cameraIdList, search, pageInfo); // 导出多少个excel int avg = (int) datalist.size() / mus; //String uuid = UUID.randomUUID().toString(); String uuid = String.valueOf(System.currentTimeMillis()); HSSFSheet sheet = null; HSSFRow titlerow = null; HSSFCell cell = null; HSSFRow datarow = null; String fileToday = DateUtils.formatTimeYMD(new Date()); //创建目录rootPath String rootPath = path + "/warnings/"+fileToday+"/"; //创建文件夹及删除之前的文件 createAndDelFile(rootPath, path,fileToday,uuid); for (int z = 0; z < avg + 1; z++) { logger.info("创建工作表实例"); // 创建工作表实例 sheet = workbook.createSheet("列表" + (z + 1)); titlerow = null; cell = null; if (datalist.size() > 0) { logger.info("创建表头"); // 创建表头 String[] title = { "监控点编号", "监控点名称", "检测日期时间", "检测类型", "故障类型", "故障描述", "检测码流类型", "视频质量级别", "检测录像保存位置", "检测录像保存天数","录像丢失时段描述", "报警处理结果", "报警处理描述" }; // 创建标题行 titlerow = sheet.createRow(0); for (int i = 0; i < title.length; i++) { // 创建单元格 cell = titlerow.createCell((short) i); /// 为每个单元格赋值 cell.setCellValue(title[i]); } int firstNum = z * mus; int index = 0; //推送进度 sendWebsocketMsg(uuid, "filecopy", (int)70/(avg+1)*(z+1)); logger.info("用数据填充表格,firstNum="+firstNum+";datalist.size()="+datalist.size()); // 用数据填充表格 for (int i = 0; i < datalist.size(); i++) { if (index == mus || (firstNum + i == datalist.size())) { break; } short cos = 0; // 创建数据行 datarow = sheet.createRow(i + 1); // 得到datalist中的第i个对象 Warnings warnings = (Warnings) datalist.get(firstNum + i); setCellValue(warnings, cell, datarow, cos); index++; } // 写入一次文件 logger.info("开始创建告警导出文件"); //推送进度 //sendWebsocketMsg(uuid, "filetrans", (int)70/(avg+1)*(z+1)); String fileName = "warning"+fileToday+"("+z +").xls"; File f = new File(rootPath+uuid+"/", fileName); if (!f.exists()) { f.createNewFile(); } logger.info("开始写入告警文件"); bos = new FileOutputStream(f); workbook.write(bos); bos.flush(); bos.close(); logger.info("完成写入告警文件"); //workbook.removeSheetAt(0); } } // 将生成的文件放入文件夹中 logger.info("开始压缩，告警记录压缩路径："+rootPath+ "zip/warnings_"+uuid+".zip"); ZipCompressorByAnt zip = new ZipCompressorByAnt(rootPath+"zip/warnings_"+uuid+".zip"); zip.compressExe(rootPath+uuid); logger.info("压缩结束"); rm.setFileURL("/videodetection/download"+"/warnings/"+fileToday+"/zip/warnings_"+uuid+".zip"); rm.setExportTaskID(uuid); rm.setExportNum(datalist.size()); sendWebsocketMsg(uuid, "filepack", 100);//推送进度 } catch (Exception e) { logger.error("导出告警信息发生异常:"+e); rm.setResult(ResultMessage.ERROR); rm.setErrorMessage("导出失败"); e.printStackTrace(); } return rm; } //用数据填充表格里每行的cell private void setCellValue(Warnings warnings,HSSFCell cell,HSSFRow datarow,int cos){ cell = datarow.createCell(cos++); cell.setCellValue(warnings.getCameraID()); cell = datarow.createCell(cos++); cell.setCellValue(warnings.getCameraName()); cell = datarow.createCell(cos++); cell.setCellValue(warnings.getDetectDateTime()); cell = datarow.createCell(cos++); //检测类型“real”：实时检测；“rec”：录像检测 if("real".equals(warnings.getDetectType())){ cell.setCellValue("实时检测"); }else if("rec".equals(warnings.getDetectType())){ cell.setCellValue("录像检测"); } cell = datarow.createCell(cos++); cell.setCellValue(warnings.getFaultType()); cell = datarow.createCell(cos++); cell.setCellValue(warnings.getFaultDesc()); cell = datarow.createCell(cos++); //检测码流类型，包括“main”：主码流；“slaver”：从码流 if("main".equals(warnings.getDetectStream())){ cell.setCellValue("主码流"); }else if("slaver".equals(warnings.getDetectStream())){ cell.setCellValue("从码流"); } cell = datarow.createCell(cos++); //视频质量级别：“highest”，“high”，“normal”，“low”，“lowest” cell.setCellValue(getDetectLevel(warnings.getDetectLevel())); cell = datarow.createCell(cos++); //检测录像保存位置，包括“plat”：平台录像；“pu”：前端录像 if("plat".equals(warnings.getDetectRecPositon())){ cell.setCellValue("平台录像"); }else if("pu".equals(warnings.getDetectRecPositon())){ cell.setCellValue("前端录像"); } cell = datarow.createCell(cos++); cell.setCellValue(warnings.getDetectRecDuration()); cell = datarow.createCell(cos++); cell.setCellValue(getRecLostPeriodDesc(warnings.getRecLostPeriod()));//录像丢失时段描述 cell = datarow.createCell(cos++); cell.setCellValue(getWarningHandle(warnings));//报警处理结果 cell = datarow.createCell(cos++); cell.setCellValue(warnings.getWarningHandleDesc()); } //创建文件夹及删除之前的文件 private void createAndDelFile(String rootPath,String path,String fileToday,String uuid){ File file = new File(rootPath); if (!file.exists()) { file.mkdirs(); }else{ //删除昨天及以前的导出文件 deleteDir(new File(path + "/warnings/"), fileToday); } //创建压缩文件文件夹 File fileZip = new File(rootPath+"zip/"); if (!fileZip.exists()) { fileZip.mkdirs(); } //创建某一客户端导出xls文件路径 File fileXLS = new File(rootPath+uuid+"/"); if (!fileXLS.exists()) { fileXLS.mkdirs(); } }
以上就是批量导入导出的代码。看起来是不是也很简单呢，后台框架使用的是struts2,spring,hibernate
欢迎关注微信公众号【Java典籍】，收看更多Java技术干货！
▼微信扫一扫下图↓↓↓二维码关注
