如何为SQL Server存储过程生成的HTML报告添加delta列条件格式高亮

你只需对现有代码做两处修改即可实现delta列的条件格式化效果：

1. 新增CSS样式类，分别定义delta为0和非0的单元格样式
2. 生成HTML表格行时，根据delta值动态给对应td节点绑定样式类

具体修改说明 #

步骤1：更新CSS样式部分 #

在原有的<style>标签内新增两个样式类，可根据需求自行调整色值：

.tg .delta-zero { background-color: #ff4d4d; color: #fff; } /* delta为0时红底白字 */
.tg .delta-nonzero { background-color: #4dff4d; color: #333; } /* delta非0时绿底黑字 */

步骤2：修改DELTA列的td生成逻辑 #

原来直接输出delta值的部分，调整为优先输出class属性，再输出值，利用FOR XML PATH的属性生成规则实现动态样式绑定。

修改后完整代码 #

SET @tableHTML1 = 
    + N' <p>This report is driven by <b>DWQC.dbo.mwsp_QC_EM_LS_EPMS_DAILY_CHECK  </b> stored procedure.
            Each METRIC in the QC is compared between the Source,House and Mart databases,  
            and the resulting delta is displayed </p>'
     
    +N'<style type="text/css">' + N'.tg  {border-collapse:collapse;border-spacing:0;border-color:#aaa;}'
    + N'.tg td{font-family:Arial, sans-serif;font-size:14px;padding:10px 5px;border-style:solid;border-width:1px;overflow:hidden;word-break:normal;border-color:#aaa;color:#333;background-color:#fff;}'
    + N'.tg th{font-family:Arial, sans-serif;font-size:14px;font-weight:normal;padding:10px 5px;border-style:solid;border-width:1px;overflow:hidden;word-break:normal;border-color:#aaa;color:#fff;background-color:#808080;}'
    + N'.tg .tg-9ajh{font-weight:bold;background-color:#808080}' + N'.tg .tg-hgcj{font-weight:bold;text-align:center}'
    /* 新增delta列条件样式 */
    + N'.tg .delta-zero { background-color: #ff4d4d; color: #fff; }'
    + N'.tg .delta-nonzero { background-color: #4dff4d; color: #333; }'
    + N'</style>' -- contains all the styling for the HTML report
    + N'<table class="tg">'  -- calls the table to included in the report
    + N'<th class="tg-hgcj">CHECK_ID </th>'
    + N'<th class="tg-hgcj">CHECK NAME </th>'
    + N'<th class="tg-hgcj">SOURCE_COUNT</th>'
    + N'<th class="tg-hgcj">TARGET_COUNT</th>'
    + N'<th class="tg-hgcj">DELTA</th>' 
    + N'</tr>'   
    + CAST ( (select  -- checks for columns that could be seen in attached report
                td=CHECK_ID,'',
                td=CHECK_NAME,'',   
                td=SOURCE_COUNT,'', 
                td=TARGET_COUNT,'', 
                /* 新增动态class属性绑定 */
                'td/@class' = CASE WHEN DELTA = 0 THEN 'delta-zero' ELSE 'delta-nonzero' END,
                td=DELTA,'' 
            from( Select top 1000
            isnull(CHECK_ID,0) as CHECK_ID,     
            isnull(CHECK_NAME,0) as CHECK_NAME, 
            isnull(SOURCE_COUNT,0) as SOURCE_COUNT, 
            isnull(TARGET_COUNT,0) as TARGET_COUNT, 
            isnull(DELTA,0) as DELTA 
            from #table  --contains the data to be used in the report; temporarily naming it "table"
            order by RID)Q
                    FOR XML PATH('tr'),
                     TYPE
                    ) AS NVARCHAR(MAX))
                    + N'</table>' 

内容的提问来源于stack exchange，提问作者DR222