Excel催化剂开源第4波-ClickOnce部署要点之导入数字证书及创建EXCEL信任文件夹

Excel催化剂

发布于 2021-08-19 14:57:01

4230

发布于 2021-08-19 14:57:01

文章被收录于专栏：Excel催化剂

Excel催化刘插件使用Clickonce的部署方式发布插件，以满足用户使用插件过程中，需要对插件进行功能升级时，可以无痛地自动更新推送新版本。但Clickonce部署，对用户环境有较大的要求，前期首次安装，比较波折，但相对于后续的自动更新的回报，笔者自我感觉还是很值得的。 Clickonce部署过程中，要求导入数字证书和设置Excel共享路径这两个步骤，本篇开源代码主要讲述这个过程的自动化处理的代码实现，同样用的是Console程序。

为了还原一个干净无侵扰的网络世界，本文将不进行大规模地分发，若您觉得此文有用，不妨小范围地分享到真正有需要的人手中

关于Clickonce部署的其他介绍

若对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

代码实现原理

导入数据证书预先把证书放到资源里，然后调用Windows证书导入的类库的一些命令即可。
创建信任位置此操作也是在注册表上完成，在注册表上新建一个条目，指向要共享的路径即可。

同样的因笔者非专业程序猿，可能写出来的代码严谨性有限，仅供参考。

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上点个赞。