Export data to excel

[Tomek]

Hi,
How could I export data from table or query to excel file in VB.NET?
Thanks in advance,
Tomek

[Michael Gray]

Re: Export data to excel

On Mon, 14 Jun 2004 11:45:42 +0200, "Tomek" <tniewoli@bcdpl us.pl>
wrote:
[color=blue]
>Hi,
>How could I export data from table or query to excel file in VB.NET?
>Thanks in advance,
>Tomek
>[/color]

What version of Excel?
What version of VB.NET?

[Tomek]

Re: Export data to excel

Hi,
VB.NET 1.1
Excel - 97, 2000 or XP.
Tomek

"Michael Gray" <fleetg@newsguy .spam.com> wrote in message
news:di1rc0h6cr ubb3b0lpbv1qb1e t9k4a8ps8@4ax.c om...[color=blue]
> On Mon, 14 Jun 2004 11:45:42 +0200, "Tomek" <tniewoli@bcdpl us.pl>
> wrote:
>[color=green]
> >Hi,
> >How could I export data from table or query to excel file in VB.NET?
> >Thanks in advance,
> >Tomek
> >[/color]
>
> What version of Excel?
> What version of VB.NET?[/color]

[Cor Ligthert]

Re: Export data to excel

Tomek,

Some links,

Office

http://support.microsoft.com/default.aspx?scid=kb;EN-US;311452

http://msdn.microsoft.com/office/

Pia Download

http://www.microsoft.com/downloads/details.aspx?FamilyID=c41bd61e-3060-4f71-a6b4-01feba508e52&displaylang=en

Excel dataset fill and updat

http://support.microsoft.com/default.aspx?scid=kb;EN-US;316934

I hope this helps?

Cor

[Bernie Yaeger]

Re: Export data to excel

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" <tniewoli@bcdpl us.pl> wrote in message
news:O5sz%23QfU EHA.644@tk2msft ngp13.phx.gbl.. .[color=blue]
> Hi,
> How could I export data from table or query to excel file in VB.NET?
> Thanks in advance,
> Tomek
>
>[/color]