Advice or different approch.

[kss444]

I have this c# app. that opens an excel file and reads information from the spreadsheet then takes this info and sticks it in a datatable. Then from there I can just loop through my rows and get my infomation back. dRow["Direction"].


It's all done in a loop. Problem is this is taking a very long time to read through.
I found this line of code. Array Myvals = workSheet.UsedRange.Cells.Value2 as Array;

That works super fast, but all my values are in a multi dimed array.
But it contains all the information, plus all the null rows exc..

I would like to take this information and put it into a datatable, so I would not have to change much in the application.

FYI, in the excel file the 1st row is a headings row, and there can be duplicates but the duplicates I don't need to worry about. So in my datatable the first array elements would have to be columns, then move to the rows, and if its a duplicate I can skip it or just tack on an index number. Also the users can move the headers around so I cannot rely on R1C1.

Thanks for any help or advice on another way to approch this,
KSS

Ordinary Programmer

 

[jmeckley]

is the data tabular[tt]
column1 column2 column3
a b c
1 2 3
[/tt]
or is it formatted for the user[tt]
label: data
labe: data
data data
data
header1 header2
a b
c d
etc...
[/tt]
if it's the latter than you are stuck using Excel Interops extracting the data. however if it's the former than you have some options to extract the data.

ADO.Net can query an excel spreadsheet if the data is tabular and consistent. you can also use FileHelper to easily extract tabular data.

I have also found that Rhino.Etl make easy work to extract data and preform bulk operations. something else to consider is have a background service to manage data extraction and place the data in a defined and consistent storage location. like extracting the data from excel and putting it in a database. then have your application query this consistent data store. this way the UI doesn't need to wait for data transformation to complete... it's already done.

Jason Meckley
Programmer
Specialty Bakers, Inc.

faq855-7190
faq732-7259

 

[kss444]

I believe I will have to use excel interops. What needs to be done is that the app will read in this information send it to a webservcie where a 3rd party will take the info. and return use shipping rates. The those rates will need to be attached to the end rows/col in the excel file.

When I use the above format to get my array that is.
myvals[1,1] would equal my heading of row1 cell1.
myvals[2,1]... would be all excels row 2 cells.
myvals[3,1]... would be all excels row 3 cells.

Currently there are only 72 rows with headers, but using this brings back 196 rows. And the rest are nulls.
But this does runs in the blink of any eye.

So if I can get this info into a datatable or something where I can just use the header names to get the information.

I just had an idea about using LINQ to objects.

Thanks hope this makes sense,
KSS

Ordinary Programmer

 

[kss444]

Ok now I am able to read my values from excel and stick them into my datatable, I am now passing needed information (Weight, class type ect..) To a web service that takes the numbers and returns rating information.

I now need to append these rating values to the spreadsheet.

Has anyone done this? Or can provide any advice on how to do this?

I have thought about appending to the sheet, or reading the new values into my datatable then replace the current active sheet with a datatable to excel way.

Thanks,
KSS

Ordinary Programmer