This article describes how to read an Excel file that has merged columns within it.
IntroductionIt'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:
Programming C# 5.0