MVC 导出Execl 的总结几种方式 （一）
在ASP.NET 中导出Execl 表格有很多方式，有利有弊，就是看自己怎么使用了；下面就是自己总结了几种导出Execl 方式的，仅供参考。
导出Execl 的原理都是一样的，其实都是将数据整合成table 或者list集合 传递给Execl 来呈现出来。
第一种导出方式：就是通过 dataTable 的形式 生成 execl ，原理呢 就是使用execl 组件生成execl 然后下载到本地，并返回一个路径出来，进行选择下载的位置；这样将会造成本地或者服务器上将会生成大量的文件，这个时候 我们就要执行一个清理的方法，将某一个时间段的文件清除掉；详见下面的代码
第一步：生成Execl 工具类
using System; using System.Collections.Generic; using System.Data; using System.IO; using System.Linq; using System.Reflection; using System.Text; using System.Threading.Tasks; using System.Web; namespace H5UpdateImage.Common { public class ExportExcel { /// <summary> /// 默认使用DataTable的列名 /// </summary> /// <param name="dt"></param> public static void ExportExcels(DataTable dt) { string result = ""; HttpContext.Current.Response.Clear(); HttpContext.Current.Response.ContentType = "text/plain"; if (dt != null && dt.Rows.Count != 0) { var strArrayTitle = new string[dt.Columns.Count]; for (int i = 0; i < dt.Columns.Count; i++) { strArrayTitle[i] = dt.Columns[i].ColumnName.Replace("_", ""); } string sWriteTotal = InitExcelData(dt, strArrayTitle); string fileName = DateTime.Now.ToString("yyyyMMddHHmmsssss") + ".xls"; //将文件写入本地缓存目录，并且返回文件下载路径。D:\JSJ_Web\JSJWEBCRM\JSJWEBCRM\ string currentPath = System.AppDomain.CurrentDomain.BaseDirectory + "DownLoadFiles\\"; if (!Directory.Exists(currentPath)) { Directory.CreateDirectory(currentPath); } #region //清理超过一天的文件 try { string[] files = System.IO.Directory.GetFiles(currentPath, "*.xls", System.IO.SearchOption.TopDirectoryOnly); //获取该目录下的xls文件 foreach (var item in files) { var fi = new FileInfo(item); if ((DateTime.Now - fi.LastWriteTime).TotalHours > 24) { File.Delete(item); } } } catch (Exception) { } #endregion string filePath = currentPath + fileName; File.WriteAllText(filePath, sWriteTotal, Encoding.UTF8); result = "../../DownLoadFiles/" + fileName; } else { result = "提示：查询不到可以导出的数据，请修改查询条件"; } HttpContext.Current.Response.Write(result); HttpContext.Current.Response.End(); } public static void ExportExcels( DataTable dt , string[] strArrayTitle ) { string sWriteTotal = InitExcelData( dt , strArrayTitle ); HttpContext.Current.Response.Clear( ); HttpContext.Current.Response.Charset = "GB2312"; //HttpContext.Current.Response.ContentType = "text/plain"; HttpContext.Current.Response.ContentType = "application/vnd.ms-excel"; HttpContext.Current.Response.ContentEncoding = Encoding.Default; string fileName = DateTime.Now.ToString( "yyyyMMddHHmmsssss" ) + ".xls"; HttpContext.Current.Response.AddHeader( "content-disposition" , "attachment; filename=" + fileName); HttpContext.Current.Response.Write( sWriteTotal ); HttpContext.Current.Response.End( ); } #region 初始化Excel数据 public static string InitExcelData( DataTable dt , string[] strArrayTitle ) { //用于导出excel的HTML StringBuilder sReturn = new StringBuilder( ); sReturn.Append( "<html><head><meta http-equiv=Content-Type content='text/html;charset=gb2312'><style>table td,th{vnd.ms-excel.numberformat:@;text-align: center;}</style></head><body><table border='1'>" ); sReturn.Append( "<tr>" ); for( int i = 0 ; i < strArrayTitle.Length ; i++ ) { sReturn.Append( "<td>" + strArrayTitle[i] + "</td>" ); } sReturn.Append( "</tr>" ); if( dt != null && dt.Rows.Count > 0 ) { for( int j = 0 ; j < dt.Rows.Count ; j++ ) { sReturn.Append( "<tr>" ); for( int i = 0 ; i < dt.Columns.Count ; i++ ) { sReturn.Append( "<td>" + dt.Rows[j][i] + "</td>" ); } sReturn.Append( "</tr>" ); } } sReturn.Append( "</table></body>" ); return sReturn.ToString( ); } #endregion } /// <summary> /// DataTable与实体类互相转换 /// </summary> /// <typeparam name="T">实体类</typeparam> public class ModelHandler<T> where T : new( ) { #region DataTable转换成实体类 /// <summary> /// 填充对象列表：用DataSet的第一个表填充实体类 /// </summary> /// <param name="ds">DataSet</param> /// <returns></returns> public List<T> FillModel( DataSet ds ) { if( ds == null || ds.Tables[0] == null || ds.Tables[0].Rows.Count == 0 ) { return null; } else { return FillModel( ds.Tables[0] ); } } /// <summary> /// 填充对象列表：用DataSet的第index个表填充实体类 /// </summary> public List<T> FillModel( DataSet ds , int index ) { if( ds == null || ds.Tables.Count <= index || ds.Tables[index].Rows.Count == 0 ) { return null; } else { return FillModel( ds.Tables[index] ); } } /// <summary> /// 填充对象列表：用DataTable填充实体类 /// </summary> public List<T> FillModel( DataTable dt ) { if( dt == null || dt.Rows.Count == 0 ) { return null; } List<T> modelList = new List<T>( ); foreach( DataRow dr in dt.Rows ) { //T model = (T)Activator.CreateInstance(typeof(T)); T model = new T( ); for( int i = 0 ; i < dr.Table.Columns.Count ; i++ ) { PropertyInfo propertyInfo = model.GetType( ).GetProperty( dr.Table.Columns[i].ColumnName ); if( propertyInfo != null && dr[i] != DBNull.Value ) propertyInfo.SetValue( model , dr[i] , null ); } modelList.Add( model ); } return modelList; } /// <summary> /// 填充对象：用DataRow填充实体类 /// </summary> public T FillModel( DataRow dr ) { if( dr == null ) { return default( T ); } //T model = (T)Activator.CreateInstance(typeof(T)); T model = new T( ); for( int i = 0 ; i < dr.Table.Columns.Count ; i++ ) { PropertyInfo propertyInfo = model.GetType( ).GetProperty( dr.Table.Columns[i].ColumnName ); if( propertyInfo != null && dr[i] != DBNull.Value ) propertyInfo.SetValue( model , dr[i] , null ); } return model; } #endregion #region 实体类转换成DataTable /// <summary> /// 实体类转换成DataSet /// </summary> /// <param name="modelList">实体类列表</param> /// <returns></returns> public DataSet FillDataSet( List<T> modelList ) { if( modelList == null || modelList.Count == 0 ) { return null; } else { DataSet ds = new DataSet( ); ds.Tables.Add( FillDataTable( modelList ) ); return ds; } } /// <summary> /// 实体类转换成DataTable /// </summary> /// <param name="modelList">实体类列表</param> /// <returns></returns> public static DataTable FillDataTable( List<T> modelList ) { if( modelList == null || modelList.Count == 0 ) { return null; } DataTable dt = CreateData( modelList[0] ); foreach( T model in modelList ) { DataRow dataRow = dt.NewRow( ); foreach( PropertyInfo propertyInfo in typeof( T ).GetProperties( ) ) { dataRow[propertyInfo.Name] = propertyInfo.GetValue( model , null ); } dt.Rows.Add( dataRow ); } return dt; } /// <summary> /// 根据实体类得到表结构 /// </summary> /// <param name="model">实体类</param> /// <returns></returns> private static DataTable CreateData( T model ) { DataTable dataTable = new DataTable( typeof( T ).Name ); foreach( PropertyInfo propertyInfo in typeof( T ).GetProperties( ) ) { dataTable.Columns.Add( new DataColumn( propertyInfo.Name , propertyInfo.PropertyType ) ); } return dataTable; } #endregion } }
第二步：编写控制器
/// <summary> /// 根据table 内容导出Execl /// </summary> public void ExportTable() { //ExportExcel.ExportExcels(GetTable()); var dt = this.GetTable(); ExportExcel.ExportExcels(dt); }
第三步：前端
function ExportTable() { $.get('@Url.Action("ExportTable")', function (result) { if (result.indexOf("提示") == -1) { self.location.href = result; } else { alert(result) } }) }
这样就轻松搞定了，如果什么问题和建议及时与我沟通，我会及时更正的，相互学习，天天进步一点点
