הבלוג של גרי רשף

09/11/2011

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

Filed under: Uncategorized — גרי רשף @ 21:07

 

העובדה שניתן לייצא נתונים מ-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

clip_image002

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

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

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

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

מודעות פרסומת

להגיב »

עדיין אין תגובות.

RSS feed for comments on this post. TrackBack URI

כתיבת תגובה

הזינו את פרטיכם בטופס, או לחצו על אחד מהאייקונים כדי להשתמש בחשבון קיים:

הלוגו של WordPress.com

אתה מגיב באמצעות חשבון WordPress.com שלך. לצאת מהמערכת / לשנות )

תמונת Twitter

אתה מגיב באמצעות חשבון Twitter שלך. לצאת מהמערכת / לשנות )

תמונת Facebook

אתה מגיב באמצעות חשבון Facebook שלך. לצאת מהמערכת / לשנות )

תמונת גוגל פלוס

אתה מגיב באמצעות חשבון Google+ שלך. לצאת מהמערכת / לשנות )

מתחבר ל-%s

בלוג בוורדפרס.קום.

%d בלוגרים אהבו את זה: