æå¨Cï¼ï¼
ApplicationClass
ï¼ä¸ä½¿ç¨Exceläºæä½ï¼å¹¶å°ä»¥ä¸ä»£ç æ¾å¨æçfinallyåå¥ä¸ï¼
while (System.Runtime.InteropServices.Marshal.ReleaseComObject(excelSheet) != 0) { }
excelSheet = null;
GC.Collect();
GC.WaitForPendingFinalizers();
尽管è¿ç§å·¥ä½ææï¼ä½æ¯å³ä½¿æå ³éExcelï¼
Excel.exe
è¿ç¨ä»å¨åå°ã ä» å¨æçåºç¨ç¨åºæå¨å ³éåæéæ¾å®ã
æå¨åä»ä¹éï¼è¿æ¯æå ¶ä»æ¹æ³å¯ä»¥ç¡®ä¿äºæä½å¯¹è±¡å¾å°æ£ç¡®å¤çï¼
#1楼
æ ¹æ®ç»éªï¼âåå¿å¨COM对象ä¸ä½¿ç¨ä¸¤ä¸ªç¹âæ¯é¿å æ³æ¼COMå¼ç¨çä¸ä¸ªå¾å¥½çç»éªæ³åï¼ä½æ¯Excel PIAå¯è½å¯¼è´æ³æ¼çæ¹å¼æ¯ä¹çæ¶è¦ææ¾ã
è¿äºæ¹æ³ä¹ä¸æ¯è®¢é ç±Excel对象模åçCOMå¯¹è±¡å ¬å¼çä»»ä½äºä»¶ã
ä¾å¦ï¼è®¢é Applicationç±»çWorkbookOpenäºä»¶ã
å ³äºCOMäºä»¶çä¸äºç论
COMç±»éè¿åè°æ¥å£å ¬å¼ä¸ç»äºä»¶ã 为äºè®¢é äºä»¶ï¼å®¢æ·ç«¯ä»£ç å¯ä»¥ç®åå°æ³¨åä¸ä¸ªå®ç°åè°æ¥å£ç对象ï¼å¹¶ä¸COMç±»å°ååºç¹å®äºä»¶æ¥è°ç¨å ¶æ¹æ³ã ç±äºåè°æ¥å£æ¯COMæ¥å£ï¼å æ¤å®ç°å¯¹è±¡æ责任为任ä½äºä»¶å¤çç¨åºåå°å®æ¥æ¶å°çä»»ä½COM对象ï¼ä½ä¸ºåæ°ï¼çå¼ç¨è®¡æ°ã
Excel PIAå¦ä½å ¬å¼COMäºä»¶
Excel PIAå°Excel Applicationç±»çCOMäºä»¶å ¬å¼ä¸ºå¸¸è§.NETäºä»¶ã åªè¦å®¢æ·ç«¯ä»£ç é¢è®¢äº.NETäºä»¶ ï¼å¼ºè°â aâï¼ï¼PIAå°±ä¼å建å®ç°åè°æ¥å£çç±»çå®ä¾ï¼å¹¶å°å ¶æ³¨åå°Excelã
å æ¤ï¼ååºäº.NET代ç çä¸å订é 请æ±ï¼è®¸å¤åè°å¯¹è±¡å·²å¨Excelä¸æ³¨åã æ¯ä¸ªäºä»¶è®¢é æä¸ä¸ªåè°å¯¹è±¡ã
ç¨äºäºä»¶å¤ççåè°æ¥å£æå³çï¼PIAå¿ é¡»ä¸ºæ¯ä¸ª.NETäºä»¶è®¢é 请æ±è®¢é æææ¥å£äºä»¶ã å®ä¸è½éæ©ã æ¥æ¶å°äºä»¶åè°åï¼åè°å¯¹è±¡å°æ£æ¥å ³èç.NETäºä»¶å¤çç¨åºæ¯å¦å¯¹å½åäºä»¶æå ´è¶£ï¼ç¶åè°ç¨è¯¥å¤çç¨åºæéé»å¿½ç¥è¯¥åè°ã
对COMå®ä¾å¼ç¨è®¡æ°çå½±å
ææè¿äºåè°å¯¹è±¡é½ä¸ä¼åå°ä»»ä½åè°æ¹æ³ï¼çè³å¯¹äºé£äºè¢«éé»å¿½ç¥ç对象ï¼æ¶å°çä»»ä½COM对象ï¼ä½ä¸ºåæ°ï¼çå¼ç¨è®¡æ°ã å®ä»¬ä» ä¾é CLRåå¾æ¶éå¨éæ¾COM对象ã
ç±äºGCè¿è¡æ¯ä¸ç¡®å®çï¼å æ¤è¿å¯è½å¯¼è´Excelè¿ç¨æ¨è¿çæ¶é´æ¯é¢æçæ´é¿ï¼å¹¶ç»äººä»¥âå åæ³æ¼âçå°è±¡ã
解
å°ç®å为æ¢ï¼å¯ä¸ç解å³æ¹æ¡æ¯é¿å 为COM类使ç¨PIAçäºä»¶æä¾ç¨åºï¼èç¼åèªå·±çäºä»¶æä¾ç¨åºï¼ä»¥ç¡®å®æ§å°éæ¾COM对象ã
对äºApplicationç±»ï¼å¯ä»¥éè¿å®ç°AppEventsæ¥å£ï¼ç¶å使ç¨IConnectionPointContaineræ¥å£å¨Excelä¸æ³¨åå®ç°æ¥å®æã Applicationç±»ï¼ä»¥åææ使ç¨åè°æºå¶å ¬å¼äºä»¶çCOM对象ï¼å®ç°IConnectionPointContaineræ¥å£ã
#2楼
ç¡®ä¿éæ¾ææä¸Excelç¸å ³ç对象ï¼
æè±äºå 个å°æ¶å°è¯å ç§æ¹æ³ã é½æ¯å¥½ä¸»æï¼ä½æç»äºåç°äºèªå·±çéè¯¯ï¼ å¦ææ¨ä¸éæ¾ææ对象ï¼é£ä¹ä¸è¿°ä»»ä½ä¸ç§æ¹æ³é½æ æ³å¸®å©æ¨ ã ç¡®ä¿éæ¾ææ对象ï¼å æ¬èå´ä¸ï¼
Excel.Range rng = (Excel.Range)worksheet.Cells[1, 1];
worksheet.Paste(rng, false);
releaseObject(rng);
é项å¨è¿éå¨ä¸èµ·ã
#3楼
æ´æ° ï¼æ·»å äºCï¼ä»£ç ï¼å¹¶é¾æ¥å°Windowsä½ä¸
æè±äºä¸äºæ¶é´è¯å¾è§£å³è¿ä¸ªé®é¢ï¼å½æ¶XtremeVBTalkæ¯ææ´»è·åååºæå¿«çã è¿æ¯æåå§æç« çé¾æ¥ï¼å³ä½¿æ¨çåºç¨ç¨åºå´©æºäºï¼ä¹è¦å½»åºå ³éExcel Interopè¿ç¨ ã ä¸é¢æ¯è¯¥å¸åçæè¦ï¼ä»¥åå°ä»£ç å¤å¶å°äºè¯¥å¸åä¸ã
- å¨å¤§å¤æ°æ
åµä¸ï¼ä½¿ç¨
åApplication.Quit()
å ³éInteropè¿ç¨æ¯ææçï¼ä½æ¯å¦æåºç¨ç¨åºå´©æºæ¶å¤±è´¥ï¼å失败ã å³ï¼å¦æåºç¨ç¨åºå´©æºï¼åExcelè¿ç¨ä»å°æ æ³æ£å¸¸è¿è¡ãProcess.Kill()
- 解å³æ¹æ¡æ¯è®©æä½ç³»ç»ä½¿ç¨Win32è°ç¨éè¿Windows Job Objectså¤çè¿ç¨çæ¸ çã å½ä¸»åºç¨ç¨åºæ»äº¡æ¶ï¼å ³èçè¿ç¨ï¼å³Excelï¼ä¹å°ç»æ¢ã
æåç°è¿æ¯ä¸ä¸ªå¹²åç解å³æ¹æ¡ï¼å 为æä½ç³»ç»æ£å¨åæ¸ çå·¥ä½ã æ¨è¦åçå°±æ¯æ³¨å Excelæµç¨ã
Windowsä½ä¸ä»£ç
å è£ Win32 APIè°ç¨ä»¥æ³¨åInteropè¿ç¨ã
public enum JobObjectInfoType
{
AssociateCompletionPortInformation = 7,
BasicLimitInformation = 2,
BasicUIRestrictions = 4,
EndOfJobTimeInformation = 6,
ExtendedLimitInformation = 9,
SecurityLimitInformation = 5,
GroupInformation = 11
}
[StructLayout(LayoutKind.Sequential)]
public struct SECURITY_ATTRIBUTES
{
public int nLength;
public IntPtr lpSecurityDescriptor;
public int bInheritHandle;
}
[StructLayout(LayoutKind.Sequential)]
struct JOBOBJECT_BASIC_LIMIT_INFORMATION
{
public Int64 PerProcessUserTimeLimit;
public Int64 PerJobUserTimeLimit;
public Int16 LimitFlags;
public UInt32 MinimumWorkingSetSize;
public UInt32 MaximumWorkingSetSize;
public Int16 ActiveProcessLimit;
public Int64 Affinity;
public Int16 PriorityClass;
public Int16 SchedulingClass;
}
[StructLayout(LayoutKind.Sequential)]
struct IO_COUNTERS
{
public UInt64 ReadOperationCount;
public UInt64 WriteOperationCount;
public UInt64 OtherOperationCount;
public UInt64 ReadTransferCount;
public UInt64 WriteTransferCount;
public UInt64 OtherTransferCount;
}
[StructLayout(LayoutKind.Sequential)]
struct JOBOBJECT_EXTENDED_LIMIT_INFORMATION
{
public JOBOBJECT_BASIC_LIMIT_INFORMATION BasicLimitInformation;
public IO_COUNTERS IoInfo;
public UInt32 ProcessMemoryLimit;
public UInt32 JobMemoryLimit;
public UInt32 PeakProcessMemoryUsed;
public UInt32 PeakJobMemoryUsed;
}
public class Job : IDisposable
{
[DllImport("kernel32.dll", CharSet = CharSet.Unicode)]
static extern IntPtr CreateJobObject(object a, string lpName);
[DllImport("kernel32.dll")]
static extern bool SetInformationJobObject(IntPtr hJob, JobObjectInfoType infoType, IntPtr lpJobObjectInfo, uint cbJobObjectInfoLength);
[DllImport("kernel32.dll", SetLastError = true)]
static extern bool AssignProcessToJobObject(IntPtr job, IntPtr process);
private IntPtr m_handle;
private bool m_disposed = false;
public Job()
{
m_handle = CreateJobObject(null, null);
JOBOBJECT_BASIC_LIMIT_INFORMATION info = new JOBOBJECT_BASIC_LIMIT_INFORMATION();
info.LimitFlags = 0x2000;
JOBOBJECT_EXTENDED_LIMIT_INFORMATION extendedInfo = new JOBOBJECT_EXTENDED_LIMIT_INFORMATION();
extendedInfo.BasicLimitInformation = info;
int length = Marshal.SizeOf(typeof(JOBOBJECT_EXTENDED_LIMIT_INFORMATION));
IntPtr extendedInfoPtr = Marshal.AllocHGlobal(length);
Marshal.StructureToPtr(extendedInfo, extendedInfoPtr, false);
if (!SetInformationJobObject(m_handle, JobObjectInfoType.ExtendedLimitInformation, extendedInfoPtr, (uint)length))
throw new Exception(string.Format("Unable to set information. Error: {0}", Marshal.GetLastWin32Error()));
}
#region IDisposable Members
public void Dispose()
{
Dispose(true);
GC.SuppressFinalize(this);
}
#endregion
private void Dispose(bool disposing)
{
if (m_disposed)
return;
if (disposing) {}
Close();
m_disposed = true;
}
public void Close()
{
Win32.CloseHandle(m_handle);
m_handle = IntPtr.Zero;
}
public bool AddProcess(IntPtr handle)
{
return AssignProcessToJobObject(m_handle, handle);
}
}
å ³äºæé å½æ°ä»£ç ç注æäºé¡¹
- å¨æé å½æ°ä¸ï¼
å«åãinfo.LimitFlags = 0x2000;
æ¯0x2000
æ举å¼ï¼è¯¥å¼ç±MSDNå®ä¹ä¸ºï¼JOB_OBJECT_LIMIT_KILL_ON_JOB_CLOSE
å½ä½ä¸çæåä¸ä¸ªå¥æå ³éæ¶ï¼å¯¼è´ä¸è¯¥ä½ä¸å ³èçææè¿ç¨ç»æ¢ã
é¢å¤çWin32 APIè°ç¨ä»¥è·åè¿ç¨IDï¼PIDï¼
[DllImport("user32.dll", SetLastError = true)]
public static extern uint GetWindowThreadProcessId(IntPtr hWnd, out uint lpdwProcessId);
使ç¨ä»£ç
Excel.Application app = new Excel.ApplicationClass();
Job job = new Job();
uint pid = 0;
Win32.GetWindowThreadProcessId(new IntPtr(app.Hwnd), out pid);
job.AddProcess(Process.GetProcessById((int)pid).Handle);
#4楼
å ³äºéæ¾COM对象çä¸ç¯å¾æ£çæç« æ¯2.5éæ¾COM对象 ï¼MSDNï¼ã
æ建议çæ¹æ³æ¯å°Excel.Interopå¼ç¨ï¼å¦æå®ä»¬æ¯éå±é¨åéï¼ä¸ºç©ºï¼ç¶å两次è°ç¨
GC.Collect()
å
GC.WaitForPendingFinalizers()
ã å±é¨èå´å çInteropåéå°è¢«èªå¨å¤çã
è¿æ ·å°±æ é为æ¯ä¸ª COM对象ä¿çä¸ä¸ªå½åå¼ç¨ã
è¿æ¯æ¬æä¸çä¸ä¸ªç¤ºä¾ï¼
public class Test {
// These instance variables must be nulled or Excel will not quit
private Excel.Application xl;
private Excel.Workbook book;
public void DoSomething()
{
xl = new Excel.Application();
xl.Visible = true;
book = xl.Workbooks.Add(Type.Missing);
// These variables are locally scoped, so we need not worry about them.
// Notice I don't care about using two dots.
Excel.Range rng = book.Worksheets[1].UsedRange;
}
public void CleanUp()
{
book = null;
xl.Quit();
xl = null;
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
GC.WaitForPendingFinalizers();
}
}
è¿äºè¯ç´æ¥æ¥èªæç« ï¼
å¨å ä¹æææ åµä¸ï¼å°RCWåè设置为é¶å¹¶å¼ºå¶è¿è¡åå¾åæ¶å°å¯ä»¥æ£ç¡®æ¸ é¤ã å¦ææ¨è¿è°ç¨GC.WaitForPendingFinalizersï¼ååå¾åæ¶å°å°½å¯è½å°å ·æç¡®å®æ§ã ä¹å°±æ¯è¯´ï¼æ¨å°ç¡®åå°ç¡®å®ä½æ¶æ¸ çäºå¯¹è±¡-ä»ç¬¬äºæ¬¡è°ç¨WaitForPendingFinalizersè¿åæ¶ã æè ï¼æ¨å¯ä»¥ä½¿ç¨Marshal.ReleaseComObjectã ä½æ¯ï¼è¯·æ³¨æï¼æ¨æä¸å¯è½éè¦ä½¿ç¨æ¤æ¹æ³ã
#5楼
ä¼ ç»ä¸ï¼æéµå¾ªVVSçæ¡ä¸ç建议ã ä½æ¯ï¼ä¸ºäºä½¿è¯¥çæ¡ä¸ææ°é项ä¿æææ°ï¼æ认为æ以åçææ项ç®é½å°ä½¿ç¨â NetOfficeâåºã
NetOfficeå®å ¨æ¿ä»£äºOffice PIAï¼å¹¶ä¸ä¸çæ¬å®å ¨æ å ³ã å®æ¯æ管COMå è£ ç¨åºçéåï¼å¯ä»¥å¤çå¨.NETä¸ä½¿ç¨Microsoft Officeæ¶ç»å¸¸å¼èµ·æ¤ç±»éº»ç¦çæ¸ çæä½ã
ä¸äºä¸»è¦åè½æ¯ï¼
- 主è¦ä¸çæ¬æ å ³ï¼å¹¶è®°å½äºä¸çæ¬æå ³çåè½ï¼
- 没æä¾èµå ³ç³»
- 没æPIA
- 没æ注å
- 没æVSTO
æç»ä¸é¶å±äºè¯¥é¡¹ç®ï¼ æççå¾æ谢头ççææ¾åè½»ã
#6楼
æ®éå¼å人åï¼æ¨ç解å³æ¹æ¡é½ä¸éåæï¼å æ¤æå³å®å®æ½ä¸ä¸ªæ°æå·§ ã
é¦å 让æ们æå®âæ们çç®æ æ¯ä»ä¹ï¼â =>âå¨ä»»å¡ç®¡çå¨ä¸å·¥ä½åçä¸å°excel对象â
好ã 让noææ并å¼å§ç ´åå®ï¼ä½èèä¸è¦ç ´å并è¡è¿è¡çå ¶ä»å®ä¾os Excelã
å æ¤ï¼è·åå½åå¤çå¨çå表并è·åEXCELè¿ç¨çPIDï¼ç¶åå¨å®ææ¨çå·¥ä½åï¼æ们å¨è¿ç¨å表ä¸æäºä¸ä¸ªå ·æå¯ä¸PIDçæ°æ¥å®¾ï¼ä» æ¥æ¾å¹¶éæ¯äºé£ä¸ªå®¢æ·æºã
<请记ä½ï¼å¨æ¨çexcelå·¥ä½æé´ï¼ä»»ä½æ°çexcelæµç¨é½å°è¢«æ£æµä¸ºæ°ç并被éæ¯> <æ´å¥½ç解å³æ¹æ¡æ¯æè·æ°å建çexcel对象çPID并éæ¯å®>
Process[] prs = Process.GetProcesses();
List<int> excelPID = new List<int>();
foreach (Process p in prs)
if (p.ProcessName == "EXCEL")
excelPID.Add(p.Id);
.... // your job
prs = Process.GetProcesses();
foreach (Process p in prs)
if (p.ProcessName == "EXCEL" && !excelPID.Contains(p.Id))
p.Kill();
è¿è§£å³äºæçé®é¢ï¼ä¹å¸ææ¨çã
#7楼
è¿éç¨äºææ£å¨ä»äºç项ç®ï¼
excelApp.Quit();
Marshal.ReleaseComObject (excelWB);
Marshal.ReleaseComObject (excelApp);
excelApp = null;
æ们äºè§£å°ï¼å®æ对Excel COM对象çææå¼ç¨åï¼å°å ¶è®¾ç½®ä¸ºnullè³å ³éè¦ã å ¶ä¸å æ¬åå æ ¼ï¼è¡¨æ ¼åå ¶ä»ææå 容ã
#8楼
Excelä¸ä¼éåºï¼å 为æ¨çåºç¨ç¨åºä»å¨ä¿ç对COM对象çå¼ç¨ã
æçæ¨æ£å¨è°ç¨COM对象çè³å°ä¸ä¸ªæåï¼è没æå°å ¶åé ç»åéã
对ææ¥è¯´ï¼è¿æ¯excelApp.Worksheets对象ï¼æç´æ¥ä½¿ç¨å®èä¸å°å ¶åé ç»åéï¼
Worksheet sheet = excelApp.Worksheets.Open(...);
...
Marshal.ReleaseComObject(sheet);
æä¸ç¥éCï¼å¨å é¨ä¸ºWorksheets COM对象å建äºä¸ä¸ªå è£ ï¼è该å è£ æ²¡æ被æç代ç éæ¾ï¼å 为æå½æ¶ä¸ç¥éï¼ï¼è¿å°±æ¯Excelæ æ³å¸è½½çåå ã
æå¨æ¤é¡µé¢ä¸æ¾å°äºè§£å³é®é¢çæ¹æ³ï¼ 该æ¹æ³å¯¹äºå¨Cï¼ä¸ä½¿ç¨COM对象ä¹æä¸ä¸ªå¾å¥½çè§åï¼
åå¿å¯¹COM对象使ç¨ä¸¤ä¸ªç¹ã
å æ¤ï¼åºäºæ¤ç¥è¯ï¼æ£ç¡®çæ¹æ³æ¯ï¼
Worksheets sheets = excelApp.Worksheets; // <-- The important part
Worksheet sheet = sheets.Open(...);
...
Marshal.ReleaseComObject(sheets);
Marshal.ReleaseComObject(sheet);
äºåæ´æ°ï¼
æå¸ææ¯ä¸ªè¯»è é½ä»ç»é 读Hans Passantççæ¡ï¼å 为å®è§£éäºæå许å¤å ¶ä»å¼å人åæé·å ¥çé·é±ã å å¹´åï¼å½æåè¿ä¸ªçæ¡æ¶ï¼æä¸ç¥éè°è¯å¨å¯¹åå¾æ¶éå¨çå½±å并å¾åºé误çç»è®ºã 为äºåå²èµ·è§ï¼æççæ¡ä¿æä¸åï¼ä½æ¯è¯·é 读æ¤é¾æ¥ï¼ ä¸è¦éç¨â两个ç¹âçæ¹å¼ï¼ äºè§£.NETä¸çåå¾æ¶é并使ç¨IDisposableæ¸ çExcel Interop对象
#9楼
Excelå称空é´ä¸çææå 容é½éè¦éæ¾ã æ
æ¨ä¸è½è¿æ ·åï¼
Worksheet ws = excel.WorkBooks[1].WorkSheets[1];
ä½ å¿ é¡»è¦å
Workbooks books = excel.WorkBooks;
Workbook book = books[1];
Sheets sheets = book.WorkSheets;
Worksheet ws = sheets[1];
ç¶åéæ¾å¯¹è±¡ã
#10楼
æè®¤ä¸ºå ¶ä¸æäºåªæ¯æ¡æ¶å¤çOfficeåºç¨ç¨åºçæ¹å¼ï¼ä½æå¯è½æ¯éçã ææ¶ï¼æäºåºç¨ç¨åºä¼ç«å³æ¸ çè¿ç¨ï¼èå¦ä¸äºæ¥åä¼¼ä¹è¦çå°åºç¨ç¨åºå ³éã é常ï¼æéåºæ¶ä¼æ³¨æç»èï¼åªæ¯è¦ç¡®ä¿ä¸å¤©ç»ææ¶æ²¡æå ¶ä»å¤ä½çæµç¨ã
å¦å¤ï¼ä¹è®¸ææ£å¨ç®åäºæ ï¼ä½æ¯æ认为æ¨å¯ä»¥...
objExcel = new Excel.Application();
objBook = (Excel.Workbook)(objExcel.Workbooks.Add(Type.Missing));
DoSomeStuff(objBook);
SaveTheBook(objBook);
objBook.Close(false, Type.Missing, Type.Missing);
objExcel.Quit();
å°±åæä¹å说çï¼æä¸å¾åäºå ³æ³¨Excelè¿ç¨ä½æ¶åºç°ææ¶å¤±çç»èï¼ä½è¿é常对ææç¨ã é¤äºæççæ¶é´ä¹å¤ï¼æä¹ä¸æ³ä¿çExcelè¿ç¨ï¼ä½æå¯è½å¯¹æ¤æå°åæ§ã
#11楼
æ£å¦å ¶ä»äººæåºçé£æ ·ï¼æ¨éè¦ä¸ºä½¿ç¨çæ¯ä¸ªExcel对象å建ä¸ä¸ªæ¾å¼å¼ç¨ï¼å¹¶å¯¹è¯¥å¼ç¨è°ç¨Marshal.ReleaseComObjectï¼å¦æ¬ç¥è¯åºæç« ä¸æè¿° ã æ¨è¿éè¦ä½¿ç¨try / finallyæ¥ç¡®ä¿å§ç»è°ç¨ReleaseComObjectï¼å³ä½¿æåºå¼å¸¸ä¹æ¯å¦æ¤ã å³ä»£æ¿ï¼
Worksheet sheet = excelApp.Worksheets(1)
... do something with sheet
æ¨éè¦æ§è¡ä»¥ä¸æä½ï¼
Worksheets sheets = null;
Worksheet sheet = null
try
{
sheets = excelApp.Worksheets;
sheet = sheets(1);
...
}
finally
{
if (sheets != null) Marshal.ReleaseComObject(sheets);
if (sheet != null) Marshal.ReleaseComObject(sheet);
}
å¦æè¦å ³éExcelï¼è¿éè¦å¨éæ¾Application对象ä¹åè°ç¨Application.Quitã
å¦æ¨æè§ï¼ä¸æ¦æ¨å°è¯åä»»ä½ä¸çå¤æçäºæ ï¼è¿å¾å¿«å°±ä¼åå¾é常笨æã æå·²ç»ä½¿ç¨ç®åçå è£ å¨ç±»æåå¼åäº.NETåºç¨ç¨åºï¼è¯¥å è£ å¨ç±»å è£ äºExcel对象模åçä¸äºç®åæä½ï¼æå¼å·¥ä½ç°¿ï¼åå ¥èå´ï¼ä¿å/å ³éå·¥ä½ç°¿çï¼ã å è£ å¨ç±»å®ç°IDisposableï¼å¨å ¶ä½¿ç¨çæ¯ä¸ªå¯¹è±¡ä¸ä»ç»å®ç°Marshal.ReleaseComObjectï¼å¹¶ä¸ä¸ä¼ååºç¨ç¨åºçå ¶ä½é¨åå ¬å¼å ¬å¼ä»»ä½Excel对象ã
ä½æ¯ï¼è¿ç§æ¹æ³ä¸è½å¾å¥½å°æ»¡è¶³æ´å¤æçéæ±ã
è¿æ¯.NET COM Interopçä¸å¤§ç¼ºé·ã 对äºæ´å¤æçæ¹æ¡ï¼æå°è®¤çèè使ç¨VB6æå ¶ä»ä¸å管ççè¯è¨ç¼åActiveX DLLï¼æ¨å¯ä»¥å°ä¸Officeè¿ç±»åºç«COM对象çææ交äºå§æ´¾ç»è¯¥ActiveX DLLã ç¶åï¼æ¨å¯ä»¥ä».NETåºç¨ç¨åºä¸å¼ç¨æ¤ActiveX DLLï¼è¿å°ä½¿äºæ åå¾å®¹æå¾å¤ï¼å 为æ¨åªéè¦éæ¾è¯¥å¼ç¨å³å¯ã
#12楼
å®é ä¸ï¼æ¨å¯ä»¥å¹²åå°éæ¾Excel Application对象ï¼ä½æ¯å¿ é¡»å°å¿ã
对äºç»å¯¹è®¿é®çæ¯ä¸ªCOM对象é½ç»´æ¤ä¸ä¸ªå½åå¼ç¨ï¼ç¶åéè¿
Marshal.FinalReleaseComObject()
æ¾å¼éæ¾å®ç建议å¨ç论ä¸æ¯æ£ç¡®çï¼ä½æ¯ä¸å¹¸çæ¯ï¼å¨å®è·µä¸å¾é¾ç®¡çã å¦ææ人å¨ä»»ä½å°æ¹æ»å¨å¹¶ä½¿ç¨â两个ç¹âï¼æè éè¿
for each
循ç¯æä»»ä½å ¶ä»ç±»ä¼¼ç±»åçå½ä»¤
for each
åå è¿è¡è¿ä»£ï¼é£ä¹æ¨å°æ¥ææªå¼ç¨çCOM对象ï¼å¹¶å¯è½ä¼æ»æºã å¨è¿ç§æ åµä¸ï¼å°æ æ³å¨ä»£ç ä¸æ¾å°åå ã æ¨å°ä¸å¾ä¸ä»ç»æ£æ¥ææ代ç 并å¸ææ¾å°åå ï¼è¿å¯¹äºå¤§å项ç®èè¨å ä¹æ¯ä¸å¯è½çã
好æ¶æ¯æ¯ï¼æ¨å®é ä¸ä¸å¿ ç»´æ¤å¯¹æ使ç¨çæ¯ä¸ªCOM对象çå½ååéå¼ç¨ã èæ¯ï¼è°ç¨
GC.Collect()
ï¼ç¶å
GC.WaitForPendingFinalizers()
以éæ¾æææ¨ä¸ææå¼ç¨çï¼é常æ¯æ¬¡è¦çï¼å¯¹è±¡ï¼ç¶åæ¾å¼éæ¾æ¨è¦ææå½ååéå¼ç¨ç对象ã
æ¨è¿åºè¯¥ä»¥ç¸åç顺åºéæ¾å½åçå¼ç¨ï¼é¦å æ¯èå´å¯¹è±¡ï¼ç¶åæ¯å·¥ä½è¡¨ï¼å·¥ä½ç°¿ï¼æåæ¯Excel Application对象ã
ä¾å¦ï¼åè®¾ä½ æä¸ä¸ªå为Range对象åé
xlRng
ï¼å½åå·¥ä½è¡¨åé
xlSheet
ï¼å½åå·¥ä½ç°¿åé
xlBook
并å½å为Excelåºç¨ç¨åºåé
xlApp
ï¼é£ä¹ä½ çæ¸ ç代ç å¯è½çèµ·æ¥åä¸é¢è¿æ ·ï¼
// Cleanup
GC.Collect();
GC.WaitForPendingFinalizers();
Marshal.FinalReleaseComObject(xlRng);
Marshal.FinalReleaseComObject(xlSheet);
xlBook.Close(Type.Missing, Type.Missing, Type.Missing);
Marshal.FinalReleaseComObject(xlBook);
xlApp.Quit();
Marshal.FinalReleaseComObject(xlApp);
å¨å¤§å¤æ°ä»£ç 示ä¾ä¸ï¼æ¨ä¼çå°ä».NETæ¸ çCOM对象çæ¹å¼ï¼
GC.WaitForPendingFinalizers()
è°ç¨
GC.Collect()
å
GC.WaitForPendingFinalizers()
æ¹å¼å¦ä¸ï¼
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
GC.WaitForPendingFinalizers();
ä½æ¯ï¼é¤éæ¨ä½¿ç¨çæ¯Visual Studio Tools for Officeï¼VSTOï¼ï¼å¦åå®ä¸æ¯å¿ éçï¼å 为Visual Studio Tools for Officeï¼VSTOï¼ä½¿ç¨çç»ç»å¨ä¼ä½¿æ´ä¸ªå¯¹è±¡å¾å¨ç»ç»éåä¸å¾å°æåã å¨ä¸ä¸æ¬¡åå¾åæ¶ä¹åï¼ä¸ä¼éæ¾æ¤ç±»å¯¹è±¡ã ä½æ¯ï¼å¦ææ¨ä¸ä½¿ç¨VSTOï¼ååºè¯¥åªè½è°ç¨ä¸æ¬¡
GC.Collect()
å
GC.WaitForPendingFinalizers()
ã
æç¥éæ¾å¼è°ç¨
GC.Collect()
æ¯ä¸è¡çï¼å½ç¶ï¼ä¸¤æ¬¡è°ç¨å¬èµ·æ¥å¾çè¦ï¼ï¼ä½æ¯è¯´å®è¯ï¼è¿æ¯æ²¡æåæ³çã éè¿æ£å¸¸æä½ï¼æ¨å°çæéèç对象ï¼è¿äºå¯¹è±¡æ²¡æå¼ç¨ï¼å æ¤ï¼é¤äºè°ç¨
GC.Collect()
ä¹å¤ï¼æ æ³éè¿å ¶ä»ä»»ä½æ¹å¼éæ¾å¼ç¨ã
è¿æ¯ä¸ä¸ªå¤æç主é¢ï¼ä½å®é ä¸ä» æ¤èå·²ã ä¸æ¦ä¸ºæ¸ çç¨åºå»ºç«äºæ¤æ¨¡æ¿ï¼å°±å¯ä»¥æ£å¸¸ç¼å代ç ï¼èæ éå è£ çã:-)
æå¨è¿éæä¸ä¸ªæç¨ï¼
使ç¨VB.Net/COM Interopèªå¨åOfficeç¨åº
å®æ¯ä¸ºVB.NETç¼åçï¼ä½ä¸è¦å æ¤èæ¨è¿ï¼å ¶åçä¸ä½¿ç¨Cï¼æ¶å®å ¨ç¸åã
#13楼
æ£å¦ä¸äºäººå¯è½å·²ç»åè¿çé£æ ·ï¼ å ³é Excelï¼å¯¹è±¡ï¼ä¸ä» éè¦ã æå¼æ¹å¼ä»¥å项ç®ç±»åä¹å¾éè¦ã
å¨WPFåºç¨ç¨åºä¸ï¼åºæ¬ä¸ç¸åç代ç å¯ä»¥æ£å¸¸å·¥ä½ï¼èä¸ä¼åºç°æå¾å°æé®é¢ã
ææä¸ä¸ªé¡¹ç®ï¼å ¶ä¸ç¸åçExcelæ件é对ä¸åçåæ°å¼è¿è¡äºå¤æ¬¡å¤ç-ä¾å¦ï¼æ ¹æ®éç¨å表ä¸çå¼è¿è¡è§£æã
æå°ææä¸Excelç¸å ³çå½æ°æ¾å ¥åºç±»ï¼å¹¶å°è§£æå¨æ¾å ¥åç±»ï¼ä¸åç解æå¨ä½¿ç¨å¸¸è§çExcelå½æ°ï¼ã æä¸å¸æå次为éç¨å表ä¸çæ¯ä¸ªé¡¹ç®æå¼åå ³éExcelï¼å æ¤æåªå¨åºç±»ä¸å°å ¶æå¼ä¸æ¬¡ï¼èå¨åç±»ä¸å°å ¶å ³éã å°ä»£ç 移è³æ¡é¢åºç¨ç¨åºæ¶éå°é®é¢ã æå·²ç»å°è¯äºè®¸å¤ä¸è¿°è§£å³æ¹æ¡ã
GC.Collect()
ä¹åå·²å®ç°ï¼æ¯å»ºè®®ç两åã
ç¶åï¼æå³å®å°å°æå¼Excelç代ç 移è³åç±»ã ç°å¨ï¼æå建ä¸ä¸ªæ°å¯¹è±¡ï¼åºç±»ï¼ï¼èä¸æ¯åªæå¼ä¸æ¬¡ï¼ç¶å为æ¯ä¸ªé¡¹ç®æå¼Excel并å¨æåå°å ¶å ³éã æ§è½ä¼ææä¸éï¼ä½æ¯åºäºå¤ä¸ªæµè¯ï¼Excelè¿ç¨æ£å¨å ³éï¼æ²¡æé®é¢ï¼å¨è°è¯æ¨¡å¼ä¸ï¼ï¼å æ¤ä¸´æ¶æ件ä¹è¢«å é¤ã æå°ç»§ç»æµè¯ï¼å¹¶å¨è·å¾ä¸äºæ´æ°çæ åµä¸ç¼åæ´å¤å 容ã
æéè¦çæ¯ï¼æ¨è¿å¿ é¡»æ£æ¥åå§å代ç ï¼å°¤å ¶æ¯å¦ææ¨æå¾å¤ç±»ï¼ççã
#14楼
¸å°å»Excel proc并åå¼æ³¡æ³¡ç³Â¸âø¤º°¨
public class MyExcelInteropClass
{
Excel.Application xlApp;
Excel.Workbook xlBook;
public void dothingswithExcel()
{
try { /* Do stuff manipulating cells sheets and workbooks ... */ }
catch {}
finally {KillExcelProcess(xlApp);}
}
static void KillExcelProcess(Excel.Application xlApp)
{
if (xlApp != null)
{
int excelProcessId = 0;
GetWindowThreadProcessId(xlApp.Hwnd, out excelProcessId);
Process p = Process.GetProcessById(excelProcessId);
p.Kill();
xlApp = null;
}
}
[DllImport("user32.dll")]
static extern int GetWindowThreadProcessId(int hWnd, out int lpdwProcessId);
}
#15楼
åè¨ï¼æççæ¡å å«ä¸¤ç§è§£å³æ¹æ¡ï¼å æ¤é 读æ¶è¯·å¡å¿ å°å¿ï¼ä¸è¦éè¿ä»»ä½å 容ã
对äºå¦ä½å¸è½½Excelå®ä¾ï¼æä¸åçæ¹æ³å建议ï¼ä¾å¦ï¼
-
使ç¨Marshal.FinalReleaseComObjectï¼ï¼æ¾å¼éæ¾æ¯ä¸ªcom对象ï¼ä¸è¦å¿è®°éå¼å建çcom对象ï¼ã è¦éæ¾æ¯ä¸ªå建çcom对象ï¼å¯ä»¥ä½¿ç¨æ¤å¤æå°ç2个ç¹çè§åï¼
å¦ä½æ£ç¡®æ¸ çExceläºæä½å¯¹è±¡ï¼
- è°ç¨GC.Collectï¼ï¼åGC.WaitForPendingFinalizersï¼ï¼å¯ä½¿CLRéæ¾æªä½¿ç¨çCOM对象*ï¼å®é ä¸ï¼å®å¯ä»¥å·¥ä½ï¼æå ³è¯¦ç»ä¿¡æ¯ï¼è¯·åè§æç第äºä¸ªè§£å³æ¹æ¡ï¼
- æ£æ¥com-server-applicationæ¯å¦æ¾ç¤ºä¸ä¸ªçå¾ ç¨æ·åççæ¶æ¯æ¡ï¼å°½ç®¡æä¸ç¡®å®å®æ¯å¦å¯ä»¥é»æ¢Excelå ³éï¼ä½æå¬è¯´è¿å 次ï¼
- å°WM_CLOSEæ¶æ¯åéå°Excel主çªå£
- å¨åç¬çAppDomainä¸æ§è¡ä¸Excelä¸èµ·ä½¿ç¨çåè½ã æ人认为ï¼å¸è½½AppDomainåï¼Excelå®ä¾å°å ³éã
- ææ»ææå¨æ们çexceläºæä½ä»£ç å¯å¨åå®ä¾åçexcelå®ä¾ã
ä½ï¼ ææ¶ï¼ææè¿äºé项é½æ æµäºäºæä¸åéï¼
ä¾å¦ï¼æ¨å¤©æåç°å¨æçå ¶ä¸ä¸ä¸ªå½æ°ï¼ä¸excelä¸èµ·ä½¿ç¨ï¼ä¸ï¼Excelå¨å½æ°ç»æåä¸ç´è¿è¡ã æå°è¯äºä¸åï¼ æå½»åºæ£æ¥äºæ´ä¸ªåè½10次ï¼å¹¶ä¸ºææå 容添å äºMarshal.FinalReleaseComObjectï¼ï¼ï¼ æä¹æGC.Collectï¼ï¼åGC.WaitForPendingFinalizersï¼ï¼ã ææ£æ¥äºéèçæ¶æ¯æ¡ã æè¯å¾å°WM_CLOSEæ¶æ¯åéå°Excel主çªå£ã æå¨åç¬çAppDomainä¸æ§è¡äºæçåè½ï¼ç¶åå¸è½½äºè¯¥åã 没æä»»ä½å¸®å©ï¼ å ³éææexcelå®ä¾çé项æ¯ä¸åéçï¼å 为å¦æç¨æ·å¨æçå½æ°æ§è¡æé´æå¨å¯å¨å¦ä¸ä¸ªExcelå®ä¾ï¼ä¹å¯ä»¥ä½¿ç¨Excelï¼ï¼å该å®ä¾ä¹å°è¢«æçå½æ°å ³éã ææ¢æèµç¨æ·ä¸ä¼æ»¡æï¼ å æ¤ï¼è¯´å®è¯ï¼è¿æ¯ä¸ä¸ªlaèçéæ©ï¼æ²¡æåç¯ç人ï¼ã å æ¤ï¼æè±äºå 个å°æ¶ææ¾å°ä¸ä¸ªå¥½ç解å³æ¹æ¡ ï¼ä»¥æçæè§ï¼ï¼ éè¿å ¶ä¸»çªå£çhWndææ»excelè¿ç¨ ï¼è¿æ¯ç¬¬ä¸ä¸ªè§£å³æ¹æ¡ï¼ã
è¿æ¯ç®åç代ç ï¼
[DllImport("user32.dll")]
private static extern uint GetWindowThreadProcessId(IntPtr hWnd, out uint lpdwProcessId);
/// <summary> Tries to find and kill process by hWnd to the main window of the process.</summary>
/// <param name="hWnd">Handle to the main window of the process.</param>
/// <returns>True if process was found and killed. False if process was not found by hWnd or if it could not be killed.</returns>
public static bool TryKillProcessByMainWindowHwnd(int hWnd)
{
uint processID;
GetWindowThreadProcessId((IntPtr)hWnd, out processID);
if(processID == 0) return false;
try
{
Process.GetProcessById((int)processID).Kill();
}
catch (ArgumentException)
{
return false;
}
catch (Win32Exception)
{
return false;
}
catch (NotSupportedException)
{
return false;
}
catch (InvalidOperationException)
{
return false;
}
return true;
}
/// <summary> Finds and kills process by hWnd to the main window of the process.</summary>
/// <param name="hWnd">Handle to the main window of the process.</param>
/// <exception cref="ArgumentException">
/// Thrown when process is not found by the hWnd parameter (the process is not running).
/// The identifier of the process might be expired.
/// </exception>
/// <exception cref="Win32Exception">See Process.Kill() exceptions documentation.</exception>
/// <exception cref="NotSupportedException">See Process.Kill() exceptions documentation.</exception>
/// <exception cref="InvalidOperationException">See Process.Kill() exceptions documentation.</exception>
public static void KillProcessByMainWindowHwnd(int hWnd)
{
uint processID;
GetWindowThreadProcessId((IntPtr)hWnd, out processID);
if (processID == 0)
throw new ArgumentException("Process has not been found by the given main window handle.", "hWnd");
Process.GetProcessById((int)processID).Kill();
}
å¦æ¨æè§ï¼æ ¹æ®Try-Parse模å¼ï¼ææä¾äºä¸¤ç§æ¹æ³ï¼æ认为è¿å¨è¿éæ¯éå½çï¼ï¼å¦ææ æ³ææ»Processï¼ä¾å¦ï¼è¯¥è¿ç¨ä¸ååå¨ï¼ï¼åä¸ç§æ¹æ³ä¸ä¼å¼åå¼å¸¸ã ï¼å¦æProcess没æ被ææ»ï¼åå¦ä¸ä¸ªæ¹æ³å°å¼åå¼å¸¸ã æ¤ä»£ç ä¸å¯ä¸ç弱项æ¯å®å ¨æéã ä»ç论ä¸è®²ï¼ç¨æ·å¯è½æ²¡æææ»è¿ç¨çæéï¼ä½æ¯å¨æææ åµä¸ï¼æ99.99ï¼ çç¨æ·é½å ·æè¿ç§æéã æä¹ç¨æ¥å®¾å¸æ·å¯¹å ¶è¿è¡äºæµè¯-ææå¾å¥½ã
å æ¤ï¼ä½¿ç¨Excelç代ç å¦ä¸æ示ï¼
int hWnd = xl.Application.Hwnd;
// ...
// here we try to close Excel as usual, with xl.Quit(),
// Marshal.FinalReleaseComObject(xl) and so on
// ...
TryKillProcessByMainWindowHwnd(hWnd);
ç§ï¼ Excelå·²ç»æ¢ï¼ :)
好çï¼è®©æ们åå°ç¬¬äºä¸ªè§£å³æ¹æ¡ï¼æ£å¦æå¨æç« å¼å¤´ææ¿è¯ºçé£æ ·ã 第äºç§è§£å³æ¹æ¡æ¯è°ç¨GC.Collectï¼ï¼åGC.WaitForPendingFinalizersï¼ï¼ã æ¯çï¼å®ä»¬ç¡®å®ææï¼ä½æ¯æ¨å¨è¿ééè¦å°å¿ï¼
许å¤äººè¯´ï¼æ说è¿ï¼ï¼è°ç¨GC.Collectï¼ï¼å¹¶æ²¡æ帮å©ã ä½è¿æ æµäºäºçåå æ¯ï¼å¦æä»ç¶æ对COM对象çå¼ç¨ï¼ GC.Collectï¼ï¼æ æçæ常è§åå ä¹ä¸æ¯å¨Debug模å¼ä¸è¿è¡é¡¹ç®ã å¨è°è¯æ¨¡å¼ä¸ï¼ä¸åçæ£å¼ç¨ç对象å°å¨æ¹æ³ç»æä¹åä¸ä¼è¢«åå¾åæ¶ã
å æ¤ï¼å¦ææ¨å°è¯ä½¿ç¨GC.Collectï¼ï¼åGC.WaitForPendingFinalizersï¼ï¼å¹¶æ²¡æ帮å©ï¼è¯·å°è¯æ§è¡ä»¥ä¸æä½ï¼
1ï¼å°è¯å¨åå¸æ¨¡å¼ä¸è¿è¡é¡¹ç®ï¼å¹¶æ£æ¥Excelæ¯å¦æ£ç¡®å ³é
2ï¼å°ä½¿ç¨Excelçæ¹æ³å è£ å¨åç¬çæ¹æ³ä¸ã å æ¤ï¼èä¸æ¯åè¿æ ·ï¼
void GenerateWorkbook(...)
{
ApplicationClass xl;
Workbook xlWB;
try
{
xl = ...
xlWB = xl.Workbooks.Add(...);
...
}
finally
{
...
Marshal.ReleaseComObject(xlWB)
...
GC.Collect();
GC.WaitForPendingFinalizers();
}
}
ä½ åï¼
void GenerateWorkbook(...)
{
try
{
GenerateWorkbookInternal(...);
}
finally
{
GC.Collect();
GC.WaitForPendingFinalizers();
}
}
private void GenerateWorkbookInternal(...)
{
ApplicationClass xl;
Workbook xlWB;
try
{
xl = ...
xlWB = xl.Workbooks.Add(...);
...
}
finally
{
...
Marshal.ReleaseComObject(xlWB)
...
}
}
ç°å¨ï¼Excelå°å ³é=ï¼
#16楼
æ¤å¤æ¥åççæ¡æ¯æ£ç¡®çï¼ä½ä¹è¯·æ³¨æï¼ä¸ä» éè¦é¿å â两个ç¹âçå¼ç¨ï¼èä¸è¿éè¦é¿å éè¿ç´¢å¼æ£ç´¢ç对象ã æ¨ä¹ä¸å¿ çå°ç¨åºå®æåå°±æ¸ çè¿äºå¯¹è±¡ï¼æ好å建å¯ä»¥å¨å®æåå°½å¿«æ¸ çå®ä»¬çå½æ°ã è¿æ¯æå建çä¸ä¸ªå½æ°ï¼ç¨äºåé å为
xlStyleHeader
çStyle对象çä¸äºå±æ§ï¼
public Excel.Style xlStyleHeader = null;
private void CreateHeaderStyle()
{
Excel.Styles xlStyles = null;
Excel.Font xlFont = null;
Excel.Interior xlInterior = null;
Excel.Borders xlBorders = null;
Excel.Border xlBorderBottom = null;
try
{
xlStyles = xlWorkbook.Styles;
xlStyleHeader = xlStyles.Add("Header", Type.Missing);
// Text Format
xlStyleHeader.NumberFormat = "@";
// Bold
xlFont = xlStyleHeader.Font;
xlFont.Bold = true;
// Light Gray Cell Color
xlInterior = xlStyleHeader.Interior;
xlInterior.Color = 12632256;
// Medium Bottom border
xlBorders = xlStyleHeader.Borders;
xlBorderBottom = xlBorders[Excel.XlBordersIndex.xlEdgeBottom];
xlBorderBottom.Weight = Excel.XlBorderWeight.xlMedium;
}
catch (Exception ex)
{
throw ex;
}
finally
{
Release(xlBorderBottom);
Release(xlBorders);
Release(xlInterior);
Release(xlFont);
Release(xlStyles);
}
}
private void Release(object obj)
{
// Errors are ignored per Microsoft's suggestion for this type of function:
// http://support.microsoft.com/default.aspx/kb/317109
try
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
}
catch { }
}
请注æï¼æå¿ é¡»å°
xlBorders[Excel.XlBordersIndex.xlEdgeBottom]
设置为åéï¼ä»¥æ¸ ç该åéï¼ä¸æ¯å 为两个ç¹æå¼ç¨çæ举ä¸éè¦éæ¾ï¼èæ¯å 为对象I' mææçå®é ä¸æ¯ç¡®å®éè¦éæ¾çBorder对象ï¼ã
è¿ç§äºæ å¨æ ååºç¨ç¨åºä¸å¹¶ä¸æ¯çæ£å¿ è¦çï¼å®ä»¬å¯ä»¥å¾å¥½å°æ¸ çèªå·±ï¼ä½æ¯å¨ASP.NETåºç¨ç¨åºä¸ï¼å³ä½¿æ¨éè¿äºå ¶ä¸ä¹ä¸ï¼æ 论æ¨å¤ä¹é¢ç¹å°è°ç¨åå¾æ¶éå¨ï¼Excelé½ä¼ä»å¨æ¨çæå¡å¨ä¸è¿è¡ã
å¨ç¼åæ¤ä»£ç æ¶ï¼å¨çè§âä»»å¡ç®¡çå¨âæ¶ï¼å®éè¦å¤§éå ³æ³¨ç»èå许å¤æµè¯æ§è¡ï¼ä½æ¯è¿æ ·åçå´äºæ¨æ¼å½å°æ索代ç 页é¢ä»¥æ¥æ¾ä¸¢å¤±çä¸ä¸ªå®ä¾ç麻ç¦ã è¿å¨å¾ªç¯ä¸å·¥ä½æ¶ç¹å«éè¦ï¼å¨å¾ªç¯ä¸æ¨éè¦éæ¾å¯¹è±¡çEACH INSTANCEï¼å³ä½¿å¯¹è±¡æ¯æ¬¡å¾ªç¯é½ä½¿ç¨ç¸åçåéåã
#17楼
ç»è¿å°è¯
- 以ç¸åç顺åºéæ¾COM对象
- æå两次添å
åGC.Collect()
GC.WaitForPendingFinalizers()
- ä¸è¶ è¿ä¸¤ä¸ªç¹
- å ³éå·¥ä½ç°¿å¹¶éåºåºç¨ç¨åº
- å¨åå¸æ¨¡å¼ä¸è¿è¡
对ææç¨çæç»è§£å³æ¹æ¡æ¯ç§»å¨ä¸ç»
GC.Collect();
GC.WaitForPendingFinalizers();
æ们å¨å½æ°çæ«å°¾æ·»å äºä¸ä¸ªå è£ å¨ï¼å¦ä¸æ示ï¼
private void FunctionWrapper(string sourcePath, string targetPath)
{
try
{
FunctionThatCallsExcel(sourcePath, targetPath);
}
finally
{
GC.Collect();
GC.WaitForPendingFinalizers();
}
}
#18楼
两ç¹è§å对æä¸èµ·ä½ç¨ã å°±æèè¨ï¼æå建äºä¸ç§æ¸ çèµæºçæ¹æ³ï¼å¦ä¸æ示ï¼
private static void Clean()
{
workBook.Close();
Marshall.ReleaseComObject(workBook);
excel.Quit();
CG.Collect();
CG.WaitForPendingFinalizers();
}
#19楼
æå®å ¨æç §è¿ä¸ªæ¥éª¤è¿è¡...ä½æ¯æä»ç¶éå°1000次é®é¢ä¸ç1次ã è°ç¥é为ä»ä¹ã æ¯æ¶åæ¿åºé¤åäº...
å¨å®ä¾åExcel Applicationç±»ä¹åï¼æç«å³ææ¡äºååå建çExcelè¿ç¨ã
excel = new Microsoft.Office.Interop.Excel.Application();
var process = Process.GetProcessesByName("EXCEL").OrderByDescending(p => p.StartTime).First();
ç¶åï¼å®æä¸è¿°ææCOMæ¸ çåï¼è¯·ç¡®ä¿è¯¥è¿ç¨æªè¿è¡ã å¦æå®ä»å¨è¿è¡ï¼è¯·ææ»å®ï¼
if (!process.HasExited)
process.Kill();
#20楼
æç解å³æ¹æ¡
[DllImport("user32.dll")]
static extern int GetWindowThreadProcessId(int hWnd, out int lpdwProcessId);
private void GenerateExcel()
{
var excel = new Microsoft.Office.Interop.Excel.Application();
int id;
// Find the Excel Process Id (ath the end, you kill him
GetWindowThreadProcessId(excel.Hwnd, out id);
Process excelProcess = Process.GetProcessById(id);
try
{
// Your code
}
finally
{
excel.Quit();
// Kill him !
excelProcess.Kill();
}
#21楼
æ¥åççæ¡å¯¹æä¸èµ·ä½ç¨ã ææå½æ°ä¸ç以ä¸ä»£ç å®æäºè¿é¡¹å·¥ä½ã
if (xlApp != null)
{
xlApp.Workbooks.Close();
xlApp.Quit();
}
System.Diagnostics.Process[] processArray = System.Diagnostics.Process.GetProcessesByName("EXCEL");
foreach (System.Diagnostics.Process process in processArray)
{
if (process.MainWindowTitle.Length == 0) { process.Kill(); }
}
#22楼
æ¨éè¦æ³¨æï¼Excelä¹å¯¹æ¨æè¿è¡çæåé常ææã
æ¨å¯è½ä¼åç°éè¦å¨è°ç¨Excelå½æ°ä¹åå°åºåæ§è®¾ç½®ä¸ºEN-USã è¿ä¸éç¨äºææåè½-ä½å ¶ä¸ä¸äºåè½ã
CultureInfo en_US = new System.Globalization.CultureInfo("en-US");
System.Threading.Thread.CurrentThread.CurrentCulture = en_US;
string filePathLocal = _applicationObject.ActiveWorkbook.Path;
System.Threading.Thread.CurrentThread.CurrentCulture = orgCulture;
å³ä½¿æ¨ä½¿ç¨çæ¯VSTOï¼è¿ä¹éç¨ã
æå ³è¯¦ç»ä¿¡æ¯ï¼ http : //support.microsoft.com/default.aspx?scid=kb;zh-CN;Q320369
#23楼
ææ¾å°äºä¸ä¸ªæç¨çéç¨æ¨¡æ¿ï¼è¯¥æ¨¡æ¿å¯ä»¥å¸®å©å®ç°COM对象çæ£ç¡®å¤ç模å¼ï¼è¿äºCOM对象å¨è¶ åºèå´æ¶éè¦Marshal.ReleaseComObjectè¿è¡è°ç¨ï¼
ç¨æ³ï¼
using (AutoReleaseComObject<Application> excelApplicationWrapper = new AutoReleaseComObject<Application>(new Application()))
{
try
{
using (AutoReleaseComObject<Workbook> workbookWrapper = new AutoReleaseComObject<Workbook>(excelApplicationWrapper.ComObject.Workbooks.Open(namedRangeBase.FullName, false, false, missing, missing, missing, true, missing, missing, true, missing, missing, missing, missing, missing)))
{
// do something with your workbook....
}
}
finally
{
excelApplicationWrapper.ComObject.Quit();
}
}
模æ¿ï¼
public class AutoReleaseComObject<T> : IDisposable
{
private T m_comObject;
private bool m_armed = true;
private bool m_disposed = false;
public AutoReleaseComObject(T comObject)
{
Debug.Assert(comObject != null);
m_comObject = comObject;
}
#if DEBUG
~AutoReleaseComObject()
{
// We should have been disposed using Dispose().
Debug.WriteLine("Finalize being called, should have been disposed");
if (this.ComObject != null)
{
Debug.WriteLine(string.Format("ComObject was not null:{0}, name:{1}.", this.ComObject, this.ComObjectName));
}
//Debug.Assert(false);
}
#endif
public T ComObject
{
get
{
Debug.Assert(!m_disposed);
return m_comObject;
}
}
private string ComObjectName
{
get
{
if(this.ComObject is Microsoft.Office.Interop.Excel.Workbook)
{
return ((Microsoft.Office.Interop.Excel.Workbook)this.ComObject).Name;
}
return null;
}
}
public void Disarm()
{
Debug.Assert(!m_disposed);
m_armed = false;
}
#region IDisposable Members
public void Dispose()
{
Dispose(true);
#if DEBUG
GC.SuppressFinalize(this);
#endif
}
#endregion
protected virtual void Dispose(bool disposing)
{
if (!m_disposed)
{
if (m_armed)
{
int refcnt = 0;
do
{
refcnt = System.Runtime.InteropServices.Marshal.ReleaseComObject(m_comObject);
} while (refcnt > 0);
m_comObject = default(T);
}
m_disposed = true;
}
}
}
åèï¼
http://www.deez.info/sengelha/2005/02/11/useful-idisposable-class-3-autoreleasecomobject/
#24楼
é¦å -ä½ ä»æ¥æ²¡æå«
Marshal.ReleaseComObject(...)
æ
Marshal.FinalReleaseComObject(...)
åçExceläºæä½æ¶ã è¿æ¯ä¸ä¸ªä»¤äººå°æçå模å¼ï¼ä½æ¯æå ³æ¤ä¿¡æ¯ï¼å æ¬æ¥èªMicrosoftçä¿¡æ¯ï¼è¡¨ææ¨å¿ é¡»æå¨ä».NETéæ¾COMå¼ç¨æ¯ä¸æ£ç¡®çã äºå®æ¯.NETè¿è¡æ¶ååå¾æ¶éå¨æ£ç¡®å°è·è¸ªå¹¶æ¸ çäºCOMå¼ç¨ã 对äºæ¨ç代ç ï¼è¿æå³çæ¨å¯ä»¥å¨é¡¶é¨å é¤æ´ä¸ª`whileï¼...ï¼å¾ªç¯ã
å ¶æ¬¡ï¼å¦æè¦ç¡®ä¿å¨è¿ç¨ç»ææ¶æ¸ é¤å¯¹è¿ç¨å¤COM对象çCOMå¼ç¨ï¼ä»¥ä¾¿Excelè¿ç¨å°å ³éï¼ï¼åéè¦ç¡®ä¿åå¾æ¶éå¨è¿è¡ã æ¨å¯ä»¥éè¿è°ç¨
GC.Collect()
å
GC.WaitForPendingFinalizers()
æ£ç¡®å°åå°è¿ä¸ç¹ã 两次è°ç¨é½æ¯å®å ¨çï¼å¹¶ä¸å¯ä»¥ç¡®ä¿ä¹ç¡®å®æ¸ é¤äºå¾ªç¯ï¼å°½ç®¡æä¸ç¡®å®æ¯å¦éè¦å¾ªç¯ï¼å¹¶ä¸å¸æçå°æ¾ç¤ºæ¤ç¤ºä¾ç示ä¾ï¼ã
第ä¸ï¼å¨è°è¯å¨ä¸è¿è¡æ¶ï¼æ¬å°å¼ç¨å°è¢«äººä¸ºä¿çï¼ç´å°æ¹æ³ç»æï¼ä»¥ä¾¿è¿è¡å±é¨åéæ£æ¥ï¼ã å æ¤ï¼
GC.Collect()
è°ç¨å¯¹äºä»åä¸æ¹æ³æ¸ é¤
rng.Cells
ä¹ç±»ç对象
rng.Cells
ã æ¨åºè¯¥å°è¿è¡COMäºæä½ç代ç ä»GCæ¸ çä¸æå为åç¬çæ¹æ³ã ï¼è¿å¯¹ææ¥è¯´æ¯ä¸ä¸ªå ³é®åç°ï¼æ¥èª@nightcoderå¨æ¤å¤åå¸ççæ¡çä¸é¨åãï¼
å æ¤ï¼ä¸è¬æ¨¡å¼ä¸ºï¼
Sub WrapperThatCleansUp()
' NOTE: Don't call Excel objects in here...
' Debugger would keep alive until end, preventing GC cleanup
' Call a separate function that talks to Excel
DoTheWork()
' Now let the GC clean up (twice, to clean up cycles too)
GC.Collect()
GC.WaitForPendingFinalizers()
GC.Collect()
GC.WaitForPendingFinalizers()
End Sub
Sub DoTheWork()
Dim app As New Microsoft.Office.Interop.Excel.Application
Dim book As Microsoft.Office.Interop.Excel.Workbook = app.Workbooks.Add()
Dim worksheet As Microsoft.Office.Interop.Excel.Worksheet = book.Worksheets("Sheet1")
app.Visible = True
For i As Integer = 1 To 10
worksheet.Cells.Range("A" & i).Value = "Hello"
Next
book.Save()
book.Close()
app.Quit()
' NOTE: No calls the Marshal.ReleaseComObject() are ever needed
End Sub
å ³äºæ¤é®é¢æå¾å¤é误çä¿¡æ¯åå°æï¼å æ¬MSDNåStack Overflowä¸ç许å¤å¸åï¼å°¤å ¶æ¯è¿ä¸ªé®é¢ï¼ï¼ã
æç»è¯´ææä»ç»çä¸ä¸å¹¶æ¾åºæ£ç¡®å»ºè®®çæ¯å客æç« Marshal.ReleaseComObject被认为æ¯å±é©ç ï¼åæ¶åç°å¨è°è¯å¨ä¸ä¿åçå¼ç¨ä»ç¶åå¨é®é¢ï¼è¿ä½¿æçæ©ææµè¯æå°å°æã
#25楼
æç®åæ£å¨ä»äºOfficeèªå¨åæ¹é¢çå·¥ä½ï¼å¶ç¶åç°äºä¸ä¸ªå¯¹ææ¥è¯´æ¯æ¬¡é½ææç解å³æ¹æ¡ã å®å¾ç®åï¼ä¸æ¶åææ»ä»»ä½è¿ç¨ã
ä¼¼ä¹ä» éè¿å¾ªç¯å½åæ´»å¨çè¿ç¨ï¼å¹¶ä»¥ä»»ä½â访é®âå¼æ¾çExcelè¿ç¨çæ¹å¼ï¼å°±å¯ä»¥å é¤ä»»ä½æ£ä¹±çExcelå®ä¾ã 以ä¸ä»£ç ä» æ£æ¥å称为â Excelâçè¿ç¨ï¼ç¶åå°è¿ç¨çMainWindowTitleå±æ§åå ¥å符串ã ä¸è¿ç¨çè¿ç§â交äºâä¼¼ä¹ä½¿Windows赶ä¸å¹¶ä¸æ¢äºå»ç»çExcelå®ä¾ã
ææ£å¨å¼åçå¤æ¥ç¨åºéåºä¹åè¿è¡ä»¥ä¸æ¹æ³ï¼å 为å®è§¦åäºå®çå¸è½½äºä»¶ã æ¯æ¬¡é½å é¤ä»»ä½æ¬æçExcelå®ä¾ã èå®è¯´ï¼æä¸å®å ¨ç¡®å®ä¸ºä»ä¹ä¼è¿æ ·ï¼ä½æ¯å®å¯¹ææ¥è¯´å¾å¥½ï¼å¯ä»¥æ¾å¨ä»»ä½Excelåºç¨ç¨åºçæ«å°¾ï¼èä¸å¿ æ å¿åç¹ï¼Marshal.ReleaseComObjectæç»æ¢è¿ç¨ã æ对任ä½ä¸ºä»ä¹ææç建议é½é常æå ´è¶£ã
public static void SweepExcelProcesses()
{
if (Process.GetProcessesByName("EXCEL").Length != 0)
{
Process[] processes = Process.GetProcesses();
foreach (Process process in processes)
{
if (process.ProcessName.ToString() == "excel")
{
string title = process.MainWindowTitle;
}
}
}
}
#26楼
æä¸è½ç¸ä¿¡è¿ä¸ªé®é¢å°æ°äºå ¨ä¸ç5å¹´ã...å¦æå建äºä¸ä¸ªåºç¨ç¨åºï¼åéè¦å å ³éå®ï¼ç¶ååå é¤é¾æ¥ã
objExcel = new Excel.Application();
objBook = (Excel.Workbook)(objExcel.Workbooks.Add(Type.Missing));
å ³éæ¶
objBook.Close(true, Type.Missing, Type.Missing);
objExcel.Application.Quit();
objExcel.Quit();
å½æ¨æ°å»ºä¸ä¸ªexcelåºç¨ç¨åºæ¶ï¼å®å°å¨åå°æå¼ä¸ä¸ªexcelç¨åºã å¨éæ¾é¾æ¥ä¹åï¼æ¨éè¦å½ä»¤excelç¨åºéåºï¼å 为该excelç¨åºä¸æ¯æ¨ç´æ¥æ§å¶çä¸é¨åã å æ¤ï¼å¦æéæ¾é¾æ¥ï¼å®å°ä¿ææå¼ç¶æï¼
大家好ç¼ç¨~~
#27楼
è¦è¡¥å 说æ为ä»ä¹å³ä½¿å¨è¯»åï¼å建å对æ¯ä¸ªå¯¹è±¡å建ç´æ¥å¼ç¨æ¶ï¼Excelä¹ä¸ä¼å ³éçåå æ¯â Forâ循ç¯ã
For Each objWorkBook As WorkBook in objWorkBooks 'local ref, created from ExcelApp.WorkBooks to avoid the double-dot
objWorkBook.Close 'or whatever
FinalReleaseComObject(objWorkBook)
objWorkBook = Nothing
Next
'The above does not work, and this is the workaround:
For intCounter As Integer = 1 To mobjExcel_WorkBooks.Count
Dim objTempWorkBook As Workbook = mobjExcel_WorkBooks.Item(intCounter)
objTempWorkBook.Saved = True
objTempWorkBook.Close(False, Type.Missing, Type.Missing)
FinalReleaseComObject(objTempWorkBook)
objTempWorkBook = Nothing
Next
#28楼
å¦æ以ä¸ææå 容åæ æï¼è¯·å°è¯ç»Excelä¸äºæ¶é´ä»¥å ³éå ¶å·¥ä½è¡¨ï¼
app.workbooks.Close();
Thread.Sleep(500); // adjust, for me it works at around 300+
app.Quit();
...
FinalReleaseComObject(app);
#29楼
è¿ç§ç¡®å®ä¼¼ä¹è¿äºå¤æäºã æ ¹æ®æçç»éªï¼åªæ以ä¸ä¸ä¸ªå ³é®äºé¡¹æè½ä½¿Excelæ£ç¡®å ³éï¼
1ï¼ç¡®ä¿æ²¡æå©ä½ç对æ¨å建çexcelåºç¨ç¨åºçå¼ç¨ï¼æ 论å¦ä½ï¼æ¨é½åºè¯¥åªæä¸ä¸ªå¼ç¨ï¼å°å ¶è®¾ç½®ä¸º
null
ï¼
2ï¼è°ç¨
GC.Collect()
3ï¼å¿ é¡»éè¿ç¨æ·æå¨å ³éç¨åºæéè¿å¨Excel对象ä¸è°ç¨
Quit
æ¥å ³éExcelã ï¼è¯·æ³¨æï¼â
Quit
åè½çä½ç¨å°±å¦åç¨æ·å°è¯å ³éç¨åºä¸æ ·ï¼å¹¶ä¸å³ä½¿æ²¡æå¯è§çExcelï¼å¦æææªä¿åçæ´æ¹ï¼ä¹ä¼æ¾ç¤ºä¸ä¸ªç¡®è®¤å¯¹è¯æ¡ãç¨æ·å¯ä»¥æâåæ¶âï¼ç¶åå°±ä¸ä¼å ³éExcelã ï¼
1å¿ é¡»å¨2ä¹ååçï¼ä½3å¯ä»¥éæ¶åçã
ä¸ç§å®ç°æ¤æ¹æ³çæ¹æ³æ¯å°Interop Excel对象ä¸æ¨èªå·±çç±»å è£ å¨ä¸èµ·ï¼å¨æé å½æ°ä¸å建interopå®ä¾ï¼å¹¶ä½¿ç¨Disposeå®ç°IDisposableï¼å¦ä¸æ示ï¼
if (!mDisposed) {
mExcel = null;
GC.Collect();
mDisposed = true;
}
è¿å°ä»ç¨åºæ¹é¢æ¸ é¤excelã ä¸æ¦å ³éExcelï¼ç±ç¨æ·æå¨æéè¿è°ç¨
Quit
ï¼ï¼è¯¥è¿ç¨å°æ¶å¤±ã å¦æç¨åºå·²ç»å ³éï¼å该è¿ç¨å°å¨
GC.Collect()
è°ç¨ä¸æ¶å¤±ã
ï¼æä¸ç¡®å®å®çéè¦æ§å¦ä½ï¼ä½æ¯æ¨å¯è½éè¦å¨
GC.Collect()
è°ç¨ä¹åè°ç¨
GC.WaitForPendingFinalizers()
ä½å¹¶éå¿ é¡»è¦æè±Excelæµç¨ãï¼
å¤å¹´æ¥ï¼è¿å¯¹æä¸ç´æ²¡æé®é¢ã 请记ä½ï¼å°½ç®¡è¿æ ·åææï¼ä½å®é ä¸æ¨å¿ é¡»ä¼é å°å ³éå®æè½æ£å¸¸å·¥ä½ã å¦æå¨æ¸ çExcelä¹åä¸æç¨åºï¼é常æ¯å¨è°è¯ç¨åºæ¶æâåæ¢âï¼ï¼æ¨ä»å°ç§¯ç´¯excel.exeè¿ç¨ã
#30楼
使ç¨Word / Exceläºæä½åºç¨ç¨åºæ¶åºæ ¼å¤å°å¿ã å¨å°è¯äºææ解å³æ¹æ¡ä¹åï¼æ们ä»ç¶å¨æå¡å¨ï¼æ¥æ2000å¤ä¸ªç¨æ·ï¼ä¸æå¼äºè®¸å¤â WinWordâè¿ç¨ã
å¨è§£å³äºå 个å°æ¶çé®é¢åï¼ææè¯å°ï¼å¦æåæ¶å¨ä¸å线ç¨ä¸ä½¿ç¨
Word.ApplicationClass.Document.Open()
æå¼å¤ä¸ªææ¡£ï¼åIISå·¥ä½è¿ç¨ï¼w3wp.exeï¼å°ä¼å´©æºï¼ä»è使ææWinWordè¿ç¨ä¿ææå¼ç¶æï¼
å æ¤ï¼æ认为没æç»å¯¹ç解å³æ¹æ¡å¯ä»¥è§£å³æ¤é®é¢ï¼èæ¯åæ¢å°å ¶ä»æ¹æ³ï¼ä¾å¦Office Open XMLå¼åã