Excel动态导入Oracle数据库(SSM框架Spring+SpringMVC+MyBatis)

导入poi解析Excel的工具包

1.判断Excel文件是否存在，存在用FileInputStream对数据进行读取

2.判断Exel文件是.xlsx还是.xls。运用office组件对Excel进行解析，解析成workbook模式

3.遍历每一行每一列，获取没个单元格的信息，对单元格中的信息进行格式化处理

4.拼接SQL语句，把得到的SQL语句放入List集合，对集合进行批处理写入数据库

程序中所需要的工具类：

检测Excel版本

package com.zhou.ssm.util;

public class CheckExcel {

	//看是否是2003的Excel
	public static boolean isExcel2003(String filePath)  {  
        return filePath.matches("^.+\\.(?i)(xls)$");  
  }
	
	//看是否是2007的Excel
	public static boolean isExcel2007(String filePath)  {  
        return filePath.matches("^.+\\.(?i)(xlsx)$");  
  }
	
	//查看是否是Excel文件
	public static boolean validateExcel(String filePath){
        if (filePath == null || !(isExcel2003(filePath) || isExcel2007(filePath))){  
            return false;  
        }  
        return true;
  }
}

 时间格式处理类

package com.zhou.ssm.util;

import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.regex.Pattern;

public class DateTime {
	// STR格式
	public static final int YEAR = 1; // 获取年;
	static final String YEAR_STR = "yyyy";
	public static final int MONTH = 2; // 获取月;
	static final String MONTH_STR = "MM";
	public static final int DATE = 3; // 获取天;
	static final String DATE_STR = "dd";
	public static final int HOUR = 4;// 获取小时;
	static final String HOUR_STR = "HH";
	public static final int MINUTE = 5;// 获取分钟;
	static final String MINUTE_STR = "mm";
	public static final int SECOND = 6;// 获取秒钟;
	static final String SECOND_STR = "ss";

	public static final int YEAR_MONTH_DATE = 7;// 获取 年 - 月 - 日;
	public static final int YEAR_MONTH_DATE_HOUR = 8;// 获取 年 - 月 - 日 小时;
	public static final int YEAR_MONTH_DATE_HOUR_MINUTE = 9;// 获取 年 - 月 - 日
															// 小时:分钟;

	public static final int HOUR_MINUTE = 10;// 小时:分钟;

	public static final int DATE_HOUR = 11;// 日 小时;

	// 获取时间
	public static String getTime(int obj) {
		switch (obj) {
		case YEAR:
			return toTime(YEAR_STR);
		case MONTH:
			return toTime(MONTH_STR);
		case DATE:
			return toTime(DATE_STR);
		case HOUR:
			return toTime(HOUR_STR);
		case MINUTE:
			return toTime(MINUTE_STR);
		case SECOND:
			return toTime(SECOND_STR);
		case YEAR_MONTH_DATE:
			return toTime(YEAR_STR + "-" + MONTH_STR + "-" + DATE_STR);
		case YEAR_MONTH_DATE_HOUR:
			return toTime(YEAR_STR + "-" + MONTH_STR + "-" + DATE_STR + " " + HOUR_STR);
		case YEAR_MONTH_DATE_HOUR_MINUTE:
			return toTime(YEAR_STR + "-" + MONTH_STR + "-" + DATE_STR + " " + HOUR_STR + ":" + MINUTE_STR);
		case HOUR_MINUTE:
			return toTime(HOUR_STR + ":" + MINUTE_STR);
		case DATE_HOUR:
			return toTime(DATE_STR + " " + HOUR_STR);
		default:
			return toTime();
		}

	}

	public static void main(String[] args) {
		System.out.println(DateTime.isDateTime("2013-11-22 12:00:21"));
		System.out.println(DateTime.parseDate("2013-9-15 00:00:00", "yyyy-MM-dd HH:mm:ss"));
		System.out.println(DateTime.toTime("yyyyMMddHHmmss"));
		Date start = DateTime.parseDate("2013-9-15", "yyyy-MM-dd");
		Date end = DateTime.parseDate("2013-9-11", "yyyy-MM-dd");
		if (start.getTime() >= end.getTime()) {
			System.out.println("1>=2");
		}
		System.out.println(getDaysBetweenTwoTimes("2013-9-15", "2013-9-15"));
	}

	// 检查字符串是否为日期
	public static boolean isDate(Object value) {
		return isDateFormat(value, "^\\d{4}-\\d{1,2}-\\d{1,2}$");
	}

	//检查字符串是否为日期时间类型
	public static boolean isDateTime(Object value) {
		return isDateFormat(value, "^\\d{4}-\\d{2}-\\d{2} \\d{2}:\\d{2}:\\d{2}$");

	}

	//检查字符串是否为指定的日期类型      fromatReg 格式正则表达式
	public static boolean isDateFormat(Object value, String fromatReg) {
		Pattern pattern = Pattern.compile(fromatReg);
		if (Validation.isNULL(value) || !pattern.matcher(value.toString()).matches())
			return false;
		else
			return true;
	}

	// 解析日期类型，格式"yyyyMMdd"
	public static String toDate(String format) {
		if (Validation.isNULL(format))
			return new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(new Date());
		else
			return new SimpleDateFormat(format).format(new Date());
	}

	public static String toDate(String format, Date date) {
		if (Validation.isNULL(format))
			return new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(date);
		else
			return new SimpleDateFormat(format).format(date);
	}

	// 解析日期时间类型，格式"yyyyMMdd hh:mm:ss"
	public static Date toDateTime(Object value) {
		return parseDate(value, "yyyy-MM-dd HH:mm:ss");
	}

	public static String toTime() {
		return toTime(null);
	}

	public static String toTime(String format) {
		if (Validation.isNULL(format)) {
			return new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(new Date());
		} else {
			return new SimpleDateFormat(format).format(new Date());
		}
	}

	// 获取两个时间的间隔天数
	public static int getDaysBetweenTwoTimes(Object dateFrom, Object dateEnd) {
		Date dtFrom = parseDate(dateFrom, "yyyy-MM-dd");
		Date dtEnd = parseDate(dateEnd, "yyyy-MM-dd");
		long begin = dtFrom.getTime();
		long end = dtEnd.getTime();
		long inter = end - begin;
		if (inter < 0) {
			inter = inter * (-1);
		}
		long dateMillSec = 24 * 60 * 60 * 1000;
		Long dateCnt = inter / dateMillSec;
		long remainder = inter % dateMillSec;
		if (remainder != 0) {
			dateCnt++;
		}
		return dateCnt.intValue();
	}

	// 解析日期格式
	public static Date parseDate(Object value, String fromat) {
		try {
			DateFormat df = new SimpleDateFormat(fromat);
			return df.parse(value.toString());
		} catch (Exception ex) {

		}
		return null;
	}

	// Long转为日期，结果为String
	public static String longToDate(Long value, String format) {
		try {
			DateFormat df = null;
			if (Validation.isNULL(format)) {
				df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
			} else {
				df = new SimpleDateFormat(format);
			}
			Date dt = new Date(value);
			return df.format(dt);
		} catch (Exception ex) {
		}
		return null;
	}

}

读取Excel表中内容 

package com.zhou.ssm.util;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import javax.servlet.http.HttpServletRequest;

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 org.springframework.web.multipart.MultipartHttpServletRequest;

import com.zhou.ssm.entity.Sql;
import com.zhou.ssm.entity.Data;
import com.zhou.ssm.entity.Field;

public class ReadExcel {
	// 总行数
	private int totalRows = 0;
	// 总条数
	private int totalCells = 0;
	// 错误信息接收器
	private String errorMsg;

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

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

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

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

	public Workbook getExcelInfo(MultipartFile Mfile,String tablename,HttpServletRequest request) {
		
		MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) request;
		InputStream in = null;
		MultipartFile file = Mfile;
		if (file.isEmpty()) {
			try {
				throw new Exception("文件不存在！");
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		try {
			in = file.getInputStream();
		} catch (IOException e) {
			e.printStackTrace();
		}

		List<Field> fieldList = new ArrayList<Field>();
		FileInputStream is = null;