title: How do I get the number of rows used by the merged cells in Excel, using ClosedXml and C#
body: <p>As an overview I am currently exporting data from my database and exporting it to an excel sheet.</p>
  
  <p>I have formatted some of the cells (i.e Merged cells etc) in the excel sheet using ClosedXml, now I am little stuck with a small issue which I can get around as of now.</p>
  
  <p>I want to fill the cells with a background color, but what is hindering is the merged cells. I am not able to color <b>all</b> the cells in the rows which are used by the merged cells.</p>
  
  <p>The current output in the excel is somewhat like this:</p>
  
  <p><a href="https://i.stack.imgur.com/WfMDU.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/WfMDU.png" alt="This is how a part of the output looks like"></a></p>
  
  <p>The solutions I have tried fill the merged cells correctly, but the adjacent unmerged cells in the row arent all filled with the background color.</p>
  
  <p>Could someone please give me an idea to go about this hurdle?</p>
  
  <p>This is a sample POC code I have been working on,</p>
  
  <pre><code>private static void ToExcel(System.Data.DataTable dataTable, HttpResponseBase response, string fileName)
      {
          using (XLWorkbook wb = new XLWorkbook())
          {
              wb.CalculateMode = XLCalculateMode.Auto;
              var ws = wb.Worksheets.Add(dataTable, "OKR Quater Report");
              ws.Tables.FirstOrDefault().ShowAutoFilter = false;
              var colRange = ws.Columns();
              colRange.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left;
              colRange.Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
              var headingCells = ws.Row(1).Cells();
              headingCells.Style.Font.Bold = true;
              headingCells.Style.Fill.SetBackgroundColor(XLColor.AirForceBlue);
              headingCells.Style.Font.SetFontColor(XLColor.White);
              ws.SheetView.FreezeRows(1);
  
              ws.Column(3).Hide();
              ws.Column(8).Hide();
  
  
              for (int i = 2; i &lt;= dataTable.Rows.Count + 1; i++)
              {
  
                  for (int k = 1; k &lt;= dataTable.Columns.Count - 4; k++)
                  {
                      if (ws.Column(k).IsHidden)
                      {
                          continue;
                      }
  
                      List&lt;IXLCell&gt; mergeRange = new List&lt;IXLCell&gt;();
                      int j = i;
  
                      while (j &lt;= dataTable.Rows.Count + 1)
                      {
                          if (ws.Column(k).Cell(j).Value.ToString().Equals(ws.Column(k).Cell(j + 1).Value.ToString()) &amp;&amp; ws.Column(1).Cell(j).Value.ToString().Equals(ws.Column(1).Cell(j + 1).Value.ToString())) //&amp;&amp; ws.Column(5).Cell(j).Value.ToString().Equals(ws.Column(5).Cell(j + 1).Value.ToString())
                          {
                              mergeRange.Add(ws.Column(k).Cell(j));
                              j++;
                          }
                          else
                          {
                              mergeRange.Add(ws.Column(k).Cell(j));
                              break;
                          }
                      }
                      if (mergeRange != null &amp;&amp; mergeRange.Count &gt; 0)
                      {
                          ws.Range(mergeRange.First(), mergeRange.Last()).Merge(false);
  
                      }
                  }
              }
  
              //Converting All string in the attachement column to Hyperlinks
              for (int i = 2; i &lt;= dataTable.Rows.Count + 1; i++)
              {
                  if (!(ws.Column(10).Cell(i).Value.ToString().Equals(string.Empty)))
                  {
                      ws.Column(10).Cell(i).FormulaA1 = "=HYPERLINK" + ws.Column(10).Cell(i).Value.ToString();
                  }
              }
  
              //Writing datatable to Excel
              MemoryStream stream = GetStream(wb);// The method is defined below
              response.Clear();
              response.Buffer = true;
              response.AddHeader("content-disposition",
              "attachment; filename=" + fileName + "_" + DateTime.Now.ToString() + ".xlsx;");
              response.ContentType = "application/vnd.ms-excel";
              response.BinaryWrite(stream.ToArray());
              response.End();
          }
      }
  </code></pre>