上次项目开发过程中需要实现通过excel文件进行导入。我才用了WorkbookFactory方法，因为导入的数据需要处理，所以在程序中我使用List对读入的数据进行了分类。所以大家对数据处理可以不用关心，把重点放在WorkbookFactory的使用方法上。今天把这个方法分享给大家，话不多说，直接上代码。
java端代码
public String importFile(HttpServletRequest request,HttpServletResponse response){
Map<String, Object> returnMap = new HashMap<String, Object>();
//需要处理的数据
List<Map<String,String>> disposeList = new ArrayList<Map<String,String>>();
//不需要处理的数据
List<Map<String,String>> noDisposeList = new ArrayList<Map<String,String>>();
boolean errorFlag = false;
try {
//SpringMVC 中处理表单上传的方法
MultipartResolver resolver=new CommonsMultipartResolver(request
.getSession().getServletContext());
MultipartHttpServletRequest multipartRequest = resolver
.resolveMultipart(request);
Iterator<String> iter=multipartRequest.getFileNames();
MultipartFile mf = null;
while (iter.hasNext()) {
mf=multipartRequest.getFile(iter.next().toString());
}
org.apache.poi.ss.usermodel.Workbook wb = WorkbookFactory.create(mf.getInputStream());
// 创建临时目录，存放此次上传的文件，此处可以直接给一个默认路径
String tempFolder = getTempFolder("wtgl");
File file = new File(tempFolder);
file.mkdir();
mf.transferTo(new File(tempFolder+File.separator+mf.getOriginalFilename()));
// 生成临时文件
//tbDataTaskServiceImpl.scSjData(tempFolder, mf.getOriginalFilename());
Sheet sheet = wb.getSheetAt(0);
Row row = null;
/**这里是关键点，对excel以行为单位取出每一个单元格中的值。补充一点，
*如果excel表中的单元格被单击过，但是没有值会取出null,但是如果单元格没有
*值也没有被点击过此时或报错。
*/
for (int i=sheet.getFirstRowNum()+1; i<sheet.getLastRowNum()+1; i++) {
Map<String, String> disposeMap = new LinkedHashMap<String, String>();
Map<String, String> noDisposeMap = new LinkedHashMap<String, String>();
row= sheet.getRow(i);
if(row!=null){
Cell col1 = row.getCell(0);
Cell col2 = row.getCell(1);
Cell col3 = row.getCell(2);
Cell col4 = row.getCell(3);
Cell col5 = row.getCell(4);
Cell col6 = row.getCell(5);
Cell col7 = row.getCell(6);
Cell col8 = row.getCell(7);
Cell col9 = row.getCell(8);
noDisposeMap.put("WTBH", col1.toString());
noDisposeMap.put("WTZT", col2.toString());
//noDisposeMap.put("ZPSJ_DATA", col6.toString());
noDisposeMap.put("CLSJ_DATA", col8.toString());
noDisposeMap.put("CLRMC", col9.toString());
disposeMap.put("SBDS", col3.toString());
disposeMap.put("YJCD", col4.toString());
disposeMap.put("LCHJ", col5.toString());
disposeMap.put("CLJG", col7.toString());
}
noDisposeList.add(noDisposeMap);
disposeList.add(disposeMap);
}
Boolean result = commonService.importWtIntoSql(disposeList, noDisposeList);
if(result){
returnMap.put("result", true);
returnMap.put("message", "123456");
}else{
returnMap.put("result", false);
}
} catch (Exception e) {
errorFlag = true;
e.printStackTrace();
returnMap.put("result", false);
returnMap.put("message", "发生未知错误，上传失败。");
} finally {
String tempFolder = CommonUtils.getTempFolder("wtgl");
CommonUtils.deletefile(tempFolder);
}
if (errorFlag) {
// 解决平台service里报错导致错误信息无法返回到页面的问题
response.setContentType("text/xml;charset=UTF-8");
try {
response.getWriter().print(
JSONObject.fromObject(returnMap).toString());
} catch (IOException e1) {
e1.printStackTrace();
}
} else {
}
return JsonBinder.getJsonBinder().toJson(returnMap.get("result").toString());
}
}
/**
* 获取临时文件夹
*
* @return
*/
public static String getTempFolder(String filePath) {
return Platform.getPlatform().getBaseDir() + filePath
+ File.separator;
}
前端JS代码
function importFunction(){
importFile({
url: 'common/contCommon/importFile.do',
multiple: false,
beforeSend: function(file){
var h = document.body.scrollHeight + "px";
//实现弹窗选择文件页面
$("<div class=\"datagrid-mask\"></div>")
.css({display:"block",'background-color':'#000000',
width:"100%",height:h,
'z-index':100000,
opacity:0.2})
.appendTo("body");
$("<div class=\"datagrid-mask-msg\"><img src=\"zbgkWeb/icons/loading2.gif\">" +
"<center><div>正在导入文件，请稍后……</div></center></div>").appendTo("body")
.css({display:"block",
'z-index':100000,
'position': 'fixed',
'background-color': '#FFFFFF',
left:($(document.body).outerWidth(true) - 190) / 2,
top:(document.body.clientHeight - 35) / 2,
padding: "25px 20px 52px 10px",
height: "25px"});
forbiddenBodyRightClick();
},
callback: function(res){
res = JSON.parse(res);
if(res.result){
$('.datagrid-mask-msg').remove();
$('.datagrid-mask').remove();
artDialog({content:'导入成功!',time:2.0});
queryGridData(param);
}else{
$('.datagrid-mask-msg').remove();
$('.datagrid-mask').remove();
artDialog({content:'导入失败!',time:2.0});
}
},
uploading: function(pre){
}
});
}
此处只是粘贴了主要代码，如果有什么不理解的可以留言，看到后会回复的。
