在ASP中导出图表到Excel.净MVC5
本文关键字:Excel MVC5 ASP | 更新日期: 2023-09-27 18:13:53
我正在尝试从我的ASP.net项目导出数据和图表到Excel,我使用下面的代码,但我没有成功显示Excel文件中的图表,我只看到文本
public ActionResult ExportToExcel()
{
string imgPath2 = Request.Url.GetLeftPart(UriPartial.Authority) + VirtualPathUtility.ToAbsolute("~/Content/" + tmpChartName);
Response.Clear();
Response.ContentType = "application/vnd.ms-excel";
Response.AddHeader("Content-Disposition", "attachment; filename=test.xls;");
StringWriter stringWrite = new StringWriter();
HtmlTextWriter htmlWrite = new HtmlTextWriter(stringWrite);
string headerTable = @"<Table><tr><td>Begin</td><td><img src='" + imgPath2 + @"' /></td><td>end</td></tr></Table>";
Response.Output.Write(headerTable);
Response.End();
return null;
}
一般来说,我们不需要过度杀伤/高脂肪的库,而是致命的简单库/helper类来解决我们的问题
我找到了一个helper类,从这里生成Excel 2007+文件,并添加了一些扩展方法,用于将List
数据转换为DataTable。
虽然其文档有,但需要总结:
- 取消对MVC/ASP.net项目的第一行的注释。
-
类在
#region HELPER_FUNCTIONS
中有一个方法public static DataTable ListToDataTable<T>(List<T> list)
但我使用另一种解决方案,这是从互联网ToDataTable
窃取的扩展方法
public static DataTable ToDataTable<T>(this List<T> list)
// ExtensionMethods.cs
static public class ExtensionMethods
{
public static DataSet ToDataSet<T>(this IEnumerable<T> data)
{
DataSet ds = new DataSet();
ds.Tables.Add(data.ToDataTable());
return ds;
}
public static DataTable ToDataTable<T>(this List<T> list)
{
DataTable dt = new DataTable();
foreach (PropertyInfo info in typeof(T).GetProperties())
{
dt.Columns.Add(new DataColumn(info.Name, GetNullableType(info.PropertyType)));
}
foreach (T t in list)
{
DataRow row = dt.NewRow();
foreach (PropertyInfo info in typeof(T).GetProperties())
{
if (!IsNullableType(info.PropertyType))
row[info.Name] = info.GetValue(t, null);
else
row[info.Name] = (info.GetValue(t, null) ?? DBNull.Value);
}
dt.Rows.Add(row);
}
return dt;
}
private static Type GetNullableType(Type t)
{
Type returnType = t;
if (t.IsGenericType && t.GetGenericTypeDefinition().Equals(typeof(Nullable<>)))
{
returnType = Nullable.GetUnderlyingType(t);
}
return returnType;
}
private static bool IsNullableType(Type type)
{
return (type == typeof(string) ||
type.IsArray ||
(type.IsGenericType &&
type.GetGenericTypeDefinition().Equals(typeof(Nullable<>))));
}
}
最后的用法很简单把这段代码放到Controller
public void ExportToExcel()
{
using (var db = new EFDbContext())
{
var data = FetchDataAsList<MyDbEntityOrMvcModel>().ToDataTable();
CreateExcelFile.CreateExcelDocument(data, "report.xlsx", System.Web.HttpContext.Current.Response);
}
}
注意我用System.Web.HttpContext.Current.Response
不是 HttpContext.Current.Response
从视图
调用你的操作<a href="@Url.Action("GridExportToExcel", new { filter = Model.Filter})" class="fa fa-file-excel-o" />