解决在IIS中调用Microsoft Office Excel组件后进程无法正常退出的问题

有一个项目用到Excel组件产生报表，本以为这个通用功能是个很简单的case，没想到结果却花了不少时间

本人开发环境: Win7 64bit + IIS7.5 + VS2012

 

最开始碰到的问题是NetworkService无法访问com组件，需要对帐户进行授权，这个相信很多人都碰到过，也很好解决

1.运行：mmc comexp.msc /32，找到我的电脑 -> DCom配置中的Microsoft Excel Application
2.在Microsoft Excel Application上点击右键，选择"属性"
3.点击"标识"标签,选择"交互式用户"
4.点击"安全"标签,在"启动和激活权限"上点击"自定义",然后点击对应的"编辑"按钮,在弹出的"安全性"对话框中填加一个"NETWORK SERVICE"用户(注意要选择本计算机名),并给它赋予"本地启动"和"本地激活"权限.
5.依然是"安全"标签,在"访问权限"上点击"自定义",然后点击"编辑",在弹出的"安全性"对话框中也填加一个"NETWORK SERVICE"用户,然后赋予"本地访问"权限.

 

之后程序能正常运行了，但这个项目需要并发处理，可能有多个ApplicationClass实例，这时问题就来了

调用_Application.Quit()之后，Excel.exe进程仍然存在，搜索之后，解决方案来了:

[DllImport("User32.dll", CharSet = CharSet.Auto)]
public static extern int GetWindowThreadProcessId(IntPtr hwnd, out int proecessId);

public static void KillExcel(Excel.Application excel)
{
    var t = new IntPtr(excel.Hwnd);
    int proecessId = 0;
    GetWindowThreadProcessId(t, out proecessId);
    Process.GetProcessById(proecessId).Kill();
}

 

用VS调试正常工作，在Console应用里也OK，但如果用IIS启动就不行了，断点调用发现GetWindowThreadProcessId取得的processId一直为0。

搜索了一下网站，大概意思是运行在不同的Session级别，Excel是以"交互式用户"启动，即本地Administrator，所以取不到对应的进程Id，

于是又在Microsoft Excel Application的属性->标识中将启动用户这项选中，发现这回Excel进程是以NetworkService启动，测试代码如下:

Application excel = new ApplicationClass { Visible = false, DisplayAlerts = false };
excel.Quit();
KillExcel(excel);

也能正常退出，好像问题已经解决了，OK，来编写业务代码了

Workbook workbook = excel.Workbooks.Add(true);

一调试，马上报异常，继续Google和看微软官方网站，发现Office自动化必须以交互式用户方式来启动，而NetworkService是虚拟的，所以这条路显然走不通。

即使你想到给NetworkService提升权限，也不清楚正常运行Excel倒底要那些权限，搜索后也无果，大部分推荐使用第三方组件，或者建立一个专用的帐户，或者建立一个Service项目来处理Http请求。

这些方式各有各的不足，将建立专用域帐户做一备选方案后，继续寻求解决办法，最先找到的代码是:

Application excel = new ApplicationClass { Visible = false, DisplayAlerts = false };
Workbook workbook = excel.Workbooks.Add(true);
Worksheet worksheet = (Worksheet)workbook.ActiveSheet;
// do sth
excel.Quit();
Marshal.ReleaseComObject(worksheet);
workbook.Close(false, null, null);
Marshal.ReleaseComObject(workbook);
Marshal.ReleaseComObject(excel);

这段代码相信遇到这个问题的人都试过吧，结果还是不行，在IIS下面启动的Excel进程不一定按你的要求及时退出

 

后来一位高手同事给了个链接 http://support.microsoft.com/kb/317109

测试代码:

Application excel = new ApplicationClass { Visible = false, DisplayAlerts = false };
Workbooks workbooks = excel.Workbooks;
Workbook workbook = workbooks.Add(true);
Worksheet worksheet = (Worksheet)workbook.ActiveSheet;
// do sth
excel.Quit();
Marshal.ReleaseComObject(worksheet);
workbook.Close(false, null, null);
Marshal.ReleaseComObject(workbook);
Marshal.ReleaseComObject(workbooks);
Marshal.ReleaseComObject(excel);

这段代码测试正常，看到希望了，继续Coding，然后测试。

服务器运行一段时间后，仍然存在大量的Excel进程没有退出，有心的人估计从两段稍有差别的代码看到问题的所在了，问题就在于:

调用com+对象后，必须要及时释放资源，那怕你只是请求了某个属性，只要这个属性是com+资源，也得显示释放资源

代码如下:

public class ExcelApp : IDisposable
{
    private Application _excel;
    private Workbooks _workbooks;
    private Workbook _workbook;
    private Worksheet _worksheet;

    public ExcelApp()
    {
        _excel = new ApplicationClass { Visible = false, DisplayAlerts = false };
        _workbooks = _excel.Workbooks;
    }

    public int ColCount { get; set; }

    public int RowCount { get; set; }

    public string WorksheetName
    {
        get
        {
            return _worksheet != null ? _worksheet.Name : null;
        }
        set
        {
            if (_worksheet != null)
            {
                _worksheet.Name = value;
            }
        }
    }

    #region Get Excel Range
    public Range GetCell(int rowIndex, int cellIndex)
    {
        Range cells = null;
        Range range = null;

        try
        {
            cells = _excel.Cells;
            range = (Range)cells[1 + rowIndex, 1 + cellIndex];
        }
        finally
        {
            Marshal.ReleaseComObject(cells);
        }

        return range;
    }

    public Range GetColumn(int cellIndex)
    {
        Range range = null;
        Range cells = null;
        object rangeX = null;
        object rangeY = null;

        try
        {
            cells = _excel.Cells;
            rangeX = cells[1, 1 + cellIndex];
            rangeY = cells[RowCount, 1 + cellIndex];
            range = _worksheet.get_Range(rangeX, rangeY);
        }
        finally
        {
            Marshal.ReleaseComObject(rangeX);
            Marshal.ReleaseComObject(rangeY);
            Marshal.ReleaseComObject(cells);
        }

        return range;
    }

    public Range GetRange(int xRowIndex, int xCellIndex, int yRowIndex, int yCellIndex)
    {
        Range range = null;
        Range cells = null;
        object rangeX = null;
        object rangeY = null;

        try
        {
            cells = _excel.Cells;
            rangeX = cells[1 + xRowIndex, 1 + xCellIndex];
            rangeY = cells[yRowIndex + 1, yCellIndex + 1];
            range = _worksheet.get_Range(rangeX, rangeY);
        }
        finally
        {
            Marshal.ReleaseComObject(rangeX);
            Marshal.ReleaseComObject(rangeY);
            Marshal.ReleaseComObject(cells);
        }

        return range;
    }
    #endregion

    public void Save(string fullFilePath)
    {
        if (string.IsNullOrEmpty(fullFilePath))
        {
            throw new ArgumentNullException("fullFilePath");
        }

        string directory = Path.GetDirectoryName(fullFilePath);

        if (string.IsNullOrEmpty(directory))
        {
            throw new ArgumentException("fullFilePath is not a valid file path.");
        }

        if (!Directory.Exists(directory))
        {
            Directory.CreateDirectory(directory);
        }

        _workbook.SaveCopyAs(fullFilePath);
    }

    public void Open(string fullFilePath)
    {
        _workbook = _workbooks._Open(fullFilePath,
                                        Missing.Value, Missing.Value,
                                        Missing.Value, Missing.Value,
                                        Missing.Value, Missing.Value,
                                        Missing.Value, Missing.Value,
                                        Missing.Value, Missing.Value,
                                        Missing.Value, Missing.Value);

        _worksheet = (Worksheet)_workbook.ActiveSheet;

        ColCount = 0;
        RowCount = 0;
    }

    public void AddWorkbook()
    {
        _workbook = _workbooks.Add(true);
        _worksheet = (Worksheet)_workbook.ActiveSheet;

        ColCount = 0;
        RowCount = 0;
    }

    public void Reset()
    {
        Close();
        AddWorkbook();
    }

    private void Close()
    {
        if (_worksheet != null)
        {
            Marshal.ReleaseComObject(_worksheet);
        }
        if (_workbook != null)
        {
            _workbook.Close(false, null, null);
            Marshal.ReleaseComObject(_workbook);
        }
        _worksheet = null;
        _workbook = null;
    }

    #region IDisposable Members

    public void Dispose()
    {
        try
        {
            Close();

            if (_workbooks != null)
            {
                Marshal.ReleaseComObject(_workbooks);
            }
            if (_excel != null)
            {
                _excel.Quit();
                Marshal.ReleaseComObject(_excel);
            }
        }
        catch (Exception ex)
        {
            Console.WriteLine("dispose ExcelApp object failed", ex);
        }

        _workbooks = null;
        _excel = null;
    }

    #endregion
}


public class Disposable
{
    public static Disposable<T> Create<T>(T o) where T : class
    {
        return new Disposable<T>(o);
    }
}

public class Disposable<T> : IDisposable where T : class
{
    public T Value;

    internal Disposable(T o)
    {
        Value = o;
    }

    public void Dispose()
    {
        if (Value != null)
        {
            Marshal.ReleaseComObject(Value);
        }
    }
}

调用示例:

using (var excel = new ExcelApp())
{
    excel.AddWorkbook();

    using (var range = Disposable.Create(excel.GetCell(0, 0)))
    {
        using (var font = Disposable.Create(range.Value.Font))
        {
            font.Value.Color = 255;
        }

        range.Value.Value = 200;
    }
}

至此在IIS里调用Excel不能退出的问题总算圆满解决了，贴出来和大家分享一下，免得其他人也在这上面浪费时间