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.

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.

excel file

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:
  1. create table Fruits  
  2. (  
  3. FruitId int identity(1,1)not null,  
  4. FruitName varchar(30),  
  5. Constraint pkFruitId primary key(FruitId))  
  6.   
  7. create table Benefits  
  8. (  
  9. BenefitID int identity(1,1) not null,  
  10. BenefitName varchar(200)not null,  
  11. constraint pkBenefitId primary key(BenefitID)  
  12. )  
  13.   
  14. create table FruitBenefit  
  15. (  
  16. FruitBenefitID int identity(1,1)not null,  
  17. FruitId int not null,  
  18. BenefitId int not null,  
  19. constraint fkFruitId foreign key(FruitID)references Fruits(FruitID),  
  20. 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.

  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.Linq;  
  4. using System.Text;  
  5. using System.Data;  
  6. using System.Data.SqlClient;  
  7. using Bytescout.Spreadsheet;  
  8.   
  9. namespace ExcelWithMergedCell  
  10. {  
  11.     class Program  
  12.     {  
  13.         SqlConnection con;  
  14.         SqlDataAdapter da;  
  15.         DataTable dtFruits, dtBenefits, dtFruitBenefits;  
  16.         DataSet dsTemp;  
  17.         SqlCommand cmd;  
  18.         const string DBCon = "Server=VISHALG\\SQLEXPRESS;Initial Catalog=Practice;Integrated Security=true";  
  19.         const string EXCEL_PATH = "D:\\Vishal\\Mvc\\ExcelWithMergedCell\\DummyData.xls";  
  20.   
  21.         private DataColumn CreateIdentityColumn(string columnName = "SrNo")  
  22.         {  
  23.             DataColumn dc = new DataColumn(columnName);  
  24.             dc.AutoIncrement = true;  
  25.             dc.AutoIncrementSeed = dc.AutoIncrementStep = 1;  
  26.             return dc;  
  27.         }  
  28.   
  29.         public void ReadExcelFile(string filePath, bool isHDR = true)  
  30.         {  
  31.             dsTemp = new DataSet();  
  32.             if (dsTemp.Tables.Count == 0)  
  33.             {  
  34.                 dtFruits = dsTemp.Tables.Add(TableNames.Fruits.ToString());  
  35.                 dtFruits.Columns.Add(CreateIdentityColumn("FruitId"));  
  36.                 dtFruits.Columns.Add("FruitName");  
  37.   
  38.                 dtBenefits = dsTemp.Tables.Add(TableNames.Benefits.ToString());  
  39.                 dtBenefits.Columns.Add(CreateIdentityColumn("BenefitID"));  
  40.                 dtBenefits.Columns.Add("BenefitName");  
  41.   
  42.                 dtFruitBenefits = dsTemp.Tables.Add(TableNames.FruitBenefit.ToString());  
  43.                 dtFruitBenefits.Columns.Add(CreateIdentityColumn("FruitBenefitID"));  
  44.                 dtFruitBenefits.Columns.Add("FruitId");  
  45.                 dtFruitBenefits.Columns.Add("BenefitId");  
  46.   
  47.             }  
  48.             if (isHDR)  
  49.             {  
  50.                 try  
  51.                 {  
  52.                     Spreadsheet document = new Spreadsheet();  
  53.   
  54.                     //load the file from the path. make sure that the file is not opened in excel else it will give error  
  55.                     document.LoadFromFile(filePath);  
  56.   
  57.                     //get the total no of worksheets within a workbook  
  58.                     int totalWorkSheet = document.Workbook.Worksheets.Count;  
  59.   
  60.                     //to iterate over multiple worksheet within a workbook  
  61.                     for (int workSheetCounter = 0; workSheetCounter < totalWorkSheet; workSheetCounter++)  
  62.                     {  
  63.                         Worksheet objWorkSheet = document.Workbook.Worksheets[workSheetCounter];  
  64.   
  65.                         //iterate over the maximum row used withing the sheet.   
  66.                         //Iterate From row 1 because your file contains header row  
  67.                         for (int row = 1; row <= objWorkSheet.UsedRangeRowMax; row++)  
  68.                         {  
  69.                             //since we known the exact columns in our excel we can directly initalize the array to that no  
  70.                             object[] data = new object[2];  
  71.   
  72.                             //if you don't known then use the following code  
  73.                             //object[] data = new object[objWorkSheet.NotEmptyColumnMax];  
  74.   
  75.                             //iterate over the maximum column used within the sheet  
  76.                             for (int col = 0; col <= objWorkSheet.NotEmptyColumnMax; col++)  
  77.                             {  
  78.                                 //check whether the column is merged or not  
  79.                                 if (objWorkSheet.Cell(row, col).Merged)  
  80.                                 {  
  81.                                     //get the range of merged cell inside the Range Object  
  82.                                     Range objRange = objWorkSheet.Cell(row, col).MergedWith;  
  83.                                     //read the value from the range object now. Always read the value after applying trim function on it  
  84.                                     data[col] = Convert.ToString(objWorkSheet.Cell(objRange.Row, objRange.LeftColumnIndex).Value).Trim();  
  85.                                 }  
  86.                                 else  
  87.                                     data[col] = Convert.ToString(objWorkSheet.Cell(row, col).Value).Trim();  
  88.                             }  
  89.                             //after every row call the Add row function to add the data in the DataTable  
  90.                             AddRow(data);  
  91.                         }  
  92.                     }  
  93.                     //finally close the document  
  94.                     document.Close();  
  95.                     DumbDataIntoSql();  
  96.                 }  
  97.                 catch (Exception ex)  
  98.                 {  
  99.                     Console.WriteLine(ex.Message);  
  100.                 }  
  101.             }  
  102.         }  
  103.   
  104.         private void AddRow(object[] data)  
  105.         {  
  106.             int check = 0;  
  107.             for (int tableCounter = 0; tableCounter < dsTemp.Tables.Count; tableCounter++)  
  108.             {  
  109.                 TableNames tableName = (TableNames)Enum.Parse(typeof(TableNames), dsTemp.Tables[tableCounter].TableName);  
  110.                 switch (tableName)  
  111.                 {  
  112.                     case TableNames.Fruits:  
  113.                         check = dtFruits.AsEnumerable().Where(x => x["FruitName"].ToString().Equals(data[0].ToString(), StringComparison.InvariantCultureIgnoreCase)).Count();  
  114.                         if (check == 0 && !string.IsNullOrEmpty(Convert.ToString(data[0])))  
  115.                         {  
  116.                             DataRow dr = dtFruits.NewRow();  
  117.                             dr["FruitName"] = data[0].ToString();  
  118.                             dtFruits.Rows.Add(dr);  
  119.                         }  
  120.                         break;  
  121.                     case TableNames.Benefits:  
  122.                         check = dtBenefits.AsEnumerable().Where(x => x["BenefitName"].ToString().Equals(data[1].ToString(), StringComparison.InvariantCultureIgnoreCase)).Count();  
  123.                         if (check == 0 && !string.IsNullOrEmpty(Convert.ToString(data[1])))  
  124.                         {  
  125.                             DataRow dr = dtBenefits.NewRow();  
  126.                             dr["BenefitName"] = data[1].ToString();  
  127.                             dtBenefits.Rows.Add(dr);  
  128.                         }  
  129.                         break;  
  130.                     case TableNames.FruitBenefit:  
  131.                         int fruitID = dtFruits.AsEnumerable().Where(x => x["FruitName"].ToString().Equals(data[0].ToString(), StringComparison.InvariantCultureIgnoreCase)).Select(x => Convert.ToInt32(x["FruitID"].ToString())).FirstOrDefault();  
  132.                         int benefitID = dtBenefits.AsEnumerable().Where(x => x["BenefitName"].ToString().Equals(data[1].ToString(), StringComparison.InvariantCultureIgnoreCase)).Select(x => Convert.ToInt32(x["BenefitID"].ToString())).FirstOrDefault();  
  133.                         check = dtFruitBenefits.AsEnumerable().Where(x => Convert.ToInt32(x["FruitId"].ToString()) == fruitID && Convert.ToInt32(x["BenefitID"].ToString()) == benefitID).Count();  
  134.                         if (check == 0 && fruitID != 0 && benefitID != 0)  
  135.                         {  
  136.                             DataRow dr = dtFruitBenefits.NewRow();  
  137.                             dr["FruitID"] = fruitID;  
  138.                             dr["BenefitID"] = benefitID;  
  139.                             dtFruitBenefits.Rows.Add(dr);  
  140.                         }  
  141.                         break;  
  142.                 }  
  143.             }  
  144.         }  
  145.   
  146.         private void DumbDataIntoSql()  
  147.         {  
  148.             try  
  149.             {  
  150.                 int rowsAffected = 0;  
  151.                 Console.WriteLine("Dumping Data into SQL Tables\n\n");  
  152.                 con = new SqlConnection(DBCon);  
  153.                 con.Open();  
  154.                 string query = string.Empty;  
  155.                 for (int i = 0; i < dsTemp.Tables.Count; i++)  
  156.                 {  
  157.                     rowsAffected = 0;  
  158.                     string colNames = string.Join(",", dsTemp.Tables[i].Columns.Cast<DataColumn>().Where(x => x.AutoIncrement == false).Select(x => x.ColumnName).ToArray<string>());  
  159.                     string[] arr = colNames.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries);  
  160.                     query = "insert into " + dsTemp.Tables[i].TableName + "(" + colNames + ") values(";  
  161.                     for (int col = 0; col < arr.Length; col++)  
  162.                     {  
  163.                         if (col != arr.Length - 1)  
  164.                             query += "@" + arr[col] + ",";  
  165.                         else  
  166.                             query += "@" + arr[col] + ")";  
  167.                     }  
  168.                     cmd = new SqlCommand(query, con);  
  169.                     for (int row = 0; row < dsTemp.Tables[i].Rows.Count; row++)  
  170.                     {  
  171.                         for (int col = 0, arrCounter = 0; col < dsTemp.Tables[i].Columns.Count; col++)  
  172.                         {  
  173.                             if (!dsTemp.Tables[i].Columns[col].AutoIncrement)  
  174.                             {  
  175.                                 cmd.Parameters.AddWithValue("@" + arr[arrCounter], dsTemp.Tables[i].Rows[row][col].ToString());  
  176.                                 arrCounter++;  
  177.                             }  
  178.                         }  
  179.                         rowsAffected += cmd.ExecuteNonQuery();  
  180.                         cmd.Parameters.Clear();  
  181.                     }  
  182.                     Console.WriteLine("{0} Records Affected For Table: \"{1}\"", rowsAffected, dsTemp.Tables[i].TableName);  
  183.                 }  
  184.                 con.Close();  
  185.                 Console.WriteLine("Press any key to Terminate");  
  186.             }  
  187.             catch (Exception ex)  
  188.             {  
  189.                 Console.WriteLine(ex.Message);  
  190.             }  
  191.         }  
  192.   
  193.         static void Main(string[] args)  
  194.         {  
  195.             Program obj = new Program();  
  196.             obj.ReadExcelFile(EXCEL_PATH);  
  197.             Console.ReadLine();  
  198.         }  
  199.     }  

The final output shown is:
 
output

You can cross-check in SQL tables also.