Excel in VB.NET - looks the same as the old days?

[Anthony]

To me, creating Excel 2003 spreadsheets programmatically via VB.NET hasn't
really changed since the days of VB6.

That is, I'd do something similar to this


Code:

Dim ExcelApp As Excel.Application
Dim ExcelWB As Excel.Workbook
Dim ExcelWS As Excel.Worksheet

Try
'
' Create a Spreadsheet for the selected company
'
ExcelApp = New Excel.Application
ExcelWB = ExcelApp.Workbooks.Add
ExcelWS = CType(ExcelWB.ActiveSheet, Excel.Worksheet)

' ...etc




What I'm wondering is there a better way of doing this now in VB.NET. That
is, can we get away from adding a reference to Excel11. This is unmanaged
code. (COM). Isn't there a .NET reference that can be added?

Someone mentioned I could go along the XML path, where I'd be creating XML
documents and the user would open them in Excel, etc. But this seams very
limiting to me. I want to be able to create a spreadsheet programmatically
with all the bells and whistles.

Of course I can do this via the old method but I can't believe MS hasn't
progressed from the old days. Or am I missing something? Probably am.

You may be asking what's wrong with the old way? Well in my experiences some
machines would not cope with creating instances of the Excel.Application,
...I'd have to use CreateObject("Excel.Application") instead. If the
application crashed half way thru there would be a bunch of Excel.exe 's
sitting in the task Manager. Argh. I just don't think it is pretty.

Any suggestions on an improvement?

Thanks,
Anthony

 

[Chris]

Until they write a remoting piece to Excel, this is the only way to do it
really.

Chris

 

[Bernie Yaeger]

Hi Anthony,

In addition to Chris's point, be aware that closing an excel spreadsheet is
a bit tricky in vb .net. Here's some of the code you'll need:
objwb.Close()

objxl.DisplayAlerts = True

Marshal.ReleaseComObject(objws)

objxl.Quit()

Marshal.ReleaseComObject(objxl)

objws = Nothing

objwb = Nothing

objwbs = Nothing

objxl = Nothing

HTH,

Bernie Yaeger

 

[Rob Windsor [MVP]]

It hasn't changed at all because you are still creating the same objects and
using the same methods and properties.

One thing that is different is the ability to use .NET code inside Excel
2003 or Word 2003 using Visual Studio Tools for Office.
http://msdn.microsoft.com/smartclient/understanding/vsto/

 

[Greg Burns]

Bernie,

I wanted to use your code, but need some help. Could you explain what your
different variables are? This is what I deduced:

Dim objxl As Excel.Application
Dim objwb As Excel.Workbook
Dim objws As Excel.Worksheet
Dim objwbs As ?

Thanks,
Greg

 

[Herfried K. Wagner [MVP]]

To me, creating Excel 2003 spreadsheets programmatically via VB.NET hasn't
really changed since the days of VB6.

In addition to the other replies: There are loads of information about how
to use Office applications together with .NET in the Microsoft Office
Developer Center:

Microsoft Office Developer Center
<URL:http://msdn.microsoft.com/office/>

 

[Bernie Yaeger]

Hi Greg,

Sorry I missed you last night - went to sleep early.

Your deductions were pretty sound:
Dim objxl As Excel.Application

Dim objwbs As Excel.Workbooks

Dim objwb As Excel.Workbook

Dim objws As Excel.Worksheet

Here's the full code I use to convert an sql table to a .csv or .xls. It's
in a .dll called 'imcfunctionlib':

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, "")

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

HTH,

Bernie

 

[Greg Burns]

I was just looking at my code (this was originally done in VB 6 and I am
trying to upgrade it)

Private objxlglobal As Excel.Global
Private objxl As Excel.Application
Private objwb As Excel.Workbook
Private objws As Excel.Worksheet

I have this objxlglobal scattered all throughout the program. I use it a lot
for its Selection object, like objxlglobal.Selection.Columns.AutoFit().

I didn't see you using that object. Would this be the correct code to
release it (see below)? I wasn't using these ReleaseComObject calls
previously. What are they doing for me? Also, I don't automatically close
Excel, I make it visible so the user can save or close as desired. Does
that change the need to release these variables (proboably not).

Marshal.ReleaseComObject(objws)
'objxl.Quit()
Marshal.ReleaseComObject(objxl)
Marshal.ReleaseComObject(objxlglobal)

objws = Nothing
objwb = Nothing
objxl = Nothing
objxlglobal = Nothing

Thanks,
Greg

 

[Bernie Yaeger]

Hi Greg,

The releasecomobject is necessary to take excel out of memory as a process.
If you open task manager you will see each instance of Excel unless you do
this.

Since you actually want to have excel available, you should not use the
releasecomobject until you want to dispose of it completely.


Also, I haven't been making the kinds of changes to the spreadsheet as you
do, so I haven't had to use the global you refer to, but much has not
changed, so I'd say continue to use it and test to see if it does what it
did formerly. It's pretty much the same object model, so it should work
fine.

Bernie

 

[Anthony]

Thanks very much to all that responded.

I've decided to go with the "old" style referencing of Excel11 et al.

As our company is just about to move from Office 97 to Office 2003 (5000
employees!), ... I've been doing a little reading and one of the microsoft
white pages (97-03Delta.doc) says late binding is actually more beneficial
in some instances, ...more reading required. Since I now know that everyone
is on 2003 I will prbably early bind (need the intellisense!)

Once I got my head round the releasing of objects (marshal) properly (order
seems very important) it all looks fine.

Thanks again.
Anthony