前几天,我在此Symantec链接中找到了一个很好的存储过程,将TSQL表的结果转换为HTML表。没有任何CSS
参数,结果效果很好。我倾向于发送带有SQL Server结果的大量电子邮件,因此这非常有帮助。
现在我能够使它起作用,我正在尝试为桌子添加一些样式。问题是我不确定如何将CSS class
作为参数工作。
例如,这是我应该如何称呼SP:
EXEC dbo.CustomTable2HTMLv3 'Buffy',@HTML1 OUTPUT,'class="horizontal"',0
问题是我不知道CSS class
horizontal
来自何处。在链接中,我可以看到实际的CSS
,但是如何读取此过程?
CSS看起来像这样:
table.horizontal tr:first-child {
background-color: Gray!important;
font-weight: bold;
color: #fff;
}
这是存储过程:
CREATE PROCEDURE [dbo].[CustomTable2HTMLv3] (
@TABLENAME NVARCHAR(500),
@OUTPUT NVARCHAR(MAX) OUTPUT,
@TBL_STYLE NVARCHAR(1024) = '',
@ALIGNMENT INT =0 )
AS
-- Author: Ian Atkin (ian.atkin@ict.ox.ac.uk)
-- Description
-- Stored Procedure to take an arbitraty temporary table and return
-- the equivalent HTML string .
-- Version History
-- 1.0 - v1 Release For Symantec Connect
-- 3.0 - v3 Release for Symantec connect.
-- Table to be outputed both horizonally and vertically. IsNull used
-- on cell value output to prevent NULLs creaping into HTML string
-- @exec_str stores the dynamic SQL Query
-- @ParmDefinition stores the parameter definition for the dynamic SQL
DECLARE @exec_str NVARCHAR(MAX)
DECLARE @ParmDefinition NVARCHAR(500)
IF @ALIGNMENT=0
BEGIN
--We need to use Dynamic SQL at this point so we can expand the input table name parameter
SET @exec_str= N'
DECLARE @exec_str NVARCHAR(MAX)
DECLARE @ParmDefinition NVARCHAR(500)
DECLARE @DEBUG INT
SET @DEBUG=0
IF @DEBUG=1 Print ''Table2HTML -Horizontal alignment''
--Make a copy of the original table adding an indexing column. We need to add an index column to the table to facilitate sorting so we can maintain the
--original table order as we iterate through adding HTML tags to the table fields.
--New column called CustColHTML_ID (unlikely to be used by someone else!)
--
select CustColHTML_ID=0,* INTO #CustomTable2HTML FROM ' + @TABLENAME + '
IF @DEBUG=1 PRINT ''Created temporary custom table''
--Now alter the table to add the auto-incrementing index. This will facilitate row finding
DECLARE @COUNTER INT
SET @COUNTER=0
UPDATE #CustomTable2HTML SET @COUNTER = CustColHTML_ID=@COUNTER+1
IF @DEBUG=1 PRINT ''Added counter column to custom table''
-- @HTMLROWS will store all the rows in HTML format
-- @ROW will store each HTML row as fields on each row are iterated through
-- using dymamic SQL and a cursor
-- @FIELDS will store the header row for the HTML Table
DECLARE @HTMLROWS NVARCHAR(MAX) DECLARE @FIELDS NVARCHAR(MAX)
SET @HTMLROWS='''' DECLARE @ROW NVARCHAR(MAX)
-- Create the first HTML row for the table (the table header). Ignore our indexing column!
SELECT @FIELDS=COALESCE(@FIELDS, '' '','''')+''<td>'' + name + ''</td>''
FROM tempdb.sys.Columns
WHERE object_id=object_id(''tempdb..#CustomTable2HTML'')
AND name not like ''CustColHTML_ID''
SET @FIELDS=@FIELDS + ''</tr>''
IF @DEBUG=1 PRINT ''table fields: '' + @FIELDS
-- @ColumnName stores the column name as found by the table cursor
-- @maxrows is a count of the rows in the table, and @rownum is for marking the
-- ''current'' row whilst processing
DECLARE @ColumnName NVARCHAR(500)
DECLARE @maxrows INT
DECLARE @rownum INT
--Find row count of our temporary table
SELECT @maxrows=count(*) FROM #CustomTable2HTML
--Create a cursor which will look through all the column names specified in the temporary table
--but exclude the index column we added (CustColHTML_ID)
DECLARE col CURSOR FOR
SELECT name FROM tempdb.sys.Columns
WHERE object_id=object_id(''tempdb..#CustomTable2HTML'')
AND name not like ''CustColHTML_ID''
ORDER BY column_id ASC
--For each row, generate dymanic SQL which requests the each column name in turn by
--iterating through a cursor
SET @rowNum=1
SET @ParmDefinition=N''@ROWOUT NVARCHAR(MAX) OUTPUT,@rowNum_IN INT''
While @rowNum <= @maxrows
BEGIN
SET @HTMLROWS=@HTMLROWS + ''<tr>''
OPEN col
FETCH NEXT FROM col INTO @ColumnName
IF @DEBUG=1 Print ''@ColumnName: '' + @ColumnName
WHILE @@FETCH_STATUS=0
BEGIN
--Get nth row from table
--SET @exec_str=''SELECT @ROWOUT=(select top 1 ['' + @ColumnName + ''] from (select top '' + cast(@rownum as varchar) + '' * from #CustomTable2HTML order by CustColHTML_ID ASC) xxx order by CustColHTML_ID DESC)''
SET @exec_str=''SELECT @ROWOUT=(select ['' + @ColumnName + ''] from #CustomTable2HTML where CustColHTML_ID=@rowNum_IN)''
IF @DEBUG=1 PRINT ''@exec_str: '' + @exec_str
EXEC sp_executesql
@exec_str,
@ParmDefinition,
@ROWOUT=@ROW OUTPUT,
@rowNum_IN=@rownum
IF @DEBUG=1 SELECT @ROW as ''@Row''
SET @HTMLROWS =@HTMLROWS + ''<td>'' + IsNull(@ROW,'''') + ''</td>''
FETCH NEXT FROM col INTO @ColumnName
END
CLOSE col
SET @rowNum=@rowNum +1
SET @HTMLROWS=@HTMLROWS + ''</tr>''
END
SET @OUTPUT=''''
IF @maxrows>0
SET @OUTPUT= ''<table ' + @TBL_STYLE + '>'' + @FIELDS + @HTMLROWS + ''</table>''
DEALLOCATE col
'
END
ELSE
BEGIN
--This is the SQL String for table columns to be aligned on the vertical
--So we select a table column, and then iterate through all the rows for that column, this forming
--one row of our html table.
SET @exec_str= N'
DECLARE @exec_str NVARCHAR(MAX)
DECLARE @ParmDefinition NVARCHAR(500)
DECLARE @DEBUG INT
SET @DEBUG=0
IF @DEBUG=1 Print ''Table2HTML -Vertical alignment''
--Make a copy of the original table adding an indexing column. We need to add an index column to the table to facilitate sorting so we can maintain the
--original table order as we iterate through adding HTML tags to the table fields.
--New column called CustColHTML_ID (unlikely to be used by someone else!)
--
select CustColHTML_ID=0,* INTO #CustomTable2HTML FROM ' + @TABLENAME + '
IF @DEBUG=1 PRINT ''CustomTable2HTMLv2: Modfied temporary table''
--Now alter the table to add the auto-incrementing index. This will facilitate row finding
DECLARE @COUNTER INT
SET @COUNTER=0
UPDATE #CustomTable2HTML SET @COUNTER = CustColHTML_ID=@COUNTER+1
-- @HTMLROWS will store all the rows in HTML format
-- @ROW will store each HTML row as fields on each row are iterated through
-- using dymamic SQL and a cursor
DECLARE @HTMLROWS NVARCHAR(MAX)
DECLARE @ROW NVARCHAR(MAX)
SET @HTMLROWS=''''
-- @ColumnName stores the column name as found by the table cursor
-- @maxrows is a count of the rows in the table
DECLARE @ColumnName NVARCHAR(500)
DECLARE @maxrows INT
--Find row count of our temporary table
--This is used here purely to see if we have any data to output
SELECT @maxrows=count(*) FROM #CustomTable2HTML
--Create a cursor which will iterate through all the column names in the temporary table
--(excepting the one we added above)
DECLARE col CURSOR FOR
SELECT name FROM tempdb.sys.Columns
WHERE object_id=object_id(''tempdb..#CustomTable2HTML'')
AND name not like ''CustColHTML_ID''
ORDER BY column_id ASC
--For each **HTML** row, we need to for each iterate through each table column as the outer loop.
--Once the column name is identified, we use Coalesc to combine all the column values into a single string.
SET @ParmDefinition=N''@COLOUT NVARCHAR(MAX) OUTPUT''
OPEN col
FETCH NEXT FROM col INTO @ColumnName
WHILE @@FETCH_STATUS=0
BEGIN
--Using current column name, grab all column values and combine into an HTML cell string using COALESCE
SET @ROW=''''
SET @exec_str='' SELECT @COLOUT=COALESCE(@COLOUT + ''''</td>'''','''''''') + ''''<td>'''' + Cast(IsNull(['' + @ColumnName + ''],'''''''') as nvarchar(max)) from #CustomTable2HTML ''
IF @DEBUG=1 PRINT ''@exec_str: '' + @exec_str
EXEC sp_executesql
@exec_str,
@ParmDefinition,
@COLOUT=@ROW OUTPUT
SET @HTMLROWS =@HTMLROWS + ''<tr>'' + ''<td>'' + @ColumnName + ''</td>'' + @ROW + ''</tr>''
IF @DEBUG=1 SELECT @ROW as ''Current Row''
IF @DEBUG=1 SELECT @HTMLROWS as ''HTML so far..''
FETCH NEXT FROM col INTO @ColumnName
END
CLOSE col
SET @OUTPUT=''''
IF @maxrows>0
SET @OUTPUT= ''<table ' + @TBL_STYLE + '>'' + @HTMLROWS + ''</table>''
DEALLOCATE col
'
END
DECLARE @ParamDefinition nvarchar(max)
SET @ParamDefinition=N'@OUTPUT NVARCHAR(MAX) OUTPUT'
--Execute Dynamic SQL. HTML table is stored in @OUTPUT which is passed back up (as it's
--a parameter to this SP)
EXEC sp_executesql @exec_str,
@ParamDefinition,
@OUTPUT=@OUTPUT OUTPUT
RETURN 1
因此,您提供的链接中提到了以下内容:
"为了帮助您样式桌子,在zip下载中,我也 包括一个HTML文件,该文件的头部有一个嵌入式样式表 元素。"
因此,如果您在那里提到html file
,您会看到他们已声明了html
本身内部的样式。您只能通过SQL创建表,如果您愿意,请将其作为参数的类名称连接,但是您需要生成一个完整的HTML文件,其中包含CSS(或已引用,无关紧要(让您的桌子样式。
正如我在评论中提到的,如果表将水平或垂直打印,仅由@alignment
属性定义,而不是由CSS class
定义。(您可以在存储过程代码中看到这一点(