Reading Excel workbooks using GetOleDbSchemaTable returns a sheet with _ suffix

We have a CRM add-on for Importing Price Lists into CRM. For this tool, we expect the details to be provided in an Excel sheet. The tool reads the excel workbook provided and imports the prices.

Once we had an issue with one of our customers complaining of the data successfully being imported, yet an error message is displayed 'Sheet1$'_ not found. What was intriguing was the error could only be replicated with the sheet that are customer had provided us. If we were to create a new Excel workbook and provide it for import, we would receive no errors :( It was a classic example of "it works on my machine..." )

After researching into this issue further, we found the reason.

The tool used the following line of code to read all sheets from the workbook

dtTables = objExcelConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

This returned 2 data tables, one with the name 'Sheet1$' and the other named 'Sheet1$'_.

the first sheet 'Sheet1$' is the normal naming convention when you read an excel workbook. But where did the second sheet 'Sheet1$'_ come from?? If you open the excel workbook, you will find only one sheet in the workbook named 'Sheet1'.

The second sheet 'Sheet1$'_ is a hidden sheet that Excel creates each time you filter the records on a sheet. It will create a hidden copy of the sheet. This is the sheet that the program tries to read through the code.

To fix this problem we added the following code to catch such sheets
if(SheetName.EndsWith("_"))
{
do nothing;
}
else
{
do operation
}