Exporting to Excel Using a CLR Stored Procedure

Question

Exporting to Excel Using a CLR Stored Procedure

Viewing 15 posts - 91 through 105 (of 124 total)

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

Rob Fisk SSCommitted Points: 1861 More actions · Answer 1

OK, got somewhere on the multi-line entries.

This method may go horribly wrong if you have special characters such as ampersand, gt, lt etc since currently it's writing raw text.

Working on getting it to just do better carriage return character only but....

In WriteHeaderInfo //default styling add the second line of

xw.WriteAttributeString("ss", "Vertical", null, "Bottom");

xw.WriteAttributeString("ss", "WrapText", null, "1"); //added for multiline support

and in Data styling change the wrap:

xw.WriteAttributeString("ss", "WrapText", null, "1");

Then in the cell writing bit change as below:

//xw.WriteString(output);

xw.WriteRaw(output.Replace(Environment.NewLine, "& #xA;")); //remove the space between & and # of course.

I'm looking at overriding the settings.NewLineChars though so there is no need for the raw writing.

Edit: OK, setting.NewLineChars is just how it writes the newlines in the whole file, not those in the data so no good.

[font="Arial"]_______________________________________________________
Change is inevitable... Except from a vending machine.[/font]

bret.lowery Old Hand Points: 302 More actions · Answer 2

That method does not work on 64-bit SQLm the CLR method does

Rob Fisk SSCommitted Points: 1861 More actions · Answer 3

bret.lowery (12/8/2009)
That method does not work on 64-bit SQLm the CLR method does

Which method does not work? You may need to be more specific or quote the post you were referring to since there are many pages worth.

[font="Arial"]_______________________________________________________
Change is inevitable... Except from a vending machine.[/font]

Idea Deadbeat SSC-Addicted Points: 416 More actions · Answer 4

The extension hardening got a little harder trying to name file with xlsx extension.

When I change C# source to have xlsx extension the Excel file will not open. Granted Office 2007 opens it fine with xls extension - I would still like to make it work with xlsx - anybody have any ideas on what needs to change in the C# source to make it happen - or is it something on the Office side.

Sartre - “One is still what one is going to cease to be and already what one is going to become. One lives one's death, one dies one's life.”

Rob Fisk SSCommitted Points: 1861 More actions · Answer 5

Brad Chapman-387295 (12/8/2009)
The extension hardening got a little harder trying to name file with xlsx extension.
When I change C# source to have xlsx extension the Excel file will not open. Granted Office 2007 opens it fine with xls extension - I would still like to make it work with xlsx - anybody have any ideas on what needs to change in the C# source to make it happen - or is it something on the Office side.
Sartre - “One is still what one is going to cease to be and already what one is going to become. One lives one's death, one dies one's life.”

Definitely an office side thing.

By specifying xlsx you are telling it it that it is definitely written in 2007 format which is the MS office XML format zipped. Since it has no zip header it won't open I assume.

Quite good actually, if you have somethign like 7zip you can extract the contents of any office2007 file to view the xml that's at the back end of it.

[font="Arial"]_______________________________________________________
Change is inevitable... Except from a vending machine.[/font]

kennydevries Grasshopper Points: 19 More actions · Answer 6

I just wanted to say thanks to the author for this article. It worked out of the box for me on a project I am doing for a process engineering firm.

I am not using the CLR approach, just using it from a web application that exports an equipment list of manufacturing plant components. Some of thse lists can be quite big and XML is kind of verbose, so I am going to have to try this with a big file.

I read SQLServerCentral.com newsletters all the time. I get a couple newsletters and these are the best.

Anyway, thanks for this article!

Ken De Vries

m.steenbakkers SSC Rookie Points: 31 More actions · Answer 7

I'd like the dll itself as a direct download, don't have the tools here to compile the sourcecode myself 🙁

Rene G SSC Enthusiast Points: 183 More actions · Answer 8

m.steenbakkers (12/14/2009)
I'd like the dll itself as a direct download, don't have the tools here to compile the sourcecode myself 🙁

In the source download, the dll is in the folder \ExcelExport\bin\Release 😀

m.steenbakkers SSC Rookie Points: 31 More actions · Answer 9

Rene G (12/14/2009)
m.steenbakkers (12/14/2009)
I'd like the dll itself as a direct download, don't have the tools here to compile the sourcecode myself 🙁
In the source download, the dll is in the folder \ExcelExport\bin\Release 😀

Sorry for polluting the discussion. :Whistling:

richard.mann SSC Rookie Points: 29 More actions · Answer 10

This is working fine for me when the filepath is to a local directory (C:\..), but when I try using a network location I get an error returned.

Msg 6522, Level 16, State 1, Procedure ExportToExcel, Line 0

A .NET Framework error occurred during execution of user-defined routine or aggregate "ExportToExcel":

System.UnauthorizedAccessException: Access to the path '\\project.co.uk\project\build\ExportTest.xls' is denied.

Where do I need to give permissions for this to work?

Jeff Moden SSC Guru Points: 1004343 More actions · Answer 11

SQL Server is running the CLR... SQL Server must be runing as a user that has the privs to "see" the UNC.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

rezanosratabadi Newbie Points: 5 More actions · Answer 12

Hi

It is very good and very useful for me

Thanks a lot 🙂

sidni Valued Member Points: 60 More actions · Answer 13

I have tried this and it seems to work fine, however it does not recognise excel when I tried to open although it opens it and my main point is it does not work on a network drive, basically the file needs to be saved in the server where sql server is installed.

Any way to make this work on a network drive? I tried and have required permissions but not working.

Ideally I need it to run on a nework driver to avoid round trips to the server to retrieve the created file.

Thanks

sidni

Rob Fisk SSCommitted Points: 1861 More actions · Answer 14

I normally just create a very restriced access share on the DB machine so I can grab the files off it remotely. I find that with SQL server it's a great sanity saver to work with local drives within SQL and work out the access elswhere.

For this exact issue though when users needed access. I actually set the file location to the SQL server local folder that Reporting Services web site is hosted in.

I had a report that ran this SP as well as the same results in the web report and included a static hyperlink to the file.

Nice easy solution.

Basically the reason for this was that they wanted the report both pretty for sending on but still able to sort through in excel and any kind of prettyness messes up the exported file's ability to sort due to merged cells etc in RS.

[font="Arial"]_______________________________________________________
Change is inevitable... Except from a vending machine.[/font]

Anders Pedersen SSChampion Points: 11410 More actions · Answer 15

sidni (6/18/2010)
I have tried this and it seems to work fine, however it does not recognise excel when I tried to open although it opens it and my main point is it does not work on a network drive, basically the file needs to be saved in the server where sql server is installed.
Any way to make this work on a network drive? I tried and have required permissions but not working.
Ideally I need it to run on a nework driver to avoid round trips to the server to retrieve the created file.
Thanks
sidni

I do this on several of my reports that I wrote this code for. As have been noted above, you should be able to do it with the account that SQL runs under's permission on the folder.

Another way I use it is part of SSIS packages, and then use the file copy in SSIS to put the file in it's final folder (I have some that for various reasons go to multiple locations).

Most of mine though are mailed out from SQL directly to the recipients.

Anders