I am developing a small winform utility to read one excel & update other excel.
I am trying to read source excel file (which has several hidden columns & rows) using OLEDB. I am able to do it and get output in Datatable but facing few challenges below:
- Sheet has columns with numbers, dates and string. After reading through OLEDB, all numbers get converted to dates or vice versa. I don't want this to happen. I have read several solutions like setting IMEX to 1 in connection string but nothing worked. How can I fix it?
- I want only active area to be returned. Currently, I see so many blank columns/rows. I have to manually handle it.
- I tried filtering excel output based on one of column value but didn't work.
- Is there any way to discard hidden columns?
- Once I load datatable, I see column appearing as F1, F2.. and so on. Yes, source excel have columns starting from row 4th. How can I retain those names in datatable?
Once I read source excel, I have to run some logic and update other excel row by row or even column by column. I have to retain formatting as well. What would be best to update other excel?