Export two DataGridViews to an Excel file

Question

Export two DataGridViews to an Excel file

Navigation

#1 by (3 votes)

1

I have to add the information of two DataGridViews in the same sheet of Excel consecutively. This is the code I have that only serves for a single DataGridView.

Microsoft.Office.Interop.Excel.Application 
    app = new Microsoft.Office.Interop.Excel.Application();
app.Visible = true;
Microsoft.Office.Interop.Excel.Workbook wb = app.Workbooks.Add(1);
Microsoft.Office.Interop.Excel.Worksheet 
    ws = (Microsoft.Office.Interop.Excel.Worksheet)wb.Worksheets[1];

ws.Name = "Exported from gridview";

ws.Rows.HorizontalAlignment = HorizontalAlignment.Center;

for (int i = 1; i < dataGridView1.Columns.Count + 1; i++)
{
    ws.Cells[1, i] = dataGridView1.Columns[i - 1].HeaderText;
}

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

// sizing the columns
ws.Cells.EntireColumn.AutoFit();


wb.SaveAs("c:\output.xls", Type.Missing, Type.Missing, Type.Missing, Type.Missing, 
    Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, 
    Type.Missing, Type.Missing, Type.Missing, Type.Missing);

app.Quit();

c# windows-forms interop

asked by oscar ramirez 10.03.2017 в 22:10

source

1 answer

How to export to Excel along with the headings? Error in Android Studio

score 3 · Accepted Answer

The second begins where the first one ends

for (int i = dataGridView1.Rows.Count  ; i < dataGridView2.Rows.Count - 1 +dataGridView1.Rows.Count ; i++)

Exactly as you did with the first one

example:

for (int i = 1; i < dataGridView1.Columns.Count + 1; i++)
{
    ws.Cells[1, i] = dataGridView1.Columns[i - 1].HeaderText;
}

for (int i = 0; i < dataGridView1.Rows.Count - 1; i++)
{
    for (int j = 0; j < dataGridView1.Columns.Count; j++)
    {
        ws.Cells[i + 2, j + 1] = dataGridView1.Rows[i].Cells[j].Value.ToString();
    }
}
int crow = dataGridView1.Rows.Count //empezamos donde termino
//crow es la fila donde termino el dgv1 empezamos ahi.
for (int i = 1; i < dataGridView2.Columns.Count + 1; i++)
{
    ws.Cells[crow, i] = dataGridView2.Columns[i - 1].HeaderText;
}
//empezamos desde crow + 1 y nos movemos dgv2.rows.count filas
for (int i = crow + 1; i < dataGridView2.Rows.Count + crow - 1; i++)
{
    for (int j = 0; j < dataGridView1.Columns.Count; j++)
    {
        ws.Cells[i + 2, j + 1] = dataGridView2.Rows[i].Cells[j].Value.ToString();
    }
}

As you will see the idea is to move crow places and do the same as we did with dgv1, keep in mind that it is not tested so there may be some damage with the indices and others. but that's the idea.