出口的Razor视图到Excel与大数据集

出口的Razor视图到Excel与大数据集

本文介绍了出口的Razor视图到Excel与大数据集的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我有高达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 +的.xl​​s);
            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< D​​isplayNameAttribute>(的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与大数据集的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

08-15 19:14