如何访问在C＃中已经打开的Excel文件？ [英] How to access an already opened Excel file in C#?

问题描述

我有一个Excel工作簿通过双击它打开Windows资源管理器，但代码不能访问它。

  Excel.Application xlApp =（应用程序）Marshal.GetActiveObject（Excel.Application）; 
 Excel.Workbooks xlBooks = xlApp.Workbooks; 
  

xlBooks.Count等于0，为什么没有引用我打开的工作簿？

修改

下面是各种场景，发生了什么：

方案1：如果文件尚未打开

• 代码打开工作簿时，我很高兴
  

情景2：如果该文件最初是从代码中打开，我关闭并重新打开应用程序

• 代码引用文件就好了 xlBooks.Count 等于1，我很高兴。

的情景3 的：如果该文件最初是从代码中开了没，并通过双击它在资源管理器

• 代码打开文件 xlBooks.Count 等于0的另一个实例，我在愤怒！

下面是整个代码，因为它现在

<$ p $代表p> 使用系统;
使用System.Collections.Generic;
：使用System.IO;
使用System.Linq的;使用System.Runtime.InteropServices
;
使用的Microsoft.Office.Interop.Excel;

公共类ExcelService：IExcelService
{
常量字符串_filePath = @C：\Somewhere
常量字符串_filename = @TestFile.xlsb
串_fileNameAndPath = Path.Combine（_filePath，_filename）;

应用xlApp;
工作簿xlBooks;
工作簿xlBook;
表xlSheet;

公共ExcelService（）
{
试
{
xlApp =（应用程序）Marshal.GetActiveObject（Excel.Application）;
xlBooks = xlApp.Workbooks;

VAR numBooks = xlBooks.Count;
Log.Info（工作簿数：{0}。FormatWith（numBooks））;
如果（numBooks大于0）
{
xlBook = xlBooks [1];
Log.Info（使用已经打开的工作簿）;
}
，否则
{
xlBook = xlBooks.Open（_fileNameAndPath）;
Log.Info（打开工作簿：{0}。FormatWith（_fileNameAndPath））;
}

xlSheet =（表）xlBook.Worksheets [1];

//读取测试的命名范围
字符串值= xlSheet.Range [TEST] Value.ToString（）。
Log.Info（@TEST：{0}。FormatWith（值））;

xlApp.Visible = TRUE;
}
赶上（例外五）
{
Log.Error（e.Message）;
}
}

〜ExcelService（）
{
GC.Collect的（）;
GC.WaitForPendingFinalizers（）;

试
{
Marshal.FinalReleaseComObject（xlSheet）;
}
赶上{}

试
{
Marshal.FinalReleaseComObject（xlBook）;
}
赶上{}

试
{
Marshal.FinalReleaseComObject（xlBooks）;
}
赶上{}

试
{
Marshal.FinalReleaseComObject（xlApp）;
}
赶上{}
}
}


解决方案

如果您的所有工作簿是在同一个Excel实例打开（您可以通过检查，如果你可以从一个切换到另一个可以使用Alt标签检查）。你可以简单地参照另一个使用工作簿（[文件名]）。因此，例如：

 暗淡作为世行工作簿//为C＃，类型Excel.Workbook WB = NULL; 
设置白平衡=工作簿（MyDuperWorkbook.xlsx）// C＃中，键入WB = Excel.Workbooks [MyDuperWorkbook.xlsx]; 
 wb.Sheets（1）.Cells（1,1）.value的=Wahou！ 
  

I have an excel workbook opened via double-clicking it in windows explorer but cannot access it in code

Excel.Application xlApp = (Application)Marshal.GetActiveObject("Excel.Application");
Excel.Workbooks xlBooks = xlApp.Workbooks;

xlBooks.Count equals 0, why isn't it referencing my opened workbook?

EDIT

Here are the various scenarios and what is happening:

Scenario 1: If the file is not already open

• Code opens workbook, I am happy.

Scenario 2: If the file is initially opened from code and I close and reopen the app

• Code references file just fine xlBooks.Count equals 1, I am happy.

Scenario 3: If the file is initially opened not from code, and via double-clicking it in explorer

• Code opens another instance of the file xlBooks.Count equals 0, I am in a rage!

Here is the entire code as it stands right now

using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Runtime.InteropServices;
using Microsoft.Office.Interop.Excel;

public class ExcelService : IExcelService
{
    const string _filePath = @"C:\Somewhere";
    const string _fileName = @"TestFile.xlsb";
    string _fileNameAndPath = Path.Combine(_filePath, _fileName);

    Application xlApp;
    Workbooks xlBooks;
    Workbook xlBook;
    Worksheet xlSheet;

    public ExcelService()
    {
        try
        {
            xlApp = (Application)Marshal.GetActiveObject("Excel.Application");
            xlBooks = xlApp.Workbooks;

            var numBooks = xlBooks.Count;
            Log.Info("Number of workbooks: {0}".FormatWith(numBooks));
            if (numBooks > 0)
            {
                xlBook = xlBooks[1];
                Log.Info("Using already opened workbook");
            }
            else
            {
                xlBook = xlBooks.Open(_fileNameAndPath);
                Log.Info("Opening workbook: {0}".FormatWith(_fileNameAndPath));
            }

            xlSheet = (Worksheet)xlBook.Worksheets[1];

            // test reading a named range
            string value = xlSheet.Range["TEST"].Value.ToString();
            Log.Info(@"TEST: {0}".FormatWith(value));

            xlApp.Visible = true;
        }
        catch (Exception e)
        {
            Log.Error(e.Message);
        }
    }

    ~ExcelService()
    {
        GC.Collect();
        GC.WaitForPendingFinalizers();

        try
        {
            Marshal.FinalReleaseComObject(xlSheet);
        }
        catch { }

        try
        {
            Marshal.FinalReleaseComObject(xlBook);
        }
        catch { }

        try
        {
            Marshal.FinalReleaseComObject(xlBooks);
        }
        catch { }

        try
        {
            Marshal.FinalReleaseComObject(xlApp);
        }
        catch { }
    }
}

解决方案

If all your workbooks are opened in the same Excel instance (you can check this by checking if you can switch from one to the other using Alt-tab). You can simply refer to the other using Workbooks("[FileName]"). So, for example :

Dim wb as Workbook //for C#, type Excel.Workbook wb = null;
Set wb = Workbooks("MyDuperWorkbook.xlsx") //for C#, type wb = Excel.Workbooks["MyDuperWorkbook.xlsx"];
wb.Sheets(1).Cells(1,1).Value = "Wahou!"

这篇关于如何访问在C＃中已经打开的Excel文件？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！