Export a list to Excel using closed xml

Question

User1051638994 posted

Hello I need to export some data to excel using closedXML 

Here is what I have done so far

                   foreach (var id in queryResults)
                    {
                       requestid = id.RequestID;

                    )
                  

                      var    dataToExcel = (from t in _context.Request
                                           join u in _context.Users
                                            on t.UserName equals u.UserName
                                           where t.RequestID == requestid 

                                           let fullName = u.UserFullName

                                           select new { t, fullName }));
                  

and my export to excel

var workbook = new XLWorkbook();

                    IXLWorksheet worksheet =
                    workbook.Worksheets.Add("Requests");
                    worksheet.Cell(1, 1).Value = "ID";
                    worksheet.Cell(1, 2).Value = "Creator";
                    worksheet.Cell(1, 3).Value = "Assign To";
                    worksheet.Cell(1, 4).Value = "Text";
                    for (int index = 1; index <= dataToExcel.ToList().Count; index++)
                    {
                        worksheet.Cell(index + 1, 1).Value =
                        dataToExcel.ToList()[index - 1].t.RequestID;
                        worksheet.Cell(index + 1, 2).Value =
                        dataToExcel.ToList()[index - 1].t.CreatorUserName;
                        worksheet.Cell(index + 1, 3).Value =
                        dataToExcel.ToList()[index - 1].fullName; 
                        worksheet.Cell(index + 1, 4).Value =
                        dataToExcel.ToList()[index - 1].t.RequestContent;


                      }
                    
                    using (var stream = new MemoryStream())
                    {
                        workbook.SaveAs(stream);
                       
                        var content = stream.ToArray();
                        return File(content, contentType, fileName);
                    }

It saves the excel file but writes only one record. This is correct since my foreach I guess is not correct. How can I export all the data?

Thank you

Answer 1

User1312693872 posted

Hi,mspace

Your code to generate the data to Email has nothing wrong in my side, I think the error occurs in the requestid , in your offered code, the

requestid will just have one value, not all in queryResults.

I assume you want to select all the rows which user.username=request.username from queryResults, you can achieve it like this demo:

            var queryResults = _context.Request.ToList();  //select rows to be queryResult from specific table?       
            var dataToExcel = (from t in queryResults
                               join u in _context.Users
                                on t.UserName equals u.UserName
                               let fullName = u.UserFullName

                               select new { t, fullName });

//...following just copied yours

Result:

Best Regards,

Jerry Cai