问题描述
我有一个Excel工作簿通过双击它打开Windows资源管理器,但代码不能访问它。
Excel.Application xlApp =(应用程序)Marshal.GetActiveObject(Excel.Application);
Excel.Workbooks xlBooks = xlApp.Workbooks;
xlBooks.Count等于0,为什么没有引用我打开的工作簿?
修改
下面是各种场景,发生了什么:
方案1:如果文件尚未打开
- 代码打开工作簿时,我很高兴。 LI>
情景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文件?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!