יצירת גליונות Excell מעוצבים

 

העובדה שניתן לייצא נתונים מ-SQL Server לאקסל או לייבא מאקסל ל-SQL Server- אין בה כל חידוש, ולכל מי שעבד עם מערכות BI זה מובן מאליו, ויש שלל דרכים לעשות זאת.
החידוש הוא שניתן לא רק לייצא נתונים לאקסל, אלא גם לעצב את הגיליון כרצוננו.
קודם כל- נאפשר שימוש ב-XP_CmdShell וב-OLE:

Exec SP_Configure 'show advanced options',1;

Go

Reconfigure;

Go

Exec sp_configure 'xp_cmdshell', 1

Go

ReConfigure

Go

Exec SP_Configure 'Ole Automation 

Procedures',1;

Go

Reconfigure;

Go

והקוד המוער:

Use AdventureWorksDW;

Go

--יצירת קובץ האקסל

Declare @Connection Int,

@Val Int;

Exec XP_CmdShell 'If Exist c:\Tmp\MyExcell.xls Del/Q c:\Tmp\MyExcell.xls';

Exec SP_OACreate 'ADODB.Connection', @Connection Out;

Exec sp_OASetProperty @Connection,

                    'ConnectionString',

                    'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\Tmp\MyExcell.xls;Extended Properties=Excel 8.0';

Exec SP_OAMethod @Connection, 'Open' ;

Exec SP_OAMethod @Connection, 'Execute', @Val out , 'Create table MyExcellTable (ID Number, Name Text, Price Number)';

Exec SP_OADestroy @Connection;

Go

Declare @LastRow Int, --יש למצוא את השורה אחרונה עבור הסיכומים

        @LastColumn Int,

        @Command Varchar(255), --יצירת פקודות דינאמיות

        @File Varchar(255), --שם קובץ האקסל

        @Excell Int, --Handle-משתנים טכניים המקבלים את מספר ה

        @Cell Int,

        @Workbook Int,

        @WorkSheet Int;

Set @File='c:\Tmp\MyExcell.xls';

--הכנסת נתונים לאקסל

Insert into OpenRowSet('Microsoft.Jet.OLEDB.4.0',

                    'Excel 8.0;Database=C:\Tmp\MyExcell.xls;', --פקודה זו אינה יכולה להיות דינאמית

                    'SELECT * FROM MyExcellTable')

Select Top 20 ProductKey ID, --מי שרוצה - יכול לשלוף את כל הטבלה

        EnglishProductName Name,

        SafetyStockLevel Price

From    DimProduct

Order By Name;

--הגדרת הקשר לאקסל לצורך עיצוב

Exec dbo.sp_OACreate 'Excel.Application', @Excell Output;

Exec master.dbo.sp_OASetProperty @Excell, 'ScreenUpdating', 'False';

Exec master.dbo.sp_OASetProperty @Excell, 'DisplayAlerts', 'False';

Exec master.dbo.SP_OAMethod @Excell, 'Workbooks', @Workbook Output;

Exec master.dbo.SP_OAMethod @Workbook, 'Open', @Workbook Output, @File;

Exec master.dbo.SP_OAMethod @Workbook, 'ActiveSheet', @WorkSheet Output;

Exec master.dbo.sp_OAGetProperty @WorkSheet, 'Cells.SpecialCells(11).Row', @LastRow Output;

Exec master.dbo.sp_OAGetProperty @WorkSheet, 'Cells.SpecialCells(11).Column', @LastColumn Output;

set @LastRow=@LastRow+2

--עיצוב הכותרת

Exec master.dbo.sp_OASetProperty @WorkSheet, 'Range("A1:C1").font.bold', 1 ;

Exec master.dbo.sp_OASetProperty @WorkSheet, 'Range("A1:C1").font.ColorIndex', 3; --http://dmcritchie.mvps.org/excel/colors.htm

--נוסחת סיכום בעמודה הראשונה

Exec master.dbo.sp_OAGetProperty @WorkSheet, 'Cells', @Cell Output, @LastRow, 1;

Set @Command='=Count(A2:A'+Cast(@LastRow-2 As Varchar)+')';

Exec master.dbo.sp_OASetProperty @Cell, 'Value', @Command;

Exec master.dbo.sp_OASetProperty @Cell, 'NumberFormat', '#,###';

--נוסחת סיכום בעמודה השלישית

Exec master.dbo.sp_OAGetProperty @WorkSheet, 'Cells', @Cell Output, @LastRow, 3;

Set @Command='=Sum(C2:C'+Cast(@LastRow-2 As Varchar)+')';

Exec master.dbo.sp_OASetProperty @Cell, 'Value', @Command;

--עיצוב שורת הסיכום

Set @Command='Range("A'+Cast(@LastRow As Varchar)+':C'+Cast(@LastRow As Varchar)+'").Font.Bold';

Exec master.dbo.sp_OASetProperty @WorkSheet, @Command, 'True';

Set @Command='Range("A'+Cast(@LastRow As Varchar)+':C'+Cast(@LastRow As Varchar)+'").Font.ColorIndex';

Exec master.dbo.sp_OASetProperty @WorkSheet, @Command, 5;

--עצוב העמודה השלישית

Set @Command='Range("C2:C'+Cast(@LastRow As Varchar)+'").NumberFormat';

Exec master.dbo.sp_OASetProperty @WorkSheet, @Command, '#,###';

--לכל העמודות AutoFit

Exec master.dbo.SP_OAMethod @WorkSheet, 'Columns.AutoFit';

--שמירה וסיום מסודר

Exec master.dbo.sp_OADestroy @Cell;

Exec master.dbo.SP_OAMethod @Workbook,'SaveAs', null, @File, -4143;

Exec master.dbo.SP_OAMethod @Workbook, 'Close';

Exec master.dbo.SP_OAMethod @Excell, 'Quit';

Go

הערות: מסיבה לא ברורה – יצירת הקובץ צריכה להיות ב-Batch נפרד מזה הכנסת הנתונים והעיצוב. לא ברור לי לגמרי למה (אולי כי מדובר ב-Connections שונים).

כפי שאפשר לראות בצילום המסך- הכותרת ושורת הסיכום מעוצבות, בשורת הסיכום יש נוסחאות, עמודה C מעוצבת והתבצע AutoFit לעמודות.

במספר מקומות השתמשתי ב-Command@ כדי ליצור פקודה לפני הפעלתה- אפשר להוסיף פקודות Print כדי להתרשם מהן.

רוב ההסברים מופיעים בתוך הקוד.