使用COM打开Excel文档注意事项

本文主要讲解程序中打开Excel文档，读写Excel文档可以参照前章：

C#读写Excel实践笔记

C#使用NPOI读写Excel的注意事项

 

如果只是单纯的打开Excel文档，建议使用：

System.Diagnostics.Process.Start("EXECL.EXE", @"C:\Users\test.xls");

因为它不会留下后台没用进程。

 

如果需要跳转到相应的sheet或者单元格，只能使用COM了。坏处就是如果不清理就会留下后台进程。

参照需要引入COM：Microsoft Excel 14.0 Object Library 或者Microsoft Excel 16.0 Object Library

命名空间：Microsoft.Office.Interop.Excel

        private static Application _app = null;
        private static Dictionary<string, Workbook> _bookDic = new Dictionary<string, Workbook>();

        public static void Open(string path, string sheetName, int row = 1, int column = 1)
        {
            if (_app == null)
            {
                _app = new Application();
            }

            Workbook workbook = null;
            if (_bookDic.ContainsKey(path))
            {
                workbook = _bookDic[path];
            }
            else
            {
                workbook = _app.Workbooks.Open(Filename: path, ReadOnly: false);
                _bookDic.Add(path, workbook);
            }

            var workSheet = workbook?.Worksheets?.getSheetByName(sheetName);
            if (workSheet != null)
            {
                workSheet.Application.Visible = false;
                workSheet.Application.Visible = true;

                workSheet.Application.DisplayAlerts = false;
                // 为了切换WorkBook，这步非常关键。
                // 不然会出异常：0x800A03EC
                workSheet.Application.ActiveWorkbook.Activate();

                workbook.Activate();
                workSheet.Select();
                workSheet.Cells[row, column]?.Activate();

                workSheet.Application.DisplayAlerts = true;

                System.Runtime.InteropServices.Marshal.ReleaseComObject(workSheet);
            }
        }

        private static Worksheet getSheetByName(this Sheets workSheets, string sheetName)
        {
            foreach (Worksheet sheet in workSheets)
            {
                if (sheet.Name == sheetName)
                {
                    return sheet;
                }
            }

            return null;
        }

        public static void Dispose()
        {
            try
            {
                if (_app != null)
                {
                    foreach (var book in _bookDic)
                    {
                        System.Runtime.InteropServices.Marshal.ReleaseComObject(book.Value.Worksheets);
                        System.Runtime.InteropServices.Marshal.ReleaseComObject(book.Value);
                    }

                    _app.Workbooks.Close();
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(_app.Workbooks);

                    _app.Quit();
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(_app);
                }
            }
            catch (Exception e)
            {
                // throw e;
            }
        }

Dispose方法如果忘记执行，哪怕Excel你关闭了，进程中还是会留下一个Excel后台进程。