POI Excel文件的导出

前提提要：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();
		}
	}

代码中存在一些业务逻辑，不是单纯的导入导出，如果需要使用到其他地方，需要修改其中的逻辑判断和处理