CODEWITHSUNDEEP
.netexcelsql-server-2008stored-procedures
Lebowski
Lebowski

Reputation: 121

From stored procedure to excel via webpage, how?

We have a stored procedure that fetches all sales data from my company.

We have a web-system developed in .net

Now we want the customer to be able to download sales reports on our webpage.

How can we make the stored procedure results automatically into excel files available on our webpage?

Extra question: We also need to insert formulas to the excel sheet, in which stage is this best to do? And how?

All ideas/guides etc would be nice :)

Upvotes: 1

Views: 92

Answers (1)

Matt H
Matt H

Reputation: 640

Lebowski!

I wanted to possibly suggest a different way of approaching this. I recently went through this same thought process of providing a client with an excel sheet that would need be generated monthly, quarterly, etc. through our website; but the excel sheet also needed formulas, which I couldn't find an answer as to how we could implement that.

What we did instead was first crafted the excel sheet (formulas and all). Instead of a stored procedure, we created a view to grab all the relevant data. Then, from Excel, we connect to the database (in this scenario you would need your db server accessible to the client). See this article for details on connecting to the DB from the Excel sheet.

After that, we used a pivot, and pulled data directly from the pivot to the main sheet. Then, we used that data where we needed formulas.

We created SQL credentials that only had access to the view(s) we made, and distributed the spreadsheet to the clients. Now, they just click "Refresh All" on their spreadsheet, enter the credentials we gave them, and their spreadsheet pulls the most recent data.

I hope this alternative approach is helpful to you. It certainly was to me.

Upvotes: 1

Related Questions