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
}

Error "Sub reports within table/matrix cells are ignored" when exporting SRS Reports to Excel

If you make an SRS report that uses Data table and has sub-reports included, you will see the below error when the report is exported to Excel.

Export to PDF format works just fine but when you export to Excel the excel file will not show the details included in the sub-report. Instead the sub-report will be replaced with a single line error "Sub reports within table/matrix cells are ignored".

To be able to export SRS reports using Sub-reports to Excel, it is advisable to use List controls instead of Data Table. Once you have made this little change, the export to excel is just perfect. Infact it will automatically even freeze the rows in the page header so that when you scroll through the data that was included in the report header stays static.

Hope this helps!