如何使用脚本任务声明工作表? [英] How to declare a Worksheet using a Script Task?

问题描述

我试图声明一个工作表来处理.xlsx文件的单元格，但是当我声明工作表对象时，我的C#脚本失败了:

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];

甚至

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

我在SSIS包中的脚本任务中使用此代码，它没有显示错误消息，只有错误窗口告诉我所包含的脚本具有错误编译功能.

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.

谢谢您的帮助.

推荐答案

如果您要添加新工作表或只是编辑当前工作表，我真的不理解.对于这两种情况，我都会给出一些建议:

如果要编辑现有的工作表，请尝试以下操作之一:

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

例如:

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;

有关更多信息，请检查以下链接:

• 在C#中读取Excel文件
• 使用Microsoft.Office从Excel 2010中读取数据.Interop.Excel
• Excel互操作:_Worksheet还是Worksheet?

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

检查您使用的Office.Interop DLL是否与计算机上安装的officeversion相关.

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

检查工作簿是否不是只读或受保护的，可以参考以下SO问题:

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

• Excel互操作阻止显示密码对话框

还要确保工作簿中不包含隐藏的工作表或临时工作表，请尝试遍历工作簿中的所有工作表并调试代码以查看发生了什么.

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.

如果您要向现有工作簿中添加新工作表，则可以:

您可以使用类似的代码:

You can use a similar code:

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

有关更多信息，您可以检查以下链接:

• 如何:以编程方式向工作簿添加新工作表
• 如何在Excel文件中创建新工作表c#?

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

首先，您必须创建一个Excel Connection Manager，然后添加一个Execute SQL Task，选择Excel Connection并编写一个CREATE语句，例如:

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

)

有关更多信息，您可以检查以下链接:

• SSIS:动态生成Excel表/表

这篇关于如何使用脚本任务声明工作表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！