Data Set to Excel ?

[Rob]

Is there an easy (and fast) way to transfer the contents of a dataset to
Excel (without using the Excel object ) ?

[AMDRIT]

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.


"Rob" <rw*****@comcast.netwrote in message
news:yo******************************@comcast.com. ..

Is there an easy (and fast) way to transfer the contents of a dataset to
Excel (without using the Excel object ) ?

[Izzy]

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.
Rob wrote:

Is there an easy (and fast) way to transfer the contents of a dataset to
Excel (without using the Excel object ) ?

[Rob]

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.

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

Thanks,
Rob
"Izzy" <is************@gmail.comwrote in message
news:11**********************@c28g2000cwb.googlegr oups.com...

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.
Rob wrote:

>Is there an easy (and fast) way to transfer the contents of a dataset to
Excel (without using the Excel object ) ?

[BK]

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

>
Thanks,
Rob

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(lcM appingName).Width() >
0 Then
excelColumn += 1
Excel.Cells(1, excelColumn) =
DataGrid1.TableStyles(0).GridColumnStyles.Item(lcM appingName).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

[Izzy]

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
Rob wrote:

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.

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

Thanks,
Rob
"Izzy" <is************@gmail.comwrote in message
news:11**********************@c28g2000cwb.googlegr oups.com...

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.
Rob wrote:

Is there an easy (and fast) way to transfer the contents of a dataset to
Excel (without using the Excel object ) ?

[pampatipraveen]

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