Excel催化剂开源第10波-VSTO开发之用户配置数据与工作薄文件一同存储

Excel催化剂

发布于 2021-08-19 15:03:08

1.3K0

发布于 2021-08-19 15:03:08

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

在传统的VBA开发中，若是用的是普通加载项方法，是可以存储数据在xlam上的，若用的是Com加载项方法同时是Addins程序级别的项目开发的，配置文件没法保存到工作薄中，一般另外用配置文件来存放供调用。

但无论以上两种方式都会带来一点缺陷，若用户配置好自己的数据，这些配置数据只能保存到自己电脑上，无法在文件共享给其他人使用时，配置文件一并传递过去。

当然一个折衷的方式是，在Excel文件中新建一个工作表并隐藏它来实现配置数据跟着工作薄一起带走。

这种方式有一缺点是，在用户工作薄里进行数据操作，用户很容易破坏到这些数据，就算深度隐藏工作表，仍然会轻松地用VBA方法重新将其显示出来，特别对一些敏感配置信息的保存非常不利。

针对以上的问题，是否有一种完美的解决办法呢？既能开发xlam或AddIns程序级的插件，同时又可以让配置文件信息跟着用户的文档走。 （如果是xlsm或文档型VSTO项目可以做到数据在文件中，但代码也仅能对此文档有效，对其他文档无效，也不是此处需要的完美解决方案）

大部分人能想到的方案是在自定义属性里CustomDocumentProperties 添加配置信息，但自定义属性，它的容量有限，且内容存储仅为文本型且有长度限制。

自定义属性

当需要存储复杂的结构如图片二进制数据时，用自定义属性就无能为力了，像Excel催化剂在上一波中提及的PictureBox关闭后不能存储的问题，如果需要存储其图片信息，供下一次打开文件时重新以PictureBox写入并关联对应的事件，这种方法显然仍然不符合要求。

使用CustomXMLPart对象保存配置信息

在xlsx版Excel文件中，区别于传统的xls文件，其文件本质是xml文件集合，在xlsx版文件结构中，除去工作表外，有另外一个对象同样可以存储数据，其存储数据的要求只要是xml格式的数据即可。

且此对象不止于在VSTO内使用，在VBA上也同样有此对象可供计用。

CustomXMLPart对象模型

虽然有此对象，但其要求的数据类型是xml，在VBA环境里操作xml将是个非常痛苦的事情，但在VSTO上操作，简直不能再简单的事情。

使用方式大概是先Add一个CustomXMLPart，然后使用LoadXML方法即可把xml内容加载到此对象中，下次访问时，只需访问其XML属性，即可将xml内容取出（文本格式）。

在.net环境下，特别是Ado.Net中，只需一个方法即可将一个DataTable转换为XML或将一个XML文件还原为DataTable对象，数据配置文件，无论多复杂，都可以用DataTable很轻松地管理数据，包括图片格式的二进制字段数据。

有以上.Net天然地对XML友好使用体验，在Excel上使用CustomXMLPart对象就变得十分轻松自在。

具体场景分享

在Excel催化剂的功能中，有好几个经典功能用上了CustomXMLPart存储配置信息，跟随文档传输的安全。

插入图片功能，将PictureBox容器及相关属性序列化为CustomXMLPart，如图片对象，工作表中插入的单元格位置信息等。
数据有效性验证功能中，验证规则及验证的单元格范围信息
数据辅助录入功能中，辅助数据源及对应的录入单元格范围信息
多级层级联动功能，多级数据源及对应的录入单元格范围信息。

核心代码分享

绑定事件，根据需要，绑定打开、关闭文件，或激活、失去激活状态事件

            Common.ExcelApp.WorkbookOpen += ExcelApp_WorkbookOpenLoadXMLPart;

            Common.ExcelApp.WorkbookBeforeClose += ExcelApp_WorkbookBeforeCloseSerialXML;
            Common.ExcelApp.WorkbookActivate += ExcelApp_WorkbookActivateLoadXMLPart;
            Common.ExcelApp.WorkbookDeactivate += ExcelApp_WorkbookDeactivateSerialXML;

使用上一波的图片功能示例打开文件时，运行图片CustomXMLPart的反序列化

        private void ExcelApp_WorkbookOpenLoadXMLPart(Excel.Workbook wb)
        {
            LoadXMLPartOfPictureBoxInfo(wb);

        }

  private static void LoadXMLPartOfPictureBoxInfo(Excel.Workbook Wb)
        {
            try
            {
                Common.ExcelApp.ScreenUpdating = false;
                var prop = Utilities.CustomXMLPartUtility.GetCustomPropertyByPropName(Wb, pictureBoxInfoXmlPartIdName);

                if (prop != null)
                {
                    DataSet.MemoryDataset.PictureBoxInfoDataTable pictureBoxInfos = new DataSet.MemoryDataset.PictureBoxInfoDataTable();

                    Utilities.CustomXMLPartUtility.FillTableFromXMLPart(Wb, pictureBoxInfos, prop);

                    foreach (var grp in pictureBoxInfos.Rows.Cast<DataSet.MemoryDataset.PictureBoxInfoRow>().GroupBy(s => s.PicSheetName))
                    {
                        Excel.Worksheet sht = Common.ExcelApp.ActiveWorkbook.Worksheets[grp.Key];

                        Worksheet vstoSht = Globals.Factory.GetVstoObject(sht);

                        foreach (var row in grp.Select(s => s))
                        {
                            Excel.Range dstRange = sht.Range[row.PicTopLeftCellAddress, row.PicBottomRightCellAddress];
                            Image image = Image.FromStream(new MemoryStream(row.PictureImageBytes));
                            ShapeArrange.AddPictureBoxToVstoWorkSheet(
                                dstRange: dstRange,
                                img: image,
                                fileExt: row.PicExt,
                                hasBordersMargin: row.PicIsLookupFromRangValue,
                                isLookupFromRangValue: row.PicIsLookupFromRangValue,
                                offsetRow: row.PicOffsetRow,
                                offsetCol: row.PicOffsetCol);//
                        }


                    }
                }
            }
            catch
            {

            }
            finally
            {
                Common.ExcelApp.ScreenUpdating = true;
            }
        }

关闭Excel文件时，将配置信息序列化到XML中存储

   private static void SerialXMLOfPictureBox(Excel.Workbook Wb)
        {
            try
            {
                Common.ExcelApp.ScreenUpdating = false;
                bool isExistsPictureBox = false;
                if (Globals.Factory.HasVstoObject(Wb))//是否是vsto工作薄
                {
                    isExistsPictureBox = CheckExistingPictrueBox(Wb, isExistsPictureBox);

                    if (isExistsPictureBox == true)
                    {
                        //是否保存工作薄
                        if (Wb.Saved == false)
                        {
                            if (MessageBox.Show(text: "检测到有未保存的内容，请确认是否保存修改内容。\n" +
                                "点击【是】保存修改内容并退出此工作薄，下次打开此工作薄时，工具插入的图片的双击、右键鼠标的操作仍然有效。\n" +
                                "单击【否】不保存修改内容退出此工作薄，且在下次打开此工作薄时，工具插入的图片的双击、右键鼠标的操作会失效。",
                                caption: "未保存内容确认", buttons: MessageBoxButtons.YesNo) == DialogResult.Yes)
                            {
                                Wb.Save();
                            }
                            else
                            {
                                return;
                            }
                        }
                        //这里和用户交互，确定是否需要删除图片
                        bool isReserveOlePictures = false;
                        if (MessageBox.Show(text: "文档是否需要共享给其他没有安装【Excel催化剂】插件的人查阅？\n" +
                            "点击【是】将保留工作薄中的图片，共享给其他人时，就算没安装【Excel催化剂】插件，插件插入的图片仍可查阅，但双击、右键鼠标的操作会失效。\n" +
                            "单击【否】将删除工作薄中的图片，在安装【Excel催化剂】插件的电脑重新打开此工作薄时，插件插入的图片重新生成，减少存储一份图片副本，对Excel文件大小有要求时选择此项。",
                                caption: "未保存内容确认", buttons: MessageBoxButtons.YesNo) == DialogResult.Yes)
                        {
                            isReserveOlePictures = true;
                        }

                        DataSet.MemoryDataset.PictureBoxInfoDataTable pictureBoxTable = new DataSet.MemoryDataset.PictureBoxInfoDataTable();
                        //开始遍历工作表
                        foreach (Excel.Worksheet sht in Wb.Worksheets)
                        {
                            if (Globals.Factory.HasVstoObject(sht))
                            {
                                ShapeArrange.AddSerializePictureBoxsToDataTable(sht, pictureBoxTable, isReserveOlePictures);//添加图片信息至dataTable里
                            }
                        }
                        //序列化到xml文件中
                        string xmlString = Utilities.CustomXMLPartUtility.SerialDataTableToXMLString(pictureBoxTable);

                        Utilities.CustomXMLPartUtility.AddOrModifyCustomXMLPart(Wb, xmlString, pictureBoxInfoXmlPartIdName);

                    }
                    else
                    {
                        try
                        {
                            DocumentProperty prop = Common.ExcelApp.ActiveWorkbook.CustomDocumentProperties[pictureBoxInfoXmlPartIdName];
                            CustomXMLPart xmlPart = Wb.CustomXMLParts.SelectByID(prop.Value);
                            xmlPart.Delete();
                        }
                        catch
                        {

                        }
                    }

                    Wb.Save();
                }
            }
            catch (Exception)
            {

            }
            finally
            {
                Common.ExcelApp.ScreenUpdating = true;
            }
        }

在ADO.Net中进行DataTable的序列化和反序列化真的容易得很，直接贴源代码

        public static string SerialDataTableToXMLString(DataTable dt)
        {
            MemoryStream stream = new MemoryStream();
            dt.WriteXml(stream, XmlWriteMode.WriteSchema);
            stream.Position = 0;
            StreamReader reader = new StreamReader(stream);
            string xmlString = reader.ReadToEnd();
            return xmlString;
        }

        public static string SerialDataSetToXMLString(System.Data.DataSet ds)
        {
            MemoryStream stream = new MemoryStream();
            ds.WriteXml(stream, XmlWriteMode.WriteSchema);
            stream.Position = 0;
            StreamReader reader = new StreamReader(stream);
            string xmlString = reader.ReadToEnd();
            return xmlString;
        }

将XML加入到CustomXMLPart也是很容易的事情

        public static void AddOrModifyCustomXMLPart(Excel.Workbook Wb, string xmlString, string xmlPartIdName)
        {
            //先查找有没有自定义属性PictureBoxInfoXmlPartId，有的话就是上次已经保存过xmlpart，这次只需改写内容
            DocumentProperty prop = GetCustomPropertyByPropName(Wb, xmlPartIdName);
            CustomXMLPart customXMLPart;
            if (prop != null)
            {
                CustomXMLPart oldCustomXMLPart = Wb.CustomXMLParts.SelectByID(prop.Value);
                if (oldCustomXMLPart != null)
                {
                    oldCustomXMLPart.Delete();
                }

                customXMLPart = Wb.CustomXMLParts.Add(xmlString);
                Wb.CustomDocumentProperties[xmlPartIdName].Value = customXMLPart.Id;
            }
            else
            {
                customXMLPart = Wb.CustomXMLParts.Add(xmlString);
                Wb.CustomDocumentProperties.Add(
                    Name: xmlPartIdName,
                    LinkToContent: false,
                    Type: Microsoft.Office.Core.MsoDocProperties.msoPropertyTypeString,
                    Value: customXMLPart.Id);
            }

        }

上述方法中，同样用到了自定义文档属性，用于存放customXMLPart的ID信息

 Wb.CustomDocumentProperties.Add(
                    Name: xmlPartIdName,
                    LinkToContent: false,
                    Type: Microsoft.Office.Core.MsoDocProperties.msoPropertyTypeString,
                    Value: customXMLPart.Id);