java-导入

由于数据量很多，需要导入这个功能。
需要注意的几个地方
1.实体需要给每个字段填加注释：@ExcelVOAttribute(name = “公司名称”, column = “A”)

@ExcelVOAttribute(name = "公司名称", column = "A")
    private String companyName;

    @ExcelVOAttribute(name = "公司英文名称", column = "B")
    private String enCompanyName;

    @ExcelVOAttribute(name = "招展代理", column = "C")
    private String exhibitorAgent;

与Excel表格相对应

2.前端jsp

$('#excelPath').change(function () {
            var excelPath = $("#excelPath").val();
            if (excelPath == null || excelPath == '') {
                alert("请选择要上传的Excel文件");
                return;
            } else {
                var fileExtend = excelPath.substring(excelPath.lastIndexOf('.')).toLowerCase();
                if (fileExtend == '.xls') {
                    $("#upload").ajaxSubmit({
                        url: "${ctx}/exhibitor/oldcustomer/importOldExhibitorExcel.action",
                        cache: false,
                        dataType: 'json',
                        success: function (ret) {
                            console.log("导入历史展商返回结果：" + ret);
                              layer.msg('导入成功');
                            page();
                            $('#upload')[0].reset()
                        },
                        error: function (ret) {
                            layer.msg(ret.msg);
                            alert("error");
                            $('#upload')[0].reset()
                        }

                    });
                } else {
                    alert("文件格式需为'.xls'格式");
                    return;
                }
            }
        });

后端Controller

//导入历史展商数据
    @RequestMapping(value = "/importOldExhibitorExcel", method = RequestMethod.POST)
    @ResponseBody
    public CommonStatus importOldExhibitorExcel(@RequestParam(value = "excelPath", required = false) MultipartFile file) {
        LOGGER.info("批量导入历史展商");
        boolean isAllow = false;
        String fileName = file.getOriginalFilename();
        int index = fileName.lastIndexOf(".");
        if (index < 0) {
            return faile("非法文件");
        }
        String suffux = fileName.substring(index);
        for (String s : WebConstant.ALLOW_IMPORT) {
            if (s.equals(suffux)) {
                isAllow = true;
                break;
            }
        }
        if (!isAllow) {
            return faile("非法文件");
        }

        try {
            FileInputStream fis = (FileInputStream) file.getInputStream();
            ExcelUtil<TOldCustomer> util = new ExcelUtil<TOldCustomer>(TOldCustomer.class);
            List<TOldCustomer> list = util.importExcel("Sheet1", fis);
            for (TOldCustomer c : list) {
                if (c == null) {
                    continue;
                }

                boolean switchFlag = false;

                String url = PropertyPlaceholder.getProperty("domain");
                String path = PropertyPlaceholder.getProperty("img_dir");

            }
            for (int i = 0; i < list.size(); i++) {

                TOldCustomer oldCustomer = list.get(i);
                oldCustomerService.insertSelective(oldCustomer);

            }


        } catch (IOException e) {
            LOGGER.info("导入excel异常e={}", e);
        }

        String path = request.getSession().getServletContext().getRealPath("positionexcel");
        SimpleDateFormat sdf = new SimpleDateFormat("/yyyy/MM/dd/");
        String urlnew = sdf.format(new Date());
        fileName = urlnew + fileName;
        File targetFile = new File(path, fileName);
        if (!targetFile.exists()) {
            targetFile.mkdirs();
        }


        // 保存
        try {
            file.transferTo(targetFile);

        } catch (Exception e) {
            e.printStackTrace();
            LOGGER.error("保存文件失败e=", e);
        }

        return success("导入成功");
    }

4.同时用到了Excel工具类，ExcelUtil.java

	public   List<T> importExcel(String sheetName, InputStream input) {
		int maxCol = 0;
		List<T> list = new ArrayList<T>();
		try {
			HSSFWorkbook workbook = new HSSFWorkbook(input);
			HSSFSheet sheet = workbook.getSheet(sheetName);
			if (!sheetName.trim().equals("")) {
				sheet = workbook.getSheet(sheetName);// 如果指定sheet名,则取指定sheet中的内容.
			}
			if (sheet == null) {
				sheet = workbook.getSheetAt(0); // 如果传入的sheet名不存在则默认指向第1个sheet.
			}
			int rows = sheet.getPhysicalNumberOfRows();

			if (rows > 0) {// 有数据时才处理
				// Field[] allFields = clazz.getDeclaredFields();// 得到类的所有field.
				List<Field> allFields = getMappedFiled(clazz, null);

				Map<Integer, Field> fieldsMap = new HashMap<Integer, Field>();// 定义一个map用于存放列的序号和field.
				for (Field field : allFields) {
					// 将有注解的field存放到map中.
					if (field.isAnnotationPresent(ExcelVOAttribute.class)) {
						ExcelVOAttribute attr = field.getAnnotation(ExcelVOAttribute.class);
						int col = getExcelCol(attr.column());// 获得列号
						maxCol = Math.max(col, maxCol);
						// System.out.println(col + "====" + field.getName());
						field.setAccessible(true);// 设置类的私有字段属性可访问.
						fieldsMap.put(col, field);
					}
				}
			
				for (int i = 1; i < rows; i++) {// 从第2行开始取数据,默认第一行是表头.
					HSSFRow row = sheet.getRow(i);
					// int cellNum = row.getPhysicalNumberOfCells();
					// int cellNum = row.getLastCellNum();
					int cellNum = maxCol;
					T entity = null;
					//System.err.println("cellNum="+cellNum);
					for (int j = 0; j <= cellNum; j++) {
						//System.err.println("j="+j);
						if (row==null) {
							continue;
						}
						HSSFCell cell = row.getCell(j);
						if (cell == null) {
							continue;
						}
						int cellType = cell.getCellType();
						String c = "";
						Integer val;
						//System.err.println("cellType="+cellType);
						if (cellType == HSSFCell.CELL_TYPE_NUMERIC) {
							val=(int)cell.getNumericCellValue();
							c = String.valueOf(val);
						} else if (cellType == HSSFCell.CELL_TYPE_BOOLEAN) {
							c = String.valueOf(cell.getBooleanCellValue());
						} else {
							c = cell.getStringCellValue();
						}
						//System.err.println("c="+c);
						if (c == null || c.equals("")) {
							continue;
						}
						
						entity = (entity == null ? clazz.newInstance() : entity);// 如果不存在实例则新建.
						// System.out.println(cells[j].getContents());
						Field field = fieldsMap.get(j);// 从map中得到对应列的field.
						if (field == null) {
							continue;
						}
						// 取得类型,并根据对象类型设置值.
						Class<?> fieldType = field.getType();
						//System.err.println("fieldType="+fieldType.toString());
						if (String.class == fieldType) {
							field.set(entity, String.valueOf(c));
						} else if ((Integer.TYPE == fieldType) || (Integer.class == fieldType)) {
							field.set(entity, Integer.valueOf(c));
						} else if ((Long.TYPE == fieldType) || (Long.class == fieldType)) {
							field.set(entity, Long.valueOf(c));
						} else if ((Float.TYPE == fieldType) || (Float.class == fieldType)) {
							field.set(entity, Float.valueOf(c));
						} else if ((Short.TYPE == fieldType) || (Short.class == fieldType)) {
							field.set(entity, Short.valueOf(c));
						} else if ((Double.TYPE == fieldType) || (Double.class == fieldType)) {
							field.set(entity, Double.valueOf(c));
						} else if (Character.TYPE == fieldType) {
							if ((c != null) && (c.length() > 0)) {
								field.set(entity, Character.valueOf(c.charAt(0)));
							}
						}else{
							System.out.println("未知的字段数据类型fieldType="+fieldType.getClass().toString());
						}

					}
					if (entity != null) {
						list.add(entity);
					}
				}
			}

		} catch (IOException e) {
			e.printStackTrace();
		} catch (InstantiationException e) {
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			e.printStackTrace();
		} catch (IllegalArgumentException e) {
			e.printStackTrace();
		}
		return list;
	}