Hi:
Microsoft has a good set of example code that shows how to move the contents of a datagrid into an excel spreadsheet.
Here is the link: You can download this and load it into Visual Studio and check out the code:
http://www.microsoft.com/downloads/d...displaylang=en
Their approach was somewhat simple: Basically, you create an object of an excel application instance, with a workbook object, and a sheet object, and you traverse your data row by row and column by column and place it in the appropriate cells, referenced by their names (ie "A1", "B2" etc )
I modified this to use the column headings I had defined in my grid table styles, and the widths I had established as well. (Divide the width in your grid by 4 when specifying it for excel or the columns will be way too big.) I also dynamically determine the cell references so that they are created automatically for any size datagrid (I limited mine to 26 columns, but you could extend it further.)
As for naming the file and saving it, once the user is presented with the Excel dialog, they can do that from there, and name it and save it where they like.
Here is my modest code for exporting (via a button) my grid to excel. It requires that you add certain referenced dlls and that the user actually HAS excel.
Private Sub btnExport_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnExport.Click
' display wait cursor
Me.Cursor = Cursors.WaitCursor
' various variables
Dim row As Integer
Dim col As Integer
Dim rowCount As Integer
Dim colCount As Integer
Dim cell As String
Dim rowcell As Integer
' Excel Variables
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)
' get count of rows and count of columns
rowCount = objDataSet.Tables(0).Rows.Count()
colCount = objDataSet.Tables(0).Columns.Count()
' add the column headings
For col = 0 To colCount - 1
row = 1
cell = GetExcelColumn(col) & row.ToString
excelWorksheet.Range(cell).Value = grdFieldnetData.TableStyles(0).GridColumnStyles(co l).HeaderText
excelWorksheet.Range(cell).ColumnWidth = grdFieldnetData.TableStyles(0).GridColumnStyles(co l).Width / 4
Next
' now add the data elements
For row = 0 To rowCount - 1
rowcell = row + 2
For col = 0 To colCount - 1
cell = GetExcelColumn(col) & rowcell.ToString
excelWorksheet.Range(cell).Value = grdFieldnetData.Item(row, col).ToString()
Next
Next
' turn off wait cursor
Me.Cursor = Cursors.Default
' view the spread sheet
excelApp.Visible = True
End Sub
' Function GetExcelColumn - returns the column reference
' from an integer representing a column in a datagrid or dataset
Function GetExcelColumn(ByVal col As Integer) As String
Dim result As String
Select Case col
Case 0 ' first column
result = "A"
Case 1
result = "B"
Case 2
result = "C"
Case 3
result = "D"
Case 4
result = "E"
Case 5
result = "F"
Case 6
result = "G"
Case 7
result = "H"
Case 8
result = "I"
Case 9
result = "J"
Case 10
result = "K"
Case 11
result = "L"
Case 12
result = "M"
Case 13
result = "N"
Case 14
result = "O"
Case 15
result = "P"
Case 16
result = "Q"
Case 17
result = "R"
Case 18
result = "S"
Case 19
result = "T"
Case 20
result = "U"
Case 21
result = "V"
Case 22
result = "W"
Case 23
result = "X"
Case 24
result = "Y"
Case 25
result = "Z"
End Select
Return result
End Function
Writing to Excel from a dataset with VB.Net
