Export to Excel without DTS. Possible?

Question

Export to Excel without DTS. Possible?

maouad

SSC-Addicted

Points: 426
More actions
August 27, 2003 at 5:53 am

#83148

I am trying to figure out how to export the results of a query to Excel with some specific formatting. Is this possible to do without using DTS?
Currently we have a web application and users can view reports via the web. However, sometimes that want to generate a really huge report (essentially a summary of all data in the system) and have it in Excel. I can do it by running a query in ASP and from the ASP generate the excel file with formatting. However I'd like to put it in a stored procedure or something so that SQL can do all the work.
Maybe someone can point me in the right direction.
Thanks,
Mick.

Viewing 15 posts - 1 through 14 (of 14 total)

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

Frank Kalis SSC Guru Points: 111183 More actions · Answer 1

quote:
I am trying to figure out how to export the results of a query to Excel with some specific formatting. Is this possible to do without using DTS?

One method could be



-- Inputs:File name of the excel document.

-- Returns:The query result, written directly in Excel document!

DECLARE @err_desc varchar(255)

DECLARE @i int

DECLARE @xls_cell varchar(100) 

DECLARE @OProp varchar(100) 

DECLARE @O_XLS int

DECLARE @ret_val int

DECLARE @xls int 

DECLARE @file varchar(255)



SET @i=0

EXEC @ret_val = sp_OACreate 'Excel.Application', @O_XLS OUTPUT, 4

IF @ret_val <> 0 or @@Error <> 0 GOTO com_handler

EXEC @ret_val = sp_OAMethod @O_XLS, 'Workbooks.Open', @xls OUTPUT,'C:\SUCCESS.xls'

-- in case you are creating new Excel file change the upper row with the following 

--EXEC @ret_val = sp_OAMethod @O_XLS, 'Workbooks.Add', @xls OUTPUT

IF @ret_val <> 0 or @@Error <> 0 GOTO com_handler

SET @file = 'C:\SUCCESS.xls'

DECLARE authors_cursor CURSOR FOR

SELECT au_lname FROM authors 

order by au_lname asc

OPEN authors_cursor

-- Perform the first fetch.

FETCH NEXT FROM authors_cursor

-- Check @@FETCH_STATUS to see if there are any more rows to fetch.

WHILE @@FETCH_STATUS = 0





    BEGIN

    -- This is executed as long as the previous fetch succeeds.

    set @i=@i+1

    set @OProp='Application.ActiveWorkbook.Sheets(1).Cells(' + cast(@i as varchar(2)) + ', 1).value'

    -- genaration of correct property addressing

    EXEC @ret_val = sp_OASetProperty @O_XLS, @OProp, @xls_cell

    -- set the value of the property 

    FETCH NEXT FROM authors_cursor into @xls_cell

    --IF @ret_val <> 0 or @@Error <> 0 GOTO com_handler

END 

CLOSE authors_cursor

DEALLOCATE authors_cursor

------------------------------êðàé íà ðàáîòàòà ñ êóðñîðà

EXEC @ret_val = sp_OAMethod @O_XLS, 'Application.ActiveWorkbook.Save',Null

-- in case you are creating new Excel file change the upper row with the following 

--EXEC @ret_val = sp_OAMethod @O_XLS,'Application.ActiveWorkbook.SaveAs',Null, 'c:\success.xls'

IF @ret_val <> 0 or @@Error <> 0 GOTO com_handler

EXEC @ret_val = sp_OAMethod @O_XLS, 'Quit'

-- I've commented the following line deliberately. SEE NOTE AT THE END

--EXEC sp_OADestroy @O_XLS

GOTO Done

com_handler:

CLOSE authors_cursor

DEALLOCATE authors_cursor

EXEC sp_oageterrorinfo @O_XLS, @ret_val out, @err_desc out

-- the last parameter gives small textual description of the error. It was very usefull!!

select @ret_val, @err_desc

EXEC @ret_val = sp_OAMethod @O_XLS, 'Quit'

EXEC sp_OADestroy @O_XLS

GOTO Done

Done:

-- I've commented the last line deliberately: It was causing the whole automation process in SQL to stop

-- so no futher automation instances could be created until the server was restarted. I read in MSDN that the instance

-- should be destroyed right after the SQL batch is completed, that is why I consider the last row not neccessary. BUT 

-- you should check it yourself

--EXEC sp_OAStop

Cheers,

Frank

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

planet115 Hall of Fame Points: 3181 More actions · Answer 2

If the users can connect directly to the database, not just via the webserver, an alternative would be to write a smattering of VBA to execute the stored procedure(s) from Excel and copy the returned recordset to the worksheet. (the ADO CopyFromRecordset method is pretty nifty IIRC).

OR .... My understanding is that Office documents can now be stored in pure XML. I guess you could write a single query to return an Excel document.

maouad SSC-Addicted Points: 426 More actions · Answer 3

Frank, thanks - that is just the sort of thing I was looking for.

planet115 - I think you're right. I know Excel docs can also be an html doc with an xls extension and the right meta tag.

Maybe that's a possibility as well, write the query to include HTML or XML formatting and dump it in a file with an xls extension from an sp. Will look at that as well. Thanks.

Mick.

Hans Lindgren SSChampion Points: 10496 More actions · Answer 4

Hello everyone!

This is a highly interesting topic for me since it seems like everyone want Excel reports for everything. Unfortunatly for me Excel Automation does not seem to be the fastest solution on this planet. I have never heard anything before about this capability of Excel to read HTML files nativly. I could have guessed it though :/ Is there anyone that knows how to write these meta tags to enable the document to be read by Excel? What versions of Excel supports it?

Thanx very much!

Regards, Hans!

Edit: Sorry, clarification: Retaining the .XLS and not displaying the user any msgbox about Stylesheets or conversions or anything when opening the file with Excel.

Edited by - hanslindgren on 08/28/2003 02:41:30 AM

Frank Kalis SSC Guru Points: 111183 More actions · Answer 5

Hi Hans,

quote:
This is a highly interesting topic for me since it seems like everyone want Excel reports for everything. Unfortunatly for me Excel Automation does not seem to be the fastest solution on this planet. I have never heard anything before about this capability of Excel to read HTML files nativly. I could have guessed it though :/ Is there anyone that knows how to write these meta tags to enable the document to be read by Excel? What versions of Excel supports it?

Excel is a fantastic tool to calculate numbers AND turn them into charts. Besides that, Excel could be viewed as least common denominator among normal users. I guess that's why it is so popular.

Starting with Excel 2000 there is support for HTML files implemented and I think I have read something about Office 2003 which provide full support for XML, although Excel XP was able to save files in XML format.

But why not use CSV format, which works even with older versions of Excel?

Cheers,

Frank

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

maouad SSC-Addicted Points: 426 More actions · Answer 6

hanslindgren,

If you write a file with .xls extension that looks something like this:

<HTML>

<HEAD>

</HEAD>

<BODY>

<TABLE>

<TR>

<TD>Another cell</TD>

</TR>

</TABLE>

</BODY>

</HTML>

When you open it Excel should display it as a a standard Excel document - if you include styles they should be picked up as well. You can also store formulas.

It works on my version - which is Excel2000.

Michael.

Edited by - maouad on 08/28/2003 03:01:27 AM

Hans Lindgren SSChampion Points: 10496 More actions · Answer 7

Hi Frank! Hi Michael!

Perfect tips 🙂 Unfortunatly for me our company have to support Excel 97 🙁 Next problem is that the legacy systems using some of the Excel files does not accept files named *.CSV

I saw that a TAB separated value-file renamed to .XLS works for Excel 2000 and Excel 97 SR-2.

Thanx again!

Regards, Hans!

planet115 Hall of Fame Points: 3181 More actions · Answer 8

Hi Hans

Err, I've looked it up and it seems that Office supports native XML as from Office 2002+ -- you can save and load an office document directly in XML format -- I assume that the VBA will support importing/exporting/manipulating document fragements as well ... however I've never used it.

I did quite a lot of development 'in' Excel a few years ago and we actually had some pretty hefty applications running only in VBA with connections to various data sources.

If you are running into performance issues in Excel that are not directly related to retrieving/pushing recordsets you may be able to tune your VBA code for dramatically different results. Things like using

WITH Object

.DoThis

.Dothat

END WITH

can have a major performace impact in loops, especially where the referenced object is a range/cell. Oftentimes there is a way to perform actions on a range of cells without For Each..ing through every cell which can be slow (e.g. using the .SpecialCells method).

It's quite surprising the different performance you get with different code approaches (basically I think some bits of the Excel object model are written to vastly different standards than others).

A site for excel tips I liked was:

http://www.j-walk.com

Edited by - planet115 on 08/28/2003 03:37:04 AM

Frank Kalis SSC Guru Points: 111183 More actions · Answer 9

quote:
Perfect tips 🙂 Unfortunatly for me our company have to support Excel 97 🙁 Next problem is that the legacy systems using some of the Excel files does not accept files named *.CSV

seems to be not very smart systems

quote:
I saw that a TAB separated value-file renamed to .XLS works for Excel 2000 and Excel 97 SR-2.

glad you've mentioned SR-2. Everything less will cause VERY funny results when you don't need them

As for planet115:

Syntax error near www.-j-walk.com

Cheers,

Frank

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Hans Lindgren SSChampion Points: 10496 More actions · Answer 10

Frank:

quote:
seems to be not very smart systems

Right, it is quite an obnoxious system 🙁

(Maybe you misspelled the URL? You wrote an extra '-' in your reply... Try http://www.j-walk.com)

Michael:

The HTML format also works for 97.x (Don't know the SR for that computer)!

Perfect!

Regards, Hans!

Edited by - hanslindgren on 08/28/2003 06:51:51 AM

Frank Kalis SSC Guru Points: 111183 More actions · Answer 11

quote:
(Maybe you misspelled the URL? You wrote an extra '-' in your reply... Try http://www.j-walk.com)

actually I have marked the '-' bold, to shows that it's there, but it can't be seen in my post. Anyway, you found the site

Cheers,

Frank

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

sgordon Old Hand Points: 359 More actions · Answer 12

Thanks for the tip of using Tab delimted,

renamed to XLS. It works great!

Using xp_sendmail, do any of you know a way to change the default attached message from

.txt to .XLS?

JeffE Mr or Mrs. 500 Points: 527 More actions · Answer 13

We use a template spreadsheet with a source workbook. The source workbook is updated when the user cliks on a link.

Private Sub CreateSpreadsheet(ByVal Parameter As Long)

On Error GoTo eh

Dim oExcel As Excel.Application

Dim oXLSheet As Excel.Worksheet

Dim sSQL As String

Dim oRS As ADODB.Recordset

Dim iCounter, iCounter2 As Long

Dim aRS As Variant

'//Grab source data from database

sSQL = "SELECT Data"

Set oRS = New ADODB.Recordset

oRS.Open sSQL, g_oConnDB

'//Open template spreadsheet and set the Source worksheet to be active

Set oExcel = New Excel.Application

oExcel.Workbooks.Open ("C:\Template.xls")

Set oXLSheet = oExcel.Worksheets("Source")

oXLSheet.Range("B4").CopyFromRecordset oRS

oRS.Close

Set oRS = Nothing

'//Save as new file and close template spreadsheet

With oExcel

.Worksheets("Input").Activate

.ActiveWorkbook.SaveAs ("C:\NewFile.xls")

.Quit

End With

Set oXLSheet = Nothing

Set oExcel = Nothing

Exit Sub

eh:

App.LogEvent ("An error occurred while generating NewFile.xls")

Exit Sub

End Sub

5409045121009-7368 SSCoach Points: 15740 More actions · Answer 14

Did something similar to JeffE.

Setup pivot table from dummy data range in spreadsheet. When user selects from combo (month) an stored prog is executed, returns data, repopulate / update data range and Excel update the rest. Users can then save month snapshot or beautify it more or do some what ifs, etc.