Sharp2b
asked on
How to calculate NETWORKDAYS in C#
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.
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);
dbConnection.Open();
string select = @"SELECT * FROM [Table]";
OleDbCommand selectCommand = new OleDbCommand(select, dbConnection);
OleDbDataAdapter dbDataAdapter = new OleDbDataAdapter();
dbDataAdapter.SelectCommand = selectCommand;
DataSet ds = new DataSet();
dbDataAdapter.Fill(ds, "XLData");
mainDataGridView.DataSource = ds.Tables[0];
dbConnection.Close();
}
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Exactly what I was looking for. Nice, short and easy to follow. It was also a good example to learn from as I still see myself as a beginner in this area. Hence it's also valuable to me for future projects.
ASKER
Thank you so much!
While getting ready to go to work this morning, I was thinking about how to implement a function like this as I figured there was no pre-built function. My thoughts was in line with this but one thing I was not immediately aware of was the Contains() method. I would probably have found it eventually but now you probably saved me at least 1/2 day of work. Since this is more of a "hobby" project, I can't spend too much time on it, at least not during the day. ;-) But now there is a good chance that I will be able to finish it off.