Ali Adnan Ali Adnan - 5 months ago 98
C# Question

c# EPPlus - Merge Rows in Excel based on conditional Data in rows

I am using EPPlus Library to create an Excel report.

So far I have loaded the

DataTable
on sheet by using this code:

string FileName = @"DataSource\sampleData.xml";

var excelDocName = @"C:\Excel\OutExcel.xlsx";
var aFile = new FileInfo(excelDocName);

if (File.Exists(excelDocName))
File.Delete(excelDocName);

DataSet ds = new DataSet();
ds.ReadXml(FileName);

DataTable xmlTable = ds.Tables[0];

using (ExcelPackage pck = new ExcelPackage(aFile))
{
ExcelWorksheet ws = pck.Workbook.Worksheets.Add("DataLoad");
ws.Cells["A1"].LoadFromDataTable(xmlTable, true);
ws.Cells.AutoFitColumns();
pck.Save();
}


The result is perfect, but what I need to is


  1. Merge the rows having same values in Column A and based on that I need to merge rows in Column B,C and D.

  2. Sum range of Values in Column E based on Column A and merge rows in column E and show the Sum result.



I am attaching the screenshot of what I am getting and what result is needed and also share the Excel file and XML Data Source to quickly use the code to generate Excel.


Answer Source

As Suggested by 'alhashmiya', I have change the logic and iterate DataTable row by row to insert and during insertion I hold the specific columns in a variable to update the merge range.

Below code is for any one in future who is having same requirement,

string FileName = @"DataSource\sampleData.xml";
var excelDocName = @"c:\temp\OutExcel.xlsx";
var aFile = new FileInfo(excelDocName);

DataSet ds = new DataSet();
ds.ReadXml(FileName);
DataTable table = ds.Tables[0];

using (ExcelPackage pck = new ExcelPackage(aFile))
{
    ExcelWorksheet ws = pck.Workbook.Worksheets[1];
    const int startRow = 2;
    int mergestarttrow = 2;
    int rw = startRow;
    int RefNo = 0;
    int TTLCONS = 0;
    string[] mergedColNamed = new string[] { "SNO", "_NAME", "CAUSE_SUBCAUSE", "_AREAS", "CONS", "GRIDNAM" };
    Dictionary<string, string> mergeRange = new Dictionary<string, string>() { { "SNO", "A" }, { "_NAME", "B" }, { "CAUSE_SUBCAUSE", "C" }, { "_AREAS", "D" }, { "CONS", "E" }, { "GRIDNAM", "F" } };

    if (table.Rows.Count != 0)
    {
        foreach (DataRow row in table.Rows)
        {
            bool needMerge = false;
            bool checkMerge = false;
                   //Hold to compare with next iteration and based to condition match Enable Check Merge Flag
            int CurrentRefNo = System.Convert.ToInt32(row["SNO"]);

            if (RefNo > 0)
            {
                if (RefNo == CurrentRefNo)
                {
                    checkMerge = true;
                    mergeRange = mergeRange = mergeRange = new Dictionary<string, string>() { { "SNO", "A" }, { "_NAME", "B" }, { "CAUSE_SUBCAUSE", "C" }, { "_AREAS", "D" }, { "CONS", "E" }, { "GRIDNAM", "F" } };
                }
                else
                {
                    mergestarttrow = rw;
                    needMerge = true;
                }

            }

            int col = 1;

            if (rw > startRow)
                ws.InsertRow(rw, 1);

            if (needMerge)
            {
                for (int i = 0; i < mergedColNamed.Length; i++)
                {
                    string MergeRangeValue = mergeRange.FirstOrDefault(x => x.Key == mergedColNamed[i]).Value;

                    using (ExcelRange Rng = ws.Cells[MergeRangeValue])
                    {
                        Rng.Merge = true;
                        ws.Cells[MergeRangeValue].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                        ws.Cells[MergeRangeValue].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
                                Rng.Style.Border.Top.Style = ExcelBorderStyle.Thin;
                                Rng.Style.Border.Left.Style = ExcelBorderStyle.Thin;
                                Rng.Style.Border.Right.Style = ExcelBorderStyle.Thin;
                                Rng.Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
                                if (mergedColNamed[i] == "CONS")
                                {
                                    Rng.Value = TTLCONS;
                                    TTLCONS = 0;
                                    Rng.Style.Numberformat.Format = "0";
                                }

                                if (mergedColNamed[i] == "SNO")
                                {
                                    Rng.Style.Numberformat.Format = "0";
                                }
                            }
                        }
                        mergeRange = new Dictionary<string, string>() { { "SNO", "A" }, { "_NAME", "B" }, { "CAUSE_SUBCAUSE", "C" }, { "_AREAS", "D" }, { "CONS", "E" }, { "GRIDNAM", "F" } };
                    }

                    foreach (DataColumn dc in table.Columns)
                    {
                        if (mergedColNamed.Contains(dc.ColumnName.ToUpper()))
                        {
                            if (dc.ColumnName.ToUpper() == "CONS")
                                TTLCONS = TTLCONS + System.Convert.ToInt32(row[dc]);

                            if (!checkMerge)//First Row
                            {
                                if (dc.ColumnName.ToUpper() == "SNO")
                                    RefNo = System.Convert.ToInt32(row[dc]);


                                ws.Cells[rw, col].Value = row[dc].ToString();
                                ws.Cells[rw, col].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                                ws.Cells[rw, col].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
                                ws.Cells[rw, col].Style.Border.Top.Style = ExcelBorderStyle.Thin;
                                ws.Cells[rw, col].Style.Border.Left.Style = ExcelBorderStyle.Thin;
                                ws.Cells[rw, col].Style.Border.Right.Style = ExcelBorderStyle.Thin;
                                ws.Cells[rw, col].Style.Border.Bottom.Style = ExcelBorderStyle.Thin;

                                if (dc.ColumnName.ToUpper() == "CONS")
                                {
                                    ws.Cells[rw, col].Style.Numberformat.Format = "0";
                                }


                            }
                            else //No Need to set Cell Values as ultimately we will merge only updating the merge range
                            {
                                string MergeRangeKey = mergeRange.FirstOrDefault(x => x.Value == mergeRange[dc.ColumnName.ToUpper()]).Key;
                                string MergeRangeValue = mergeRange[dc.ColumnName.ToUpper()];
                                mergeRange[MergeRangeKey] = string.Format(mergeRange[dc.ColumnName.ToUpper()] + "{0}:" + mergeRange[dc.ColumnName.ToUpper()] + "{1}", mergestarttrow, rw);
                            }

                        }
                        else
                        {
                            ws.Cells[rw, col].Value = row[dc].ToString();
                            ws.Cells[rw, col].Style.Border.Top.Style = ExcelBorderStyle.Thin;
                            ws.Cells[rw, col].Style.Border.Left.Style = ExcelBorderStyle.Thin;
                            ws.Cells[rw, col].Style.Border.Right.Style = ExcelBorderStyle.Thin;
                            ws.Cells[rw, col].Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
                        }
                        col++;
                    }
                    rw++;
                }
            }

            ws.Cells.AutoFitColumns();
            pck.Save();
            System.Diagnostics.Process.Start(excelDocName);