List<custom> to Excel c#

Question

can anyone help me?

I have a structure

    public struct Data
    {

        public string aaaAAA;          
        public string bbbBBB;          
        public string cccCCC; 
        ...
        ...
     }

then some code to bring in a data into a List, creaitng new list etc.

I want to then transport this to excel which I have done like this,

        for (int r = 0; r < newlist.Count; r++)
        {
            ws.Cells[row,1] = newlist[r].aaaAAA;
            ws.Cells[row,2] = newlist[r].bbbBBB;
            ws.Cells[row,3] = newlist[r].cccBBB;
        }

This works, but it is painfully slow. I am inputting over 12,000 rows and my structure has 85 elements (so each row has 85 columns of data).

Can anyone help make this quicker??

Thanks, Timujin

Answer 1

If as @juharr mentioned you are able to use OpenXML, look at the ClosedXML library for creating Excel documents, found here.

Using your example above you could then use the following code:

var wb = new XLWorkbook();
var ws = wb.Worksheets.Add("Data_Test_Worksheet");
ws.Cell(1, 1).InsertData(newList);
wb.SaveAs(@"c:\temp\Data_Test.xlsx");

If you require a header row, then you would just have to add those manually, using something like the below(Then you would start inserting your rows above from Row 2):

PropertyInfo[] properties = newList.First().GetType().GetProperties();
List<string> headerNames = properties.Select(prop => prop.Name).ToList();
for (int i = 0; i < headerNames.Count; i++)
{
    ws.Cell(1, i + 1).Value = headerNames[i];
}

On the performance requirement, this seems to be more performant than iterating through the array. I have done some basic testing on my side and to insert 20 000 rows for sample object containing 2 properties, it took a total of 1 second.