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.




Show quoteHide quote

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:
Show quoteHide quote

Thanks Izzy,

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

Thanks,
Rob


Show quoteHide quote

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


Rob wrote:
Show quoteHide quote

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

vb.net serial IO problem
da.update (syntax error in UPDATE statement)
VB to C# converter
RuntimeHelpers.GetObjectValue Discussion
[Newbie] Customize Anchor properties ?
SelectedIndexChanged causes selectedindex to reset to -1?
Newbie Paint Question
Focus problem with the tab control
Q: Activated
windows control library