Optimize performance of data processing method

Question

I am using the following code to take some data (in XML like format - Not well formed) from a .txt file and then write it to an .xlsx using EPPlus after doing some processing. StreamElements is basically a modified XmlReader. My question is about performance, I have made a couple of changes but don't see what else I can do. I'm going to use this for large datasets so I'm trying to modify to make this as efficient and fast as possible. Any help will be appreciated!

I tried using p.SaveAs() to do the excel writing but it did not really see a performance difference. Are there better faster ways to do the writing? Any suggestions are welcome.

using (ExcelPackage p = new ExcelPackage())
    {
    ExcelWorksheet ws = p.Workbook.Worksheets[1];
    ws.Name = "data1";
    int rowIndex = 1; int colIndex = 1;

      foreach (var element in StreamElements(pa, "XML"))
      {
         var values = element.DescendantNodes().OfType<XText>()
         .Select(v => Regex.Replace(v.Value, "\\s+", " "));
         string[] data = string.Join(",", values).Split(',');

         data[2] = toDateTime(data[2]);

         for (int i = 0; i < data.Count(); i++)
         {
           if (rowIndex < 1000000) 
           { 
           var cell1 = ws.Cells[rowIndex, colIndex];
           cell1.Value = data[i];
           colIndex++;
           }
         }
         rowIndex++;
      }
    }

    ws.Cells[ws.Dimension.Address].AutoFitColumns();

    Byte[] bin = p.GetAsByteArray();
    using (FileStream fs = File.OpenWrite("C:\\test.xlsx"))
    {
      fs.Write(bin, 0, bin.Length);
    }

  }
}

Currently, for it to do the processing and then write 1 Million lines into an Excel worksheet, it takes about ~30-35 Minutes.

Answer 1

I've ran into this issue before and excel has a huge overhead when you're modifying worksheet cells individually one by one.

The solution to this is to create an object array and populate the worksheet using the WriteRange functionality.

using(ExcelPackage p = new ExcelPackage()) {
    ExcelWorksheet ws = p.Workbook.Worksheets[1];
    ws.Name = "data1";

    //Starting cell
    int startRow = 1;
    int startCol = 1;

    //Needed for 2D object array later on
    int maxColCount = 0;
    int maxRowCount = 0;

    //Queue data
    Queue<string[]> dataQueue = new Queue<string[]>();

    //Tried not to touch this part
    foreach(var element in StreamElements(pa, "XML")) {
        var values = element.DescendantNodes().OfType<XText>()
            .Select(v = > Regex.Replace(v.Value, "\\s+", " "));

        //Removed unnecessary split and join, use ToArray instead
        string[] eData = values.ToArray();
        eData[2] = toDateTime(eData[2]);

        //Push the data to queue and increment counters (if needed)
        dataQueue.Enqueue(eData);

        if(eData.Length > maxColCount)
            maxColCount = eData.Length;

        maxRowCount++;
    }

    //We now have the dimensions needed for our object array
    object[,] excelArr = new object[maxRowCount, maxColCount];

    //Dequeue data from Queue and populate object matrix
    int i = 0;
    while(dataQueue.Count > 0){
        string[] eData = dataQueue.Dequeue();

        for(int j = 0; j < eData.Length; j++){
            excelArr[i, j] = eData[j];
        }

        i++;
    }

    //Write data to range
    Excel.Range c1 = (Excel.Range)wsh.Cells[startRow, startCol];
    Excel.Range c2 = (Excel.Range)wsh.Cells[startRow + maxRowCount - 1, maxColCount];
    Excel.Range range = worksheet.Range[c1, c2];

    range.Value2 = excelArr;

    //Tried not to touch this stuff
    ws.Cells[ws.Dimension.Address].AutoFitColumns();

    Byte[] bin = p.GetAsByteArray();
    using(FileStream fs = File.OpenWrite("C:\\test.xlsx")) {
        fs.Write(bin, 0, bin.Length);
    }
}

I didn't try compiling this code, so double check the indexing used; and check for any small syntax errors.

A few extra pointers to consider for performance:

• Try to parallel the population of the object array, since it is primarily index based (maybe have a dictionary with an index tracker Dictionary<int, string[]>) and lookup in there for faster population of the object array. You would likely have to trade space for time.
• See if you are able to hardcode the column and row counts, or figure it out quickly. In my code fix, I've set counters to count the maximum rows and columns on the fly; I wouldn't recommend it as a permanent solution.
• AutoFitColumns is very costly, especially if you're dealing with over a million rows