Extracting data out of an Excel file is not that hard. The only problem you might face is creating a valid connection string. Below is a simple Excel file that I will load into a webform.
The file has four columns and I have left the first cell of the first column blank (on purpose). Now, let's see how to load it on the page.
public class ExcelDataAccess
{
private static string connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Timesheet.xls;Extended Properties=Excel 8.0;";
public static List<Timesheet> GetTimesheets()
{
List<Timesheet> timesheets = new List<Timesheet>();
using (OleDbConnection myConnection = new OleDbConnection(connectionString))
{
string query = "SELECT * FROM [Sheet1$]"; // Sheet1 represents the sheet in the excel file
OleDbCommand myCommand = new OleDbCommand(query, myConnection);
myConnection.Open();
IDataReader reader = myCommand.ExecuteReader();
while (reader.Read())
{
Timesheet timesheet = new Timesheet()
{
CheckInTime = reader["checkintime"] == DBNull.Value ? DateTime.Parse("1/1/1") : Convert.ToDateTime(reader["checkintime"]),
CheckOutTime = Convert.ToDateTime(reader["checkouttime"]),
HoursWorked = (double)(reader["hoursworked"]),
Pay = (double)(reader["pay"]),
Rate = (double)reader["rate"]
};
timesheets.Add(timesheet);
}
return timesheets;
}
}
}
The Timesheet is a simple class that expresses the Timesheet domain.
public class Timesheet
{
private DateTime _checkInTime;
private DateTime _checkOutTime;
private double _rate;
private double _hoursWorked;
private double _pay;
public DateTime CheckInTime
{
get { return _checkInTime; }
set { _checkInTime = value; }
}
public DateTime CheckOutTime
{
get { return _checkOutTime; }
set { _checkOutTime = value; }
}
public double Rate
{
get { return _rate; }
set { _rate = value; }
}
public double HoursWorked
{
get
{
TimeSpan t = _checkOutTime.Subtract(_checkInTime);
double s = ((double)t.Hours) + ((double)t.Minutes / 100);
return s;
}
set { _hoursWorked = value; }
}
public double Pay
{
get
{
return (HoursWorked * Rate);
}
set { _pay = value; }
}
}
The problem comes that what do you want to display when the date is DbNull. Here is the page level method that formats the datetime displayed on the screen.
public const int NULL_YEAR = 2001;
protected string FormatTime(DateTime datetime)
{
if (datetime.Year == NULL_YEAR) return String.Empty;
return datetime.ToString("t");
}
I am using YEAR 2001 as the DbNull year.
<asp:TemplateField HeaderText="Check In Time">
<ItemTemplate>
<%# FormatTime((DateTime)Eval("checkintime")) %>
</ItemTemplate>
</asp:TemplateField>
Now, when the date is DbNull then a blank cell is displayed as shown below:
