Excel导入MySql数据库

1、JSP页面

	<div id="hideDiag">
		<form id="dataForm" enctype="multipart/form-data" >
			<table class="tableForm" cellspacing="8">
				<tr>
					<td class="td100" align="right">请选择excel文件:</td>
					<td class="td200">
					 <input type="file" name="excelFile" id="excelFile"/>
					</td>
				</tr>
			</table>
    	</form>
	</div>
	
	<div id="progress" style="display: none" >
		<div id="title">
			<span id="text">导入进度</span>
			<div id="close">X</div>
		</div>
			<div id="progressBar">
				<div id="uploaded"></div>
			</div>
			<div id="info"></div>
	</div>

 2、JS

function save(){
		if(!$("#dataForm").form('validate')){
			return ;
		}
		var form = $("#dataForm");
	    var options  = {    
	        url:'/mallGiftAction/save.shtml',    
	        type:'post', 
	        beforeSend: ajaxLoading, 
	        success:function(data)    
	        {    
	        	ajaxLoadEnd();
	         	if(data.success){
	         		$('#tb_mallGift').datagrid('reload');
	         		$('#tb_mallGift').datagrid('clearSelections');
	         	}else{	
	         		$.messager.info(data.msg);
	         		$("#progress").css("display", "none");
	         	}
	        }    
	    };    
	    form.ajaxSubmit(options); 
}

function ajaxLoading(){   
	$('#hideDiag').dialog('close');
	$("#progress").css("display", "block");
	var myDate = new Date();
	startTime = myDate.getTime();
	$(this).attr("disabled", true);
	window.setTimeout("getProgressBar()", 1000);
    $("<div class=\"datagrid-mask\"></div>").css({display:"block",width:"100%",height:$(window).height()}).appendTo("body");   
}   
function ajaxLoadEnd(){  
     $(".datagrid-mask").remove();   
} 

 3、Action

@RequestMapping(value = "/save")
	@ResponseBody
	public ResultVO save(String excelFilePath, MultipartFile excelFile, HttpServletRequest request, HttpServletResponse response) {

		try {
			mallGiftService.doStockin(excelFile,request);
			return success("导入成功");
		} catch (Exception e) {
			return error(e.getMessage());
		}

	}

 4、Service

public void doStockin(MultipartFile excelFile, HttpServletRequest request) throws Exception {

		// excel读取的字段
		Map<String, String> beanNameMap = new HashMap<String, String>();
		beanNameMap.put("卡号", "cardNo");
		beanNameMap.put("密码", "verifyCode");

		// 读取excel数据
		List<Map<String, Object>> list = ExcelUtil.parseExcel(excelFile, beanNameMap);
		List<MallGift> mallGiftList = ExcelUtil.toObjectList(list, MallGift.class);

		int totalCount = mallGiftList.size();
		int count = 1;
		// excel数据入库
		for (MallGift mallGift : mallGiftList) {
			mallGift.setStatus(MallGift.STATUS_NEW);
			// 加载监听器
			ProgressModel status = new ProgressModel();
			status.setTotalCount(totalCount);
			status.setOverCount(count++);
			request.getSession().setAttribute("status", status);
			mallGiftDao.buffSave(mallGift);
		}
	}

 5、ExcelUtil

public static List<Map<String, Object>> parseExcel(MultipartFile excelFile, Map<String, String> beanNameMap) throws IOException {

		List<Map<String, Object>> result = new LinkedList<Map<String, Object>>();
		InputStream is = excelFile.getInputStream();
		XSSFWorkbook xssfWorkbook = new XSSFWorkbook(is);
		XSSFRow beanNameRow = xssfWorkbook.getSheetAt(0).getRow(0);
		int excleRowLength = xssfWorkbook.getSheetAt(0).getRow(0).getPhysicalNumberOfCells();
		String[] columnName = new String[excleRowLength];
		for (int i = 0; i < columnName.length; i++) {
			XSSFCell name = beanNameRow.getCell(i);
			Iterator<String> it = beanNameMap.keySet().iterator();
			while (it.hasNext()) {
				String key = (String) it.next();
				String value = beanNameMap.get(key);
				if (key.trim().equals(name.getStringCellValue())) {
					columnName[i] = value;
				}
			}

		}

		for (int sheetIndex = 0; sheetIndex < xssfWorkbook.getNumberOfSheets(); sheetIndex++) {
			XSSFSheet sheet = (XSSFSheet) xssfWorkbook.getSheetAt(sheetIndex);
			for (int rowIndex = 1; rowIndex < sheet.getPhysicalNumberOfRows(); rowIndex++) {
				XSSFRow row = sheet.getRow(rowIndex);
				Map<String, Object> map = new HashMap<String, Object>();
				for (int cellIndex = 0; cellIndex < xssfWorkbook.getSheetAt(0).getRow(0).getPhysicalNumberOfCells(); cellIndex++) {
					XSSFCell cell = row.getCell(cellIndex);
					String value = getCellValue(cell);
					if (StringUtils.isEmpty(value)) {
						throw new BaseException("Excel表格中的第:" + (rowIndex + 1) + "行第" + (cellIndex + 1) + "列为空.");
					}
					if (columnName[cellIndex] != null && columnName[cellIndex].trim().length() > 0) {
						map.put(columnName[cellIndex].trim(), getCellValue(cell));
					}
				}
				result.add(map);
			}
		}
		return result;
	}

	/**
	 * 利用反射将 List<Map<String,Object>>数据 生成相应的List<T>数据
	 * 
	 */
	public static <T> List<T> toObjectList(List<Map<String, Object>> list, Class<T> clazz) throws Exception {

		List<T> returnList = new LinkedList<T>();
		for (int i = 0; i < list.size(); i++) {
			Set<Map.Entry<String, Object>> set = list.get(i).entrySet();
			Iterator<Entry<String, Object>> it = set.iterator();
			T obj = clazz.newInstance();
			Method[] methods = clazz.getDeclaredMethods();
			while (it.hasNext()) {
				Map.Entry<String, Object> entry = (Map.Entry<String, Object>) it.next();
				for (Method m : methods) {
					if (m.getName().startsWith("set")) {
						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);
		}
		return returnList;
	}

	/**
	 * 获取当前单元格内容
	 */
	private static String getCellValue(Cell cell) {

		String value = "";
		if (cell != null) {
			switch (cell.getCellType()) {
			case Cell.CELL_TYPE_NUMERIC:
				if (HSSFDateUtil.isCellDateFormatted(cell)) {
					SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
					Date date = HSSFDateUtil.getJavaDate(cell.getNumericCellValue());
					value = sdf.format(date);
				} else {
					DecimalFormat format = new DecimalFormat("###################.###########");
					value = format.format(cell.getNumericCellValue());
				}
				break;
			case Cell.CELL_TYPE_STRING:
				value = cell.getStringCellValue();
				break;
			case Cell.CELL_TYPE_BOOLEAN:
				Boolean data = cell.getBooleanCellValue();
				value = data.toString();
				break;
			case Cell.CELL_TYPE_ERROR:
				System.out.println("单元格内容出现错误");
				break;
			case Cell.CELL_TYPE_FORMULA:
				DecimalFormat format = new DecimalFormat("###################.###########");
				value = format.format(cell.getNumericCellValue());
				if (value.equals("NaN")) {
					value = cell.getStringCellValue().toString();
				}
				break;
			case Cell.CELL_TYPE_BLANK:
				System.out.println("单元格内容 为空值 ");
				break;
			default:
				value = cell.getStringCellValue().toString();
				break;
			}
		}
		return value;
	}

	public static void createExcel(List<Map<String, Object>> list, LinkedHashMap<String, String> headMap, String fileName) {

		HSSFWorkbook workbook = new HSSFWorkbook();
		HSSFSheet sheet = workbook.createSheet();
		sheet.setDefaultRowHeightInPoints(10);
		sheet.setDefaultColumnWidth(10);
		int rowNum = 0;

		// 设置标题行
		HSSFCellStyle titleStyle = workbook.createCellStyle();
		titleStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直
		titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 水平
		HSSFFont font = workbook.createFont();
		font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 加粗
		titleStyle.setFont(font);
		HSSFRow titleRow = sheet.createRow((short) 0);
		int m = 0;
		for (String title : headMap.values()) {
			HSSFCell cell = titleRow.createCell(m);
			cell.setCellValue(title);
			cell.setCellStyle(titleStyle);
			m++;
		}
		rowNum++;

		HSSFCellStyle style = workbook.createCellStyle();
		style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直
		style.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 水平

		// 设置内容行
		for (Map<String, Object> map : list) {
			HSSFRow row = sheet.createRow((short) rowNum);
			int i = 0;
			for (String value : headMap.keySet()) {
				HSSFCell cell = row.createCell(i);
				cell.setCellValue(String.valueOf(map.get(value)));
				cell.setCellStyle(style);
				i++;
			}
			rowNum++;
		}
		OutputStream out = null;

		try {
			out = new FileOutputStream(fileName);
			workbook.write(out);
		} catch (IOException e) {
			e.printStackTrace();
		} finally {
			if (out != null) {
				try {
					out.close();
				} catch (IOException e) {
					e.printStackTrace();
				}
			}
		}
	}

 6、ProgressModel

private long pBytesRead = 0L;

	private long pContentLength = 0L;

	private int totalCount;

	private int overCount;

	public ProgressModel() {
		pBytesRead = 0L;
		pContentLength = 0L;
	}

	public long getPBytesRead() {

		return pBytesRead;
	}

	public void setPBytesRead(long bytesRead) {

		pBytesRead = bytesRead;
	}

	public long getPContentLength() {

		return pContentLength;
	}

	public void setPContentLength(long contentLength) {

		pContentLength = contentLength;
	}

	public int getTotalCount() {

		return totalCount;
	}

	public void setTotalCount(int totalCount) {

		this.totalCount = totalCount;
	}

	public int getOverCount() {

		return overCount;
	}

	public void setOverCount(int overCount) {

		this.overCount = overCount;
	}

 7、MallGiftDao

public void buffSave(MallGift mallGift) {

		try {
			super.buffSave(mallGift);//存入数据库方法
		} catch (RuntimeException e) {
			Throwable cause = e.getCause();
			if (cause instanceof MySQLIntegrityConstraintViolationException) {
				throw new RuntimeException("卡号:" + mallGift.getCardNo() + "在数据库已经存在,不能重复导入");
			}
		}
		//myCache.set(mallGift);
		//if (StringUtils.isNotEmpty(mallGift.getCardNo()))
		//	myCache.hSet(mallGift, "cardNo", mallGift.getCardNo());

	}