Geeks With Blogs
AzamSharp Some day I will know everything. I hope that day never comes.

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.

blank_excel_cell

 

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:

blank_excel_cell_2

Posted on Wednesday, April 9, 2008 2:31 PM | Back to top


Comments on this post: Extracting and Displaying Excel Data on ASP.NET Page

# re: Extracting and Displaying Excel Data on ASP.NET Page
Requesting Gravatar...
Thanks,

Can you please help me how to delete first column in excel sheet using asp.net

Left by KumarVelu on May 17, 2008 12:37 AM

# re: Extracting and Displaying Excel Data on ASP.NET Page
Requesting Gravatar...
when we trying to exract the data from Exported Excel(Other than the created excel) it is giving error as External table is not in the expected format.
Left by Swamy on Jun 04, 2008 11:12 PM

Your comment:
 (will show your gravatar)


Copyright © Mohammad Azam | Powered by: GeeksWithBlogs.net