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++)
{
ws.Cells[i + 8, 1] = (i + 1).ToString();
ws.Cells[i + 8, 2] = regDGV[0, i].Value.ToString();
ws.Cells[i + 8, 3] = regDGV[1, i].Value.ToString();
ws.Cells[i + 8, 5] = ((Convert.ToInt32(regDGV[2, i].Value) == 1) ? "1" : "X");
ws.Cells[i + 8, 6] = ((Convert.ToInt32(regDGV[3, i].Value) == 1) ? "1" : "X");
ws.Cells[i + 8, 7] = ((Convert.ToInt32(regDGV[4, i].Value) == 1) ? "1" : "X");
ws.Cells[i + 8, 8] = ((Convert.ToInt32(regDGV[5, i].Value) == 1) ? "1" : "X");
ws.Cells[i + 8, 9] = ((Convert.ToInt32(regDGV[6, i].Value) == 1) ? "1" : "X");
ws.Cells[i + 8, 10] = ((Convert.ToInt32(regDGV[7, i].Value) == 1) ? "1" : "X");
ws.Cells[i + 8, 11] = ((Convert.ToInt32(regDGV[8, i].Value) == 1) ? "1" : "X");
ws.Cells[i + 8, 4] = ((Convert.ToDouble(regDGV[9, i].Value) > 0.0) ? Convert.ToDouble(regDGV[9, i].Value) : 0.0);
if (i == 15) ws.Cells[i + 8, 17] = "";
else ws.Cells[i + 8, 17] = "2";
ws.Cells[i + 8, 12] = "=Sum(E" + (i + 8).ToString() + ":K" + (i + 8).ToString() + "";
ws.Cells[i + 8, 13] = "=IF(E" + (i + 8).ToString() + "=1,D" + (i + 8).ToString() + "*1.5,0)";
ws.Cells[i + 8, 15] = "=D" + (i + 8).ToString() + "/9";
ws.Cells[i + 8, 16] = "=FLOOR(O" + (i + 8).ToString() + ",3)";
ws.Cells[i + 8, 18] = "=P" + (i + 8).ToString() + "*Q" + (i + 8).ToString() + "";
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() + "";
ws.Cells[i + 8, 22] = "=S" + (i + 8).ToString() + "-T" + (i + 8).ToString() + "";
((Range)ws.Cells[i + 8, 5]).Interior.Color = Color.LightGray.ToArgb();
((Range)ws.Cells[i + 8, 12]).HorizontalAlignment = XlVAlign.xlVAlignCenter;
((Range)ws.Cells[i + 8, 13]).HorizontalAlignment = XlVAlign.xlVAlignCenter;
((Range)ws.Cells[i + 8, 15]).HorizontalAlignment = XlVAlign.xlVAlignCenter;
((Range)ws.Cells[i + 8, 17]).HorizontalAlignment = XlVAlign.xlVAlignCenter;
((Range)ws.Cells[i + 8, 18]).HorizontalAlignment = XlVAlign.xlVAlignCenter;
((Range)ws.Cells[i + 8, 19]).HorizontalAlignment = XlVAlign.xlVAlignCenter;
((Range)ws.Cells[i + 8, 22]).HorizontalAlignment = XlVAlign.xlVAlignCenter;
((Range)ws.Cells[i + 8, 13]).NumberFormat = "#,###.00";
((Range)ws.Cells[i + 8, 15]).NumberFormat = "#,###.00";
((Range)ws.Cells[i + 8, 16]).NumberFormat = "#,###.00";
((Range)ws.Cells[i + 8, 18]).NumberFormat = "#,##0.00";
((Range)ws.Cells[i + 8, 19]).NumberFormat = "#,###.00";
((Range)ws.Cells[i + 8, 22]).NumberFormat = "#,###.00";
for (int j = 1; j <= 23; j++)
((Range)ws.Cells[i + 8, j]).Borders.LineStyle = 3;
for(int row_font = 1; row_font <= 22; row_font++)
((Range)ws.Cells[i + 8, row_font]).Font.Name = "Cambria";
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++;
ws.Cells[last_row, 12] = "=Sum(L8:L" + (last_row - 1).ToString() + ")";
((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() + ")";
((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() + ")";
((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() + ")";
((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() + ")";
((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() + ")";
((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() + ")";
((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() + ")";
((Range)ws.Cells[last_row, 22]).Borders.LineStyle = 12;
((Range)ws.Cells[last_row, 22]).Borders.Weight = XlBorderWeight.xlMedium;
((Range)ws.Cells[last_row, 12]).HorizontalAlignment = XlVAlign.xlVAlignCenter;
((Range)ws.Cells[last_row, 13]).HorizontalAlignment = XlVAlign.xlVAlignCenter;
((Range)ws.Cells[last_row, 14]).HorizontalAlignment = XlVAlign.xlVAlignCenter;
((Range)ws.Cells[last_row, 17]).HorizontalAlignment = XlVAlign.xlVAlignCenter;
((Range)ws.Cells[last_row, 18]).HorizontalAlignment = XlVAlign.xlVAlignCenter;
((Range)ws.Cells[last_row, 19]).HorizontalAlignment = XlVAlign.xlVAlignCenter;
((Range)ws.Cells[last_row, 22]).HorizontalAlignment = XlVAlign.xlVAlignCenter;
((Range)ws.Cells[last_row, 13]).NumberFormat = "#,###.00";
((Range)ws.Cells[last_row, 17]).NumberFormat = "#,###.00";
((Range)ws.Cells[last_row, 18]).NumberFormat = "#,##0.00";
((Range)ws.Cells[last_row, 19]).NumberFormat = "#,###.00";
((Range)ws.Cells[last_row, 22]).NumberFormat = "#,###.00";
((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); }
}