Excle的导入功能

.net后台的Excle的导入功能

这个是使用.net的后台开发的表格导入功能，前端框架使用的是layui的框架；

前端代码

<div class="layui-tab-item"><form class="layui-form" action=""><div class="layui-form-item" style="margin-left:-20px;"><div class="layui-row"><div class="layui-col-xs10"><div class="grid-demo grid-demo-bg1"><label class="layui-form-label">行政县名称</label><div class="layui-input-inline"><select name="Hd_xzxianName" id="Hd_xzxianName" lay-filter="Hd_xzxianName" lay-search></select></div></div></div><!--<div class="layui-col-xs4"><div class="grid-demo grid-demo-bg1"><label class="layui-form-label">选择河段</label><div class="layui-input-inline"><select style="position:relative;z-index: 999;" name="xzHeDuan" id="xzHeDuan" lay-filter="xzHeDuan" lay-search></select></div></div></div><div class="layui-col-xs4"><div class="grid-demo"><label class="layui-form-label">选择水面线</label><div class="layui-input-inline"><select style="position:relative;z-index: 999;" name="waterline" id="waterline" lay-filter="waterline" lay-search></select></div></div></div>--></div></div><div class="layui-form-item"><div class="layui-col-xs8"><div class="grid-demo grid-demo-bg1"><div class="layui-upload"><button type="button" class="layui-btn layui-btn-normal" id="Luploadfile">批量导入</button><button type="button" class="layui-btn" id="gfsquploadfile">开始上传</button></div></div></div><div class="layui-col-xs4"><div class="grid-demo"><div class="tab-change"><button type="button" class="layui-btn layui-btn-normal change-tab tab-click" id="zxss" onclick="_type('实时','中小河流')">实时</button><button type="button" class="layui-btn layui-btn-normal change-tab" id="zxyc" onclick="_type('预测', '中小河流')">预测</button></div></div></div></div></form><div class="layui-form-item"><div class="layui-input-inline" style="margin-right:0;padding:4px;margin: 0px;width:100%;"><label id="hdgxsj" style="left:10px">更新时间：2019-5-14</label><label id="zxhlj" style="right:15px;position:absolute;color:red;">需要转移：0个</label></div></div><table class="layui-hide" id="customerTable_hd" lay-filter="customerTable_hd"></table><script type="text/html" id="toolbarDemo_hd"><div class="layui-btn-container"><label style="font-size:14px">中小河流列表</label><div lay-event="add" style='position: absolute;margin-top: 3px;right: 76px;' class="layui-inline" title="添加"><i class="layui-icon layui-icon-add-1"></i> <!--layui-icon layui-icon-addition--></div><div lay-event="delete" style='position: absolute;margin-top: 3px;right: 110px;' class="layui-inline" title="删除"><i class="layui-icon layui-icon-delete"></i></div><!--<div lay-event="edit" style='position: absolute;margin-top: 3px;right: 144px;' class="layui-inline" title="编辑"><i class="layui-icon layui-icon-edit"></i></div>--><!--<label style="font-size:14px;color:blue;position: absolute;right: 30px;" onclick="sk_detail(2)">详情</label>--></div></script></div>

使用的layui的表格控件。

js

 layui.use(['element', 'table', 'layer', 'form'], function () {var table = layui.table;var layer = layui.layer;var form = layui.form;var tableName = "user";$('.layer-customer-btn').on('click', function () {var othis = $(this), type = othis.data('method');active[type] ? active[type].call(this, othis) : '';});table.render({elem: '#customerTable_hd',//url: '/handler/shuili.ashx',data: _data,toolbar: '#toolbarDemo_hd',defaultToolbar: ['filter', 'exports', {title: '批量删除', layEvent: 'delete', icon:'layui-icon layui-icon-delete'}],//method: 'POST',loading: true,limit: 8,limits: [8, 16, 24, 36],//loading: false,//where: {//    flag: "yjzxhl",//    tableName: "ZTSJ_weixiandian",//    //whereValue: escape($("#key").val()),//    lieName: lieName,//},parseData: function (res) { //res 即为原始返回的数据//parent.clearQueryResults();var result = {"code": 0, //解析接口状态"msg": '' //解析提示文本};if (res) {// result.code = 0;// result.msg = '';result.count = res.count;var resultData = [];if (res.data && res.data && res.data.length >0) {time3 = AllresultData[0].tm;dataObj.data = res.data;var _features = res.data;for (var i = 0; i < _features.length; i++) {//if (Number(_features[0].房屋高程_) <= Number(AllresultData[k].z) && AllresultData[k].name == _features[i].关联水文站) {//}for (var j = 0; j < AllresultData.length; j++) {if (AllresultData[j].name.indexOf(_features[i].关联水文站) != -1) {_features[i].z = AllresultData[j].z;_features[i].code = AllresultData[j].code;}//if (_features[i].关联水文站 == AllresultData[j].name) {//}}resultData.push(_features[i]);}result.data = resultData;}}},cols: [[{ type: 'checkbox', fixed: 'left', unresize: true },{ width: 62, unresize: true, type: 'numbers' }, { field: '关联水文站', title: '水文站', width: 85, unresize: true }, { field: '区县', title: '区县', width: 85, unresize: true }, { field: '乡镇', title: '乡镇', width: 85, unresize: true }, { field: '行政村', title: '行政村', width: 90, unresize: false, sort: true, }, { field: '村小组', title: '村小组', width: 90, unresize: false, sort: true, }, { field: 'z', title: '河道水位', width: 100, unresize: true, templet: function (d) {return parseFloat(d["z"]).toFixed(3);} }//, { field: '差值', title: '差值', width: 80, unresize: true, }, {field: '房屋高程_', title: '承灾水位', width: 100, unresize: true, templet: function (d) {return parseFloat(d["房屋高程_"]).toFixed(2);}}, { fixed:'right', title: '操作',align:'center', width: 120, unresize: true, toolbar: '#operation' }]],page: true,done: function (res, curr, count) {// parent.ClearEntities("zhongxiaoheliu");$("#hdgxsj").text("更新时间:" + time3);var that = this.elem.next();var _hlyj = [];res.data.forEach(function (item, index) {if (Number(item.房屋高程_) <= Number(item.z) && Number(item.房屋高程_) != 0 && Number(item.z) != 0 && item.差值!="") {var tr = that.find(".layui-table-box tbody tr[data-index='" + index + "']").css("color", "red");}});$(".layui-table-cell").css("cursor", "pointer");//$(".layui-table-tool-self").css("display", "none");$('.layer-customer-btn-location').on('click', function () {var othis = $(this), method = othis.data('method');active[method] ? active[method].call(this, othis) : '';});}});//头工具栏事件table.on('toolbar(customerTable_hd)', function (obj) {console.log(obj, "obj")var checkStatus = table.checkStatus(obj.config.id);data = checkStatus.data; //获取选中的数据switch (obj.event) {case 'add':parent.layer.open({title: "添加中小河流的危险区",type: 2,zIndex: parent.layer.zIndex,area: ["48%", "92%"],fixed: false, //不固定maxmin: true,btn: ["提交", "取消"],content: "lib/Map/Html/FXYJ/addzxw.html",yes: function (index, layero) {var formSubmit = parent.layer.getChildFrame('form', index);var submited = formSubmit.find('button')[0];submited.click();var value = "";zxhl(value, diffvalue);}});break;case 'delete':{if (data.length == 0) {layer.msg('请选择一行');}else {layer.confirm('确定删除这' + data.length + '行数据吗？', function (index) {layer.close(index);var deleteid = "";for (var i = 0; i < data.length; i++) {deleteid += data[i].id;deleteid += ",";}$.ajax({url: '/handler/shuili.ashx',  //获得大江大河type: 'post',async: false,data: {flag: 'zxpldelete',deleteid: deleteid,},success: function (data) {var _data = JSON.parse(data);if (_data.code == 1) {layer.msg('操作成功', {icon: 1,//1 是√号time: 1500});// parent.$('.layui-laypage-btn').click();//相当于刷新表格var value = "";zxhl(value, diffvalue);} else if (_data.code == 0) {layer.msg('操作失败', {icon: 2,//2是x号time: 2000});} else {layer.msg('出错啦', {icon: 7,//7是！号time: 2000});}}})})}}break;case 'edit':{if (data.length == 0 ) {layer.msg('请选择一行');}else if (data.length > 1) {layer.msg('只能同时编辑一行');}else {parent.layer.open({title: "编辑信息",type: 2,area: ["48%", "92%"],fixed: false, //不固定maxmin: true,btn: ["确定", "取消"],content: "lib/Map/Html/FXYJ/editzxw.html",success: function (index) {var a = $(index[0]).find("iframe").eq(0).contents();a.find("#id").val(data[0].id);a.find("#river").val(data[0].河名);a.find("#qx").val(data[0].区县);a.find("#swz").val(data[0].关联水文站);a.find("#sump").val(data[0].总人口);a.find("#xz").val(data[0].乡镇);a.find("#wnrk").val(data[0]['5年一遇人口']);a.find("#xzc").val(data[0].行政村);a.find("#wnhs").val(data[0]['5年一遇户数']);a.find("#cunxz").val(data[0].村小组);a.find("#wngd").val(data[0]['5年一遇耕地']);a.find("#lat").val(data[0].东经);a.find("#enrk").val(data[0]['20年一遇人口']);a.find("#lng").val(data[0].北纬);a.find("#enhs").val(data[0]['20年一遇户数']);a.find("#fwheight").val(data[0].房屋高程_);a.find("#engd").val(data[0]['20年一遇耕地']);a.find("#smxheight").val(data[0].水面线高程);a.find("#wsnrk").val(data[0]['50年一遇人口'] );a.find("#chazhi").val(data[0].差值);a.find("#wsnhs").val(data[0]['50年一遇户数']);a.find("#szpl").val(data[0].受灾频率);a.find("#wsngd").val(data[0]['50年一遇耕地']);//var formSubmit = parent.layer.getChildFrame('form', index);//var submited = formSubmit.find('button')[0];//submited.click();},yes: function (index, layero) {var formSubmit = parent.layer.getChildFrame('form', index);var submited = formSubmit.find('button')[0];submited.click();var value = "";zxhl(value, diffvalue);}})}}break;};});//分两个按钮，能够编辑和查询详情table.on('tool(customerTable_hd)', function (obj) {if (obj.event == "edit") {var data = obj.data;parent.layer.open({title: "编辑信息",type: 2,zIndex: parent.layer.zIndex,area: ["48%", "92%"],fixed: false, //不固定maxmin: true,btn: ["确定", "取消"],content: "lib/Map/Html/FXYJ/editzxw.html",success: function (index) {var a = $(index[0]).find("iframe").eq(0).contents();a.find("#id").val(data.id);a.find("#river").val(data.河名);a.find("#qx").val(data.区县);a.find("#swz").val(data.关联水文站);a.find("#sump").val(data.总人口);a.find("#xz").val(data.乡镇);a.find("#wnrk").val(data['5年一遇人口']);a.find("#xzc").val(data.行政村);a.find("#wnhs").val(data['5年一遇户数']);a.find("#cunxz").val(data.村小组);a.find("#wngd").val(data['5年一遇耕地']);a.find("#lat").val(data.东经);a.find("#enrk").val(data['20年一遇人口']);a.find("#lng").val(data.北纬);a.find("#enhs").val(data['20年一遇户数']);a.find("#fwheight").val(data.房屋高程_);a.find("#engd").val(data['20年一遇耕地']);a.find("#smxheight").val(data.水面线高程);a.find("#wsnrk").val(data['50年一遇人口']);a.find("#chazhi").val(data.差值);a.find("#wsnhs").val(data['50年一遇户数']);a.find("#szpl").val(data.受灾频率);a.find("#wsngd").val(data['50年一遇耕地']);//var formSubmit = parent.layer.getChildFrame('form', index);//var submited = formSubmit.find('button')[0];//submited.click();},yes: function (index, layero) {var formSubmit = parent.layer.getChildFrame('form', index);var submited = formSubmit.find('button')[0];submited.click();var value = "";zxhl(value, diffvalue);}})else if (obj.event == "detail") {var data = obj.data;var _gid = obj.data.id;var _locationObj = {type: "FeatureCollection", features: []};var _features = dataObj1.data;_locationObj.features.push(obj.data);parent.locationObject.mapLocationpoint(_locationObj, "location_hd");//标注选中样式obj.tr.addClass('layui-table-click').siblings().removeClass('layui-table-click');}else {return;}})})

使用的假数据，进行的假分页；

对导入控件的监听

layui.use(['jquery', 'form', 'layer', 'laydate', 'upload'], function () {var $ = layui.$,layer = layui.layer,form = layui.form,laydate = layui.laydate,upload = layui.upload;upload.render({elem: '#Luploadfile',//url: 'https://httpbin.org/post', //改成您自己的上传接口url: "/handler/systemHandler.ashx",data: { flag: 'cyzy_upload', "_type": "hsymqdcpj" },auto: false,exts: 'xls',// multiple: true,bindAction: '#gfsquploadfile',done: function (res) {if (res.data != undefined && res.data.code == 1) {layer.msg('上传成功');$("#waterline").empty();// _waterline = "";//水面线select选中的值_diffline = [];//水面线的差值//zxhlline();//更新水面线的名称zxhl("");}else {layer.msg(res.data.msg);}},error: function (index, upload) {layer.alert("上传失败!!!");}});});

使用的是预上传的layui控件；

后台的一般处理程序

`在这里插入代码片 public string daoruexecle(string filepath, string type)
{
string str = “”;
int a = 0;
try
{
FileStream files = new FileStream(Path.GetFullPath(filepath), FileMode.Open);
HSSFWorkbook workBook = new HSSFWorkbook(files);
HSSFSheet sheet = (HSSFSheet)workBook.GetSheetAt(0);
//string liename = DateTime.Now.ToString(“yyyyMMdd”);
string time = DateTime.Now.ToString(“yyyy/MM/dd HH:mm:ss”);
//liename = liename + “水面线”;

            int rowCount = sheet.LastRowNum;if (type == "ylzdvxqy")///山洪灾害{for (int j = 0; j < rowCount; j++){//string _id = sheet.GetRow(j + 1).GetCell(0).ToString().Trim().Replace("\n", "");//序号string dyylz = sheet.GetRow(j + 1).GetCell(0).ToString().Trim().Replace("\n", "");//对应雨量站string dyylzm = sheet.GetRow(j + 1).GetCell(1).ToString().Trim().Replace("\n", "");//对应雨量站站码string bxylz = sheet.GetRow(j + 1).GetCell(2).ToString().Trim().Replace("\n", "");//备选雨量站string bxylzm = sheet.GetRow(j + 1).GetCell(3).ToString().Trim().Replace("\n", "");//备选雨量站站码string xzx = sheet.GetRow(j + 1).GetCell(4).ToString().Trim().Replace("\n", "");//行政县string xz = sheet.GetRow(j + 1).GetCell(5).ToString().Trim().Replace("\n", "");//乡镇string xzc = sheet.GetRow(j + 1).GetCell(6).ToString().Trim().Replace("\n", "");//行政村string cunxz = sheet.GetRow(j + 1).GetCell(7).ToString().Trim().Replace("\n", "");//村小组string lat = sheet.GetRow(j + 1).GetCell(8).ToString().Trim().Replace("\n", "");//东经string lng = sheet.GetRow(j + 1).GetCell(9).ToString().Trim().Replace("\n", "");//北纬string zby = sheet.GetRow(j + 1).GetCell(10).ToString().Trim().Replace("\n", "");//准备1小时string zbs = sheet.GetRow(j + 1).GetCell(11).ToString().Trim().Replace("\n", "");//准备3小时string zbl = sheet.GetRow(j + 1).GetCell(12).ToString().Trim().Replace("\n", "");//准备6小时string ljy = sheet.GetRow(j + 1).GetCell(13).ToString().Trim().Replace("\n", "");//立即1小时string ljs = sheet.GetRow(j + 1).GetCell(14).ToString().Trim().Replace("\n", "");//立即3小时string ljl = sheet.GetRow(j + 1).GetCell(15).ToString().Trim().Replace("\n", "");//立即6小时string wnyy = sheet.GetRow(j + 1).GetCell(16).ToString().Trim().Replace("\n", "");//5年一遇string esnyy = sheet.GetRow(j + 1).GetCell(17).ToString().Trim().Replace("\n", "");//20年一遇string ybnyy = sheet.GetRow(j + 1).GetCell(18).ToString().Trim().Replace("\n", "");//100年一遇if (dyylz != "" && dyylzm != "" && bxylz != "" && bxylzm != "" && xzx != "" && xz != "" && xzc != "" && cunxz != "" && lat != "" && lng != ""){string sql1 = "select * from ylzdvxqy where 行政县='{0}'and 乡镇='{1}'and 行政村='{2}'and 村小组='{3}' and 站码={4} ";sql1 = string.Format(sql1, xzx, xz, xzc, cunxz, dyylzm);var res = dbHelper.GetDataTable(sql1);string sqls = "";if (res.Rows.Count < 0 || res.Rows.Count == 0)///判断是否有重复的行，如果有的话不添加只进行修改{string sql2 = "select max(id) from ylzdvxqy";var lastid = dbHelper.GetDataTable(sql2);int newid = Convert.ToInt32(lastid.Rows[0][0]) + 1;sqls = "INSERT into ylzdvxqy([对应雨量站],[站码],备选雨量站,[站码1],[乡镇],[行政村],[东经],[北纬],[村小组],[准备1小时],[准备3小时],[准备6小时],[立即1小时],[立即3小时],[立即6小时],[5年一遇],[20年一遇],[100年一遇],[行政县],[是否转移],[是否转移1],[雨量1小时],[雨量3小时],[雨量6小时],[累计降雨量],id,isdelect)values('{0}','{1}','{2}',{3},'{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}','{13}','{14}','{15}','{16}','{17}','{18}','暂不需转移','2','0','0','0','暂无数据',{19},1)";sqls = string.Format(sqls, dyylz, dyylzm, bxylz, bxylzm, xz, xzc, lat, lng, cunxz, zby, zbs, zbl, ljy, ljs, ljl, wnyy, esnyy, ybnyy, xzx, newid);}else{int id = Convert.ToInt32(res.Rows[0]["id"]);sqls = "update ylzdvxqy set [对应雨量站]='{0}',[站码]={1},备选雨量站='{2}',[站码1]={3},[乡镇]='{4}',[行政村]='{5}',[东经]='{6}',[北纬]='{7}',[村小组]='{8}',[准备1小时]='{9}',[准备3小时]='{10}',[准备6小时]='{11}',[立即1小时]='{12}',[立即3小时]='{13}',[立即6小时]='{14}',[5年一遇]='{15}',[20年一遇]='{16}',[100年一遇]='{17}',[行政县]='{18}' where id={19}";sqls = string.Format(sqls, dyylz, dyylzm, bxylz, bxylzm, xz, xzc, lat, lng, cunxz, zby, zbs, zbl, ljy, ljs, ljl, wnyy, esnyy, ybnyy, xzx, id);}if (!string.IsNullOrEmpty(sqls)){a++;int num1 = dbHelper.ExecuteNonQuery(CommandType.Text, sqls);}str = "success";}else{str = "error";}if (str != "error"){str = "success";}//List parameters = new List() {//    new SqlParameter("fff",)//};}}}catch (Exception e){var s = e;str = "error";//return e;}return str;}

本文来自互联网用户投稿，文章观点仅代表作者本人，不代表本站立场，不承担相关法律责任。如若转载，请注明出处。 如若内容造成侵权/违法违规/事实不符，请点击【内容举报】进行投诉反馈！