When a sheetname isn't found, the type provider defaults to a non-deterministic first sheet with a different name #77
Description
Description
In an Excel file with Sheet1, Sheet2, Sheet3, assuming each has a different type, and assuming the sheetname is given as an argument to the constructor: if the given sheetname is not found at runtime, it is silently ignored and the internally first sheet is used instead.
Note that the "first sheet" is the first you've created. When you move sheets to the right in Excel, the display order changes, but not the internal index of the sheet. The type provider will select the first sheet by index when the sheetname is not found.
Repro steps
- Create an Excel sheet with three different types.
- Create some code to read these and to specify the sheetname dynamically
- Delete a sheet that isn't the first sheet
Expected behavior
Either an error or an empty set.
Actual behavior
The type provider takes the first sheet that's programmatically (i.e., inside the XML) the first sheet by index. If this type doesn't match, an error will occur. If the type does match, this can lead to unpredictable behavior, like the same sheet being read twice.
Known workarounds
I've researched this with my colleagues but couldn't find a workaround.
Some semblance of a workaround can be created by using the cell-gettors (the dynamic properties from the type provider) and inspect whether the result gives null, in which case either the sheet is empty, the column is absent, or the wrong sheet is loaded.
Related information
Observed with the most recent version of this repo.
When I debug this and inspect the internal fields, you can see that, upon instantiation of the type provider, the internal sheetname is set to the one provided in the constructor. However, the actual data can be something totally different and from a different sheet, because it can silently take the first-sheet-by-index.