How to import Excel (MS O 2007) in Visual Studio 2012 (c#)

Try This :

private DataTable GetTableDataXl(string XlFile)
{
DataTable dt = new DataTable();
try
{
string Ext = Path.GetExtension(XlFile);
string connectionString = "";
if (Ext == ".xls")
{ //For Excel 97-03
connectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source =" + XlFile + "; Extended Properties = 'Excel 8.0;HDR=YES'";
}
else if (Ext == ".xlsx")
{ //For Excel 07 and greater
connectionString = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source =" + XlFile + "; Extended Properties = 'Excel 8.0;HDR=YES'";
}
OleDbConnection conn = new OleDbConnection(connectionString);
OleDbCommand cmd = new OleDbCommand();
OleDbDataAdapter dataAdapter = new OleDbDataAdapter();
cmd.Connection = conn;
//Fetch Fisrt Sheet Name
conn.Open();
DataTable dtSchema;
dtSchema = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
string ExcelSheetName = dtSchema.Rows[0]["TABLE_NAME"].ToString();
conn.Close();
//Read all data from the Sheet to a Data Table
conn.Open();
cmd.CommandText = "SELECT * From [" + ExcelSheetName + "]";
dataAdapter.SelectCommand = cmd;
dataAdapter.Fill(dt); // Fill Sheet Data to Datatable
conn.Close();
}
catch (Exception ex)
{ }
return dt;
}
}

1 Reply

Know the answer? Post it — somebody with the same question will find it here.

Altaf Ansari8y ago
Try This :
   private DataTable GetTableDataXl(string XlFile)
    {
        DataTable dt = new DataTable();
        try
        {
            string Ext = Path.GetExtension(XlFile);
            string connectionString = "";
            if (Ext == ".xls")
            {   //For Excel 97-03
                connectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source =" + XlFile + "; Extended Properties = 'Excel 8.0;HDR=YES'";
            }
            else if (Ext == ".xlsx")
            {    //For Excel 07 and greater
                connectionString = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source =" + XlFile + "; Extended Properties = 'Excel 8.0;HDR=YES'";
            }
            OleDbConnection conn = new OleDbConnection(connectionString);
            OleDbCommand cmd = new OleDbCommand();
            OleDbDataAdapter dataAdapter = new OleDbDataAdapter();

            cmd.Connection = conn;
            //Fetch Fisrt Sheet Name
            conn.Open();
            DataTable dtSchema;
            dtSchema = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
            string ExcelSheetName = dtSchema.Rows[0]["TABLE_NAME"].ToString();
            conn.Close();
            //Read all data from the Sheet to a Data Table
            conn.Open();
            cmd.CommandText = "SELECT * From [" + ExcelSheetName + "]";
            dataAdapter.SelectCommand = cmd;
            dataAdapter.Fill(dt); // Fill Sheet Data to Datatable
            conn.Close();
        }
        catch (Exception ex)
        { }

        return dt;
    }
}