CODEWITHSUNDEEP
c#excelssisetlscript-task
MedEc
MedEc

Reputation: 179

How to declare a Worksheet using a Script Task?

I'm trying to declare a Worksheet to handle cells of .xlsx file, but my C# script fails when I declare Worksheet object :

Microsoft.Office.Interop.Excel.Application xlApp = new 
Microsoft.Office.Interop.Excel.Application();

Workbook excelBook = xlApp.Workbooks.Open(fileFullPath);

MySheet = (Excel.Worksheet)excelBook.Worksheets[Data_Sheet];

I've tried all of this statements :

MySheet workSheet = (Worksheet)excelBook.Application.Sheets[1];  
MySheet = (Excel.Worksheet)excelBook.Worksheets[1];

Even 

Worksheet MySheet = new Worksheet();
MySheet = excelBook.Worksheets[Data_Sheet];

I'm using this code in a script task in SSIS package and it doesn't show me the error message, I have only the error window telling me that the contained scripts have error compilation.

Thank you for your help.

Upvotes: 3

Views: 1328

Answers (2)

Hadi
Hadi

Reputation: 37348

I really didn't understood if you are looking to add a new worksheet or just edit a current one. I will give some suggestions for both cases:

(1) Edit an existing Worksheet

If you are looking to edit an existing Worksheet, try one of the following:

(a) Using _Worksheet instead of Worksheet

Excel._Worksheet xlWorksheet = (Excel._Worksheet)xlWorkbook.Sheets[1];

As example:

Excel.Application xlApp = new Excel.Application();
Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(@"file.xlsx");
Excel._Worksheet xlWorksheet = xlWorkbook.Sheets[1];
Excel.Range xlRange = xlWorksheet.UsedRange;

For more information, check to following links:

(b) Interop Library Version

Check that the Office.Interop DLL you are using are relevant to the officeversion installed on the machine.

(c) Permissions and Protection issues

Check that the workbook is not ReadOnly or it is protected, you can refer to the following SO question:

(d) Hidden Worksheets issue

Also make sure that the workbook does not contains hidden or temp worksheets, try to loop over all Worksheets in the Workbook and Debug the code to see what is going on.

(2) Add a new Worksheet

If you are looking to add a new worksheet to an existing workbook you can:

(a) Add it via Script-Task

You can use a similar code:

Excel._Worksheet newWorksheet;
newWorksheet = (Excel._Worksheet)ThisWorkbook.Worksheets.Add();

For more information, you can check the following links:

(b) Use Execute SQL Task

First you have to create an Excel Connection Manager, then add an Execute SQL Task, choose the Excel Connection and write a CREATE Statement, as example:

CREATE TABLE
`Excel Destination` (

    `PromotionKey` INTEGER,
    `PromotionAlternateKey` INTEGER,
    `EnglishPromotionName` NVARCHAR(255),
    `SpanishPromotionName` NVARCHAR(255),
    `FrenchPromotionName` NVARCHAR(255),
    `DiscountPct` DOUBLE PRECISION,
    `EnglishPromotionType` NVARCHAR(50),
    `SpanishPromotionType` NVARCHAR(50),
    `FrenchPromotionType` NVARCHAR(50),
    `EnglishPromotionCategory` NVARCHAR(50),
    `SpanishPromotionCategory` NVARCHAR(50),
    `FrenchPromotionCategory` NVARCHAR(50),
    `StartDate` DATETIME,
    `EndDate` DATETIME,
    `MinQty` INTEGER,
    `MaxQty` INTEGER

)

For more information, you can check the following links:

Upvotes: 3

Flydog57
Flydog57

Reputation: 7111

The Office Interop / Object Model API is based on running Office code in the context of your application. It expects to be running on the UI thread of an interactive (i.e. non-server) application.

See Considerations for server-side Automation of Office

If you ever do get this working, it will fail at just about the most inconvenient time.

If you need to manipulate Office documents on a server, use a server-side appropriate API like OpenXML

Upvotes: 1

Related Questions