Click here to Skip to main content
15,887,434 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
Hello everyone,
I have one excel file which i want to import in Grdiview in asp.net c#.
I got the records in gridview but some dates does not shows in gridview.
my code is.
C#
if (FileUpload1.HasFile)
       {
           ddlParty.Enabled = true;
       }
       String strConnection = "ConnectionString";
       string connectionString = "";
       if (FileUpload1.HasFile)
       {
           string fileName = Path.GetFileName(FileUpload1.PostedFile.FileName);
           string fileExtension = Path.GetExtension(FileUpload1.PostedFile.FileName);
           string fileLocation = Server.MapPath("~/App_Data/" + fileName);
           FileUpload1.SaveAs(fileLocation);
           if (fileExtension == ".xls")
           {
               connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +
                 fileLocation + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";
           }
           else if (fileExtension == ".xlsx")
           {
               connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" +
                 fileLocation + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
           }
           OleDbConnection con = new OleDbConnection(connectionString);
           OleDbCommand cmd = new OleDbCommand();
           cmd.CommandType = System.Data.CommandType.Text;
           cmd.Connection = con;
           OleDbDataAdapter dAdapter = new OleDbDataAdapter(cmd);
           DataTable dtExcelRecords = new DataTable();
           con.Open();
           DataTable dtExcelSheetName = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

           string getExcelSheetName = dtExcelSheetName.Rows[0]["Table_Name"].ToString();
           cmd.CommandText = "SELECT * FROM [" + getExcelSheetName + "]";
           dAdapter.SelectCommand = cmd;
           dAdapter.Fill(dtExcelRecords);

           gvExcelFile.DataSource = dtExcelRecords;
           gvExcelFile.DataBind();
       }

Here is my Excel file LINK

So please help me to solve my problem.

What I have tried:

I have tried code and changed the date format in excel file with Format Cells so many time and with so many types. But didn't get solution.
Posted
Updated 26-Aug-17 3:53am
Comments
Richard MacCutchan 26-Aug-17 8:56am    
Which are the fields that are not showing, and what are their values in the Workbook?
Veeshal Mali 26-Aug-17 9:22am    
PODate and DeliverySchedule these fields are not showing and the values are like this "18/08/2016" in which the date has more than 12.
Richard MacCutchan 26-Aug-17 9:29am    
More than 12 what? Please provide complete details of your problem.
Richard MacCutchan 26-Aug-17 9:45am    
OK I think I know what is happening. Your spreadsheet has some date fields that are quoted text, e.g. "18/08/2016", including the double quote characters. It would appear that OLEDB is ignoring these fields for some reason. I will look a little closer.
Veeshal Mali 27-Aug-17 3:02am    
han yes sir, when date like 7/7/2017 or 11/8/2017 or 12/8/2017 in excel it will pickup and show in grid view but when when date exceed the date more than 12 like 13/08/2017 or 18/08/2017 it will not pick up and show in gridview

1 solution

You need to set the parameter "IMEX=1" in your connection string as described in Working with MS Excel(xls / xlsx) Using MDAC and Oledb[^] to ensure that OLEDB does not ignore fields that are not dates.
 
Share this answer
 
Comments
Veeshal Mali 27-Aug-17 3:08am    
eeeuuuuu..... it's working.....
Thanks a lot sir... its working... when I put "IMEX=1" in my connection string rather than "IMEX=2"...

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