c# - 当我尝试将数据从 db 提取到 ASP.NET MVC 和 C# 中的 excel 文件中时，出现匿名类型错误

Question

我正在使用 Entity Framework 和 C# & ASP.NET MVC 从表中提取数据并将其上传到 Excel 文件中。当我使用第一个或默认值时，上传只会重复上传一条记录，所以我更改了我的代码以对所需的列进行选择，但我收到一条错误消息，错误发生在 element(0) 处，我认为这是因为它是一个字符串方法。

LINQ to Entities 无法识别方法 '<>f__AnonymousType5 7[System.Int32,System.String,System.String,System.String,System.String,System.Nullable1[System.DateTime],System.Nullable 1[System.Int32]] ElementAt[<>f__AnonymousType57](System.Linq.IQueryable 1[<>f__AnonymousType57[System.Int32,System.String,System.String,System.String,System. String,System.Nullable 1[System.DateTime],System.Nullable1[System.Int32]]], Int32)' 方法，并且该方法不能翻译成存储表达式。

我的控制器

namespace App.Web.Controllers
{
    public class ExportToExcelController : Controller
    {
        private MarketingDBEntitiesModel db = new MarketingDBEntitiesModel();

        public ActionResult ExportToExcel(int UploadId)
        {
                  select c);

            UploadId = 4;
            var dataToExport = db.marketingdbclients_invalidEmails
               .Join(db.marketingdbclients_dataTable
               , od => od.ClientId
               , o => o.ClientId
               , (o, od) => new {
                   o.ClientId,
                   o.Email1,
                   o.Email2,
                   o.Email3,
                   o.Email4,
                   o.DateStamp, 
                   od.UploadId
               }).Where(a=> a.UploadId == UploadId)  
                .Select(s => s);

            ExcelPackage excel = new ExcelPackage();
            var workSheet = excel.Workbook.Worksheets.Add("Sheet1");
            workSheet.TabColor = System.Drawing.Color.Black;
            workSheet.DefaultRowHeight = 12;
            //Header of table  
            //  
            workSheet.Row(1).Height = 20;
            workSheet.Row(1).Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
            workSheet.Row(1).Style.Font.Bold = true;
            workSheet.Cells[1, 1].Value = dataToExport.ElementAt(0);
            workSheet.Cells[1, 2].Value = dataToExport.ElementAt(1);
            workSheet.Cells[1, 3].Value = dataToExport.ElementAt(2);
            workSheet.Cells[1, 4].Value = dataToExport.ElementAt(3);
            workSheet.Cells[1, 5].Value = dataToExport.ElementAt(4);
            workSheet.Cells[1, 6].Value = dataToExport.ElementAt(5);

            //workSheet.Cells[1, 2].Value = dataToExport.Email1;
            //workSheet.Cells[1, 3].Value = dataToExport.Email2;
            //workSheet.Cells[1, 4].Value = dataToExport.Email3;
            //workSheet.Cells[1, 5].Value = dataToExport.Email4;
            //workSheet.Cells[1, 6].Value = dataToExport.DateStamp;

            // Body of table  
            int recordIndex = 2;

            foreach (var exportData in dataToExport.ToString())
            {
                workSheet.Cells[recordIndex, 1].Value = (recordIndex - 1).ToString();
                workSheet.Cells[recordIndex, 2].Value = dataToExport.ElementAt(0);
                workSheet.Cells[recordIndex, 3].Value = dataToExport.ElementAt(1);
                workSheet.Cells[recordIndex, 4].Value = dataToExport.ElementAt(2);
                workSheet.Cells[recordIndex, 5].Value = dataToExport.ElementAt(3);
                workSheet.Cells[recordIndex, 6].Value = dataToExport.ElementAt(4);
                workSheet.Cells[recordIndex, 7].Value = dataToExport.ElementAt(5);
                workSheet.Cells[recordIndex, 8].Value = dataToExport.ElementAt(6);

                //workSheet.Cells[recordIndex, 2].Value = dataToExport.ClientId;
                //workSheet.Cells[recordIndex, 3].Value = dataToExport.Email1;
                //workSheet.Cells[recordIndex, 4].Value = dataToExport.Email2;
                //workSheet.Cells[recordIndex, 5].Value = dataToExport.Email3;
                //workSheet.Cells[recordIndex, 6].Value = dataToExport.Email4;
                //workSheet.Cells[recordIndex, 7].Value = dataToExport.DateStamp;
                //workSheet.Cells[recordIndex, 8].Value = dataToExport.ClientId;
                recordIndex++;
            }

            workSheet.Column(1).AutoFit();
            workSheet.Column(2).AutoFit();
            workSheet.Column(3).AutoFit();
            workSheet.Column(4).AutoFit();
            workSheet.Column(5).AutoFit();
            workSheet.Column(6).AutoFit();
            workSheet.Column(7).AutoFit();
            string excelName = "invalidEmailRecord";

            using (var memoryStream = new MemoryStream())
            {
                Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
                Response.AddHeader("content-disposition", "attachment; filename=" + excelName + ".xlsx");
                excel.SaveAs(memoryStream);
                memoryStream.WriteTo(Response.OutputStream);
                Response.Flush();
                Response.End();

                return View();
            }
        }
    }
}

标签： c#asp.net-mvclambdaepplus

Answer 1

你在做for一个dataToExport.ToString()吗？在 a 上调​​用枚举（如 a for）string只会给你一个chars 数组。它可能看起来像（逗号分隔的输出）：

S, y, s, t, e, m, ., L, i, n, q, ., E, n, u, m, e, r, a, b, l, e, +, W, h, e, r, e, S, e, l, e, c, t, E, n, u, m, e, r, a, b, l, e, I, t, e, r, a, t, o, r, `, 2, [, <, >, f, _, _, A, n, o, n, y, m, o, u, s, T, y, p, e, 0, `, 3, [, S, y, s, t, e, m, ., I, n, t, 3, 2, ,, S, y, s, t, e, m, ., S, t, r, i, n, g, ,, S, y, s, t, e, m, ., S, t, r, i, n, g, ], ,, <, >, f, _, _, A, n, o, n, y, m, o, u, s, T, y, p, e, 0, `, 3, [, S, y, s, t, e, m, ., I, n, t, 3, 2, ,, S, y, s, t, e, m, ., S, t, r, i, n, g, ,, S, y, s, t, e, m, ., S, t, r, i, n, g, ], ]

我认为你所追求的是：

foreach (var exportData in dataToExport)
{
    workSheet.Cells[recordIndex, 2].Value = exportData.ClientId;
    workSheet.Cells[recordIndex, 3].Value = exportData.Email1;
    workSheet.Cells[recordIndex, 4].Value = exportData.Email2;
    workSheet.Cells[recordIndex, 5].Value = exportData.Email3;
    workSheet.Cells[recordIndex, 6].Value = exportData.Email4;
    workSheet.Cells[recordIndex, 7].Value = exportData.DateStamp;
    workSheet.Cells[recordIndex, 8].Value = exportData.ClientId;

    //Move to the next row
    recordIndex++
}

这将导致对 LINQ 表达式求值并为您提供一组匿名对象。