解决在IIS中调用Microsoft Office Excel组件后进程无法正常退出的问题
来源:http://www.cnblogs.com/ahui/archive/2013/03/05/2944441.html
有一个项目用到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+资源,也得显示释放资源
代码如下:
1 public class ExcelApp : IDisposable 2 { 3 private Application _excel; 4 private Workbooks _workbooks; 5 private Workbook _workbook; 6 private Worksheet _worksheet; 7 8 public ExcelApp() 9 { 10 _excel = new ApplicationClass { Visible = false, DisplayAlerts = false }; 11 _workbooks = _excel.Workbooks; 12 } 13 14 public int ColCount { get; set; } 15 16 public int RowCount { get; set; } 17 18 public string WorksheetName 19 { 20 get 21 { 22 return _worksheet != null ? _worksheet.Name : null; 23 } 24 set 25 { 26 if (_worksheet != null) 27 { 28 _worksheet.Name = value; 29 } 30 } 31 } 32 33 #region Get Excel Range 34 public Range GetCell(int rowIndex, int cellIndex) 35 { 36 Range cells = null; 37 Range range = null; 38 39 try 40 { 41 cells = _excel.Cells; 42 range = (Range)cells[1 + rowIndex, 1 + cellIndex]; 43 } 44 finally 45 { 46 Marshal.ReleaseComObject(cells); 47 } 48 49 return range; 50 } 51 52 public Range GetColumn(int cellIndex) 53 { 54 Range range = null; 55 Range cells = null; 56 object rangeX = null; 57 object rangeY = null; 58 59 try 60 { 61 cells = _excel.Cells; 62 rangeX = cells[1, 1 + cellIndex]; 63 rangeY = cells[RowCount, 1 + cellIndex]; 64 range = _worksheet.get_Range(rangeX, rangeY); 65 } 66 finally 67 { 68 Marshal.ReleaseComObject(rangeX); 69 Marshal.ReleaseComObject(rangeY); 70 Marshal.ReleaseComObject(cells); 71 } 72 73 return range; 74 } 75 76 public Range GetRange(int xRowIndex, int xCellIndex, int yRowIndex, int yCellIndex) 77 { 78 Range range = null; 79 Range cells = null; 80 object rangeX = null; 81 object rangeY = null; 82 83 try 84 { 85 cells = _excel.Cells; 86 rangeX = cells[1 + xRowIndex, 1 + xCellIndex]; 87 rangeY = cells[yRowIndex + 1, yCellIndex + 1]; 88 range = _worksheet.get_Range(rangeX, rangeY); 89 } 90 finally 91 { 92 Marshal.ReleaseComObject(rangeX); 93 Marshal.ReleaseComObject(rangeY); 94 Marshal.ReleaseComObject(cells); 95 } 96 97 return range; 98 } 99 #endregion 100 101 public void Save(string fullFilePath) 102 { 103 if (string.IsNullOrEmpty(fullFilePath)) 104 { 105 throw new ArgumentNullException("fullFilePath"); 106 } 107 108 string directory = Path.GetDirectoryName(fullFilePath); 109 110 if (string.IsNullOrEmpty(directory)) 111 { 112 throw new ArgumentException("fullFilePath is not a valid file path."); 113 } 114 115 if (!Directory.Exists(directory)) 116 { 117 Directory.CreateDirectory(directory); 118 } 119 120 _workbook.SaveCopyAs(fullFilePath); 121 } 122 123 public void Open(string fullFilePath) 124 { 125 _workbook = _workbooks._Open(fullFilePath, 126 Missing.Value, Missing.Value, 127 Missing.Value, Missing.Value, 128 Missing.Value, Missing.Value, 129 Missing.Value, Missing.Value, 130 Missing.Value, Missing.Value, 131 Missing.Value, Missing.Value); 132 133 _worksheet = (Worksheet)_workbook.ActiveSheet; 134 135 ColCount = 0; 136 RowCount = 0; 137 } 138 139 public void AddWorkbook() 140 { 141 _workbook = _workbooks.Add(true); 142 _worksheet = (Worksheet)_workbook.ActiveSheet; 143 144 ColCount = 0; 145 RowCount = 0; 146 } 147 148 public void Reset() 149 { 150 Close(); 151 AddWorkbook(); 152 } 153 154 private void Close() 155 { 156 if (_worksheet != null) 157 { 158 Marshal.ReleaseComObject(_worksheet); 159 } 160 if (_workbook != null) 161 { 162 _workbook.Close(false, null, null); 163 Marshal.ReleaseComObject(_workbook); 164 } 165 _worksheet = null; 166 _workbook = null; 167 } 168 169 #region IDisposable Members 170 171 public void Dispose() 172 { 173 try 174 { 175 Close(); 176 177 if (_workbooks != null) 178 { 179 Marshal.ReleaseComObject(_workbooks); 180 } 181 if (_excel != null) 182 { 183 _excel.Quit(); 184 Marshal.ReleaseComObject(_excel); 185 } 186 } 187 catch (Exception ex) 188 { 189 Console.WriteLine("dispose ExcelApp object failed", ex); 190 } 191 192 _workbooks = null; 193 _excel = null; 194 } 195 196 #endregion 197 } 198 199 200 public class Disposable 201 { 202 public static Disposable<T> Create<T>(T o) where T : class 203 { 204 return new Disposable<T>(o); 205 } 206 } 207 208 public class Disposable<T> : IDisposable where T : class 209 { 210 public T Value; 211 212 internal Disposable(T o) 213 { 214 Value = o; 215 } 216 217 public void Dispose() 218 { 219 if (Value != null) 220 { 221 Marshal.ReleaseComObject(Value); 222 } 223 } 224 }
![](https://images.cnblogs.com/OutliningIndicators/ExpandedBlockStart.gif)
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); } } }
调用示例:
1 using (var excel = new ExcelApp()) 2 { 3 excel.AddWorkbook(); 4 5 using (var range = Disposable.Create(excel.GetCell(0, 0))) 6 { 7 using (var font = Disposable.Create(range.Value.Font)) 8 { 9 font.Value.Color = 255; 10 } 11 12 range.Value.Value = 200; 13 } 14 }
![](https://images.cnblogs.com/OutliningIndicators/ExpandedBlockStart.gif)
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不能退出的问题总算圆满解决了,贴出来和大家分享一下,免得其他人也在这上面浪费时间