* 督导结果数据导入
* @param file 导入excel文件实体
* @param request
* @param session
* @return
* @throws Exception
*/
@RequestMapping(value="/surveyDataImport.html",method={RequestMethod.POST},produces= "text/html;charset=UTF-8")
public@ResponseBody String surveyDataImport(@RequestParam("file") CommonsMultipartFile file,HttpServletRequest request,HttpSession session,HttpServletResponse response) throws Exception{
InputStream is = null;
Workbook workbook = null;
String returnStr = "";
JSONObject jsonObj = new JSONObject();
String fileName = "";
boolean flag = false;
try {
if(file != null){
fileName = file.getOriginalFilename();
}else{
jsonObj.put("status", -1);
jsonObj.put("msg", "请选择文件在上传...");
return jsonObj.toString();
}
//高版本excel文件处理
is = file.getInputStream();
workbook = new XSSFWorkbook(is);
flag = true;
} catch (Exception e) {
if(workbook == null){//低版本excel文件处理
is = file.getInputStream();
workbook = new HSSFWorkbook(is);
flag = true;
}
}finally{
if(!flag || workbook == null){
jsonObj.put("status", -1);
jsonObj.put("msg", "该上传文件解析失败,请选择重新上传...");
return jsonObj.toString();
}
if(is != null){
is.close();
}
}
logger.info("该excel文件【"+ fileName +"】数据开始导入,请稍等...");
returnStr = serveyDataImportService. eduSurveyDataImport(workbook, fileName);
return returnStr;
}
public String eduSurveyDataImport(Workbook workbook, String fileName){
//根据项目名称判断数据文件数据是否重复导入
int beginIndex = fileName.indexOf("“");
int endIndex = fileName.indexOf("”");
String itemName = fileName.substring(beginIndex + 1, endIndex);
JSONObject jsonObj = new JSONObject();
List<EduSurveyMain>eduSurveyMainList = eduSurveyMainDao.getEduSurveyMainbyItemName(itemName);
if(eduSurveyMainList != null && eduSurveyMainList.size() > 0){
jsonObj.put("status", -1);
jsonObj.put("msg", "该文件内容已经导过,请勿重复添加...");
return jsonObj.toString();
}
//1.将excel数据处理并保存到edu_survey_detail_log表中
List<EduSurveyDetailLog> surveyLogList = excelData2DetailList(workbook);
boolean importFlag = false;
try{
importFlag = eduSurveyDetailLogDao.saveLog(surveyLogList);
if(importFlag){
//2.如果数据保存成功,开始掉存储过程
String procedureName = "bianmin.edu_survey";
boolean callFlag = eduSurveyDetailLogDao.callProcedure(procedureName);
if(callFlag){
jsonObj.put("status", 1);
jsonObj.put("msg", "该调查结果文件【"+ fileName +"】数据导入成功!");
logger.info("文件【"+fileName+"】数据导入完毕!");
}else{
jsonObj.put("status", -1);
jsonObj.put("msg", "存储过程调用失败!");
}
}else{
jsonObj.put("status", -1);
jsonObj.put("msg", "该调查结果文件【"+ fileName +"】数据保存edu_survey_detail_log表失败!");
}
}finally{
//3.清理edu_survey_detail_log表中所有数据
eduSurveyDetailLogDao.clearLogData();
}
return jsonObj.toString();
}
当时解析excel我们使用的组件是poi,而非jxl。因为poi支持更高版本的excel 处理。实现excel导入功能过程还比较顺利,只是在读取excel时出现了点问题。简述如下: