Export DataView

[nologin]

Is it possible to export data exposed by DataView to Excel file for example
?

Seba

 

[Cor]

Hi Nologin,

Is it possible to export data exposed by DataView to Excel file for

example

Somewhat down is a problem with an OleDB for Excel from Omar,
It is an error, but the sollution has to be giving possibilities for you to
I think.
Take a look for it, it is somewhat your problem to.
And that you can use without an office connection.

I hope this brings you on the Route

Cor

 

[Jay B. Harlow [MVP - Outlook]]

Seba,
In addition to Cor's comments, back in June I wrote a function that exports
a DataTable to a comma delimited text file.

However if you have newer versions of Excel, why not simply save the DataSet
as an XML file, then open the XML file in Excel?

Here's a quick VB.NET 1.1 export routine that is very general (too
general?):

' Required imports
Imports System.IO ' for the StreamWriter
Imports System.Text ' for the UnicodeEncoding

' sample usage
Export("Customers.csv", DataSet1.Tables("Customers"))
Export("Employees.csv", DataSet1.Tables("Employees"))


Public Sub Export(ByVal path As String, ByVal table As DataTable)
Dim output As New StreamWriter(path, False, UnicodeEncoding.Default)
Dim delim As String

' Write out the header row
delim = ""
For Each col As DataColumn In table.Columns
output.Write(delim)
output.Write(col.ColumnName)
delim = ","
Next
output.WriteLine()

' write out each data row
For Each row As DataRow In table.Rows
delim = ""
For Each value As Object In row.ItemArray
output.Write(delim)
If TypeOf value Is String Then
output.Write(""""c) ' thats four double quotes and a c
output.Write(value)

output.Write(""""c) ' thats four double quotes and a c
Else
output.Write(value)
End If
delim = ","
Next
output.WriteLine()
Next

output.Close()

End Sub

You can change (or remove) the Encoding parameter above to suit your needs,
I used Unicode as the table had non ASCII characters in it. Also when
writing strings, I don't deal with double quotes in the string. If you make
the StreamWriter a parameter it will be much more flexible (you could go to
a memory stream to support cut & paste). I use the default formatting for
numeric types.

You can change it to use a DataView (for sorting & filtering for example) by
changing the following lines:

'Public Sub Export(ByVal path As String, ByVal table As DataTable)
Public Sub Export(ByVal path As String, ByVal view As DataView)

'For Each col As DataColumn In table.Columns
For Each col As DataColumn In view.Table.Columns

'For Each row As DataRow In table.Rows
For Each row As DataRowView In view

'For Each value As Object In row.ItemArray
For Each value As Object In row.Row.ItemArray


Hope this helps
Jay

 

[Bernie Yaeger]

Hi,

Here's a function that will convert a datatable to an excel spreadsheet. No
reason it couldn't be quickly modified to do the same with a dataview.

Public Function sqltabletocsvorxls(ByVal dt As DataTable, ByRef strpath As
String, ByVal dtype As String, ByVal includeheader As Boolean) As Integer

' signature:

' dim funcs as new imcfunctionlib.functions

' dim xint as integer

' xint = funcs.sqltabletocsvorxls(dsmanifest.tables(0),mstrpath,
"csv",false)

' where mstrpath = , say, "f:\imcapps\xlsfiles\test.xls"

sqltabletocsvorxls = 0

Dim objxl As Excel.Application

Dim objwbs As Excel.Workbooks

Dim objwb As Excel.Workbook

Dim objws As Excel.Worksheet

Dim mrow As DataRow

Dim colindex As Integer

Dim rowindex As Integer

Dim col As DataColumn

Dim fi As FileInfo = New FileInfo(strpath)

If fi.Exists = True Then

Kill(strpath)

End If

objxl = New Excel.Application

'objxl.Visible = False ' i may not need to do this

objwbs = objxl.Workbooks

objwb = objwbs.Add

objws = CType(objwb.Worksheets(1), Excel.Worksheet)

' i many want to change this to pass in a variable to determine

' if i want to have a column name row or not

If includeheader Then

For Each col In dt.Columns

colindex += 1

objws.Cells(1, colindex) = col.ColumnName

Next

rowindex = 1

Else

rowindex = 0

End If

For Each mrow In dt.Rows

rowindex += 1

colindex = 0

For Each col In dt.Columns

colindex += 1

objws.Cells(rowindex, colindex) = mrow(col.ColumnName).ToString()

Next

Next

If dtype = "csv" Then

objwb.SaveAs(strpath, xlCSV)

Else

objwb.SaveAs(strpath)

End If

objxl.DisplayAlerts = False

objws.Close()

objxl.DisplayAlerts = True

Marshal.ReleaseComObject(objws)

objxl.Quit()

Marshal.ReleaseComObject(objxl)

objws = Nothing

objwb = Nothing

objwbs = Nothing

objxl = Nothing

End Function

HTH,

Bernie Yaeger