-1

I am importing data from excel sheet to my sql server database in c#.And below is my connection string for that -

var OleDbcon = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=Excel 12.0;");

But this is not working on machines where MS Office is not installed.

And for this issue I have changed my connection string like this :

var OleDbcon = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + ";Extended Properties=Text;");

But still I am not able to import the data.Can any one help me out on this ?

I am importing data from .xls files only.

pnuts
  • 58,317
  • 11
  • 87
  • 139
Pawan Agrawal
  • 263
  • 2
  • 4
  • 11

2 Answers2

0

you can open it without using the excel process with this example code, so you don't need to use Microsoft.Office.Interop.Excel

var fileName = @"C:\ExcelFile.xlsx";
var connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileName + ";Extended Properties=\"Excel 12.0;IMEX=1;HDR=NO;TypeGuessRows=0;ImportMixedTypes=Text\""; ;
using (var conn = new OleDbConnection(connectionString))
{
    conn.Open();

    var sheets = conn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
    using (var cmd = conn.CreateCommand())
    {
        cmd.CommandText = "SELECT * FROM [" + sheets.Rows[0]["TABLE_NAME"].ToString() + "] ";

        var adapter = new OleDbDataAdapter(cmd);
        var ds = new DataSet();
        adapter.Fill(ds);
    }
}

Here is the link of it: How to read an excel file in C# without using Microsoft.Office.Interop.Excel libraries

I hope this helps, good luck!

Community
  • 1
  • 1
Ferryzijl
  • 642
  • 2
  • 8
  • 22
0

It is not necesary to have microsoft installed in other machines, just do a local copy of the dll, I mean Microsoft.Office.Interop.Excel.dll

(sorry if this would be a comment, but I have not enough reputation to do that)

razielx4crazy
  • 159
  • 9