Imports Excel.XlFileFormat
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports System.IO
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