建议收藏:.net core 使用EPPlus导入导出Excel详细案例,精心整理源码已更新至开源模板
还记得刚曾经因为导入导出不会做而发愁的自己吗?我见过自己前同事因为一个导出改了好几天,然后我们发现虽然有开源的库但是用起来却不得心应手,主要是因为百度使用方案的时候很多方案并不能解决问题。
尤其是尝试新技术那些旧的操作还会有所改变,为了节约开发时间,我们把解决方案收入到一个个demo中,方便以后即拿即用。而且这些demo有博客文档支持,帮助任何人非常容易上手开发跨平台的.net core。随着时间的推移,我们的demo库会日益强大请及时收藏github。
一、首先在common公用项目中引用epplus.core类库和json序列化的类库及读取配置文件的类库
install-package epplus.core -version 1.5.4 install-package newtonsoft.json -version 12.0.3-beta2 install-package microsoft.extensions.configuration.json -version 3.0.0
二、在common公用项目中添加相关操作类officehelper和commonhelper及confighelper
1.officehelper中excel的操作方法
#region excel #region epplus导出excel /// <summary> /// datatable导出excel /// </summary> /// <param name="dt">数据源</param> /// <param name="swebrootfolder">webroot文件夹</param> /// <param name="sfilename">文件名</param> /// <param name="scolumnname">自定义列名(不传默认dt列名)</param> /// <param name="msg">失败返回错误信息,有数据返回路径</param> /// <returns></returns> public static bool dtexportepplusexcel(datatable dt, string swebrootfolder, string sfilename, string[] scolumnname, ref string msg) { try { if (dt == null || dt.rows.count == 0) { msg = "数据为空"; return false; } //转utf-8 utf8encoding utf8 = new utf8encoding(); byte[] buffer = utf8.getbytes(sfilename); sfilename = utf8.getstring(buffer); //判断文件夹,不存在创建 if (!directory.exists(swebrootfolder)) directory.createdirectory(swebrootfolder); //删除大于30天的文件,为了保证文件夹不会有过多文件 string[] files = directory.getfiles(swebrootfolder, "*.xlsx", searchoption.alldirectories); foreach (string item in files) { fileinfo f = new fileinfo(item); datetime now = datetime.now; timespan t = now - f.creationtime; int day = t.days; if (day > 30) { file.delete(item); } } //判断同名文件 fileinfo file = new fileinfo(path.combine(swebrootfolder, sfilename)); if (file.exists) { //判断同名文件创建时间 file.delete(); file = new fileinfo(path.combine(swebrootfolder, sfilename)); } using (excelpackage package = new excelpackage(file)) { //添加worksheet excelworksheet worksheet = package.workbook.worksheets.add(sfilename.split('.')[0]); //添加表头 int column = 1; if (scolumnname.count() == dt.columns.count) { foreach (string cn in scolumnname) { worksheet.cells[1, column].value = cn.trim();//可以只保留这个,不加央视,导出速度也会加快 worksheet.cells[1, column].style.font.bold = true;//字体为粗体 worksheet.cells[1, column].style.horizontalalignment = officeopenxml.style.excelhorizontalalignment.center;//水平居中 worksheet.cells[1, column].style.fill.patterntype = officeopenxml.style.excelfillstyle.solid;//设置样式类型 worksheet.cells[1, column].style.fill.backgroundcolor.setcolor(system.drawing.color.fromargb(159, 197, 232));//设置单元格背景色 column++; } } else { foreach (datacolumn dc in dt.columns) { worksheet.cells[1, column].value = dc.columnname;//可以只保留这个,不加央视,导出速度也会加快 worksheet.cells[1, column].style.font.bold = true;//字体为粗体 worksheet.cells[1, column].style.horizontalalignment = officeopenxml.style.excelhorizontalalignment.center;//水平居中 worksheet.cells[1, column].style.fill.patterntype = officeopenxml.style.excelfillstyle.solid;//设置样式类型 worksheet.cells[1, column].style.fill.backgroundcolor.setcolor(system.drawing.color.fromargb(159, 197, 232));//设置单元格背景色 column++; } } //添加数据 int row = 2; foreach (datarow dr in dt.rows) { int col = 1; foreach (datacolumn dc in dt.columns) { worksheet.cells[row, col].value = dr[col - 1].tostring();//这里已知可以减少一层循环,速度会上升 col++; } row++; } //自动列宽,由于自动列宽大数据导出严重影响速度,我这里就不开启了,大家可以根据自己情况开启 //worksheet.cells.autofitcolumns(); //保存workbook. package.save(); } return true; } catch (exception ex) { msg = "生成excel失败:" + ex.message; commonhelper.writeerrorlog("生成excel失败:" + ex.message); return false; } } /// <summary> /// model导出excel /// </summary> /// <param name="list">数据源</param> /// <param name="swebrootfolder">webroot文件夹</param> /// <param name="sfilename">文件名</param> /// <param name="scolumnname">自定义列名</param> /// <param name="msg">失败返回错误信息,有数据返回路径</param> /// <returns></returns> public static bool modelexportepplusexcel<t>(list<t> mylist, string swebrootfolder, string sfilename, string[] scolumnname, ref string msg) { try { if (mylist == null || mylist.count == 0) { msg = "数据为空"; return false; } //转utf-8 utf8encoding utf8 = new utf8encoding(); byte[] buffer = utf8.getbytes(sfilename); sfilename = utf8.getstring(buffer); //判断文件夹,不存在创建 if (!directory.exists(swebrootfolder)) directory.createdirectory(swebrootfolder); //删除大于30天的文件,为了保证文件夹不会有过多文件 string[] files = directory.getfiles(swebrootfolder, "*.xlsx", searchoption.alldirectories); foreach (string item in files) { fileinfo f = new fileinfo(item); datetime now = datetime.now; timespan t = now - f.creationtime; int day = t.days; if (day > 30) { file.delete(item); } } //判断同名文件 fileinfo file = new fileinfo(path.combine(swebrootfolder, sfilename)); if (file.exists) { //判断同名文件创建时间 file.delete(); file = new fileinfo(path.combine(swebrootfolder, sfilename)); } using (excelpackage package = new excelpackage(file)) { //添加worksheet excelworksheet worksheet = package.workbook.worksheets.add(sfilename.split('.')[0]); //添加表头 int column = 1; if (scolumnname.count() > 0) { foreach (string cn in scolumnname) { worksheet.cells[1, column].value = cn.trim();//可以只保留这个,不加央视,导出速度也会加快 worksheet.cells[1, column].style.font.bold = true;//字体为粗体 worksheet.cells[1, column].style.horizontalalignment = officeopenxml.style.excelhorizontalalignment.center;//水平居中 worksheet.cells[1, column].style.fill.patterntype = officeopenxml.style.excelfillstyle.solid;//设置样式类型 worksheet.cells[1, column].style.fill.backgroundcolor.setcolor(system.drawing.color.fromargb(159, 197, 232));//设置单元格背景色 column++; } } //添加数据 int row = 2; foreach (t ob in mylist) { int col = 1; foreach (system.reflection.propertyinfo property in ob.gettype().getruntimeproperties()) { worksheet.cells[row, col].value = property.getvalue(ob);//这里已知可以减少一层循环,速度会上升 col++; } row++; } //自动列宽,由于自动列宽大数据导出严重影响速度,我这里就不开启了,大家可以根据自己情况开启 //worksheet.cells.autofitcolumns(); //保存workbook. package.save(); } return true; } catch (exception ex) { msg = "生成excel失败:" + ex.message; commonhelper.writeerrorlog("生成excel失败:" + ex.message); return false; } } #endregion #region eppluse导入 #region 转换为datatable public static datatable inputepplusbyexceltodt(fileinfo file) { datatable dt = new datatable(); if (file != null) { using (excelpackage package = new excelpackage(file)) { try { excelworksheet worksheet = package.workbook.worksheets[1]; dt = worksheettotable(worksheet); } catch (exception ex) { console.writeline(ex.message); } } } return dt; } /// <summary> /// 将worksheet转成datatable /// </summary> /// <param name="worksheet">待处理的worksheet</param> /// <returns>返回处理后的datatable</returns> public static datatable worksheettotable(excelworksheet worksheet) { //获取worksheet的行数 int rows = worksheet.dimension.end.row; //获取worksheet的列数 int cols = worksheet.dimension.end.column; datatable dt = new datatable(worksheet.name); datarow dr = null; for (int i = 1; i <= rows; i++) { if (i > 1) dr = dt.rows.add(); for (int j = 1; j <= cols; j++) { //默认将第一行设置为datatable的标题 if (i == 1) dt.columns.add(getstring(worksheet.cells[i, j].value)); //剩下的写入datatable else dr[j - 1] = getstring(worksheet.cells[i, j].value); } } return dt; } private static string getstring(object obj) { try { return obj.tostring(); } catch (exception) { return ""; } } #endregion #region 转换为ienumerable<t> /// <summary> /// 从excel中加载数据(泛型) /// </summary> /// <typeparam name="t">每行数据的类型</typeparam> /// <param name="filename">excel文件名</param> /// <returns>泛型列表</returns> public static ienumerable<t> loadfromexcel<t>(fileinfo existingfile) where t : new() { //fileinfo existingfile = new fileinfo(filename);//如果本地地址可以直接使用本方法,这里是直接拿到了文件 list<t> resultlist = new list<t>(); dictionary<string, int> dictheader = new dictionary<string, int>(); using (excelpackage package = new excelpackage(existingfile)) { excelworksheet worksheet = package.workbook.worksheets[1]; int colstart = worksheet.dimension.start.column; //工作区开始列 int colend = worksheet.dimension.end.column; //工作区结束列 int rowstart = worksheet.dimension.start.row; //工作区开始行号 int rowend = worksheet.dimension.end.row; //工作区结束行号 //将每列标题添加到字典中 for (int i = colstart; i <= colend; i++) { dictheader[worksheet.cells[rowstart, i].value.tostring()] = i; } list<propertyinfo> propertyinfolist = new list<propertyinfo>(typeof(t).getproperties()); for (int row = rowstart + 1; row <=rowend; row++) { t result = new t(); //为对象t的各属性赋值 foreach (propertyinfo p in propertyinfolist) { try { excelrange cell = worksheet.cells[row, dictheader[p.name]]; //与属性名对应的单元格 if (cell.value == null) continue; switch (p.propertytype.name.tolower()) { case "string": p.setvalue(result, cell.getvalue<string>()); break; case "int16": p.setvalue(result, cell.getvalue<int16>()); break; case "int32": p.setvalue(result, cell.getvalue<int32>()); break; case "int64": p.setvalue(result, cell.getvalue<int64>()); break; case "decimal": p.setvalue(result, cell.getvalue<decimal>()); break; case "double": p.setvalue(result, cell.getvalue<double>()); break; case "datetime": p.setvalue(result, cell.getvalue<datetime>()); break; case "boolean": p.setvalue(result, cell.getvalue<boolean>()); break; case "byte": p.setvalue(result, cell.getvalue<byte>()); break; case "char": p.setvalue(result, cell.getvalue<char>()); break; case "single": p.setvalue(result, cell.getvalue<single>()); break; default: break; } } catch (keynotfoundexception ex) { } } resultlist.add(result); } } return resultlist; } #endregion #endregion #endregion
2.confighelper添加读取配置文件方法()
private static iconfiguration _configuration; static confighelper() { //在当前目录或者根目录中寻找appsettings.json文件 var filename = "config/managerconfig.json"; var directory = appcontext.basedirectory; directory = directory.replace("\\", "/"); var filepath = $"{directory}/{filename}"; if (!file.exists(filepath)) { var length = directory.indexof("/bin"); filepath = $"{directory.substring(0, length)}/{filename}"; } var builder = new configurationbuilder() .addjsonfile(filepath, false, true); _configuration = builder.build(); } public static string getsectionvalue(string key) { return _configuration.getsection(key).value; }
3.commonhelper中加入json相关操作
/// <summary> /// 得到一个包含json信息的jsonresult /// </summary> /// <param name="isok">服务器处理是否成功 1.成功 -1.失败 0.没有数据</param> /// <param name="msg">报错消息</param> /// <param name="data">携带的额外信息</param> /// <returns></returns> public static string getjsonresult(int code, string msg, object data = null) { var jsonobj = new { code = code, msg = msg, data = data }; return newtonsoft.json.jsonconvert.serializeobject(jsonobj); }
三、添加officecontroller控制器和managerconfig配置文件
1.managerconfig配置()
{ "filemap": { "imgpath": "d:\\myfile\\templatecore\\templatecore\\wwwroot\\upimg\\", "imgweb": "http://127.0.0.1:1994/upimg/", "filepath": "d:\\myfile\\templatecore\\templatecore\\wwwroot\\upfile\\", "fileweb": "http://127.0.0.1:1994/upfile/", "videopath": "d:\\myfile\\templatecore\\templatecore\\wwwroot\\upvideo\\", "videoweb": "http://127.0.0.1:1994/upvideo/", "web": "http://127.0.0.1:1994/" } }
2.officecontroller控制器添加excel处理相应方法
#region epplus导出excel public string dtexportepplusexcel() { string code = "fail"; datatable tbldatas = new datatable("datas"); datacolumn dc = null; dc = tbldatas.columns.add("id", type.gettype("system.int32")); dc.autoincrement = true;//自动增加 dc.autoincrementseed = 1;//起始为1 dc.autoincrementstep = 1;//步长为1 dc.allowdbnull = false;// dc = tbldatas.columns.add("product", type.gettype("system.string")); dc = tbldatas.columns.add("version", type.gettype("system.string")); dc = tbldatas.columns.add("description", type.gettype("system.string")); datarow newrow; newrow = tbldatas.newrow(); newrow["product"] = "大话西游"; newrow["version"] = "2.0"; newrow["description"] = "我很喜欢"; tbldatas.rows.add(newrow); newrow = tbldatas.newrow(); newrow["product"] = "梦幻西游"; newrow["version"] = "3.0"; newrow["description"] = "比大话更幼稚"; tbldatas.rows.add(newrow); newrow = tbldatas.newrow(); newrow["product"] = "西游记"; newrow["version"] = null; newrow["description"] = ""; tbldatas.rows.add(newrow); for (int x = 0; x < 100000; x++) { newrow = tbldatas.newrow(); newrow["product"] = "西游记"+x; newrow["version"] = ""+x; newrow["description"] = x; tbldatas.rows.add(newrow); } string filename = "myexcel.xlsx"; string[] namestrs = new string[tbldatas.rows.count];//每列名,这里不赋值则表示取默认 string savepath = "wwwroot/excel";//相对路径 string msg = "excel/"+ filename;//文件返回地址,出错就返回错误信息。 system.diagnostics.stopwatch watch = new system.diagnostics.stopwatch(); watch.start(); //开始监视代码运行时间 bool b = officehelper.dtexportepplusexcel(tbldatas, savepath, filename, namestrs, ref msg) ; timespan timespan = watch.elapsed; //获取当前实例测量得出的总时间 watch.stop(); //停止监视 if (b) { code = "success"; } return "{\"code\":\"" + code + "\",\"msg\":\"" + msg + "\",\"timeseconds\":\"" + timespan.totalseconds + "\"}"; } public string modelexportepplusexcel() { string code = "fail"; list<article> articlelist = new list<article>(); for (int x = 0; x < 100000; x++) { article article = new article(); article.context = "内容:"+x; article.id = x + 1; article.createtime = datetime.now; article.title = "标题:" + x; articlelist.add(article); } string filename = "mymodelexcel.xlsx"; string[] namestrs = new string[4] {"id", "title", "context", "createtime" };//按照模型先后顺序,赋值需要的名称 string savepath = "wwwroot/excel";//相对路径 string msg = "excel/" + filename;//文件返回地址,出错就返回错误信息。 system.diagnostics.stopwatch watch = new system.diagnostics.stopwatch(); watch.start(); //开始监视代码运行时间 bool b = officehelper.modelexportepplusexcel(articlelist, savepath, filename, namestrs, ref msg); timespan timespan = watch.elapsed; //获取当前实例测量得出的总时间 watch.stop(); //停止监视 if (b) { code = "success"; } return "{\"code\":\"" + code + "\",\"msg\":\"" + msg + "\",\"timeseconds\":\"" + timespan.totalseconds + "\"}"; } #endregion #region epplus导出数据 public async task<string> excelimportepplusdtjsonasync() { iformfilecollection files = request.form.files; datatable articles = new datatable(); int code = 0; string msg = "失败!"; var file = files[0]; string path = confighelper.getsectionvalue("filemap:filepath") + files[0].filename; using (filestream fs = system.io.file.create(path)) { await file.copytoasync(fs); fs.flush(); } system.diagnostics.stopwatch watch = new system.diagnostics.stopwatch(); watch.start(); //开始监视代码运行时间 fileinfo fileexcel = new fileinfo(path); articles = officehelper.inputepplusbyexceltodt(fileexcel); timespan timespan = watch.elapsed; //获取当前实例测量得出的总时间 watch.stop(); //停止监视 code = 1; msg = "成功!"; string json = commonhelper.getjsonresult(code, msg, new { articles, timespan }); return json; } public async task<string> excelimportepplusmodeljsonasync() { iformfilecollection files = request.form.files; list<article> articles = new list<article>(); int code = 0; string msg = "失败!"; var file = files[0]; string path = confighelper.getsectionvalue("filemap:filepath")+files[0].filename; using (filestream fs = system.io.file.create(path)) { await file.copytoasync(fs); fs.flush(); } system.diagnostics.stopwatch watch = new system.diagnostics.stopwatch(); watch.start(); //开始监视代码运行时间 fileinfo fileexcel = new fileinfo(path); articles=officehelper.loadfromexcel<article>(fileexcel).tolist(); timespan timespan = watch.elapsed; //获取当前实例测量得出的总时间 watch.stop(); //停止监视 code = 1;msg = "成功!"; string json = commonhelper.getjsonresult(code, msg, new { articles, timespan }); return json; } #endregion
四、前端请求设计
<script type="text/javascript"> function dtepplusexport() { $.ajax({ type: "post", contenttype: 'application/json', url: '/office/dtexportepplusexcel', datatype: 'json', async: false, success: function (data) { if (data.code == "success") { window.open("../" + data.msg); $("#dtepplusexcel").text("导出10w条耗时"+data.timeseconds+"秒"); } console.log(data.code); }, error: function (xhr) { console.log(xhr.responsetext); } }); } function modelepplusexport() { $.ajax({ type: "post", contenttype: 'application/json', url: '/office/modelexportepplusexcel', datatype: 'json', async: false, success: function (data) { if (data.code == "success") { window.open("../" + data.msg); $("#modelepplusexcel").text("导出10w条耗时"+data.timeseconds+"秒"); } console.log(data.code); }, error: function (xhr) { console.log(xhr.responsetext); } }); } function exceltomodel() { $("#filename").text(document.getelementbyid("exceltomodel").files[0].name); var formdata = new formdata(); formdata.append('file',document.getelementbyid("exceltomodel").files[0]); $.ajax({ url: "../office/excelimportepplusmodeljson", type: "post", data: formdata, contenttype: false, processdata: false, datatype: "json", success: function(result){ if (result.code == 1) { console.log(result.data.articles); $("#tomodel").text("导入10w条耗时"+result.data.timespan+"秒"); } } }); } function exceltodt() { $("#filename1").text(document.getelementbyid("exceltodt").files[0].name); var formdata = new formdata(); formdata.append('file',document.getelementbyid("exceltodt").files[0]); $.ajax({ url: "../office/excelimportepplusdtjson", type: "post", data: formdata, contenttype: false, processdata: false, datatype: "json", success: function(result){ if (result.code == 1) { console.log(result.data.articles); $("#todt").text("导入10w条耗时"+result.data.timespan+"秒"); } } }); } </script> <style> body { margin:auto; text-align:center; } </style> <div style="margin-top:30px;"> <h3>epplus导出案例</h3> <button type="button" class="btn btn-default" id="dtepplusexcel" onclick="dtepplusexport();">excel导出测试(datatable)</button> <button type="button" class="btn btn-default" id="modelepplusexcel" onclick="modelepplusexport();">excel导出测试(model)</button> <h3>epplus导入案例</h3> <div class="col-xs-12 col-sm-4 col-md-4" style="width:100%;"> <div class="file-container" style="display:inline-block;position:relative;overflow: hidden;vertical-align:middle"> <label>model:</label> <button class="btn btn-success fileinput-button" type="button">上传</button> <input type="file" accept=".xls,.xlsx" id="exceltomodel" onchange="exceltomodel(this.files[0])" style="position:absolute;top:0;left:0;font-size:34px; opacity:0"> </div> <span id="filename" style="vertical-align: middle">未上传文件</span> <span id="tomodel"></span> </div><br /><br /> <div class="col-xs-12 col-sm-4 col-md-4" style="width:100%;"> <div class="file-container" style="display:inline-block;position:relative;overflow: hidden;vertical-align:middle"> <label>datatable:</label> <button class="btn btn-success fileinput-button" type="button">上传</button> <input type="file" accept=".xls,.xlsx" id="exceltodt" onchange="exceltodt(this.files[0])" style="position:absolute;top:0;left:0;font-size:34px; opacity:0"> </div> <span id="filename1" style="vertical-align: middle">未上传文件</span> <span id="todt"></span> </div> </div>
上面的上传为了美观用了bootstrap的样式,如果你喜欢原生可以把两个导入案例通过如下代码替换,其它条件无需改变。
<label>model:</label><input accept=".xls,.xlsx" type="file" id="exceltomodel" style="display:initial;" /><button id="tomodel" onclick="exceltomodel();" >上传</button><br /> <label>datatable:</label><input accept=".xls,.xlsx" type="file" id="exceltodt" style="display:initial;" /><button id="todt" onclick="exceltodt();">上传</button>
五、那么看下效果吧,我们这里模拟了10w条数据进行了简单实验,一般要求满足应该没什么问题。
开源地址 动动小手,点个推荐吧!
注意:我们机遇屋该项目将长期为大家提供asp.net core各种好用demo,旨在帮助.net开发者提升竞争力和开发速度,建议尽早收藏该模板集合项目。
上一篇: 一文掌握 Lambda 表达式
下一篇: ARTS改版啦,在改变中前行