How to declare a Worksheet using a Script Task?

Question

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.

Answer 1

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:

• Read Excel File in C#
• Reading data from excel 2010 using Microsoft.Office.Interop.Excel
• Excel interop: _Worksheet or Worksheet?

(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:

• Excel interop prevent showing password dialog

(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:

• How to: Programmatically add new worksheets to workbooks
• How to create a new worksheet in Excel file c#?

(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:

• SSIS: Dynamically Generate Excel Table/Sheet

Answer 2

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