Code to: Loop through a datagrid and display its data in a new Excel spreadsheet

[Walaza]

Hi, Does anyone have code to loop through data displayed in a datagrid and display that data in a new Excel spreadsheet? Thanks in advance. :doh:

Mvelo Walaza Developer Telkom SA

[Thomas Stockwell]

Microsoft.Office.Interop.Excel._Application _appClass = new ApplicationClass(); _appClass.Visible = true; _appClass.Caption = "Excel custom title"; Microsoft.Office.Interop.Excel.Workbook wbk = _appClass.Workbooks.Add(XlWBATemplate.xlWBATWorksheet); Worksheet obj = (Worksheet)wbk.ActiveSheet; //Range r = (Range)obj.Cells[this.lsvDocumentList.Items.Count + 1, ((GridView)this.lsvDocumentList.View).Columns.Count + 1]; Range r = (Range)obj.Cells; for (int i = 0; i < ((GridView)this.lsvDocumentList.View).Columns.Count; i++) { r[1, i + 1] = ((GridView)this.lsvDocumentList.View).Columns[i].Header.ToString(); ((Range)r.Cells[1, i + 1]).Font.Bold = true; } The above code is using a XAML listview, so it may be slightly different with WinForms. You need to also make sure to reference the Excel dll.

Regards, Thomas Stockwell Programming today is a race between software engineers striving to build bigger and better idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning. Visit my homepage Oracle Studios[^]

[MumbleB]

I have this code that will export to a CSV file. Maybe it will help.

        //Write Header Record
        StreamWriter sw = new StreamWriter(@"C:\\EPORT\\ExportFile\_" + DateTime.Today.ToString("ddMMyyyy") + ".csv", false);
        DataTable dt = m\_dtMobEmail;
        int iColCount = dt.Columns.Count;
        for (int i = 0; i < iColCount; i++)
        {
            sw.Write(@"""{0}""", dt.Columns\[i\]);
            if (i < iColCount - 1)
            {
                sw.Write(",");
            }
        }
        sw.Write(sw.NewLine);

        //Write all records in the DB
        foreach (DataRow dr in dt.Rows)
        {
            //Search for all Records with value 0
            if (dr\["Exported"\].ToString() == "0")
            {
                for (int i = 0; i < iColCount; i++)
                {
                    if (!Convert.IsDBNull(dr\[i\]))
                    {
                        sw.Write(@"""{0}""", dr\[i\].ToString());
                    }
                    if (i < iColCount - 1)
                    {
                        sw.Write(",");
                    }
                }
                sw.Write(sw.NewLine);
            }
        }

        sw.Close();

[Walaza]

Hi, Thanks for the answer, but I tried to convert it and it works but strangely it only shows the headings of the datagrid. Can you tell me what causes this. Here is my code: Microsoft.Office.Interop.Excel._Application _appClass = new ApplicationClass(); _appClass.Visible = true; _appClass.Caption = "Excel custom title"; Microsoft.Office.Interop.Excel.Workbook wbk = _appClass.Workbooks.Add(XlWBATemplate.xlWBATWorksheet); Worksheet obj = (Worksheet)wbk.ActiveSheet; //Range r = (Range)obj.Cells[this.lsvDocumentList.Items.Count + 1, ((GridView)this.lsvDocumentList.View).Columns.Count + 1]; Range r = (Range)obj.Cells; for (int i = 0; i < ((DataGridView)this.dgOutput).Columns.Count; i++) { r[1, i + 1] = ((DataGridView)this.dgOutput).Columns[i].HeaderText.ToString(); ((Range)r.Cells[1, i + 1]).Font.Bold = true; }

Mvelo Walaza Developer Telkom SA

[Thomas Stockwell]

It only shows headers because that is the only code that I gave you. You need to add another loop after the for loop to work with the cells. The following code is what prints the headers to the excel document. Just try to duplicate the same results through trial-and-error. I would give you the cell output code, but my code does a whole bunch of extra stuff that would be to confusing to explain If you continue to have problems with trying to create your own code 'based' on what I gave you, then post back and I'll look more into it. for (int i = 0; i < ((DataGridView)this.dgOutput).Columns.Count; i++) { r[1, i + 1] = ((DataGridView)this.dgOutput).Columns[i].HeaderText.ToString(); ((Range)r.Cells[1, i + 1]).Font.Bold = true; }

Regards, Thomas Stockwell Programming today is a race between software engineers striving to build bigger and better idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning. Visit my homepage Oracle Studios[^]

[Walaza]

Hi, Thanks again for the reply. Did you mean something like this: for (int i = 0; i < ((DataGridView)this.dgOutput).Columns.Count; i++) { r[1, i + 1] = ((DataGridView)this.dgOutput).Columns[i].HeaderText.ToString(); ((Range)r.Cells[1, i + 1]).Font.Bold = true; for (int j = 0; j < ((DataGridView)this.dgOutput).Columns.Count; i++) { r[1, j + 1] = ((DataGridView)this.dgOutput).Columns[j].ToString(); } } The inner for loop does not print the cells and throws the ff exception: HRESULT: 0x800AC472 Regards,

Mvelo Walaza Developer Telkom SA

[Thomas Stockwell]

it looks like you have the second for loop inside the first, that is not what you want to do. Here is some pseudocode to assist you foreach column in the data grid that contains header information { r[1,colIndex]=column text } foreach row in the data grid that contains information to write to database { r[row+1,1]=first column of the specified row will write its data to the excel spreadsheet r[row+1,2]=second column of the specified row will write its data to the excel spreadsheet }//this will increment for every row in the data grid.

Regards, Thomas Stockwell Programming today is a race between software engineers striving to build bigger and better idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning. Visit my homepage Oracle Studios[^]

[Walaza]

Thanks a lot, this was very helpful.

Mvelo Walaza Developer Telkom SA