java根据excel导入数据库 java 导入excel数据并存到数据库

导入Excel表，并解析数据录入数据库，Excel表一个sheet或者多个sheet均可使用

controller类的导入接口

/**
     * 解析Excel表数据并保存
     * @param request
     * @param response
     * @throws Exception
     */
    @RequestMapping(value="import")
    @Action(description="导入数据")
    public void importExcelSource(MultipartHttpServletRequest request,HttpServletResponse response) throws Exception{
		Map<String, Object> map = new HashMap<String, Object>();

		Map<String, MultipartFile> files = request.getFileMap();
        Iterator<MultipartFile> it = files.values().iterator();
        MultipartFile file = (MultipartFile) it.next();
        
        // 1、解析Excel数据
        ExcelToBeanUtil eToBeanUtil = new ExcelToBeanUtil();
        Map<Integer, String[]> keys = new HashMap<Integer, String[]>();
        //个人信息表相应属性数组
        String [] perColumnName = {"customerName","customerType", "customerIndustry", "customerArea", "customerCategory", "personIdentityCard","personPapersExpire"
        		, "personCategory", "personEducation", "personMobile", "personCensusAddr","personMarital", "personFamily", "personExistChild"};
       
        //企业信息表相应属性数组
        String [] entColumnName = {"customerName","customerType", "customerIndustry", "customerArea", "customerCategory", "enterpriseNumber"
    			,"enterpriseType", "userType", "enterpriseLegalIdNo", "enterpriseLegalUser", "enterpriseLegalUserPhone","registrationType", "enterpriseOrganizingNumber"};
    	keys.put(0, perColumnName);
    	keys.put(1, entColumnName);
    	
        List<CrmCustomerDto> list =  eToBeanUtil.toObjectList(CrmCustomerDto.class, file, keys, 3, 0); //从第四行，第一列开始解析数据

        //2、保存数据
        Integer resultCode = this.crmCustomerService.insertList(list);
        String resultMsg = "导入数据成功！";
		
        if(resultCode != Constant.YES){
			resultMsg = "导入数据失败！";
		}
		
		map.put("result", resultCode);
		map.put("message", resultMsg);
		
		String jsonStr = JSONObject.fromObject(map).toString();
		response.setCharacterEncoding("utf-8");
		response.getWriter().print(jsonStr);
    }

解析Excel文件数据工具类  直接调用工具类 toObjectList该方法就可以，会将数据转换成相应的实体类集合返回，接着就可以进行数据库的录入保存操作。

1、Excel文件解析获取单元格的数据都是以字符串的形式获取，设计到其他数据类型的可以解析到数据后再做类型转换（本人单独写了个dto来做数据接收的，这里属性的命名可以享有的实体类的属性命名一直，这样方便直接使用BeanUtils.copyProperties(customerdDto, person)方法来copy）。

package com.deelon.common.util;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Method;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import java.util.Set;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
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.springframework.web.multipart.MultipartFile;

import com.alibaba.fastjson.JSONObject;

/**
 * Excel表数据导入，数据解析转换成实体bean工具类
 * @author FUYONGJIN
 *
 */
public class ExcelToBeanUtil {
	
	// 总行数
    private int totalRows = 0;
    // 总条数
    private int totalCells = 0;
    // 错误信息接收器
    private String errorMsg;

    // 构造方法
    public ExcelToBeanUtil() {
    }

    // 获取总行数
    public int getTotalRows() {
        return totalRows;
    }

    // 获取总列数
    public int getTotalCells() {
        return totalCells;
    }

    // 获取错误信息
    public String getErrorInfo() {
        return errorMsg;
    }

	
 
    /**
     * 利用反射将	List<Map<String,Object>>结构 生成相应的List<T>数据
     * @param clazz
     * @param file
     * @param keys  工作表相应的javabean类的属性名称数组 （map集合 key 表示表的下标，value表示属性数组）
     * @param rowNum  开始行的下标
     * @param colNum  开始列的下标
     * @return
     * @throws Exception
     */
    public  <T>  List<T> toObjectList(Class<T> clazz,MultipartFile file,Map<Integer, String []> keys,Integer rowNum,Integer colNum) throws Exception{
    	
    	List<Map<String, String>> list = getExcelInfo(file, keys, rowNum, colNum);
    	
    	List<T> returnList = new LinkedList<T>();
    	for(int i=0;i<list.size();i++){
    		Set<Map.Entry<String, String>> set =  list.get(i).entrySet();
    		Iterator<Entry<String, String>> it = set.iterator();
			T obj= clazz.newInstance();
			Method[] methods = clazz.getDeclaredMethods();
    		while (it.hasNext()) {		//生成一个obj
				Map.Entry<String, String> entry = (Map.Entry<String, String>) it.next();
				for(Method m:methods){
					if(m.getName().startsWith("set")){		//为obj赋值
						String methodName = entry.getKey().toString();
						StringBuffer sb = new StringBuffer(methodName);  
		                sb.replace(0, 1, (methodName.charAt(0)+"").toUpperCase());  
		                methodName = "set" +  sb.toString();
						if(methodName.equals(m.getName())){
							m.invoke(obj, entry.getValue());
							break;
						}
					}
				}
			}
			returnList.add(obj);
    	}
    	System.out.println("size=" + returnList.size());
    	return returnList;
    }
    
    /**
     * 将workbook中的值放入List<Map<String,Object>>结构中
     * @author FUYONGJIN
     * @param wb  文件workbook 对象
     * @param keys  工作表相应的javabean类的属性名称数组 （map集合 key 表示表的下标，value表示属性数组）
     * @param rowNum  开始行的下标
     * @param colNum  开始列的下标
     * @throws Exception
     * @return
     */
    private List<Map<String, String>> readExcelValue(Workbook wb,Map<Integer, String []> keys,Integer rowNum,Integer colNum) throws Exception{
		List<Map<String, String>> list = new ArrayList<Map<String, String>>();
		
		String[] columnName = null; 
		// 遍历工作表sheet
		for (int sheetIndex = 0; sheetIndex < wb.getNumberOfSheets(); sheetIndex++) { 
			
			if(keys.containsKey(sheetIndex)){
				columnName = keys.get(sheetIndex);
			}
			
			Sheet sheet = wb.getSheetAt(sheetIndex);
			// 得到Excel的行数
			this.totalRows = sheet.getLastRowNum();
			
			// 得到Excel的列数(前提是有行数)
			if (totalRows > 1 && sheet.getRow(0) != null) {
				this.totalCells = sheet.getRow(0).getPhysicalNumberOfCells();
			}

			String str = "";
			
			for (int i = rowNum; i <= totalRows; i++) {
				Row row = sheet.getRow(i);

				if (isRowEmpty(row))
					continue;

				Map<String, String> map = new HashMap<String, String>();

				int j = colNum; 
				while (j < totalCells) {
					str = getCellFormatValueString(row.getCell((short) j), j).trim();

					for (int k = 0; k < columnName.length; k++) {
						if (k+colNum == j) {
							map.put(columnName[k], str);
							break;
						}
					}
					j++;
				}

				list.add(map);

			}
		}

		return list;
    }
    
    /**
     * 根据HSSFCell类型设置数据
     * @param xssfCell 纯数字的内容也按照String类型获取
     * @return
     */
    private String getCellFormatValueString(Cell cell,int j) {
        String cellvalue = "";
        if (cell != null) {
            // 判断当前Cell的Type
            switch (cell.getCellType()) {
                // 如果当前Cell的Type为NUMERIC
                case HSSFCell.CELL_TYPE_NUMERIC:
                case HSSFCell.CELL_TYPE_FORMULA: {
                    // 判断当前的cell是否为Date
                    if (HSSFDateUtil.isCellDateFormatted(cell)) {
                        // 如果是Date类型则，转化为Data格式
                    	SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                        if(j == 2){
                        	sdf = new SimpleDateFormat("HH:mm");
                        }
                        Date date = cell.getDateCellValue();
                       
                        cellvalue = sdf.format(date);
                        
                    }
                    // 如果是纯数字
                    else {
                    	cell.setCellType(HSSFCell.CELL_TYPE_STRING);  //设置为String类型
                        // 取得当前Cell的数值
                        cellvalue = cell.getRichStringCellValue().getString(); //String.valueOf(cell.getNumericCellValue());
                    }
                    break;
                }
                // 如果当前Cell的Type为STRIN
                case HSSFCell.CELL_TYPE_STRING:
                    // 取得当前的Cell字符串
                    cellvalue = cell.getRichStringCellValue().getString();
                    break;
                    // 默认的Cell值
                default:
                    cellvalue = " ";
            }
        } else {
            cellvalue = "";
        }
        return cellvalue;
        
    }
    
    public static boolean isRowEmpty(Row row){
        for (int i = row.getFirstCellNum(); i < row.getLastCellNum(); i++) {
            Cell cell = row.getCell(i);
            if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK){
                return false;
            }
        }
        return true;
    }

   
    /**
     * 读EXCEL文件，获取信息集合
     * @param mFile
     * @param keys
     * @param rowNum
     * @param colNum
     * @throws Exception
     * @return
     */
    public List<Map<String, String>> getExcelInfo(MultipartFile mFile,Map<Integer, String []> keys,Integer rowNum,Integer colNum) throws Exception {
        
    	List<Map<String, String>> mapList = null;
    	String fileName = mFile.getOriginalFilename();// 获取文件名
        try {
            if (!validateExcel(fileName)) {// 验证文件名是否合格
                return null;
            }
            boolean isExcel2003 = true;// 根据文件名判断文件是2003版本还是2007版本
            if (isExcel2007(fileName)) {
                isExcel2003 = false;
            }
            
            Workbook wb = createExcel(mFile.getInputStream(), isExcel2003);
           
            // 解析Excel表数据
            mapList = readExcelValue(wb,keys,rowNum,colNum);// 解析Excel表数据
            
        } catch (Exception e) {
            e.printStackTrace();
        }
        return mapList;
    }
    
    /**
     * 获取Workbook对象
     * @param is      输入流
     * @param isExcel2003   excel是2003还是2007版本
     * @return
     * @throws Exception
     */
    public Workbook createExcel(InputStream is, boolean isExcel2003) throws Exception {
    	 Workbook wb = null;
    	try {
            if (isExcel2003) {// 当excel是2003时,创建excel2003
                wb = new HSSFWorkbook(is);
            } else {// 当excel是2007时,创建excel2007
                wb = new XSSFWorkbook(is);
            }
            return wb;
        } catch (IOException e) {
            e.printStackTrace();
        }
        return wb;
    }
    
    /**
     * 验证EXCEL文件
     * 
     * @param filePath
     * @return
     */
    public boolean validateExcel(String filePath) {
        if (filePath == null || !(isExcel2003(filePath) || isExcel2007(filePath))) {
            errorMsg = "文件名不是excel格式";
            return false;
        }
        return true;
    }

    // @描述：是否是2003的excel，返回true是2003
    public static boolean isExcel2003(String filePath) {
        return filePath.matches("^.+\\.(?i)(xls)$");
    }

    // @描述：是否是2007的excel，返回true是2007
    public static boolean isExcel2007(String filePath) {
        return filePath.matches("^.+\\.(?i)(xlsx)$");
    }
    
    public static void main(String args[]) throws Exception{
    	
    	FileInputStream input = new FileInputStream("D:\\用户信息导入模板.xlsx");
    	XSSFWorkbook workbook = new XSSFWorkbook(input);
    	
    	Map<Integer, String[]> keys = new HashMap<Integer, String[]>();
    	String [] columnName = {"tradeDate", "tradeTime", "remittAmount", "reciprocalAccount", "summary"
    			,"tradeDate", "tradeTime", "remittAmount", "reciprocalAccount", "summary","tradeDate", "tradeTime", "remittAmount"};
    	keys.put(1, columnName);
    	ExcelToBeanUtil util = new ExcelToBeanUtil();
    	List<Map<String, String>> list = util.readExcelValue(workbook,keys , 3,0);
    	System.out.println(JSONObject.toJSONString(list)); //利用fastjson将其序列化打印出来
    }

}

用于接收数据的dto（这里我个人的代码在set方法里做了相应的转换）

package com.deelon.crmform.dto;

import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;

import com.deelon.constant.Constant;

/**
 * 批量导入用户实体dto
 * 
 * @author FUYONGJIN
 *
 */
public class CrmCustomerDto {
	
	// 客户名称
	private String customerName;
	// 客户类型
	private String customerType;
	// 所属行业
	private String customerIndustry;
	// 所属区域
	private String customerArea;
	// 客户分类
	private String customerCategory;

	// 个人用户信息
	// 身份证号码
	private String personIdentityCard;
	// 证件到期时间
	private Date personPapersExpire;
	// 户别
	private String personCategory;
	// 学历
	private String personEducation;
	// 移动电话
	private String personMobile;
	// 户籍地址
	private String personCensusAddr;
	// 婚姻状况
	private String personMarital;
	// 家庭人员
	private Integer personFamily;
	// 是否有子女
	private String personExistChild;

	// 企业用户信息
	// 营业执照号
	private String enterpriseNumber;
	// 企业性质
	private String enterpriseType;
	// 注册人类型
	private String userType;
	// 注册人身份证号码
	private String enterpriseLegalIdNo;
	// 注册人姓名
	private String enterpriseLegalUser;
	// 注册人联系电话
	private String enterpriseLegalUserPhone;
	// 企业注册类型
	private String registrationType;
	// 组织机构代码号/社会信用代码
	private String enterpriseOrganizingNumber;

	
	public String getCustomerType() {
		return customerType;
	}

	public void setCustomerType(String customerType) {
		if(customerType.isEmpty()){
			this.customerType = customerType;
			return;
		}
		if (customerType.trim().equals("个人")) {
			customerType = String.valueOf(Constant.PersonalOrEnterprise.PERSONAL);
		} else {
			customerType = String.valueOf(Constant.PersonalOrEnterprise.ENTERPRISE);
		}
		this.customerType = customerType;
	}

	public String getCustomerName() {
		return customerName;
	}

	public String getCustomerIndustry() {
		return customerIndustry;
	}

	public String getCustomerArea() {
		return customerArea;
	}

	public String getCustomerCategory() {
		return customerCategory;
	}

	public String getPersonIdentityCard() {
		return personIdentityCard;
	}

	public Date getPersonPapersExpire() {
		return personPapersExpire;
	}

	public String getPersonCategory() {
		return personCategory;
	}

	public String getPersonEducation() {
		return personEducation;
	}

	public String getPersonMobile() {
		return personMobile;
	}

	public String getPersonCensusAddr() {
		return personCensusAddr;
	}

	public String getPersonMarital() {
		return personMarital;
	}

	public Integer getPersonFamily() {
		return personFamily;
	}

	public String getPersonExistChild() {
		return personExistChild;
	}

	public String getEnterpriseNumber() {
		return enterpriseNumber;
	}

	public String getEnterpriseType() {
		return enterpriseType;
	}

	public String getUserType() {
		return userType;
	}

	public String getEnterpriseLegalIdNo() {
		return enterpriseLegalIdNo;
	}

	public String getEnterpriseLegalUser() {
		return enterpriseLegalUser;
	}

	public String getEnterpriseLegalUserPhone() {
		return enterpriseLegalUserPhone;
	}

	public String getRegistrationType() {
		return registrationType;
	}

	public String getEnterpriseOrganizingNumber() {
		return enterpriseOrganizingNumber;
	}

	public void setCustomerName(String customerName) {
		this.customerName = customerName;
	}

	public void setCustomerIndustry(String customerIndustry) {
		this.customerIndustry = customerIndustry;
	}

	public void setCustomerArea(String customerArea) {
		this.customerArea = customerArea;
	}

	public void setCustomerCategory(String customerCategory) {
		if (customerCategory.isEmpty()) {
			this.customerCategory = customerCategory;
			return;
		}
		if (customerCategory.trim().equals("借款人")) {
			customerCategory = Constant.customerCategory.BORROWER;
			
		} else if (customerCategory.trim().equals("担保人")) {
			customerCategory = Constant.customerCategory.GUARANTEE;
			
		} else if (customerCategory.trim().equals("银行")) {
			customerCategory = Constant.customerCategory.BANK;
			
		} else {
			customerCategory = Constant.customerCategory.FINANCING;
		}
		this.customerCategory = customerCategory;
	}

	public void setPersonIdentityCard(String personIdentityCard) {
		this.personIdentityCard = personIdentityCard;
	}

	public void setPersonPapersExpire(String personPapersExpire) {
		SimpleDateFormat format=new SimpleDateFormat("yyyy-MM-dd"); 
		try {
			if(null != personPapersExpire && !personPapersExpire.isEmpty()){
				this.personPapersExpire = format.parse(personPapersExpire);
			}else {
				this.personPapersExpire = null;
			}
			
		} catch (ParseException e) {
			e.printStackTrace();
		}
	}

	public void setPersonCategory(String personCategory) {
		if(personCategory.isEmpty()){
			this.personCategory = personCategory;
			return;
		}
		if (personCategory.trim().equals("农户")) {
			personCategory = Constant.perResidenceType.COUNTRYSIDE;
		} else {
			personCategory = Constant.perResidenceType.RESIDENT;
		}
		this.personCategory = personCategory;
	}

	public void setPersonEducation(String personEducation) {

		if (personEducation.isEmpty()) {
			this.personEducation = personEducation;
			return;
		}
		if (personEducation.trim().equals("本科")) {
			personEducation = Constant.education.UNDERGRADUATE;
			
		} else if (personEducation.trim().equals("硕士研究生")) {
			personEducation = Constant.education.MASTER;
			
		} else if (personEducation.trim().equals("博士研究生")) {
			personEducation = Constant.education.DOCTOR;
			
		} else if (personEducation.trim().equals("专科")) {
			personEducation = Constant.education.SPECIALTY;
			
		} else {
			personEducation = Constant.education.BELOWSPECIALTY;
		}
		this.personEducation = personEducation;
	}

	public void setPersonMobile(String personMobile) {
		this.personMobile = personMobile;
	}

	public void setPersonCensusAddr(String personCensusAddr) {
		this.personCensusAddr = personCensusAddr;
	}

	public void setPersonMarital(String personMarital) {
		
		if(personMarital.isEmpty()){
			this.personMarital = personMarital;
			return;
		}
		if (personMarital.trim().equals("未婚")) {
			personMarital = Constant.marital.UNMARRIED;
		}else if (personMarital.trim().equals("已婚")) {
			personMarital = Constant.marital.MARRIED;
		}else if (personMarital.trim().equals("离异")) {
			personMarital = Constant.marital.DIVORCE;
		}else if (personMarital.trim().equals("再婚")) {
			personMarital = Constant.marital.REMARRIAGE;
		} else {
			personMarital = Constant.marital.OTHER;
		}
		
		this.personMarital = personMarital;
	}

	public void setPersonFamily(String personFamily) {
		if(null != personFamily && !personFamily.isEmpty()){
			this.personFamily = Integer.valueOf(personFamily);
		}else {
			this.personFamily = null;
		}
		
	}

	public void setPersonExistChild(String personExistChild) {
		if(personExistChild.isEmpty()){
			this.personExistChild = personExistChild;
			return;
		}
		if (personExistChild.trim().equals("是")) {
			personExistChild = Constant.YES.toString();
		} else {
			personExistChild = Constant.NO.toString();
		}
		this.personExistChild = personExistChild;
	}

	public void setEnterpriseNumber(String enterpriseNumber) {
		this.enterpriseNumber = enterpriseNumber;
	}

	public void setEnterpriseType(String enterpriseType) {
		if(enterpriseType.isEmpty()){
			this.enterpriseType = enterpriseType;
			return;
		}
		if (enterpriseType.trim().equals("有限责任")) {
			enterpriseType = Constant.enterpriseType.LIMITEDLIABILITY;
		}else if (enterpriseType.trim().equals("股份制")) {
			enterpriseType = Constant.enterpriseType.SHAREHOLDINGSYSTEM;
		}else if (enterpriseType.trim().equals("私营")) {
			enterpriseType = Constant.enterpriseType.PRIVATEENTERPRISE;
		}else if (enterpriseType.trim().equals("独资")) {
			enterpriseType = Constant.enterpriseType.SOLEPROPRIET;
		} else {
			enterpriseType = Constant.enterpriseType.JOINTVENTURE;
		}
		
		this.enterpriseType = enterpriseType;
	}

	public void setUserType(String userType) {
		if(userType.isEmpty()){
			this.userType = userType;
			return;
		}
		if (userType.trim().equals("法人")) {
			userType = String.valueOf(Constant.EnterpriseUserType.LEGAT);
		} else {
			userType = String.valueOf(Constant.EnterpriseUserType.AGENT);
		}
		this.userType = userType;
	}

	public void setEnterpriseLegalIdNo(String enterpriseLegalIdNo) {
		this.enterpriseLegalIdNo = enterpriseLegalIdNo;
	}

	public void setEnterpriseLegalUser(String enterpriseLegalUser) {
		this.enterpriseLegalUser = enterpriseLegalUser;
	}

	public void setEnterpriseLegalUserPhone(String enterpriseLegalUserPhone) {
		this.enterpriseLegalUserPhone = enterpriseLegalUserPhone;
	}

	public void setRegistrationType(String registrationType) {
		if(registrationType.isEmpty()){
			this.registrationType = registrationType;
			return;
		}
		if (registrationType.trim().equals("多证合一")) {
			registrationType = String.valueOf(Constant.EnterpriseRegistrationType.UNITY);
		} else {
			registrationType = String.valueOf(Constant.EnterpriseRegistrationType.ORGANIZATION);
		}
		this.registrationType = registrationType;
	}

	public void setEnterpriseOrganizingNumber(String enterpriseOrganizingNumber) {
		this.enterpriseOrganizingNumber = enterpriseOrganizingNumber;
	}


}