Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The business world is becoming increasingly integrated with tech. It’s not just for a select few anymore — but what about if you have a small business? It may be easier than you think to integrate technology into your small business, and it’s likely…
Article by: evilrix
Looking for a way to avoid searching through large data sets for data that doesn't exist? A Bloom Filter might be what you need. This data structure is a probabilistic filter that allows you to avoid unnecessary searches when you know the data defin…
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…
This Micro Tutorial will teach you how to reformat your flash drive. Sometimes your flash drive may have issues carrying files so this will completely restore it to manufacturing settings. Make sure to backup all files before reformatting. This w…
Suggested Courses

596 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