December 3, 2009 at 10:42 am
alber-1003707 (12/2/2009)
Looking forward to test it. Exactly what I needhttp://www.xodox.ch - LCD vs Plasma
My sentiments exactly. Thanks for posting this. I have an application I am working on that requires Excel output. This seems like a very nice solution.
This is a very helpful forum!
December 3, 2009 at 2:54 pm
Is there a way to do additional formating with this method, such auto align the columns??
December 4, 2009 at 4:36 am
I like this way to export to Excel (in SpreadsheetML format). It's very fast. But then I need to import back the Excel file to SQL Server and here is where I get the problem: it fails because it's not a "pure" XML file. I've tried using BULK INSERT and OPENROWSET methods. It works fine if I importing I open it with Excel 2003 and save it as XLS format before importing it, but that's not a solution 🙁
Here is the OPENROWSET configuration used:
ExcelConnectionString: Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Temp\Excel.xls;Persist Security Info=False; Extended Properties="Excel 8.0;HDR=YES;"
Select applied: "SELECT * FROM ExcelSheet$A1:F1000"
I wonder if someone has found in the same problem and what's the workarround ...
Josep
December 4, 2009 at 6:32 am
Josep,
I don't have that situation here, I would just transfer the data directly from SQL to SQL. The solution should be obvious! ImportFromExcel.DLL !!! Get to it =)
December 4, 2009 at 6:39 am
Anders Pedersen (12/4/2009)
Josep,I don't have that situation here, I would just transfer the data directly from SQL to SQL. The solution should be obvious! ImportFromExcel.DLL !!! Get to it =)
Well, It would need to be ImportFromSpreadsheetML.dll but.....
[font="Arial"]_______________________________________________________
Change is inevitable... Except from a vending machine.[/font]
December 4, 2009 at 6:42 am
True, but, at 7:32 in the morning.....
Honestly though, if I was going to import Excel files back in to SQL (or programmatically into anywhere) I would use SSIS instead of this method to export it.
December 4, 2009 at 6:47 am
See, where I will find this really handy is the following scenario...
SSRS report with multiple tables. Two summaries and a detailed list for instance.
When a user exports it to excel to play with it is not sortable or anythign because of all the merged cells and stuff.
My plan was to include the executon of the export in the report query to dump a plain spreadsheet of the data on the web server and include a link in the report. Problem solved.
[font="Arial"]_______________________________________________________
Change is inevitable... Except from a vending machine.[/font]
December 4, 2009 at 6:52 am
Rob Fisk (12/4/2009)
See, where I will find this really handy is the following scenario...SSRS report with multiple tables. Two summaries and a detailed list for instance.
When a user exports it to excel to play with it is not sortable or anythign because of all the merged cells and stuff.
My plan was to include the executon of the export in the report query to dump a plain spreadsheet of the data on the web server and include a link in the report. Problem solved.
That is pretty much excatly how this is used right now. It is used to report on activities inside the IVR, so there are a total of 8 reports. I have 8 stored procedures, one for each report, and one driver stored procedure that runs all 8. Due to this there are 8 result sets coming out of the driver, which is the stored procedure called by the export procedure.
Time to make that into 8 spreadsheets with named tabs? 2 minutes more than just developing the 8 stored procedures, out of that a minute and 30 seconds was spent on runing it and verifying the file ended up where it should.
December 4, 2009 at 6:57 am
Just need to get this pesky carriage return issue sorted now.
Tried the commented line but only allowed whitespace, \ n and \r there:
settings.Indent = true;
//settings.NewLineChars = "& #xA;";
[font="Arial"]_______________________________________________________
Change is inevitable... Except from a vending machine.[/font]
December 4, 2009 at 7:22 am
Anders Pedersen (12/4/2009)
Josep,I don't have that situation here, I would just transfer the data directly from SQL to SQL. The solution should be obvious! ImportFromExcel.DLL !!! Get to it =)
Obviously the users should be able to change some content directly in the Excel file before importing it into SQL Server 😉 (and I can't ask them to do a 'Save as...' and they could forget it...).
Well, I'll let you know if I find something...
Josep
December 4, 2009 at 9:03 am
Josep,
I have been on a project like that before... We had a situation where the SAP people refused to give us the needed security to load data directly from SQL into SAP. The previous way of doing what we tried to automate was trough a spreadsheet that the accountants modifed the data as needed, then clicked a button to upload it to SAP. We took the "simple" approach of populating the existing spreadsheet using a VB6 (could have been 5, was before .NET came out) application that got the data from SQL, populated it using named ranges, then clicked the upload button.
Yah, I know, circumventin security, but that is what will happen when admin's are too protective of their domain, people will find ways around it eventually.
December 4, 2009 at 9:50 am
cameron.hebert (12/3/2009)
Is there a way to do additional formating with this method, such auto align the columns??
Not sure about the auto align but I do know you can open up a new Excel spreadsheet and perform the formatting you want and save as XML. From there look at the XML to see what output Excel produced then modify the code accordingly. I believe you can also include macros and functions but I'm not sure how and when they would run when you open the spreadsheet.
Nick Hanson
December 4, 2009 at 1:30 pm
Rob Fisk (12/2/2009)
alex-743051 (12/2/2009)
I just can use VBA in the Excel worksheet to perform same task:I've used similar in the past but it does not cover the fact that with this, the destination is configurable, if your SP returns multiple results sets then the required worksheets are created and the end product is a clean, macro free spreadsheet.
In addition, the above could be used with a SQL job to run to a schedule, could be followed by a database mail execution to then mail out the generated sheet etc.
I agree... I wish Microsoft would make it just a little easier to "talk" with their other products from T-SQL. I have a general dislike for CLR's but I see this as being really useful, schedulable, and a bunch of other Boy Scout terms.
--Jeff Moden
December 4, 2009 at 8:23 pm
Forget about EXCEL, I like an idea to have a SINGLE point of execution for ANY Stored procedure in database. Will try for output parameters....
December 6, 2009 at 2:44 pm
irozenberg (12/4/2009)
Forget about EXCEL, I like an idea to have a SINGLE point of execution for ANY Stored procedure in database. Will try for output parameters....
The point of this is that is not actually an Exel output but generic spreadsheet xml output that works with Excel as well as other spreadheet clients and, if your queries are SP based iod pretty easy to use.
On top of that it's a fantastic learning mechanism for the interface and xml writer in general.
There is always a need to output to a format that can be opened by those without DB access and to be able to do it via and SP is great, CLR or not. The thing this gives over native methods is the client independant output and the multiple tabs from one SP.
Have not yet found anywhere that has any advice on getting over the carriage return issue.
Latest attempt was writing it as cdata instead of string but excel then reads 0A as the carriage return and 0D as an unreadable character.
[font="Arial"]_______________________________________________________
Change is inevitable... Except from a vending machine.[/font]
Viewing 15 posts - 76 through 90 (of 124 total)
You must be logged in to reply to this topic. Login to reply