Exporting Datagrid to Excel

[SonicBoomAu]

Hi All,

Dont 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 youll 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: Ive only tried this in VB6 and "ADO classic" but I dont 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.

[VB]
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
[/VB]