locked
Export a list to Excel using closed xml RRS feed

  • 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

    Thursday, March 11, 2021 6:56 AM

All replies

  • 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

    Friday, March 12, 2021 6:12 AM