locked
Unable to Generate the Excel RRS feed

  • General discussion

  • Hi All , I am using the below code to generate the Excel Sheet.

    The requirement is to generate the Excel Sheet with the data from the Another Sheet Columns. So i've prepared the template of that Excel Report and Appending the data to the template Excel and using the below code.

         private void GetDataFromFile(string file)
            {
                excel.Application excelApp = new excel.Application();

                excel.Worksheet xlworksheet;

                FileStream stream = null;

                string strExcelTemplate = ConfigurationManager.AppSettings["ExcelTemplate"].ToString();

                try
                {
                    using (StreamWriter writer = new StreamWriter(strExcelTemplate, true))
                    {     

              Microsoft.Office.Interop.Excel.Workbook theWorkbook = excel.Workbooks.Open(file);
                        Microsoft.Office.Interop.Excel.Worksheet sheet1 = theWorkbook.Sheets["Project Info"]; // Project Info Sheet
                        Microsoft.Office.Interop.Excel.Range project_date = sheet1.Range["B1"];
                        Microsoft.Office.Interop.Excel.Range turnkey_ctrl = sheet1.Range["B2"];
                        Microsoft.Office.Interop.Excel.Range current_status = sheet1.Range["B8"]; // Current Status
                        Microsoft.Office.Interop.Excel.Range account_executive = sheet1.Range["I4"]; // Account Executive
                        Microsoft.Office.Interop.Excel.Range account_name = sheet1.Range["H7"]; // Contract/National Account Name
                        Microsoft.Office.Interop.Excel.Range enduser_pjtname = sheet1.Range["B4"]; // End-user Project Name/Area being installed

                        string tk_agent = Convert.ToString(turnkey_ctrl.Value.Substring(0, 2)); // TK Agent Initials that opened the project
                        string mohawk_ctrl = Convert.ToString(turnkey_ctrl.Value.Substring(2)); // Mohawk Control

                        DateTime pjt_date = Convert.ToDateTime(project_date.Value);
                        string project_opendate = pjt_date.ToString("dd/M/yyyy");  // Project Opened Date


                        excel.Workbook excelWorkbook = excelApp.Workbooks.Open(strExcelTemplate, 0, false, 5, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "",
                                          true, false, 0, true, false, false);

                        excelWorkbook.Close(false, Missing.Value, Missing.Value);

                        string currentSheet = "LogBookData";

                        xlworksheet = (excel.Worksheet)excelWorkbook.Worksheets.get_Item(currentSheet);

                        int row = 2;

                        xlworksheet.Cells[row, 1] = tk_agent;

                        xlworksheet.Cells[row, 2] = mohawk_ctrl;

                        xlworksheet.Cells[row, 3] = project_opendate;

                        xlworksheet.Cells[row, 4] = account_executive.Value;

                        xlworksheet.Cells[row, 5] = current_status.Value;

                        xlworksheet.Cells[row, 6] = "Turnkey";

                        xlworksheet.Cells[row, 7] = account_name.Value;

                        xlworksheet.Cells[row, 8] = "Probability";

                        xlworksheet.Cells[row, 9] = enduser_pjtname.Value;

                        xlworksheet.Cells[row, 10] = "Category";

                        // excelWorkbook.Save();

                        // Apply some predefined styles for data to look nicely :)
                        xlworksheet.Range["A2"].AutoFormat(Microsoft.Office.Interop.Excel.XlRangeAutoFormat.xlRangeAutoFormatClassic1);

                        string dfilename = "LogBookData_" + DateTime.Now.ToString("dd-MM-yyyy-HH-mm") + ".xls";

                        // Define filename
                        string fileName = string.Format(dfilename, Environment.GetFolderPath(Environment.SpecialFolder.DesktopDirectory));
                        // string fileName = string.Format(@"{0}\ExcelData" + "_" + DateTime.Now + ".xlsx", Environment.GetFolderPath(Environment.SpecialFolder.DesktopDirectory));

                        excelApp.DisplayAlerts = false;

                        xlworksheet.Name = "LogBookData";

                        // Save this data as a file
                        xlworksheet.SaveAs(fileName);

                        // Display SUCCESS message
                        MessageBox.Show(string.Format("The Report '{0}' Completed Successfully. Please Save this file and close.!", fileName));

    }

    }

                          

     catch (Exception ex)
                {
                    LogError(ex);
                }
                finally
                {
                    // Quit Excel application
                    excel.Quit();

                    // Release COM objects (very important!)
                    if (excel != null)
                        System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);

                  //  if (xlworksheet != null)
                      //  System.Runtime.InteropServices.Marshal.ReleaseComObject(xlworksheet);

                    // Empty variables
                    excel = null;
                    xlworksheet = null;

                    // Force garbage collector cleaning
                    GC.Collect();

                }



            }

    But i am getting the "LogBookData_Template is being modified by another user. Open as read-only " every time, when i tried to run this code.

    Kindly Suggest.

    Thanks in Advance.

    Regards,

    Sureshbabu.


    Thursday, July 21, 2016 6:13 AM

All replies

  • On which line of code does the exception (error) occur?

    Paul ~~~~ Microsoft MVP (Visual Basic)

    Thursday, July 21, 2016 1:48 PM