实现一个UiPath中不更新链接的WorkbookApplication
浏览：1229
发布日期：2023-02-09 11:18:53
在UiPath中我们处理Excel时，当Excel文件中有链接，而这些链接又无法访问时，会弹出让我们更新的弹窗：
通过查看Uipath.Excel.Activities 2.8.6中打开Excel文件的源码：
this.CurrentWorkbook = workbooks.Open(workbookArguments.Path, Type.Missing, (object) workbookArguments.ReadOnly, Type.Missing, this.GetPasswordParameter(workbookArguments.Password), passwordParameter, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
通过查看Workbooks.Open方法的api，
public Microsoft.Office.Interop.Excel.Workbook _Open (string Filename, object UpdateLinks, object ReadOnly, object Format, object Password, object WriteResPassword, object IgnoreReadOnlyRecommended, object Origin, object Delimiter, object Editable, object Notify, object Converter, object AddToMru);
我们可以发现第二个参数控制是否更新链接，这个值为3表示更新链接，0表示不更新。
所以我们需要重写这部分内容。
我们查看Uipath的Excel应用程序范围，发现它可以直接使用现有工作簿对象-即WorkbookApplication，所以我们的思路是手动创建出一个WorkbookApplication对象，我们查看ExcelApplicationScope的执行代码，首先看它的默认构造函数：
public ExcelApplicationScope()
: this((Func<WorkbookArguments, WorkbookApplication>) (args => WorkbookApplication.AttachOrCreateNew(args)))
{
}它调用了:
internal ExcelApplicationScope(
Func<WorkbookArguments, WorkbookApplication> createWorkbook)
{
this._createWorkbook = createWorkbook;
this.Visible = true;
this.AutoSave = true;
this.CreateNewFile = true;
this.EditPassword = (InArgument<string>) null;
ActivityAction<WorkbookApplication> activityAction = new ActivityAction<WorkbookApplication>();
activityAction.Argument = new DelegateInArgument<WorkbookApplication>(ExcelApplicationScope.WorkbookScopePropertyTag);
Sequence sequence = new Sequence();
sequence.DisplayName = UiPath_Excel_Activities.Do;
activityAction.Handler = (Activity) sequence;
this.Body = activityAction;
}它通过WorkbookApplication.AttachOrCreateNew(args)来创建，让我们来看看:
public static WorkbookApplication AttachOrCreateNew(WorkbookArguments workbookArguments) => WorkbookApplication.Initialize(WorkbookApplication.InitializeExcelApp(), workbookArguments); WorkbookApplication.InitializeExcelApp()
它启动一个Excel程序
protected static Application InitializeExcelApp()
{
try
{
// ISSUE: variable of a compiler-generated type
Application openExcel = WorkbookApplication.AttachToOpenExcel();
return openExcel;
}
catch
{
// ISSUE: variable of a compiler-generated type
Application application = WorkbookApplication.StartNewApplication();
return application;
}
}然后我们来看Initialize方法：
private static WorkbookApplication Initialize(
Application excelApp,
WorkbookArguments args,
bool onlyIfWorkbookExists = false)
{
if (args.MacroSetting == MacroSetting.DisableAll)
excelApp.AutomationSecurity = MsoAutomationSecurity.msoAutomationSecurityForceDisable;
else if (args.MacroSetting == MacroSetting.ReadFromExcelSettings)
excelApp.AutomationSecurity = MsoAutomationSecurity.msoAutomationSecurityByUI;
WorkbookApplication workbookApplication = new WorkbookApplication(excelApp, args.AutoSave);
workbookApplication.InternalOpenWorkbook(args, onlyIfWorkbookExists);
return workbookApplication;
}它最终调用private void InitializeCurrentWorkbook(WorkbookArguments workbookArguments, Workbooks workbooks)来打开excel：
private void InitializeCurrentWorkbook(WorkbookArguments workbookArguments, Workbooks workbooks)
{
object passwordParameter = WorkbookApplication.GetEditPasswordParameter(workbookArguments.EditPassword);
// ISSUE: reference to a compiler-generated method
this.CurrentWorkbook = workbooks.Open(workbookArguments.Path, Type.Missing, (object) workbookArguments.ReadOnly, Type.Missing, this.GetPasswordParameter(workbookArguments.Password), passwordParameter, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
if (this.CurrentWorkbook == null)
throw new InvalidOperationException(UiPath_Excel.CannotOpenWorkbookException);
}我们可以看到以上大多数方法，是无法在外部创建，并干预这些活动的，所以我们可以使用反射技术，来兼容Uipath.Excel.Activities创建的对象：
我们需要创建一个BxExcelWorkbookApplication.activities，它实现创建WorkbookApplication对象导出，它的参数与ExcelApplicationScope大多数相同：
[LocalizedCategory("File")]
[DisplayName("密码")]
public InArgument<string> Password { get; set; }
[LocalizedCategory("File")]
[DisplayName("编辑密码")]
[DefaultValue(null)]
public InArgument<string> EditPassword { get; set; }
[LocalizedCategory("File")]
[DisplayName("文件路径")]
[RequiredArgument]
[OverloadGroup("New Workbook File")]
public InArgument<string> WorkbookPath { get; set; }
[LocalizedCategory("Options")]
[DisplayName("是否显示")]
[DefaultValue(true)]
public bool Visible { get; set; } = true;
[LocalizedCategory("Options")]
[DisplayName("自动保存")]
[DefaultValue(true)]
public bool AutoSave { get; set; } = true;
[LocalizedCategory("Options")]
[DisplayName("只读")]
[DefaultValue(false)]
public bool ReadOnly { get; set; }
[LocalizedCategory("Options")]
[DisplayName("宏设置")]
public MacroSetting MacroSetting { get; set; }
[LocalizedCategory("Options")]
[DisplayName("实例缓存时间")]
[Description("仅对2.9.x有效")]
public InArgument<int> InstanceCachePeriod { get; set; } = new InArgument<int>(3000);
[LocalizedCategory("Options")]
[DisplayName("更新链接")]
public ExcelUpdateLinks UpdateLinks { get; set; }
[LocalizedCategory("Output")]
[DisplayName("WorkbookApp")]
[DefaultValue(null)]
public OutArgument<WorkbookApplication> WorkbookApp { get; set; }
private Version UipathExcelVersion { get; set; }我们去掉了创建新文件的选项，所以我们将其设为false：
protected override void Execute(CodeActivityContext context)
{
WorkbookArguments args = new WorkbookArguments()
{
Path = WorkbookPath.Get(context),
Password = Password.Get(context),
Visible = Visible,
AutoSave = AutoSave,
CreateNew = false,
EditPassword = EditPassword.Get(context),
ReadOnly = ReadOnly,
MacroSetting = this.MacroSetting
};
PropertyInfo instanceCacheProperty = typeof(WorkbookArguments).GetProperty("InstanceCachePeriod");
PropertyInfo useInstanceProperty = typeof(WorkbookArguments).GetProperty("UseInstanceCache");
if (instanceCacheProperty != null && useInstanceProperty != null)
{
instanceCacheProperty.SetValue(args, instanceCacheProperty.GetValue(args));
useInstanceProperty.SetValue(args, useInstanceProperty.GetValue(args));
}
var wba = AttachOrCreateNew(args);
this.WorkbookApp.Set(context, wba);
Console.WriteLine("当前版本：{0}", UipathExcelVersion.ToString());
}
public WorkbookApplication AttachOrCreateNew(WorkbookArguments args)
{
PropertyInfo property = typeof(WorkbookArguments).GetProperty("UseInstanceCache");
bool useInstanceCache = false;
if (property != null)
{
useInstanceCache = true;
}
excel.Application excelApp = ReflectionMethodHelper.InitExcelApp(useInstanceCache);
if (args.MacroSetting == MacroSetting.DisableAll)
{
excelApp.AutomationSecurity = MsoAutomationSecurity.msoAutomationSecurityForceDisable;
}
else if (args.MacroSetting == MacroSetting.ReadFromExcelSettings)
{
excelApp.AutomationSecurity = MsoAutomationSecurity.msoAutomationSecurityByUI;
}
var wba = ReflectionMethodHelper.InitWorkbookApp(excelApp, args);
InternalOpenworkbook(wba, args, false);
return wba;
}
private static void ReleaseAndClearComObject<T>(ref T o)
{
if ((object)o == null)
return;
if (Marshal.IsComObject((object)o))
Marshal.ReleaseComObject((object)o);
o = default(T);
}
private void InternalOpenworkbook(WorkbookApplication wba, WorkbookArguments args, bool onlyIfWorkbookExists)
{
ReflectionPropertyHelper.SetCurrentWorkbookInitArgs(wba, args);
ReflectionPropertyHelper.SetQuit(wba, args.QuitExcelOnDispose);
if (string.IsNullOrEmpty(args.Path))
{
throw new ArgumentException("文件路径不能为空");
}
args.Path = Path.GetFullPath(args.Path);
excel.Workbooks workbooks = null;
try
{
excel.Application excelApp = ReflectionPropertyHelper.GetExcelApp(wba);
workbooks = excelApp.Workbooks;
excel.Workbook existWorkbook = ReflectionMethodHelper.FindExistingWorkbook(wba, args, workbooks);
if (existWorkbook != null)
{
ReflectionMethodHelper.WarnUserIfPasswordIsIgnored(wba, args);
ReflectionPropertyHelper.SetQuit(wba, false);
ReflectionPropertyHelper.SetCurrentWorkbook(wba, existWorkbook);
}
else
{
if (onlyIfWorkbookExists)
{
throw new ArgumentException("Workbook没有打开");
}
ReflectionMethodHelper.CreateNewWorkbookIfNeeded(wba, args.Path, args.CreateNew, workbooks);
InitializeCurrentWorkbook(wba, args, workbooks);
excelApp.ExecuteInReadyState(() => excelApp.Visible = args.Visible, 20000L);
}
}
finally
{
ReleaseAndClearComObject<excel.Workbooks>(ref workbooks);
}
}
private excel.Workbook InitializeCurrentWorkbook(WorkbookApplication wba, WorkbookArguments args,
excel.Workbooks workbooks)
{
excel.Workbook wb;
int updateLinkVal = this.UpdateLinks == ExcelUpdateLinks.UpdateLinksAlways ? 3 : 0;
object editPassword = ReflectionMethodHelper.GetEditPasswordParameter(wba, args.EditPassword);
wb = workbooks.Open(Filename: args.Path, UpdateLinks: updateLinkVal, ReadOnly: args.ReadOnly, Password: args.Password,
WriteResPassword: editPassword);
ReflectionPropertyHelper.SetCurrentWorkbook(wba, wb);
if (wb == null)
{
throw new InvalidOperationException("工作簿打开失败");
}
return wb;
}namespace BxExcelWorkbookApplication.activities.utils { internal static class ExcelAppExtensionMethods { public const int DefaultReadyStateTimeoutMilliseconds = 300000;
public static void ExecuteInReadyState(this excel.Application app, Action actionToExecuteWhenReady, long timeoutMilliseconds = 300000L)
{
if (TryExecuteAction(app, actionToExecuteWhenReady))
{
return;
}
Stopwatch stopwatch = new Stopwatch();
stopwatch.Start();
while (stopwatch.ElapsedMilliseconds < timeoutMilliseconds)
{
Thread.Sleep(100);
if (TryExecuteAction(app, actionToExecuteWhenReady))
{
return;
}
}
throw new ExcelException("excel未准备好");
}
private static bool TryExecuteAction(excel.Application app, Action actionToExecuteWhenReady)
{
try
{
if (!app.Ready)
{
return false;
}
actionToExecuteWhenReady();
return true;
}
catch (Exception ex)
{
Exception ex2 = (ex as ExcelException)?.InnerException ?? ex;
if (ex2 != null && ex2.HResult == -2146777998)
{
return false;
}
if (ex2 != null && ex2.HResult == -2147418111)
{
return false;
}
throw;
}
}
}}
namespace BxExcelWorkbookApplication.activities.utils { public static class ReflectionMethodHelper { ///
///
object[] appParams;
if (methodInfo.GetParameters().Length == 0)
{
appParams = new object[0];
}
else
{
appParams = new object[] { useInstanceCache };
}
excel.Application app = methodInfo.Invoke(null, appParams) as excel.Application;
return app;
}
/// <summary>
/// 初始化WorkbookApplication
/// </summary>
/// <param name="excelApp"></param>
/// <param name="args"></param>
/// <returns></returns>
public static WorkbookApplication InitWorkbookApp(excel.Application excelApp, WorkbookArguments args)
{
PropertyInfo property = typeof(WorkbookArguments).GetProperty("InstanceCachePeriod");
Type[] types;
object[] pars;
if (property != null)
{
types = new Type[] { typeof(excel.Application), typeof(bool), typeof(excel.Workbook), typeof(int) };
pars = new object[] { excelApp, args.AutoSave, null, property.GetValue(args)};
}
else
{
types = new Type[] { typeof(excel.Application), typeof(bool), typeof(excel.Workbook) };
pars = new object[] { excelApp, args.AutoSave, null };
}
ConstructorInfo constructorInfo = typeof(WorkbookApplication)
.GetConstructor(BindingFlags.Instance | BindingFlags.NonPublic, null, types, null);
WorkbookApplication wba = constructorInfo.Invoke(pars) as WorkbookApplication;
return wba;
}
public static void CreateNewWorkbookIfNeeded(WorkbookApplication wba, string workbookPath, bool createNew, Workbooks workbooks)
{
MethodInfo methodInfo = wba.GetType().GetMethod("CreateNewWorkbookIfNeeded", BindingFlags.Instance | BindingFlags.NonPublic);
methodInfo.Invoke(wba, new object[] { workbookPath, createNew, workbooks });
}
public static excel.Workbook FindExistingWorkbook(WorkbookApplication wba, WorkbookArguments args, Workbooks workbooks)
{
MethodInfo methodInfo = wba.GetType().GetMethod("FindExistingWorkbook",
BindingFlags.Instance | BindingFlags.NonPublic);
excel.Workbook existWorkbook = methodInfo.Invoke(wba, new object[] { args.Path, workbooks }) as excel.Workbook;
return existWorkbook;
}
public static void WarnUserIfPasswordIsIgnored(WorkbookApplication wba, WorkbookArguments args)
{
MethodInfo methodInfo = wba.GetType().GetMethod("WarnUserIfPasswordIsIgnored",
BindingFlags.Static | BindingFlags.NonPublic);
methodInfo.Invoke(wba, new object[] { args });
}
public static object GetEditPasswordParameter(WorkbookApplication wba, string editPassword)
{
MethodInfo method = wba.GetType().GetMethod("GetEditPasswordParameter", BindingFlags.Static | BindingFlags.NonPublic);
return method.Invoke(null, new object[] { editPassword });
}
}}
namespace BxExcelWorkbookApplication.activities.utils { public class ReflectionPropertyHelper { public static void SetQuit(WorkbookApplication wba, bool quit) { FieldInfo field = wba.GetType().GetField("_quit", BindingFlags.Instance| BindingFlags.NonPublic); field.SetValue(wba, quit); }
public static void SetCurrentWorkbook(WorkbookApplication wba, excel.Workbook workbook)
{
FieldInfo field = wba.GetType().GetField("_currentWorkbook", BindingFlags.Instance | BindingFlags.NonPublic);
field.SetValue(wba, workbook);
}
public static void SetCurrentWorkbookInitArgs(WorkbookApplication wba, WorkbookArguments args)
{
FieldInfo field = wba.GetType().GetField("_currentWorkbookInitArgs", BindingFlags.Instance | BindingFlags.NonPublic);
field.SetValue(wba, args);
}
public static excel.Application GetExcelApp(WorkbookApplication wba)
{
FieldInfo field = wba.GetType().GetField("ExcelApp", BindingFlags.Instance | BindingFlags.NonPublic);
return field.GetValue(wba) as excel.Application;
}
public static excel.Workbook GetCurrentWorkbook(WorkbookApplication wba)
{
FieldInfo field = wba.GetType().GetField("_currentWorkbook", BindingFlags.Instance | BindingFlags.NonPublic);
return field.GetValue(wba) as excel.Workbook;
}
}}