Reading one & Update some other Excel with c#

1
votes

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:

  1. 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?
  2. I want only active area to be returned. Currently, I see so many blank columns/rows. I have to manually handle it.
  3. I tried filtering excel output based on one of column value but didn't work.
  4. Is there any way to discard hidden columns?
  5. 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?

1
c#excelwinformsformatoledb

1 Answers

0
votes
  1. What you want to do is use a schema.ini file, here is a sample:
[ABC_20171128.csv]
Format = Delimited(|)
CharacterSet=ANSI
ColNameHeader=True
DateTimeFormat=dd MM yyyy hh:nn:ss

Col1 = Date DateTime
Col2 = AccountID Text
Col3 = SubAccount Text
Col4 = MarketCode Text
Col5 = SecurityCode Text
Col6 = Units Single

Put the schema.ini file in the same directory as the file you're reading.

More info: Missing column of Dates in CSV file using Schema.ini file

  1. Yes manually handle it or dont use OleDB use Interop.

  2. MCVE with a new question.

  3. You'll need interop to work out which columns are hidden. Alternatively you will know which ones are hidden to discard.

  4. The Schema.ini should do the trick for column headers.