title: Performance of merging column in excel using C# Closed XML
body: <p>I'm having a List it contains 9 more property in that it contains a Child list.</p>
  
  <p>For Example</p>
  
  <pre><code>Public Class BaseClass
  {
      public string Item1 { get; set; }
      public string Item2 { get; set; }
      public List&lt;ChildClass&gt; Item3 { get; set; }
      ............
  }
  
  Public Class ChildClass
  {
      public string Item1 { get; set; }
      public string Item2 { get; set; }
      public string Item3 { get; set; }
  }
  </code></pre>
  
  <p>I Programmed to convert the collection as a Excel File with Column Grouping. In my Actual Original List I'm having more than 3000 items, each Item has a Child List with Count 4 to 6. For iterating the Collection <strong>it takes more than 20 minutes for 1500 Collections</strong>.</p>
  
  <p>Snapshot of Sample Excel Sheet:</p>
  
  <p><a href="https://i.stack.imgur.com/j4E76.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/j4E76.jpg" alt="enter image description here"></a></p>
  
  <p>Kindly refer the following Sample Code it generates the Excel with Dummy Data</p>
  
  <p>I'm using ClosedXML - Kindly get the reference from NuGet</p>
  
  <pre><code>using ClosedXML.Excel;
  using System;
  using System.Collections.Generic;
  using System.Diagnostics;
  using System.Linq;
  using System.Text;
  using System.Threading.Tasks;
  
  
  class Program
  {
      static void Main(string[] args)
      {
          Program obj = new Program();
          obj.GenerateData();
      }
  
      public void GenerateData()
      {
          XLWorkbook xlWorkBook = new XLWorkbook();
          IXLWorksheet xlWorkSheet = xlWorkBook.Worksheets.Add("SearchResults");
  
          Stopwatch stopwatch = new Stopwatch();
          List&lt;TimeSpan&gt; timeElapsedList = new List&lt;TimeSpan&gt;();
  
          Console.WriteLine("Excel Generation Started...");
  
          stopwatch.Start();
          int counter = 0;
          int length = 1500;
          int rowCount = 1;
  
          for (int i = 0; i &lt; length; i++)
          {
              Stopwatch loopstopwatch = new Stopwatch();
              loopstopwatch.Start();
              counter++;
  
              #region Loop Statements Start
              int rowHeight = 5;
  
              int minHt = rowCount;
              int maxHt = rowCount + rowHeight - 1;
  
              xlWorkSheet.Cell(rowCount, 1).WorksheetColumn().Width = 25;
              xlWorkSheet.Cell(rowCount, 1).Value = "Row " + i + " - Item 1";
              xlWorkSheet.Cell(rowCount, 1).Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
              xlWorkSheet.Range("A" + minHt.ToString() + ":A" + maxHt).Column(1).Merge();
  
              xlWorkSheet.Cell(rowCount, 2).WorksheetColumn().Width = 25;
              xlWorkSheet.Cell(rowCount, 2).Value = "Row " + i + " - Item 2";
              xlWorkSheet.Cell(rowCount, 2).Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
              xlWorkSheet.Range("B" + minHt.ToString() + ":B" + maxHt).Column(1).Merge();
  
              xlWorkSheet.Cell(rowCount, 3).WorksheetColumn().Width = 25;
              xlWorkSheet.Cell(rowCount, 3).Value = "Row " + i + " - Item 3";
              xlWorkSheet.Cell(rowCount, 3).Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
              xlWorkSheet.Range("C" + minHt.ToString() + ":C" + maxHt).Column(1).Merge();
  
              xlWorkSheet.Cell(rowCount, 4).WorksheetColumn().Width = 25;
              xlWorkSheet.Cell(rowCount, 4).Value = "Row " + i + " - Item 4";
              xlWorkSheet.Cell(rowCount, 4).Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
              xlWorkSheet.Range("D" + minHt.ToString() + ":D" + maxHt).Column(1).Merge();
  
              xlWorkSheet.Cell(rowCount, 5).WorksheetColumn().Width = 25;
              xlWorkSheet.Cell(rowCount, 5).Value = "Row " + i + " - Item 5";
              xlWorkSheet.Cell(rowCount, 5).Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
              xlWorkSheet.Range("E" + minHt.ToString() + ":E" + maxHt).Column(1).Merge();
  
              xlWorkSheet.Cell(rowCount, 6).WorksheetColumn().Width = 25;
              xlWorkSheet.Cell(rowCount, 6).Value = "Row " + i + " - Item 6";
              xlWorkSheet.Cell(rowCount, 6).Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
              xlWorkSheet.Range("F" + minHt.ToString() + ":F" + maxHt).Column(1).Merge();
  
              xlWorkSheet.Cell(rowCount, 7).WorksheetColumn().Width = 25;
              xlWorkSheet.Cell(rowCount, 7).Value = "Row " + i + " - Item 7";
              xlWorkSheet.Cell(rowCount, 7).Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
              xlWorkSheet.Range("G" + minHt.ToString() + ":G" + maxHt).Column(1).Merge();
  
              xlWorkSheet.Cell(rowCount, 8).WorksheetColumn().Width = 25;
              xlWorkSheet.Cell(rowCount, 8).Value = "Row " + i + " - Item 8";
              xlWorkSheet.Cell(rowCount, 8).Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
              xlWorkSheet.Range("H" + minHt.ToString() + ":H" + maxHt).Column(1).Merge();
  
              for (int j = 0; j &lt; rowHeight; j++)
              {
                  xlWorkSheet.Cell(rowCount, 9).WorksheetColumn().Width = 25;
                  xlWorkSheet.Cell(rowCount, 9).Value = "Row " + i + " | SubRow" + j + " - Sub Item 1";
                  xlWorkSheet.Cell(rowCount, 10).WorksheetColumn().Width = 25;
                  xlWorkSheet.Cell(rowCount, 10).Value = "Row " + i + " | SubRow" + j + " - Sub Item 2";
                  xlWorkSheet.Cell(rowCount, 11).WorksheetColumn().Width = 25;
                  xlWorkSheet.Cell(rowCount, 11).Value = "Row " + i + " | SubRow" + j + " - Sub Item 3";
                  xlWorkSheet.Cell(rowCount, 12).WorksheetColumn().Width = 25;
                  xlWorkSheet.Cell(rowCount, 12).Value = "Row " + i + " | SubRow" + j + " - Sub Item 4";
                  xlWorkSheet.Cell(rowCount, 13).WorksheetColumn().Width = 25;
                  xlWorkSheet.Cell(rowCount, 13).Value = "Row " + i + " | SubRow" + j + " - Sub Item 5";
                  xlWorkSheet.Cell(rowCount, 14).WorksheetColumn().Width = 25;
                  xlWorkSheet.Cell(rowCount, 14).Value = "Row " + i + " | SubRow" + j + " - Sub Item 6";
                  xlWorkSheet.Cell(rowCount, 15).WorksheetColumn().Width = 25;
                  xlWorkSheet.Cell(rowCount, 15).Value = "Row " + i + " | SubRow" + j + " - Sub Item 7";
  
                  rowCount++;
              }
  
              xlWorkSheet.Cell(minHt, 16).WorksheetColumn().Width = 25;
              xlWorkSheet.Cell(minHt, 16).Value = "Row " + i + " - Item 9";
              xlWorkSheet.Cell(minHt, 16).Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
              xlWorkSheet.Range("P" + minHt.ToString() + ":P" + maxHt).Column(1).Merge();
  
              #endregion
  
              loopstopwatch.Stop();
              timeElapsedList.Add(loopstopwatch.Elapsed);
  
              Console.WriteLine("Iteration No: {0} - Time Taken {1}", counter, loopstopwatch.Elapsed);
          }
  
          stopwatch.Stop();
  
          Console.WriteLine("Excel Construction Completed !");
          Console.WriteLine("Total Time Taken to Complete {0}", stopwatch.Elapsed);
          Console.WriteLine("Minimum Time : {0}", timeElapsedList.Min());
          Console.WriteLine("Maximum Time : {0}", timeElapsedList.Max());
      }
  
  }
  </code></pre>
  
  <p>Kindly assist me how to optimize the Code with Column Merging in terms of Time management.</p>