Jun 13, 2011 at 9:13 PM
Edited Jun 13, 2011 at 9:31 PM
Converting an existing C# routine from using the Interop Excel read over to the Excel Data Reader to eliminate the memory leaks, but have run into a problem that is perplexing.
Have an excel xlsx workbook with 15 sheets.
TableName DataReader row count Excel Tabname Actual row count in workbook
Interop Excel rowcount
Master 20 Master
623113845 131 623113845
727255817 181 72755817
77255878 116 77255878
77255902 91 77255892
925832187 89 825732176
.. All of the remaining sheets have the correct DataReader row count to match actual Excel and Interop Excel functionality.
The table names returned in Tables.TableName sheet names are as expected on the sheets, HOWEVER upon inspecting the contents of the rows and columns for those in error I see that in fact the contents within Data Reader TableName "623113845" reflect what
is expected to be in "77255878". Somehow, the data reader function is associating the wrong Excel tab name with the wrong set of data, but only for the sheets "623113845", "72755817", and "77255878" - all the rest have the appropriate row counts and
data contents contained in them. Renaming the tabs produces the same results. Moving the first wrong sheet "623113845" to the last position in the workbook then produces the results where "Master" is 20 rows, "727255817" is correct at 116 rows,
"77255878" is correct at 131 rows, but "77255902" is incorrect at 89 rows (s/b 91) and "925832187" is incorrect at 121 rows (the next sheets actual row count).
So, I recreated the entire file in excel using copy paste-as-value and saved. Appears now that all the row counts are correct. So I move the order of one of the tabs within the workbook, and re-run the Data Reader and guess what- the problem
reappears. The problem appears to be reading some internal excel structure that is incorrectly associating the tab name with the row count.
I would really like to use this utility as it is much faster than using Interop Excel procedures, however this mixing up of the data with the tabs makes it problematic.