[Solved] Importing Excel display empty value when different DataType using OleDbConnection in ASP.Net

Hi EmadKhan,

Change IMEX=2 with IMEX=1.

Always use IMEX=1. It is a safer way to retrieve data for mixed data columns.

Check the sample.

HTML

<asp:GridView ID="gvExcelFile" runat="server" />

Namespaces

C#

using System.Data;
using System.Data.OleDb;

VB.Net

Imports System.Data
Imports System.Data.OleDb

Code

C#

protected void Page_Load(object sender, EventArgs e)
{
    LoadGridData();
}

private void LoadGridData()
{
    try
    {
        string connectionString = "";

        string fileExtension = ".xls";
        string fileLocation = Server.MapPath("~/FinalizedColumns.xls");
        if (fileExtension == ".xls")
        {
            connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileLocation + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\"";
        }
        else if (fileExtension == ".xlsx")
        {
            connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileLocation + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=1\"";
        }
        OleDbConnection con = new OleDbConnection(connectionString);
        OleDbCommand cmd = new OleDbCommand
        {
            CommandType = CommandType.Text,
            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();
    }
    catch (Exception ex)
    {
    }
}

VB.Net

Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
    LoadGridData()
End Sub

Private Sub LoadGridData()
    Try
        Dim connectionString As String = ""
        Dim fileExtension As String = ".xls"
        Dim fileLocation As String = Server.MapPath("~/FinalizedColumns.xls")

        If fileExtension = ".xls" Then
            connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & fileLocation & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"""
        ElseIf fileExtension = ".xlsx" Then
            connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & fileLocation & ";Extended Properties=""Excel 12.0;HDR=Yes;IMEX=1"""
        End If

        Dim con As OleDbConnection = New OleDbConnection(connectionString)
        Dim cmd As OleDbCommand = New OleDbCommand With {
            .CommandType = CommandType.Text,
            .Connection = con
        }
        Dim dAdapter As OleDbDataAdapter = New OleDbDataAdapter(cmd)
        Dim dtExcelRecords As DataTable = New DataTable()
        con.Open()
        Dim dtExcelSheetName As DataTable = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing)
        Dim getExcelSheetName As String = dtExcelSheetName.Rows(0)("Table_Name").ToString()
        cmd.CommandText = "SELECT * FROM [" & getExcelSheetName & "]"
        dAdapter.SelectCommand = cmd
        dAdapter.Fill(dtExcelRecords)
        gvExcelFile.DataSource = dtExcelRecords
        gvExcelFile.DataBind()
    Catch ex As Exception
    End Try
End Sub

Screenshots

The Excel

Binded GridView