Gathering Data For ClosedXML Using foreach Loop

Question

My C# ASP.NET MVC5 application has a model named Ticket. I want to export all of the tickets in my database to an excel spreadsheet using ClosedXML. I have an Action just for exporting and downloading the data to the spreadsheet. Everything is working great, except when I open my newly downloaded spreadsheet I only have one ticket in there. I think I don't have something just right with my foreach loop. What am I doing wrong?

My action that is called when the user wants to download tickets:

public ActionResult DownloadTickets()
{
    string date = "";
    string title = "";
    string createdBy = "";
    bool isCallBack = true;

    using (var wb = new XLWorkbook())
    {
        var ticket = _context.Tickets.ToList();
        var dateForXcellSheet = DateTime.Now;
        var worksheet = wb.Worksheets.Add("Sample Sheet");

        foreach (var i in ticket)
        {
            date = i.DateCreated.ToString();
            title = i.Title;
            createdBy = i.CreatedBy;
            isCallBack = i.IsCallBack;
        }

        worksheet.Cell("A1").Value = date;
        worksheet.Cell("B1").Value = title;
        worksheet.Cell("C1").Value = createdBy;
        worksheet.Cell("D1").Value = isCallBack;

        // Add ClosedXML.Extensions in your using declarations

        return wb.Deliver("tickets-" + dateForXcellSheet + ".xlsx");
    }
}

Answer 1

Instead of using your own loop, a much better way is to use IXLCell.InsertData(data)

Example:

public void Create()
{
  var wb = new XLWorkbook();
  var ws = wb.Worksheets.Add("Inserting Data");

  // From a list of strings
  var listOfStrings = new List<String>();
  listOfStrings.Add("House");
  listOfStrings.Add("Car");
  ws.Cell(1, 1).Value = "From Strings";
  ws.Cell(1, 1).AsRange().AddToNamed("Titles");
  var rangeWithStrings = ws.Cell(2, 1).InsertData(listOfStrings);

  // From a list of arrays
  var listOfArr = new List<Int32[]>();
  listOfArr.Add(new Int32[] { 1, 2, 3 });
  listOfArr.Add(new Int32[] { 1 });
  listOfArr.Add(new Int32[] { 1, 2, 3, 4, 5, 6 });
  ws.Cell(1, 3).Value = "From Arrays";
  ws.Range(1, 3, 1, 8).Merge().AddToNamed("Titles");
  var rangeWithArrays = ws.Cell(2, 3).InsertData(listOfArr);

  // From a DataTable
  var dataTable = GetTable();
  ws.Cell(6, 1).Value = "From DataTable";
  ws.Range(6, 1, 6, 4).Merge().AddToNamed("Titles");
  var rangeWithData = ws.Cell(7, 1).InsertData(dataTable.AsEnumerable());

  // From a query
  var list = new List<Person>();
  list.Add(new Person() { Name = "John", Age = 30, House = "On Elm St."   });
  list.Add(new Person() { Name = "Mary", Age = 15, House = "On Main St."  });
  list.Add(new Person() { Name = "Luis", Age = 21, House = "On 23rd St."  });
  list.Add(new Person() { Name = "Henry", Age = 45, House = "On 5th Ave." });

  var people = from p in list
    where p.Age >= 21
    select new { p.Name, p.House, p.Age };

  ws.Cell(6, 6).Value = "From Query";
  ws.Range(6, 6, 6, 8).Merge().AddToNamed("Titles");
  var rangeWithPeople = ws.Cell(7, 6).InsertData(people.AsEnumerable());

  // Prepare the style for the titles
  var titlesStyle = wb.Style;
  titlesStyle.Font.Bold = true;
  titlesStyle.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
  titlesStyle.Fill.BackgroundColor = XLColor.Cyan;

  // Format all titles in one shot
  wb.NamedRanges.NamedRange("Titles").Ranges.Style = titlesStyle;

  ws.Columns().AdjustToContents();

  wb.SaveAs("InsertingData.xlsx");
}

class Person
{
  public String House { get; set; }
  public String Name { get; set; }
  public Int32 Age { get; set; }
}

private DataTable GetTable()
{
  DataTable table = new DataTable();
  table.Columns.Add("Dosage", typeof(int));
  table.Columns.Add("Drug", typeof(string));
  table.Columns.Add("Patient", typeof(string));
  table.Columns.Add("Date", typeof(DateTime));

  table.Rows.Add(25, "Indocin", "David", DateTime.Now);
  table.Rows.Add(50, "Enebrel", "Sam", DateTime.Now);
  table.Rows.Add(10, "Hydralazine", "Christoff", DateTime.Now);
  table.Rows.Add(21, "Combivent", "Janet", DateTime.Now);
  table.Rows.Add(100, "Dilantin", "Melanie", DateTime.Now);
  return table;
}

Refer to https://github.com/ClosedXML/ClosedXML/wiki/Inserting-Data

Answer 2

The code to add the data to the worksheet cells (and maybe to add the ClosedXML extensions) also needs to be inside the foreach loop. As it stands, you are looping through all the tickets, but only the property values from the last ticket are being added to the spreadsheet.

Edit: Seeing as the OP asked in a comment on the accepted answer... ...it is possible to achieve the same result using the foreach loop:

var rowIndex = 1;

foreach (var ticket in ticketList)
{
    date = ticket.DateCreated.ToString();
    title = ticket.Title;
    createdBy = ticket.CreatedBy;
    isCallBack = ticket.IsCallBack;

    worksheet.Cell("A" + rowIndex).Value = date;
    worksheet.Cell("B" + rowIndex).Value = title;
    worksheet.Cell("C" + rowIndex).Value = createdBy;
    worksheet.Cell("D" + rowIndex).Value = isCallBack;

    rowIndex++;
}

Answer 3

Change your code like below. Use for loop and use proper index to assign values to cell in spreadsheet.

 using (var wb = new XLWorkbook())
        {
            var ticketList = _context.Tickets.ToList();
            var dateForXcellSheet = DateTime.Now;
            var worksheet = wb.Worksheets.Add("Sample Sheet");

            for (int i= 0; i < ticketList.Count(); i++)
            {
                date = ticketList[i].DateCreated.ToString();
                title = ticketList[i].Title;
                createdBy = ticketList[i].CreatedBy;
                isCallBack = ticketList[i].IsCallBack;

            int index =  i + 1;
            worksheet.Cell("A" + index).Value = date;
            worksheet.Cell("B" + index).Value = title;
            worksheet.Cell("C" + index).Value = createdBy;
            worksheet.Cell("D" + index).Value = isCallBack;
            }



            // Add ClosedXML.Extensions in your using declarations

            return wb.Deliver("tickets-" + dateForXcellSheet + ".xlsx");
        }