Unable to import xlsx file in Asp .Net

I am unable to import开发者_运维百科 xlsx file in Asp .Net getting below error:-

Could not find installable ISAM.

I am using below code for importing xlsx file:-

'function

   Protected Function ExcelConnection() As OleDbCommand

    ' Connect to the Excel Spreadsheet
    Dim xConnStr As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                "Data Source=C:\Users\tcs0028\Desktop\Upload1.xlsx;" & _
                "Extended Properties=Excel 12.0;"

    ' create your excel connection object using the connection string
    Dim objXConn As New OleDbConnection(xConnStr)
    objXConn.Open()

    Dim objCommand As New OleDbCommand("SELECT * FROM [Sheet1$]", objXConn)
    Return objCommand

Plz suggest?

You need to change the Connection String first to

Dim ConnStr As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=~\Upload1.xlsx";Extended Properties=\"Excel 12.0 Xml;HDR=YES;IMEX=1\";";

Try Excel Package Plus it's a fork of Excel Package with additional features and it's still maintained and developed.

 public class ImportExcel
    {
        public DataTable Importar(string arquivo)
        {
            string ext = Path.GetExtension(arquivo);
            string aspas = "\"";
            string Conexao = string.Empty;

            if (ext == ".xls")
            {
                Conexao = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + arquivo + ";" + "Extended Properties=" + aspas + "Excel 8.0;HDR=YES" + aspas;
            }
            if (ext == ".xlsx")
            {
                Conexao = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" + arquivo + ";" + "Extended Properties=" + aspas + "Excel 12.0;HDR=YES" + aspas;
            }

            System.Data.OleDb.OleDbConnection Cn = new System.Data.OleDb.OleDbConnection();
            Cn.ConnectionString = Conexao;
            Cn.Open();
            object[] Restricoes = { null, null, null, "TABLE" };
            DataTable DTSchema = Cn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, Restricoes);
            if (DTSchema.Rows.Count > 0)
            {
                string Sheet = DTSchema.Rows[0]["TABLE_NAME"].ToString();
                System.Data.OleDb.OleDbCommand Comando = new System.Data.OleDb.OleDbCommand("SELECT * FROM [" + Sheet + "]", Cn);
                DataTable Dados = new DataTable();
                System.Data.OleDb.OleDbDataAdapter DA = new System.Data.OleDb.OleDbDataAdapter(Comando);
                DA.Fill(Dados);
                Cn.Close();
                return Dados;
            }
            return null;
        }
    }

important

To import xlsx file you need to be installed AccessDatabaseEngine.exe download from microsoft

While the import is done the user can not be with the sheet open, so it's important to create a copy of this in a temporary folder and pass this path as a parameter. (don't forget to delete the temporary)

uses:

System;
System.Data;
System.Data.Odbc;
System.Data.OleDb;
System.IO;
System.Reflection;
System.Text;
System.Web;

You could try Excel Package instead

You need to change the Connection String first to

Dim xConnStr As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\tcs0028\Desktop\Upload1.xlsx";Extended Properties=\"Excel 12.0 Xml;HDR=YES;IMEX=1\";";

Next thing You need to Install the MSExcel in order to be able to import Excel Files or You can run a free alternative package which will do as if there's an Excel 2007 installed Here's the link:

http://www.microsoft.com/downloads/en/details.aspx?familyid=7554f536-8c28-4598-9b72-ef94e038c891&displaylang=en

Hope that helps.