how to export new datagridview datas to excel

Question

I am getting data in datagridview with a SQL query. Then I'm exporting the data to excel. When I click the button I get the data to datagridview perfectly and I can export and save the first data to excel.

When I want to export to new data to excel I cannot do that on the data I have saved

For example; I saved 10 rows of data and then when I want to save 20 rows of data it should be 30 rows data on excel but I have 20 rows of data

How can I fix it?

Here is my code to export to excel:

private void bttn_Excel_Click(object sender, EventArgs e)
{
            Microsoft.Office.Interop.Excel._Application app = new Microsoft.Office.Interop.Excel.Application();

            Microsoft.Office.Interop.Excel._Workbook workbook = app.Workbooks.Add(Type.Missing);

            Microsoft.Office.Interop.Excel._Worksheet worksheet = null;

            app.Visible = true;
            worksheet = workbook.Sheets["Sayfa1"];
            worksheet = workbook.ActiveSheet;

            worksheet.Name = "Anlık Değerleri";


            // storing header part in Excel
            for (int i = 1; i < dataGridView1.Columns.Count + 1; i++)
            {
                worksheet.Cells[1, i] = dataGridView1.Columns[i - 1].HeaderText;
            }    

            // storing Each row and column value to excel sheet
            for (int i = 0; i < dataGridView1.Rows.Count - 1; i++)
            {
                for (int j = 0; j < dataGridView1.Columns.Count; j++)
                {
                    worksheet.Cells[i + 2, j + 1] = dataGridView1.Rows[i].Cells[j].Value.ToString();
                }
            }

            // save the application
            workbook.SaveAs("C:\\Users\\senerk\\Desktop\\Kitap1.xlsx", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing);

            // Exit from the application
            app.Quit();
 }

It sounds like you need to maintain a count or last row position and begin your loop at that number instead of 0 so that you do not overwrite what was done on the previous button click. — Crowcoder
– Crowcoder, Commented Aug 4, 2017 at 12:40
Use worksheet.UsedRange.Rows.Count for your last row position — Azar Shaikh
– Azar Shaikh, Commented Aug 4, 2017 at 12:41

saidfagan · Accepted Answer · 2017-08-04 13:58:26Z

1

As Azar Shaikh mentioned you should use worksheet.UsedRange.Rows.Count. Change your second loop to this, and it should work:

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

I didn't test it in working environment, but you got the idea! More info here.

answered Aug 4, 2017 at 13:58

saidfagan

8422 gold badges9 silver badges27 bronze badges

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

Collectives™ on Stack Overflow

how to export new datagridview datas to excel

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related