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

wojkxlq

　　有一个项目用到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);
}
}
}
View Code 　　调用示例:


View Code


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不能退出的问题总算圆满解决了，贴出来和大家分享一下，免得其他人也在这上面浪费时间