问题描述
我已将数据从数据库导出到Windows服务中的excel.
我已经使用Interop对象将数据导出到excel,但是不幸的是我无法释放这些对象,并且任务管理器中的进程显示了excel列表.
请建议可以做什么.
以下是代码:
Hi,
I have exported data from database into excel in my Windows Service.
I have used Interop objects for exporting data into excel but unfortunately I am not able to release the objects and the processes in the task manager shows up a list of excels.
Please suggest what can be done.
Following is the code:
public void automate()
{
string filepath,filename, fileExcel,renamedfile;
string ReportCount = "select count(rs.contactID) from tblReportLOg rs inner join tblContact c on c.contactID = rs.contactID where LogDate >= '2011-01-20 00:00:00.000' and c.email like '%micro%' and rs.contactid not in (39287,39286,27546)";
string UserCount = "select count(distinct(rs.contactID)) from tblReportLOg rs inner join tblContact c on c.contactID = rs.contactID where LogDate >= '2011-01-20 00:00:00.000' and c.email like '%micro%' and rs.contactid not in (39287,39286,27546)";
Random nRandom = new Random(DateTime.Now.Millisecond);
//Create a random file name.
fileExcel = "t" + nRandom.Next().ToString() + ".xls";
filepath = "C:\\test";
filename = filepath + "\\" + "test.xlsx";
renamedfile = System.IO.Path.GetFileNameWithoutExtension(filename);
renamedfile = System.IO.Path.GetPathRoot(filename) + "test\\" + renamedfile + '_' + DateTime.Now.ToString("ddMMyyyy hhmm") + System.IO.Path.GetExtension(filename);
Microsoft.Office.Interop.Excel._Application excelApp = new Microsoft.Office.Interop.Excel.Application();
Excel.Workbook excelWorkbook = excelApp.Workbooks.Add(Type.Missing);
try
{
SqlConnection conn = new SqlConnection();
conn.ConnectionString = ConfigurationManager.AppSettings["Connection"];
SqlCommand cmd = new SqlCommand("select rs.contactID,SurveyType,ModuleName,LogDate from tblReportLOg rs inner join tblContact c on c.contactID = rs.contactID where LogDate >= '2011-01-20 00:00:00.000' and c.email like '%micro%' and rs.contactid not in (39287,39286,27546) order by LogDate ", conn);
SqlCommand cmd1 = new SqlCommand(ReportCount, conn);
SqlCommand cmd2 = new SqlCommand(UserCount, conn);
SqlDataAdapter mySqlDataAdapter = new SqlDataAdapter(cmd);
DataSet d = new DataSet();
mySqlDataAdapter.Fill(d, "dataset");
conn.Open();
string reports = Convert.ToString(cmd1.ExecuteScalar());
string users = Convert.ToString(cmd2.ExecuteScalar());
int sheetIndex = 0;
// Copy each DataTable
foreach (System.Data.DataTable dt in d.Tables)
{
// Copy the DataTable to an object array
object[,] rawData = new object[dt.Rows.Count + 1, dt.Columns.Count];
// Copy the column names to the first row of the object array
for (int col = 0; col < dt.Columns.Count; col++)
{
// if(dt.Columns[col].ColumnName).
rawData[0, col] = dt.Columns[col].ColumnName;
}
// Copy the values to the object array
for (int col = 0; col < dt.Columns.Count; col++)
{
for (int row = 0; row < dt.Rows.Count; row++)
{
rawData[row + 1, col] = dt.Rows[row].ItemArray[col];
}
}
// Calculate the final column letter
string finalColLetter = string.Empty;
string colCharset = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";
int colCharsetLen = colCharset.Length;
if (dt.Columns.Count > colCharsetLen)
{
finalColLetter = colCharset.Substring((dt.Columns.Count - 1) / colCharsetLen - 1, 1);
}
finalColLetter += colCharset.Substring((dt.Columns.Count - 1) % colCharsetLen, 1);
// Create a new Sheet
Excel.Worksheet excelSheet = (Excel.Worksheet)excelWorkbook.Sheets.Add(excelWorkbook.Sheets.get_Item(++sheetIndex), Type.Missing, 1, Microsoft.Office.Interop.Excel.XlSheetType.xlWorksheet);
excelSheet.Name = dt.TableName;
WriteToLog("Create a new Sheet");
// Fast data export to Excel
string excelRange = string.Format("A1:{0}{1}", finalColLetter, dt.Rows.Count + 1);
excelSheet.get_Range(excelRange, Type.Missing).Value2 = rawData;
Excel.Range rg,rg1;
rg = excelSheet.get_Range("D:D", Type.Missing);
rg.NumberFormat = "MM-DD-YYYY hh:mm";
rg1 = excelSheet.get_Range("B:B", Type.Missing);
rg1.Cells.Replace("SCC", "SC",Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
rg1.Cells.Replace("SPC", "SP", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
rg1.Cells.Replace("TADay", "STA Day", Excel.XlLookAt.xlWhole, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
rg1.Cells.Replace("TA", "STA Hour", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
//rg1.Cells.Replace("TAC", "STA Hour", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
//rg1.Cells.Replace("SCCMTS", "SC Comment", Excel.XlLookAt.xlWhole, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
//rg1.Cells.Replace("TACDay", "STA Day", Excel.XlLookAt.xlWhole, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
Marshal.FinalReleaseComObject(excelSheet);
Marshal.FinalReleaseComObject(rg);
}
// Save and Close the Workbook
WriteToLog("save workbook");
excelWorkbook.SaveCopyAs(renamedfile);//, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
WriteToLog("Control cleared");
excelWorkbook.Close(true, Type.Missing, Type.Missing);
WriteToLog("file creation check");
}
catch (Exception ex)
{
WriteToLog(ex.Message);
}
finally
{
// Collect the unreferenced objects
GC.Collect();
GC.WaitForPendingFinalizers();
// Release the Application object
//Marshal.ReleaseComObject(excelSheet);
// Marshal.ReleaseComObject(excelRange);
// Marshal.ReleaseComObject(rg);
// Marshal.ReleaseComObject(rg1);
Marshal.FinalReleaseComObject(excelWorkbook);
Marshal.FinalReleaseComObject(excelApp);
excelApp.Quit();
excelApp = null;
excelWorkbook = null;
}
}
推荐答案
ExcelApplication excel = new ExcelApplication();
excel.Workbooks[0].Worksheets...
等等等等.使用如下..
etc etc..use as follows..
ExcelApplication excel = new ExcelApplication(rm);
ExcelWorkbooks xlBooks = excel.Workbooks();
ExcelWorkbook xlBook = xlBooks.Open("c:\\sales.xls");
ExcelWorksheets xlSheets = xlBook.Worksheets();
ExcelWorksheet xlSheet = xlSheets.Item(1);
ExcelRange xlRange = xlSheet.Cells();
这些中的每一个都会增加一个COM引用,并且您需要释放它们以使excel正确放置在互操作的末尾.否则,您将以挂起Excel进程结束.
我可以在您发布的代码中看到您没有执行此操作,这可能是导致挂起过程的原因.
您可能需要使用Excel包装器.虽然我仍然要确保您仔细使用Excel并遵循COM互操作规则,但Excel Wrapper类提供了一种解决方案,以确保没有孤立的Excel对象
该类的用法如下...
Each one of these increments a COM reference and you need to release them all in order for excel to be correctly disposed at the end of your interop. Otherwise, you''ll end of with hanging Excel processes.
I can see in the code you''ve posted that you''re not doing this and it''s probably what''s caused the hanging process.
Something you may want is to use an Excel Wrapper. While I still say make sure you use Excel carefully and follow the rules for COM interop, the Excel Wrapper class offers a solution to make sure there are no orphaned Excel objects
Usage of this class is as follows...
using (var wrapper = new ExcelWrapper())
{
// Access to the Excel.Application instance
var application = wrapper.Application;
// Any Interop code to automate excel goes here
}
使用块完成后,Excel将终止.查看实现的Dispose方法
When the using block is finished, Excel will be terminated. Look at the Dispose method for implementation
namespace Excel.Helpers
{
using System;
using System.Runtime.InteropServices;
using System.Diagnostics;
using Microsoft.Office.Interop.Excel;
/// <summary>
/// Helper class for automating excel
/// </summary>
/// <remarks>
/// Loads of issues with COM interop and ensuring that we release all variables
/// to ensure we don't get orphaned excel processes floating about, this class helps
/// out with that
/// </remarks>
internal class ExcelWrapper : IDisposable
{
private class Window
{
[DllImport("user32.dll", SetLastError = true)]
static extern IntPtr FindWindow(string lpClassName, string lpWindowName);
[DllImport("user32.dll")]
private static extern IntPtr GetWindowThreadProcessId(IntPtr hWnd, out IntPtr ProcessId);
public static IntPtr GetWindowThreadProcessId(IntPtr hWnd)
{
IntPtr processId;
IntPtr returnResult = GetWindowThreadProcessId(hWnd, out processId);
return processId;
}
public static IntPtr FindExcel(string caption)
{
System.IntPtr hWnd = FindWindow("XLMAIN", caption);
return hWnd;
}
}
private Application excel;
private IntPtr windowHandle;
private IntPtr processId;
private const string ExcelWindowCaption = "MyUniqueExcelCaption";
public ExcelWrapper()
{
// Create the excel application
excel = CreateExcelApplication();
// Find the specific process we have created with the caption 'COExcel',
// get the window handle
windowHandle = Window.FindExcel(ExcelWindowCaption);
// Now we can get the process ID from the hWnd
processId = Window.GetWindowThreadProcessId(windowHandle);
}
/// <summary>
/// Creates an EXCEL.EXE instance with some common properties set, ready for automation
/// </summary>
/// <returns>Excel Application object</returns>
private Application CreateExcelApplication()
{
Application excel = new Application();
excel.Caption = ExcelWindowCaption;
excel.DisplayAlerts = false;
excel.AlertBeforeOverwriting = false;
excel.AskToUpdateLinks = false;
return excel;
}
/// <summary>
/// Read only. Access to the application object
/// </summary>
public Application Excel
{
get { return this.excel; }
}
/// <summary>
/// Read only. The process id that the automated instance of Excel is
/// running as
/// </summary>
public int ProcessId
{
get { return this.processId.ToInt32(); }
}
/// <summary>
/// Read only. The window handle of the automated Excel instance
/// </summary>
public int WindowHandle
{
get { return this.windowHandle.ToInt32(); }
}
#region IDisposable Members
/// <summary>
/// The dispose method will attempt to clean up any COM objects that we have used
/// during automation
/// </summary>
public void Dispose()
{
if (excel != null)
{
excel.Workbooks.Close();
excel.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
excel = null;
GC.Collect();
GC.WaitForPendingFinalizers();
// The GC needs to be called twice in order to get the
// Finalizers called - the first time in, it simply makes
// a list of what is to be finalized, the second time in,
// it actually the finalizing. Only then will the
// object do its automatic ReleaseComObject.
GC.Collect();
GC.WaitForPendingFinalizers();
// OK, hammer and egg time. If we've still got a process open
// maybe due to accidentially incrementing the COM interfaces
// without using an object reference, then we'll just kill
// the process we have created since we know it's finished
// now
try
{
Process process = Process.GetProcessById(this.ProcessId);
if (process != null)
{
process.Kill();
}
}
catch
{
}
}
}
#endregion
}
}
Marshal.FinalReleaseComObject(excelApp);
excelApp.Quit();
因此,如果这有效,则excelApp不会发布.如果excelApp.Quit()没有自我处置,则在调用此函数后调用发布.
让我知道是否可行.
So if that works then the excelApp not released. if excelApp.Quit() not self dispose, then call the release after calling this function.
Let me know if that works.
这篇关于释放Excel COM对象方面的困难的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!