Is there an easy (and fast) way to transfer the contents of a dataset to
Excel (without using the Excel object ) ?
Excel (without using the Excel object ) ?
-
-
Re: Data Set to Excel ?
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" <rwchome@comcas t.netwrote in message
news:yoqdnWZH5a C9Gb_YnZ2dnUVZ_ oidnZ2d@comcast .com...Is there an easy (and fast) way to transfer the contents of a dataset to
Excel (without using the Excel object ) ?
>
-
Re: Data Set to Excel ?
Is there a fast way....sure (streamwriter), is it easy....everyth ing 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.WriteXm l(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 ) ?Comment
-
Re: Data Set to Excel ?
Thanks Izzy,
If you have the code handy, that would be great...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.
Thanks,
Rob
"Izzy" <israel.richner @gmail.comwrote in message
news:1159892111 .142080.197360@ c28g2000cwb.goo glegroups.com.. .Is there a fast way....sure (streamwriter), is it easy....everyth ing 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.WriteXm l(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 ) ?
Comment
-
Re: Data Set to Excel ?
If you have the code handy, that would be great...This is code we have in one of our base forms for dumping the contents>
Thanks,
Rob
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.WaitCur sor
Dim Excel As New Microsoft.Offic e.Interop.Excel .Application
Dim lcMappingName As String
Dim excelColumn As Integer, excelRow As Integer, colCtr As
Integer
excelColumn = 0
excelRow = 1
Excel.Applicati on.Workbooks.Ad d(True)
Dim rowIndex As Integer
Dim GridTextColumn As DataGridTextBox Column
Dim GridBoolColumn As DataGridBoolCol umn
For colCtr = 0 To
DataGrid1.Table Styles(0).GridC olumnStyles.Cou nt - 1
If
DataGrid1.Table Styles(0).GridC olumnStyles(col Ctr).GetType.To String() =
"System.Windows .Forms.DataGrid TextBoxColumn" Then
GridTextColumn =
DataGrid1.Table Styles(0).GridC olumnStyles(col Ctr)
lcMappingName = GridTextColumn. MappingName
Else
GridBoolColumn =
DataGrid1.Table Styles(0).GridC olumnStyles(col Ctr)
lcMappingName = GridBoolColumn. MappingName
End If
If
DataGrid1.Table Styles(0).GridC olumnStyles.Ite m(lcMappingName ).Width() >
0 Then
excelColumn += 1
Excel.Cells(1, excelColumn) =
DataGrid1.Table Styles(0).GridC olumnStyles.Ite m(lcMappingName ).HeaderText
rowIndex = 2
Dim row As DataRow
For Each row In DataSet1.Tables (0).Rows
rowIndex += 1
Excel.Cells(row Index, excelColumn) =
row(lcMappingNa me).ToString()
Next row
End If
Next
Me.Cursor = Cursors.Default
Excel.Visible = True
Comment
-
Re: Data Set to Excel ?
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(ByVa l dsData As DataSet, Optional ByVal
ExportColumnNam es 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.TableNa me & vbCrLf
If ExportColumnNam es Then
For Each dcColumn In dtTable.Columns
strData = strData & dcColumn.Column Name & ","
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).ToStri ng.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.FileNam e.Length = 0 Then
swWriter = New IO.StreamWriter (sfdFile.FileNa me)
swWriter.WriteL ine(strData)
swWriter.Close( )
swWriter.Dispos e()
End If
sfdFile.Dispose ()
End If
Catch ex As Exception
MsgBox(ex.Messa ge, MsgBoxStyle.OkO nly)
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" <israel.richner @gmail.comwrote in message
news:1159892111 .142080.197360@ c28g2000cwb.goo glegroups.com.. .Is there a fast way....sure (streamwriter), is it easy....everyth ing 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.WriteXm l(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 ) ?Comment
-
datset to excel
you can bind the datset to the gridview and then export it in the following way.
With Response
.Clear()
.AddHeader("con tent-disposition", "attachment;fil ename=" + strFileName)
.Cache.SetCache ability(HttpCac heability.NoCac he)
.ContentType = "applicatio n/vnd.ms-excel"
End With
Dim strWrite As System.IO.Strin gWriter = New System.IO.Strin gWriter
Dim htmlWrite As System.Web.UI.H tmlTextWriter = New HtmlTextWriter( strWrite)
Dim frm As HtmlForm = New HtmlForm
Me.Controls.Add (frm)
frm.Controls.Ad d(ExportGridVie w)
frm.RenderContr ol(htmlWrite)
Response.Write( strWrite.ToStri ng)
Response.End()
EggHeadCafe.com - .NET Developer Portal of Choice
Comment
Comment