How do I get the number of rows used by the merged cells in Excel, using ClosedXml and C#

Question

As an overview I am currently exporting data from my database and exporting it to an excel sheet.

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.

I want to fill the cells with a background color, but what is hindering is the merged cells. I am not able to color all the cells in the rows which are used by the merged cells.

The current output in the excel is somewhat like this:

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.

Could someone please give me an idea to go about this hurdle?

This is a sample POC code I have been working on,

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 <= dataTable.Rows.Count + 1; i++)
            {

                for (int k = 1; k <= dataTable.Columns.Count - 4; k++)
                {
                    if (ws.Column(k).IsHidden)
                    {
                        continue;
                    }

                    List<IXLCell> mergeRange = new List<IXLCell>();
                    int j = i;

                    while (j <= dataTable.Rows.Count + 1)
                    {
                        if (ws.Column(k).Cell(j).Value.ToString().Equals(ws.Column(k).Cell(j + 1).Value.ToString()) && ws.Column(1).Cell(j).Value.ToString().Equals(ws.Column(1).Cell(j + 1).Value.ToString())) //&& 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 && mergeRange.Count > 0)
                    {
                        ws.Range(mergeRange.First(), mergeRange.Last()).Merge(false);

                    }
                }
            }

            //Converting All string in the attachement column to Hyperlinks
            for (int i = 2; i <= 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();
        }
    }

Can you share the code, output excel after applying background color and Expected output excel? — selva kumar
@selvakumar I have edited the the question and added the code from my POC, the output excel is as the screenshot shows. — sanktify

Francois Botha Francois Botha · Accepted Answer · 2017-01-17T10:42:27

You can use ws.MergedRanges to get a list of all the merged cells on the sheet. To find the merged range which includes a given cell, you can do something like this: ws.MergedRanges.First(r => r.Contains("B4")). Apply the styling to the entire range that you retrieve.

How do I get the number of rows used by the merged cells in Excel, using ClosedXml and C#

1 Answers