导入Excel用户表，调用存储过程-白红宇

导入Excel用户表，调用存储过程

阅读量：5793 次

发布时间：2019-06-18

本文共 8738 字，大约阅读时间需要 29 分钟。

花了一天半的时间学习了一下导入Excel用户表，调用存储过程，主要是学习存储过程。因为之前没有具体在项目中应用过。

这里我们采用导入Excel到临时表，然后存储过程中读取临时表判断数据类型和数据格式，然后保存到正式表。

导入Excel采用spring 的POI技术。

本文内容较多，请选择性阅读。

controller里面的代码：

/**	 * Excel导入用户表，调用存储过程，先导入临时表，再在存储过程中判断，判断通过后导入正式表，错误的则记录错误日志表中。 lijianbo	 * 	 * @param request	 * @param respnse	 * @throws Exception	 */	public void excelImport(HttpServletRequest request, HttpServletResponse response, UpLoadFile upLoadFile)			throws Exception {		ExcelImportResult excelToUserTemp = new ExcelImportResult();		try {			response.setCharacterEncoding("GBK");			if (upLoadFile == null || upLoadFile.isEmpty()) {				response.getWriter().println("");				return;			}			/**			 * 清空临时表                            
     
                                    truncate table USERTEMP                    	                    	*/			excelImportManager.truncate();			/**			 * 读取Excel数据封装为List			 */			excelToUserTemp = saveExcelToUserTemp(request, response, upLoadFile);			if (excelToUserTemp.getResultFlag() == 0) {				response.setCharacterEncoding("UTF-8");				response.getWriter().println(						"
      ");				return;			} else {// 保存数据到临时表				@SuppressWarnings("unchecked")				List
      
        resultList = (List
       
        ) excelToUserTemp.getResultList();				if (resultList != null && resultList.size() > 0) {					for (String sqlStr : resultList) {						this.excelImportManager.excuteInsertSql(sqlStr);					}				}			}			HashMap
        
          parmMap = new HashMap
         
          ();			parmMap.put("totalCount", "");			// 调用存储过程，判断数据格式，保存数据到正式表，记录错误日志			userManager.batchImportUser(parmMap);			String totalCount = parmMap.get("totalCount");			System.out.println("保存的总记录数为：" + totalCount);			response.setCharacterEncoding("UTF-8");			response.getWriter().println("
          ");		} catch (Exception e) {			response.setCharacterEncoding("UTF-8");			response.getWriter().println(					"
          ");			e.printStackTrace();		}	}	/**	 * 导入Excel到临时表	 * 	 * @throws IOException	 */	public ExcelImportResult saveExcelToUserTemp(HttpServletRequest request, HttpServletResponse response,			UpLoadFile upLoadFile) throws Exception {		String result = "";		ExcelImportResult excelImportResult = new ExcelImportResult();		List
          
            dataList = new ArrayList
           
            (); // 创建工作簿 Workbook wb = WorkbookFactory.create(upLoadFile.getFile().getInputStream()); // 取得第一个sheets Sheet sheet = wb.getSheetAt(0); // 最大的行数 int lastRowNum = sheet.getLastRowNum(); // 检查表的列数是否和模板一致 result = excelLineCheck(sheet); if ("true".equals(result)) { // 读取excel内容 for (int i = 1; i <= lastRowNum; i++) { Row row = sheet.getRow(i); // 获取行(row)对象 if (row == null) { // row为空的话,不处理 continue; } String sqlvalue = ""; for (int j = 0; j < 4; j++) { Cell cell = row.getCell(j); // 获得单元格(cell)对象 // 将单元格的数据添加至一个对象 在sql中 sqlvalue = sqlvalue + "'" + cell.toString() + "',"; } sqlvalue = sqlvalue.substring(0, sqlvalue.length() - 1); String sql = "insert into USERTEMP(ID,NAME,CARDTYPE,CARDNO,STATUS) values(SEQ_USERTEMP.NEXTVAL," + sqlvalue + ")"; System.out.println("sql:" + sql); dataList.add(sql); excelImportResult.setResultList(dataList); excelImportResult.setResultFlag(1); } } else { excelImportResult.setErrorMsg(result); excelImportResult.setResultFlag(0); } return excelImportResult; } /**  * 检查Excel的列是否与模板一致  * @param sheet  * @return  */ private String excelLineCheck(Sheet sheet) { /**  * getPhysicalNumberOfRows()获取的是物理行数，也就是不包括那些空行（隔行）的情况。  * getLastRowNum()获取的是最后一行的编号（编号从0开始）  */ // 物理行数（不包括隔行） int rowNumbers = sheet.getPhysicalNumberOfRows(); if (rowNumbers == 0) { return "excel中数据为空！"; } Row excelRow = sheet.getRow(0); int excelFirstRow = excelRow.getFirstCellNum(); int excelLastRow = excelRow.getLastCellNum(); if (4 != (excelLastRow - excelFirstRow)) { System.out.println("模版列数与excel列数不相符，请检查"); return "模版列数与excel列数不相符，请检查"; } else { return "true"; } }

user.xml里面调用存储过程。参数我们只有一个返回参数count，即成功导入的条数。

存储过程BATCHIMPORTUSER为：

CREATE OR REPLACE procedure "BATCHIMPORTUSER"(TOTALCOUNT  out VARCHAR2) isv_sql varchar2(2000);excellogid NUMBER;userId NUMBER;totalCount1 NUMBER;cardtype varchar2(255);status varchar2(125);cardNo varchar2(255);flag NUMBER;Cursor cursor is select * from USERTEMP;-- 显性游标，cursor 存储了所有数据begindbms_output.put_line('start--BATCHIMPORTUSER');-- 读取临时表，验证数据。totalCount1:=0;TOTALCOUNT:=0;for us in cursor LOOPif us.CARDTYPE ='×××' then   cardtype:=1;else if us.CARDTYPE ='护照' then 	cardtype:=2;else if us.CARDTYPE ='驾驶证' then 	cardtype:=3;else -- 保存错误日志,证件类型错误v_sql := 'SELECT SEQ_EXCEL_LOG.NEXTVAL FROM dual';execute immediate v_sql into excellogid; INSERT INTO EXCEL_LOG(ID,ROW_NO,CODE,ERROR_FIELD,TYPE,CREATE_TIME,BATCH_NO,BATCH_NAME,REMARK ) VALUES   (excellogid,'','','cardtype','1',sysdate,'',us.name,'证件类型错误');flag:=0;end if;end if;end if;if us.STATUS ='正常' then 	status:=2;else if us.STATUS ='异常' then 	status:=1;else -- 保存错误日志，状态错误v_sql := 'SELECT SEQ_EXCEL_LOG.NEXTVAL FROM dual';execute immediate v_sql into excellogid; INSERT INTO EXCEL_LOG( ID,ROW_NO,CODE,ERROR_FIELD,TYPE,CREATE_TIME,BATCH_NO,BATCH_NAME,REMARK ) VALUES   (excellogid,'','','status','2',sysdate,'',us.name,'状态错误,只能填正常、异常');flag:=0;end if;end if;--判断是否为数字if  translate(replace(us.CARDNO,'0',''), '0123456789', '$') is not null then	-- 保存错误日志，证件号格式错误。v_sql := 'SELECT SEQ_EXCEL_LOG.NEXTVAL FROM dual';execute immediate v_sql into excellogid; INSERT INTO EXCEL_LOG( ID,ROW_NO,CODE,ERROR_FIELD,TYPE,CREATE_TIME,BATCH_NO,BATCH_NAME,REMARK ) VALUES   (excellogid,'','','cardNo','3',sysdate,'',us.name,'证件号格式错误，必须为字母数字格式');flag:=0;else 	cardNo:=us.CARDNO;end if;v_sql := 'SELECT	SEQ_USERS.NEXTVAL AS id FROM dual';execute immediate v_sql into userId;--用户数据导入if flag=0 then 	null;else	INSERT INTO USERS(ID,COMPANY_ID,NAME,EMAIL,STATUS,PHONE,GMT_UPDATE_PWD,CONTACT_TYPE,MOBILE,IDX_NUM,	ID_TYPE,ID_NO,ID_EXPIRE_DATE,ID_TYPE2,ID_NO2,SEX,ETHNICITY,NATION,POST_ADDRESS,RESIDENCE_ADDRESS,HOMETOWN,	BIRTHDAY,BANK,BANK_ACCOUNT_NO,MARITAL_STATUS,FINGER_PRINT ,BLOOD_TYPE,HOBBY,WEI_XIN_ID,QQ,CHANGE_BY,	CHANGE_AT,CREATE_BY,CREATE_AT,PROVIDER_ID, LOGIN_ID,PASSWORD ) VALUES (userId,'',	us.NAME,'',status,'','','','','',cardtype,cardNo,'','','','','','','','','','','','','','','','','','','','','',sysdate,'','','');	totalCount1:=totalCount1+1;end if;end LOOP;totalCount:=totalCount1;end ;

总结：

①可以直接在sql中用SEQ_USERTEMP.NEXTVAL 表示自增的主键ID（前提是已经创建了序列 SEQ_USERTEMP）

这里还要注意zhangs等字符串要加单引号，否则会报错。

  INSERT INTO USERTEMP(id,name,cardtype,cardno,status)  VALUES(SEQ_USERTEMP.NEXTVAL,'zhangs','1','511322','2');

②逻辑判断时，如果只是需要判读是否成功，则一般返回TRUE或FALSE。

如果要根据不同的情况处理，则需要封装到一个类或map中，根据不同的flag处理。

如果是最后返回页面了，则通常为成功或失败，这里则用try--catch。

③POI技术：

*主要就是创建工作簿workbook

*创建表sheets

*获取最大行，循环，

*对每一行数据循环，取单元格。

*处理具体的单元格。

/**	 * 导入Excel到临时表	 * 	 * @throws IOException	 */	public ExcelImportResult saveExcelToUserTemp(HttpServletRequest request, HttpServletResponse response,			UpLoadFile upLoadFile) throws Exception {		String result = "";		ExcelImportResult excelImportResult = new ExcelImportResult();		List
     
       dataList = new ArrayList
      
       ();		// 创建工作簿		Workbook wb = WorkbookFactory.create(upLoadFile.getFile().getInputStream());		// 取得第一个sheets		Sheet sheet = wb.getSheetAt(0);		// 最大的行数		int lastRowNum = sheet.getLastRowNum();		// 检查表的列数是否和模板一致		result = excelLineCheck(sheet);		if ("true".equals(result)) {			// 读取excel内容			for (int i = 1; i <= lastRowNum; i++) {				Row row = sheet.getRow(i); // 获取行(row)对象				if (row == null) {					// row为空的话,不处理					continue;				}				String sqlvalue = "";				for (int j = 0; j < 4; j++) {					Cell cell = row.getCell(j); // 获得单元格(cell)对象					// 将单元格的数据添加至一个对象 在sql中					sqlvalue = sqlvalue + "'" + cell.toString() + "',";				}				sqlvalue = sqlvalue.substring(0, sqlvalue.length() - 1);				String sql = "insert into USERTEMP(ID,NAME,CARDTYPE,CARDNO,STATUS) values(SEQ_USERTEMP.NEXTVAL,"						+ sqlvalue + ")";				dataList.add(sql);				excelImportResult.setResultList(dataList);				excelImportResult.setResultFlag(1);			}		} else {			excelImportResult.setErrorMsg(result);			excelImportResult.setResultFlag(0);		}		return excelImportResult;	}

④存储过程中的，在loop循环中继续下一次循环，怎么做？相当于Java中的continue。

？？？？？

⑤获取Excel文件：

jsp页面上传文件：

后台能获取到一个：

UpLoadFile upLoadFilepublic class UpLoadFile{	//文件  二进制	protected MultipartFile	file;	protected String		busiAlias;	protected Long			busiId;	protected String		description;	protected Long			categoryId;	protected Long			upLoadStaffId;	protected String		ifDown;}

这样就能得到Excel文件MultipartFile。

转载于:https://blog.51cto.com/jianboli/1897699

你可能感兴趣的文章

机器学习实战_一个完整的程序（一）

查看>>

Web框架的常用架构模式（JavaScript语言）