How to merge excel cells based on the cell value using c#

Question

I am using excel interop with C#. I want to merge all adjacent cells that contain a specific value using C# code.

How to merge all the cells that contain the value (Merged cells) using C# code [![image 1][1]][1]

But I want something like this below,

[![image 2][2]][2]

Here is my full code, but it shows COM Exception handling

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Diagnostics;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using Microsoft.Office.Interop;
using OfficeOpenXml;


namespace ExportExcel
{
public partial class Form1 : Form
{
    DataTable ctr_raw_table = new DataTable();
    public Form1()
    {
        InitializeComponent();
        ctr_raw_table.Columns.Add("Name", typeof(String));
        ctr_raw_table.Columns.Add("Company", typeof(String));
        ctr_raw_table.Columns.Add("Job", typeof(String));
        ctr_raw_table.Columns.Add("Level", typeof(String));
        ctr_raw_table.Columns.Add("ID", typeof(String));
        ctr_raw_table.Columns.Add("Address", typeof(String));
        string[] row = new string[] { "John", "Wipro", "Engineer", "0203794", "India", "Mumbai" };
        ctr_raw_table.Rows.Add(row);
        row = new string[] { "David", "TCS", "Engineer", "02065394", "India", "Thane" };
        ctr_raw_table.Rows.Add(row);
        row = new string[] { "Albert", "Infosys", "Engineer", "0206394", "India", "Mumbai" };
        ctr_raw_table.Rows.Add(row);
        row = new string[] { "Daniel", "IBM", "Engineer", "02036594", "India", "Mumbra" };
        ctr_raw_table.Rows.Add(row);
        row = new string[] { "James", "Accenture", "Engineer", "02560394", "India", "Bhiwandi" };
        ctr_raw_table.Rows.Add(row);
        row = new string[] { "Jacob", "L&T", "Engineer", "02036594", "India", "kalyan" };
        ctr_raw_table.Rows.Add(row);
    }

    private void button1_Click(object sender, EventArgs e)
    {
        Export_Ctr_Excel(ctr_raw_table, "Output");
    }
    private void Export_Ctr_Excel(DataTable tablelist, string excelFilename)
    {

        Microsoft.Office.Interop.Excel.Application objexcelapp = new Microsoft.Office.Interop.Excel.Application();
        objexcelapp.Application.Workbooks.Add(Type.Missing);
        objexcelapp.Columns.AutoFit();
        for (int i = 1; i < tablelist.Columns.Count + 1; i++)
        {
            Microsoft.Office.Interop.Excel.Range xlRange = (Microsoft.Office.Interop.Excel.Range)objexcelapp.Cells[1, i];
            xlRange.Font.Bold = -1;
            //xlRange[objexcelapp.Cells["C2:C7"]].Merge();

            int Row = 1;
            int Column = 1;

            Microsoft.Office.Interop.Excel.Range rng = (Microsoft.Office.Interop.Excel.Range)objexcelapp.Cells[Row, Column];
            rng.EntireColumn.Font.Bold = true;
            rng.EntireColumn.Font.Italic = true;
            //rng.EntireColumn.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.HotPink);
            rng.EntireColumn.BorderAround();

            xlRange.Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;

            xlRange.VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;

            //String startRange = "C1";
            //String endRange = "C7";
            //String repetitiveValue = "Merged Cells";
            //Microsoft.Office.Interop.Excel.Range xlrange = (Microsoft.Office.Interop.Excel.Range)objexcelapp.Cells[startRange, endRange];
            //xlrange.Value2 = repetitiveValue;
            //xlRange.Select();
            //xlrange.Merge(System.Reflection.Missing.Value);

            objexcelapp.Cells[1, i] = tablelist.Columns[i - 1].ColumnName;

            using (var excel = new ExcelPackage(new System.IO.FileInfo(@"physical_address_of_your_xslx_file")))
            {
                var sheetName = "Sheet1";
                var sheet1 = excel.Workbook.Worksheets["Output"];
                // or :
                //var sheet1 = excel.Workbook.Worksheets.First();

                sheet1.Cells["C2:C7"].Merge = true;
                sheet1.Cells["C2:C7"].Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Bottom;
                sheet1.Cells["C2"].Value = "The value";

                excel.Save();
            }



            //DataTable dt = tablelist.Columns[i];
            //if (DataColumnCollection.tablelist.Column.Company = "Wipro")

            //{
            //    Console.WriteLine(System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Yellow));
            //}
            //else
            //{
            //    Console.WriteLine(System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red));
            //}

        }
        for (int i = 0; i < tablelist.Rows.Count; i++)
        {
            for (int j = 0; j < tablelist.Columns.Count; j++)
            {
                if (tablelist.Rows[i][j] != null)
                {
                    Microsoft.Office.Interop.Excel.Range xlRange = (Microsoft.Office.Interop.Excel.Range)objexcelapp.Cells[i + 2, j + 1];

                    xlRange.Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;

                    xlRange.Borders.Weight = 1d;
                    xlRange.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
                    objexcelapp.Cells[i + 2, j + 1] = tablelist.Rows[i][j].ToString();
                }
            }
        }
        objexcelapp.Columns.AutoFit();
        System.Windows.Forms.Application.DoEvents();
        if (Directory.Exists("C:\\CTR_Data\\"))
        {
            objexcelapp.ActiveWorkbook.SaveCopyAs("C:\\CTR_Data\\" + excelFilename + ".xlsx");
        }
        else
        {
            Directory.CreateDirectory("C:\\CTR_Data\\");
            objexcelapp.ActiveWorkbook.SaveCopyAs("C:\\CTR_Data\\" + excelFilename + ".xlsx");
        }
        objexcelapp.ActiveWorkbook.Saved = true;
        System.Windows.Forms.Application.DoEvents();
        foreach (Process proc in System.Diagnostics.Process.GetProcessesByName("EXCEL"))
        {
            proc.Kill();
        }
    }

    private object Worksheets(string v)
    {
        throw new NotImplementedException();

    }
}
}

Answer 1

Welcome to Stack-overflow, I don't recommend to use OfficeInterLopos since they are not easy to use. They are dependent on Office dlls and etc.

There is a cool library calls EPPLUS. EPPlus is a .NET library that reads and writes Excel files using the Office Open XML format (xlsx). EPPlus has no dependencies other than .NET. You can install it via NUGET.

        using (var excel = new ExcelPackage(new System.IO.FileInfo(@"physical_address_of_your_xslx_file")))
        {
            var sheetName = "Sheet1";
            var sheet1 = excel.Workbook.Worksheets[sheetName];
            // or :
            // var sheet1 = excel.Workbook.Workseets.First();

            sheet1.Cells["C2:C7"].Merge = true;
            sheet1.Cells["C2:C7"].Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Bottom;
            sheet1.Cells["C2"].Value = "The value";

            excel.Save();                
        }

Not only it's easy to use but also very powerful and well-documented.

p.s: Don't forget to reference using OfficeOpenXml; on your code.