先说导入
需求:在页面上选取特定格式的excel,后台将其导入到数据库中
前台代码:
<form id="importForm1" action="ProductImport!doImportProductExcel1.action" method="post" enctype="multipart/form-data" target="uploadTarget">
<s:file label="选择模板一" name="importProExcel1"></s:file><span style="color:red;font-size=8px;">*(文件仅限于excel文档)</span>
<a href="#" class="easyui-linkbutton" id="importSbt1" style="text-decoration:none;">导入</a>
</form>
注:定义一个表单,注意form中的method、enctype属性,表单中定义一个file标签用来 选择excel文件,下面是一个提交表单的按钮
<div style="background:#ffffbb;border:1px solid #0099cc;padding:5px 5px 5px 5px;margin-top:5px;">
已导入附件:<span id="import-show1" style="color:#0099cc;text-decoration:underline;"></span>
</div>
注:定义一个div,用来显示已经导入的文件
<iframe id="uploadTarget" name="uploadTarget" style='display:none;'></iframe>
注:定义一个影藏的iframe用于存放表单提交后的页面,注意上面form标签中的target属性,就是对应这个iframe
$j('#importSbt1').click(function(){
var file = $j('input[name=importProExcel1]').val();
if(file.lastIndexOf('.xls')==-1){
$j.messager.alert('提示框','附件格式不符,请上传excel文档!','error');
return false;
}
$j('#importForm1').submit();
});
注:提交前验证上传文档的格式,用js提交表单
后台代码:
// 导入产品信息 模板1
public String doImportProductExcel1(){
this.excelDataList = new ArrayList();
InputStream is = null;
try {
is = new FileInputStream(importProExcel1);
parseExcel1(is);
importData1();
} catch (FileNotFoundException e) {
e.printStackTrace();
this.importFlag = "-1";
return "importResult";
} catch (IOException e) {
e.printStackTrace();
this.importFlag = "-1";
return "importResult";
}
if(this.opResult != null && this.opResult.isSuccess()){
this.importFlag = "1";
}else {
this.importFlag = "-1";
}
return "importResult";
}
注:后台action主要有parseExcel1和importData1两个方法,分别用于excel的解析和数据的导入到数据库,然后返回到importResult页面
public void parseExcel1(InputStream is) throws IOException{
if(is == null){
return ;
}
XSSFWorkbook xwb = new XSSFWorkbook(is);
//System.out.println(xwb.getNumberOfSheets());
for(int sheetNum=0;sheetNum<xwb.getNumberOfSheets();sheetNum++){
Map sheetMap = new HashMap();
Map<String,String> compMap = new HashMap<String, String>();
List prodList = new ArrayList();
XSSFSheet xs = xwb.getSheetAt(sheetNum);
// 读取单位信息
XSSFRow xrComp = xs.getRow(1);
for(int cellNum=0;cellNum<xrComp.getLastCellNum();cellNum++){
XSSFCell xc = xrComp.getCell(cellNum);
String xcValue = "";
if(xc != null){
xcValue = xc.getStringCellValue();
}
switch(cellNum){
case 0:compMap.put("companyName", xcValue);break;
case 1:compMap.put("companyAddress", xcValue);break;
}
}
sheetMap.put("compMap", compMap);
System.out.println(compMap);
//compMap.put("id", XUtils.getId());
//template.insert("pkcc.imports.productimport.insertBuyCompanyinfo",compMap);
//template.insert("pkcc.imports.productimport.insertBuyCompanyContact",compMap);
// 读取产品信息
for(int rowNum=3;rowNum<=xs.getLastRowNum();rowNum++){
XSSFRow xrProd = xs.getRow(rowNum);
Map<String,String> prodMap = new HashMap<String,String>();
for(int cellNum=0;cellNum<xrProd.getLastCellNum();cellNum++){
XSSFCell xc = xrProd.getCell(cellNum);
String xcValue = "";
if(xc != null){
xcValue = xc.getStringCellValue();
}
switch(cellNum){
case 0:prodMap.put("moduleNumber", xcValue);break;
case 1:prodMap.put("serialNumber", xcValue);break;
case 2:prodMap.put("simNumber", xcValue);break;
}
}
prodList.add(prodMap);
}
sheetMap.put("prodList", prodList);
this.excelDataList.add(sheetMap);
//System.out.println(prodList);
}
}
public void importData1(){
this.opResult = daoHelper.execute(new DaoCallback() {
public Object exec(IBatisTemplate template) {
if(excelDataList!=null&&excelDataList.size()>0){
for(int i=0;i<excelDataList.size();i++){
String compId = XUtils.getId();
Map sheetMap = (Map)excelDataList.get(i);
Map<String,String> compMap = (Map<String,String>)sheetMap.get("compMap");
compMap.put("id",compId);
template.insert("pkcc.imports.productimport.insertBuyCompanyinfo",compMap);
List<Map<String,String>> prodList = (List<Map<String,String>>)sheetMap.get("prodList");
for(int j=0;j<prodList.size();j++){
Map<String,String> prodMap = prodList.get(j);
// 插入到分配表 产品表 服务表
String productId = XUtils.getId();
prodMap.put("id", productId);
prodMap.put("state", "0");
template.insert("pkcc.product.product.insertProduct",prodMap);
}
}
}
return null;
}
});
}
返回页面importResult:
$j(function(){
// 判断excel是否导入成功
if('${importFlag}' == '1'){
window.parent.$j.messager.alert('提示框','导入成功!','info');
window.parent.$j('#import-show1').html(window.parent.$j('#import-show1').html()+'${importProExcel1FileName}'+',');
} else if('${importFlag}' == '2') {
window.parent.$j.messager.alert('提示框','导入成功!','info');
window.parent.$j('#import-show2').html(window.parent.$j('#import-show2').html()+'${importProExcel2FileName}'+',');
} else {
window.parent.$j.messager.alert('提示框','导入失败!','error');
}
});
判断导入是否成功,并用弹出框提示结果