导出到Excel - LINQ - 包括在.ToList（）ForeignKey的值？ [英] Export to Excel - LINQ - Include ForeignKey values in .ToList()?

问题描述

我试图通过的 EPPlus 库。在我的导出查看我已经填补了 MultiSelectList 每个我的 INV_Assets 模型属性。然后，这些获得通过进入我的 ExportController 来指定哪些模型领域是出口。

我已经得到了标题（通过 MultiSelectList ）加载到Excel的ROW1从我 INV_Assets 模型导出通过EPPlus到excel LoadFromCollection（）如下（举例）：

code

  [HttpPost]
    公众的ActionResult ExportUsingEPPlus（ExportAssetsViewModel模型）
    {
        ExcelPackage包=新ExcelPackage（）;
        变种WS = package.Workbook.Worksheets.Add（TestExport）;        VAR exportFields =新的List＆LT;串GT;（）;
        的foreach（在model.SelectedFields VAR selectedField）
        {
            //添加所选字段[exportFields]列表＆LT;串GT;
            exportFields.Add（model.ListOfExportFields.First（S = GT; s.Key == selectedField）.value的）;
        }       {
            ws.Cells [1，I + 1] .value的= exportFields [I]的ToString（）;
        }        变种membersToShow = typeof运算（INV_Assets）.GetMembers（）如（p值=＆GT; exportFields.Contains（p.Name））。ToArray的（）;        。ws.Cells [A2] LoadFromCollection（_db.INV_Assets.ToList（），假的，TableStyles.None，BindingFlags.Default，membersToShow）;        VAR的MemoryStream =新的MemoryStream（）;
        package.SaveAs（MemoryStream的）;        字符串文件名=导出-InventoryAssets-+ DateTime.Now +的.xl​​sx
        字符串的contentType =应用程序/ vnd.openxmlformats-officedocument.s preadsheetml.sheet        memoryStream.Position = 0;
        返回文件（MemoryStream的，contentType中，文件名）;
    }
 

输出

唯一的问题我仍然有我的模型中的 ForeignKey的字段。例如， [状态] 在我的 INV_Assets 模型中定义为：

  [必需]
    公众诠释STATUS_ID {搞定;组; }
    [ForeignKey的（STATUS_ID）]
    公共虚拟INV_Statuses状态{搞定;组; }
 

在目前出口执行，而不是让说，虽然，可用 / 回收的在Excel [状态] 列包含InventoryTracker.Models.INV_Statuses在每个细胞中的每个记录导出。

任何人都可以提供深入了解如何得到的不仅直接 INV_Assets 田出口，但对 ForeignKey的值型号，位置，状态，供应商，制造商和型号也到Excel？

我做了一些阅读和思考的包括（）法会的工作，但没有运气。 （例） [类型] 仍显示 InventoryTracker.Models.INV_Types 使用以下$ C $的所有记录细胞C：

<$p$p><$c$c>ws.Cells[\"A2\"].LoadFromCollection(_db.INV_Assets.Include(\"Model\").Include(\"Manufacturer\").Include(\"Type\").Include(\"Status\").Include(\"Vendor\").ToList(),假的，TableStyles.None，BindingFlags.Default，membersToShow）;


---

修改

我添加使用System.Data.Entity的我的 ExportController 这让下面的编译：

  ws.Cells [A2] LoadFromCollection（_db.INV_Assets.Include（M = GT; m.Model.model_description）.INCLUDE（M = GT; m.Manufacturer。 manufacturer_description）.INCLUDE（M = GT; m.Type.type_description）.INCLUDE（M = GT; m.Status.status_description）.INCLUDE（M = GT; m.Vendor.vendor_name）.ToList（），假的，TableStyles。无，BindingFlags.Default，membersToShow）;
 

我认为这会做的伎俩，但在执行过程中我收到：

  InvalidOperationException异常是由用户code未处理。'System.InvalidOperationException'类型的异常出现在EntityFramework.SqlServer.dll但在用户code没有处理。其他信息：指定的include路径无效。该的EntityTypeInventoryTracker.DAL.INV_Models未声明名为'model_description一个naviation属性。
 

我不明白这是为什么以这种方式标记。 INV_Assets 有关于 INV_Models 这样定义：

 公众诠释MODEL_ID {搞定;组; }
    [ForeignKey的（MODEL_ID）]
    公共虚拟INV_Models型号{搞定;组; }
 

和定义为INV_Models：

 公共类INV_Models
{
    公众诠释标识{搞定;组; }    [必需（的ErrorMessage =请输入型号说明。）
    公共字符串model_description {搞定;组; }    [需要]
    [DisplayFormat（DataFormatString ={0：MM / DD / YYYY}）]
    公众的DateTime CREATED_DATE {搞定;组; }    [需要]
    公共字符串CREATED_BY {搞定;组; }    [DisplayFormat（DataFormatString ={0：MM / DD / YYYY}）]
    公众的DateTime？ MODIFIED_DATE {搞定;组; }    公共字符串modified_by {搞定;组; }
}
 

任何人能发现我在做什么的包括（）？

不正确

---

EDIT2

感谢埃里克Ĵ的建议，这个问题就解决了​​！

：添加我的每一个期望模型的的ToString（）覆盖，以确保通过价值，而不是仅仅对象类型来了

 公共类INV_Models
{    公共重写字符串的ToString（）
    {
        返回this.model_description;
    }
}公共类INV_Manufacturers
{
    公共重写字符串的ToString（）
    {
        返回this.manufacturer_description;
    }
}公共类INV_Locations
{
    公共重写字符串的ToString（）
    {
        返回this.location_dept +| + this.location_room;
    }
}公共类INV_Vendors
{
    公共重写字符串的ToString（）
    {
        返回this.vendor_name;
    }
}公共类INV_Types
{
    公共重写字符串的ToString（）
    {
        返回this.type_description;
    }
}公共类INV_Statuses
{
    公共重写字符串的ToString（）
    {
        返回this.status_description;
    }
}
 

ExportController

  [HttpPost]
    公众的ActionResult ExportUsingEPPlus（ExportAssetsViewModel模型）
    {
        ExcelPackage包=新ExcelPackage（）;
        变种WS = package.Workbook.Worksheets.Add（TestExport）;        VAR exportFields =新的List＆LT;串GT;（）;
        的foreach（在model.SelectedFields VAR selectedField）
        {
            //添加所选字段[exportFields]列表＆LT;串GT;
            exportFields.Add（model.ListOfExportFields.First（S = GT; s.Key == selectedField）.value的）;
        }        //循环到插入列标题到Excel的第1行
        的for（int i = 0; I＆LT; exportFields.Count（）;我++）
        {
            ws.Cells [1，I + 1] .value的= exportFields [I]的ToString（）;
        }        变种membersToShow = typeof运算（INV_Assets）.GetMembers（）如（p值=＆GT; exportFields.Contains（p.Name））。ToArray的（）;        。ws.Cells [A2] LoadFromCollection（_db.INV_Assets.ToList（），假的，TableStyles.None，BindingFlags.Default，membersToShow）;        变种membersToShow = typeof运算（INV_Assets）.GetMembers（）如（p值=＆GT; exportFields.Contains（p.Name））。ToArray的（）;        VAR的MemoryStream =新的MemoryStream（）;
        package.SaveAs（MemoryStream的）;        字符串文件名=导出-InventoryAssets-+ DateTime.Now +的.xl​​sx
        字符串的contentType =应用程序/ vnd.openxmlformats-officedocument.s preadsheetml.sheet        memoryStream.Position = 0;
        返回文件（MemoryStream的，contentType中，文件名）;
    }
 

和输出Excel文件：

解决方案

  

在目前的出口虽然执行，而不是让说，在为每个记录每一个细胞/可循环使用，在Excel中[状态]列中包含InventoryTracker.Models.INV_Statuses出口。

这就是你得到什么，如果Sy​​stem.Object.ToString（）调用INV_Statuses的一个实例。

试着重写的ToString（）的那个类：

 公共类INV_Statuses
{
    公共重写字符串的ToString（）
    {
        // TODO：返回无论你想有出现在Excel中
    }    这里的类//休息
}
 

I am attempting to add Excel Export functionality to my MVC5/Code-First Entity Framework application via the EPPlus library. On my Export View I have filled a MultiSelectList with each of my INV_Assets model properties. These then get passed into my ExportController to designate which fields of the model are to be exported.

I've gotten the Headers (via the MultiSelectList) to load into Row1 of Excel and the data from my INV_Assets model to export to excel via EPPlus LoadFromCollection() as below (example):

Code:

    [HttpPost]
    public ActionResult ExportUsingEPPlus(ExportAssetsViewModel model)
    {
        ExcelPackage package = new ExcelPackage();
        var ws = package.Workbook.Worksheets.Add("TestExport");

        var exportFields = new List<string>();
        foreach (var selectedField in model.SelectedFields)
        {
            // Adds selected fields to [exportFields] List<string>
            exportFields.Add(model.ListOfExportFields.First(s => s.Key == selectedField).Value);
        }

       {
            ws.Cells[1, i + 1].Value = exportFields[i].ToString();
        }

        var membersToShow = typeof(INV_Assets).GetMembers().Where(p => exportFields.Contains(p.Name)).ToArray();

        ws.Cells["A2"].LoadFromCollection(_db.INV_Assets.ToList(), false, TableStyles.None, BindingFlags.Default, membersToShow);

        var memoryStream = new MemoryStream();
        package.SaveAs(memoryStream);

        string fileName = "Exported-InventoryAssets-" + DateTime.Now + ".xlsx";
        string contentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";

        memoryStream.Position = 0;
        return File(memoryStream, contentType, fileName);
    }

Output:

The only issue I still have is the ForeignKey fields within my model. For example, [Status] is defined in my INV_Assets Model as:

    [Required]
    public int Status_Id { get; set; }
    [ForeignKey("Status_Id")]
    public virtual INV_Statuses Status { get; set; }

When the current export executes though, instead of getting say AVAILABLE/RECYCLED, the [Status] column in excel contains "InventoryTracker.Models.INV_Statuses" in every cell for each record exported.

Can anyone offer insight into how to get not only the direct INV_Assets fields exported, but the ForeignKey values for Model, Location, Status, Vendor, Manufacturer, and Type also into Excel?

I did some reading and thought the Include() method would work, but no luck. (Ex.) [Type] still displays InventoryTracker.Models.INV_Types in all record cells using the following code:

ws.Cells["A2"].LoadFromCollection(_db.INV_Assets.Include("Model").Include("Manufacturer").Include("Type").Include("Status").Include("Vendor").ToList(), false, TableStyles.None, BindingFlags.Default, membersToShow);

---

EDIT:

I added using System.Data.Entity to my ExportController which allowed the following to compile:

ws.Cells["A2"].LoadFromCollection(_db.INV_Assets.Include(m => m.Model.model_description).Include(m => m.Manufacturer.manufacturer_description).Include(m => m.Type.type_description).Include(m => m.Status.status_description).Include(m => m.Vendor.vendor_name).ToList(), false, TableStyles.None, BindingFlags.Default, membersToShow);

I thought this would do the trick, but during execution I receive:

InvalidOperationException was unhandled by user code.

An exception of type 'System.InvalidOPerationException' occurred in EntityFramework.SqlServer.dll but was not handled in user code.

Additional Information: A specified Include path is not valid. The EntityType 'InventoryTracker.DAL.INV_Models' does not declare a naviation property with the name 'model_description'.

I don't understand why this is flagging in this manner. INV_Assets has the relation to INV_Models defined thus:

    public int Model_Id { get; set; }
    [ForeignKey("Model_Id")]
    public virtual INV_Models Model { get; set; }

And INV_Models defined as:

public class INV_Models
{
    public int Id { get; set; }

    [Required(ErrorMessage = "Please enter a Model Description.")]
    public string model_description { get; set; }

    [Required]
    [DisplayFormat(DataFormatString = "{0:MM/dd/yyyy}")]
    public DateTime created_date { get; set; }

    [Required]
    public string created_by { get; set; }

    [DisplayFormat(DataFormatString = "{0:MM/dd/yyyy}")]
    public DateTime? modified_date { get; set; }

    public string modified_by { get; set; }
}

Can anyone spot what I'm doing incorrect on the Include()?

---

EDIT2:

Thanks to Eric J's suggestion, the issue is solved! Added a ToString() override on each of my desired Models to ensure the value came through instead of just the object type:

public class INV_Models
{

    public override string ToString()
    {
        return this.model_description;
    }
}

public class INV_Manufacturers
{
    public override string ToString()
    {
        return this.manufacturer_description;
    }
}

public class INV_Locations
{
    public override string ToString()
    {
        return this.location_dept + "|" + this.location_room;
    }
}

public class INV_Vendors
{
    public override string ToString()
    {
        return this.vendor_name;
    }
}

public class INV_Types
{
    public override string ToString()
    {
        return this.type_description;
    }
}

public class INV_Statuses
{
    public override string ToString()
    {
        return this.status_description;
    }
}

ExportController:

    [HttpPost]
    public ActionResult ExportUsingEPPlus(ExportAssetsViewModel model)
    {
        ExcelPackage package = new ExcelPackage();
        var ws = package.Workbook.Worksheets.Add("TestExport");

        var exportFields = new List<string>();
        foreach (var selectedField in model.SelectedFields)
        {
            // Adds selected fields to [exportFields] List<string>
            exportFields.Add(model.ListOfExportFields.First(s => s.Key == selectedField).Value);
        }

        // Loops to insert column headings into Row 1 of Excel
        for (int i = 0; i < exportFields.Count(); i++)
        {
            ws.Cells[1, i + 1].Value = exportFields[i].ToString();
        }

        var membersToShow = typeof(INV_Assets).GetMembers().Where(p => exportFields.Contains(p.Name)).ToArray();

        ws.Cells["A2"].LoadFromCollection(_db.INV_Assets.ToList(), false, TableStyles.None, BindingFlags.Default, membersToShow);



        var membersToShow = typeof(INV_Assets).GetMembers().Where(p => exportFields.Contains(p.Name)).ToArray();

        var memoryStream = new MemoryStream();
        package.SaveAs(memoryStream);

        string fileName = "Exported-InventoryAssets-" + DateTime.Now + ".xlsx";
        string contentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";

        memoryStream.Position = 0;
        return File(memoryStream, contentType, fileName);
    }

And the output Excel file:

解决方案

When the current export executes though, instead of getting say AVAILABLE/RECYCLED, the [Status] column in excel contains "InventoryTracker.Models.INV_Statuses" in every cell for each record exported.

That is what you get if System.Object.ToString() is called on an instance of INV_Statuses.

Try overriding ToString() in that class:

public class INV_Statuses
{
    public override string ToString()
    {
        // TODO: Return whatever you would like to have appear in Excel
    }

    // Rest of the class here
}

这篇关于导出到Excel - LINQ - 包括在.ToList（）ForeignKey的值？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！