Row counts not consistent with actual xlsx file

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                        20                                          20

623113845                     131                                         623113845                  181                                         181

727255817                     181                                         72755817                    116                                         116

77255878                       116                                         77255878                    131                                         131

77255902                         91                                        77255892                     91                                           91

925832187                       89                                        825732176                    89                                           89

..  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.

Jan 8, 2013 at 12:09 PM

I also tried this and gave up because the data was being read from the wrong tabs.

When I examined the DataSet, all the data from all the tabs was present, BUT the correlation between the Names of DataTables and Tab names had been mangled.

It did work once on a brand new workbook, but as soon as I edited it to add more tabs it went bang...

Jan 11, 2013 at 5:01 PM

@mike - a couple of us are trying to reboot this project. Did this problem you describe happen recently? And was it with the latest code from source control?