Populating DataGridView with Excel data (is there an easy way todatabind with datasource-property?)

[pompair]

Hi,

I've been implementing a program that populates winforms DataGridView
from an excel sheet (.xlsx-file).
The app works ok. Now I'm refactoring it and I've got a feeling that
databinding could be done more elaborately than I'm doing currently.

First I read in the excel file and extract the first row and make it's
columns the header columns in the datagridview...


foreach (string s in CreateRow(xh, columns, 1))
dataGridView1.Columns.Add(s, s);

(CreateRow is a subroutine that return IList<string> by the row number
entere in the last argument)


....and then I add needed amount of rows in one line...

dataGridView1.Rows.Add(rows - 1);

(note that rows-variable holds the number of rows in the excel sheet)

....then I populate the cells in each row manually...

for (i = 0; i <= rows - 1; i++)
{
int j = 0;
foreach (string s in CreateRow(xh, columns, i + 2))
dataGridView1.Rows.Cells[j++].Value = s;
}


This works. But I'm just wondering if it's possible to say:

dataGridView1.DataSource = myMagicListOfExcelData;

???

I could do it if I knew the format of the excel data.
For example if I knew that it contains columns called "Product" and
"Price" I could do some kind of holder-class for them...

// just example
class ProductAndPriceContainer
{
public string Product { get; set; }

public float Price { get; set; }
}

....and then I could list these in an IEnumerable-list and say...

dataGridView1.DataSource = myProductsAndContainersList;

....or I could do an anonymous types to create a class on the fly, but
that too requires mee to know the fields...

var Holder = new {
Product = getfromexcel...;
Price = getfromexcel...;
}


But I want to make a dynamic solution, not fixed to predetermined
fields.
Any ideas?

 

[Marc Gravell]

The simplest option here might be to create a DataTable, add columns
and then rows?

I know of a few other tricks for binding to dynamic data, but the
above is very simple...

Example code (different context): http://tinyurl.com/ypr3yw

Marc