How to import excel sheet having merged cells.

developer trilochan
develop...
4 Points
2 Posts

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

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

Share:   fb twitter linkedin
Answers
Rahul Maurya
Rahul M...
Moderator
1170 Points
210 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, Microsoft.office.Interop.Excel.dll. For Microsoft provides, Microsoft.office.Interop.Excel.dll you can follow link https://www.c-sharpcorner.com/UploadFile/17e8f6/reading-merged-cell-from-excel-using-microsoft-office-intero/

For third party DLL you can use EPPlus. it is available on nugget package https://www.nuget.org/packages/EPPlus.  You can follow the link on stack overflow https://stackoverflow.com/questions/17172829/epplus-how-to-traverse-through-every-content-block-cell-or-merged-range-of-a 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
banner

Blog

Active User (2)

 Log In to Chat