SSM框架poi实现excel数据导入到MySQL数据库

SSM框架poi实现excel数据导入到MySQL数据库

前端代码

<td style="vertical-align:top;padding-left:2px;">
	<a class="btn btn-light btn-xs" onclick="fromExcel();" title="从EXCEL导入">
		<i id="nav-search-icon"	class="ace-icon fa fa-download bigger-110 nav-search-icon blue"></i>
	</a>
</td>

//打开上传excel页面
	function fromExcel() {
		 top.jzts();
		 var diag = new top.Dialog();
		 diag.Drag=true;
		 diag.Title ="EXCEL 导入到数据库";
		 diag.URL = '<%=basePath%>village/goUploadExcel.do';
		 diag.Width = 300;
		 diag.Height = 150;
		 diag.CancelEvent = function() { //关闭事件
			 if(diag.innerFrame.contentWindow.document.getElementById('zhongxin').style.display == 'none') {
				 if('${page.currentPage}' == '0') {
					 top.jzts();
					 setTimeout("self.location.reload()", 100);
				 } else {
					 nextPage(${page.currentPage});
				 }
			}
			diag.close();
		 };
		 diag.show();
	}	

后台代码

/**打开上传EXCEL页面
	 * @return
	 * @throws Exception
	 */
	@RequestMapping(value="/goUploadExcel")
	public ModelAndView goUploadExcel()throws Exception{
		ModelAndView mv = this.getModelAndView();
		mv.setViewName("system/village/uploadexcel");
		return mv;
	}

excel上传页面

<form action="village/readExcel.do" name="Form" id="Form" method="post" enctype="multipart/form-data">
								<div id="zhongxin">
								<table style="width:95%;" >
									<tr>
										<td style="padding-top: 20px;"><input type="file" id="excel" name="excel" style="width:50px;" onchange="fileType(this)" /></td>
									</tr>
									<tr>
										<td style="text-align: center;padding-top: 10px;">
											<a class="btn btn-mini btn-primary" onclick="save();">导入</a>
											<a class="btn btn-mini btn-danger" onclick="top.Dialog.close();">取消</a>
										</td>
									</tr>
								</table>
								</div>
								<div id="zhongxin2" class="center" style="display:none"><br/><img src="static/images/jzx.gif" /><br/><h4 class="lighter block green"></h4></div>
							</form>

后台代码

 * 将Excel文件的数据导入到数据库
	 * @param file
	 * @return
	 * @throws Exception
	 */
	@RequestMapping(value = "/readExcel")
	public ModelAndView readExcel(@RequestParam(value = "excel", required = false) MultipartFile file) throws Exception {
		if (!Jurisdiction.buttonJurisdiction(menuUrl, "add")) {
			return null;
		}
		ModelAndView mv = this.getModelAndView();
		if (null != file && !file.isEmpty()) {
			String filePath = PathUtil.getWebAppRootPath() + Const.FILEPATHFILE;								//文件上传路径
			System.out.println("filePath----->"+filePath);
			String fileName =  FileUpload.fileUp(file, filePath, "appointmentExcel");							//执行上传
			List<PageData> listPd = (List) ObjectExcelRead.readExcel(filePath, fileName, 1, 0, 0);		//执行读EXCEL操作,读出的数据导入List 1:从第2行开始；0:从第A列开始；0:第0个sheet
			List<PageData> list = new ArrayList<PageData>();
			for(int i=0;i<listPd.size();i++){
				PageData pd = new PageData();
				pd.put("apartment_id", listPd.get(i).getString("var0").equals("")?null:listPd.get(i).getString("var0"));//公寓编号
				pd.put("apartment_name", listPd.get(i).getString("var1").equals("")?null:listPd.get(i).getString("var1"));					//公寓名称
				pd.put("village_address", listPd.get(i).getString("var2").equals("")?null:listPd.get(i).getString("var2"));					//公寓地址
				pd.put("img_id", listPd.get(i).getString("var3").equals("")?null:listPd.get(i).getString("var3"));			//图片id			
				pd.put("region", listPd.get(i).getString("var4").equals("")?null:listPd.get(i).getString("var4"));					//区
				pd.put("v_describe", listPd.get(i).getString("var5").equals("")?null:listPd.get(i).getString("var5"));				//简介			
				list.add(pd);			
			}
			villageService.addList(list);
		}
		mv.addObject("msg","success");
		mv.setViewName("save_result");
		return mv;
	}

工具类

/**
 * 从EXCEL导入到数据库
 * @version
 */
public class ObjectExcelRead {

	/**
	 * @param filepath //文件路径
	 * @param filename //文件名
	 * @param startrow //开始行号
	 * @param startcol //开始列号
	 * @param sheetnum //sheet
	 * @return list
	 */
	public static List<Object> readExcel(String filepath, String filename, int startrow, int startcol, int sheetnum) {
		List<Object> varList = new ArrayList<Object>();
		File target = new File(filepath, filename);
		Workbook workbook = getWorkbook(target.getAbsolutePath());
		if(null == workbook) {
			LogHelper.err(" readExcel, workbook is null, please check !");
			return null;
		}
		Sheet sheet = workbook.getSheetAt(sheetnum); 					//sheet 从0开始
		if(null == sheet) {
			LogHelper.err(" readExcel, sheet is null, sheetnum ( " + sheetnum + " ) is error !");
			return null;
		}
		int rowNum = sheet.getLastRowNum() + 1; 					//取得最后一行的行号
		PageData varpd = null;
		Row row = null;
		Cell cell = null;
		for (int i = startrow; i < rowNum; i++) {					//行循环开始
			varpd = new PageData();
			row = sheet.getRow(i); 							//行
			int cellNum = row.getLastCellNum(); 					//每行的最后一个单元格位置
			for (int j = startcol; j < cellNum; j++) {				//列循环开始
				cell = row.getCell(j);
				String cellValue = null;
				if (null != cell) {
					switch (cell.getCellType()) { 					// 判断excel单元格内容的格式，并对其进行转换，以便插入数据库
					case 0:
						cellValue = String.valueOf((int) cell.getNumericCellValue());
						break;
					case 1:
						cellValue = cell.getStringCellValue();
						break;
					case 2:
						cellValue = cell.getNumericCellValue() + "";
						// cellValue = String.valueOf(cell.getDateCellValue());
						break;
					case 3:
						cellValue = "";
						break;
					case 4:
						cellValue = String.valueOf(cell.getBooleanCellValue());
						break;
					case 5:
						cellValue = String.valueOf(cell.getErrorCellValue());
						break;
					}
				} else {
					cellValue = "";
				}
				varpd.put("var" + j, cellValue);
			}
			varList.add(varpd);
		}
		return varList;
	}
	
	/**
	 * 读取 Excel 转换为 List<String[]>   
	 * <p> 此方法会自动过滤空行数据，如果需要对数据的字段做校验，需要读取完数据之后，再验证数据的有效性
	 * @param targetFilePath  	需要解析的 Excel的完整路径， 如：/home/tomcat/apache-tomcat/template/你好.xls
	 * @param beginRowNum		excel 数据开始行
	 * @param beginColNum		excel 数据开始列
	 * @param askColNum			excel 数据需要解析的总列数
	 * @param sheetnum			sheet 页索引
	 * @return
	 */
	public static List<String[]> readExcel(String targetFilePath, int beginRowNum, int beginColNum, int askColNum, int sheetnum) {
		Workbook workbook = getWorkbook(targetFilePath);
		if(null == workbook) {
			LogHelper.err(" readExcel, workbook is null, please check !");
			return null;
		}
		Sheet sheet = workbook.getSheetAt(sheetnum); 					//sheet 从0开始
		if(null == sheet) {
			LogHelper.err(" readExcel, sheet is null, sheetnum ( " + sheetnum + " ) is error !");
			return null;
		}
		
		List<String[]> dataList = new ArrayList<String[]>();
		Row row = null;
		Cell cell = null;
		// beginRowNum 指代非数据行数(数据从第几行开始导入)
		for (int i = beginRowNum; i < sheet.getPhysicalNumberOfRows(); i++) {
			row = sheet.getRow(i);
			boolean allEmpty = true; // 过滤掉空行数据
			if(row == null) continue;
			for (int j = beginColNum; j < askColNum; j++) {
				cell = row.getCell(j);
				if (cell != null) {
					cell.setCellType(Cell.CELL_TYPE_STRING);
					String data = cell.getStringCellValue();
					if (!data.equals("")) {
						allEmpty = false;
						break;
					}
				}
			}
			if (allEmpty) {
				continue;
			}
			 用表头来判断有多少列
			int cellNum = sheet.getRow(beginRowNum - 1).getPhysicalNumberOfCells();
			String rowData[] = new String[askColNum + 1]; add by shusheng.wang 最后一个值存放行号
			if (cellNum >= askColNum) {
				for (int j = 0; j < askColNum; j++) {
					cell = row.getCell(j);
					rowData[j] = "";
					if (cell != null) {
						//LogHelper.debug("=====================" + (cell.getCellType() == Cell.CELL_TYPE_NUMERIC));
						int cellType = cell.getCellType();
						if (cellType == Cell.CELL_TYPE_NUMERIC && DateUtil.isCellDateFormatted(cell)) {
							// 用于转化为日期格式
							Date d = cell.getDateCellValue();
							DateFormat formater = new SimpleDateFormat("yyyyMMdd");
							rowData[j] = formater.format(d);
						} else {
							cell.setCellType(Cell.CELL_TYPE_STRING);
							rowData[j] = cell.getStringCellValue();
						}
					}
				}
			}// 如果该行数据列数小于配置的列数，则先按数据列数读取，后面的补空字符串
			else {
				for (int j = 0; j < cellNum; j++) {
					cell = row.getCell(j);
					rowData[j] = "";
					if (cell != null) {
						//LogHelper.debug("=====================" + (cell.getCellType() == Cell.CELL_TYPE_NUMERIC));
   						int cellType = cell.getCellType();
						if (cellType == Cell.CELL_TYPE_NUMERIC && DateUtil.isCellDateFormatted(cell)) {
							// 用于转化为日期格式
							Date d = cell.getDateCellValue();
							DateFormat formater = new SimpleDateFormat("yyyyMMdd");
							rowData[j] = formater.format(d);
						} else {
							cell.setCellType(Cell.CELL_TYPE_STRING);
							rowData[j] = cell.getStringCellValue();
						}
   					}
				}
				for (int j = cellNum; j < askColNum; j++) {
					rowData[j] = "";
				}
			}
			rowData[askColNum] = (i + 1) + "";
			dataList.add(rowData);
		}
    	return dataList;
    }
	
	private static Workbook getWorkbook(String targetFilePath) {
		if (Tools.isEmpty(targetFilePath)) {
			LogHelper.err(" getWorkbook, targetFilePath is empty !");
			return null;
		}
		Workbook workbook = null;
		try {
			FileInputStream fis = new FileInputStream(targetFilePath);
			//针对xls和xlsx做不同处理
			if(targetFilePath.endsWith(".xls")){
				workbook = new HSSFWorkbook(new POIFSFileSystem(fis));
			} else if(targetFilePath.endsWith(".xlsx")){
				workbook = new XSSFWorkbook(fis);
			}
			fis.close();
		} catch (Exception e) {
			LogHelper.err(" getWorkbook, file exception : " + e.getMessage());
		}
		return workbook;
	}
	
}