Click here to Skip to main content
15,881,882 members
Please Sign up or sign in to vote.
4.00/5 (2 votes)
See more:
Note It is windows application.
Importing Excel Data into Datagridview using c#

for that my code as follows;
C#
private void Btn_Import_Click(object sender, EventArgs e)
          {
  try
              {
  FileStream stream = File.Open(@"E:\Projects\Sheet1.xls", FileMode.Open, FileAccess.Read);
  IExcelDataReader excelReader = ExcelReaderFactory.CreateBinaryReader(stream);
   DataSet result = excelReader.AsDataSet();
  excelReader.Close();
                  datagridView.DataSource = result.Tables[0];

              }
              catch (Exception ex)
              {
                  MessageBox.Show(ex.ToString(), "Error in Import");
                  return;
              }
              finally
              {
                  GFun.OleDbCon.Close();
              }
          } 


when i click the import button,the excel data is retrieving but Date is not retrieving in the correctly.


In excel records as follows


Date     Session     Day

15-APR     1         MOn
           2
           3
           4

16-APR     1         Tue
           2
           3
           4



IN Datagridview the excel records are retrieving But Date is not retrieving correct

In Datagridview records as follows(Importing From Excel)


Date     Session     Day

 41379      1         MOn
            2
            3
            4

 41380      1         Tue
            2
            3
            4


Please help me.

what is the problem.
Regards,
Narasiman P.
Posted
Updated 2-Jun-13 21:48pm
v2

This is because of the way Excel stores it's date data. You can demonstrate this in an empty excel workbook ... type 41379 into a cell, then use Format, Cells, Date and that number will be displayed as 15/04/2013.
You could try changing the spreadsheet contents to text format (not recommended), refer to the documentation and/or code for ExcelReaderFactory or use an alternative method to load the data from excel. This link has been recommended on several other posts http://codehill.com/2009/01/reading-excel-2003-and-2007-files-using-oledb/[^]
 
Share this answer
 
Comments
Maciej Los 3-Jun-13 4:47am    
Yes, Excel stores date data type as numeric values (long data type). That's why convertion <cpde>CDate(41379) give as output proper date value 2013/04/15. If i'm not wrong, to convert 41379 to datetime, need to add that no. of days to 1900/01/01 or 1899/12/31;)
Example:
int intDate = 41379;
DateTime d = DateTime.Parse("1899/12/31");
d= d.AddDays (intDate);
Console.WriteLine("date is: {0}", d.ToString());

BTW: A 5!
Try this brother...Work for me...


OleDbConnection Connection = new OleDbConnection(source);
Connection.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};" + "Extended Properties=\"Excel 12.0;HDR={1};IMEX=2\"", filePath, header ? "Yes" : "No";

OleDbCommand command = new OleDbCommand("SELECT * Column1, Column2 FROM [Sheet1$]", Connection);
DataSet dsTest = new Datasets();
OleDbDataAdapter adapter = new OleDbDataAdapter(command);
adapter.Fill(dsTest);
dataGridView1.DataSource = dsTest.Tables[0];
 
Share this answer
 
v2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900