Generating Reports from Access and Excel Files?

casals asks: "I'm a computer engineer working at a non-IT company, and there's this thing bothering me: by the end of each job, we have to generate a huge report that's actually a composite of lots of minor reports, each one of them made using a different software. Since the softwares used don't interact at all, we have to input the same information five or six times - not too smart, I guess. The outputs are either Access databases or Excel spreadsheets (some of these reports are just Excel spreadsheets that must be filled with data); so, I was thinking about making an application that could aggregate all the input models and generate all the outputs I need, at once. Any suggestions?" "Here's the thing: it cannot be a web-based application (connectivity is a luxury at the rig), it has to run in a laptop (each employee should have it installed, stand-alone) and it must be able to import images from Excel worksheets. Crystal Reports uses spreadsheets as data sources, but it's not Open Source; I was thinking about using BIRT or JasperReports + POI, but that looks to me like inventing the wheel itself, so I decided to ask before digging into it."

Comments

dayjobmode

[LiquidCoooled]

This is the sort of job visual basic (classic) can be good at.
Interaction with MS objects is simple in this environment and theres plenty of help in the IDE.
It lives on a machine quite nicely and is certainly quick enough since most operations will be at the speed of the apps, excel word or ado for the data.

All very "enterprisey"(-4 years) and works on everything from 95 to now with minimal effort just install office and your app and everything is there.

Write a new extractor for each report required and let it grab data as required and push it into the outputs.

Its macro-macros.

ot: does the .net experience appeal? theres gotta be some VB devs hanging around here - where have you lot started moving to since v6.0 'closed' its doors?

Re:dayjobmode

[CaymanIslandCarpedie]

Yes, .NET could do this well. ADO.NET can accept Excel and Access files as data sources. 2005 versions ship with a nice little reporting component (based on same technology as SQL Reporting Services). All that can be free with the Express verions (I'm pretty sure the Express versions ship with the reporting components, but not sure you'd have to verify that).

Though I never really use them (do more big projects where I prefer custom business objects for the most part) this sounds like a great little app for using .NET datasets. Use ADO.NET to populate the datasets (just a few lines of code) then build the relationships between your datasets and BAM you've basically got a little in-memory relational database consisting of data from your multiple misc data sources (be they Excel, Access, or whatever). You can then use this data to get what you need and display via databinding to grids, feeding the reporting component, or whatever makes the most sense to you.