c# excel vsto 批注公式动态替换

 /// <summary>
        /// 设置注释公式赋给作价表
        /// </summary>
        /// <param name="worksheet"></param>
        /// <param name="methodRange"></param>
        /// <param name="methodName"></param>
        public static void SetCommentFormula(Worksheet worksheet)
        {
            var comments = worksheet.Comments;
            foreach (var com in comments) {
                var convert =(Comment)com;               
                var comText = convert.Text();
                if (!string.IsNullOrEmpty(comText) && comText.Contains("="))
                {
                    var address = GetCommentAddress(worksheet, comText);
                    Regex regex = new Regex(@"(?<=\{)[^}]*(?=\})", RegexOptions.IgnoreCase);
                    MatchCollection matches = regex.Matches(comText);
                    var result = matches.Cast<Match>().Select(m => m.Value).Distinct().ToList();
                    foreach (var res in result) {
                        var location = GetCommentAddress(worksheet, res);
                        if (!string.IsNullOrEmpty(location))
                        {
                            comText = comText.Replace("{" + res + "}", location);
                        }
                    }
                    Range range = worksheet.get_Range(address, Type.Missing);
                    range.Formula = comText;
                }
            }
        }


/// <summary>
        /// 获取批注单元格地址
        /// </summary>
        /// <param name="worksheet">工作表</param>
        /// <param name="comment">批注</param>
        /// <param name="isRemove">是否去掉$符 默认值true</param>
        /// <returns></returns>
        public static string GetCommentAddress(Excel.Worksheet worksheet, string comment, bool isRemove = true)
        {
            try
            {
                string address = "";
                Range rngAll = worksheet.UsedRange.SpecialCells(XlCellType.xlCellTypeComments);
                Range rng = null;
                foreach (Range range in rngAll.Cells)
                {
                    if (range.Comment != null && range.Comment.Shape.AlternativeText.Replace("文本框: ", "") == comment)
                    {
                        rng = range;
                        break;
                    }
                }
                if (rng != null)
                {
                    if (isRemove)
                    {
                        address = rng.Address.Replace("$", "");
                    }
                    else
                    {
                        address = rng.Address;
                    }

                }
                return address;
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.ToString());
                return "";
            }
        }

实际开发中遇到设置模板复制到实际的作业表中，有些公式不能正常对应，用户可能会增删列；

通过设置批注动态赋公式，需要把变量、公式都打上批注，

再根据获取批注对应的表格找到对应的格子如I1替换公式。

第一次加载模板不管用户在怎么删除作业表里的列都能正常对应公式。

主要思路：
1、设置变量批注、公式批注：
2、批注转换成所在表格位置；
3、替换公式即可。

 

操作大忌【不要删除批注】