Export data to excel

  • Thread starter Thread starter Tomek
  • Start date Start date
T

Tomek

Hi,
How could I export data from table or query to excel file in VB.NET?
Thanks in advance,
Tomek
 
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),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

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
 
Back
Top