[RESOLVED] Excel import mapping to datagridview

[charlie20]

Hi all,

I have an Excel spreadsheet that I am importing into a datagridview in a winforms application.

Unfortunately the Excel spreadsheet comes from an external source that I have no control over and they often change the column order, headings, etc. All in all I cannot rely on a specific column in the spreadsheet being in a specific place in the order. What I am looking to do is to have an intermediate step where I can specify the excel spreadsheet name, and location, select the specific worksheet and then be able to "map" the Excel columns to specific columns in the datagridview.

Example :-
Column1 in the worksheet maps to Column2 in the datagridview,
Column2 in the worksheet maps to Column4 in the datagridview,
Column3 in the worksheet maps to Column3 in the datagridview,
Column4 in the worksheet maps to Column1 in the datagridview,

I need to "map", or align, the columns as I need the data in a specific order in the datagridview.

I am able to select the spreadsheet and the relevant sheet however I can't find a way to "map", or align, the columns.

Any advice, pointers, or assistance greatly appreciated.

Thanks

Charlie

[mrgr8avill]

As each line of data is read from the file, create a Dictionary<string,string> to hold the data in memory. Assign the key of the dictionary as the column name in the Excel file, and the value as the contents of the cell.

Once the line is loaded, draw it to the datagridview using the Dictionary as your source, probably best using a string array of your column names.

Say you had a DG with columns Name,Address,PhoneNumber
and an excel file with phone number, name, address

pseudoCode example below:

string[] dgvColumnNames = {"Name","Address","Phone"};
...
int rowCount = 0;
foreach(string csvDataLine in ExcelFile){
  Dictionary<string,string> lineData = new Dictionary<string,string>();
  foreach(string column in dataRow){
      lineData.Add(columnHeader,cellvalue);
  }
  for(int i=0;i<dgvColumnNames.Length;i++){
      DataGridView[i,rowCount].Value = lineData[dgvColumnNames[i]];
  }
  rowCount++;
}

You could also build a List<Dictionary<string,string>> to import all rows of Excel data before drawing them to the DGv if you want. The Dictionary class allows you to store key/value pairs with each value looked up by name - regardless of what order it was placed into the dictionary. It is a simple and convenient tool for mapping data in multiple columns, and has the built-in ability to handle re-ordered source columns without muss or fuss.

Just remember that the "lookup" is strongly-typed. So:

Dictionary<string,string> myDict = new Dictionary<string,string>();
myDict.Add("Name","Mrgr8avill");
Console.WriteLine(myDict["name"]); //this will throw a KeyNotFound exception because "name" does not exist in the dictionary

So, for this to work seamlessly, you'd have to name your DataGridiew columns EXACTLY the same as the corresponding Excel column. If the names are different, then you'd have to do a second level of mapping for the column names. A dictionary would probably be good for that, as well. If that is the case for you, let me know and I'll get you an example.

good luck!