Appending data to existing Excel file using C#

Question

I'm fairly new with C# and I am trying to export some data from a DataGridView in C# into an Excel file. The inputs from the datagridview are filled in by the user.

Currently, my program can create an excel file along with the values from the datagridview with the given date as its file name.

My problem is I can't seem to find a way to append the data from the gridview IF the excel file already exists, it overwrites the current excel file instead.

Any help/tips/suggestion is highly appreciated.

Thanks :)

Here is my code:

Microsoft.Office.Interop.Excel.Application xlApp;          
Microsoft.Office.Interop.Excel.Workbook xlWorkBook; 
Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet;
Microsoft.Office.Interop.Excel.Sheets xlBigSheet;
Microsoft.Office.Interop.Excel.Sheets xlSheet;
object misValue;
String newPath;

private void buttonOK_Click(object sender, EventArgs e)
{
    createXLSfile();
}

private void createXLSfile(){
    String cDate = datePicker.Value.ToShortDateString();
    String cMonth = datePicker.Value.ToString("MMMM");
    String cYear = datePicker.Value.ToString("yy");
    String cDay = datePicker.Value.ToString("dd");

    String fName = cDay + "-" + cMonth+ "-" + cYear + ".xls";

    String mainPath = @"C:\Users\User1\Desktop\" + cYear;
    String folderPath = System.IO.Path.Combine(mainPath, cMonth);
    String excelPath = System.IO.Path.Combine(folderPath, fName);

    System.IO.Directory.CreateDirectory(mainPath);
    System.IO.Directory.CreateDirectory(folderPath);

    String fNameOnly = Path.GetFileNameWithoutExtension(excelPath);
    String extension = Path.GetExtension(excelPath);
    String path = Path.GetDirectoryName(excelPath);
    newPath = excelPath;

    if(File.Exists(newPath))
    {
        existingFile();
    }else
    {
        newFile();
    }
    MessageBox.Show("Submitted");
}

private void newFile()
{
    xlApp = new Microsoft.Office.Interop.Excel.Application();
    xlApp.Visible = true;
    misValue = System.Reflection.Missing.Value;
    xlWorkBook = xlApp.Workbooks.Add(misValue);
    xlWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);

    xlWorkSheet = xlWorkBook.Sheets["Sheet1"];
    xlWorkSheet = xlWorkBook.ActiveSheet;
    xlWorkSheet.Name = "Sheet1";

    xlWorkSheet.Cells[2, 1] = "Header1";
    xlWorkSheet.Cells[2, 2] = "Header2";
    xlWorkSheet.Cells[2, 3] = "Total";
    getData();

    xlWorkBook.SaveAs(newFullPath, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, misValue,
    misValue, misValue, misValue, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
    xlWorkBook.Close(true, misValue, misValue);
    xlApp.Quit();
    Marshal.ReleaseComObject(xlWorkSheet);
    Marshal.ReleaseComObject(xlWorkBook);
    Marshal.ReleaseComObject(xlApp);
}

private void existingFile()
{
    xlApp = new Microsoft.Office.Interop.Excel.Application();
    xlApp.Visible = true;
    xlWorkBook = xlApp.Workbooks.Open(newPath, 0, 
                false, 5, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows,
                 "", true, false, 0, true, false, false);

    xlBigSheet = xlWorkBook.Worksheets;
    string x = "Sheet1";
    xlWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlBigSheet.get_Item(x);

    getData();

    xlWorkBook.SaveAs(newPath, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal,
            misValue, misValue, misValue, misValue, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive,
            misValue, misValue, misValue,
            misValue, misValue);

    xlWorkBook.Close(misValue, misValue, misValue);
    xlWorkBook = null;
    xlApp.Quit();
    GC.WaitForPendingFinalizers();
    GC.Collect();
    GC.WaitForPendingFinalizers();
    GC.Collect();
}

private void getData()
{
    double a,b,c,d,total = 0;
    int lastRow_ = 3;

    foreach(DataGridViewRow r in dataGridView1.Rows)
    {
        if(!r.IsNewRow)
        {
            a = Convert.ToDouble(r.Cells[2].Value);
            b = Convert.ToDouble(r.Cells[3].Value);
            c = Convert.ToDouble(r.Cells[4].Value);
            d = Convert.ToDouble(r.Cells[5].Value);

            total = a + b + c + d;

            xlWorkSheet.Cells[lastRow_, 1] = "Hi";
            xlWorkSheet.Cells[lastRow_, 2] = "Hello";
            xlWorkSheet.Cells[lastRow_, 3] = Convert.ToString(total);
            lastRow_ = xlWorkSheet.Cells.Find(
                        "*",
                        xlWorkSheet.Cells[1, 1],
                        misValue,
                        Microsoft.Office.Interop.Excel.XlLookAt.xlPart,
                        Microsoft.Office.Interop.Excel.XlSearchOrder.xlByRows,
                        Microsoft.Office.Interop.Excel.XlSearchDirection.xlPrevious,
                        misValue,
                        misValue,
                        misValue).Row + 1;
        }
    }
    total = 0;
}

Update 1: Still stuck. Been trying to follow this link: https://www.codeproject.com/articles/5123/opening-and-navigating-excel-with-c

OUTPUT

Directory of outputted excel file

This is what's inside the outputted excel file

Answer 1

When you need to append data to an existing worksheet, you need to find out where the last used row is and start adding data after this row. Your current code to get this “last” row is awkward as once you start adding rows you keep checking for this “last” row which is unnecessary. The getData() method is simply adding data to a new excel file where the last row won’t matter. If the file exists, then you simply need to get the last used row and start importing the data on the next row. I am guessing it may be better as your code goes, to send over a starting row index for the GetData(RowToStart) method and simply increment the lastRow_ variable, like below: There is no need to keep checking for this last row.

private void getData(int lastRow_) {
  double a, b, c, d, total = 0;
  //int lastRow_ = 4;

  foreach (DataGridViewRow r in dataGridView1.Rows) {
    //if (!row.IsNewRow) {
    if (!r.IsNewRow) {
        a = Convert.ToDouble(r.Cells[2].Value);
      b = Convert.ToDouble(r.Cells[3].Value);
      c = Convert.ToDouble(r.Cells[4].Value);
      d = Convert.ToDouble(r.Cells[5].Value);

      total = a + b + c + d;

      xlWorkSheet.Cells[lastRow_, 1] = "Hi";
      xlWorkSheet.Cells[lastRow_, 2] = "Hello";
      xlWorkSheet.Cells[lastRow_, 3] = Convert.ToString(total);
      lastRow_++;
      //lastRow_ = xlWorkSheet.Cells.Find(
      //            "*",
      //            xlWorkSheet.Cells[1, 1],
      //            misValue,
      //            Microsoft.Office.Interop.Excel.XlLookAt.xlPart,
      //            Microsoft.Office.Interop.Excel.XlSearchOrder.xlByRows,
      //            Microsoft.Office.Interop.Excel.XlSearchDirection.xlPrevious,
      //            misValue,
      //            misValue,
      //            misValue).Row + 1;
    }
  }
  total = 0;
}

If the file is new, you would call this method like below.

 .
 .
 .
  xlWorkSheet.Cells[3, 1] = "Header1";
  xlWorkSheet.Cells[3, 2] = "Header2";
  xlWorkSheet.Cells[3, 3] = "Total";
  getData(4);
 .
 .
 .

If the file already exists and you need to append the data to existing worksheet you need to get the last used row then start on the next row. You can call getData(RowToStart) like below.

 .
 .
 .

 xlWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlBigSheet.get_Item("Sheet1");
 Microsoft.Office.Interop.Excel.Range last = xlWorkSheet.Cells.SpecialCells(Microsoft.Office.Interop.Excel.XlCellType.xlCellTypeLastCell, Type.Missing);
 int lastUsedRow = last.Row;
 getData(lastUsedRow + 1);
 .
 .
 .

I hope this makes sense.