C# 獲取Excel里引用的外部其他excel文件清單

關鍵方法:mySheet.Application.ActiveWorkbook.LinkSources(XlLink.xlExcelLinks);

詳情見Office官方說明 https://docs.microsoft.com/zh-cn/office/vba/api/excel.workbook.linksources

主要代碼如下

        Application myExcel = null;//引用Excel Application类別
        Workbook myBook = null;//引用活页簿类別
        Worksheet mySheet = null;//引用工作表类別            
        Range myRange = null;//引用Range类別
        string excelPath = "";

        public ExcelModify(string filePath)
        {
            excelPath = filePath;
            try
            {

                myExcel = new Application();//实例化Excel Application
                myExcel.DisplayAlerts = false;//停用警告
                myExcel.Visible = false;      //Excel 不可见  
                // 3 時,打開excel沒有更新提示,詳情見Office官方說明
                myBook = myExcel.Workbooks._Open(excelPath, "3", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);                          
                // Microsoft.Office.Interop.Excel 引用的屬性 Embed interop type 改成false才可以
                mySheet = (Worksheet)myBook.Worksheets[1];//Excel文件打开工作簿的第一个文件
            }
            catch (Exception ex)
            {
                this.Close();
                throw ex;
            }
        }
        /// <summary>
        /// 將object[*]轉化為object[]
        /// </summary>
        /// <param name="arr"></param>
        /// <returns></returns>
        private object[] ConvertArray(Array arr)
        {
            int lb = arr.GetLowerBound(0);
            var ret = new object[arr.GetUpperBound(0) - lb + 1];
            for (int ix = 0; ix < ret.Length; ++ix)
            {
                ret[ix] = arr.GetValue(ix + lb);
            }
            return ret;
        }

        public void ReadFile()
        {
            string notex = "";

            if (mySheet != null && mySheet.Application.ActiveWorkbook != null)
            {
                object oj = mySheet.Application.ActiveWorkbook.LinkSources(XlLink.xlExcelLinks);
                //object[] obj = (object[])oj; // 報錯
                object[] obj = ConvertArray(oj as Array);


                if (obj != null && obj.Length > 0)
                {
                    for (int i = 0; i < obj.Length; i++)
                    {
                        string fi = (string)obj[i];
                        if (!File.Exists(fi))
                        {
                            notex += fi;
                        }
                    }
                }
            }
        }

 

遍歷外部鏈接的的單元格清單

public Dictionary<string, string> GetSheetLinksData(string xlsPath)
        { 
             Application _appliation=null;
            Workbook _workbook = null;
            Object missing = System.Reflection.Missing.Value;
            Dictionary<string, string> linksData = new Dictionary<string, string>();
            try
            {
                FileInfo xlsInfo = new FileInfo(xlsPath);
                _appliation = new Application();
                _appliation.DisplayAlerts = false;//停用警告
                _appliation.Visible = false;      //Excel 不可见                 
                // 採用 3,和另存為,保證新文件數據是更新后的數據
                _workbook = (Workbook)_appliation.Workbooks.Open(xlsPath, "3", missing, missing, missing, missing, missing, missing,
                                                                           missing, missing, missing, missing, missing, missing, missing);
                Worksheet mySheet = (Worksheet)_workbook.Worksheets[1]; //引用工作表类別  
                try
                {
                    Range cs = mySheet.Cells.SpecialCells(XlCellType.xlCellTypeFormulas);
                    if (cs != null)
                    {
                        foreach (Range c in cs)
                            try
                            {
                                string col = convertToCharacter(c.Column) + (c.Row).ToString();
                                if (c.Formula.ToString() != "" && c.Formula.ToString().Contains("$"))
                                {
                                    linksData.Add(col, c.Formula.ToString());
                                }
                            }
                            catch { }
                    }
                }
                catch
                {
                }                
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally {
                if (_workbook != null)
                {
                    //關掉當前sheet
                    _workbook.Close(missing, missing, missing);
                    //關掉excel
                    _appliation.Workbooks.Close();
                    //退出程序
                    _appliation.Quit();
                }
            }
            return linksData;
        }

        /// <summary>
        /// 十進制轉化為26進制
        /// </summary>
        /// <param name="i"></param>
        /// <returns></returns>
        private string convertToCharacter(int i)
        {
            char[] list = "ABCDEFGHIJKLMNOPQRSTUVWXYZ".ToCharArray();
            StringBuilder sb = new StringBuilder();
            while ((i - 1) / 26 != 0)
            {
                sb.Append(list[i / 26 - 1]);
                i = i % 26;
            }
            i = (i - 1) % 26;
            sb.Append(list[i]);
            return sb.ToString();
        }