SQL 2005 into designed Excel via Stored Procedure.

Question

SQL 2005 into designed Excel via Stored Procedure.

BOR15K

SSCertifiable

Points: 5894
More actions
August 6, 2013 at 3:07 am

#280876

Hi All,
I need to export data from SQL 2005 into Excel. I know I can use bcp or OPENSOURCE, but the requirement is that I need
to customise my excel: with titles of different sizes, company's logo, making various results bold and so.
I assume the best way to do so would be by creating an html or even xml file, but cannot find a proper example, so my questions are:
- what is the best way to create such an Excel file?
- can some one point me to a website I can have a look, or even post some basic example here, please?
Any suggestion will be much appreciated!
Thanks!

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 1

I would create a report in SSRS and then export the report to Excel.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

BOR15K SSCertifiable Points: 5894 More actions · Answer 2

Thank you for the reply.

Do you think it would be a good approach for automated processes, which might be invoked by end-user

from an application?

Kind Regards

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 3

Sure. You can schedule SSRS reports. And if a user needs the Excel report, he can always run the report itself.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

BOR15K SSCertifiable Points: 5894 More actions · Answer 4

I understand,

Is there any particular website you would suggest to have a look how to create SSRS with images, please?

Also I have a concern that some of the clients may run only SQL 2005 Express version. Will the SSRS work for them as well?

Kind Regards

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 5

Images in SSRS:

Images (Report Builder and SSRS)

Some tutorials:

Reporting Services Tutorials (SSRS)

Reporing Services is supported to some extent in SQL Server 2005 Express with Advanced Services:

Features Supported by the Editions of SQL Server 2005

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

BOR15K SSCertifiable Points: 5894 More actions · Answer 6

BOR15K

SSCertifiable

Points: 5894

August 6, 2013 at 4:40 am

#1638848

Thank you for your help.

Kind Regards

BOR15K SSCertifiable Points: 5894 More actions · Answer 7

I guess I need to look for another solution as customer's sites do not have SSRS installed.

Any other advise, please?

Kind Regards

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 8

BOR15K (8/6/2013)
I guess I need to look for another solution as customer's sites do not have SSRS installed.
Any other advise, please?
Kind Regards

The only tool in the SQL Server stack that can format Excel files is SSRS.

All the others (SSIS for example) just dump the raw data into the Excel file. You could try to work with named ranges though as destinations in SSIS, but SSIS is not included in SQL Server express.

Macro's are always another alternative. You take an excel file with the layout already present and you fetch the data using a macro.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

SGT_squeequal SSCertifiable Points: 7167 More actions · Answer 9

HI I have just implimeted something a little similar in excell however, instead of exporting from SQL i import from excel.

I created a button on spreadhseet then create some VBA to open a connection and then populate a worksheet, works wonders

😀

***The first step is always the hardest *******

BOR15K SSCertifiable Points: 5894 More actions · Answer 10

SGT_squeequal (8/6/2013)
HI I have just implimeted something a little similar in excell however, instead of exporting from SQL i import from excel.
I created a button on spreadhseet then create some VBA to open a connection and then populate a worksheet, works wonders
😀

Thanks a lot - it looks cool indeed! But my problem is that the Excel file will be sent to clients, which won't be able to access customer's SQL server.

Thanks again!

BOR15K SSCertifiable Points: 5894 More actions · Answer 11

Koen Verbeeck (8/6/2013)
BOR15K (8/6/2013)
I guess I need to look for another solution as customer's sites do not have SSRS installed.
Any other advise, please?
Kind Regards
The only tool in the SQL Server stack that can format Excel files is SSRS.
All the others (SSIS for example) just dump the raw data into the Excel file. You could try to work with named ranges though as destinations in SSIS, but SSIS is not included in SQL Server express.
Macro's are always another alternative. You take an excel file with the layout already present and you fetch the data using a macro.

I see there is also an option to export into html style file and just to change its extension. But then my issue is an image. Although I have converted it into base64 and can see it in html, in the excel it ignores it and just shows the x instead.

<html xmlns:v="urn:schemas-microsoft-com:vml"

xmlns:o="urn:schemas-microsoft-com:office:office"

xmlns:x="urn:schemas-microsoft-com:office:excel"

xmlns="http://www.w3.org/TR/REC-html40">

Thanks a lot!

tim.hansen SSC Veteran Points: 296 More actions · Answer 12

Because I'm old and it still works, I use vb6 to create excel (from SQL stored procedures) and format within the vb code.

I've been sending various reports to people in my agency this way for years and they love it.

We have a dedicated machine that runs the vb.exe file when scheduled.

Living in Paradise: N 34°16'07.99" W 119°12'17.58"