Click here to Skip to main content
15,897,187 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)

  Print Answers RSS
Top Experts
Last 24hrsThis month


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