How to close the excel file using C#
  • Hi genius I have done below how to export the data to excel. but i dont know how to close the open file through the C# code . 
    I have mention below where i had an error plz help me .Here i used epplus dll
                string _ReportName = "Report";
    using (var package = new ExcelPackage())
                            {
                                ExcelWorksheet worksheet = package.Workbook.Worksheets.FirstOrDefault();

                                if (worksheet == null)
                                    worksheet = package.Workbook.Worksheets.Add(_ReportName);
                                else
                                    worksheet.Name = _ReportName;

                                worksheet.Cells["A1:G1"].Value = BranchCmboBox.Text.ToUpper() + "'s " + AlertNameCmboBox.Text.ToUpper() + "'s ALERT REPORT";
                                worksheet.Cells["A1:G1"].Merge = true;
                                worksheet.Cells["A1:G1"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                                worksheet.Cells["A1:G1"].Style.Font.Size = 14;
                                worksheet.Cells["A1:G1"].Style.Border.Top.Style = ExcelBorderStyle.Thin;
                                worksheet.Cells["A1:G1"].Style.Font.Bold = true;

                                worksheet.Cells["A2:B2"].Value = "Branch Name :";
                                worksheet.Cells["A2:B2"].Merge = true;
                                worksheet.Cells["A2:B2"].Style.Font.Size = 10;
                                worksheet.Cells["A2:B2"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Right;
                                worksheet.Cells["A2:B2"].Style.Font.Bold = true;

                                worksheet.Cells["C2"].Value = BranchCmboBox.Text;
                                worksheet.Cells["C2"].Style.Font.Size = 10;
                                worksheet.Cells["C2"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;

                                worksheet.Cells["E2:F2"].Value = "Vehicle Reg.No :";
                                worksheet.Cells["E2:F2"].Merge = true;
                                worksheet.Cells["E2:F2"].Style.Font.Size = 10;
                                worksheet.Cells["E2:F2"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Right;
                                worksheet.Cells["E2:F2"].Style.Font.Bold = true;

                                worksheet.Cells["G2"].Value = RegNoCmboBox.Text;
                                worksheet.Cells["G2"].Style.Font.Size = 10;
                                worksheet.Cells["G2"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;

                                worksheet.Cells["A3:B3"].Value = "From :";
                                worksheet.Cells["A3:B3"].Merge = true;
                                worksheet.Cells["A3:B3"].Style.Font.Size = 10;
                                worksheet.Cells["A3:B3"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Right;
                                worksheet.Cells["A3:B3"].Style.Font.Bold = true;

                                worksheet.Cells["C3"].Value = _FromDate;
                                worksheet.Cells["C3"].Style.Font.Size = 10;
                                worksheet.Cells["C3"].Style.Numberformat.Format = "dd-MM-yyyy HH:mm";
                                worksheet.Cells["C3"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;

                                worksheet.Cells["E3:F3"].Value = "To :";
                                worksheet.Cells["E3:F3"].Merge = true;
                                worksheet.Cells["E3:F3"].Style.Font.Size = 10;
                                worksheet.Cells["E3:F3"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Right;
                                worksheet.Cells["E3:F3"].Style.Font.Bold = true;

                                worksheet.Cells["G3"].Value = _ToDate;
                                worksheet.Cells["G3"].Style.Font.Size = 10;
                                worksheet.Cells["G3"].Style.Numberformat.Format = "dd-MM-yyyy HH:mm";
                                worksheet.Cells["G3"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;

                                worksheet.Cells["A4:G4"].Style.Font.Size = 12;
                                worksheet.Cells["A4:G4"].Style.Font.Bold = true;
                                worksheet.Cells["A4:G4"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                                worksheet.Cells["A4:G4"].Style.Border.Top.Style = ExcelBorderStyle.Thin;
                                worksheet.Cells["A1:G1"].Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
                                worksheet.Cells["A4:G4"].Style.VerticalAlignment = ExcelVerticalAlignment.Center;

                                int k = 4;
                                int l = 1;

                                foreach (DataGridViewColumn _col in AlertGridView.Columns)
                                {
                                    worksheet.Cells[k, l].Value = _col.HeaderText;
                                    l = _col.Index + 1; 
                                    if (l == 5)
                                    {
                                        worksheet.Column(l).Width = 20;
                                        worksheet.Column(l).Style.Numberformat.Format = "dd-MM-yyyy hh:mm:ss";
                                    }
                                    else if (l == 7)
                                        worksheet.Column(l).Width = 30;
                                    else if (l == 1)
                                        worksheet.Column(l).Width = 6;
                                    else
                                        worksheet.Column(l).Width = 18;
                                    l++;
                                }

                                int f = 5;
                                int j = 0;
                                foreach (DataGridViewRow _row in AlertGridView.Rows)
                                {
                                    j = 1;
                                    for (int iCol = 0; iCol < AlertGridView.ColumnCount; iCol++)
                                    {
                                        worksheet.Cells[f, j].Value = _row.Cells[iCol].Value;
                                        worksheet.Cells[f, j].Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
                                        worksheet.Cells[f, j].Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
                                        worksheet.Cells[f, j].Style.Border.Top.Style = ExcelBorderStyle.Thin;
                                        j++;
                                    }
                                    f++;
                                }
                                ExcelRange range = worksheet.Cells[4, 1, (f - 1), (j - 1)];

                                range.Style.Font.Size = 10;
                                range.Style.Border.Right.Style = ExcelBorderStyle.Thin;
                                range.Style.Border.Right.Color.SetColor(Color.Black);
                                range.Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
                                range.Style.Border.Bottom.Color.SetColor(Color.Black);
                                worksheet.Cells["A1:A" + (f - 1)].Style.Border.Left.Style = ExcelBorderStyle.Thin;
                                worksheet.Cells["A1:A" + (f - 1)].Style.Border.Left.Color.SetColor(Color.Black);
                                worksheet.Cells["G1:G" + (f - 1)].Style.Border.Right.Style = ExcelBorderStyle.Thin;
                                worksheet.Cells["G1:G" + (f - 1)].Style.Border.Right.Color.SetColor(Color.Black);
                                worksheet.Cells["A6:A" + (f - 1)].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                                worksheet.Cells["F6:F" + (f - 1)].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                                worksheet.Cells["G1:G" + (f - 1)].Style.WrapText = true;

                                worksheet.Protection.SetPassword("GBC@CTMS");
                                worksheet.PrinterSettings.Orientation = eOrientation.Landscape;
                                worksheet.PrinterSettings.PaperSize = ePaperSize.A4;

                                SaveFileDialog ss = new SaveFileDialog();

                                ss.Filter = "Excel Files(.xls)|*.xls| Excel Files(.xlsx)|*.xlsx| Excel Files(*.xlsm)|*.xlsm";
                                ss.FileName = BranchCmboBox.Text + "'s " + AlertNameCmboBox.Text + "'s " + "All Alert" + _ReportName + " " + DateTime.Today.ToString("dd-MM-yyyy") + ".xlsx";
                                DialogResult result = ss.ShowDialog();
                                string strFileName = ss.FileName;
                                if (result == DialogResult.OK)
                                {
                                    FileInfo finfo = new FileInfo(ss.FileName); 
                                    package.SaveAs(finfo); // if the current filr is open means it shows the error How to avoid that & How to close the Current Excel file 
                                    MessageBox.Show("Excel Sheet exported!", "CTMS - Vehicle Alert Reprt", MessageBoxButtons.OK, MessageBoxIcon.Information);
                                }
                                package.Dispose();
                            }
  • 3 Comments sorted by
  • Vote Up0Vote Down atirnaveedatirnaveed
    17.50 Karma Accepted Answer
    var saveFileDialog1 = new SaveFileDialog();
    saveFileDialog1.Filter = "Excel File (*.xlsx)|*.xlsx";
    saveFileDialog1.FilterIndex = 1;
    if (saveFileDialog1.ShowDialog() == DialogResult.OK)
    {
    try
    {
    FileInfo file = new FileInfo(saveFileDialog1.FileName);
    if (file.Exists)
    {
    file.Delete();
    }

    using (ExcelPackage pck = new ExcelPackage(file))
    {
    ExcelWorksheet ws = pck.Workbook.Worksheets.Add("Sheet1");
    ws.Cells["A1"].LoadFromDataTable(((DataTable)DataGridView1.DataSource), true);
    ws.Cells.AutoFitColumns();

    using (ExcelRange rng = ws.Cells[1, 1, 1, DataGridView1.Columns.Count])
    {
    rng.Style.Font.Bold = true;
    rng.Style.Fill.PatternType = ExcelFillStyle.Solid;
    rng.Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.FromArgb(79, 129, 189));
    rng.Style.Font.Color.SetColor(System.Drawing.Color.White);
    }

    pck.Save();
    }

    MessageBox.Show(string.Format("Excel file \"{0}\" generated successfully.", file.Name));
    }
    catch (Exception ex)
    {
    MessageBox.Show("Failed to export to Excel. Original error: " + ex.Message);
    }


    Hope this will help you
  • you are great @atirnaveed
    thank you so much
  • Your welcome buddy (y), Have a great day

Howdy, Stranger!

It looks like you're new here. If you want to get involved, or you want to Ask a new Question, Please Login or Create a new Account by Clicking below

Login with Facebook

Popular Posts of the Week

    Optimum Creative