Excel催化刘插件使用Clickonce的部署方式发布插件,以满足用户使用插件过程中,需要对插件进行功能升级时,可以无痛地自动更新推送新版本。 但Clickonce部署,对用户环境有较大的要求,前期首次安装,比较波折,但相对于后续的自动更新的回报,笔者自我感觉还是很值得的。 Clickonce部署过程中,要求导入数字证书和设置Excel共享路径这两个步骤,本篇开源代码主要讲述这个过程的自动化处理的代码实现,同样用的是Console程序。
为了还原一个干净无侵扰的网络世界,本文将不进行大规模地分发,若您觉得此文有用,不妨小范围地分享到真正有需要的人手中
若对Clickonce部署的其他深入知识点,可以通过百度自行补充或通过以下链接继续深入学习。
ClickOnce部署 - 无恨星晨 - 博客园 http://www.cnblogs.com/weixing/p/3358740.html
Excel催化剂公众号历史文章 https://mp.weixin.qq.com/s/HCluSw-8uZkXiLWBeeJqiA
https://mp.weixin.qq.com/s/G8B2gEG8LfIUCuSyAPFX2w
同样的因笔者非专业程序猿,可能写出来的代码严谨性有限,仅供参考。
using Microsoft.Win32;
using System;
using System.Collections.Generic;
using System.Diagnostics;
using System.Linq;
using System.Security.Cryptography.X509Certificates;
using System.Text;
using System.Text.RegularExpressions;
namespace 导入证书及设置EXCEL信任文件夹
{
class Program
{
static void Main(string[] args)
{
//64位系统
try
{
Console.WriteLine("正在导入证书操作");
X509Store storeTrustedPublisher = new X509Store(StoreName.TrustedPublisher, StoreLocation.CurrentUser);
//导入外部信任者
ImportCertificate(storeTrustedPublisher);
//导入根证书信任
X509Store storeRoot = new X509Store(StoreName.Root, StoreLocation.CurrentUser);
ImportCertificate(storeRoot);
Console.WriteLine("正在创建EXCEL信任文件夹");
TrustDirSetting.SettingTrustDir("http://LiWeiJianWeb/");
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
finally
{
Console.WriteLine("操作完成,请按任意键结束!");
Console.ReadKey();
}
}
private static void CreateTargetSubKey(RegisterManager registerManager, List<string> listSubKeys, RegistryKey localKey)
{
var regAllowNetworkLocations = listSubKeys.Where(s => s.EndsWith(@"Excel\Security\Trusted Locations"));
//设置信任网络路径
foreach (var item in regAllowNetworkLocations)
{
registerManager.SetRegeditKeyValue(item, "AllowNetworkLocations", "1");
}
//包含EXCEL字样的,并且有location节点
var listSecurity = listSubKeys.Where(s => s.Contains(@"Excel\Security\Trusted Locations")).Where(s => Regex.IsMatch(s, @"Location\d+$")).ToList();
foreach (var item in listSecurity)
{
if (registerManager.IsRegeditKeyAndValueExist(item, "Path", @"http://LiWeiJianWeb/"))
{
return;
}
};
var result = from s in listSecurity
select new { GroupName = Regex.Match(s, @".+?\\.+?\\.+?\\.+?\\").Value, Fullpath = s };
//按HKEY_CURRENT_USER\Software\Microsoft\Office\15.0分组,防止多个EXCEL版本的原因引起信任位置添加不全
var query = from s in result
group s by s.GroupName;
foreach (var item in query)
{
//只取第1条记录,去掉最后一个尾数
string locationName = Regex.Match(item.First().Fullpath, @".+Location").Value;
//用最后的尾数来比较大小,不是用字符串,可以最终比较出11比2大
int locationIndex = item.Max(s => int.Parse(Regex.Match(s.Fullpath, @".+Location(\d+)").Groups[1].Value) + 1);
string newLocationName = Regex.Match(locationName, ".+Location").Value + locationIndex;
RegistryKey Location = localKey.CreateSubKey(newLocationName);
Location.SetValue("Path", @"http://LiWeiJianWeb/");
Location.SetValue("AllowSubfolders", "00000001", RegistryValueKind.DWord);
Location.SetValue("Date", DateTime.Now.ToString());
Location.SetValue("Description", "");
}
}
private static void ImportCertificate(X509Store store)
{
store.Open(OpenFlags.ReadWrite);
X509Certificate2Collection certs = store.Certificates.Find(X509FindType.FindBySubjectName, "Excel催化剂", false);
if (certs.Count == 0 || certs[0].NotAfter < DateTime.Now)
{
X509Certificate2 certificate = new X509Certificate2(Resource1.Excel催化剂);
store.Remove(certificate); //可省略
store.Add(certificate);
store.Close();
}
}
}
}
作了个帮助类
using Microsoft.Win32;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;
namespace 导入证书及设置EXCEL信任文件夹
{
class TrustDirSetting
{
public static void SettingTrustDir(string trustDir)
{
if (Environment.Is64BitOperatingSystem)
{
//64位的EXCEL
AddTrustDirToRegister(trustDir, RegistryView.Registry64);
}
//32位的EXCEL
AddTrustDirToRegister(trustDir, RegistryView.Registry32);
}
private static void AddTrustDirToRegister(string trustDir, RegistryView registryView)
{
List<string> listSubKeys = new List<string>();
var localKey = RegistryKey.OpenBaseKey(RegistryHive.CurrentUser, registryView);
RegisterManager registerManager = new RegisterManager() { BaseKey = localKey };
registerManager.EnumerateKeyNames(@"Software\Microsoft\Office", ref listSubKeys);
CreateTargetSubKey(registerManager, listSubKeys, localKey, trustDir);
}
private static void CreateTargetSubKey(RegisterManager registerManager, List<string> listSubKeys, RegistryKey localKey, string trustDir)
{
var regAllowNetworkLocations = listSubKeys.Where(s => s.EndsWith(@"Excel\Security\Trusted Locations"));
//设置信任网络路径
foreach (var item in regAllowNetworkLocations)
{
registerManager.SetRegeditKeyValue(item, "AllowNetworkLocations", "1");
}
//包含EXCEL字样的,并且有location节点
var listSecurity = listSubKeys.Where(s => s.Contains(@"Excel\Security\Trusted Locations")).Where(s => Regex.IsMatch(s, @"Location\d+$")).ToList();
foreach (var item in listSecurity)
{
if (registerManager.IsRegeditKeyAndValueExist(item, "Path", trustDir))
{
return;
}
};
var result = from s in listSecurity
select new { GroupName = Regex.Match(s, @".+?\\.+?\\.+?\\.+?\\").Value, Fullpath = s };
//按HKEY_CURRENT_USER\Software\Microsoft\Office\15.0分组,防止多个EXCEL版本的原因引起信任位置添加不全
var query = from s in result
group s by s.GroupName;
foreach (var item in query)
{
//只取第1条记录,去掉最后一个尾数
string locationName = Regex.Match(item.First().Fullpath, @".+Location").Value;
//用最后的尾数来比较大小,不是用字符串,可以最终比较出11比2大
int locationIndex = item.Max(s => int.Parse(Regex.Match(s.Fullpath, @".+Location(\d+)").Groups[1].Value) + 1);
string newLocationName = Regex.Match(locationName, ".+Location").Value + locationIndex;
RegistryKey Location = localKey.CreateSubKey(newLocationName);
Location.SetValue("Path", trustDir);
Location.SetValue("AllowSubfolders", "00000001", RegistryValueKind.DWord);
Location.SetValue("Date", DateTime.Now.ToString());
Location.SetValue("Description", "");
}
}
}
internal class RegisterManager
{
public RegistryKey BaseKey { get; set; }
private bool IsRegeditKeyExist(string subKeyString, string key)
{
string[] subkeyNames;
RegistryKey subKey = this.BaseKey.OpenSubKey(subKeyString);
subkeyNames = subKey.GetValueNames();
//取得该项下所有键值的名称的序列,并传递给预定的数组中
foreach (string keyName in subkeyNames)
{
if (keyName == key) //判断键值的名称
{
return true;
}
}
return false;
}
public bool IsRegeditKeyAndValueExist(string subKeyString, string key, string valueString)
{
string[] subkeyNames;
RegistryKey subKey = this.BaseKey.OpenSubKey(subKeyString);
subkeyNames = subKey.GetValueNames();
//取得该项下所有键值的名称的序列,并传递给预定的数组中
foreach (string keyName in subkeyNames)
{
if (keyName == key) //判断键值的名称
{
if (subKey.GetValue(key).ToString() == valueString)
{
return true;
}
}
}
return false;
}
public void SetRegeditKeyValue(string subKeyString, string key, string valueString)
{
RegistryKey subKey = this.BaseKey.OpenSubKey(subKeyString, true);
subKey.SetValue(key, valueString, RegistryValueKind.DWord);
}
public void EnumerateKeyNames(string fatherKey, ref List<string> listSubKeys)
{
RegistryKey RegKey = this.BaseKey.OpenSubKey(fatherKey);
string[] subKeys = RegKey.GetSubKeyNames();
foreach (string subKey in subKeys)
{
string fullPath = fatherKey + "\\" + subKey;
EnumerateKeyNames(fullPath, ref listSubKeys);
listSubKeys.Add(fullPath);
}
}
}
}
开源地址为:https://github.com/minren118/ExcelUdfByExcelCuiHuaJi,不妨对您有帮助时帮忙在GtiHub上点个赞。
登录Github后点击红框给个星星