How do I create a report with an Excel front end and SQL server back end?

Posted on 2004-04-20
Medium Priority
Last Modified: 2010-04-03
I've been looking but can't find the best way to do this.
The ideal situation would be an Excel file with a SQL Server database.
The data is coming from different tables within one database.  Various calculations have to be done to the data based on information in these tables.  
The end user should be able to just click a button on a web page and download a copy of the report/spreadsheet.  
The data is updated everyday, and the spreadsheet should update automatically.  For example, todays report would include days in April from the 1st to yesterday, the 19th.  Tomorrow's report should include data from the 1st through the 20th.

Can someone point me in the best direction for accomplishing this?


BTW, my background is with ASP and SQL Server.
Question by:jholton
  • 2
  • 2

Author Comment

ID: 10872380
The report will also need input parameters such as Region ID and Month.


Expert Comment

ID: 10872917
It sounds like you're asking a design kind of question.  If you're looking for working code I can't help but I can help you conceptualize what the code would have to do.

Something like this maybe:
It starts with your ASP, you click on a link to generate a report.  The ASP passes a query or series of queries to the SQL Server to retrieve the data which returns it to the ASP.  Now you have a choice to make - will the data be manipulated server-side or client-side.  Either way ASP will have to call an ActiveX/ OLE/COM/whatever it is now object on the CLIENT computer telling to prepare to receive Excel spreadsheet data, (or you may just have to declare the MIME type for Excel to accomplish this).  This is pretty routine stuff.  Should be easy to pick up some snippets or documentation.

If you decide to manipulate the data client side you're probably going to have to learn a few things about the XLS file format.  Can't help you there.

If you decide server-side you might be able to simply pass the data in a simpler form that Excel may be able to interpret for you into it's own format. But maybe not.

Or for one other option you may find it worth your while to pass the data in whatever form to the client as XML.  Then you can do just about whatever you want with it.  But you're also talking about more work.

For setting the date range, all you have to do is use the function that retrieves to day's date and stores it in one variable and another variable that's always set to the first day of the month and use them as the test boundaries for the data coming across.

I hope you find this helpful.  If you  don't, I'm not offended.  It probably means I'm way in over my head here.

Good luck,

Author Comment

ID: 10872936
I know how to create a spreadsheet in ASP by setting the MIME type.  
Do you know if I can open an exiting file though, and dump the data on a "tab" in it?


Accepted Solution

CrackerJack326 earned 500 total points
ID: 10875679
If you're talking about an existing file on the client side this is not possible directly.  For security reasons, web languages, especially scripting languages are not permitted to read or write to client hard drives.

There are, of course indirect ways of doing this.  This requires the use of a plugin or ActiveX control "signed with permissions" which already exists on the client or can be automatically downloaded.  However since it is required to do potentially dangerous things with client data, the client must give permission.  This is common with ActiveX or Java based programs.  It does however require you to get your code authenticated by a third party.  Well, it's not required, but not doing it is a bad idea.  Basically you show them your code and they will make sure that it's not going to pull any funny business with the end-user, and then they provide a digital signature for it.

As far as an existing server-side file goes, if ASP can't (and it may or may not), I'm sure other well established server-side languages can handle file I/O.

If anything doesn't make sense let me know.

Good Luck,

P.S. The technology may not be called ActiveX any more.  I haven't kept up on my terminology in that department.  But it will definitely be something like COM/COM+/DCOM (D for distributed) or possibly ADO (ActiveX Data Objects).  These are all extensively documented at http://msdn.microsoft.com and freely available.

Assisted Solution

pek99 earned 500 total points
ID: 10876150
What would I do is the following:

On the server side
1. Desired Excel Table (empty) assign as an ODBC source.
2. The SQL DB as another ODBC source
3. write a small C++ program running as Windows service, which keeps the data up to date on regular basis (copying from one ODBC to another, should be simple enough for you.)
4. Make the Excel file available for download from Web by any browser.

Maybe you can achieve the step 3 with ASP, too.

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Many businesses neglect disaster recovery and treat it as an after-thought. I can tell you first hand that data will be lost, hard drives die, servers will be hacked, and careless (or malicious) employees can ruin your data.
Among the most obnoxious of Exchange errors is error 1216 – Attached Database Mismatch error of the Jet Database Engine. When faced with this error, users may have to suffer from mailbox inaccessibility and in worst situations, permanent data loss.
This video teaches viewers how to encrypt an external drive that requires a password to read and edit the drive. All tasks are done in Disk Utility. Plug in the external drive you wish to encrypt: Make sure all previous data on the drive has been …
This tutorial will walk an individual through the process of installing the necessary services and then configuring a Windows Server 2012 system as an iSCSI target. To install the necessary roles, go to Server Manager, and select Add Roles and Featu…

584 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question