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

There are a few things in your current posted code that will not compile, so I question your statement that you are able to export the DataGridView to a new excel file. The line: String mainPath = "@C:\Users\User1\Desktop\"" + cYear; is incorrect… the ‘@’ is out of place and an extra ’”’quote is at the end… should be String mainPath = @"C:\Users\User1\Desktop\" + cYear; Also the line: System.IO.Directory.Create(folderPath); is incorrect as Create DNE… should be: System.IO.Directory.CreateDirectory(mainPath); Lastly to help. — JohnG
– JohnG, Commented Jan 25, 2017 at 2:39
You may want to add the line: xlApp.Visible = true; while debugging the creation of the excel file. This will allow you to SEE what is happening in your code step by step and may reveal some problems you are having. Just a thought. — JohnG
– JohnG, Commented Jan 25, 2017 at 2:39
@JohnG thanks for the help, that's just a mock code that I created here since my main program is too big. There's no problem with my directory and I have done what u have said. It overwrites the excel file instead of appending it to the file. Do you have any suggestions on this? — DisplayName
– DisplayName, Commented Jan 25, 2017 at 2:48
@JohnG I have cleaned up the code in the question. thanks for pointing it out — DisplayName
– DisplayName, Commented Jan 25, 2017 at 2:52
Let me see if I got this right. Since your main program code is too big to post… then the part that you are having a problem with... instead of copy/pasting the code you re-typed it to create a “mock” version with errors? So the errors I pointed out earlier are not really in your code? If this is the case then why are you wasting others time? Post the code (not a “mock” version) that is not working. Otherwise as to your posted “mock” code the answer is… it has errors and it doesn’t work, since it is “mock” code it makes no sense for others to try and debug it or help you find your errors. — JohnG
– JohnG, Commented Jan 25, 2017 at 18:18

JohnG · Accepted Answer · 2017-01-25 21:00:31Z

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.

OMG. thank you so much, it works. thanks for putting up with me too :) last question sorry.. do you know how to make the "A filed name .... already exists in this location. Do you want to replace it?" from Excel not show up? thanks
If you do not want to see the overwrite dialog you can turn off DisplayAlerts in excel like: xlApp.DisplayAlerts = false;

Collectives™ on Stack Overflow

Appending data to existing Excel file using C#

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related