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

swatsonpicken · Answer 1 · 2017-07-18T12:25:53.977

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++;
}

edited Jul 18 '17 at 12:25

answered Jul 18 '17 at 12:07

swatsonpicken

873
1
7
21

I tried that but i still get one ticket in my excel sheet. – Michael Jul 18 '17 at 12:11
The cell identifiers will need updating each time around the loop. The answer from @Ankush Jain shows one way to do this. If you are satisfied that his answer works for you, you should mark it as the accepted answer. – swatsonpicken Jul 18 '17 at 12:15
Awesome addition. Thank you much! – Michael Jul 18 '17 at 12:28

score 2 · Accepted Answer · answered Jul 18 '17 at 12:10

2

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

answered Jul 18 '17 at 12:10

Ankush Jain

5,654
4
32
57

1

This worked. Basically, I was not using the correct type of loop? – Michael Jul 18 '17 at 12:18
It wasn't so much the type of loop (there is a solution that uses the foreach loop), it was more the not adding the ticket items to the worksheet and updating the cell references inside the loop. – swatsonpicken Jul 18 '17 at 12:20
oh, ok. I see. The loop couldn't add data to the rest of the cells because I wasn't telling it to. – Michael Jul 18 '17 at 12:23
@Michael Spot on! I've also updated my answer to show a solution using foreach. Either will work, though some will have strong opinions about the preferred approach! – swatsonpicken Jul 18 '17 at 12:27

score 1 · Answer 3 · answered Jul 19 '17 at 07:14

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

Gathering Data For ClosedXML Using foreach Loop

3 Answers3