Jevon Jevon - 26 days ago 5
C# Question

append to excel file that is already created

I have this excel file that currently writes contents from my c# application to its cell contents:

private void button8_Click(object sender, EventArgs e)
{
Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();

if (xlApp == null)
{
MessageBox.Show("Excel is not properly installed!!");
return;
}


Excel.Workbook xlWorkBook;
Excel.Worksheet xlWorkSheet;
object misValue = System.Reflection.Missing.Value;

xlWorkBook = xlApp.Workbooks.Add(misValue);
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);

xlWorkSheet.Cells[1, 1] = comboBox2.Text;
xlWorkSheet.Cells[1, 2] = textBox5.Text;
xlWorkSheet.Cells[1, 3] = textBox2.Text;
xlWorkSheet.Cells[1, 4] = comboBox3.Text;
xlWorkSheet.Cells[1, 5] = textBox3.Text;
xlWorkSheet.Cells[1, 6] = comboBox1.Text;



xlWorkBook.SaveAs(@"cross_check.xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
xlWorkBook.Close(true, misValue, misValue);
xlApp.Quit();

Marshal.ReleaseComObject(xlWorkSheet);
Marshal.ReleaseComObject(xlWorkBook);
Marshal.ReleaseComObject(xlApp);

MessageBox.Show("Excel file created succcessfully");
}

}


How do I append to this same file which has already been created?To expand further, currently I have to specify the cells the values have to be added to. How do I like increment in some fashion as to say no matter how many times the user hits the add to file button it should just increment the previous pattern.
eg. I have :

xlWorkSheet.Cells[1, 1] = comboBox2.Text;
xlWorkSheet.Cells[1, 2] = textBox5.Text;
xlWorkSheet.Cells[1, 3] = textBox2.Text;
xlWorkSheet.Cells[1, 4] = comboBox3.Text;
xlWorkSheet.Cells[1, 5] = textBox3.Text;
xlWorkSheet.Cells[1, 6] = comboBox1.Text;


Upon clicking the button, how would I make it now follow this pattern:

xlWorkSheet.Cells[2, 1] = comboBox2.Text;
xlWorkSheet.Cells[2, 2] = textBox5.Text;
xlWorkSheet.Cells[2, 3] = textBox2.Text;
xlWorkSheet.Cells[2, 4] = comboBox3.Text;
xlWorkSheet.Cells[2, 5] = textBox3.Text;
xlWorkSheet.Cells[2, 6] = comboBox1.Text;

Answer

I suppose you use the Excel object through the Microsoft.Office.Interop.Excel reference. Then you have to modify your code as follows

     private void button8_Click(object sender, EventArgs e)
      {
        Microsoft.Office.Interop.Excel.Application xlApp; //Declare the 
                 //Excel object
           try
           {
             xlApp = (Microsoft.Office.Interop.Excel.Application)System.Runtime.InteropServices.Marshal.GetActiveObject("Excel.Application");
           }
           catch (Exception ee)
           {
                xlApp = new Microsoft.Office.Interop.Excel.Application();


              if (xlApp == null)
              {
                MessageBox.Show("Excel is not properly installed!!");
                return;
              }


            }

        if (xlApp == null)
        {
            MessageBox.Show("Excel is not properly installed!!");
            return;
        }



        object misValue = System.Reflection.Missing.Value;


      Microsoft.Office.Interop.Excel.Workbook xlWorkBook=xlApp.Workbooks.Add(misValue);

    try
    {

      xlWorkBook = xlApp.Workbooks.Open(@"cross_check.xls");//,


    }
   catch (Exception ex)
   {

     ;//     
   }
       Microsoft.Office.Interop.Excel.Range range;

       Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet = 
       (Microsoft.Office.Interop.Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
       int rownum = 1;
       int MAX_ROWS=30000; //You may define your own limit
       bool written = true;
       range = xlWorkSheet.UsedRange;
       while ((written) && (rownum<MAX_ROWS))
       {
         var test = (range.Cells[rownum, 1] as 
        Microsoft.Office.Interop.Excel.Range).Value2;
         if (test != null)
          {
             rownum++;
          }
         else
          {
            written = false;
          }
        }
       if (written == false)
       {
         xlWorkSheet.Cells[rownum, 1] = comboBox2.Text;
         xlWorkSheet.Cells[rownum, 2] = textBox5.Text;
         xlWorkSheet.Cells[rownum, 3] = textBox2.Text;
         xlWorkSheet.Cells[rownum, 4] = comboBox3.Text;
         xlWorkSheet.Cells[rownum, 5] = textBox3.Text;
         xlWorkSheet.Cells[rownum, 6] = comboBox1.Text;
       }
        xlApp.DisplayAlerts = false; //Disables the prompts
        xlWorkBook.SaveAs(@"cross_check.xls", Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlShared, misValue, misValue, misValue, misValue, misValue);
        xlApp.DisplayAlerts = true; //

        xlWorkBook.Close(true, misValue, misValue);
        xlApp.Quit();

        Marshal.ReleaseComObject(xlWorkSheet);
        Marshal.ReleaseComObject(xlWorkBook);
        Marshal.ReleaseComObject(xlApp);

        MessageBox.Show("Excel file created/updated succcessfully");

   }

At the first steps of the code a check for the Excel object, if it is already running, is performed. If so, we do not create a new Excel object but we use the one that is running in the system. Then the workbook is properly created or updated. When saving it, it must be saved with

     Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlShared

in order to be able to reopen it and update it.

To speed up the data entering a modified code you may use is quoted.

You should add an additional button e.g. button9 and use the quoted code in the click event with the necessary modifications made for the button8 where you enter data. Also, you have to declare the variables xlApp,xlWorkBook,xlWorkSheet etc to be global and PUBLIC as in the following code,

    ............
    public bool startd = false;
    public int rownum;
    public int MAX_ROWS = 30000;//You may define your own limit here
    public bool isFirstTime = true;
    public string oldBtnFileText;
    public string oldBtnDataText;
    public Microsoft.Office.Interop.Excel.Application xlApp;
    public Microsoft.Office.Interop.Excel.Workbook xlWorkBook;
    public Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet;
    public Microsoft.Office.Interop.Excel.Range range;

    public object misValue;


    private void button8_Click(object sender, EventArgs e)
    {
        if (startd == false)
        {

            return;
        }
        if (isFirstTime == true)
        {
            bool written = true;
            int rnum = 1;
            if (xlWorkSheet!=null)
            {
                range=xlWorkSheet.UsedRange;
            while ((written) && (rnum < MAX_ROWS))
            {
                var test = (range.Cells[rnum, 1] as Microsoft.Office.Interop.Excel.Range).Value2;
                if (test != null)
                {
                    rnum++;
                }
                else
                {
                    written = false;
                }
            }
            if (written == false)
            {
                rownum = rnum;
                isFirstTime = false;
            }
            else
            {
                MessageBox.Show("The current WorkSheet is Full");
                return;
            }
        }
        }
        if (xlWorkSheet!=null)
        {
            xlWorkSheet.Cells[rownum, 1] =  comboBox2.Text;
            xlWorkSheet.Cells[rownum, 2] =  textBox5.Text;
            xlWorkSheet.Cells[rownum, 3] =  textBox2.Text;
            xlWorkSheet.Cells[rownum, 4] =  comboBox3.Text;
            xlWorkSheet.Cells[rownum, 5] =  textBox3.Text;
            xlWorkSheet.Cells[rownum, 6] =  comboBox1.Text;
            rownum++;
        }

    }
    private void button9_Click(object sender, EventArgs e)
    {
        if (startd == false)
        {
            try
            {
                xlApp = (Microsoft.Office.Interop.Excel.Application)System.Runtime.InteropServices.Marshal.GetActiveObject("Excel.Application");
            }
            catch (Exception ee)
            {
                xlApp = new Microsoft.Office.Interop.Excel.Application();
                if (xlApp == null)
                {
                    MessageBox.Show("Excel is not properly installed!!");
                    return;
                }
            }
            if (xlApp == null)
            {
                MessageBox.Show("Excel is not properly installed!!");
                return;
            }
            misValue = System.Reflection.Missing.Value;
            xlWorkBook = xlApp.Workbooks.Add(misValue);
            try
            {
                xlWorkBook = xlApp.Workbooks.Open(@"cross_check.xls");//,
            }
            catch (Exception ex)
            {
                ;//     
            }
            xlWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
            oldBtnFileText = button9.Text.ToString();
            button9.Text = "File Ready to accept data";
            oldBtnDataText = button1.Text.ToString();
            button8.Text = "Enter Data";
            startd = true;
        }
        else
        {
            xlApp.DisplayAlerts = false;
            xlWorkBook.SaveAs(@"cross_check.xls", Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlShared, misValue, misValue, misValue, misValue, misValue);
            xlApp.DisplayAlerts = true;
            xlWorkBook.Close(true, misValue, misValue);
            xlApp.Quit();

            Marshal.ReleaseComObject(xlWorkSheet);
            Marshal.ReleaseComObject(xlWorkBook);
            Marshal.ReleaseComObject(xlApp);

            MessageBox.Show("Excel file created/updated succcessfully");

            startd = false;
            button9.Text = oldBtnFileText; //Restore the initial captions
            button8.Text = oldBtnDataText;//...
        }

    }
//

Hope these can be useful.