java通过Excel表导入数据和下载模板

第一：导入数据

1：在get()上加上@ExcelField注解

@ExcelField(title = “工会类型”, sort = 10)，根据sort大小，将excel的数据列依次按顺序set到对应的属性中

package com.thinkgem.jeesite.modules.ncgh.entity;

import com.fasterxml.jackson.annotation.JsonBackReference;
import com.fasterxml.jackson.annotation.JsonFormat;
import com.thinkgem.jeesite.common.persistence.DataEntity;
import com.thinkgem.jeesite.common.utils.StringUtils;
import com.thinkgem.jeesite.common.utils.excel.annotation.ExcelField;
import com.thinkgem.jeesite.modules.sys.entity.Area;
import org.hibernate.validator.constraints.Length;

import java.util.Date;

/**
 * 建会管理Entity
 * @author LZY
 * @version 2020-11-25
 */
public class NcUnion extends DataEntity<NcUnion> {
   

	private static final long serialVersionUID = 1L;
	private String adminId;		//管理员id
	private String parentId;		// 父id
	private String name;		// 工会名称
	private String beforehandName;		// 预建工会名称
	private String type;		// 工会类型
	private String belongArea;		// 所属区域
	private String principal;		// 负责人
	private String creditCode;		// 社会统一信用代码
	private String unitName;		// 单位名称
	private String unitAddress;		// 单位地址
	private String unitNature;		// 单位性质
	private String unitPic;		// 单位图片
	private String unitBelongIndustry;		// 单位所属行业
	private String unitLagal;		// 单位法人
	private String economicType;		// 经济类型
	private String staffNumber;		// 职工人数
	private String contactPerson;		// 联系人
	private String contactPhone;		// 联系人手机号
	private Date applyTime;		// 申请时间
	private Date dealTime;		// 办结时间
	private String operator;		// 经办人
	private String status;		// 状态
	private String adjunct;		// 附件
	private String approvalAdvise;		// 审批意见
	private String dataSourse;		// 数据来源
	private Date beginApplyDate;
	private Date endApplyDate;
	private Date beginFinishDate;
	private Date endFinishDate;
	private Area area;
	private NcUnion parent;
	private String checkStatus;


    @ExcelField(title = "工会类型", sort = 10)
	public String getType() {
   
		return type;
	}
    //根据sort大小，将excel的数据set到对应的属性中
	@ExcelField(title = "工会名称", sort = 15)
	public String getName() {
   
		return name;
	}

	public void setName(String name) {
   
		this.name = name;
	}

	public void setType(String type) {
   
		this.type = type;
	}
	public String getAdminId() {
   
		return adminId;
	}

	public void setAdminId(String adminId) {
   
		this.adminId = adminId;
	}

	public Date getBeginFinishDate() {
   
		return beginFinishDate;
	}

	public void setBeginFinishDate(Date beginFinishDate) {
   
		this.beginFinishDate = beginFinishDate;
	}

	public Date getEndFinishDate() {
   
		return endFinishDate;
	}

	public void setEndFinishDate(Date endFinishDate) {
   
		this.endFinishDate = endFinishDate;
	}
	public String getCheckStatus() {
   
		return checkStatus;
	}

	public void setCheckStatus(String checkStatus) {
   
		this.checkStatus = checkStatus;
	}

	public NcUnion() {
   
		super();
	}

	public NcUnion(String id){
   
		super(id);
	}
	public Area getArea() {
   
		return area;
	}

	public void setArea(Area area) {
   
		this.area = area;
	}

	public String getParentId() {
   
		return parentId;
	}

	public void setParentId(String parentId) {
   
		this.parentId = parentId;
	}
	

	public String getBeforehandName() {
   
		return beforehandName;
	}

	public void setBeforehandName(String beforehandName) {
   
		this.beforehandName = beforehandName;
	}
	

	public String getBelongArea() {
   
		return belongArea;
	}

	public void setBelongArea(String belongArea) {
   
		this.belongArea = belongArea;
	}

	public String getPrincipal() {
   
		return principal;
	}

	public void setPrincipal(String principal) {
   
		this.principal = principal;
	}

	public String getCreditCode() {
   
		return creditCode;
	}

	public void setCreditCode(String creditCode) {
   
		this.creditCode = creditCode;
	}
	public String getUnitName() {
   
		return unitName;
	}

	public void setUnitName(String unitName) {
   
		this.unitName = unitName;
	}
	public String getUnitAddress() {
   
		return unitAddress;
	}

	public void setUnitAddress(String unitAddress) {
   
		this.unitAddress = unitAddress;
	}

	public String getUnitNature() {
   
		return unitNature;
	}

	public void setUnitNature(String unitNature) {
   
		this.unitNature = unitNature;
	}

	public String getUnitPic() {
   
		return unitPic;
	}

	public void setUnitPic(String unitPic) {
   
		this.unitPic = unitPic;
	}

	public String getUnitBelongIndustry() {
   
		return unitBelongIndustry;
	}

	public void setUnitBelongIndustry(String unitBelongIndustry) {
   
		this.unitBelongIndustry = unitBelongIndustry;
	}

	public String getUnitLagal() {
   
		return unitLagal;
	}

	public void setUnitLagal(String unitLagal) {
   
		this.unitLagal = unitLagal;
	}

	public String getEconomicType() {
   
		return economicType;
	}

	public void setEconomicType(String economicType) {
   
		this.economicType = economicType;
	}

	public String getStaffNumber() {
   
		return staffNumber;
	}

	public void setStaffNumber(String staffNumber) {
   
		this.staffNumber = staffNumber;
	}

	public String getContactPerson() {
   
		return contactPerson;
	}

	public void setContactPerson(String contactPerson) {
   
		this.contactPerson = contactPerson;
	}

	public String getContactPhone() {
   
		return contactPhone;
	}

	public void setContactPhone(String contactPhone) {
   
		this.contactPhone = contactPhone;
	}
	@JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss")
	public Date getApplyTime() {
   
		return applyTime;
	}

	public void setApplyTime(Date applyTime) {
   
		this.applyTime = applyTime;
	}
	@JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss")
	public Date getDealTime() {
   
		return dealTime;
	}

	public void setDealTime(Date dealTime) {
   
		this.dealTime = dealTime;
	}

	public String getOperator() {
   
		return operator;
	}

	public void setOperator(String operator) {
   
		this.operator = operator;
	}

	public String getStatus() {
   
		return status;
	}

	public void setStatus(String status) {
   
		this.status = status;
	}

	public String getAdjunct() {
   
		return adjunct;
	}

	public void setAdjunct(String adjunct) {
   
		this.adjunct = adjunct;
	}

	public String getApprovalAdvise() {
   
		return approvalAdvise;
	}

	public void setApprovalAdvise(String approvalAdvise) {
   
		this.approvalAdvise = approvalAdvise;
	}

	public String getDataSourse() {
   
		return dataSourse;
	}

	public void setDataSourse(String dataSourse) {
   
		this.dataSourse = dataSourse;
	}
	public Date getBeginApplyDate() {
   
		return beginApplyDate;
	}

	public void setBeginApplyDate(Date beginApplyDate) {
   
		this.beginApplyDate = beginApplyDate;
	}
	@JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss")
	public Date getEndApplyDate() {
   
		return endApplyDate;
	}

	public void setEndApplyDate(Date endApplyDate) {
   
		this.endApplyDate = endApplyDate;
	}

	public NcUnion getParent() {
   
		return parent;
	}

	public void setParent(NcUnion parent) {
   
		this.parent = parent;
	}
}

2：导入Excel工具类

/**
 * Copyright &copy; 2012-2016 <a href="https://github.com/thinkgem/jeesite">JeeSite</a> All rights reserved.
 */
package com.thinkgem.jeesite.common.utils.excel;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.util.Collections;
import java.util.Comparator;
import java.util.Date;
import java.util.List;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.web.multipart.MultipartFile;

import com.google.common.collect.Lists;
import com.thinkgem.jeesite.common.utils.Reflections;
import com.thinkgem.jeesite.common.utils.excel.annotation.ExcelField;
import com.thinkgem.jeesite.modules.sys.utils.DictUtils;

/**
 * 导入Excel文件（支持“XLS”和“XLSX”格式）
 * @author ThinkGem
 * @version 2013-03-10
 */
public class ImportExcel {
   
	
	private static 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