Databases
StackOverflow2013
Postsdbo
POExporting Data From Multiple DataGridViews on a Form to Multiple Excel Sheets in a Single Excel File
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POExporting Data From Multiple DataGridViews on a Form to Multiple Excel Sheets in a Single Excel File
    text
    copied!<p>I have a Database Application in which it has multiple <code>dataGridViews</code> on the <code>Form</code>. I am able to successfully export a single grid view to an Excel sheet but I would I would like to export the grid to different Excel sheets in a single Excel file. </p> <p>I tried the following which didn't work. </p> <pre><code>public void ExportGridViewToExcel(DataGridView dataGridViewCommission, DataGridView dataGridViewPaymentsReceived, string commissionsheet, string paymentsheet) { // creating Excel Application Microsoft.Office.Interop.Excel._Application app = new Microsoft.Office.Interop.Excel.Application(); // creating new WorkBook within Excel application Microsoft.Office.Interop.Excel._Workbook workbook = app.Workbooks.Add(Type.Missing); // creating new Excelsheet in workbook Microsoft.Office.Interop.Excel._Worksheet worksheet1 = null; Microsoft.Office.Interop.Excel._Worksheet worksheet2 = null; // see the excel sheet behind the program // app.Visible = true; // get the reference of first sheet. By default its name is Sheet1. // store its reference to worksheet worksheet1 = workbook.Sheets[1]; // changing the name of active sheet worksheet1.Name = commissionsheet; string filelocation; SaveFileDialog SaveFile = new SaveFileDialog(); if (SaveFile.ShowDialog() == DialogResult.OK) { filelocation = SaveFile.FileName; // storing header part in Excel for (int i = 1; i &lt; dataGridViewCommission.Columns.Count + 1; i++) { worksheet1.Cells[1, i + 1] = dataGridViewCommission.Columns[i - 1].HeaderCell.Value; } for (int i = 1; i &lt; dataGridViewCommission.Rows.Count + 1; i++) { worksheet1.Cells[i + 1, 1] = dataGridViewCommission.Rows[i - 1].HeaderCell.Value; //worksheet2.Cells[i + 1, 1] = dataGridViewPaymentsReceived.Rows[i - 1].HeaderCell.Value; } for (int i = 0; i &lt; dataGridViewCommission.Rows.Count; i++) { for (int j = 0; j &lt; dataGridViewCommission.Columns.Count; j++) { worksheet1.Cells[i + 2, j + 2] = dataGridViewCommission.Rows[i].Cells[j].Value.ToString(); } } worksheet2 = workbook.Sheets["Sheet2"]; worksheet2.Name = paymentsheet; for (int i = 1; i &lt; dataGridViewPaymentsReceived.Columns.Count + 1; i++) { worksheet2.Cells[1, i + 1] = dataGridViewPaymentsReceived.Columns[i - 1].HeaderCell.Value; } // storing Each row and column value to excel sheet for (int i = 0; i &lt; dataGridViewPaymentsReceived.Rows.Count; i++) { for (int j = 0; j &lt; dataGridViewPaymentsReceived.Columns.Count; j++) { worksheet2.Cells[i + 2, j + 2] = dataGridViewPaymentsReceived.Rows[i].Cells[j].Value.ToString(); } } // save the application workbook.SaveAs(filelocation, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing); workbook.Close(true, Type.Missing, Type.Missing); app.Quit(); Process.Start(filelocation + ".xls"); } } </code></pre> <p>I encountered a <code>COMexception</code>, Invalid Index. (Exception from HRESULT: 0x8002000B (DISP_E_BADINDEX))</p> <p>Can anyone suggest me a way to get away with this?</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload