How to import excel sheet having merged cells.

developer trilochan
4 Points
2 Posts

Is it possible to import an excel spreadsheet which contains some merged cells.

Views: 11342
Total Answered: 1
Total Marked As Answer: 0
Posted On: 16-Feb-2017 11:39

Share:   fb twitter linkedin
Rahul Maurya
Rahul M...
1402 Points
236 Posts

Yes, it is possible but when you want to read an Excel file with merged cells then the way to do that is a little different. Using OLEDB we 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. You can do this either by use a third-party DLL or you can use one that Microsoft provides, For Microsoft provides, you can follow link

For third party DLL you can use EPPlus. it is available on nugget package  You can follow the link on stack overflow as

var ws = _excel.Workbook.Worksheets["myTemplateWorksheet"];
var dim = ws.Dimension;
// first loop through all non-merged cells
for (int r = dim.Start.Row; r <= dim.End.Row; ++r)
for (int c = dim.Start.Column; c <= dim.End.Column; ++c)
if (ws.Cells[r, c].Merge) continue;
string s = GetRangeText(ws.Cells[r, c]);
if (string.IsNullOrEmpty(s)) continue;
if (s.StartsWith("^^"))
ProcessCell(ws.Cells[r, c], s.Substring(2));
// then loop through all merged ranges
foreach (string mc in ws.MergedCells)
string s = GetRangeText(ws.Cells[mc]);
if (string.IsNullOrEmpty(s)) continue;
if (s.StartsWith("^^"))
ProcessCell(ws.Cells[mc], s.Substring(2));
private string GetRangeText(ExcelRangeBase range)
var val = range.Value;
string s = val as string;
if (string.IsNullOrEmpty(s))
object[,] arr = val as object[,];
if (arr != null && arr.GetLength(0) > 0 && arr.GetLength(1) > 0)
s = arr[0, 0] as string;
if (string.IsNullOrEmpty(s) && val != null)
s = val.ToString();
return s;


Posted On: 16-Feb-2017 22:33
 Log In to Chat