Reading Merged Cell From Excel Using Microsoft.Office.Interop.Excel in C#

Introduction

It's very simple and easy for us to read and upload an Excel file with normal columns and you'll find ample articles for that. But when you want to read an Excel file with merged cells then the way to do that is a little different. Using OLEDB anyone can read the Excel file that has a header at the first row and the rest of the data is vertically grown with no merged columns (in other words a single-celled single value). But if you try to read an Excel file that contains a merged cell using OLEDB then the result will be that it is in an unexpected format. The workaround is to either use a third-party DLL or you can use one that Microsoft provides, Microsoft.office.Interop.Excel.dll.

This was the requirement for me when working on a project. We needed to import data from an Excel file having merged cells and place the data inside SQL without duplication and also properly format the relationship among multiple tables, because the tables were highly normalized.

Let's first create a sample Excel file. The following is the snapshot of it.



As you can see, I have multiple fruit names along with their benefits. For each fruit there are multiple benefits. Now let's say you have tables in your SQL that store the name of the fruit, another table that stores the benefits offered and a third table that has the mapping of the fruit with that of the benefits. In that case you need to read the Excel file in accordance with your SQL table and also form the relationship with that of the benefits while reading. Also a special attention while reading must be taken about the duplication of the data, in other words no fruit or benefits should be duplicated and also their mapping.

The following is the SQL table's script:

create table Fruits
(
FruitId int identity(1,1)not null,
FruitName varchar(30),
Constraint pkFruitId primary key(FruitId))

create table Benefits
(
BenefitID int identity(1,1) not null,
BenefitName varchar(200)not null,
constraint pkBenefitId primary key(BenefitID)
)

create table FruitBenefit
(
FruitBenefitID int identity(1,1)not null,
FruitId int not null,
BenefitId int not null,
constraint fkFruitId foreign key(FruitID)references Fruits(FruitID),
constraint fkbenefitId foreign key(BenefitId)references Benefits(BenefitId)
)

Okay everything is now in place. The dilemma now is, how to read the Excel merged cell. Well to read an Excel merged cell its simple, whatever columns are merged all form a range and we can get the data from that range. In simple terms we can say that only the first cell of the merged cell contains the data and the rest of them contain an empty string. Merging a cell doesn't mean that the cell does not exist, it exists but doesn't have a value.

NOTE: For reading an Excel file we are using Microsoft.Office.Interop.Excel.dll.

You need to add a reference for Microsoft.Office.Interop.Excel.dll to your application. Here I'm using a Console Application for demonstration purposes.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.Office.Interop.Excel;
using System.Data.SqlClient;
using System.Data;

namespace ExcelWithMergedCells
{
    internal sealed class Program
    {
        SqlConnection con;
        SqlDataAdapter da;
        System.Data.DataTable dtFruits, dtBenefits, dtFruitBenefits;
        DataSet dsTemp;
        SqlCommand cmd;
        const string DBCon = "Server=VISHALG;Initial Catalog=Practice;Integrated Security=true";
        const string EXCEL_PATH = @"D:\Vishal\Mvc\Practice\ExcelWithMergedCells\DummyData.xlsx";
        Application excelApplication = null;
        Workbook excelWorkBook = null;

        private DataColumn CreateIdentityColumn(string columnName = "SrNo")
        {
            DataColumn dc = new DataColumn(columnName);
            dc.AutoIncrement = true;
            dc.AutoIncrementSeed = dc.AutoIncrementStep = 1;
            return dc;
        }

        private void ReadExcelFile()
        {
            try
            {
                dsTemp = new DataSet();
                if (dsTemp.Tables.Count == 0)
                {
                    dtFruits = dsTemp.Tables.Add(TableNames.Fruits.ToString());
                    dtFruits.Columns.Add(CreateIdentityColumn("FruitId"));
                    dtFruits.Columns.Add("FruitName");

                    dtBenefits = dsTemp.Tables.Add(TableNames.Benefits.ToString());
                    dtBenefits.Columns.Add(CreateIdentityColumn("BenefitID"));
                    dtBenefits.Columns.Add("BenefitName");

                    dtFruitBenefits = dsTemp.Tables.Add(TableNames.FruitBenefit.ToString());
                    dtFruitBenefits.Columns.Add(CreateIdentityColumn("FruitBenefitID"));
                    dtFruitBenefits.Columns.Add("FruitId");
                    dtFruitBenefits.Columns.Add("BenefitId");
                }

                excelApplication = new Application();
                //Opening/Loading the workBook in memory
                excelWorkBook = excelApplication.Workbooks.Open(EXCEL_PATH);

                //retrieving the worksheet counts inside the excel workbook
                int workSheetCounts = excelWorkBook.Worksheets.Count;
                int totalColumns = 0;
                Range objRange = null;

                for (int sheetCounter = 1; sheetCounter <= workSheetCounts; sheetCounter++)
                {
                    Worksheet workSheet = excelWorkBook.Sheets[sheetCounter];

                    totalColumns = workSheet.UsedRange.Cells.Columns.Count + 1;

                    object[] data = null;

                    //Iterating from row 2 because first row contains HeaderNames
                    for (int row = 2; row < workSheet.UsedRange.Cells.Rows.Count; row++)
                    {
                        data = new object[totalColumns - 1];

                        for (int col = 1; col < totalColumns; col++)
                        {
                            objRange = workSheet.Cells[row, col];
                            if (objRange.MergeCells)
                            {
                                data[col - 1] = Convert.ToString(((Range)objRange.MergeArea[1, 1]).Text).Trim();
                            }
                            else
                            {
                                data[col - 1] = Convert.ToString(objRange.Text).Trim();
                            }
                        }
                        AddRow(data);
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                //Release the Excel objects
                excelWorkBook.Close(false, System.Reflection.Missing.Value, System.Reflection.Missing.Value);
                excelApplication.Workbooks.Close();
                excelApplication.Quit();
                excelApplication = null;
                excelWorkBook = null;

                GC.GetTotalMemory(false);
                GC.Collect();
                GC.WaitForPendingFinalizers();
                GC.Collect();
                GC.GetTotalMemory(true);

                DumbDataIntoSql();
            }
        }

        private void AddRow(object[] data)
        {
            int check = 0;
            for (int tableCounter = 0; tableCounter < dsTemp.Tables.Count; tableCounter++)
            {
                TableNames tableName = (TableNames)Enum.Parse(typeof(TableNames), dsTemp.Tables[tableCounter].TableName);
                switch (tableName)
                {
                    case TableNames.Fruits:

                        check = dtFruits.AsEnumerable().Where(x => x["FruitName"].ToString().Equals(data[0].ToString(), StringComparison.InvariantCultureIgnoreCase)).Count();

                        if (check == 0 && !string.IsNullOrEmpty(Convert.ToString(data[0])))
                        {
                            DataRow dr = dtFruits.NewRow();
                            dr["FruitName"] = data[0].ToString();
                            dtFruits.Rows.Add(dr);
                        }
                        break;
                    case TableNames.Benefits:

                        check = dtBenefits.AsEnumerable().Where(x => x["BenefitName"].ToString().Equals(data[1].ToString(), StringComparison.InvariantCultureIgnoreCase)).Count();

                        if (check == 0 && !string.IsNullOrEmpty(Convert.ToString(data[1])))
                        {
                            DataRow dr = dtBenefits.NewRow();
                            dr["BenefitName"] = data[1].ToString();
                            dtBenefits.Rows.Add(dr);
                        }
                        break;
                    case TableNames.FruitBenefit:

                        int fruitID = dtFruits.AsEnumerable().Where(x => x["FruitName"].ToString().Equals(data[0].ToString(), StringComparison.InvariantCultureIgnoreCase)).Select(x => Convert.ToInt32(x["FruitID"].ToString())).FirstOrDefault();

                        int benefitID = dtBenefits.AsEnumerable().Where(x => x["BenefitName"].ToString().Equals(data[1].ToString(), StringComparison.InvariantCultureIgnoreCase)).Select(x => Convert.ToInt32(x["BenefitID"].ToString())).FirstOrDefault();

                        check = dtFruitBenefits.AsEnumerable().Where(x => Convert.ToInt32(x["FruitId"].ToString()) == fruitID && Convert.ToInt32(x["BenefitID"].ToString()) == benefitID).Count();

                        if (check == 0 && fruitID != 0 && benefitID != 0)
                        {
                            DataRow dr = dtFruitBenefits.NewRow();
                            dr["FruitID"] = fruitID;
                            dr["BenefitID"] = benefitID;
                            dtFruitBenefits.Rows.Add(dr);
                        }
                        break;
                }
            }
        }

        private void DumbDataIntoSql()
        {
            try
            {
                int rowsAffected = 0;
                Console.WriteLine("Dumping Data into SQL Tables\n\n");
                con = new SqlConnection(DBCon);
                con.Open();
                string query = string.Empty;
                for (int i = 0; i < dsTemp.Tables.Count; i++)
                {
                    rowsAffected = 0;
                    string colNames = string.Join(",", dsTemp.Tables[i].Columns.Cast<DataColumn>().Where(x => x.AutoIncrement == false).Select(x => x.ColumnName).ToArray<string>());
                    string[] arr = colNames.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries);
                    query = "insert into " + dsTemp.Tables[i].TableName + "(" + colNames + ") values(";
                    for (int col = 0; col < arr.Length; col++)
                    {
                        if (col != arr.Length - 1)
                            query += "@" + arr[col] + ",";
                        else
                            query += "@" + arr[col] + ")";
                    }
                    cmd = new SqlCommand(query, con);
                    for (int row = 0; row < dsTemp.Tables[i].Rows.Count; row++)
                    {
                        for (int col = 0, arrCounter = 0; col < dsTemp.Tables[i].Columns.Count; col++)
                        {
                            if (!dsTemp.Tables[i].Columns[col].AutoIncrement)
                            {
                                cmd.Parameters.AddWithValue("@" + arr[arrCounter], dsTemp.Tables[i].Rows[row][col].ToString());
                                arrCounter++;
                            }
                        }
                        rowsAffected += cmd.ExecuteNonQuery();
                        cmd.Parameters.Clear();
                    }
                    Console.WriteLine("{0} Records Affected For Table: \"{1}\"", rowsAffected, dsTemp.Tables[i].TableName);
                }
                con.Close();
                Console.WriteLine("Press any key to Terminate");
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
        }

        static void Main(string[] args)
        {
            Program objProgram = new Program();
            objProgram.ReadExcelFile();
            Console.ReadLine();
        }
    }
}

The following will be the output for that.