c# - Asp.net MVC 导出表格到 Excel |外键显示

Question

我想从数据库中导出一份学生名单。

我已成功将过滤后的数据导出到 excel 文件。

看起来像这样:

问题是我想显示 id 引用的表中的信息，而不是 ID 包含 id 的字段。

例如，有一个名为“Group”的表，它包含“GroupID”、“Name”和“Number”

CREATE TABLE [dbo].[Group] (
    [GroupID]      INT          IDENTITY (1, 1) NOT NULL,
    [Name] VARCHAR (10) NOT NULL,
    [Number]        VARCHAR (10) NOT NULL,
    PRIMARY KEY CLUSTERED ([GrupaID] ASC)
);

所以我想在 excel 文件中“GroupID”列中显示“Group”表中的数据，而不是“7”的 GroupID 来显示 Group 表中的名称+编号之类的内容。

这里是一些项目信息:

-我要保存在excel文件中的学生表

CREATE TABLE [dbo].[Student] (
    [Nume]          VARCHAR (20) NOT NULL,
    [Prenume]       TEXT         NOT NULL,
    [Nota]          INT          NOT NULL,
    [GrupaID]       INT          NOT NULL,
    [ConducatorID]  INT          NOT NULL,
    [TPracticaID]   INT          NOT NULL,
    [PerioadaID]    INT          NOT NULL,
    [StudentID]     INT          IDENTITY (1, 1) NOT NULL,
    [LocPracticaID] INT          NOT NULL,
    PRIMARY KEY CLUSTERED ([StudentID] ASC),
    CONSTRAINT [FK_Student_ToTable_1] FOREIGN KEY ([ConducatorID]) REFERENCES [dbo].[Conducator] ([ConducatorID]),
    CONSTRAINT [FK_Student_ToTable_3] FOREIGN KEY ([PerioadaID]) REFERENCES [dbo].[Perioada] ([PerioadaID]),
    CONSTRAINT [FK_Student_ToTable_2] FOREIGN KEY ([TPracticaID]) REFERENCES [dbo].[Tpractica] ([TpracticaID]),
    CONSTRAINT [FK_Student_ToTable_4] FOREIGN KEY ([LocPracticaID]) REFERENCES [dbo].[LocPractica] ([LocPracticaID]),
    CONSTRAINT [FK_Student_ToTable] FOREIGN KEY ([GrupaID]) REFERENCES [dbo].[Grupa] ([GrupaID])
);

我为导出函数存储了 session 信息

     if (!string.IsNullOrEmpty(grupString))
                {
    //Filter
                    student = student.Where(s => (s.Grupa.Specialitate + s.GrupaID).Contains(grupString));

                }
//Session storing student        
                Session["student"] = student.ToList<Student>(); ;
                return View(student);

导出函数如下:受 Hadi Hassan 回复启发的修改代码:

public void ExportToExcel(string grupString)
       {
            var student = (List<Student>)Session["student"];



            var gv = new GridView();

           gv.DataSource =(from e in db.Student
                           select new 
                            {
                                Nume = e.Nume,
                                Prenume = e.Prenume,
                                Nota = e.Nota,
                                Grupa = e.Grupa.Specialitate+" "+e.Grupa.Numar,
/* The problem with cell space happens here ->**Conducator = e.Conducator.Nume+" "e.Conducator.Prenume ** when i try to make this cell made from adition of 2 string like i did before 
here **Grupa = e.Grupa.Specialitate+" "+e.Grupa.Numar,** -here the string are short and fit in the cell but when i try to do the same here 
 **Conducator = e.Conducator.Nume+" "e.Conducator.Prenume **, the strings are a little longer and throws and exception
 */
                                Conducator = e.Conducator.Nume, 
                                Tpractica = e.Tpractica.Tip,
                                Perioada =e.Perioada.An,
                                LocPractica= e.LocPractica.Denumire,
                               }
                               ).ToList(); 


            gv.DataBind();


            Response.ClearContent();
            Response.Buffer = true;
            Response.AddHeader("content-disposition", "attachment; filename=DemoExcel.xls");
            Response.ContentType = "application/ms-excel";

            Response.Charset = "";
            StringWriter objStringWriter = new StringWriter();
            HtmlTextWriter objHtmlTextWriter = new HtmlTextWriter(objStringWriter);

            gv.RenderControl(objHtmlTextWriter);

            Response.Output.Write(objStringWriter.ToString());
            Response.Flush();
            Response.End();       
        }

现在我可以显示数据了，但它显示了所有数据，但它不再被过滤，如果行中单元格中的文本长于列标题，则它来自于它抛出异常。

修改后的Excel文件是这样的

1)如何只导出过滤后的数据？2)如何扩展单元格以便显示更长的文本？

Answer 1

您可以执行以下操作

public void ExportToExcel()
        {
            var student = (List<Student>)Session["student"];
            var groups  = **YourDBContextGoesHere**.Groups.ToList();

            // extract new result from the student dbset and join it 
            // with groups dbset to get the name
            // Note: only one trip to the db to get the group names since
            //       the student already stored in the session
            var result  = from s in student
                          join g in groups on s.GroupID = g.GroupID
                          select new { // you can define a ModelView with whatever properties you want inside, but I will assume that you want the following
                             Nume = s.Name,
                             Prenume = s.Prenume,
                             Nota= s.Nota,
                             GrupaID = g.Name, // here you put the name of the group
                             ConducatorID =s.ConducatorID,
                             TPracticaID = s.TPracticaID,
                             PerioadaID = s.PerioadaID ,
                             StudentID = StudentID,
                          };
            var gv = new GridView();
            gv.DataSource =result.ToList(); // here you put the result
            gv.DataBind(); 

            Response.ClearContent();
            Response.Buffer = true;
            Response.AddHeader("content-disposition", "attachment; filename=DemoExcel.xls");
            Response.ContentType = "application/ms-excel";
            Response.Charset = "";
            StringWriter objStringWriter = new StringWriter();
            HtmlTextWriter objHtmlTextWriter = new HtmlTextWriter(objStringWriter);
            gv.RenderControl(objHtmlTextWriter);
            Response.Output.Write(objStringWriter.ToString());
            Response.Flush();
            Response.End();
        } 

希望对你有帮助