如何关闭和释放后台工作者创建的excel实例的资源 [英] How to close and release the resources of an excel instance created by a background worker
本文介绍了如何关闭和释放后台工作者创建的excel实例的资源的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我有一个后台工作程序，可以打开并编辑excel文件。但是如果后台工作程序在完成之前被取消，则excel文件不会被关闭，资源也不会被释放。请问如何解决这个问题，即关闭并释放后台工作人员取消的excel资源。我的代码添加如下：
I have a background worker which opens and edits an excel file. But if the background worker is cancelled before completion, the excel file doesn't get closed and the resources would not be released. Please how do I get around this problem, that is, to close and release the excel resources on background worker cancellation. My code is added below:
private void exportBW_DoWork(object sender, DoWorkEventArgs e)
{
try
{
string query = "select excel_sheet from paysheet where id = 1";
byte[] file = null;
SQLiteCommand cmd = new SQLiteCommand(query, con);
openConnection();
file = (byte[])cmd.ExecuteScalar();
closeConnection();
File.WriteAllBytes(System.Windows.Forms.Application.StartupPath + @"\\Akiota Payment Sheet.xls",file);
Microsoft.Office.Interop.Excel.Application xla = new Microsoft.Office.Interop.Excel.Application();
Workbook wb = xla.Workbooks.Open(System.Windows.Forms.Application.StartupPath + @"\\Akiota Payment Sheet.xls", Missing.Value, Missing.Value, 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 ws = (Worksheet)xla.ActiveSheet;
xla.Visible = true;
string day = "";
string month = "";
for (int i = 5; i <= 11; i++)
{
if (i > 5)
week_first_date = week_first_date.AddDays(1.0);
switch (week_first_date.Day)
{
case 1: day = "1st"; break;
case 2: day = "2nd"; break;
case 3: day = "3rd"; break;
case 21: day = "21st"; break;
case 22: day = "22nd"; break;
case 23: day = "23rd"; break;
case 31: day = "31st"; break;
default: day = week_first_date.Day.ToString() + "th"; break;
}
month = DateTimeFormatInfo.InvariantInfo.GetMonthName(week_first_date.Month);
ws.Cells[6, i] = day + " " + month.ToUpper();
}
int last_row = 0;
for (int i = 0; i < regDGV.Rows.Count; i++)
{
//Data from database
ws.Cells[i + 8, 1] = (i + 1).ToString(); //S/N
ws.Cells[i + 8, 2] = regDGV[0, i].Value.ToString(); //Name
ws.Cells[i + 8, 3] = regDGV[1, i].Value.ToString(); //Designation
ws.Cells[i + 8, 5] = ((Convert.ToInt32(regDGV[2, i].Value) == 1) ? "1" : "X"); //Sun
ws.Cells[i + 8, 6] = ((Convert.ToInt32(regDGV[3, i].Value) == 1) ? "1" : "X"); //Mon
ws.Cells[i + 8, 7] = ((Convert.ToInt32(regDGV[4, i].Value) == 1) ? "1" : "X"); //Tues
ws.Cells[i + 8, 8] = ((Convert.ToInt32(regDGV[5, i].Value) == 1) ? "1" : "X"); //Wed
ws.Cells[i + 8, 9] = ((Convert.ToInt32(regDGV[6, i].Value) == 1) ? "1" : "X"); //Thurs
ws.Cells[i + 8, 10] = ((Convert.ToInt32(regDGV[7, i].Value) == 1) ? "1" : "X"); //Fri
ws.Cells[i + 8, 11] = ((Convert.ToInt32(regDGV[8, i].Value) == 1) ? "1" : "X"); //Sat
//Abitrary figures
ws.Cells[i + 8, 4] = ((Convert.ToDouble(regDGV[9, i].Value) > 0.0) ? Convert.ToDouble(regDGV[9, i].Value) : 0.0); //"1500"; daily rate
if (i == 15) ws.Cells[i + 8, 17] = ""; //overtime hours
else ws.Cells[i + 8, 17] = "2";
//Formulas
ws.Cells[i + 8, 12] = "=Sum(E" + (i + 8).ToString() + ":K" + (i + 8).ToString() + ""; //total no. of days
ws.Cells[i + 8, 13] = "=IF(E" + (i + 8).ToString() + "=1,D" + (i + 8).ToString() + "*1.5,0)"; //sun or holiday work
ws.Cells[i + 8, 15] = "=D" + (i + 8).ToString() + "/9"; //overtime rate per hr
ws.Cells[i + 8, 16] = "=FLOOR(O" + (i + 8).ToString() + ",3)"; //rounding
ws.Cells[i + 8, 18] = "=P" + (i + 8).ToString() + "*Q" + (i + 8).ToString() + ""; //overtime due
ws.Cells[i + 8, 19] = "=(D" + (i + 8).ToString() + "*L" + (i + 8).ToString() + ")+M" + (i + 8).ToString() + "+R" + (i + 8).ToString() + "+N" + (i + 8).ToString() + ""; //amount due
ws.Cells[i + 8, 22] = "=S" + (i + 8).ToString() + "-T" + (i + 8).ToString() + ""; //amount payable
//Color
((Range)ws.Cells[i + 8, 5]).Interior.Color = Color.LightGray.ToArgb(); //color sun column
//Alignment
((Range)ws.Cells[i + 8, 12]).HorizontalAlignment = XlVAlign.xlVAlignCenter; //total no. of days
((Range)ws.Cells[i + 8, 13]).HorizontalAlignment = XlVAlign.xlVAlignCenter; //sun or holiday work
((Range)ws.Cells[i + 8, 15]).HorizontalAlignment = XlVAlign.xlVAlignCenter; //overtime rate per hr
((Range)ws.Cells[i + 8, 17]).HorizontalAlignment = XlVAlign.xlVAlignCenter; //overtime hrs
((Range)ws.Cells[i + 8, 18]).HorizontalAlignment = XlVAlign.xlVAlignCenter; //overtime due
((Range)ws.Cells[i + 8, 19]).HorizontalAlignment = XlVAlign.xlVAlignCenter; //amount due
((Range)ws.Cells[i + 8, 22]).HorizontalAlignment = XlVAlign.xlVAlignCenter; //amount payable
//Cell Number format
((Range)ws.Cells[i + 8, 13]).NumberFormat = "#,###.00"; //sun or holiday work
((Range)ws.Cells[i + 8, 15]).NumberFormat = "#,###.00"; //overtime rate per hr
((Range)ws.Cells[i + 8, 16]).NumberFormat = "#,###.00"; //rounding
((Range)ws.Cells[i + 8, 18]).NumberFormat = "#,##0.00"; //overtime due
((Range)ws.Cells[i + 8, 19]).NumberFormat = "#,###.00"; //amount due
((Range)ws.Cells[i + 8, 22]).NumberFormat = "#,###.00"; //amount payable
//Cell Borders
for (int j = 1; j <= 23; j++)
((Range)ws.Cells[i + 8, j]).Borders.LineStyle = 3;
//Set Fonts type
for(int row_font = 1; row_font <= 22; row_font++)
((Range)ws.Cells[i + 8, row_font]).Font.Name = "Cambria";
//Set Font size
for (int row_font = 1; row_font <= 22; row_font++)
((Range)ws.Cells[i + 8, row_font]).Font.Size = 16;
last_row = i + 8;
}
last_row++;
//Finalize the sheet
ws.Cells[last_row, 12] = "=Sum(L8:L" + (last_row - 1).ToString() + ")"; //sumation of days
((Range)ws.Cells[last_row, 12]).Borders.LineStyle = 12;
((Range)ws.Cells[last_row, 12]).Borders.Weight = XlBorderWeight.xlMedium;
ws.Cells[last_row, 13] = "=SUM(M8:M" + (last_row - 1).ToString() + ")"; //sumation of sunday and holiday works
((Range)ws.Cells[last_row, 13]).Borders.LineStyle = 12;
((Range)ws.Cells[last_row, 13]).Borders.Weight = XlBorderWeight.xlMedium;
ws.Cells[last_row, 14] = "=SUM(N8:N" + (last_row - 1).ToString() + ")"; //sumation of night works
((Range)ws.Cells[last_row, 14]).Borders.LineStyle = 12;
((Range)ws.Cells[last_row, 14]).Borders.Weight = XlBorderWeight.xlMedium;
ws.Cells[last_row, 17] = "=SUM(Q8:Q" + (last_row - 1).ToString() + ")"; //sumation of overtime hours
((Range)ws.Cells[last_row, 17]).Borders.LineStyle = 12;
((Range)ws.Cells[last_row, 17]).Borders.Weight = XlBorderWeight.xlMedium;
ws.Cells[last_row, 18] = "=SUM(R8:R" + (last_row - 1).ToString() + ")"; //sumation of overtime due
((Range)ws.Cells[last_row, 18]).Borders.LineStyle = 12;
((Range)ws.Cells[last_row, 18]).Borders.Weight = XlBorderWeight.xlMedium;
ws.Cells[last_row, 19] = "=SUM(S8:S" + (last_row - 1).ToString() + ")"; //sumation of amount due
((Range)ws.Cells[last_row, 19]).Borders.LineStyle = 12;
((Range)ws.Cells[last_row, 19]).Borders.Weight = XlBorderWeight.xlMedium;
ws.Cells[last_row, 20] = "=SUM(T8:T" + (last_row - 1).ToString() + ")"; //sumation of surcharges
((Range)ws.Cells[last_row, 20]).Borders.LineStyle = 12;
((Range)ws.Cells[last_row, 20]).Borders.Weight = XlBorderWeight.xlMedium;
ws.Cells[last_row, 22] = "=SUM(V8:V" + (last_row - 1).ToString() + ")"; //sumation of amount payable
((Range)ws.Cells[last_row, 22]).Borders.LineStyle = 12;
((Range)ws.Cells[last_row, 22]).Borders.Weight = XlBorderWeight.xlMedium;
//Alignment
((Range)ws.Cells[last_row, 12]).HorizontalAlignment = XlVAlign.xlVAlignCenter; //days
((Range)ws.Cells[last_row, 13]).HorizontalAlignment = XlVAlign.xlVAlignCenter; //sun or holiday work
((Range)ws.Cells[last_row, 14]).HorizontalAlignment = XlVAlign.xlVAlignCenter; //night works
((Range)ws.Cells[last_row, 17]).HorizontalAlignment = XlVAlign.xlVAlignCenter; //overtime hrs
((Range)ws.Cells[last_row, 18]).HorizontalAlignment = XlVAlign.xlVAlignCenter; //overtime due
((Range)ws.Cells[last_row, 19]).HorizontalAlignment = XlVAlign.xlVAlignCenter; //amount due
((Range)ws.Cells[last_row, 22]).HorizontalAlignment = XlVAlign.xlVAlignCenter; //amount payable
//Cell Number format
//((Range)ws.Cells[last_row, 12]).NumberFormat = "#,###.00"; //days
((Range)ws.Cells[last_row, 13]).NumberFormat = "#,###.00"; //sun or holiday work
//((Range)ws.Cells[last_row, 14]).NumberFormat = "#,###.00"; //overtime rate per hr
((Range)ws.Cells[last_row, 17]).NumberFormat = "#,###.00"; //rounding
((Range)ws.Cells[last_row, 18]).NumberFormat = "#,##0.00"; //overtime due
((Range)ws.Cells[last_row, 19]).NumberFormat = "#,###.00"; //amount due
((Range)ws.Cells[last_row, 22]).NumberFormat = "#,###.00"; //amount payable
//font format
((Range)ws.Cells[last_row, 12]).Font.Bold = true;
((Range)ws.Cells[last_row, 13]).Font.Bold = true;
((Range)ws.Cells[last_row, 14]).Font.Bold = true;
((Range)ws.Cells[last_row, 17]).Font.Bold = true;
((Range)ws.Cells[last_row, 18]).Font.Bold = true;
((Range)ws.Cells[last_row, 19]).Font.Bold = true;
((Range)ws.Cells[last_row, 22]).Font.Bold = true;
last_row = last_row + 2;
ws.Cells[last_row, 12] = "PAYMENT SUMMARY FOR THE WEEK";
((Range)ws.Cells[last_row, 12]).Font.Name = "Cambria";
((Range)ws.Cells[last_row, 12]).Font.Underline = true;
((Range)ws.Cells[last_row, 12]).Font.Bold = true;
Microsoft.Office.Interop.Excel.Range ws_range = (Range)ws.get_Range("L" + last_row.ToString() + "", "P" + last_row.ToString() + "");
ws_range.Merge(5);
((Range)ws.Cells[last_row, 12]).HorizontalAlignment = XlVAlign.xlVAlignCenter;
last_row = last_row + 2;
ws.Cells[last_row, 11] = "TOTAL PAYMENT INCLUDING OVERTIMES, SUNDAY AND NIGHT WORK";
Microsoft.Office.Interop.Excel.Range er = ws.get_Range("K" + last_row.ToString(), "N" + (last_row + 2).ToString());
er.MergeCells = true;
er.WrapText = true;
er.Font.Name = "Cambria";
er.Font.Bold = true;
er.Font.Size = 16;
er.Borders.LineStyle = 12;
er = ws.get_Range("O" + last_row.ToString(), "O" + (last_row + 2).ToString());
er.MergeCells = true;
er.Borders.LineStyle = 12;
ws.Cells[last_row, 16] = "= V" + (last_row - 4).ToString();
er = ws.get_Range("P" + last_row.ToString(), "P" + (last_row + 2).ToString());
er.MergeCells = true;
er.WrapText = true;
er.Font.Name = "Cambria";
er.Font.Bold = true;
er.Font.Size = 16;
er.Borders.LineStyle = 12;
er = ws.get_Range("I" + (last_row + 3).ToString(), "N" + (last_row + 3).ToString());
er.Merge(true);
er.Borders.LineStyle = 12;
((Range)ws.Cells[(last_row + 3), 15]).Borders.LineStyle = 12;
((Range)ws.Cells[(last_row + 3), 16]).Borders.LineStyle = 12;
ws.Cells[(last_row + 4), 15] = "GRAND TOTAL PAY FOR THE WEEK INCLUDING OT AND SUNDAY WORK";
er = ws.get_Range("L" + (last_row + 4).ToString(), "O" + (last_row + 5).ToString());
er.MergeCells = true;
er.Font.Name = "Cambria";
er.Font.Size = 18;
er.Font.Bold = true;
er.Borders.LineStyle = 12;
er.Interior.Color = Color.LightGray.ToArgb();
er.WrapText = true;
er.VerticalAlignment = XlVAlign.xlVAlignBottom;
er.HorizontalAlignment = XlHAlign.xlHAlignCenter;
ws.Cells[(last_row + 4), 16] = "=P" + last_row.ToString();
er = ws.get_Range("P" + (last_row + 4).ToString(), "P" + (last_row + 5).ToString());
er.MergeCells = true;
er.Borders.LineStyle = 12;
er.Font.Name = "Cambria";
er.Font.Size = 18;
er.Font.Bold = true;
er.Interior.Color = Color.LightGray.ToArgb();
ws.Cells[(last_row + 5), 4] = "1";
((Range)ws.Cells[(last_row + 5), 4]).Borders.LineStyle = 12;
((Range)ws.Cells[(last_row + 5), 4]).Borders.Weight = XlBorderWeight.xlMedium;
((Range)ws.Cells[(last_row + 5), 4]).Font.Name = "Calibri";
((Range)ws.Cells[(last_row + 5), 4]).Font.Size = 16;
((Range)ws.Cells[(last_row + 5), 4]).HorizontalAlignment = XlHAlign.xlHAlignCenter;
ws.Cells[(last_row + 5), 5] = "→";
((Range)ws.Cells[(last_row + 5), 5]).Font.Name = "Calibri";
((Range)ws.Cells[(last_row + 5), 5]).Font.Size = 16;
((Range)ws.Cells[(last_row + 5), 5]).HorizontalAlignment = XlHAlign.xlHAlignCenter;
ws.Cells[(last_row + 5), 6] = "PRESENT";
er = ws.get_Range("F" + (last_row + 5).ToString(), "G" + (last_row + 5).ToString());
er.Merge(true);
er.Font.Name = "Calibri";
er.Font.Size = 16;
er.HorizontalAlignment = XlHAlign.xlHAlignCenter;
ws.Cells[(last_row + 5), 18] = "PREPARED BY:....................................................................................";
er = ws.get_Range("R" + (last_row + 5).ToString(), "V" + (last_row + 5).ToString());
er.Merge(true);
er.Font.Name = "Calibri";
er.Font.Size = 16;
er.HorizontalAlignment = XlHAlign.xlHAlignCenter;
ws.Cells[(last_row + 7), 4] = "X";
((Range)ws.Cells[(last_row + 7), 4]).Borders.LineStyle = 12;
((Range)ws.Cells[(last_row + 7), 4]).Borders.Weight = XlBorderWeight.xlMedium;
((Range)ws.Cells[(last_row + 7), 4]).Font.Name = "Calibri";
((Range)ws.Cells[(last_row + 7), 4]).Font.Size = 16;
((Range)ws.Cells[(last_row + 7), 4]).HorizontalAlignment = XlHAlign.xlHAlignCenter;
ws.Cells[(last_row + 7), 5] = "→";
((Range)ws.Cells[(last_row + 7), 5]).Font.Name = "Calibri";
((Range)ws.Cells[(last_row + 7), 5]).Font.Size = 16;
((Range)ws.Cells[(last_row + 7), 5]).HorizontalAlignment = XlHAlign.xlHAlignCenter;
ws.Cells[(last_row + 7), 6] = "ABSENT";
er = ws.get_Range("F" + (last_row + 7).ToString(), "G" + (last_row + 7).ToString());
er.Merge(true);
er.Font.Name = "Calibri";
er.Font.Size = 16;
er.HorizontalAlignment = XlHAlign.xlHAlignCenter;
((Range)ws.Cells[(last_row + 7), 10]).Interior.Color = Color.LightGray.ToArgb();
((Range)ws.Cells[(last_row + 7), 10]).Borders.LineStyle = 12;
((Range)ws.Cells[(last_row + 7), 10]).Borders.Weight = XlBorderWeight.xlMedium;
ws.Cells[(last_row + 7), 11] = "→";
((Range)ws.Cells[(last_row + 7), 11]).Font.Name = "Calibri";
((Range)ws.Cells[(last_row + 7), 11]).Font.Size = 16;
((Range)ws.Cells[(last_row + 7), 11]).HorizontalAlignment = XlHAlign.xlHAlignCenter;
ws.Cells[(last_row + 7), 12] = "PUBLIC HOLIDAY";
ws.Cells[(last_row + 7), 16] = "CHECKED BY:.......................................................................................";
er = ws.get_Range("P" + (last_row + 7).ToString(), "T" + (last_row + 7).ToString());
er.Merge(true);
er.Font.Name = "Calibri";
er.Font.Size = 16;
er.HorizontalAlignment = XlHAlign.xlHAlignCenter;
ws.Cells[(last_row + 9), 4] = "X";
((Range)ws.Cells[(last_row + 9), 4]).Interior.Color = Color.Red.ToArgb();
((Range)ws.Cells[(last_row + 9), 4]).Font.Name = "Calibri";
((Range)ws.Cells[(last_row + 9), 4]).Font.Size = 16;
((Range)ws.Cells[(last_row + 9), 4]).HorizontalAlignment = XlHAlign.xlHAlignCenter;
ws.Cells[(last_row + 9), 5] = "→";
((Range)ws.Cells[(last_row + 9), 5]).Font.Name = "Calibri";
((Range)ws.Cells[(last_row + 9), 5]).Font.Size = 16;
((Range)ws.Cells[(last_row + 9), 5]).HorizontalAlignment = XlHAlign.xlHAlignCenter;
ws.Cells[(last_row + 9), 6] = "INCLEMENT WEATHER";
er = ws.get_Range("F" + (last_row + 9).ToString(), "J" + (last_row + 9).ToString());
er.Merge(true);
er.Font.Name = "Calibri";
er.Font.Size = 16;
er.HorizontalAlignment = XlHAlign.xlHAlignCenter;
ws.Cells[(last_row + 9), 16] = "APPROVED BY:...................................................................................";
er = ws.get_Range("P" + (last_row + 9).ToString(), "T" + (last_row + 9).ToString());
er.Merge(true);
er.Font.Name = "Calibri";
er.Font.Size = 16;
er.HorizontalAlignment = XlHAlign.xlHAlignCenter;
var desktopFolder = Environment.GetFolderPath(Environment.SpecialFolder.DesktopDirectory);
var filePath = Path.Combine(desktopFolder, "Akiota Paysheet.xls");
wb.SaveAs(filePath, XlFileFormat.xlWorkbookNormal, Type.Missing, Type.Missing, Type.Missing, Type.Missing, XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
wb.Close(false, Type.Missing, Type.Missing);
xla.Quit();
GC.Collect();
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(ws);
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(wb);
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(xla);
}
catch (Exception ex) { MessageBox.Show(ex.Message); }
}
推荐答案
这里有一个很好的例子。
http://msdn.microsoft.com/en-us /library/cc221403%28v=vs.95%29.aspx [ ^ ]
您可以将资源打包到对象中，并将其传递给BackgroundWorker。 RunWorkerAsync作为参数。然后通过处理RunWorkerCompleted事件，您可以释放所有资源。
There is a good example here.
http://msdn.microsoft.com/en-us/library/cc221403%28v=vs.95%29.aspx[^]
You can package your resources into an object, pass it to BackgroundWorker.RunWorkerAsync as a parameter. Then by handling RunWorkerCompleted event, you can free all of the resources.
这篇关于如何关闭和释放后台工作者创建的excel实例的资源的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
