Reading Merged Cell From Excel Using Bytescout.SpreadSheet in C#

This article describes how to read an Excel file that has merged columns within it. It's very simple and easy for us to read and upload an Excel file with normal columns and you'll find ample articles of that. But when you want to read an Excel file with merged cells then the way to do that is a bit way different.

This was the requirement for me while working on a project, that we needed to import data from an Excel file having merged cells and place the data inside SQL without duplication and also properly formating the relationship among multiple tables, because the tables were highly normalized.
Let's move towards creation of the Excel file. The following is the snapshot of it.

As you can see I've multiple fruit names along with there 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 tables 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)
)

OK everything is now in place, the dilemma now is about how to read the Excel merged cell. Well to read an Excel merged cell it's 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, its exists but doesn't have a value.

NOTE: For reading an Excel file we are using Bytescout.spreadsheet.dll.

Here is the code for it.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using Bytescout.Spreadsheet;

namespace ExcelWithMergedCell
{
    class Program
    {
        SqlConnection con;
        SqlDataAdapter da;
        DataTable dtFruits, dtBenefits, dtFruitBenefits;
        DataSet dsTemp;
        SqlCommand cmd;
        const string DBCon = "Server=VISHALG\\SQLEXPRESS;Initial Catalog=Practice;Integrated Security=true";
        const string EXCEL_PATH = "D:\\Vishal\\Mvc\\ExcelWithMergedCell\\DummyData.xls";

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

        public void ReadExcelFile(string filePath, bool isHDR = true)
        {
            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");

            }
            if (isHDR)
            {
                try
                {
                    Spreadsheet document = new Spreadsheet();

                    //load the file from the path. make sure that the file is not opened in excel else it will give error
                    document.LoadFromFile(filePath);

                    //get the total no of worksheets within a workbook
                    int totalWorkSheet = document.Workbook.Worksheets.Count;

                    //to iterate over multiple worksheet within a workbook
                    for (int workSheetCounter = 0; workSheetCounter < totalWorkSheet; workSheetCounter++)
                    {
                        Worksheet objWorkSheet = document.Workbook.Worksheets[workSheetCounter];

                        //iterate over the maximum row used withing the sheet.
                        //Iterate From row 1 because your file contains header row
                        for (int row = 1; row <= objWorkSheet.UsedRangeRowMax; row++)
                        {
                            //since we known the exact columns in our excel we can directly initalize the array to that no
                            object[] data = new object[2];

                            //if you don't known then use the following code
                            //object[] data = new object[objWorkSheet.NotEmptyColumnMax];

                            //iterate over the maximum column used within the sheet
                            for (int col = 0; col <= objWorkSheet.NotEmptyColumnMax; col++)
                            {
                                //check whether the column is merged or not
                                if (objWorkSheet.Cell(row, col).Merged)
                                {
                                    //get the range of merged cell inside the Range Object
                                    Range objRange = objWorkSheet.Cell(row, col).MergedWith;
                                    //read the value from the range object now. Always read the value after applying trim function on it
                                    data[col] = Convert.ToString(objWorkSheet.Cell(objRange.Row, objRange.LeftColumnIndex).Value).Trim();
                                }
                                else
                                    data[col] = Convert.ToString(objWorkSheet.Cell(row, col).Value).Trim();
                            }
                            //after every row call the Add row function to add the data in the DataTable
                            AddRow(data);
                        }
                    }
                    //finally close the document
                    document.Close();
                    DumbDataIntoSql();
                }
                catch (Exception ex)
                {
                    Console.WriteLine(ex.Message);
                }
            }
        }

        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 obj = new Program();
            obj.ReadExcelFile(EXCEL_PATH);
            Console.ReadLine();
        }
    }
}

The final output shown is:

 

You can cross-check in SQL tables also.