Data Set to Excel ?

  • Thread starter Thread starter Rob
  • Start date Start date
R

Rob

Is there an easy (and fast) way to transfer the contents of a dataset to
Excel (without using the Excel object ) ?
 
Excel supports xml as if they were xls documents. There are only a couple
of nuances to be concerned with.

1. Processor Instrcutions
2. Additional Hierachy Members

Save an Excel Document as XML, open it in notepad and replicate the layout.
I think it is pretty cool.

Of course you can always save the dataset as a CSV, Ecxel also knows this
format real well.
 
Is there a fast way....sure (streamwriter), is it easy....everything is
easy once you know how to do it.

You could write a routine to loop through evey table and every row and
every column and export all of it too a .csv file. Then you could open
that in Excel.

I have a need to display data in a certain way, formated a certain way
in excel and for this I created a Crystal Report and then exported it
too Excel.

I think I already have a progam that does this, it run a query against
a database then exports the resuts to a .csv file.

Let me know if you want the code.

Izzy

BTW, if you want super quick and easy and don't care about formatting
at all.

DataSet.WriteXml(Path)

Then you could read the .xml file in Excel.
 
Thanks Izzy,
I think I already have a progam that does this, it run a query against
a database then exports the resuts to a .csv file.
Click to expand...

If you have the code handy, that would be great...

Thanks,
Rob
 
If you have the code handy, that would be great...
Thanks,
Rob
Click to expand...

This is code we have in one of our base forms for dumping the contents
of a datagrid to Excel. It works with the old style datagrids (.NET
FW1.1, VS 2003) and will work in FW 2.0. I don't know if it works with
the new datagrid object, but I'm sure it could be adapted. Hope this
helps:

Me.Cursor = Cursors.WaitCursor
Dim Excel As New Microsoft.Office.Interop.Excel.Application
Dim lcMappingName As String
Dim excelColumn As Integer, excelRow As Integer, colCtr As
Integer
excelColumn = 0
excelRow = 1
Excel.Application.Workbooks.Add(True)
Dim rowIndex As Integer
Dim GridTextColumn As DataGridTextBoxColumn
Dim GridBoolColumn As DataGridBoolColumn
For colCtr = 0 To
DataGrid1.TableStyles(0).GridColumnStyles.Count - 1
If
DataGrid1.TableStyles(0).GridColumnStyles(colCtr).GetType.ToString() =
"System.Windows.Forms.DataGridTextBoxColumn" Then
GridTextColumn =
DataGrid1.TableStyles(0).GridColumnStyles(colCtr)
lcMappingName = GridTextColumn.MappingName
Else
GridBoolColumn =
DataGrid1.TableStyles(0).GridColumnStyles(colCtr)
lcMappingName = GridBoolColumn.MappingName
End If
If
DataGrid1.TableStyles(0).GridColumnStyles.Item(lcMappingName).Width() >
0 Then
excelColumn += 1
Excel.Cells(1, excelColumn) =
DataGrid1.TableStyles(0).GridColumnStyles.Item(lcMappingName).HeaderText
rowIndex = 2
Dim row As DataRow
For Each row In DataSet1.Tables(0).Rows
rowIndex += 1
Excel.Cells(rowIndex, excelColumn) =
row(lcMappingName).ToString()
Next row
End If
Next

Me.Cursor = Cursors.Default
Excel.Visible = True
 
Here you go, pass your DataSet to this function and it will create a
..csv file out of it. It also has an optional parameter to export the
column names.

It will prompt you to save it somewhere.

Enjoy.

Private Sub WriteToCSV(ByVal dsData As DataSet, Optional ByVal
ExportColumnNames As Boolean = False)

Dim dtTable As DataTable
Dim drRow As DataRow
Dim strData As String = ""
Dim swWriter As IO.StreamWriter
Dim dcColumn As DataColumn
Dim sfdFile As SaveFileDialog
Dim i As Int32 = 0

Try

For Each dtTable In dsData.Tables

strData = strData & dtTable.TableName & vbCrLf

If ExportColumnNames Then

For Each dcColumn In dtTable.Columns
strData = strData & dcColumn.ColumnName & ","
Next

strData = strData.TrimEnd(",") & vbCrLf
End If

For Each drRow In dtTable.Rows

For i = 0 To dtTable.Columns.Count - 1
strData = strData &
drRow(i).ToString.Trim.Replace(",", "") & ","
Next

strData = strData.TrimEnd(",") & vbCrLf
Next

strData = strData & vbCrLf
Next

If strData.Length > 0 Then

sfdFile = New SaveFileDialog

With sfdFile
.Filter = "CSV Files (*.csv)|*.csv"
.ShowDialog()
End With

If Not sfdFile.FileName.Length = 0 Then
swWriter = New IO.StreamWriter(sfdFile.FileName)
swWriter.WriteLine(strData)
swWriter.Close()
swWriter.Dispose()
End If

sfdFile.Dispose()
End If
Catch ex As Exception
MsgBox(ex.Message, MsgBoxStyle.OkOnly)
End Try

End Sub


Izzy
 
you can bind the datset to the gridview and then export it in the following way.

With Response
.Clear()
.AddHeader("content-disposition", "attachment;filename=" + strFileName)
.Cache.SetCacheability(HttpCacheability.NoCache)
.ContentType = "application/vnd.ms-excel"
End With

Dim strWrite As System.IO.StringWriter = New System.IO.StringWriter
Dim htmlWrite As System.Web.UI.HtmlTextWriter = New HtmlTextWriter(strWrite)

Dim frm As HtmlForm = New HtmlForm
Me.Controls.Add(frm)
frm.Controls.Add(ExportGridView)
frm.RenderControl(htmlWrite)
Response.Write(strWrite.ToString)
Response.End()

EggHeadCafe.com - .NET Developer Portal of Choice
http://www.eggheadcafe.com
 
Back
Top