How to close and release the resources of an excel instance created by a background worker

See more:

C#3.0

C#

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); }
        }