Как администратор баз данных SQL Server, вам часто приходится отправлять срочные отчеты заинтересованным лицам. Однако, стандартный табличный формат может быть скучным и непривлекательным. В этой статье мы рассмотрим, как улучшить ваши отчеты, реализовав зебра-полосы и подсветку ячеек на основе их значений.
Проблема
При использовании хранимой процедуры sp_send_mail для отправки табличных отчетов, стандартный формат не имеет визуальной привлекательности. Вы хотите улучшить отчет, реализовав следующие требования:
- Добавить зебра-полосы для строк таблицы, чередуя фоновые цвета для нечетных и четных строк.
- Применить разные цвета текста к ячейкам в зависимости от их значений. Например, если значение ячейки превышает 10 000, оно должно отображаться зеленым цветом. Если значение равно или меньше 5 000, оно должно отображаться красным цветом.
Решение
Для достижения желаемого форматирования мы составим отчет в формате HTML с использованием хранимой процедуры sp_send_dbmail. Это позволяет нам использовать HTML и CSS для создания визуально привлекательных отчетов.
Сначала нам нужно сгенерировать HTML-таблицу с желаемым форматированием. Мы можем сделать это, изменяя SQL-запрос и используя встроенный CSS. Вот основные моменты алгоритма:
- Мы будем подсчитывать количество вхождений строки “
<tr>” в XML-представлении таблицы. Строки с нечетными номерами будут заменены на “<tr id="odd">“, где “id="odd"” – это тег CSS, определенный во встроенном CSS. - Мы изменим SQL-запрос, чтобы добавить специальные строки (“
zr” и “zg“) к значениям ячеек на основе заданных порогов. Например, если значение меньше или равно 5 000, оно будет преобразовано в “zr5000“. Если значение превышает 10 000, оно будет преобразовано в “zg12390“. - Мы будем использовать функцию
replace()для замены “<td>zr” на “<td id="r">” и “<td>zg” на “<td id="g">“. Это применит желаемые цвета текста к ячейкам.
После получения измененной HTML-таблицы мы можем включить ее в тело электронной почты, отправленной с помощью sp_send_dbmail. Письмо будет отформатировано как HTML-документ, с таблицей встроенной в него.
Пример кода
Вот пример кода T-SQL, реализующего решение:
-- Определение CSS внутри раздела head HTML
DECLARE @body VARCHAR(MAX) = '
<html>
<head>
<style>
#g {color: green;}
#r {color: red;}
#odd {background-color: lightgrey}
</style>
</head>';
-- Генерация основной строки XML
DECLARE @xml NVARCHAR(MAX);
SET @xml = CAST((
SELECT [Rank] AS 'td', '', [Player Name] AS 'td', '',
CASE
WHEN [Ranking Points] <= 5000 THEN 'zr' + CAST([Ranking Points] AS VARCHAR(30))
WHEN [Ranking Points] >= 10000 THEN 'zg' + CAST([Ranking Points] AS VARCHAR(30))
ELSE CAST([Ranking Points] AS VARCHAR(30))
END AS 'td', '', Country AS 'td'
FROM #Temp
ORDER BY Rank
FOR XML PATH('tr'), ELEMENTS
) AS NVARCHAR(MAX));
-- Применение CSS и зебра-полос
SET @xml = REPLACE(@xml, '<td>zg', '<td id="g">');
SET @xml = REPLACE(@xml, '<td>zr', '<td id="r">');
DECLARE @s VARCHAR(MAX) = '', @pos INT, @ts VARCHAR(MAX);
DECLARE @i INT = 0;
-- Подсчет количества вхождений <tr> и применение зебра-полос
SET @pos = CHARINDEX('<tr>', @xml, 4);
WHILE (@pos > 0)
BEGIN
SET @i += 1;
SET @ts = SUBSTRING(@xml, 1, @pos - 1);
IF (@i % 2 = 1)
SET @ts = REPLACE(@ts, '<tr>', '<tr id="odd">');
SET @s += @ts;
SET @xml = SUBSTRING(@xml, @pos, LEN(@xml));
SET @pos = CHARINDEX('<tr>', @xml, 4);
END
-- Обработка последнего фрагмента
SET @i += 1;
SET @ts = @xml;
IF (@i % 2 = 1)
SET @ts = REPLACE(@ts, '<tr>', '<tr id="odd">');
SET @s += @ts;
-- Формирование окончательного HTML-тела
SET @body += '
<body>
<H3>Информация о рейтинге в теннисе</H3>
<table border=1>
<tr>
<th>Ранг</th>
<th>Имя игрока</th>
<th>Очки рейтинга</th>
<th>Страна</th>
</tr>'
+ @s +
'</table>
</body>
</html>';
-- Отправка электронной почты
EXEC msdb.dbo.sp_send_dbmail
@recipients = '[email protected]', -- Замените на ваш адрес электронной почты
@subject = 'Красивая DB Mail',
@body = @body,
@body_format = 'HTML';
-- Очистка
DROP TABLE #Temp;Вывод
В этой статье мы продемонстрировали, как создавать визуально привлекательные табличные отчеты в SQL Server. Используя HTML и CSS, мы можем добавить зебра-полосы для строк таблицы и применить разные цвета текста к ячейкам в зависимости от их значений. Это улучшает читаемость и визуальную привлекательность от