C# Paste List<object> into Excel column withou looping-CodePudding

I have some code that gathers issue from the application run and i wanted to create a report about issues. Unfortunately using Intertop makes this export a long run.

        //loop results
        int i = 2;
        foreach (EntryIssue entryIssue in DuplicatesList)
        {
            xlWorkSheet.Cells[i, 1] = entryIssue.dataString;
            xlWorkSheet.Cells[i, 2] = entryIssue.fileLine;
            xlWorkSheet.Cells[i, 3] = entryIssue.fileName;
            i  ;
        }

There are three other sheets created and three other lists that fill them in code, but they work in similar manner.

Is there a way to avoid iteration of the list and paste the results from it in specific range? I think this could save some time or should i use other library than Intertop?

EDIT:

Ok, I had located a solution although it seems to work only on 2D arrays, there seems to be a problem with 1D array as it only assignes records based on first record within the array. I'm not sure how to solve that.

My initial idea was to paste it with Linq

1D arrays (doesn't work):

  xlRange = xlWorkSheet.get_Range((object)xlWorkSheet.Cells[2, 1], (object)xlWorkSheet.Cells[DuplicatesList.Count   1, 1]);
            var getArray = DuplicatesList.Select(r => r.dataString).ToArray();
            xlRange.Value = getArray;
            xlRange = xlWorkSheet.get_Range((object)xlWorkSheet.Cells[2, 2], (object)xlWorkSheet.Cells[DuplicatesList.Count   1, 2]);
            getArray = DuplicatesList.Select(r => r.fileLine).ToArray();
            xlRange.Value = getArray;
            xlRange = xlWorkSheet.get_Range((object)xlWorkSheet.Cells[2, 3], (object)xlWorkSheet.Cells[DuplicatesList.Count   1, 3]);
            getArray = DuplicatesList.Select(r => r.fileName).ToArray();
            xlRange.Value = getArray;

Then i decided to iterate list elements into array, as i dont know if you can assign from list of objects into multidimensioarray. On a file with 220 thousand items it went from above 10 minutes to approx 2-3 seconds.

2D array (does work)

        //set range
        xlRange = xlWorkSheet.get_Range((object)xlWorkSheet.Cells[2, 1], (object)xlWorkSheet.Cells[DuplicatesList.Count   1, 3]);
        //fill array 
        int i = 0;
        object[,] getArray = new object[DuplicatesList.Count, 3];
        foreach (EntryIssue entryIssue in DuplicatesList)
        {
            getArray[i, 0] = entryIssue.dataString;
            getArray[i, 1] = entryIssue.fileLine;
            getArray[i, 2] = entryIssue.fileName;
            i  ;
        }
        //assign array to range
        xlRange.Value = getArray;

later on im clearing Marshall

    ips.Marshal.ReleaseComObject(xlRange);
    ips.Marshal.ReleaseComObject(xlWorkSheet);
    ips.Marshal.ReleaseComObject(xlWorkBook);
    ips.Marshal.ReleaseComObject(xlApp);

CodePudding user response：

Epplus from Nuget would be an easy solution. ie:

void Main()
{

    var r = new Random();
    var data = new List<MyData>();
    for (int i = 0; i < 100000; i  )
    {
        data.Add(new MyData { DataString = $"Data {i}", FileLine = r.Next(1, 1000), FileName = $"File #{i}" });
    }

    Stopwatch sw = new Stopwatch();
    sw.Start();
    ExcelPackage pck = new ExcelPackage();
    var ws = pck.Workbook.Worksheets.Add("FromAnonymous");

    //Load the collection starting from cell A1...
    ws.Cells["A1"].LoadFromCollection(data, true, TableStyles.Medium9);
    ws.Cells[ws.Dimension.Address].AutoFitColumns();

    //...and save
    var fi = new FileInfo(@"c:\temp\AnonymousCollection.xlsx");
    if (fi.Exists)
    {
        fi.Delete();
    }
    pck.SaveAs(fi);
    sw.Stop();
    Console.WriteLine(sw.ElapsedMilliseconds);
}

public class MyData
{
    public string DataString { get; set; }
    public int FileLine { get; set; }
    public string FileName { get; set; }
}