Hi userNK,
Add IMEX=1;TypeGuessRows=0;ImportMixedTypes=Text to the Extended Properties in the connection string would fix the problem.
Check this example. Now please take its reference and correct your code.
HTML
<asp:FileUpload ID="FileUpload1" runat="server" />
<asp:Button ID="btnUpload" runat="server" Text="Upload" OnClick="btnUpload_Click" />
<br /><br />
<asp:GridView ID="GridView1" runat="server">
</asp:GridView>
Namespaces
C#
using System;
using System.Data;
using System.Data.OleDb;
using System.IO;
VB.Net
Imports System
Imports System.Data
Imports System.Data.OleDb
Imports System.IO
Code
C#
protected void Upload(object sender, EventArgs e)
{
if (FileUpload1.HasFile)
{
string filename = Path.GetFileName(FileUpload1.PostedFile.FileName);
string extension = Path.GetExtension(FileUpload1.PostedFile.FileName);
string filePath = Path.Combine(Server.MapPath("~/UploadedFiles/TemporaryFiles"), filename);
FileUpload1.SaveAs(filePath);
string excelConnectionString = "";
switch (extension)
{
case ".xls": //Excel 97-03
excelConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR=YES;IMEX=1;TypeGuessRows=0;ImportMixedTypes=Text'";
break;
case ".xlsx": //Excel 07
excelConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 12.0;HDR=YES;IMEX=1;TypeGuessRows=0;ImportMixedTypes=Text'";
break;
}
excelConnectionString = String.Format(excelConnectionString, filePath);
OleDbConnection excelConnection = new OleDbConnection(excelConnectionString);
OleDbCommand cmdExcel = new OleDbCommand();
OleDbDataAdapter oleDA = new OleDbDataAdapter();
DataTable dt = new DataTable();
cmdExcel.Connection = excelConnection;
excelConnection.Open();
DataTable dtExcelSchema;
dtExcelSchema = excelConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
string SheetName = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString();
excelConnection.Close();
excelConnection.Open();
cmdExcel.CommandText = "SELECT * From [" + SheetName + "]";
oleDA.SelectCommand = cmdExcel;
oleDA.Fill(dt);
excelConnection.Close();
GridView1.DataSource = dt;
GridView1.DataBind();
}
}
VB.Net
Protected Sub Upload(ByVal sender As Object, ByVal e As EventArgs)
If FileUpload1.HasFile Then
Dim filename As String = Path.GetFileName(FileUpload1.PostedFile.FileName)
Dim extension As String = Path.GetExtension(FileUpload1.PostedFile.FileName)
Dim filePath As String = Path.Combine(Server.MapPath("~/UploadedFiles/TemporaryFiles"), filename)
FileUpload1.SaveAs(filePath)
Dim excelConnectionString As String = ""
Select Case extension
Case ".xls"
excelConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR=YES;IMEX=1;TypeGuessRows=0;ImportMixedTypes=Text'"
Case ".xlsx"
excelConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 12.0;HDR=YES;IMEX=1;TypeGuessRows=0;ImportMixedTypes=Text'"
End Select
excelConnectionString = String.Format(excelConnectionString, filePath)
Dim excelConnection As OleDbConnection = New OleDbConnection(excelConnectionString)
Dim cmdExcel As OleDbCommand = New OleDbCommand()
Dim oleDA As OleDbDataAdapter = New OleDbDataAdapter()
Dim dt As DataTable = New DataTable()
cmdExcel.Connection = excelConnection
excelConnection.Open()
Dim dtExcelSchema As DataTable
dtExcelSchema = excelConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing)
Dim SheetName As String = dtExcelSchema.Rows(0)("TABLE_NAME").ToString()
excelConnection.Close()
excelConnection.Open()
cmdExcel.CommandText = "SELECT * From [" & SheetName & "]"
oleDA.SelectCommand = cmdExcel
oleDA.Fill(dt)
excelConnection.Close()
GridView1.DataSource = dt
GridView1.DataBind()
End If
End Sub
Screenshot
