Hi Tomek,
I had need to do this so often that I wrote a function for it. It is below.
Bear in mind that this takes some time, but it's currently better that using
..xml, which is much faster but it has a couple of flaws - the biggest of
which is that a label with number characters only will appear inside excel
as, say, '833' when it should be '00833'. If you can figure that out, let
me know.
The function below allows for either .csv or .xls (.xls works best but is
slow), and you can also use column names as headers, if you wish.
HTH,
Bernie Yaeger
Public Function sqltabletocsvor xls(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.sqltablet ocsvorxls(dsman ifest.tables(0) ,mstrpath,
"csv",false )
' where mstrpath = , say, "f:\imcapps\xls files\test.xls"
sqltabletocsvor xls = 0
Dim objxl As Excel.Applicati on
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(strpat h)
If fi.Exists = True Then
Kill(strpath)
End If
objxl = New Excel.Applicati on
'objxl.Visible = False ' i may not need to do this
objwbs = objxl.Workbooks
objwb = objwbs.Add
objws = CType(objwb.Wor ksheets(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
Dim fixedstring As String
For Each mrow In dt.Rows
rowindex += 1
colindex = 0
For Each col In dt.Columns
colindex += 1
fixedstring = mrow(col.Column Name).ToString( ).Replace(vbCrL f, "")
'objws.Cells(ro windex, colindex) = mrow(col.Column Name).ToString( )
If col.DataType.To String = "System.Str ing" Then
objws.Cells(row index, colindex) = "'" & fixedstring
Else
objws.Cells(row index, colindex) = fixedstring
End If
Next
Next
If dtype = "csv" Then
objwb.SaveAs(st rpath, xlCSV)
Else
objwb.SaveAs(st rpath)
End If
objxl.DisplayAl erts = False
objwb.Close()
objxl.DisplayAl erts = True
Marshal.Release ComObject(objws )
objxl.Quit()
Marshal.Release ComObject(objxl )
objws = Nothing
objwb = Nothing
objwbs = Nothing
objxl = Nothing
End Function
"Tomek" <tn******@bcdpl us.pl> wrote in message
news:O5******** *******@tk2msft ngp13.phx.gbl.. .
Hi,
How could I export data from table or query to excel file in VB.NET?
Thanks in advance,
Tomek
