欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

Net.Core导入EXCel文件里的数据

程序员文章站 2022-07-23 08:41:16
1、前台的表单:

1、前台的表单:

<form enctype="multipart/form-data" method="post" id="inportfile" name="inportfile"><input type="file" name="excelfile"  id="excelfile"/></form>

2、数据打包

var postdata = new formdata($("#inportfile")[0]);

3、ajax上传 (使用:$.postuploadajax()) 

封装后的代码

postuploadajax: function (url, postdata, callbacksuccessfunc, callbackerrorfunc) {
$.ajax({
url: url + "&r=" + math.random(),
data: postdata,
type: "post",
datatype: "json",
processdata: false,
contenttype: false,
success: function (jsondata) {
if (typeof callbacksuccessfunc === "function") {
callbacksuccessfunc(jsondata);
}
},
error: function (e) {
if (typeof callbackerrorfunc === "function") {
callbackerrorfunc(e);
}
}
});
},

 

 

5、c#代码 【导入部分】(引用using npoi.core.hssf.usermodel;)

    [httppost]
        public actionresult importpost(iformfile excelfile)
        {
            var result = new ajaxresult();
            result.state = ajaxstate.success;
            result.message = "导入成功!";
            try
            {
                var errorlist = new list<string>();
             string path = appdomain.currentdomain.basedirectory + "\\temp";
                if (!directory.exists(path))
                {
                    directory.createdirectory(path);
                }
                
            string filename = $"{guid.newguid()}.xlsx";
            var fullpath = path + "\\" + filename;
            var businessid = webhelper.getqueryint("businessid");

                selectrule selectrule = new selectrule("v_app_business_model_detail", "is_hide", false);
                selectrule.addcolumnwhere("disabled", false);
                selectrule.addcolumnwhere("business_id", businessid);
                selectrule.orderby = "order by order_by asc ";
                list<app_business_model_detailmodel> list = _service.gsf.getdatatable(selectrule).tolistmodel<app_business_model_detailmodel>();


                fileinfo file = new fileinfo(path.combine(path, filename));
            using (filestream fss = new filestream(file.tostring(), filemode.create))
            {
                excelfile.copyto(fss);
                fss.flush();
            }
            iworkbook wk = null;
           string extension = system.io.path.getextension(fullpath);
      
                filestream fs = new filestream(fullpath, filemode.open);
                if (extension.equals(".xls"))
                {
                    //把xls文件中的数据写入wk中
                    wk = new hssfworkbook(fs);
                }
                else
                {
                    //把xlsx文件中的数据写入wk中
                    wk = new xssfworkbook(fs);
                }



                fs.close();
                //读取当前表数据
                isheet sheet = wk.getsheetat(0);
                irow row = sheet.getrow(0);  //读取当前行数据
                string strjsonarrary = "";
                for (int i = 1; i <= sheet.lastrownum; i++)
                {
                    row = sheet.getrow(i);  //读取当前行数据
                    if (row != null)
                    {
                        string  strjson="";
                        //lastcellnum 是当前行的总列数
                        for (int j = 0; j < row.lastcellnum; j++)
                        {
                            //读取该行的第j列数据
                            string key = sheet.getrow(0).getcell(j).tostring();
                            var model = list.find(x => x.column_name == key);
                            string value = row.getcell(j).tostring();
                            var keyvalue = getjson(model, value);
                            if (keyvalue != "")
                            {
                                strjson += keyvalue;
                            }
  
                        }
                        if (strjson != "")
                        {
                            strjson ="{"+strjson.substring(0, strjson.length - 1)+"},";
                            strjsonarrary += strjson;
                        }
                    }
                }
                strjsonarrary = "["+strjsonarrary.substring(0, strjsonarrary.length - 1) + "]";
                result.data = jarray.parse(strjsonarrary);
            }
            catch (exception e)
            {
                result.state = ajaxstate.error;
                result.message = e.message;
            }

            return json(result);
        }

  

6、导出部分(excel)

 

        public actionresult downtemplet(int business_id,string  tablename)
        {
            selectrule selectrule = new selectrule("v_app_business_model_detail", "is_hide", false);
            selectrule.addcolumnwhere("disabled", false);
            selectrule.addcolumnwhere("business_id", business_id);
            selectrule.orderby = "order by order_by asc ";
            list<app_business_model_detailmodel> list = _service.gsf.getdatatable(selectrule).tolistmodel<app_business_model_detailmodel>();

            iworkbook workbook = new xssfworkbook();
            isheet sheet = workbook.createsheet(tablename);
            irow headrow = sheet.createrow(0);
            headrow.height = 500;
            for (int i = 0; i < list.count; i++)
            {
                icell cell = headrow.createcell(i);
                cell.setcellvalue(gettitle(list[i].column_name));
            }
            var stream = new npoimemorystream();
            workbook.write(stream);
            stream.flush();
            stream.position = 0;
            return file(stream, "application/ms-excel", string.format("{0}.xlsx", tablename+"_模板"));
        }