导入excel文件

1-前端框架EasyUI,导入弹出框样式

<!--导入弹出框  -->
	<div id="imp" class="easyui-dialog" data-options="modal:true"
		title="导入" style="width: 300px; height: 250px; padding: 5px 2px"
		closed="true">
		<div align="center" style="margin-top: 10px;">
			<span>导入模板：</span>
			<td><a
				href="<%=this.getServletContext().getContextPath()%>/xls/Price.xls">Price</a></td>
		</div>
		<div align="center" style="margin-top: 20px;">

			<form id="impFile" method="post" enctype="multipart/form-data">
				<p>
					<input id="importExcel" name="importExcel" type="file"
						class="easyui-filebox" style=" border:1px solid #c0c0c0;" />
				</p>
				<div align="center" style="margin-top: 20px;">
					<span>请选择价格信息年份：</span> <input id="query_combobox"
						class="easyui-combobox" name="queryCombobox" style="width:106px;"></input>
				</div>

			</form>
		</div>

2-js代码验证导入信息：根据自己需要判断的逻辑和后台返回的数据去验证

function impExcel(){
	var year=$("#query_combobox").combobox('getValue');
	var importFile=$("#importExcel").val();
	if(importFile==''){
		$.messager.alert('提示',"请选择导入文件");
		return false;
	}
	if(year==''){
		$.messager.alert('提示',"请选择价格年份");
		return false;
	}
    var reg=".xls$|.xlsx$";
    var patrn=new RegExp(reg);
    //匹配文件后缀
    if(patrn.exec(importFile)){
        $("#impFile").form('submit',{
            url:'import?year='+year,
            onSubmit:function(){

            },
            success:function(data){
                data=eval('('+data+')');
                var flag=data.flag;
                alert(flag);
                if(flag){
                    $("#imp").window("close");
                    $.messager.alert('提示',"导入成功！");
                    All();
                }else{
                    $("#imp").window("close");
                    $.messager.alert('提示',"批量新增失败！请核对导入的信息是否是选择的年份信息！");
                    All();
                }
            }
        });
    }else{
        $.messager.alert("提示","请导入.xls形式的Excel!");
        return false;
    }  
}

3-后台controller

	@RequestMapping("/import")
	@onMethod(remark="价格信息导入")
	public void fileUpload(HttpServletRequest request, HttpServletResponse response,String year) throws IOException {
		PrintWriter out = response.getWriter();
		Workbook wb = getWorkbookFromRequest(request, response);
		Map<String, Object> map = tPriceMainService.importExcel(wb,year);

		JSONObject json = new JSONObject(map);
		String jsonString = json.toJSONString();
		out.print(jsonString);
		out.flush();
		out.close();
	}

	private Workbook getWorkbookFromRequest(HttpServletRequest request, HttpServletResponse response)
			throws IOException {
		InputStream is = null;
		response.setCharacterEncoding("UTF-8");
		response.setContentType("text/json;charset=UTF-8");
		PrintWriter out = response.getWriter();
		CommonsMultipartResolver multipartResolver = new CommonsMultipartResolver(
				request.getSession().getServletContext());
		if (multipartResolver.isMultipart(request)) {
			MultipartHttpServletRequest multiRequest = (MultipartHttpServletRequest) request;
			Iterator<String> iterator = multiRequest.getFileNames();
			while (iterator.hasNext()) {
				MultipartFile multipartFile = multiRequest.getFile(iterator.next());
				if (multipartFile != null) {
					is = new ByteArrayInputStream(multipartFile.getBytes());
				}
			}
		}
		Workbook wb = new HSSFWorkbook(is);
		return wb;
	}

4-导入实现类：根据自己的需求，在里面ha一下代码，删除花里胡哨的。

	public Map<String, Object> importExcel(Workbook wb, String year) {
		Map<String, Object> map = new HashMap<String, Object>();
		List<TPriceMain> list = new ArrayList<TPriceMain>();
		Sheet sheet = wb.getSheetAt(0);
		// 获取总行数
		int rows = sheet.getPhysicalNumberOfRows();
		for (int start = 1; start < rows; start++) {
			// 从第二行开始
			Row row = sheet.getRow(start);
			if(row.getCell(0).toString()==null||"".equals(row.getCell(0).toString())){
				break;
			}
			System.out.println(row.getCell(0).toString().substring(0, 4));
			if (!row.getCell(0).toString().substring(0, 4).equals(year)) {
				map.put("flag", false);
				return map;
			} else {
				TPriceMain tPriceMain = new TPriceMain();
				for (int i = 0; i < 15; i++) {
					Cell cell = row.getCell(i);
					String cellValue = getCellValue(cell);
						switch (i) {
						case 0:
							if (cellValue == null || "".equals(cellValue)) {
								tPriceMain.setYear(null);
							} else {
								tPriceMain.setYear(cellValue);
							}
							break;
						case 1:
							if (cellValue == null || "".equals(cellValue)) {
								tPriceMain.setValidityDate(null);
							} else {
								tPriceMain.setValidityDate(cellValue);
							}
							break;
						case 2:
							if (cellValue == null || "".equals(cellValue)) {
								tPriceMain.setSendingDateFirmorder(null);
							} else {
								tPriceMain.setSendingDateFirmorder(cellValue);
							}
							break;
						case 3:
							if (cellValue == null || "".equals(cellValue)) {
								tPriceMain.setVehicleDescription(null);
							} else {
								tPriceMain.setVehicleDescription(cellValue);
							}
							break;
						case 4:
							if (cellValue == null || "".equals(cellValue)) {
								tPriceMain.setVehicleDescriptionI(null);
							} else {
								tPriceMain.setVehicleDescriptionI(cellValue);
							}
							break;
						case 5:
							if (cellValue == null || "".equals(cellValue)) {
								tPriceMain.setListOfVehicles(null);
							} else {
								tPriceMain.setListOfVehicles(cellValue);
							}
							break;
						case 6:
							if (cellValue == null || "".equals(cellValue)) {
								tPriceMain.setSpeficicOperations(null);
							} else {
								tPriceMain.setSpeficicOperations(cellValue);
							}
							break;
						case 7:
							if (cellValue == null || "".equals(cellValue)) {
								tPriceMain.setListVehiclesAttributesmax(null);
							} else {
								tPriceMain.setListVehiclesAttributesmax(cellValue);
							}
							break;
						case 8:
							if (cellValue == null || "".equals(cellValue)) {
								tPriceMain.setVovrType(null);
							} else {
								tPriceMain.setVovrType(cellValue);
							}
							break;
						case 9:
							if (cellValue == null || "".equals(cellValue)) {
								tPriceMain.setVoType(null);
							} else {
								tPriceMain.setVoType(cellValue);
							}
							break;
						case 10:
							if (cellValue == null || "".equals(cellValue)) {
								tPriceMain.setPlantCode(null);
							} else {
								tPriceMain.setPlantCode(cellValue);
							}
							break;
						case 11:
							if (cellValue == null || "".equals(cellValue)) {
								tPriceMain.setOriginType(null);
							} else {
								tPriceMain.setOriginType(cellValue);
							}
							break;
						case 12:
							if (cellValue == null || "".equals(cellValue)) {
								tPriceMain.setVehicleCodificationCqca(null);
							} else {
								tPriceMain.setVehicleCodificationCqca(cellValue);
							}
							break;
						case 13:
							if (cellValue == null || "".equals(cellValue)) {
								tPriceMain.setPrice(null);
							} else {
								tPriceMain.setPrice(new BigDecimal(cellValue));
							}
							break;
						case 14:
							if (cellValue == null || "".equals(cellValue)) {
								tPriceMain.setPriceYear(null);
							} else {
								tPriceMain.setPriceYear(cellValue);
							}
							break;
						default:
							break;
						}
					}
				if (tPriceMain != null) {
					list.add(tPriceMain);
				}
			}
		}
		for (TPriceMain tp : list) {
			TPriceMainExample example = new TPriceMainExample();
			Criteria cr = example.createCriteria();
			cr.andYearEqualTo(tp.getYear());
			cr.andVehicleCodificationCqcaEqualTo(tp.getVehicleCodificationCqca());
			int count = tPriceMainMapper.countByExample(example);
			if (count == 0) {
				tPriceMainMapper.insertSelective(tp);
			} else {
				tPriceMainMapper.updateByExample(tp, example);
			}
		}
		map.put("flag", true);
		return map;
	}

	private String getCellValue(Cell cell) {
		String result = "";
		if (cell != null) {
			switch (cell.getCellType()) {
			// 数字类型 +日期类型
			case HSSFCell.CELL_TYPE_NUMERIC:
				if (HSSFDateUtil.isCellDateFormatted(cell)) {// 处理日期格式、时间格式
					SimpleDateFormat sdf = null;
					if (cell.getCellStyle().getDataFormat() == HSSFDataFormat.getBuiltinFormat("h:mm")) {
						sdf = new SimpleDateFormat("HH:mm");
					} else {// 日期
						sdf = new SimpleDateFormat("yyyy-MM-dd");
					}
					Date date = cell.getDateCellValue();
					result = sdf.format(date);
				} else if (cell.getCellStyle().getDataFormat() == 58) {
					// 处理自定义日期格式：m月d日(通过判断单元格的格式id解决，id的值是58)
					SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
					double value = cell.getNumericCellValue();
					Date date = org.apache.poi.ss.usermodel.DateUtil.getJavaDate(value);
					result = sdf.format(date);
				} else {
					DecimalFormat df = new DecimalFormat();
					df.setGroupingUsed(false);
					result = String.valueOf(df.format(cell.getNumericCellValue()));
				}
				break;
			// String类型
			case HSSFCell.CELL_TYPE_STRING:
				result = String.valueOf(cell.getStringCellValue());
				break;
			case HSSFCell.CELL_TYPE_BLANK:
				result = "";
			default:
				result = "";
				break;
			}
		}
		return result;
	}