ssm页面导入文件并做校验

导入文件详细步骤

一、搭建是ssm环境（略）

二、jsp内容（表格模板的存放位置就是下载模板的位置）

定义一个名为groupCateList.jsp的文件

类目导入

类目导入

<div class="addpop_cont pdlr-0">
    <div class="page" style="padding-top:30px;">
        <div class="fixed-bar">
            <div class="item-title">
                <div class="subject">
                    <h3>
                        选择导入文件:<a href="${ctxStatic}/plat2016/templete/groupCategoryDome.xlsx"
                                  style="color: #3b639f">【类目导入模板下载.xlsx】</a>
                    </h3>
                </div>
            </div>
        </div>

        <form id="importDataList" class="form-inline"
              action="" method="post"
              modelAttribute="entityBusi" enctype="multipart/form-data">
            <div class="row">
                <div class="form-group col-lg-6">
                    <div class="col-sm-9">
                        <input type="file" id="file" name="file" class="input-n4 fl w-input262"/>
                    </div>
                </div>
                <div class="form-group col-lg-12">
                    <div class="col-sm-10" id="showErrorMessage"></div>
                </div>
            </div>
        </form>
        <div class="modal-footer" style="padding-top:20px;">
            <input type="button" value="取消" class="btn-gray-outline " onclick="closeImportPop()">
            <input type="button" value="确认" class="btn-yellow " onclick="excelFileImport('file');">
        </div>
    </div>
</div>

### 三、JavaScript内容

 //导入数据
        function importExcel() {
            $("#importPop").removeClass("dis-none");
            $("#importPop").show();
        }

        function checkExcelFile(fileId) {
            var filepath = $("#" + fileId).attr("value");
            filepath = filepath.substring(filepath.lastIndexOf('.') + 1, filepath.length);
            filepath = filepath.toLowerCase();
            if (filepath == '' || filepath == null) {
                $("#showErrorMessage").text("请选择导入数据文件");
                return false;
            }
            if (filepath != 'xlsx' && filepath != 'xls') {
                $("#showErrorMessage").text("请下载对应模板填写数据");
                return false;
            }
            return true;
        }

        function closeImportPop() {
            $("#importPop").hide();
            $("#importPop").addClass("dis-none");
        }

        function excelFileImport(fileId) {
            if (checkExcelFile(fileId)) {
                $("#importDataList").attr("action", "${ctx}/group/cate/importData");
                loading('正在提交，请稍等...');
                $("#importDataList").submit();
            }
        }

四、实体类内容

import java.util.Date;
import java.util.LinkedHashMap;

import com.ai.ecs.common.utils.excel.annotation.ExcelField;
import com.ai.iis.domain.pojo.base.DataEntity;

public class GroupCategory  {

    private static final long serialVersionUID = -8991882881883173222L;

    @ExcelField(title="类目Id",sort=1)
    private String cateId;//类目id

    @ExcelField(title="父级类目id",sort=3)
    private String parentId;//父级id
    private String parentIds;
    @ExcelField(title="层级",sort=4)
    private String cateLevel;//层级

    @ExcelField(title="类目名称",sort=2)
    private String cateName;//类目名称

}

然后给实体类添加get和set方法，再添加tostring和hashcode方法，直接鼠标右键然后选择generate,然后选择添加这些方法就可以，后者使用快捷键alt+insert，快速选择就OK
@ExcelField(title=“类目名称”,sort=2)这个注解必须要添加，

五、控制器controller

1、控制器第一步必须要有个ModelAndView返回到前面写的jsp中，

@Controller
@RequestMapping(value = "${adminPath}/group/cate/")
public class GroupCategoryController  {

    @Autowired
    private IGroupCategoryService groupCategoryService;

    @Autowired
    private IGroupCategoryGoodsService groupCategoryGoodsService;

    /*
     * 初始化类目列表页面，递归查询
     */

    @RequestMapping(value = "initPage")
    public ModelAndView initPage(GroupCategory groupCategory, HttpServletRequest request, HttpServletResponse response) {
        ModelAndView mav = new ModelAndView("modules/group/cate/groupCateList");
        //查询类目树形结构
        List<GroupCategory> groupCategoryList = groupCategoryService.queryAllGroupCategoryList();
        for (int i = 0; i < groupCategoryList.size(); i++) {//剔除状态为0或者删除过的数据
            if (groupCategoryList.get(i).getCateStatus().equals("0")
                    || groupCategoryList.get(i).getDelFlag().equals("1")) {
                groupCategoryList.remove(i);
                i--;
            }
        }
       /* GroupCategory info = new GroupCategory();
        info.setCateId("0");
        info.setCateLevel("0");//层级
        info.setCateName("类目");//类目名称
        info.setCateSort("0");
        groupCategoryList.add(info);*/
        mav.addObject("groupCategoryList", groupCategoryList);
        return mav;
    }

第二步，写导入类目的控制器，在这里需要返回到导入之后的结果，而且需要一个校验表格的类，首先，先写导入之后的页面groupCateImport.jsp

<%@ page contentType="text/html;charset=UTF-8"%>
<%@ include file="/WEB-INF/views/include/taglib.jsp"%>
<html>
<head>
    <title>类目导入结果</title>
    <%@ include file="/WEB-INF/views/mod2016/include/head.jsp"%>
</head>
<body class="bg-gray">
<div id="tabchunk">
    <div class="recommend-s1  bg-white mgt-10">
        <div class="recom-top recom-tops1 clearfix">
            <div class="floor-tabs">
                <ul class="clearfix">
                    <li class="active"><a href="javascript:void(0)">导入详情</a></li>
                </ul>
                <span style="left: 0px;" class="tabs-guide"></span>
                <a href="${ctx}/group/cate/initPage" class=" fr mgr-10  edited-back-btn">返回</a>
            </div>
        </div>
    </div>

    <div class="list-top pdlr-20 clear  pdt-5">
        <div class="list-title fl fs14">类目列表<span class="fs12 font-gray">（成功${successNum}条，失败${failureNum}条）</span>
        </div>
    </div>

    <div class="list-con pdlr-20  clear js_pans">
        <table id="dataTable" border="0" width="100%" class=" table-style4  js_table_style4">
            <thead>
            <tr class="erow">
                <td>类目ID </td>
                <td>类目名</td>
                <td>父级类目id</td>
                <td>层级</td>
                <td>导入结果</td>
                <td>失败原因</td>
            </tr>
            </thead>
            <tbody>
            <c:forEach items="${addList}" var="item">
                <tr class="erow">
                    <td>${item.cateId}</td>
                    <td>${item.cateName}</td>
                    <td>${item.parentId}</td>
                    <td>${item.cateLevel}</td>
                    <td>${item.importResult=='0'?'成功':'失败'}</td>
                    <td>${item.failReason}</td>
                </tr>
            </c:forEach>
            </tbody>
        </table>
    </div>
</div>
</body>
</html>

校验表格的类

/**
 * 导入Excel文件（支持“XLS”和“XLSX”格式）
 * @author ThinkGem
 * @version 2013-03-10
 */
public class ImportExcel {

	private static final int FIVE_EIGHT = 58;
	private static final int TWO = 2;
	private static final int THREE = 3;
	private static final int NUM_58 = 58;

	private static final Logger log = LoggerFactory.getLogger(ImportExcel.class);
			
	/**
	 * 工作薄对象
	 */
	private Workbook wb;
	
	/**
	 * 工作表对象
	 */
	private Sheet sheet;
	
	/**
	 * 标题行号
	 */
	private int headerNum;
	
	/**
	 * 构造函数
	 * @param path 导入文件，读取第一个工作表
	 * @param headerNum 标题行号，数据行号=标题行号+1
	 * @throws InvalidFormatException 
	 * @throws IOException 
	 */
	public ImportExcel(String fileName, int headerNum) 
			throws InvalidFormatException, IOException {
		this(new File(fileName), headerNum);
	}
	
	/**
	 * 构造函数
	 * @param path 导入文件对象，读取第一个工作表
	 * @param headerNum 标题行号，数据行号=标题行号+1
	 * @throws InvalidFormatException 
	 * @throws IOException 
	 */
	public ImportExcel(File file, int headerNum) 
			throws InvalidFormatException, IOException {
		this(file, headerNum, 0);
	}

	/**
	 * 构造函数
	 * @param path 导入文件
	 * @param headerNum 标题行号，数据行号=标题行号+1
	 * @param sheetIndex 工作表编号
	 * @throws InvalidFormatException 
	 * @throws IOException 
	 */
	public ImportExcel(String fileName, int headerNum, int sheetIndex) 
			throws InvalidFormatException, IOException {
		this(new File(fileName), headerNum, sheetIndex);
	}
	
	/**
	 * 构造函数
	 * @param path 导入文件对象
	 * @param headerNum 标题行号，数据行号=标题行号+1
	 * @param sheetIndex 工作表编号
	 * @throws InvalidFormatException 
	 * @throws IOException 
	 */
	public ImportExcel(File file, int headerNum, int sheetIndex) 
			throws InvalidFormatException, IOException {
		this(file.getName(), new FileInputStream(file), headerNum, sheetIndex);
	}
	
	/**
	 * 构造函数
	 * @param file 导入文件对象
	 * @param headerNum 标题行号，数据行号=标题行号+1
	 * @param sheetIndex 工作表编号
	 * @throws InvalidFormatException 
	 * @throws IOException 
	 */
	public ImportExcel(MultipartFile multipartFile, int headerNum, int sheetIndex) 
			throws InvalidFormatException, IOException {
		this(multipartFile.getOriginalFilename(), multipartFile.getInputStream(), headerNum, sheetIndex);
	}

	/**
	 * 构造函数
	 * @param path 导入文件对象
	 * @param headerNum 标题行号，数据行号=标题行号+1
	 * @param sheetIndex 工作表编号
	 * @throws InvalidFormatException 
	 * @throws IOException 
	 */
	public ImportExcel(String fileName, InputStream is, int headerNum, int sheetIndex) 
			throws InvalidFormatException, IOException {
		if (StringUtils.isBlank(fileName)){
			throw new RuntimeException("导入文档为空!");
		}else if(fileName.toLowerCase().endsWith("xls")){    
			this.wb = new HSSFWorkbook(is);    
        }else if(fileName.toLowerCase().endsWith("xlsx")){  
        	this.wb = new XSSFWorkbook(is);
        }else{  
        	throw new RuntimeException("文档格式不正确!");
        }  
		if (this.wb.getNumberOfSheets()<sheetIndex){
			throw new RuntimeException("文档中没有工作表!");
		}
		this.sheet = this.wb.getSheetAt(sheetIndex);
		this.headerNum = headerNum;
		log.debug("Initialize success.");
	}
	
	/**
	 * 获取行对象
	 * @param rownum
	 * @return
	 */
	public Row getRow(int rownum){
		return this.sheet.getRow(rownum);
	}

	/**
	 * 获取数据行号
	 * @return
	 */
	public int getDataRowNum(){
		return headerNum+1;
	}
	
	/**
	 * 获取最后一个数据行号
	 * @return
	 */
	public int getLastDataRowNum(){
		return this.sheet.getLastRowNum()+headerNum;
	}
	
	/**
	 * 获取最后一个列号
	 * @return
	 */
	public int getLastCellNum(){
		return this.getRow(headerNum).getLastCellNum();
	}
	
	/**
	 * 获取单元格值
	 * @param row 获取的行
	 * @param column 获取单元格列号
	 * @return 单元格值
	 */
	public Object getCellValue(Row row, int column){
		Object val = "";
		try{
			Cell cell = row.getCell(column);
			if (cell != null){
				if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC){
					//防止长数字变成科学计数法显示
					DecimalFormat df = new DecimalFormat("0");
					val = df.format(cell.getNumericCellValue());
				}else if (cell.getCellType() == Cell.CELL_TYPE_STRING){
					val = cell.getStringCellValue();
				}else if (cell.getCellType() == Cell.CELL_TYPE_FORMULA){
					val = cell.getCellFormula();
				}else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN){
					val = cell.getBooleanCellValue();
				}else if (cell.getCellType() == Cell.CELL_TYPE_ERROR){
					val = cell.getErrorCellValue();
				}
			}
		}catch (Exception e) {
			return val;
		}
		return val;
	}
	
	public static Object getCellString(Cell cell) {
		Object result = null;
		if (cell != null) {
			int cellType = cell.getCellType();
			switch (cellType) {
			case HSSFCell.CELL_TYPE_STRING:
				result = cell.getRichStringCellValue().getString();
				break;
			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 HH:mm:ss");
					}
					Date date = cell.getDateCellValue();
					result = sdf.format(date);
				} else if (cell.getCellStyle().getDataFormat() == NUM_58) {
					// 处理自定义日期格式：m月d日(通过判断单元格的格式id解决，id的值是FIVE_EIGHT)
					SimpleDateFormat sdf = new SimpleDateFormat(
							"yyyy-MM-dd HH:mm:ss");
					double value = cell.getNumericCellValue();
					Date date = org.apache.poi.ss.usermodel.DateUtil
							.getJavaDate(value);
					result = sdf.format(date);
				} else {
					double value = cell.getNumericCellValue();
					CellStyle style = cell.getCellStyle();
					DecimalFormat format = new DecimalFormat();
					String temp = style.getDataFormatString();
					// 单元格设置成常规
					if (temp.equals("General")) {
						format.applyPattern("#");
					}
					result = format.format(value);
				}

				// if (HSSFDateUtil.isCellDateFormatted(cell)) {
				// SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd
				// HH:mm:ss");
				// double value = cell.getNumericCellValue();
				// Date date =
				// org.apache.poi.ss.usermodel.DateUtil.getJavaDate(value);
				// result = sdf.format(date);
				// }else{
				// result = cell.getNumericCellValue();
				// }
				break;
			case HSSFCell.CELL_TYPE_FORMULA:
				result = cell.getNumericCellValue();
				break;
			case HSSFCell.CELL_TYPE_ERROR:
				result = null;
				break;
			case HSSFCell.CELL_TYPE_BOOLEAN:
				result = cell.getBooleanCellValue();
				break;
			case HSSFCell.CELL_TYPE_BLANK:
				result = null;
				break;
			default:
				break;
			}
		}
		return result;
	}
	
	/**
	 * 获取导入数据列表
	 * @param cls 导入对象类型
	 * @param groups 导入分组
	 */
	public <E> List<E> getDataList(Class<E> cls, int... groups) throws InstantiationException, IllegalAccessException{
		List<Object[]> annotationList = Lists.newArrayList();
		// Get annotation field 
		Field[] fs = cls.getDeclaredFields();
		for (Field f : fs){
			ExcelField ef = f.getAnnotation(ExcelField.class);
			if (ef != null && (ef.type()==0 || ef.type()==TWO)){
				if (groups!=null && groups.length>0){
					boolean inGroup = false;
					for (int g : groups){
						if (inGroup){
							break;
						}
						for (int efg : ef.groups()){
							if (g == efg){
								inGroup = true;
								annotationList.add(new Object[]{ef, f});
								break;
							}
						}
					}
				}else{
					annotationList.add(new Object[]{ef, f});
				}
			}
		}
		// Get annotation method
		Method[] ms = cls.getDeclaredMethods();
		for (Method m : ms){
			ExcelField ef = m.getAnnotation(ExcelField.class);
			if (ef != null && (ef.type()==0 || ef.type()==TWO)){
				if (groups!=null && groups.length>0){
					boolean inGroup = false;
					for (int g : groups){
						if (inGroup){
							break;
						}
						for (int efg : ef.groups()){
							if (g == efg){
								inGroup = true;
								annotationList.add(new Object[]{ef, m});
								break;
							}
						}
					}
				}else{
					annotationList.add(new Object[]{ef, m});
				}
			}
		}
		// Field sorting
		Collections.sort(annotationList, new Comparator<Object[]>() {
			@Override
			public int compare(Object[] o1, Object[] o2) {
				return new Integer(((ExcelField)o1[0]).sort()).compareTo(
						new Integer(((ExcelField)o2[0]).sort()));
			};
		});
		//log.debug("Import column count:"+annotationList.size());
		// Get excel data
		List<E> dataList = Lists.newArrayList();
		for (int i = this.getDataRowNum(); i <= this.getLastDataRowNum(); i++) {
			E e = (E)cls.newInstance();
			int column = 0;
			Row row = this.getRow(i);
			StringBuilder sb = new StringBuilder();
			for (Object[] os : annotationList){
				Object val = this.getCellValue(row, column++);
				if (val != null){
					ExcelField ef = (ExcelField)os[0];
					// If is dict type, get dict value
					/*if (StringUtils.isNotBlank(ef.dictType())){
						val = DictUtils.getDictValue(val.toString(), ef.dictType(), "");
						//log.debug("Dictionary type value: ["+i+","+colunm+"] " + val);
					}*/
					// Get param type and type cast
					Class<?> valType = Class.class;
					if (os[1] instanceof Field){
						valType = ((Field)os[1]).getType();
					}else if (os[1] instanceof Method){
						Method method = ((Method)os[1]);
						if ("get".equals(method.getName().substring(0, THREE))){
							valType = method.getReturnType();
						}else if("set".equals(method.getName().substring(0, THREE))){
							valType = ((Method)os[1]).getParameterTypes()[0];
						}
					}
					//log.debug("Import value type: ["+i+","+column+"] " + valType);
					try {
						if (valType == String.class){
							String s = String.valueOf(val.toString());
							if(StringUtils.endsWith(s, ".0")){
								val = StringUtils.substringBefore(s, ".0");
							}else{
								val = String.valueOf(val.toString());
							}
						}else if (valType == Integer.class){
							val = Double.valueOf(val.toString()).intValue();
						}else if (valType == Long.class){
							val = Double.valueOf(val.toString()).longValue();
						}else if (valType == Double.class){
							val = Double.valueOf(val.toString());
						}else if (valType == Float.class){
							val = Float.valueOf(val.toString());
						}else if (valType == Date.class){
							val = DateUtil.getJavaDate((Double)val);
						}else{
							if (ef.fieldType() != Class.class){
								val = ef.fieldType().getMethod("getValue", String.class).invoke(null, val.toString());
							}else{
								val = Class.forName(this.getClass().getName().replaceAll(this.getClass().getSimpleName(), 
										"fieldtype."+valType.getSimpleName()+"Type")).getMethod("getValue", String.class).invoke(null, val.toString());
							}
						}
					} catch (Exception ex) {
						log.info("Get cell value ["+i+","+column+"] error: " + ex.toString());
						val = null;
					}
					// set entity value
					if (os[1] instanceof Field){
						Reflections.invokeSetter(e, ((Field)os[1]).getName(), val);
					}else if (os[1] instanceof Method){
						String mthodName = ((Method)os[1]).getName();
						if ("get".equals(mthodName.substring(0, THREE))){
							mthodName = "set"+StringUtils.substringAfter(mthodName, "get");
						}
						Reflections.invokeMethod(e, mthodName, new Class[] {valType}, new Object[] {val});
					}
				}
				sb.append(val+", ");
			}
			dataList.add(e);
			log.debug("Read success: ["+i+"] "+sb.toString());
		}
		return dataList;
	}

//	/**
//	 * 导入测试
//	 */
	/*public static void main(String[] args) throws Throwable {

		ImportExcel ei = new ImportExcel("target/export.xlsx", 1);

		for (int i = ei.getDataRowNum(); i < ei.getLastDataRowNum(); i++) {
			Row row = ei.getRow(i);
			for (int j = 0; j < ei.getLastCellNum(); j++) {
				Object val = ei.getCellValue(row, j);
			}
		}

	}*/

}

然后是导入数据的控制器

 /*
     * 导入
     * */
    @RequestMapping(value = "importData", method = RequestMethod.POST)
    public String importData(MultipartFile file, Model model) {

        int successNum = 0;
        int failureNum = 0;
        try {
            ImportExcel ei = new ImportExcel(file.getOriginalFilename(),
                    file.getInputStream(), 0, 0);
            List<GroupCategory> importInfos = ei.getDataList(GroupCategory.class);//获取表格数据转换成实体类数据
            List<GroupCategory> addList = new ArrayList<>();
            for (GroupCategory groupCategory : importInfos) {//遍历数据
                if (!groupCategory.getParentId().isEmpty() && !groupCategory.getCateName().isEmpty()) {//父级id级类目名称为必填项
                    GroupCategory category = groupCategoryService.queryGroupCategoryByName(groupCategory.getCateName());//判断数据库中是否已经存在
                    if (category == null) {
                        groupCategoryService.insertGroupCategoryInfo(groupCategory);
                        groupCategory.setImportResult("0");
                        addList.add(groupCategory);
                        successNum++;

                    } else {
                        groupCategory.setImportResult("1");
                        groupCategory.setFailReason("类目已经存在，请修改类目名");
                        addList.add(groupCategory);
                        failureNum++;

                    }
                } else {
                    groupCategory.setImportResult("1");
                    groupCategory.setFailReason("父级id和类目名不能为空");
                    addList.add(groupCategory);
                    failureNum++;

                }
            }
            model.addAttribute("successNum", successNum);
            model.addAttribute("failureNum", failureNum);
            model.addAttribute("addList", addList);

        } catch (Exception e) {


        }

        return "modules/group/cate/groupCateImport";

    }

控制器完成，之后得完成相对应Service接口和数据库查询的dao

首先是service接口（名字根据controller里面的bean命名）：

    /*根据类目名称查询类目信息*/
    GroupCategory queryGroupCategoryByName(String cateName);

    /*	*
     * 新增类目信息
     * @param groupCategory
     * @return
     */
    public int insertGroupCategoryInfo(GroupCategory groupCategory);

然后是实现类，注意添加@Service注解和注入dao

@Override
public GroupCategory queryGroupCategoryByName(String cateName) {
    return GroupCategoryDao.queryGroupCategoryByName(cateName);
}

 /*
     * 新增类目
     */
    @Override
    public int insertGroupCategoryInfo(GroupCategory groupCategory) {
        return GroupCategoryDao.insertGroupCategoryInfo(groupCategory);
    }

    

接下来到对应的dao以及xml文件

dao：

    /*根据名字获取类目*/
    GroupCategory queryGroupCategoryByName(String cateName);
    
    //新增类目
    int insertGroupCategoryInfo(GroupCategory groupCategory);

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.ai.iis.newuop.group.dao.IGroupCategoryDao">

    <!--根据名称获取类目-->
    <select id="queryGroupCategoryByName" resultType="com.ai.iis.domain.pojo.group.GroupCategory"
            parameterType="java.lang.String">
		select * from new_uop.TF_GROUP_CATEGORY T WHERE T.CATE_NAME=#{cateName} AND DEL_FLAG='0'
	</select>
	
    <!--新增类目信息-->
    <insert id="insertGroupCategoryInfo" parameterType="com.ai.iis.domain.pojo.group.GroupCategory">
	    INSERT INTO TF_GROUP_CATEGORY (PARENT_ID,CATE_NAME,CATE_SORT,CREATE_DATE,CATE_LEVEL)
		VALUES(#{parentId},#{cateName},#{cateSort},sysdate,#{cateLevel})
	</insert>
</mapper>