Exporting Datagrid to Excel

[SonicBoomAu]

Hi All,

 

Don't know if this is meant to be here or under the database heading, but anyway here goes.

 

I am trying to gather some information about exporting data contained in a datagrid to an excel spreadsheet. I am not using ASP. The number of rows and columns are not set(fixed).

 

If someone was able to point me in the right direction or even better a quick example. I would be greatly appreciative. I have search through previous threads and am unable to find any that relate to exporting from a datagrid on a windows form to an excel document.

 

Please help.

 

SonicBoomAu

[herilane]

Lots of approaches discussed here:

http://support.microsoft.com/kb/306022/EN-US/

 

Assuming the datagrid is bound to a dataset, you should be able to use ADO.NET to push the data directly into an Excel sheet. That should be the fastest way.

 

If you want to create a new sheet, "SELECT * INTO" should work; if you want to replace existing data I think you'll have to open a new dataset for the Excel file and transfer the data from your bound dataset into the "Excel dataset" one record at a time. (Caveat: I've only tried this in VB6 and "ADO classic" but I don't see any reason why it would not work the same in ADO.NET).

[SonicBoomAu]

Still having trouble. if anyone has a working example it would be greatly appreciated.

[SonicBoomAu]

I just found a example on the microsoft web site for office automation. It has a program which has a "export to excel" (from a datagrid) button on a windows form.

 

Link to Download Page

 

The main part of the code is below. I Hope that this helps the next person.

 

Private Sub btnExport_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnExport.Click
       ' An Excel spreadsheet involves a hierarchy of objects, from Application
       ' to Workbook to Worksheet.
       Dim excelApp As New Excel.Application()
       Dim excelBook As Excel.Workbook = excelApp.Workbooks.Add
       Dim excelWorksheet As Excel.Worksheet = _
           CType(excelBook.Worksheets(1), Excel.Worksheet)

       ' Make the spreadsheet visible so you can see
       ' the data being entered.
       excelApp.Visible = True

       With excelWorksheet
           ' Set the column headers and desired formatting for the spreadsheet.
           .Columns().ColumnWidth = 21.71
           .Range("A1").Value = "Item"
           .Range("A1").Font.Bold = True
           .Range("B1").Value = "Price"
           .Range("B1").Font.Bold = True
           .Range("C1").Value = "Calories"
           .Range("C1").Font.Bold = True

           ' Start the counter on the second row, following the column headers
           Dim i As Integer = 2
           ' Loop through the Rows collection of the DataSet and write the data
           ' in each row to the cells in Excel. 
           Dim dr As DataRow
           For Each dr In dsMenu.Tables(0).Rows
               .Range("A" & i.ToString).Value = dr("Item")
               .Range("B" & i.ToString).Value = dr("Price")
               .Range("C" & i.ToString).Value = dr("Calories")
               i += 1
           Next

           ' Select and apply formatting to the cell that will display the calorie
           ' average, then call the Average formula.
           .Range("C7").Select()
           .Range("C7").Font.Color = RGB(255, 0, 0)
           .Range("C7").Font.Bold = True
           excelApp.ActiveCell.FormulaR1C1 = "=AVERAGE(R[-5]C:R[-1]C)"
       End With

   End Sub