Question Excel data import mapped to datagridview columns

[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

 

[jmcilhinney]

There are various ways that this could be done but the easiest option is probably to simply populate a DataTable with the data, bind that to the grid and let it create the columns in the default order, then change the DisplayIndex of the columns to display them in the order you want. For instance, after bind the data you could do this:

listBox1.DisplayMember = "HeaderText";
listBox1.Items.AddRange(dataGridView1.Columns.Cast<object>().ToArray());

You could then provide the facility to move the columns up and down with the ListBox. Finally, to reorder the columns in the grid, do this:

for (int i = 0; i < listBox1.Items.Count - 1; i++)
{
    ((DataGridViewColumn) listBox1.Items[i]).DisplayIndex = i;
}

Note that that will NOT change the order of the columns in the grid's Columns collection. It will only change the order in which they are displayed in the UI.

 

[charlie20]

Many thanks jmcilhinney - that worked a treat.