search

nissl-lab / toxy

.net text extraction framework
Apache License 2.0
358 stars 107 forks source link

Does a VBA module in Excel treated as a spreadsheet in NPOI or POI? #14

Closed smu86 closed 2 years ago

smu86 commented 7 years ago

I have an Excel file which contains 5 spread sheets out of which: • 2 are hidden (as they are dialog, user interface, kind of sheets DlgView1, DlgView2). • 3 work sheets (WrkSht1, WrkSht2, WrkSht3) are visible with some data containing formulas and external links. Also, the Excel sheet contains a VBA module called Module1.bas with some logic. When I check the number of worksheets using workbook.NumberOfSheets property, it shows 5, but when I iterate through the code for all worksheets present, I get the below results:

  1. Sheet1 = DlgView1 -------> which is hidden
  2. Sheet2 = DlgView2 -------> which is hidden
  3. Sheet3 = Module1 -------> which is hidden --->This is what is confusing!!!!
  4. Sheet4 = WrkSht1 -------> which is visible
  5. Sheet5 = WrkSht2 -------> which is visible It should have been like this instead I suppose:
  6. Sheet1 = DlgView1 -------> which is hidden
  7. Sheet2 = DlgView2 -------> which is hidden
  8. Sheet3 = WrkSht1 -------> which is visible
  9. Sheet4 = WrkSht2 -------> which is visible
  10. Sheet5 = WrkSht3 -------> which is visible I cannot get access to one of the visible worksheet WrkSht3 as the count(= 5) of worksheet ends by this time. Also, when my code sets to play around different cells of worksheet WrkSht1, it will actually be pointing to WrkSht2 and for worksheet WrkSht2, it will actually be pointing to WrkSht3. But WrkSht3 was never initialzed as a worksheet as seen from the above results. For testing purpose, I deleted Module1 and checked, it works fine with count as 5 and considering all the worksheets (DlgView1, DlgView2, WrkSht1, WrkSht2, WrkSht3) to process. Also, to mention, I get an exception thrown while working with this file & not with any others: "Unsupported BOF found of type VBModule" My question is: Does NPOI or POI treat a VBA module in a Excel sheet as a hidden worksheet? • If not, why is the code considering Module1 as worksheet and not the actual visible worksheet WrkSht3 as explained above. • If yes, is there a way to tell code not consider VBA Modules as worksheets and also actual visible worksheets like WrkSht3. If needed, I can send the Excel sheet which I am working on. Thanks
--- Want to back this issue? **[Post a bounty on it!](https://www.bountysource.com/issues/43610918-does-a-vba-module-in-excel-treated-as-a-spreadsheet-in-npoi-or-poi?utm_campaign=plugin&utm_content=tracker%2F621213&utm_medium=issues&utm_source=github)** We accept bounties via [Bountysource](https://www.bountysource.com/?utm_campaign=plugin&utm_content=tracker%2F621213&utm_medium=issues&utm_source=github).