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