在mvc4中上传、导入和导出excel表方法总结
通过excel的导入导出练习，使用NPOI组件还是方便一点，所有下面就以NPOI下的导入导出给出实例，通过网页导入excel表，首先上传，再导入数据到库，这里为了方便就不导入到库中了，直接拿到数据。导出方法比较多。
第一步下载NPOI组件，并在VS中导入dll.
第二步：为了方便，把功能提取成方法:
1 集合与DataTable间的相互转换创建类
public static class DataTableTool { /// <summary> /// 转化一个DataTable /// </summary> /// <typeparam name="T"></typeparam> /// <param name="list"></param> /// <returns></returns> public static DataTable ToDataTable<T>(this IEnumerable<T> list) { //创建属性的集合 List<PropertyInfo> pList = new List<PropertyInfo>(); //获得反射的入口 Type type = typeof(T); DataTable dt = new DataTable(); //把所有的public属性加入到集合 并添加DataTable的列 Array.ForEach<PropertyInfo>(type.GetProperties(), p => { pList.Add(p); dt.Columns.Add(p.Name, p.PropertyType); }); foreach (var item in list) { //创建一个DataRow实例 DataRow row = dt.NewRow(); //给row 赋值 pList.ForEach(p => row[p.Name] = p.GetValue(item, null)); //加入到DataTable dt.Rows.Add(row); } return dt; } /// <summary> /// DataTable 转换为List 集合 /// </summary> /// <typeparam name="TResult">类型</typeparam> /// <param name="dt">DataTable</param> /// <returns></returns> public static List<T> ToList<T>(this DataTable dt) where T : class, new() { //创建一个属性的列表 List<PropertyInfo> prlist = new List<PropertyInfo>(); //获取TResult的类型实例 反射的入口 Type t = typeof(T); //获得TResult 的所有的Public 属性 并找出TResult属性和DataTable的列名称相同的属性(PropertyInfo) 并加入到属性列表 Array.ForEach<PropertyInfo>(t.GetProperties(), p => { if (dt.Columns.IndexOf(p.Name) != -1) prlist.Add(p); }); //创建返回的集合 List<T> oblist = new List<T>(); foreach (DataRow row in dt.Rows) { //创建TResult的实例 T ob = new T(); //找到对应的数据 并赋值 prlist.ForEach(p => { if (row[p.Name] != DBNull.Value) p.SetValue(ob, row[p.Name], null); }); //放入到返回的集合中. oblist.Add(ob); } return oblist; } /// <summary> /// 将集合类转换成DataTable /// </summary> /// <param name="list">集合</param> /// <returns></returns> public static DataTable ToDataTableTow(IList list) { DataTable result = new DataTable(); if (list.Count > 0) { PropertyInfo[] propertys = list[0].GetType().GetProperties(); foreach (PropertyInfo pi in propertys) { result.Columns.Add(pi.Name, pi.PropertyType); } for (int i = 0; i < list.Count; i++) { ArrayList tempList = new ArrayList(); foreach (PropertyInfo pi in propertys) { object obj = pi.GetValue(list[i], null); tempList.Add(obj); } object[] array = tempList.ToArray(); result.LoadDataRow(array, true); } } return result; } /**/ /// <summary> /// 将泛型集合类转换成DataTable /// </summary> /// <typeparam name="T">集合项类型</typeparam> /// <param name="list">集合</param> /// <returns>数据集(表)</returns> public static DataTable ToDataTable<T>(IList<T> list) { return ToDataTable<T>(list, null); } /**/ /// <summary> /// 将泛型集合类转换成DataTable /// </summary> /// <typeparam name="T">集合项类型</typeparam> /// <param name="list">集合</param> /// <param name="propertyName">需要返回的列的列名</param> /// <returns>数据集(表)</returns> public static DataTable ToDataTable<T>(IList<T> list, params string[] propertyName) { List<string> propertyNameList = new List<string>(); if (propertyName != null) propertyNameList.AddRange(propertyName); DataTable result = new DataTable(); if (list.Count > 0) { PropertyInfo[] propertys = list[0].GetType().GetProperties(); foreach (PropertyInfo pi in propertys) { if (propertyNameList.Count == 0) { result.Columns.Add(pi.Name, pi.PropertyType); } else { if (propertyNameList.Contains(pi.Name)) result.Columns.Add(pi.Name, pi.PropertyType); } } for (int i = 0; i < list.Count; i++) { ArrayList tempList = new ArrayList(); foreach (PropertyInfo pi in propertys) { if (propertyNameList.Count == 0) { object obj = pi.GetValue(list[i], null); tempList.Add(obj); } else { if (propertyNameList.Contains(pi.Name)) { object obj = pi.GetValue(list[i], null); tempList.Add(obj); } } } object[] array = tempList.ToArray(); result.LoadDataRow(array, true); } } return result; } }
2 excel导入导出类
public static class ExcelTool { /// <summary> /// 将excel中的数据导入到DataTable中 /// </summary> /// <param name="isFirstRowColumn">第一行是否是DataTable的列名</param> /// <param name="fileName">文件路径</param> /// <param name="sheetName">excel工作薄sheet的名称</param> /// <returns>返回的DataTable</returns> public static DataTable ExcelToDataTable(bool isFirstRowColumn, string fileName, string sheetName = "") { if (string.IsNullOrEmpty(fileName)) { throw new ArgumentNullException(fileName); } var data = new DataTable(); IWorkbook workbook = null; FileStream fs = null; try { fs = new FileStream(fileName, FileMode.Open, FileAccess.Read); if (fileName.IndexOf(".xlsx", StringComparison.Ordinal) > 0) { workbook = new XSSFWorkbook(fs); } else if (fileName.IndexOf(".xls", StringComparison.Ordinal) > 0) { workbook = new HSSFWorkbook(fs); } ISheet sheet = null; if (workbook != null) { //如果没有找到指定的sheetName对应的sheet，则尝试获取第一个sheet if (sheetName == "") { sheet = workbook.GetSheetAt(0); } else { sheet = workbook.GetSheet(sheetName) ?? workbook.GetSheetAt(0); } } if (sheet == null) return data; var firstRow = sheet.GetRow(0); //一行最后一个cell的编号 即总的列数 int cellCount = firstRow.LastCellNum; int startRow; if (isFirstRowColumn) { for (int i = firstRow.FirstCellNum; i < cellCount; ++i) { var cell = firstRow.GetCell(i); var cellValue = cell.StringCellValue; if (cellValue == null) continue; var column = new DataColumn(cellValue); data.Columns.Add(column); } startRow = sheet.FirstRowNum + 1; } else { startRow = sheet.FirstRowNum; } //最后一列的标号 var rowCount = sheet.LastRowNum; for (var i = startRow; i <= rowCount; ++i) { var row = sheet.GetRow(i); //没有数据的行默认是null if (row == null) continue; var dataRow = data.NewRow(); for (int j = row.FirstCellNum; j < cellCount; ++j) { //同理，没有数据的单元格都默认是null if (row.GetCell(j) != null) dataRow[j] = row.GetCell(j).ToString(); } data.Rows.Add(dataRow); } return data; } catch (IOException ioex) { throw new IOException(ioex.Message); } catch (Exception ex) { throw new Exception(ex.Message); } finally { if (fs != null) { fs.Close(); } } } /// <summary> /// 将DataTable数据导入到excel中 /// </summary> /// <param name="data">要导入的数据</param> /// <param name="isColumnWritten">DataTable的列名是否要导入</param> /// <param name="sheetName">要导入的excel的sheet的名称</param> /// <param name="fileName">文件夹路径</param> /// <returns>导入数据行数(包含列名那一行)</returns> public static int DataTableToExcel(DataTable data, string sheetName, bool isColumnWritten, string fileName) { if (data == null) { throw new ArgumentNullException("data"); } if (string.IsNullOrEmpty(sheetName)) { throw new ArgumentNullException(sheetName); } if (string.IsNullOrEmpty(fileName)) { throw new ArgumentNullException(fileName); } IWorkbook workbook = null; if (fileName.IndexOf(".xlsx", StringComparison.Ordinal) > 0) { workbook = new XSSFWorkbook(); } else if (fileName.IndexOf(".xls", StringComparison.Ordinal) > 0) { workbook = new HSSFWorkbook(); } FileStream fs = null; try { fs = new FileStream(fileName, FileMode.OpenOrCreate, FileAccess.ReadWrite); ISheet sheet; if (workbook != null) { sheet = workbook.CreateSheet(sheetName); } else { return -1; } int j; int count; //写入DataTable的列名，写入单元格中 if (isColumnWritten) { var row = sheet.CreateRow(0); for (j = 0; j < data.Columns.Count; ++j) { row.CreateCell(j).SetCellValue(data.Columns[j].ColumnName); } count = 1; } else { count = 0; } //遍历循环datatable具体数据项 int i; for (i = 0; i < data.Rows.Count; ++i) { var row = sheet.CreateRow(count); for (j = 0; j < data.Columns.Count; ++j) { row.CreateCell(j).SetCellValue(data.Rows[i][j].ToString()); } ++count; } //将文件流写入到excel workbook.Write(fs); return count; } catch (IOException ioex) { throw new IOException(ioex.Message); } catch (Exception ex) { throw new Exception(ex.Message); } finally { if (fs != null) { fs.Close(); } } } }
第三步：下面开始做一个很简单的网页来展示导入与导出，还有数据的显示页面。页面中使用Vue
@{ Layout = null; } <!DOCTYPE html> <html> <head> <meta name="viewport" content="width=device-width" /> <title>Index</title> <script src="~/Scripts/jquery-3.2.1.min.js"></script> <script src="~/Scripts/vue.js"></script> <script src="~/Scripts/axios.min.js"></script> <style> td{ padding:1rem; } </style> </head> <body > <div id="app"> <form id="myform"> <table border="1"> <tr> <td>导入</td> <td> <input type="file" name="fileup" id="fileup" v-on:change="fileChange()" style="display:none"/> <img src="/content/images/upload.png" v-on:click="upclick()"> </td> </tr> </table> </form> <table> <tr v-for="item in list"> <td>{{item.No}}</td> <td>{{item.Name}}</td> <td>{{item.Age}}</td> <td>{{item.Sex}}</td> </tr> </table> <br /> <a href="/home/export">转成excel</a> </div> <script> var app = new Vue({ el: "#app", data: { list: [], }, methods: { downExcel: function () { $.post("", {},function (res) { }); }, upclick:function(){ document.getElementById("fileup").click(); }, fileChange: function () { if (!document.getElementById("fileup").files[0].size) return; var obj = new FormData(document.getElementById("myform")); obj.append("name", "wzh"); var _this = this; $.ajax({ type: 'post', url: '/home/Import', data: obj, cache: false, processData: false, // 不处理发送的数据，因为data值是Formdata对象，不需要对数据做处理 contentType: false, // 不设置Content-type请求头 success: function (data) { if (data != "no") { _this.list = data; } else { alert(data); } }, }); }, } }) </script> </body> </html>
第四步：actionresult的完成
上传与导入：
public ActionResult Import() { try { HttpPostedFileBase uploadfile = Request.Files["fileup"]; if (uploadfile == null) { return Content("no:非法上传"); } if (uploadfile.FileName == "") { return Content("no:请选择文件"); } string fileExt = Path.GetExtension(uploadfile.FileName); StringBuilder sbtime = new StringBuilder(); sbtime.Append(DateTime.Now.Year).Append(DateTime.Now.Month).Append(DateTime.Now.Day).Append(DateTime.Now.Hour).Append(DateTime.Now.Minute).Append(DateTime.Now.Second); string dir = "/UploadFile/" + sbtime.ToString() + fileExt; string realfilepath = Request.MapPath(dir); string readDir = Path.GetDirectoryName(realfilepath); if (!Directory.Exists(readDir)) Directory.CreateDirectory(readDir); uploadfile.SaveAs(realfilepath); //提取数据 var dt =ExcelTool.ExcelToDataTable(true, realfilepath); List<User> list = new List<User>(); foreach (DataRow item in dt.Rows) { list.Add(new User() { No = Convert.ToInt32(item[0]), Name = item[1].ToString(), Age = Convert.ToInt32(item[2]), Sex = item[3].ToString() }); } return Json(list,JsonRequestBehavior.AllowGet); } catch (Exception ex) { return Content(ex.Message); } }
导出：
public ActionResult export() { var path = Server.MapPath(@"/content/user.xlsx"); var dt = ExcelTool.ExcelToDataTable(true, path); List<User> list = new List<User>(); foreach (DataRow item in dt.Rows) { list.Add(new User() { No = Convert.ToInt32(item[0]), Name = item[1].ToString(), Age = Convert.ToInt32(item[2]), Sex = item[3].ToString() }); } //datatable //DataTable d = new DataTable(); //d.Columns.Add("No"); //d.Columns.Add("Name"); //d.Columns.Add("Age"); //d.Columns.Add("Sex"); //foreach (var item in list) //{ // d.NewRow(); // d.Rows.Add(new object[]{item.No,item.Name,item.Age,item.Sex}); //} //创建生成的excel的名称 StringBuilder sbtime = new StringBuilder(); sbtime.Append(DateTime.Now.Year).Append(DateTime.Now.Month).Append(DateTime.Now.Day).Append(DateTime.Now.Hour).Append(DateTime.Now.Minute).Append(DateTime.Now.Second); var d = DataTableTool.ToDataTableTow(list); var url = "/downfile/"+sbtime+".xls"; var newpath = Server.MapPath(url); ExcelTool.DataTableToExcel(d, "aa", true, newpath); //取到生成的名称 var name=Path.GetFileName(newpath); return File(newpath, "application/vnd.ms-excel",name); }
这样就完成此次的功能，下面补充几个知识点，
1、File中的参数contentType有
| 扩展名 | 类型/子类型 |
|---|---|
| application/octet-stream | |
| 323 | text/h323 |
| acx | application/internet-property-stream |
| ai | application/postscript |
| aif | audio/x-aiff |
| aifc | audio/x-aiff |
| aiff | audio/x-aiff |
| asf | video/x-ms-asf |
| asr | video/x-ms-asf |
| asx | video/x-ms-asf |
| au | audio/basic |
| avi | video/x-msvideo |
| axs | application/olescript |
| bas | text/plain |
| bcpio | application/x-bcpio |
| bin | application/octet-stream |
| bmp | image/bmp |
| c | text/plain |
| cat | application/vnd.ms-pkiseccat |
| cdf | application/x-cdf |
| cer | application/x-x509-ca-cert |
| class | application/octet-stream |
| clp | application/x-msclip |
| cmx | image/x-cmx |
| cod | image/cis-cod |
| cpio | application/x-cpio |
| crd | application/x-mscardfile |
| crl | application/pkix-crl |
| crt | application/x-x509-ca-cert |
| csh | application/x-csh |
| css | text/css |
| dcr | application/x-director |
| der | application/x-x509-ca-cert |
| dir | application/x-director |
| dll | application/x-msdownload |
| dms | application/octet-stream |
| doc | application/msword |
| dot | application/msword |
| dvi | application/x-dvi |
| dxr | application/x-director |
| eps | application/postscript |
| etx | text/x-setext |
| evy | application/envoy |
| exe | application/octet-stream |
| fif | application/fractals |
| flr | x-world/x-vrml |
| gif | image/gif |
| gtar | application/x-gtar |
| gz | application/x-gzip |
| h | text/plain |
| hdf | application/x-hdf |
| hlp | application/winhlp |
| hqx | application/mac-binhex40 |
| hta | application/hta |
| htc | text/x-component |
| htm | text/html |
| html | text/html |
| htt | text/webviewhtml |
| ico | image/x-icon |
| ief | image/ief |
| iii | application/x-iphone |
| ins | application/x-internet-signup |
| isp | application/x-internet-signup |
| jfif | image/pipeg |
| jpe | image/jpeg |
| jpeg | image/jpeg |
| jpg | image/jpeg |
| js | application/x-javascript |
| latex | application/x-latex |
| lha | application/octet-stream |
| lsf | video/x-la-asf |
| lsx | video/x-la-asf |
| lzh | application/octet-stream |
| m13 | application/x-msmediaview |
| m14 | application/x-msmediaview |
| m3u | audio/x-mpegurl |
| man | application/x-troff-man |
| mdb | application/x-msaccess |
| me | application/x-troff-me |
| mht | message/rfc822 |
| mhtml | message/rfc822 |
| mid | audio/mid |
| mny | application/x-msmoney |
| mov | video/quicktime |
| movie | video/x-sgi-movie |
| mp2 | video/mpeg |
| mp3 | audio/mpeg |
| mpa | video/mpeg |
| mpe | video/mpeg |
| mpeg | video/mpeg |
| mpg | video/mpeg |
| mpp | application/vnd.ms-project |
| mpv2 | video/mpeg |
| ms | application/x-troff-ms |
| mvb | application/x-msmediaview |
| nws | message/rfc822 |
| oda | application/oda |
| p10 | application/pkcs10 |
| p12 | application/x-pkcs12 |
| p7b | application/x-pkcs7-certificates |
| p7c | application/x-pkcs7-mime |
| p7m | application/x-pkcs7-mime |
| p7r | application/x-pkcs7-certreqresp |
| p7s | application/x-pkcs7-signature |
| pbm | image/x-portable-bitmap |
| application/pdf | |
| pfx | application/x-pkcs12 |
| pgm | image/x-portable-graymap |
| pko | application/ynd.ms-pkipko |
| pma | application/x-perfmon |
| pmc | application/x-perfmon |
| pml | application/x-perfmon |
| pmr | application/x-perfmon |
| pmw | application/x-perfmon |
| pnm | image/x-portable-anymap |
| pot, | application/vnd.ms-powerpoint |
| ppm | image/x-portable-pixmap |
| pps | application/vnd.ms-powerpoint |
| ppt | application/vnd.ms-powerpoint |
| prf | application/pics-rules |
| ps | application/postscript |
| pub | application/x-mspublisher |
| qt | video/quicktime |
| ra | audio/x-pn-realaudio |
| ram | audio/x-pn-realaudio |
| ras | image/x-cmu-raster |
| rgb | image/x-rgb |
| rmi | audio/mid |
| roff | application/x-troff |
| rtf | application/rtf |
| rtx | text/richtext |
| scd | application/x-msschedule |
| sct | text/scriptlet |
| setpay | application/set-payment-initiation |
| setreg | application/set-registration-initiation |
| sh | application/x-sh |
| shar | application/x-shar |
| sit | application/x-stuffit |
| snd | audio/basic |
| spc | application/x-pkcs7-certificates |
| spl | application/futuresplash |
| src | application/x-wais-source |
| sst | application/vnd.ms-pkicertstore |
| stl | application/vnd.ms-pkistl |
| stm | text/html |
| svg | image/svg+xml |
| sv4cpio | application/x-sv4cpio |
| sv4crc | application/x-sv4crc |
| swf | application/x-shockwave-flash |
| t | application/x-troff |
| tar | application/x-tar |
| tcl | application/x-tcl |
| tex | application/x-tex |
| texi | application/x-texinfo |
| texinfo | application/x-texinfo |
| tgz | application/x-compressed |
| tif | image/tiff |
| tiff | image/tiff |
| tr | application/x-troff |
| trm | application/x-msterminal |
| tsv | text/tab-separated-values |
| txt | text/plain |
| uls | text/iuls |
| ustar | application/x-ustar |
| vcf | text/x-vcard |
| vrml | x-world/x-vrml |
| wav | audio/x-wav |
| wcm | application/vnd.ms-works |
| wdb | application/vnd.ms-works |
| wks | application/vnd.ms-works |
| wmf | application/x-msmetafile |
| wps | application/vnd.ms-works |
| wri | application/x-mswrite |
| wrl | x-world/x-vrml |
| wrz | x-world/x-vrml |
| xaf | x-world/x-vrml |
| xbm | image/x-xbitmap |
| xla | application/vnd.ms-excel |
| xlc | application/vnd.ms-excel |
| xlm | application/vnd.ms-excel |
| xls | application/vnd.ms-excel |
| xlt | application/vnd.ms-excel |
| xlw | application/vnd.ms-excel |
| xof | x-world/x-vrml |
| xpm | image/x-xpixmap |
| xwd | image/x-xwindowdump |
| z | application/x-compress |
| zip | application/zip |
2、导出方法还有：
public FileResult ExportExcel() { var sbHtml = new StringBuilder(); sbHtml.Append("<table border='1' cellspacing='0' cellpadding='0'>"); sbHtml.Append("<tr>"); var lstTitle = new List<string> { "编号", "姓名", "年龄", "创建时间" }; foreach (var item in lstTitle) { sbHtml.AppendFormat("<td style='font-size: 14px;text-align:center;background-color: #DCE0E2; font-weight:bold;' height='25'>{0}</td>", item); } sbHtml.Append("</tr>"); for (int i = 0; i < 1000; i++) { sbHtml.Append("<tr>"); sbHtml.AppendFormat("<td style='font-size: 12px;height:20px;'>{0}</td>", i); sbHtml.AppendFormat("<td style='font-size: 12px;height:20px;'>屌丝{0}号</td>", i); sbHtml.AppendFormat("<td style='font-size: 12px;height:20px;'>{0}</td>", new Random().Next(20, 30) + i); sbHtml.AppendFormat("<td style='font-size: 12px;height:20px;'>{0}</td>", DateTime.Now); sbHtml.Append("</tr>"); } sbHtml.Append("</table>"); //第一种:使用FileContentResult byte[] fileContents = Encoding.Default.GetBytes(sbHtml.ToString()); return File(fileContents, "application/ms-excel", "fileContents.xls"); //第二种:使用FileStreamResult var fileStream = new MemoryStream(fileContents); return File(fileStream, "application/ms-excel", "fileStream.xls"); //第三种:使用FilePathResult //服务器上首先必须要有这个Excel文件,然会通过Server.MapPath获取路径返回. var fileName = Server.MapPath("~/Files/fileName.xls"); return File(fileName, "application/ms-excel", "fileName.xls"); }
导出文件名有问题可以看这里
http://blog.csdn.net/denghejing/article/details/60753205
