0

I've a code that read data from excel using Interop.Excel. The problem is when the second time I'm calling the write method it seems like it's being saved in another copy and just the first call actually write data to the original excel file. It looks like the interop object won't clean up properly.

This is the Read/Write methods

public class ExcelHelper
{
    Microsoft.Office.Interop.Excel.Application excelApp;
    static AppSettingsReader settingsReader = new AppSettingsReader();


    public string[,] getExcelInfo(string excelFileName)
    {

        string[,] dataArray;

        string logfileName = settingsReader.GetValue("LogFileName", typeof(string)).ToString();
        string logDirectory = settingsReader.GetValue("LogDirectory", typeof(string)).ToString();
        string logLevel = settingsReader.GetValue("LogLevel", typeof(string)).ToString();

        Logger.Log(logfileName, logDirectory, logLevel, "Getting File " + excelFileName);

        Workbook wb = excelApp.Workbooks.Open(excelFileName,null,true);
        Microsoft.Office.Interop.Excel._Worksheet xlWorksheet = (Microsoft.Office.Interop.Excel._Worksheet)wb.Sheets[1];

        Worksheet ws = wb.Worksheets[1];

        Range range = ws.UsedRange;

       dataArray = new string[range.Rows.Count, range.Columns.Count];

        string str = string.Empty;
        int rCnt = 0;
        int cCnt = 0;

        for (rCnt = 1; rCnt <= range.Rows.Count; rCnt++)
        {
            for (cCnt = 1; cCnt <= range.Columns.Count; cCnt++)
            {



                dataArray[rCnt - 1, cCnt - 1] = System.Convert.ToString((range.Cells[rCnt, cCnt] as Range).Value2);




            }
        }

        wb.Close();


        releaseObject(range);
        releaseObject(ws);
        releaseObject(wb);



       return dataArray;


    }


    public static int WritePRSIdToExcel(int PRSInt, int line)
    {


      string excelApp = ConfigurationManager.AppSettings["excelFileName"];


      //  Application excel = (Application)Marshal.GetActiveObject("Excel.Application");
        _Application docExcel = (Application)Marshal.GetActiveObject("Excel.Application");
        docExcel.Visible = false;
        docExcel.DisplayAlerts = false;

        _Workbook workbooksExcel = docExcel.Workbooks.Open(excelApp, null, true);
        _Worksheet worksheetExcel = (_Worksheet)workbooksExcel.ActiveSheet;


        Range range = worksheetExcel.UsedRange;


        string str;

        str = (string)(range.Cells[line, 3] as Microsoft.Office.Interop.Excel.Range).Value2;



        if (!(str == null))
        {

            ((Range)worksheetExcel.Cells[line + 1, 5]).Value2 = PRSInt;

            workbooksExcel.Save();
            GC.Collect();   
            GC.WaitForPendingFinalizers();
            workbooksExcel.Close(false, Type.Missing, Type.Missing);
            docExcel.Quit();
            docExcel.Application.DisplayAlerts = true;
            Marshal.ReleaseComObject(workbooksExcel);
            Marshal.ReleaseComObject(docExcel);
            Marshal.ReleaseComObject(worksheetExcel);
            Marshal.ReleaseComObject(range);

        }



        return PRSInt;


    }

This is the method to read the data that was written to to the cell:

public class GetIDNumber
{
    static AppSettingsReader settingsReader = new AppSettingsReader();



    static void Main(string[] args)
    {
        ExcelHelper excelHelper = new ExcelHelper();



        /// <summary>
        /// Log settings
        /// </summary>
        string logfileName = settingsReader.GetValue("LogFileName", typeof(string)).ToString();
        string logDirectory = settingsReader.GetValue("LogDirectory", typeof(string)).ToString();
        string logLevel = settingsReader.GetValue("LogLevel", typeof(string)).ToString();

        string excelFileName = settingsReader.GetValue("ExcelFileName", typeof(string)).ToString();
        string TeamProjectName = settingsReader.GetValue("TeamProject", typeof(string)).ToString();

        excelHelper.InitExcel();

        tfsHelper.Connect();

        string[,] dataArray = excelHelper.getExcelInfo(excelFileName);



        //handle data


        const int START_ROW = 1;
        const int PRODUCT_REQUIRMENT_TITLE_COL = 2;
        const int REQ_DESC = 3;
        const int WORKITEM_ID_COL = 4;
        const int REQUIRMENT_ACV_ID_COL = 5;
        const int RISK_COL = 6;

        int TFSId = 0;     
        int PRSId = 0;



        while (line < dataArray.GetLength(0))
        {

            line++;



                  if (!string.IsNullOrEmpty(dataArray[line, REQ_DESC]))
                  {

                                    string TfsId = string.Empty;

                                     TfsId = ((dataArray[line, 4]));

                  } 

          }






enter code here
2
  • Not sure I understand your issue. Is it that the Excel process remains running in the background? Commented Oct 15, 2013 at 18:02
  • The issue is only the time I'm calling the method the value (number) is being saved. the code ran without any exception the other time and I assume that it's being saved in another Excel process. Commented Oct 15, 2013 at 19:31

1 Answer 1

1

Have you stepped through debugging to make sure you're str is not equal to null on the second run? If that's the case the workbook won't be saved and the process won't be ended.

You should probably do your Excel clean-up in a "finally" statement instead of in an "if".

Sign up to request clarification or add additional context in comments.

3 Comments

Yeah. non of the values are void. I do however receive a void exception when I try to read the value from the file: string Id = string.Empty; Id = ((dataArray[line, 4])); and it won't write to the excel the second time the method is being called :
@OrenAshkenazy When are you getting the exception? I don't see an Id or dataArray in your posted code.
I've added the rest of the code. I get the exception on the second method when trying to get the cell data I was trying to add with WritePRSIdToExcel.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.