问题描述
我有高达15万的对象名单,我想绑定在运行时剃刀视图将其导出为EXCEL但在当时,结合我让我走出内存异常,有没有什么解决办法来克服这个限制?
Export方法:
公共无效ExportToExcel()
{
字符串viewPath =〜/模块/报告/查看/+ TempData的[所以reportName] +.cshtml
字符串viewPathCopy =〜/模块/报告/查看/+ TempData的[所以reportName] +2.cshtml 字符串serverViewPath =使用Server.Mappath(viewPath);
字符串serverViewPathCopy =使用Server.Mappath(viewPathCopy); 如果(System.IO.File.Exists(serverViewPathCopy))
{
System.IO.File.Delete(serverViewPathCopy);
} System.IO.File.Copy(serverViewPath,serverViewPathCopy); 字符串viewContents = System.IO.File.ReadAllText(serverViewPathCopy).Replace(THEAD,TR); viewContents + =<风格>机身{字体大小:8像素重要;} {表填充:0重要的是,保证金:!0重要}< /风格与GT;
System.IO.File.WriteAllText(serverViewPathCopy,viewContents); System.IO.File.WriteAllText(viewPathCopy,TemplateFromFile(viewPathCopy,TempData的[reportData])); FileInfo的文件=新的FileInfo(viewPathCopy);
如果(file.Exists)
{
Response.Clear();
Response.AddHeader(内容处置,附件;文件名=+ file.Name +的.xls);
Response.AddHeader(内容长度,file.Length.ToString());
Response.ContentType =应用程序/八位字节流;
Response.WriteFile(file.FullName);
到Response.End();
}
其他
{
的Response.Write(此文件不存在。);
}
}
绑定模型查看:
公共字符串TemplateFromFile(字符串文件,动态模型)
{
串模板=;
的TextReader的TextReader =新的StreamReader(HelperMethods.GetFullFilePath(文件));
尝试
{
模板= textReader.ReadToEnd();
}
最后
{
textReader.Close();
}
返回Razor.Parse(模板模型);
}
如果我有大量的数据导出,我转到工具的这可以让你创建一个有效的出口XLSX。随着抄你甚至可以创建多标签Excel表(见我在的例子)。
有些code
扩展了IEnumerable
公共静态ExportBuilder<&的TModel GT;出口<&的TModel GT;(这IEnumerable的<&的TModel GT;型号),其中的TModel:类
{
返回ExportBuilder<&的TModel GT; .Create(型号);
}
导出生成器
公共类ExportBuilder<&的TModel GT;那里的TModel:类
{
私人只读的IEnumerable<&的TModel GT; _楷模;
私人只读的ICollection< IExportColumn<&的TModel GT;> _列; ///<总结>
///初始化的在&lt一个新的实例;见CREF =T:System.Object的/>类。
///< /总结>
私人ExportBuilder(IEnumerable的<&的TModel GT;型号)
{
_models =模型;
_columns =新的List< IExportColumn<&的TModel GT;>();
} 公共静态ExportBuilder<&的TModel GT;创建(IEnumerable的<&的TModel GT;型号)
{
返回新ExportBuilder<&的TModel GT;(型号);
} 公共ExportBuilder<&的TModel GT;列< TProperty>(防爆pression<&Func键LT;的TModel,TProperty>>显示器)
{
如果(!(display.Body是MemberEx pression))
抛出新的ArgumentException(显示器+不是财产!); VAR的MemberInfo =((MemberEx pression)display.Body)。成员;
如果(memberInfo.HasAttribute<!DisplayNameAttribute>())
抛出新的ArgumentException(显示器+没有一个[显示]属性);
VAR displayAttribute = ExtensionsForMemberInfo.GetAttribute< DisplayNameAttribute>(的MemberInfo); _columns.Add(新ExportColumn<的TModel,TProperty>(displayAttribute.DisplayName,显示器));
返回此;
} 公共ExportBuilder<&的TModel GT;列< TProperty>(串头,防爆pression<&Func键LT;的TModel,TProperty>>属性)
{
_columns.Add(新ExportColumn<的TModel,TProperty>(头,财产));
返回此;
} 公共IReportSource ToReportSource()
{
如果(_models.Any())
{
返回DoddleExporter.ToReportSource(_models.Select(型号=> _columns.ToDictionary(C => c.Header,C => C.DISPLAY(模型))));
}
VAR的结果= _columns
.ToDictionary(一个= GT; a.Header,一个= GT;的String.Empty);
返回DoddleExporter.ToReportSource(新[] {结果});
} 公开报告ToReport([CanBeNull]的IEnumerable< KeyValuePair<字符串,字符串>>头,[CanBeNull] IReportWriter作家= NULL)
{
头=头? Enumerable.Empty< KeyValuePair<字符串,字符串>>();
VAR报告=新的报告(ToReportSource(),作家);
//report.TextFields.Footer =的String.Format(@Aangemaakt OP:{0},DateTime.Now.ToString(DataFormatStrings.Date));
VAR headersArray =标题,如KeyValuePair<字符串,字符串> []? headers.ToArray();
如果(headersArray.Any())
{
report.TextFields.Header = headersArray.Aggregate(的String.Empty,
(currentHeaders,报头)=>的String.Format({0} {3} {1} {2},currentHeaders,header.Key,header.Value,Environment.NewLine));
} 返回报告;
} 公共ReportResult ToExcelReportResult([CanBeNull]的IEnumerable< KeyValuePair<字符串,字符串>>头)
{
返回新ReportResult(ToReport(头),新ExcelReportWriter(),应用程序/ vnd.openxmlformats-officedocument.s preadsheetml.sheet);
}
}
的出口商
公共静态类DoddleExporter
{
///<总结>
///字典的枚举转换为报表源
///< /总结>
///< typeparam NAME =TValue>
///在字典的类型值
///< / typeparam>
///< PARAM NAME =元素>
///元素的一个枚举
///< /参数>
///<退货和GT;这是从元素&lt创建报表源; /回报>
公共静态IReportSource ToReportSource< TValue>(IEnumerable的<&IDictionary的LT;字符串,TValue>>元素)
{
变种elementsArray = elements.ToArray();
如果(!elementsArray.Any())
抛出新的ArgumentException(不能出口的元素空列表);
返回ToReportSource(elementsArray,elementsArray.First()。Keys.ToArray(),
(元素,键)=> element.ContainsKey(钥匙)?元素[关键]:默认(TValue));
} ///<总结>
///的XElement的枚举转换为报表源
///< /总结>
///< PARAM NAME =rootElements>
///包含值XML根元素
///< /参数>
///< PARAM NAME =键>
///它们键,可用于从每个根元素取的值
///< /参数>
///<退货和GT;这是从元素&lt创建报表源; /回报>
公共静态IReportSource ToReportSource(IEnumerable的<&的XElement GT; rootElements,字符串[]键)
{
返回ToReportSource(rootElements,钥匙,委托(的XElement元素,字符串键)
{
VAR值= element.Element(XmlConvert.En codeLocalName(键));
返回值!= NULL? value.Value:空;
});
} ///<总结>
///元素列表转换为报表源
///< /总结>
///< PARAM NAME =元素>
///元素的一个枚举
///< /参数>
///< PARAM NAME =键>
///他们键与该值可以从一个元素获取
///< /参数>
///< PARAM NAME =valueSelector>
///与一个值可以获取的给定的一个键和一个元素的功能
///< /参数>
///<退货和GT;这是从元素&lt创建报表源; /回报>
公共静态IReportSource ToReportSource< T>(IEnumerable的< T>元素,字符串[]键,
FUNC< T,字符串,对象> valueSelector)
{
VAR expandos的=新名单< ExpandoObject>();
的foreach(以元素变种元素)
{
无功的expando =新ExpandoObject();
VAR expandoDictionary =(IDictionary的<字符串对象>)的expando;
的foreach(在密钥VAR键)
{
VAR值= valueSelector(元素,键);
expandoDictionary [关键] =价值;
}
expandos.Add(Expando的);
}
返回expandos.ToReportSource();
}
}
辅助类
公共接口IExportColumn<&的TModel GT;那里的TModel:类
{
串标头{搞定; }
FUNC<的TModel,对象>显示{搞定; }
}
公共类ExportColumn<的TModel,TProperty> :IExportColumn<&的TModel GT;那里的TModel:类
{
私人只读字符串_header;
私人只读防爆pression<&Func键LT;的TModel,TProperty>> _显示; 公共字符串标题{{返回_header; }}
公共Func键<的TModel,对象>显示{{返回模型=> 。_display.Compile()调用(模型); }} ///<总结>
///初始化的在&lt一个新的实例;见CREF =T:System.Object的/>类。
///< /总结>
公共ExportColumn(串头,防爆pression<&Func键LT;的TModel,TProperty>>显示器)
{
_header =头;
_display =显示; ;
} ///<总结>
///返回重新presents当前对象的字符串。
///< /总结>
///<退货和GT;
///是重新presents当前对象的字符串。
///< /回报>
公共重写字符串的ToString()
{
返回的String.Format(头文件:{0},显示:{1},_header,_display);
}
}
用法
VAR报告= probing.Measurements.Export()
.Column(MeasurementResource.Depth,M => m.Depth)
.Column(MeasurementResource.DepthBelowWater,M => m.DepthBelowWater)
.Column(MeasurementResource.ResistancePoint,M => m.ResistancePoint)
.Column(MeasurementResource.FrictionLateral,M => m.FrictionLateral)
.Column(MeasurementResource.FrictionLocal,M => m.FrictionLocal)
.Column(MeasurementResource.FrictionTotal,M => m.FrictionTotal)
.Column(MeasurementResource.Inclination,M => m.Inclination)
.Column(MeasurementResource.PoreWater pressure,M => m.PoreWater pressure)
.Column(MeasurementResource.Speed,M => m.Speed)
.Column(MeasurementResource.CalcAlpha,M => m.CalcAlpha)
.Column(MeasurementResource.CalcGammaDry,M => m.CalcGammaDry)
.Column(MeasurementResource.CalcGammaWet,M => m.CalcGammaWet)
.Column(MeasurementResource.GrainTension,M => m.GrainTension)
.Column(MeasurementResource.Com pressionCoefficient,M => m.Com pressionCoefficient)
.ToReport(空,新ExcelReportWriter()); VAR流=新的MemoryStream(); writer.WriteReport(报告,流); stream.Seek(0,SeekOrigin.Begin); 返回文件(流应用程序/ vnd.ms-EXCEL,的String.Format(@{0} - {1}的.xlsx,probing.Project.ProjectNumber,probing.ProbingNumber));
I have list of Objects upto 150000 and I want to bind to razor view at run time for it export as EXCEL but at the time binding i am getting out of memory exception , is there any workaround to overcome this limitation ?
Export Method :
public void ExportToExcel()
{
string viewPath = "~/Modules/Reports/Views/" + TempData["reportName"] + ".cshtml";
string viewPathCopy = "~/Modules/Reports/Views/" + TempData["reportName"] + "2.cshtml";
string serverViewPath = Server.MapPath(viewPath);
string serverViewPathCopy = Server.MapPath(viewPathCopy);
if (System.IO.File.Exists(serverViewPathCopy))
{
System.IO.File.Delete(serverViewPathCopy);
}
System.IO.File.Copy(serverViewPath, serverViewPathCopy);
string viewContents = System.IO.File.ReadAllText(serverViewPathCopy).Replace("thead", "tr");
viewContents += "<style>body{font-size:8px !important;}table {padding:0 !important,margin:0 !important}</style>";
System.IO.File.WriteAllText(serverViewPathCopy, viewContents);
System.IO.File.WriteAllText(viewPathCopy, TemplateFromFile(viewPathCopy, TempData["reportData"]));
FileInfo file = new FileInfo(viewPathCopy);
if (file.Exists)
{
Response.Clear();
Response.AddHeader("Content-Disposition", "attachment; filename=" + file.Name + ".xls");
Response.AddHeader("Content-Length", file.Length.ToString());
Response.ContentType = "application/octet-stream";
Response.WriteFile(file.FullName);
Response.End();
}
else
{
Response.Write("This file does not exist.");
}
}
Binding Model To View :
public string TemplateFromFile(string file, dynamic model)
{
string template = "";
TextReader textReader = new StreamReader(HelperMethods.GetFullFilePath(file));
try
{
template = textReader.ReadToEnd();
}
finally
{
textReader.Close();
}
return Razor.Parse(template, model);
}
If i have large amounts of data to export, my goto tool is DoddleReport this lets you create a valid xlsx export. With doddle you can even create multi tab excel sheets (see my example on Github).
Some code
Extension for IEnumerable
public static ExportBuilder<TModel> Export<TModel>(this IEnumerable<TModel> models) where TModel : class
{
return ExportBuilder<TModel>.Create(models);
}
The Export Builder
public class ExportBuilder<TModel> where TModel : class
{
private readonly IEnumerable<TModel> _models;
private readonly ICollection<IExportColumn<TModel>> _columns;
/// <summary>
/// Initializes a new instance of the <see cref="T:System.Object"/> class.
/// </summary>
private ExportBuilder(IEnumerable<TModel> models)
{
_models = models;
_columns = new List<IExportColumn<TModel>>();
}
public static ExportBuilder<TModel> Create(IEnumerable<TModel> models)
{
return new ExportBuilder<TModel>(models);
}
public ExportBuilder<TModel> Column<TProperty>(Expression<Func<TModel, TProperty>> display)
{
if (!(display.Body is MemberExpression))
throw new ArgumentException(display + " is not a property!");
var memberInfo = ((MemberExpression)display.Body).Member;
if (!memberInfo.HasAttribute<DisplayNameAttribute>())
throw new ArgumentException(display + " does not have a [Display] attribute");
var displayAttribute = ExtensionsForMemberInfo.GetAttribute<DisplayNameAttribute>(memberInfo);
_columns.Add(new ExportColumn<TModel, TProperty>(displayAttribute.DisplayName, display));
return this;
}
public ExportBuilder<TModel> Column<TProperty>(string header, Expression<Func<TModel, TProperty>> property)
{
_columns.Add(new ExportColumn<TModel, TProperty>(header, property));
return this;
}
public IReportSource ToReportSource()
{
if (_models.Any())
{
return DoddleExporter.ToReportSource(_models.Select(model => _columns.ToDictionary(c => c.Header, c => c.Display(model))));
}
var result = _columns
.ToDictionary(a => a.Header, a => string.Empty);
return DoddleExporter.ToReportSource(new[] { result });
}
public Report ToReport([CanBeNull] IEnumerable<KeyValuePair<string, string>> headers, [CanBeNull] IReportWriter writer = null)
{
headers = headers ?? Enumerable.Empty<KeyValuePair<string, string>>();
var report = new Report(ToReportSource(), writer);
//report.TextFields.Footer = string.Format(@"Aangemaakt op: {0}", DateTime.Now.ToString(DataFormatStrings.Date));
var headersArray = headers as KeyValuePair<string, string>[] ?? headers.ToArray();
if (headersArray.Any())
{
report.TextFields.Header = headersArray.Aggregate(string.Empty,
(currentHeaders, header) => string.Format("{0}{3}{1} : {2}", currentHeaders, header.Key, header.Value, Environment.NewLine));
}
return report;
}
public ReportResult ToExcelReportResult([CanBeNull] IEnumerable<KeyValuePair<string, string>> headers)
{
return new ReportResult(ToReport(headers), new ExcelReportWriter(), "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
}
}
The Exporter
public static class DoddleExporter
{
/// <summary>
/// Converts an enumerable of dictionaries to a report source
/// </summary>
/// <typeparam name="TValue">
/// The type of values in the dictionaries
/// </typeparam>
/// <param name="elements">
/// An enumerable of elements
/// </param>
/// <returns>The report source that was created from the elements</returns>
public static IReportSource ToReportSource<TValue>(IEnumerable<IDictionary<string, TValue>> elements)
{
var elementsArray = elements.ToArray();
if (!elementsArray.Any())
throw new ArgumentException("Can't export empty list of elements");
return ToReportSource(elementsArray, elementsArray.First().Keys.ToArray(),
(element, key) => element.ContainsKey(key) ? element[key] : default(TValue));
}
/// <summary>
/// Converts an enumerable of XElement to a report source
/// </summary>
/// <param name="rootElements">
/// The xml root elements that contain the values
/// </param>
/// <param name="keys">
/// They keys that can be used to fetch values from each root element
/// </param>
/// <returns>The report source that was created from the elements</returns>
public static IReportSource ToReportSource(IEnumerable<XElement> rootElements, string[] keys)
{
return ToReportSource(rootElements, keys, delegate(XElement element, string key)
{
var value = element.Element(XmlConvert.EncodeLocalName(key));
return value != null ? value.Value : null;
});
}
/// <summary>
/// Converts a list of elements to a report source
/// </summary>
/// <param name="elements">
/// An enumerable of elements
/// </param>
/// <param name="keys">
/// They keys with which the values can be fetched from one element
/// </param>
/// <param name="valueSelector">
/// The function with which one value can be fetched given one key and one element
/// </param>
/// <returns>The report source that was created from the elements</returns>
public static IReportSource ToReportSource<T>(IEnumerable<T> elements, string[] keys,
Func<T, string, object> valueSelector)
{
var expandos = new List<ExpandoObject>();
foreach (var element in elements)
{
var expando = new ExpandoObject();
var expandoDictionary = (IDictionary<string, object>) expando;
foreach (var key in keys)
{
var value = valueSelector(element, key);
expandoDictionary[key] = value;
}
expandos.Add(expando);
}
return expandos.ToReportSource();
}
}
Helper classes
public interface IExportColumn<TModel> where TModel : class
{
string Header { get; }
Func<TModel, Object> Display { get; }
}
public class ExportColumn<TModel, TProperty> : IExportColumn<TModel> where TModel : class
{
private readonly string _header;
private readonly Expression<Func<TModel, TProperty>> _display;
public string Header { get { return _header; } }
public Func<TModel, Object> Display { get { return model => _display.Compile().Invoke(model); } }
/// <summary>
/// Initializes a new instance of the <see cref="T:System.Object"/> class.
/// </summary>
public ExportColumn(string header, Expression<Func<TModel, TProperty>> display)
{
_header = header;
_display = display; ;
}
/// <summary>
/// Returns a string that represents the current object.
/// </summary>
/// <returns>
/// A string that represents the current object.
/// </returns>
public override string ToString()
{
return string.Format("Header: {0}, Display: {1}", _header, _display);
}
}
Usage
var report = probing.Measurements.Export()
.Column(MeasurementResource.Depth, m => m.Depth)
.Column(MeasurementResource.DepthBelowWater, m => m.DepthBelowWater)
.Column(MeasurementResource.ResistancePoint, m => m.ResistancePoint)
.Column(MeasurementResource.FrictionLateral, m => m.FrictionLateral)
.Column(MeasurementResource.FrictionLocal, m => m.FrictionLocal)
.Column(MeasurementResource.FrictionTotal, m => m.FrictionTotal)
.Column(MeasurementResource.Inclination, m => m.Inclination)
.Column(MeasurementResource.PoreWaterPressure, m => m.PoreWaterPressure)
.Column(MeasurementResource.Speed, m => m.Speed)
.Column(MeasurementResource.CalcAlpha, m => m.CalcAlpha)
.Column(MeasurementResource.CalcGammaDry, m => m.CalcGammaDry)
.Column(MeasurementResource.CalcGammaWet, m => m.CalcGammaWet)
.Column(MeasurementResource.GrainTension, m => m.GrainTension)
.Column(MeasurementResource.CompressionCoefficient, m => m.CompressionCoefficient)
.ToReport(null, new ExcelReportWriter());
var stream = new MemoryStream();
writer.WriteReport(report, stream);
stream.Seek(0, SeekOrigin.Begin);
return File(stream, "application/vnd.ms-excel", string.Format(@"{0}-{1}.xlsx", probing.Project.ProjectNumber, probing.ProbingNumber));
这篇关于出口的Razor视图到Excel与大数据集的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!