Excel导入功能(Ajaxfileupload)
前言:
前端采用Easyui+Ajaxfileupload实现
后端采用springmvc框架,其中把解析xml封装成了一个jar包,直接调用即可
准备:
前端需要导入(easyui导入js省略,自行导入即可) Ajaxfileupload.js 下载地址:https://files.cnblogs.com/files/holdon521/ajaxfileupload.zip
后端需要导入:
ht-excel.jar 下载地址:https://files.cnblogs.com/files/holdon521/ht-excel.zip
前端代码:
<div class="easyui-layout" fit="true" style="overflow-y:auto;"> <div class="ht-info"> <div class="ht-tip icon-tip"></div> <div>电视台类型导入</div> </div> <div> <form id="uploadform" action="" method="post" enctype="multipart/form-data"> <table> <tr> <td>请选择文件:</td> <td> <input type="file" name="file" id="file" ></input> </td> </tr> </table> </form> </div> <div region="south" border="false" style="text-align:right;height:30px;line-height:30px;background:#EAF2FF;"> <a href="#" onclick="ajaxFileUploadForType()"class="easyui-linkbutton" icon="icon-ok" >提交</a> <a class="easyui-linkbutton" icon="icon-cancel" href="javascript:$ht.win.close('uploadTvType')">取消</a> </div> </div> <script type="text/javascript"> function ajaxFileUploadForType(){ if($('input[type="file"]').val()!=""){ var extend=$('input[type="file"]').val().substr($('input[type="file"]').val().lastIndexOf(".")+1); if("xls|xlsx".indexOf(extend+"|")==-1){ flagPic=false; $.messager.alert("提示信息","选择的文件必须是EXCEL文件,请确认!"); }else{ $.ajaxFileUpload ( { url: 'uploadTvType', //用于文件上传的服务器端请求地址 secureuri: false, //是否需要安全协议,一般设置为false fileElementId: 'file', //文件上传域的ID dataType: 'text', //返回值类型 一般设置为json success: function (responseJSON) //服务器成功响应处理函数 { if(responseJSON == "0"){ $.messager.alert('提示信息','电视台类型导入成功!'); $ht.win.close('uploadTvType'); $ht.grid.reload('gridTvType'); }else if(responseJSON == "2"){ $.messager.alert('提示信息','没有符合要求的数据或要导入的数据,在数据库中已经存在,请确认!'); }else if(responseJSON == "error"){ $.messager.alert('提示信息','电视台类型导入失败!'); }else { $.messager.alert('提示信息',responseJSON); $ht.grid.reload('gridTvType'); } }, error: function (data, status, e)//服务器响应失败处理函数 { $.messager.alert('提示信息','电视台类型导入失败!'); } } ) } }else{ $.messager.alert("提示信息","请选EXCEL文件!"); } } </script>
后端代码:
注:实体类对应Excel里列名称,可以设置非空校验(如果为空,这默认该行数据获取不到),重写父方法即可,以下代码注释重写了
import java.util.List;
import com.sh.excelUtil.model.BaseModel;
import com.sh.excelUtil.persistence.FieldNote;
public class TvTypeBean extends BaseModel implements java.io.Serializable{ private static final long serialVersionUID = -5217032731630006972L; @FieldNote(logicalName = "终端类型",physicalName = "terminalType",notNull = true) private String terminalType; @FieldNote(logicalName = "分类编码",physicalName = "tvTypeCode",notNull = true) private String tvTypeCode; @FieldNote(logicalName = "分类名称",physicalName = "tvTypeName",notNull = true) private String tvTypeName; @FieldNote(logicalName = "分类缩写名称",physicalName = "tvTypeAbridgeName",notNull = true) private String tvTypeAbridgeName; @FieldNote(logicalName = "排序字段",physicalName = "orderBy") private String orderBy; @Override public List<Object> check(BaseModel model, List<Object> valueList){ // valueList = super.check(model,valueList); //TODO:实现非空时的数据检验 return valueList; } /** * @return the terminalType */ public String getTerminalType() { return terminalType; } /** * @param terminalType the terminalType to set */ public void setTerminalType(String terminalType) { this.terminalType = terminalType; } /** * @return the tvTypeCode */ public String getTvTypeCode() { return tvTypeCode; } /** * @param tvTypeCode the tvTypeCode to set */ public void setTvTypeCode(String tvTypeCode) { this.tvTypeCode = tvTypeCode; } /** * @return the tvTypeName */ public String getTvTypeName() { return tvTypeName; } /** * @param tvTypeName the tvTypeName to set */ public void setTvTypeName(String tvTypeName) { this.tvTypeName = tvTypeName; } /** * @return the tvTypeAbridgeName */ public String getTvTypeAbridgeName() { return tvTypeAbridgeName; } /** * @param tvTypeAbridgeName the tvTypeAbridgeName to set */ public void setTvTypeAbridgeName(String tvTypeAbridgeName) { this.tvTypeAbridgeName = tvTypeAbridgeName; } /** * @return the orderBy */ public String getOrderBy() { return orderBy; } /** * @param orderBy the orderBy to set */ public void setOrderBy(String orderBy) { this.orderBy = orderBy; } }
import java.util.List;
import com.sh.excelUtil.excel.POIExcelUtil;
@Controller
@RequestMapping("/channelinfomanage")
public class ChannelInfoManageController{
@RequestMapping("/uploadTvType") public void uploadTvType(HttpServletRequest request, @RequestParam("file") MultipartFile file, HttpServletResponse response) throws Exception { //该对象为jar中工具类 POIExcelUtil poi = new POIExcelUtil(); poi.impExcelFile(fileName, new TvTypeBean(),inputStream); List<Object> valueList = poi.getValueList();
if(valueList!=null && valueList.size()>0){
for(int i =0;i<valueList.size();i++){
TvTypeBean bean = (TvTypeBean)valueList.get(i);
//输出字段数据,并插入到相应的表中,省略
System.out.println(bean.getTvTypeCode()+。。。。。。。);
}
}
}
}
}