花了一天半的时间学习了一下导入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("<script>parent.uploadCallback('0','请选择导入的文件');</script>");
return;
}
/**
* 清空临时表
<select id="truncate" >
truncate table USERTEMP
</select>
*/
excelImportManager.truncate();
/**
* 读取Excel数据封装为List
*/
excelToUserTemp = saveExcelToUserTemp(request, response, upLoadFile);
if (excelToUserTemp.getResultFlag() == 0) {
response.setCharacterEncoding("UTF-8");
response.getWriter().println(
"<script>parent.uploadCallback('0','" + excelToUserTemp.getErrorMsg() + "');</script>");
return;
} else {// 保存数据到临时表
@SuppressWarnings("unchecked")
List<String> resultList = (List<String>) excelToUserTemp.getResultList();
if (resultList != null && resultList.size() > 0) {
for (String sqlStr : resultList) {
this.excelImportManager.excuteInsertSql(sqlStr);
}
}
}
HashMap<String, String> parmMap = new HashMap<String, String>();
parmMap.put("totalCount", "");
// 调用存储过程,判断数据格式,保存数据到正式表,记录错误日志
userManager.batchImportUser(parmMap);
String totalCount = parmMap.get("totalCount");
System.out.println("保存的总记录数为:" + totalCount);
response.setCharacterEncoding("UTF-8");
response.getWriter().println("<script>parent.uploadCallback('0','导入成功!共导入" + totalCount + "条');</script>");
} catch (Exception e) {
response.setCharacterEncoding("UTF-8");
response.getWriter().println(
"<script>parent.uploadCallback('0','导入失败!'" + excelToUserTemp.getErrorMsg() + ");</script>");
e.printStackTrace();
}
}
/**
* 导入Excel到临时表
*
* @throws IOException
*/
public ExcelImportResult saveExcelToUserTemp(HttpServletRequest request, HttpServletResponse response,
UpLoadFile upLoadFile) throws Exception {
String result = "";
ExcelImportResult excelImportResult = new ExcelImportResult();
List<String> dataList = new ArrayList<String>();
// 创建工作簿
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,即成功导入的条数。
<!-- 存储过程 -->
<parameterMap id="map_batchImportUser" type="java.util.HashMap">
<parameter property="totalCount" jdbcType="VARCHAR" javaType="java.lang.String" mode="OUT" />
</parameterMap>
<select id="batchImportUser" parameterMap="map_batchImportUser" statementType="CALLABLE" >
<![CDATA[
{call BATCHIMPORTUSER(?)}
]]>
</select>
存储过程BATCHIMPORTUSER为:
CREATE OR REPLACE procedure "BATCHIMPORTUSER"(TOTALCOUNT out VARCHAR2) is v_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 存储了所有数据 begin dbms_output.put_line('start--BATCHIMPORTUSER'); -- 读取临时表,验证数据。 totalCount1:=0; TOTALCOUNT:=0; for us in cursor LOOP if 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<String> dataList = new ArrayList<String>();
// 创建工作簿
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页面上传文件:
<tr>
<td>
<div class="file_down">
<input name="file" type="file" id="file" style="width:90%"/>
</div>
</td>
</tr>
后台能获取到一个:
UpLoadFile upLoadFile
public 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