0

i am trying to read excel cell values from excel sheets and i am doing this by opening the excel files and reading values using by two loops by rows and columns.

but i am facing problem is that it's taking long time to process all the excel files. is there any best way to read excel files and bind to dataset.

i have different kinds of files in different folders(each folder contains 100 excel files) and for each sheet i have defined sheetname,rowstart ,rowend, headerstart, headerend to use in loop for rows and columns and i have loaded this into datatable. while processing the excel files , i have filtered the above datatable based sheetname and then start looping based on row start, row end and columnstart and columnend.

is there any tool where i can pass rowstart , rowend, columnstart,columnend to read excel cell values.

Please help me on this.

Thanks Santosh

user1818042
  • 75
  • 1
  • 11
  • afaik the longest part is the conversion of the cell content. excel just reads the first 50 rows and "guesses" the type. so if you want to make it faster, then you should use specialized libaries that are more typed – Radinator Oct 10 '16 at 14:30
  • perhaps converting everything to csv and/or upload to a database could help http://stackoverflow.com/a/1860269/335905 – celerno Oct 10 '16 at 14:36

1 Answers1

2

Without seeing code it's impossible to give a definite answer, but the biggest performance problem I see when reading excel files is looping through ranges and getting each cell's value individually.

It's much more efficient to call Value on the range, which creates an array in c#, and then loop through the array. The same is true for setting values - create an array with all of the values for the range and set the Value property of the range to the array.

D Stanley
  • 149,601
  • 11
  • 178
  • 240