content top

תרגום טבלאות לפורמט HTML

Moti Shapira

במסגרת עבודתי קיבלתי מספר פעמים דרישה לשלוח טבלאות בפורמט HTML.

את המשלוח ביצעתי בעזרת Database Mail.

אך בכל פעם נתקלתי באתגר של תרגום הטבלה ל HTML .

לצורך כך פיתחתי Stored Procedure שמקבל את שם הטבלה ופרמטרים לעיצוב הטבלה ומחזירה ערך טקסטואלי מסוג nvarchar(max)

המכיל את הטבלה מתורגמת ל HTML.

התהליך מתאים לכל טבלה,כולל טבלאות זמניות.

להל"ן דרך הפעולה של ה SP:

פרמטרים להרצה:

שם הפרמטר

סוג

ערך ברירת מחדל

@DBNAME

NVARCHAR(100)

'tempdb'

@SCEMANAME

NVARCHAR(100)

"

@TABLENAME

NVARCHAR(100)

 

@TabalHeaderStyle

NVARCHAR(100)

"

@TableStyle

NVARCHAR(100)

' border=1 cellpadding=1 cellspacing=1'

@HTMLData (OUTPUT)

NVARCHAR(MAX)

 

 

אופן הפעולה של התהליך:

בשלב ראשון נדרש להוציא רשימה של השדות וסוג הנתונים מטבלת sys.columns של בסיס הנתונים שבו נמצאת הטבלה.

        DECLARE @COLLIST TABLE (

                RN INT identity(1, 1)

                ,NAME SYSNAME

                ,column_id int

                ,max_length INT

                )

 

        SET @CMD = 'Select DISTINCT
columns.name,column_id

        ,case when (columns.max_length=-1 or columns.max_length>4000)
then 4000 

                                when types.name="datetime" then 30

                                when types.name="int" then 8

                                when types.name="bigint" then 16

                                when types.name="uniqueidentifier"
then 40

                                else columns.max_length end as
max_length

from ' + @DBNAME + '.sys.columns  Left Join
sys.types on columns.system_type_id=types.system_type_id

where Object_id= object_id("' + @DBNAME + '.' + @SCEMANAME + '.' + @TABLENAME + "')

order by column_id

';

 

        INSERT INTO @COLLIST

        EXEC sp_sqlexec @CMD

 תשאול sys.columns מתבצע בעזרת SQL דינאמי ומוכנס אל משתנה טבלאי @COLLIST .

 

בשלב שני מבוצעת בנייה של כותרת טבלת ה HTML, בעזרת Select for XML path.

       SET @TableHeader
= STUFF((

                                SELECT NAME AS 'td'

                                FROM @COLLIST

                                FOR XML path(")

                                ), 1, 0, ") + '</tr>'

 

בשלב שלישי מבוצע תשאול של הטבלה בעזרת SQL דינאמי עם Select for XML Path .

        SELECT @nsql = 'select
convert(nvarchar(max),(SELECT  "@@@@"+'
+
STUFF((

SELECT ',isnull(convert(nvarchar('
+ convert(NVARCHAR(32), max_length) + '),' + NAME + ')," ")+"####" AS
"td" '

                                FROM @COLLIST

                                FOR XML PATH(")

), 1,
1, ") + ' FROM ' + @DBNAME + '.' + @SCEMANAME + '.' + @TABLENAME + ' for xml path(""))+"$$$$",1)'

 

        INSERT INTO #Outputdata

        EXEC sp_executesql @nsql

 

תוצאת השאילתה מוכנסת לטבלה זמנית #Outputdata  .

בסיום התהליך מתבצעת בנייה של פלט ה HTML והוספת פרמטרי העיצוב,
והתוצאה מוחזרת למשתמש.

SELECT TOP
1 @TableBody = '<table '+@TableStyle+'><tr '+@TabalHeaderStyle+'>' + @TableHeader + isnull(replace(replace(replace(replace(Data, '####</td>$$$$',
'</td></tr>'),
'<td>’@@@@',
'<tr><td>'),
'####', '</td><td>'),
'</td><td></td>', '</td></tr>'), ") + '</table>'

        FROM #Outputdata

        DROP TABLE #Outputdata

        SET @HTMLData=  @TableBody

 

להלן דוגמת הרצה:

declare @@HTMLDataOut nvarchar(max)

declare @BODY nvarchar(max)

 

 

exec
Table2HTML @DBNAME  = 'tempdb'

       ,@SCEMANAME  = "

       ,@TABLENAME ='#test'

       ,@TabalHeaderStyle='BGColor=lightGreen'

       ,@HTMLData=@@HTMLDataOut OUTPUT;

SET
@BODY=@@HTMLDataOut

 

מצורף סקריפט של ה Stored Procedure

TableToHTML.sql

להל"ן תוכן הקובץ:

CREATE PROCEDURE Table2HTML (
@DBNAME NVARCHAR(100) = 'tempdb'
,@SCEMANAME NVARCHAR(100) = "
,@TABLENAME NVARCHAR(100)
,@TabalHeaderStyle NVARCHAR(100) ="
,@TableStyle NVARCHAR(100)=' border=1 cellpadding=1 cellspacing=1'
,@HTMLData nvarchar(max) OUTPUT
)
AS
BEGIN
DECLARE @CMD NVARCHAR(4000)
,@nsql NVARCHAR(4000)
,@col_list NVARCHAR(4000)
,@TableHeader NVARCHAR(4000)
,@TableBody NVARCHAR(max)
,@ParmDefinition NVARCHAR(500)
,@@HTMLData nvarchar(max)
CREATE TABLE #Outputdata (Data NVARCHAR(max))
DECLARE @COLLIST TABLE (
RN INT identity(1, 1)
,NAME SYSNAME
,column_id int
,max_length INT
)

SET @CMD = 'Select DISTINCT columns.name,column_id
,case when (columns.max_length=-1 or columns.max_length>4000) then 4000
when types.name="datetime" then 30
when types.name="int" then 8
when types.name="bigint" then 16
when types.name="uniqueidentifier" then 40
else columns.max_length end as max_length
from ' + @DBNAME + '.sys.columns Left Join sys.types on columns.system_type_id=types.system_type_id
where Object_id= object_id("' + @DBNAME + '.' + @SCEMANAME + '.' + @TABLENAME + "')
order by column_id
';

INSERT INTO @COLLIST
EXEC sp_sqlexec @CMD
–select * from @COLLIST
SET @TableHeader = STUFF((
SELECT NAME AS 'td'
FROM @COLLIST
FOR XML path(")
), 1, 0, ") + '</tr>'

SELECT @nsql = 'select convert(nvarchar(max),(SELECT "@@@@"+' + STUFF((
SELECT ',isnull(convert(nvarchar(' + convert(NVARCHAR(32), max_length) + '),' + NAME + ')," ")+"####" AS "td" '
FROM @COLLIST
FOR XML PATH(")
), 1, 1, ") + ' FROM ' + @DBNAME + '.' + @SCEMANAME + '.' + @TABLENAME + ' for xml path(""))+"$$$$",1)'

INSERT INTO #Outputdata
EXEC sp_executesql @nsql

SELECT TOP 1 @TableBody = '<table '+@TableStyle+'><tr '+@TabalHeaderStyle+'>' + @TableHeader + isnull(replace(replace(replace(replace(Data, '####</td>$$$$', '</td></tr>'), '<td>@@@@', '<tr><td>'), '####', '</td><td>'), '</td><td></td>', '</td></tr>'), ") + '</table>'
FROM #Outputdata
DROP TABLE #Outputdata
SET @HTMLData= @TableBody
END


7 תגובות על “תרגום טבלאות לפורמט HTML”

  1. מאת עמית:

    אחלה פוסט !

  2. מאת גיא דבח:

    מעולה!
    כבר יש לי שימוש עבור זה אצל אחד הלקוחות שלי :)

    תודה מוטי!

  3. מאת mkamenko:

    Great article!!! but I can’t download attachment 

  4. מאת שמאי:

    שלום, לא ניתן להוריד את הסקריפט המצורף. בעת שמקליקים על הקישור מקבלים שגיאת הרשאות. האם אפשר לשחרר להורדה ושימוש לפי צורך ?

  5. מאת מוטי שפירא:

    צורף תוכן הקובץ.

  6. מאת ניר שזיפי:

    אני יכול להישבע שראיתי את הקוד הזה כבר בעבר, בטוח שאתה כתבת את זה?

  7. מאת מוטי שפירא:

    ניר שלום.
    לכל DBA יש סט כלים ויש מספר סקריפטים דומים שפותחו ע"י DBA ים שונים. הסקריפט הנ"ל פותח על ידי לפני שלוש שנים.
    מאז פיתוחו ישמתי פתרונות הכוללים שימוש בו אצל מספר לקוחות.

    כמו כן הסקריפט הופץ לשימוש בקרב היועצים לפחות בשתי חברות שעבדתי בהן.
    לכן לא אתפלא אם באמת ראית את הקוד הנ"ל או קוד דומה לו.

כתיבת תגובה

האימייל לא יוצג באתר. (*) שדות חובה מסומנים

1 × 3 =