|
Exporting Data into a Web Template
By Kalman Toth, M.Phil., M.Phil., MCDBA
May 21, 2005
How about surprising your user community with a neat professional
web reports? A coworker emails you for a report, and you respond
with an eye-catching report as HTML attachment. Actually it can
be easily done after you setup the HTML template. Following is
the stored procedure for exporting data into HTML document. The
query part has two SELECTs in it. One for the date and time and
the second one for the data rows.
EXEC master.dbo.sp_makewebtask
'e:\data\export\ProductInfo3.html',
'SELECT CONVERT(CHAR(20),GETDATE())
SELECT ProductID,ProductName,UnitsInStock,UnitsOnOrder,
UnitPrice,ReorderLevel FROM Northwind.dbo.products',
@templatefile = 'e:\data\export\ProductTemplate.html'
Following is the definition ProductTemplate.html web template
which will combine with the data to make up an HTML webpage. You
need an HTML editor for efficient preparation. Simple ones can
be prepared with NotePad.
<HTML>
<HEAD>
<TITLE>Product Information from NorthWind</TITLE>
<BODY BGCOLOR="#F0FFF0">
<CENTER>
<H2><font color="#FF0000" face="VERDANA">PRODUCT
INFO</font></H2>
<TABLE BORDER=1 BGCOLOR="#FEFEFE" CELLPADDING=3 CELLSPACING=4
width="285">
<%begindetail%>
<TR>
<TD><b>Date of Pull</b>:
<TD ALIGN="CENTER" BGCOLOR="#020202"><FONT
COLOR="#FEFEFE">
<%insert_data_here%>
</FONT></TD>
</TR>
<%enddetail%>
</TABLE>
<P>
<TABLE BORDER="2" BORDERCOLOR="#FF0000"
BGCOLOR="#DDDDDD" CELLPADDING="4" CELLSPACING="4">
<TR>
<TH VALIGN="MIDDLE">PRODUCT<BR>
ID</TH>
<TH VALIGN="MIDDLE">NAME</TH>
<TH VALIGN="MIDDLE">REORDER</TH>
<TH VALIGN="MIDDLE">UNITS<BR>
IN<BR>
STOCK</TH>
<TH VALIGN="MIDDLE">UNITS<BR>
ON<BR>
ORDER</TH>
<TH VALIGN="MIDDLE">UNIT<BR>
PRICE</TH>
</TR>
<%begindetail%>
<TR>
<TD BGCOLOR="#FFFFCC" ALIGN="CENTER">
<FONT COLOR="0A0A0A"><B>
<%insert_data_here%>
</B></FONT></TD>
<TD BGCOLOR="#FFFFCC"> <FONT COLOR="0A0A0A"><I>
<%insert_data_here%>
</I></FONT></TD>
<TD BGCOLOR="#FFFFCC"> <FONT COLOR="0A0A0A"><I>
<%insert_data_here%>
</I></FONT></TD>
<TD BGCOLOR="#FFFFCC" ALIGN="RIGHT">
<FONT COLOR="#0A0A0A">
<%insert_data_here%>
</FONT></TD>
<TD BGCOLOR="#FFFFCC" ALIGN="RIGHT">
<FONT COLOR="0A0A0A">
<%insert_data_here%>
</FONT></TD>
<TD BGCOLOR="#FFFFCC" ALIGN="RIGHT">
<FONT COLOR="0A0A0A"><I>
<%insert_data_here%>
</I></FONT></TD>
</TR>
<%enddetail%>
</TABLE>
</CENTER>
</BODY>
</HTML>
This is the actual report:
PRODUCT INFO
| Date of Pull:
|
May 22 2005 8:51PM
|
PRODUCT
ID |
NAME |
REORDER |
UNITS
IN
STOCK |
UNITS
ON
ORDER |
UNIT
PRICE |
|
1
|
Chai
|
8
|
0
|
18.0000
|
10
|
|
2
|
Chang
|
17
|
40
|
19.0000
|
25
|
|
3
|
Aniseed Syrup
|
13
|
70
|
10.0000
|
25
|
|
4
|
Chef Anton's Cajun Seasoning
|
53
|
0
|
22.0000
|
0
|
|
5
|
Chef Anton's Gumbo Mix
|
27
|
0
|
21.3500
|
0
|
|
6
|
Grandma's Boysenberry Spread
|
120
|
0
|
25.0000
|
25
|
|
7
|
Uncle Bob's Organic Dried Pears
|
15
|
0
|
30.0000
|
10
|
|
8
|
Northwoods Cranberry Sauce
|
6
|
0
|
40.0000
|
0
|
|
9
|
Mishi Kobe Niku
|
29
|
0
|
97.0000
|
0
|
|
10
|
Ikura
|
31
|
0
|
31.0000
|
0
|
|
11
|
Queso Cabrales
|
22
|
30
|
21.0000
|
30
|
|
12
|
Queso Manchego La Pastora
|
86
|
0
|
38.0000
|
0
|
|
13
|
Konbu
|
24
|
0
|
6.0000
|
5
|
|
14
|
Tofu
|
35
|
0
|
23.2500
|
0
|
|
15
|
Genen Shouyu
|
39
|
0
|
15.5000
|
5
|
|
16
|
Pavlova
|
29
|
0
|
17.4500
|
10
|
|
17
|
Alice Mutton
|
27
|
0
|
39.0000
|
0
|
|
18
|
Carnarvon Tigers
|
42
|
0
|
62.5000
|
0
|
|
19
|
Teatime Chocolate Biscuits
|
25
|
0
|
9.2000
|
5
|
|
20
|
Sir Rodney's Marmalade
|
40
|
0
|
81.0000
|
0
|
|
21
|
Sir Rodney's Scones
|
3
|
40
|
10.0000
|
5
|
|
22
|
Gustaf's Knäckebröd
|
104
|
0
|
21.0000
|
25
|
|
23
|
Tunnbröd
|
61
|
0
|
9.0000
|
25
|
|
24
|
Guaraná Fantástica
|
20
|
0
|
4.5000
|
0
|
|
25
|
NuNuCa Nuß-Nougat-Creme
|
76
|
0
|
14.0000
|
30
|
|
26
|
Gumbär Gummibärchen
|
15
|
0
|
31.2300
|
0
|
|
27
|
Schoggi Schokolade
|
49
|
0
|
43.9000
|
30
|
|
28
|
Rössle Sauerkraut
|
26
|
0
|
45.6000
|
0
|
|
29
|
Thüringer Rostbratwurst
|
27
|
0
|
123.7900
|
0
|
|
30
|
Nord-Ost Matjeshering
|
10
|
0
|
25.8900
|
15
|
|
31
|
Gorgonzola Telino
|
27
|
70
|
12.5000
|
20
|
|
32
|
Mascarpone Fabioli
|
9
|
40
|
32.0000
|
25
|
|
33
|
Geitost
|
112
|
0
|
2.5000
|
20
|
|
34
|
Sasquatch Ale
|
111
|
0
|
14.0000
|
15
|
|
35
|
Steeleye Stout
|
20
|
0
|
18.0000
|
15
|
|
36
|
Inlagd Sill
|
112
|
0
|
19.0000
|
20
|
|
37
|
Gravad lax
|
11
|
50
|
26.0000
|
25
|
|
38
|
Côte de Blaye
|
17
|
0
|
263.5000
|
15
|
|
39
|
Chartreuse verte
|
69
|
0
|
18.0000
|
5
|
|
40
|
Boston Crab Meat
|
123
|
0
|
18.4000
|
30
|
|
41
|
Jack's New England Clam Chowder
|
85
|
0
|
9.6500
|
10
|
|
42
|
Singaporean Hokkien Fried Mee
|
26
|
0
|
14.0000
|
0
|
|
43
|
Ipoh Coffee
|
17
|
10
|
46.0000
|
25
|
|
44
|
Gula Malacca
|
27
|
0
|
19.4500
|
15
|
|
45
|
Rogede sild
|
5
|
70
|
9.5000
|
15
|
|
46
|
Spegesild
|
95
|
0
|
12.0000
|
0
|
|
47
|
Zaanse koeken
|
36
|
0
|
9.5000
|
0
|
|
48
|
Chocolade
|
15
|
70
|
12.7500
|
25
|
|
49
|
Maxilaku
|
10
|
60
|
20.0000
|
15
|
|
50
|
Valkoinen suklaa
|
65
|
0
|
16.2500
|
30
|
|
51
|
Manjimup Dried Apples
|
20
|
0
|
53.0000
|
10
|
|
52
|
Filo Mix
|
38
|
0
|
7.0000
|
25
|
|
53
|
Perth Pasties
|
27
|
0
|
32.8000
|
0
|
|
54
|
Tourtière
|
21
|
0
|
7.4500
|
10
|
|
55
|
Pâté chinois
|
115
|
0
|
24.0000
|
20
|
|
56
|
Gnocchi di nonna Alice
|
21
|
10
|
38.0000
|
30
|
|
57
|
Ravioli Angelo
|
18
|
0
|
19.5000
|
20
|
|
58
|
Escargots de Bourgogne
|
62
|
0
|
13.2500
|
20
|
|
59
|
Raclette Courdavault
|
79
|
0
|
55.0000
|
0
|
|
60
|
Camembert Pierrot
|
19
|
0
|
34.0000
|
0
|
|
61
|
Sirop d'érable
|
113
|
0
|
28.5000
|
25
|
|
62
|
Tarte au sucre
|
17
|
0
|
49.3000
|
0
|
|
63
|
Vegie-spread
|
24
|
0
|
43.9000
|
5
|
|
64
|
Wimmers gute Semmelknödel
|
22
|
80
|
33.2500
|
30
|
|
65
|
Louisiana Fiery Hot Pepper Sauce
|
76
|
0
|
21.0500
|
0
|
|
66
|
Louisiana Hot Spiced Okra
|
4
|
100
|
17.0000
|
20
|
|
67
|
Laughing Lumberjack Lager
|
52
|
0
|
14.0000
|
10
|
|
68
|
Scottish Longbreads
|
6
|
10
|
12.5000
|
15
|
|
69
|
Gudbrandsdalsost
|
26
|
0
|
36.0000
|
15
|
|
70
|
Outback Lager
|
15
|
10
|
15.0000
|
30
|
|
71
|
Flotemysost
|
26
|
0
|
21.5000
|
0
|
|
72
|
Mozzarella di Giovanni
|
14
|
0
|
34.8000
|
0
|
|
73
|
Röd Kaviar
|
4
|
0
|
15.0000
|
5
|
|
74
|
Longlife Tofu
|
4
|
20
|
10.0000
|
5
|
|
75
|
Rhönbräu Klosterbier
|
125
|
0
|
7.7500
|
25
|
|
76
|
Lakkalikööri
|
57
|
0
|
18.0000
|
20
|
|
77
|
Original Frankfurter grüne Soße
|
32
|
0
|
13.0000
|
15
|
|
78
|
Pate de Campagne
|
4
|
0
|
24.0000
|
20
|
|
79
|
Pate de Champion
|
4
|
0
|
24.0000
|
20
|
|
80
|
Pate de Vin
|
115
|
0
|
24.0000
|
20
|
|
81
|
Pate de Bourdeux
|
115
|
0
|
24.0000
|
20
|
|
85
|
Pate de Fish
|
27
|
0
|
5.0000
|
0
|
|
86
|
Hickory Smoked Sausage
|
60
|
0
|
6.0000
|
0
|
|
87
|
Hickory Smoked Turkey
|
55
|
0
|
5.0000
|
0
|
|
