Click here to Skip to main content
14,970,849 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/[^]
   
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];
   
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