javaExcel文件上传和下载

上传在页面必须加上下面属性

<form method="post" enctype="multipart/form-data" target="frameFile" action="${vehiclePath }/bindVehicle?${_csrf.parameterName}=${_csrf.token}&userId=${condition.userId }"> 后台方法

@RequestMapping(value="bindVehicle") @PreAuthorize(value=SecuritySettings.DENY_ON_READ_ONLY_ENV) public void bindVehicle(@RequestParam("vbfile") MultipartFile file, @RequestParam Long userId, HttpServletResponse response, HttpServletRequest request) throws IOException { String status = ApplicationKeys.SUCC; String message = ""; PrintWriter out = response.getWriter(); log.info("bindVehicle::::::::::::::::::::"); boolean failure=false; if(!file.getOriginalFilename().endsWith(".xlsx") && !file.getOriginalFilename().endsWith(".xls") ) { status = ApplicationKeys.FAIL; message = "onlyExcel"; failure=true; }else { try { List<VehicleBatchBindingDto> vehiclebinds = this.buildVehicleBinds(file); if(vehiclebinds.size() > 50) { status = ApplicationKeys.FAIL; message = "excelLengthError"; failure=true; }else { List<VehicleBindResultDto> results = this.vehiclebtServiceImpl.bindVehicleTwo(vehiclebinds, userId); SessionUtils.setAttribute(request, ApplicationKeys.BIND_RESULTS, (ArrayList<VehicleBindResultDto>)results); } } catch (Exception e) { // TODO: handle exception e.printStackTrace(); status = ApplicationKeys.FAIL; message = "error"; failure=true; } } OperationUtil.adminLog(userId, OperationKeys.UPLOAD_VEHICLE_EXCEL, null, failure?StatusKeys.FAILURE:StatusKeys.SUCCESS, null, OperationTypeKeys.VEHICLE_MGMT, message); response.setContentType("text/html;charset=UTF-8"); out.println("<script name='testsp'>parent.bindVehicleCallback('"+status+"','"+message+"')</script>");}

public List<VehicleBatchBindingDto> buildVehicleBinds(MultipartFile file) throws IOException { List<VehicleBatchBindingDto> vehiclebinds = new ArrayList<VehicleBatchBindingDto>(); VehicleBatchBindingDto vb = null; //不能超过16列 InputStream is = new ByteArrayInputStream(file.getBytes()); Workbook workbook = new XSSFWorkbook(is); Sheet sheet = workbook.getSheetAt(0); int coloumNum=sheet.getRow(0).getPhysicalNumberOfCells();//总列数 int rowNum=sheet.getLastRowNum();//总行数 if(coloumNum!=19||rowNum<1){ throw new RuntimeException("Excel Row Or Coloum size is error!"); }else{ for(int j=0;j<coloumNum;j++){ Row row = sheet.getRow(0); if(row.getCell(j)==null){ throw new RuntimeException("Excel Row Or Coloum size is error!"); } } for (int i = 1; i <= sheet.getLastRowNum(); i++) { Row row = sheet.getRow(i); if(isEmptyRow(row)) { continue; } //acc_no acct_province acct_city longitude经度 latitude radius vb = new VehicleBatchBindingDto(); vb.setUsername(this.getCellValue(row.getCell(0))); vb.setFleetname(this.getCellValue(row.getCell(1))); vb.setVin(this.getCellValue(row.getCell(2)));//车架号 vb.setPlateId(this.getCellValue(row.getCell(3))); vb.setModel(this.getCellValue(row.getCell(4))); vb.setDriver(this.getCellValue(row.getCell(5))); vb.setEmail(this.getCellValue(row.getCell(6))); vb.setTelephone(this.getCellValue(row.getCell(7)));//报警接受手机号码 vb.setPackagecode(this.getCellValue(row.getCell(8)).replaceAll("，", ",")); String std=this.getCellValue(row.getCell(9)); String edd=this.getCellValue(row.getCell(10)); if(null!=std&&!"".equals(std)){ vb.setDocked_stdate(std); } if(null!=edd&&!"".equals(edd)){ vb.setDocked_eddate(edd); } vb.setOperationtype(this.getCellValue(row.getCell(11))); String rdv=StringUtil.isNullToEmpty(this.getCellValue(row.getCell(12))); if(rdv.contains("KM")||rdv.contains("km")||rdv.contains("Km")||rdv.contains("kM")){ vb.setRadius(rdv.replace("km", "KM")); }else{ if(!rdv.equals("")){ vb.setRadius(rdv+"KM"); }else{ vb.setRadius("0KM"); } } vb.setLongitude(this.getCellValue(row.getCell(13))); vb.setLatitude(this.getCellValue(row.getCell(14))); vb.setAlertphonenumber(this.getCellValue(row.getCell(15))); vb.setAccno(this.getCellValue(row.getCell(16))); vb.setFranchisercode(this.getCellValue(row.getCell(17))); vb.setFranchisername(this.getCellValue(row.getCell(18))); vehiclebinds.add(vb); } } log.info("vehiclebinds size :::: "+vehiclebinds.size()); return vehiclebinds; } 下载 

HSSFWorkbook wb = this.vehicleBindingServiceImpl.createExl(results,where,startd, endd); response.setContentType("application/vnd.ms-excel"); if(tp.equals("5")){ response.setHeader("Content-disposition", "attachment;filename=user_operationLog.xls"); }else{ response.setHeader("Content-disposition", "attachment;filename=fleet_list.xls"); } OutputStream ouputStream = null ; try { response.flushBuffer(); ouputStream = response.getOutputStream(); wb.write(ouputStream); } catch (IOException e) { mes = e.getMessage(); failure = true; }finally{try { ouputStream.flush(); ouputStream.close(); } catch (IOException e) { mes = e.getMessage(); failure = true; }}}下面是createexl方法 int[] excelHeaderWidths = {150,150,150,150,150,150,150,150, 180, 100, 100, 100, 150,200}; int[] excelHeaderWidth=null; String[] excelHeader={}; String[] waitlist = {"编号","车架号","车型","车牌","使用人","联系方式","公司名称","绑定时间","操作状态"}; excelHeader=all; excelHeaderWidth=excelHeaderWidths; HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet = wb.createSheet("sheet1"); HSSFRow row = sheet.createRow((int) 0); HSSFCellStyle style = wb.createCellStyle(); HSSFFont font = wb.createFont(); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); style.setFont(font); style.setAlignment(HSSFCellStyle.ALIGN_CENTER); HSSFCellStyle rowstyle = wb.createCellStyle(); rowstyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); for (int i = 0; i < excelHeader.length; i++) { HSSFCell cell = row.createCell(i); cell.setCellValue(excelHeader[i]); cell.setCellStyle(style); sheet.setColumnWidth(i, 32 * excelHeaderWidth[i]); } for(int i=0;i<vehiclelist.size();i++){ row=sheet.createRow(i+1); VehicleBadingListDto vh=vehiclelist.get(i); row.createCell(0).setCellValue(i); row.createCell(1).setCellValue(vh.getVIN()+""); } return wb; }