有没有办法查看Excel工作簿(例如DataSheet.xls)是否已经打开(正在使用)? 如果是,我想关闭该工作簿。
有没有办法查看Excel工作簿(例如DataSheet.xls)是否已经打开(正在使用)? 如果是,我想关闭该工作簿。
正确的方法是检查Application.Workbooks对象。在VBA中,您需要编写:
Dim wb as Workbook
On Error Resume Next '//this is VBA way of saying "try"'
Set wb = Application.Workbooks(wbookName)
If err.Number = 9 then '//this is VBA way of saying "catch"'
'the file is not opened...'
End If
换句话说,Workbooks是所有打开的工作簿的数组(或在VBA术语中,集合)。
在C#中,以下代码可行:
static bool IsOpened(string wbook)
{
bool isOpened = true;
Excel.Application exApp;
exApp = (Excel.Application)System.Runtime.InteropServices.Marshal.GetActiveObject("Excel.Application");
try
{
exApp.Workbooks.get_Item(wbook);
}
catch (Exception)
{
isOpened = false;
}
return isOpened;
}
你可能需要自己传递对Excel.Application的引用。
试试这个:
try
{
Stream s = File.Open(FileName, FileMode.Open, FileAccess.Read, FileShare.None);
s.Close();
return true;
}
catch (Exception)
{
return false;
}
这将尝试以独占方式打开文件。如果文件已经被打开,它将抛出一个异常,在那里您可以尝试关闭它并继续进行。
Marshal.GetActiveObject
仅获取对象,如果Excel在另一个对象中打开会怎样呢? 绝佳的解决方案。+1 - Sangram Nandkhile对于任何想要避免使用try-catch的人,以下是一个简洁的代码:
bool wbOpened = ((Application)Marshal.GetActiveObject("Excel.Application")).Workbooks.Cast<Workbook>().FirstOrDefault(x => x.Name == "Some Workbook.xlsx") != null;
或者使用完全限定名称...
bool wbOpened = ((Microsoft.Office.Interop.Excel.Application)System.Runtime.InteropServices.Marshal.GetActiveObject("Excel.Application")).Workbooks.Cast<Microsoft.Office.Interop.Excel.Workbook>().FirstOrDefault(x => x.Name == "Some Workbook.xlsx") != null;
Marshal.GetActiveObject("Excel.Application")
将抛出一个错误。因此,除非有其他保证或处理,否则应始终在try-catch内执行此操作。bool wbOpened = false;
try
{
wbOpened = ((Application)Marshal.GetActiveObject("Excel.Application")).Workbooks.Cast<Workbook>().FirstOrDefault(x => x.Name == "Some Workbook.xlsx") != null;
}
catch
{
...
}
注意 2: Marshal.GetActiveObject("Excel.Application")
只会返回一个 Excel 实例。如果您需要搜索任何可能的 Excel 实例,则下面的代码可能是更好的选择。
更好的替代方案
如果您不介意添加一个辅助类,下面的代码可能是一个更好的替代方案。除了能够搜索任何打开的Excel实例外,它还允许您检查完整路径并返回实际的工作簿对象(如果找到的话)。它还避免了在没有打开Excel实例时抛出错误。
使用方法如下...
If (IsOpenedWB_ByName("MyWB.xlsx"))
{
....
}
or
Workbook wb = GetOpenedWB_ByPath("C:\MyWB.xlsx")
if (wb.obj == null) //If null then Workbook is not already opened
{
...
}
using System;
using System.Collections.Generic;
using System.Linq;
using Microsoft.Office.Interop.Excel;
using System.Runtime.InteropServices.ComTypes;
public class WBHelper
{
public static bool IsOpenedWB_ByName(string wbName)
{
return (GetOpenedWB_ByName(wbName) != null);
}
public static bool IsOpenedWB_ByPath(string wbPath)
{
return (GetOpenedWB_ByPath(wbPath) != null);
}
public static Workbook GetOpenedWB_ByName(string wbName)
{
return (Workbook)GetRunningObjects().FirstOrDefault(x => (System.IO.Path.GetFileName(x.Path) == wbName) && (x.Obj is Workbook)).Obj;
}
public static Workbook GetOpenedWB_ByPath(string wbPath)
{
return (Workbook)GetRunningObjects().FirstOrDefault(x => (x.Path == wbPath) && (x.Obj is Workbook)).Obj;
}
public static List<RunningObject> GetRunningObjects()
{
// Get the table.
List<RunningObject> roList = new List<RunningObject>();
IBindCtx bc;
CreateBindCtx(0, out bc);
IRunningObjectTable runningObjectTable;
bc.GetRunningObjectTable(out runningObjectTable);
IEnumMoniker monikerEnumerator;
runningObjectTable.EnumRunning(out monikerEnumerator);
monikerEnumerator.Reset();
// Enumerate and fill list
IMoniker[] monikers = new IMoniker[1];
IntPtr numFetched = IntPtr.Zero;
List<object> names = new List<object>();
List<object> books = new List<object>();
while (monikerEnumerator.Next(1, monikers, numFetched) == 0)
{
RunningObject running;
monikers[0].GetDisplayName(bc, null, out running.Path);
runningObjectTable.GetObject(monikers[0], out running.Obj);
roList.Add(running);
}
return roList;
}
public struct RunningObject
{
public string Path;
public object Obj;
}
[System.Runtime.InteropServices.DllImport("ole32.dll")]
static extern void CreateBindCtx(int a, out IBindCtx b);
}
GetRunningObjects()
方法。对于之前的 C# 答案,这里补充一点,如果有人可能会遇到:
你要检查的方法是:
xlAppRef.Workbooks.Item
只要变量wbook为"myworkbook.xlsx",而不是工作簿的完整路径(例如"C/Users/Me/Desktop/myworkbook.xlsx"),代码就会正常运行。
bool IsOpened(string wbook, Excel.Application xlAppRef)
{
bool isOpened = true;
try
{
// wbook should be: "name-of-the-workbook.xlsx". Otherwise it will always raise the
// exception and never return true
var vb = xlAppRef.Workbooks.Item[wbook];
}
catch (Exception e)
{
isOpened = false;
}
return isOpened;
}
如果Excel应用程序当前未运行,则马丁的答案无法正常工作。您可能需要按照以下方式修改代码:
static bool IsOpened(string wbook)
{
bool isOpened = true;
Excel.Application exApp;
try
{
// place the following line here :
exApp = (Excel.Application)System.Runtime.InteropServices.Marshal.GetActiveObject("Excel.Application");
// because it throws an exception if Excel is not running.
exApp.Workbooks.get_Item(wbook);
}
catch (Exception)
{
isOpened = false;
}
return isOpened;
}
这并不是特别好的做法 - 我们将尝试打开文件并检查异常(如果失败)。我不确定在C#中你有其他选择。
然而,重要的是只处理正确的异常:基本上,我们尝试以不允许共享的方式打开文件。如果失败,并且我们得到了正确类型的异常和正确的异常消息,那么我们就知道它已经打开了。
// open the file with no sharing semantics (FileShare.None)
using (FileStream stream = new FileStream(file, FileMode.Open, FileAccess.Read, FileShare.None))
{
try
{
stream.ReadByte();
return false;
}
catch (IOException ex)
{
// catch ONLY the exception we are interested in, and check the message too
if (ex.Message != null
&& ex.Message.Contains("The process cannot access the file"));
{
return true;
}
// if the message was incorrect, this was not the IOException we were looking for. Rethrow it.
throw;
}
}
显然,这种方法对于将来的 .Net 版本中异常消息的更改是脆弱的。您可能希望通过故意锁定文件并调用此功能来检查它是否正确检测到消息,来补充此类功能。
using System.Collections.Generic;
using System.IO;
using System.Linq;
using wf = System.Windows.Forms;
using xl = Microsoft.Office.Interop.Excel;
public static class ExcelTest
{
public xl.Application xlApp = null;
public xl.Workbook xlWb = null;
public xl.Worksheet xlWs = null;
public static bool IsXlFileOpen(string xlFileName)
{
try
{
if (!File.Exists(xlFileName))
{
wf.MessageBox.Show("Excel File does not exists!");
return false;
}
try
{
xlApp = (xl.Application)Marshal.GetActiveObject("Excel.Application");
}
catch (Exception ex)
{
return false;
}
foreach (xl.Workbook wb in xlApp.Workbooks)
{
if (wb.FullName == xlFileName)
{
xlWb = wb;
return true;
}
}
return false;
}
catch (Exception ex)
{
return false;
}
}
public static void GetXlSheet(string xlFileName,
string xlSheetName)
{
try
{
if (!File.Exists(xlFileName))
{
wf.MessageBox.Show("Excel File does not exists!");
return false;
}
xlApp = (xl.Application)Marshal.GetActiveObject("Excel.Application");
foreach (xl.Workbook wb in xlApp.Workbooks)
{
if (wb.FullName == xlFileName)
{
if (!xlWb
.Sheets
.Cast<xl.Worksheet>()
.Select(s => s.Name)
.Contains(xlSheetName))
{
wf.MessageBox.Show("Sheet name does not exist in the Excel workbook!");
return;
}
xlWs = xlWb.Sheets[xlSheetName];
}
}
}
catch (Exception ex)
{
// catch errors
}
}
}