I use this project mainly as a learning exercise for DataSet and LINQ.
The background is that I have a report in Excel html format that I read into a .NET 3.5 C# Windows Forms Application. I then display the data in a DataGridView and I also want to calculate some statistics on the data. To do so, I need to add a new column showing the elapsed number of working days between two dates.
In my DataSet, I have two columns, "Open Date" and "Close Date". In Excel I was able to use a formula like "=NETWORDAYS(B7:C7, Z2:Z50) to calculate the number of working days elapsed between the two dates.
Is there an easy way of achieving the same thing in my C# code, adding a new column to my DataTable?
The code needs a bit more polishing, eg. adding some error trapping etc but it shows what I have at the moment.
private void GetDataFromExcel(string fileName)
String connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;data source=" + fileName + ";Extended Properties=HTML Import;";
OleDbConnection dbConnection = new OleDbConnection(connectionString);
string select = @"SELECT * FROM [Table]";
OleDbCommand selectCommand = new OleDbCommand(select, dbConnection);
OleDbDataAdapter dbDataAdapter = new OleDbDataAdapter();
dbDataAdapter.SelectCommand = selectCommand;
DataSet ds = new DataSet();
mainDataGridView.DataSource = ds.Tables;