c# - Asp.net MVC Export Table to Excel | Foreign Key Display -

i want export excel list of students database.

i exported filtered data excel file.

it looks this:

the thing want display information tables id's refer instead of fields id contains id's.

for example, there table called "group" contains "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) ); 

so want in excel file in column "groupid" display data "group" table ,so instead of groupid "7" show name+number group table.

here project info :

-the student table want save in excel file

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]) ); 

i stored session info export function

     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); 

here export function: modified code inspired hadi hassan's reply :

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, /* problem cell space happens here ->**conducator = e.conducator.nume+" "e.conducator.prenume ** when try make cell made adition of 2 string did before  here **grupa = e.grupa.specialitate+" "+e.grupa.numar,** -here string short , fit in cell when try same here   **conducator = e.conducator.nume+" "e.conducator.prenume **, strings little longer , throws , 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();                } 

now able show data shows data not filtered anymore , if text in cell row longer title of column throws , exception.

after modification excel file looks

1)how export filtered data ? 2)how expand cells can display longer text ?

you can following

public void exporttoexcel()         {             var student = (list<student>)session["student"];             var groups  = **yourdbcontextgoeshere**.groups.tolist();              // extract new result student dbset , join              // groups dbset name             // note: 1 trip db group names since             //       student stored in session             var result  = s in student                           join g in groups on s.groupid = g.groupid                           select new { // can define modelview whatever properties want inside, assume want following                              nume = s.name,                              prenume = s.prenume,                              nota= s.nota,                              grupaid = g.name, // here put name of group                              conducatorid =s.conducatorid,                              tpracticaid = s.tpracticaid,                              perioadaid = s.perioadaid ,                              studentid = studentid,                           };             var gv = new gridview();             gv.datasource =result.tolist(); // here put 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();         }  

hope you