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

拼搏现实的明天。 2023-03-13 13:17 5阅读 0赞

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

詳情見Office官方說明 [https://docs.microsoft.com/zh-cn/office/vba/api/excel.workbook.linksources][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();
            }

[https_docs.microsoft.com_zh-cn_office_vba_api_excel.workbook.linksources]: https://docs.microsoft.com/zh-cn/office/vba/api/excel.workbook.linksources