前提提要：excel 2003只能存在5w6多条数据，数据超出使用2007（槽点：excel中超过5w多行数据也是没谁了，毕竟excel是用来给人看的，真正保存哪里有数据库好使）
话不多说，这段代码我先干了
@RequestMapping(value = "/importPropertyAvgPriceRecord.do", method = RequestMethod.POST) @ResponseBody public Result importPropertyAvgPriceRecord(@RequestParam("fileName") CommonsMultipartFile[] files,Model model, HttpSession session,HttpServletRequest request) { Result res = new Result(); City city = (City) session.getAttribute("city"); try { SimpleDateFormat df = new SimpleDateFormat("yyyyMMddHHmmss");// 设置日期格式 String strDate = "-" + df.format(new Date()); for (int i = 0; i < files.length; i++) { File newFile = null; String fileName = files[i].getOriginalFilename(); fileName = fileName.substring(0, fileName.lastIndexOf(".xls")) + strDate + fileName.substring(fileName.lastIndexOf(".xls")); //这个校验很粗暴 if (!fileName.contains("xls")) { res.setMsg("导入的文件格式暂时只支持.xls格式"); return res; } try { FileManager.saveFileFromInputStream(files[i].getInputStream(), request.getSession().getServletContext().getRealPath("/upload"), fileName); newFile = new File( request.getSession().getServletContext().getRealPath("/upload") + "/" + fileName); } catch (Exception ex) { res.setMsg("上传文件失败"); return res; } res.setMsg(propertyPriceRecordService.addExcelData(newFile, city, city.getId())); } } catch (Exception e) { this.handleException(e, res); } return res; } 以上为controller中的code
以下为service中的code
@Override public String addExcelData(File file, City city, Long cityId) { if (cityId != city.getId()) { return "城市不匹配"; } //查询出导入价格源列表 AvgPriceSourceExample example11 = new AvgPriceSourceExample(); com.ulink.zgzf.biz.price.city.model.AvgPriceSourceExample.Criteria c = example11.createCriteria(); c.andCityIdEqualTo(cityId); c.andPriceTypeEqualTo(4); List<AvgPriceSource> pps = propertyPriceSourceService.selectByExample(example11); if(pps==null||pps.size()==0){ return "该城市没有导入价格源"; } String[] priceSource = new String[pps.size()]; for (int i = 0; i < pps.size(); i++) { priceSource[i] = pps.get(i).getName(); } File excelFile = file;// Excel文件对象 InputStream is = null;// 输入流对象 StringBuffer strMsg = new StringBuffer(); StringBuffer strMsg2 = new StringBuffer(); int x = 0; int y = 0; int errorCount = 0; Workbook wb = null; Sheet sheet = null; String lstStrValue[] = { "城市", "楼盘名", "价格来源", "价格", "价格时点" }; // 补全城区id,入库时间,楼盘编码,城市id List<String> userList = new ArrayList<String>(); Collections.addAll(userList, lstStrValue); SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd"); String cityName = city.getName(); Long ccityId = city.getId(); try { is = new FileInputStream(excelFile); if (excelFile.getName().indexOf(".xlsx") > 0) { wb = new XSSFWorkbook(is); } else if (excelFile.getName().indexOf(".xls") > 0) { wb = new HSSFWorkbook(is); } sheet = wb.getSheetAt(0); strMsg.append("excle中总共" + sheet.getLastRowNum() + "条数据\n"); // 标题总列数 int colNum = sheet.getRow(0).getPhysicalNumberOfCells(); Property property = new Property(); PropertyExample example = new PropertyExample(); com.ulink.zgzf.biz.bd.property.model.PropertyExample.Criteria query = example.createCriteria(); query.andCityIdEqualTo(ccityId); for (int j = 0; j < colNum; j++) { String strValue = getValue(sheet.getRow(0).getCell(j)); if (!userList.contains(strValue)) { strMsg2.append("第" + j + "列数据项“" + strValue + "”未能与模板匹配; \r\n"); errorCount++; return "第" + j + "列数据项“" + strValue + "”未能与模板匹配; \r\n"; } } logger.info("开始导入数据"); for (int i = 1; i <= sheet.getLastRowNum(); i++) { example.clear(); query = example.createCriteria(); query.andCityIdEqualTo(ccityId); PropertyPriceRecord propertyPriceRecord = new PropertyPriceRecord(); Row row = sheet.getRow(i);// 获取行对象 if (row == null) { continue; } // 获得楼盘对象 从楼盘对象中取到城区id和其他数据 query.andNameEqualTo(getValue(row.getCell(1))); List<Property> propertyList = propertyService.selectByExample(example); if (propertyList.size() == 0) { strMsg2.append("系数表里第" + i + "行" + "\"楼盘找不到,该项数据被忽略\r\n"); errorCount++; continue; } property = propertyList.get(0); for (int j = 0; j < colNum; j++) { String strValue = getValue(sheet.getRow(0).getCell(j)); switch (strValue) { case "城市": String value = getValue(row.getCell(j)); if (value != null && !value.equals("")) { if (value.equals(cityName)) { propertyPriceRecord.setCityId(ccityId); propertyPriceRecord.setCityName(cityName); }else{ strMsg2.append("表里第" + i + "行第" + (j + 1) + "列数据内容\"" + value + "\"不是对应城市名,该项数据被忽略\r\n"); errorCount++; } } break; case "楼盘名": value = getValue(row.getCell(j)); if (value != null && !value.equals("")) { propertyPriceRecord.setPropertyName(value); propertyPriceRecord.setPropertyId(property.getId()); propertyPriceRecord.setPropertyCode(property.getPropertySystemCode()); } break; case "价格时点": value = getValue(row.getCell(j)); if (value != null && !value.equals("")) { try { Date date = sdf.parse(value); if(date.before(new Date())){ propertyPriceRecord.setPriceDate(date); }else{ strMsg2.append("表里第" + i + "行第" + (j + 1) + "列数据内容\"" + value + "\"价格时点异常,该项数据被忽略\r\n"); errorCount++; } } catch (ParseException e) { e.printStackTrace(); strMsg2.append("表里第" + i + "行第" + (j + 1) + "列数据内容\"" + value + "\"不是Date类型,该项数据被忽略\r\n"); errorCount++; } } break; case "价格": value = getValue(row.getCell(j)); if (Validation.isDouble(value) || Validation.isInteger(value)) { propertyPriceRecord.setPrice(Double.valueOf(value)); } else { strMsg2.append("表里第" + i + "行第" + (j + 1) + "列数据内容\"" + value + "\"不是double类型,该项数据被忽略\r\n"); errorCount++; } break; case "价格来源": value = getValue(row.getCell(j)); if (value != null && !value.equals("")) { if(Arrays.asList(priceSource).contains(value)){ propertyPriceRecord.setPriceSource(value); for (AvgPriceSource avgPriceSource : pps) { if(avgPriceSource.getName().equals(value)){ propertyPriceRecord.setPriceSourceId(avgPriceSource.getId()); } } }else{ strMsg2.append("表里第" + i + "行第" + (j + 1) + "列数据内容\"" + value + "\"不是该城市设置的楼盘导入价格来源\r\n"); errorCount++; } } break; } } if (propertyPriceRecord.getCityId() == null || propertyPriceRecord.getPriceDate() == null || propertyPriceRecord.getPrice() == null || propertyPriceRecord.getPriceSource() == null) { strMsg2.append("第" + i + "行数据导入错误"); errorCount++; continue; } // 将楼盘表的剩余字段进行赋值 propertyPriceRecord.setDistrictId(property.getDistrictId()); propertyPriceRecord.setDistrictName(property.getDistrictName()); propertyPriceRecord.setTargetType(property.getPropertyTargetType()); propertyPriceRecord.setCreateTime(new Date()); // 进行去重校验 PropertyPriceRecordExample example1 = new PropertyPriceRecordExample(); Criteria query1 = example1.createCriteria(); query1.andCityIdEqualTo(propertyPriceRecord.getCityId()); query1.andPropertyIdEqualTo(propertyPriceRecord.getPropertyId()); query1.andPriceDateEqualTo(propertyPriceRecord.getPriceDate()); query1.andPriceSourceEqualTo(propertyPriceRecord.getPriceSource()); List<PropertyPriceRecord> recordList = mapper.selectByExample(example1); try { propertyPriceRecord.setPrice(Double .valueOf(new java.text.DecimalFormat("#0.00").format(propertyPriceRecord.getPrice()))); } catch (Exception e) { logger.error("价格简写为两位失败：" + e); } if (recordList.size() == 0) { this.insertSelective(propertyPriceRecord,"导入价格数据"); x++; } else { propertyPriceRecord.setId(recordList.get(0).getId()); this.updateByPrimaryKeySelective(propertyPriceRecord); y++; } logger.info("总共" + sheet.getLastRowNum() + "条数据，已导入" + i + "条数据"); } } catch (IOException e) { e.printStackTrace(); return "文件读取错误"; } finally { if (is != null) { try { is.close(); } catch (IOException e) { e.printStackTrace(); } } } strMsg.append("已成功导入" + x + "条数据\r\n"); if (y > 0) { strMsg.append("更新数据" + y + "条数据\r\n"); } strMsg.append(strMsg2); String strErrorPath = SysParameterUtil.getPropertyName("errer_log_url").getParamValue(); File txt = new File(strErrorPath + "/importPropertyPriceRecordErrorLog.txt"); if (errorCount > 10) { PrintStream ps; try { if (!new File(strErrorPath).exists()) { new File(strErrorPath).mkdirs(); } if (txt.exists()) { txt.delete(); } ps = new PrintStream(new FileOutputStream(txt)); ps.println(strMsg.toString()); ps.flush(); ps.close(); } catch (FileNotFoundException e) { e.printStackTrace(); } } logger.info("数据导入完成"); if (errorCount > 10) { return SysParameterUtil.getPropertyName("server_url").getParamValue() + "/bd/property/getErrorLog.do" + "?logPath=" + txt.getPath(); } else { return strMsg.toString(); } } 代码中存在一些业务逻辑，不是单纯的导入导出，如果需要使用到其他地方，需要修改其中的逻辑判断和处理
