CODEWITHSUNDEEP
c#asp.netexcelloopsoledb
thevan
thevan

Reputation: 10344

Loop worksheets in excel using c#

I want to loop all the sheets in a excel and need to insert that in a sql server table.

I have done a coding part to insert a single sheet excel into a sql server table.

If the excel contains more worksheets, I want to loop those worksheets and need to insert it in a sql server table.

I have written the following code for looping purpose, but it gives the following error;

"ComException was caught" "Retrieving the COM class factory for component with CLSID {00024500-0000-0000-C000-000000000046} failed due to the following error: 80040154 Class not registered (Exception from HRESULT: 0x80040154 (REGDB_E_CLASSNOTREG))."

This is the code for looping:

string path = @"D:/Projects/sample.xls";
string strConnection = ConfigurationManager.ConnectionStrings["Source"].ToString();
string excelConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1\";";
Excel.Application ExcelApp = new Excel.Application();
var Wbook = ExcelApp.Workbooks.Open(path);
foreach (var sheet in Wbook.Worksheets)
                {
                    OleDbCommand cmd = new OleDbCommand("Select [Name], [City], [Address], [Designation] from [" + sheet + "$]", excelConnection);
                    excelConnection.Open();
                    OleDbDataReader dReader;
                    dReader = cmd.ExecuteReader();
                    SqlBulkCopy sqlBulk = new SqlBulkCopy(strConnection);
                    //Give your Destination table name
                    sqlBulk.DestinationTableName = "temp1";
                    sqlBulk.WriteToServer(dReader);
                    excelConnection.Close();
                }

But while executing the line

var Wbook = ExcelApp.Workbooks.Open(path);

the above mentioned error arise. Please suggest me.

Upvotes: 0

Views: 909

Answers (1)

Patrick Hofman
Patrick Hofman

Reputation: 156918

You need Microsoft Office installed in order to use the interop assemblies, like you do.

You have to either:

  • Install Excel;
  • or use a third-party component, like EPPlus.

Upvotes: 1

Related Questions