How to append datagridview to excel file using c#

Question

I am trying to export my datagridview contents to a excel file which exist in application.startuppath. The problem I am facing is that whenever I export my grid content to excel, it overwrites the previous content. What I would like to do is append to my excel file and not overwrite my existing content.

Here is my code:

Microsoft.Office.Interop.Excel._Application app = new Microsoft.Office.Interop.Excel.Application();     
Microsoft.Office.Interop.Excel._Workbook workbook = app.Workbooks.Open(Application.StartupPath+"excelfile.xls");
Microsoft.Office.Interop.Excel._Worksheet worksheet = null;                
app.Visible = false;
worksheet = workbook.Sheets["Sheet1"];
worksheet = workbook.ActiveSheet;
for (int i = 0; i < dataGridView1.Rows.Count - 1; i++)
 {
   for (int j = 0; j < dataGridView1.Columns.Count; j++)
    {
     if (dataGridView1.Rows[i].Cells[j].Value != null)
      worksheet.Cells[i + 2, j + 1] = dataGridView1.Rows[i].Cells[j].Value.ToString();
     else
      worksheet.Cells[i + 2, j + 1] = "";
    }
 }
workbook.Close(SaveChanges: true);
app.Quit();

EDIT: Updated code : thanks to @Alex Bell

int _offset = worksheet.UsedRange.Rows.Count;
for (int i = 0; i < dataGridView1.Rows.Count - 1; i++)
 {
  for (int j = 1; j < dataGridView1.Columns.Count; j++)
   {
    if (dataGridView1.Rows[i].Cells[j].Value != null)
      worksheet.Cells[i + 2 + _offset, j + 1] = dataGridView1.Rows[i].Cells[j].Value.ToString();
    else
      worksheet.Cells[i + 2 + _offset, j + 1] = "";
   }
 }

Resolved my problem of appending data but another small problem is every time I update it leaves a blank row between old rows and new rows.

Possible duplicate of http://stackoverflow.com/questions/16722339/writing-to-an-existing-excel-file-using-c-sharp — bit, Aug 19 '14 at 02:43

Alexander Bell · Accepted Answer · 2014-08-19T02:49:42.500

0

Pertinent to your case, you may use int _offset = worksheet.UsedRange.Rows.Count as row offset in your loop:

for (int i = 0; i < dataGridView1.Rows.Count - 1; i++) { for (int j = 0; j < dataGridView1.Columns.Count; j++) { if (dataGridView1.Rows[i].Cells[j].Value != null) worksheet.Cells[i + 2 + _offset, j + 1] = dataGridView1.Rows[i].Cells[j].Value.ToString(); else worksheet.Cells[i + 2 + _offset, j + 1] = ""; } }

Hope this will help. Regards,

edited Aug 19 '14 at 02:49

answered Aug 19 '14 at 02:44

Alexander Bell

7,842
3
26
42

your code helped me. Thanks. But one small problem. every time I append data to file it leaves 1 blank row between prev rows and new rows. any idea how to fix that? – Malav Shah Aug 19 '14 at 03:19
I am glad the code is helpful, and would appreciate if you mark the question answered. Please accept the answer, and if you have more questions, please put them in a separate post. Thanks and regards, – Alexander Bell Aug 19 '14 at 12:00

How to append datagridview to excel file using c#

1 Answers1