VB.Net Reporting Project

tommycahir

Hi All

Just really looking for some ideas on how to approach this rather than actual assistance with an error.

I currently am generating a number of excel spreadsheets for different customers and then at the end of the month there is a number of reports generated from the spreadsheet for that particular customer.


What i am looking to do is create program that can read that spreadsheet and then depending on which button is pressed generate the report.

i am planning to use VB.net to create this program( as i have some VB experience and have coded macros in VBA to generate some of the report's and am interested in learning VB.Net)

as i have stated i have some of the VBA macros developed and working in the excel spreadsheets themselves but i want to be able to distribute this single exe to allow the customer to generate the report themselves without worrying about the macros in the spreadsheet

i was thinking of displaying these charts and data tables in a html format so that they can be placed on a web server.

Any body got any ideas on how i can read in the excel file -> run the code to generate the charts,graphs,data tables and display neatly in HTML or another better format for this?


I must also note the the spreadsheets contain a header row and was thinking of a way to select the columns that should be read in and used to generate the reports

Any help or suggestions would be welcome

Tommy

Smoggy

I think you want to use a combination of vb.net to read the data from the spreadsheets via the office com interface an use this data to produce HTML reports via a report program like crystal reports.

This would be run client side and would end up with the user having reports generated in HTML, from here they can do what they like with them, such as upload to servers etc.

bpmurray

Well, to read the thing is pretty basic. Try something like:

Dim myApp As Excel.Application
Dim mySheet As Excel.Worksheet
Dim myRange As Excel.Range

Try
   Set myApp = New Excel.Application
   myApp.DisplayAlerts = False
   myApp.Workbooks.Open "myFile.xls"
   mySheet = myApp.Sheets(1)
   myRange = mySheet.Cells(1, 1)

   ... Do your processing here
   frmVar.Value = myRange.Value

catch ex as Exception
   (error handling here)
finally
   myApp.Workbooks.Close()
   ... this is necessary to prevent memory problems
   ... the order is important
   System.Runtime.InteropServices.Marshal.ReleaseComObject(myRange)
   System.Runtime.InteropServices.Marshal.ReleaseComObject(mySheet)
   System.Runtime.InteropServices.Marshal.ReleaseComObject(myApp)
   myApp = Nothing
   myRange = Nothing
   mySheet = Nothing
   GC.Collect()
End Try

tommycahir

thx for the input guy's

and especially for the code snippet

Been thinking bout this again is it hard to display the graphs charts etc in the application on a frame ( or something similar) first and then have the option to write out to a html file?

Gosh

You can save a spreadsheet in EXCEL as an HTML page (under Save As) - all sheets will be visible and navigation buttons are placed on the web page to allow this.

tommycahir

Not sure if that is the sort of option that i was looking for as i want the reports generated to be dependent only on what sub-routines the user calls by pressing the button

Gosh

If you only want specific sheets then this can be done too. You can have a function in your VB code that does exactly that

Something like ...

With ActiveWorkbook.PublishObjects.Add(xlSourceSheet, "F:\Temp\Book1.htm", _
        "Sheet1", "", xlHtmlCalc, "Book1_3423", "Title")
        .Publish (True)
        .AutoRepublish = False
End With
ChDir "F:\Temp"

The above would only publish Sheet1 as an HTML page ...

Smoggy

if you are going with crystal reports it has a report viewer which is the equivelant of a frame and then either from the viewer you can save to html or via code you can select a path and output as a html file.

bonkey

Pretty much you can do with Excel manually can be achieved throuh the Office .Net libraries. Its "just" a case of reading up on the object model and its methods etc.

One thing to watch out for is the version of office being used. As far as I remember, only Office 2003 comes with .Net Interop Assemblies. You can also download assemblies for Office XP.

jc

tommycahir

mm interesting as most the customers will be using the 2002 version of excel - and ill be using VS.net 2003 is there any conflict with these 2 versions.
Thanks for the ideas guys the really appreciated.. i am seriously thinking of going for the crystal reports option sounds like more fun to mess about with when i implementing it

bonkey

tommycahir wrote:

mm interesting as most the customers will be using the 2002 version of excel - and ill be using VS.net 2003 is there any conflict with these 2 versions.

2002 is, AFAIR, Office XP. You'll need to download the Office XP Interop Assemblies from Microsoft to do your development I think. (google on that term or on 'oxpia' to find them.

i am seriously thinking of going for the crystal reports option sounds like more fun to mess about with when i implementing it

If you're gonna do that, could I suggest you might also consider having a look at ActiveReports by DataDynamics. OK - its not free - but its my favourite .Net reporting environment, not least because of its programmability.