使用C#读取单个excel单元格(A3)

本文关键字:A3 单元格 excel 读取 单个 使用 | 更新日期: 2023-09-27 18:25:38

我正在尝试使用C#读取单个excel单元格(A3),但由于下面提到的错误,此代码失败了。我正在使用SSIS脚本任务。请帮帮我!!!感谢

/*
   Microsoft SQL Server Integration Services Script Task
   Write scripts using Microsoft Visual C# 2008.
   The ScriptMain is the entry point class of the script.
*/
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using Microsoft.Office.Interop.Excel; //use the reference in your code
namespace ST_72bc640805c54a799cae807cc596a894.csproj
{
    [System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]
    public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
    {
        #region VSTA generated code
        enum ScriptResults
        {
            Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
            Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
        };
        #endregion
        /*
        The execution engine calls this method when the task executes.
        To access the object model, use the Dts property. Connections, variables, events,
        and logging features are available as members of the Dts property as shown in the following examples.
        To reference a variable, call Dts.Variables["MyCaseSensitiveVariableName"].Value;
        To post a log entry, call Dts.Log("This is my log text", 999, null);
        To fire an event, call Dts.Events.FireInformation(99, "test", "hit the help message", "", 0, true);
        To use the connections collection use something like the following:
        ConnectionManager cm = Dts.Connections.Add("OLEDB");
        cm.ConnectionString = "Data Source=localhost;Initial Catalog=AdventureWorks;Provider=SQLNCLI10;Integrated Security=SSPI;Auto Translate=False;";
        Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
        To open Help, press F1.
    */
        public void Main()
        {
            object _row = 3;
            object _column = 1;
            Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
            excelApp.Visible = false;
            excelApp.ScreenUpdating = false;
            excelApp.DisplayAlerts = false;
            Microsoft.Office.Interop.Excel.Workbook excelWorkbook = excelApp.Workbooks.Open(@"C:''ETL Process''Sample.xlsx", 0, false, 5, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);
             Microsoft.Office.Interop.Excel.Sheets excelSheets = excelWorkbook.Worksheets;
            string currentSheet = "Sheet1";
            Microsoft.Office.Interop.Excel.Worksheet excelWorksheet = (Microsoft.Office.Interop.Excel.Worksheet)excelSheets.get_Item(currentSheet);
            Microsoft.Office.Interop.Excel.Range range = (Microsoft.Office.Interop.Excel.Range)excelWorksheet.UsedRange;
            string sValue = (range.Cells[_row, _column] as Microsoft.Office.Interop.Excel.Range).Value2.ToString();
            MessageBox.Show(sValue); 
            //sValue has your value
        }
    }
}

这里是错误

错误:System.Reflection.TargetInvocationException:已发生异常由调用的目标抛出。--->System.IO.FileNotFoundException:未能加载文件或程序集'Microsoft.Office.Interop.Excel,版本=12.0.0.0,区域性=中性,PublicKeyToken=71e9cce111e9429c'或其依赖项之一。这个系统找不到指定的文件。文件名:'Microsoft.Office.Interop.Excel,版本=12.0.0.0,区域性=中性,PublicKeyToken=71e9cce111e9429c'ST_72bc640805c54a799cae807cc596a894.csproj.ScriptMain.Main()

警告:程序集绑定日志记录已关闭。若要启用程序集绑定故障日志记录,设置注册表值[HHKLM''Software''Microsoft''Fusion!EnableLog](DWORD)到1。注:有是与程序集绑定失败相关的性能损失日志记录。若要关闭此功能,请删除注册表值[HHKLM''Software''Microsoft''Fusion!EnableLog]。

---内部异常堆栈跟踪结束--在System.RuntimeMethodHandle_InvokeMethodFast(对象目标,对象[]自变量,SignatureStruct&sig,MethodAttributes MethodAttributes,RuntimeTypeHandle typeOwner)System.Reflection.RuntimeMethodInfo.IInvoke(Object obj,BindingFlagsinvokeAttr,Binder绑定器,Object[]参数,CultureInfo区域性,布尔skipVisibilityChecks)System.Reflection.RuntimeMethodInfo.IInvoke(Object obj,BindingFlagsinvokeAttr、Binder绑定器、Object[]参数、CultureInfo区域性)
位于System.RuntimeType.IInvokeMember(字符串名称,BindingFlagsbindingFlags,Binder Binder,Object target,Object[]providedArgs,ParameterModifier[]修饰符,CultureInfo区域性,String[]namedParams)Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTATaskScriptingEngine.ExecuteScript()

我甚至把它添加到了系统中http://msdn.microsoft.com/en-us/library/kh3965hw%28VS.80%29.aspx

使用C#读取单个excel单元格(A3)

您的代码使用的是程序集Microsoft.Office.Interop.Excel。当您安装了MS Office时,您的本地计算机可以使用此功能。

您现在已经将代码部署到服务器上。此服务器未安装Office。上述程序集不是核心.NET运行时的一部分。当您的包尝试运行时,它会抛出Could not load file or assembly 'Microsoft.Office.Interop.Excel',因为您没有提供它所需的资源。

如果您选择在服务器上安装Office,请意识到这种方法会带来许可成本,而且此服务器符合条件的修补程序数量也会增加。这可能会导致更长的停机时间/中断,这可能会引起关注,具体取决于您的SLA。

如果你认为"这只是一两个DLL,我可以将它们从我的机器复制到服务器",甚至如果你成功地将它们全部复制并运行,那么你已经有效地安装了Office,如果你接受审计,上述许可将适用。

否则,您唯一的其他途径是重新编码您的程序包,使其不使用程序集。

  • 无法加载文件或程序集';Microsoft.Office.Interop.Excel'
  • 无法加载文件或程序集';Microsoft.Office.Interop.Excel,版本=14.0.0.0
  • 在生成服务器上使用Microsoft.Office.Interop dll进行单元测试失败