Working with data from an Excel spreadsheet in C#

Question

I have data in an Excel spreadsheet that has 10,000 rows with the following structure:

Country | Region | City        | Latitude | Longitude
-----------------------------------------------------
USA     | CA     | Los Angeles | 34°03′N  | 118°15′W
...

The first three columns effectively constitute a primary key. I would like to load this data into a C# program so that I can have expressions conceptually like the following:

string lat = someDataStructure["USA"]["CA"]["Los Angeles"].latitude;

I would also like to be able to do things like get a list of all latitudes of cities in California.

What would be the best way to load this data from Excel? Would I use Linq for this?

(I cannot use third-party libraries/modules for this task.)

Answer 1

I would suggest using ADO.net to fetch data from Excel sheet into a dataset.
You could do a filter/sort on DataTable OR you can use LINQ as well.

Answer 2

You can load the excel data into a dataset as described here then you could use code like

string strExpr;
string strSort;
strExpr = "country = 'USA' AND Region = 'CA'";
strSort = "City DESC";
DataRow[] foundRows = ds.Table[0].Select(strExpr, strSort, DataViewRowState.Added);

or using linq 2 dataset

var query = from c in ds.Tables[0].AsEnumerable()
            where c.Field<string>("Country") == "USA" &&
            where c.Field<string>("State") == "CA" 
            select c;