Hello,
I am trying without success to import an excel document where cells may or may not contain data.
It appears that the importing process completely skips empty cells rather than import them as such, meaning any kind of index based traversal of the data is impossible..
Could you please advise me on how I can maintain the structure of my excel data to retain empty cells, or maybe give an example of the best practice to shift the excel data into a DataTable or other data representation that can be used.
I am using Documents.Excel 12.1
Thanks,
The loading process does not skip empty cells. They are never written out to the file format to begin with. Actually, if you would like to get all cells, empty or not, an indexed based approach is the way to go. Use a for loop to access rows in order and then the cells of each row in order. The rows and cells which were not loaded from the file will be lazily created and returned from the indexer.
However, if you use a foreach loop to loop over the Rows collection and the Cells collection within each row, you will only loop over the already created items.
OK i removed the foreach loops and replaced with indexed for loops taking the count of rows and rows[0].cells.Count() as the column count, and it works fine now.
It feels like a bit of a dirty/fuzzy workaround though, and would love to see a more transparent and easier to use method of unpacking excel data in a future update, as I find the excel functions incredibly useful and powerful except for this issue..
But its working which is the main thing,
Thanks for your assistance!!
This does not work - rows[index].Cells.Count() returns only the amount of populated cells in the row. How did you resolve this?