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 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),mstr path,
"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
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.ColumnName).ToString().Replace(vbCrLf, "")
'objws.Cells(rowindex, colindex) = mrow(col.ColumnName).ToString()
If col.DataType.ToString = "System.String" Then
objws.Cells(rowindex, colindex) = "'" & fixedstring
Else
objws.Cells(rowindex, colindex) = fixedstring
End If
Next
Next
If dtype = "csv" Then
objwb.SaveAs(strpath, xlCSV)
Else
objwb.SaveAs(strpath)
End If
objxl.DisplayAlerts = False
objwb.Close()
objxl.DisplayAlerts = True
Marshal.ReleaseComObject(objws)
objxl.Quit()
Marshal.ReleaseComObject(objxl)
objws = Nothing
objwb = Nothing
objwbs = Nothing
objxl = Nothing
End Function
"Tomek" <tn******@bcdplus.pl> wrote in message
news:O5***************@tk2msftngp13.phx.gbl...
Hi,
How could I export data from table or query to excel file in VB.NET?
Thanks in advance,
Tomek
