go backsqlschool blogs list

Export data to Excel File using pure T-SQL

by Antonios Chatzipavlis

O SQL Server έχει αρκετά εργαλεία για να κάνεις export & import data. Από τα απλά T-SQL BULK INSET ή το κλασσικό BCP μέχρι τα DTS (Data Transformation Services)  και σήμερα τα SSIS ( SQL Server Integration Services).

Όμως υπάρχουν αρκετές περιπτώσεις που δεν θα ήθελες να εμπλακείς με αυτά.

Ένα σενάριο το οποίο κάποια στιγμή σε ένα project μου έτυχε ήταν η απαίτηση να πατάει ο χρήστης ένα κουμπί και να γίνονται τα δεδομένα export σε Excel αρχείο.

Για να δούμε λοιπόν την υλοποίηση του χωρίς να χρησιμοποιήσω κάτι από τα παραπάνω αλλά κάνοντας χρήση αγνού T-SQL και της δυνατότητας που έχει ο SQL Server με τους Linked Servers.

Βασική προϋπόθεση για να μπορέσει η λύση να δουλέψει είναι να δημιουργήσεις ένα άδειο αρχείο excel στο server με συγκεκριμένο όνομα και σε συγκεκριμένο directory. Ας το πούμε empty.xls και ας το βάλουμε στο c:\temp. Αυτό θα λειτουργεί σαν template για τα αρχεία που δα δημιουργήσουμε παρακάτω.

Όμως δεν φτάνει μόνο αυτό.

Θα πρέπει να ανοίξω το excel αρχείο και να δώσω ένα όνομα στο φύλλο (πχ MyData).

Ακόμα θα πρέπει να γνωρίζω πόσα πεδία θα βάλω στο excel φύλλο πχ αν ξέρω ότι θα βάλω 2 πεδία θα πρέπει στα πρώτα 2 κελιά της πρώτης γραμμής να δώσω κάποιο όνομα πχ A,B.

Φτιάχνω την stored procedure που θα κάνει όλη την δουλειά

create proc usp_write2Excel (@fileName varchar(100),@NumOfColumns tinyint,@query varchar(200))

as

begin

declare @dosStmt varchar(200)

declare @tsqlStmt varchar(500)

declare @colList varchar(200)

declare @charInd tinyint

set nocount on

--
-- COLUMNS LIST CREATION

--
set @charInd=0

set @colList = 'A'

while @charInd < @NumOfColumns - 1

begin

set @charInd = @charInd + 1

set @colList = @colList + ',' + char(65+ @charInd)

end

-- CREATE MY EXCEL FILE BY COPING EXCEL TEMPLATE

set @dosStmt = ' copy c:\temp\empty.xls ' + @fileName

exec master..xp_cmdshell @dosStmt

-- Create a "temporary" linked server to that file in order to "Export" Data

EXEC sp_addlinkedserver 'ExcelSource','Jet 4.0','Microsoft.Jet.OLEDB.4.0',@fileName,NULL,'Excel 5.0'

-- construct a T-SQL statement that will actually export the query results-- to the Table in the target linked server

set @tsqlStmt = 'Insert ExcelSource...[MyData$] ' + ' ( ' + @colList + ' ) '+ @query

-- execute dynamically the TSQL statement

exec (@tsqlStmt)

-- drop the linked server

EXEC sp_dropserver 'ExcelSource'

set nocount off

end

GO

Execute sp

exec usp_write2Excel 'c:\temp\Customers.xls',2,'select customerid,companyname from northwind.dbo.customers'


 
Αυτό ήταν έχεις τα δεδομένα σου σε Excel!!!
Ημερομηνία: 19 August 2009 14:10
Αξιολόγηση:
Tags:
Share it:

Σχόλια - Comments

user-gravatar

Στις 05 Nov 2012 @ 4:18 PM o/η Γιώργος Ροζάκης έγραψε:

Αντώνη καλησπέρα,προσπαθώ να κάνω export data από έναν πίνακα σε αρχείο .xlsΈτρεξα των κώδικα που έχεις εδώ: http://www.sqlschool.gr/blog/export-data-to-excel-file-using-pure-t-sql-91.aspxΈχω δημιουργήσει το αρχείο excel ακριβώς όπως λες, δημιουργήθηκε σωστά και η stored procedure χωρίς σφάλμα.Όταν πάει να τρέξει τις δύο τελευταίες γραμμές όμως μου βγάζει αφενός μεν για την εντολή Execute sp ότι:Msg 2812, Level 16, State 62, Line 1Could not find stored procedure 'sp'.και αφετέρου για την: exec usp_write2Excel 'c:\temp\Furnace.xls',2,'select Furnace_ID,FurnaceHow from extrusion.dbo.furnace'Msg 15028, Level 16, State 1, Procedure sp_MSaddserver_internal, Line 89The server 'ExcelSource' already exists.OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "ExcelSource" returned message "Cannot start your application. The workgroup information file is missing or opened exclusively by another user.".Msg 7399, Level 16, State 1, Line 1The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "ExcelSource" reported an error. Authentication failed.Msg 7303, Level 16, State 1, Line 1Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "ExcelSource".Τι κάνω λάθος; Stored procedure σκέτο 'sp' όντως δεν υπάρχει στη βάση μου πάντως...

Αφήστε το σχόλιο σας - Leave your comment

Τα σχόλια έχουν κλείσει.
Επιτρέπονται μόνο τα σχόλια από τα μέλη του SqlSchool.gr.


newsletter subscription

Εάν επιθυμείτε να λαμβάνετε ενημέρωση από εμάς, δώστε μας το e-mail σας.
PASS chapter logo
Official Professional Association for SQL Server (PASS) chapter for Greece
Join to PASS