IronPython script for generating a customized HTML table from an aggregated data table is covered in this article
from Spotfire.Dxp.Data import * import re import datetime from datetime import timedelta from Spotfire.Dxp.Application.Visuals import VisualContent from Spotfire.Dxp.Application.Visuals import HtmlTextArea # Form Column Table Data table=Document.Data.Tables["Data Table 1"] col = [] for c in table.Columns: col.append(str(c)) # Form Row Table Data rows = [] record = [] for cn in col: cursor = DataValueCursor.CreateFormatted(table.Columns[cn]) for row in table.GetRows(cursor): record.append(cursor.CurrentValue) rows.append(record) record = [] # print col # print rows # formatting negative numbers into '()' style def negative_format(s): if s >= 0: return str(s) elif s < 0: return "(" + str(s*-1) + ")" else: return str(s) # Dynamic Dates # function to determine the last day of the month from snapshot date def last_day_of_month(any_day): next_month = any_day.replace(day=28) + datetime.timedelta(days=4) return next_month - datetime.timedelta(days=next_month.day) def convert(date): e, f, g = date.split('/') return "/".join([('0'+e if int(e) < 10 else e),('0'+f if int(f) < 10 else f),g]) # Formating a negative number def negFormat(x): try: if float(x) >= 0: return x[:-1] elif float(x) < 0: return "(" + str(float(x[:-1])*-1) + ")" except ValueError: return "" s=rows[-1][-1] s=convert(s) s=s.replace('/','') s=datetime.datetime.strptime(s, "%m%d%Y").date() hd1 = s.strftime("%d")+"-"+s.strftime("%b") # print hd1 b,c,d=s.year,s.month,s.day r=last_day_of_month(datetime.date(b, c, d)) l,m,n=r.year,r.month,r.day currMn=(datetime.date(l, m, n)+ timedelta(days=0)).strftime('%b-%y') currMnP1=(datetime.date(l, m, n)+ timedelta(days=28)).strftime('%b-%y') currMnP2=(datetime.date(l, m, n)+ timedelta(days=56)).strftime('%b-%y') currMnP3=(datetime.date(l, m, n)+ timedelta(days=84)).strftime('%b-%y') currMnP4=(datetime.date(l, m, n)+ timedelta(days=112)).strftime('%b-%y') currMnP5=(datetime.date(l, m, n)+ timedelta(days=140)).strftime('%b-%y') currMnP6=(datetime.date(l, m, n)+ timedelta(days=168)).strftime('%b-%y') currMnM1=(datetime.date(l, m, n)+ timedelta(days=-56)).strftime('%b-%y') currMnM2=(datetime.date(l, m, n)+ timedelta(days=-84)).strftime('%b-%y') currMnM3=(datetime.date(l, m, n)+ timedelta(days=-112)).strftime('%b-%y') currMnM4=(datetime.date(l, m, n)+ timedelta(days=-140)).strftime('%b-%y') currMnM5=(datetime.date(l, m, n)+ timedelta(days=-168)).strftime('%b-%y') july_index = None def give_july_index(): Header_Month_List = [currMnM5,currMnM4,currMnM3,currMnM2,currMnM1,currMn,currMnP1,currMnP2,currMnP3,currMnP4,currMnP5,currMnP6] global july_index for ind,month in enumerate(Header_Month_List): if 'Jul' in month: july_index = 18 + ind give_july_index() def empty_to_dash(val): if val != '(Empty)': return val elif val == '(Empty)': return '-' THEAD1 = "<TR>" + "<TH colspan=28 class='main fontTop'>" + "AM Changes: Week of " + hd1 + "</TR>" THEAD2 = "<TR>" + "<TH>" + "</TH>" + "<TH colspan=2 class='font bold'>" + "AMs (million)" + "</TH>" + "<TH colspan=13 class='font bold'>" + "Year Over Year (Percent Change)" + "</TH>" + "<TH colspan=12 class='font bold'>" + "Theory" + "</TH>" + "</TR>" THEAD3 = "<TR>" + "<TH style='text-align:left'> Reg </TH>" + "<TH>" + currMnP3 + "</TH>" + "<TH>" + "% Share" + "</TH>" + "<TH>" + currMnM5 + "</TH>" + "<TH>" + currMnM4 + "</TH>" + "<TH>" + currMnM3 + "</TH>" + "<TH>" + currMnM2 + "</TH>" + "<TH>" + currMnM1 + "</TH>" + "<TH>" + currMn + "</TH>" + "<TH>" + currMnP1 + "</TH>" + "<TH>" + currMnP2 + "</TH>" + "<TH>" + currMnP3 + "</TH>" + "<TH>" + currMnP4 + "</TH>" + "<TH>" + currMnP5 + "</TH>" + "<TH>" + currMnP6 + "</TH>" + "<TH>" + "12M Avg" + "</TH>" + "<TH>" + currMnM5 + "</TH>" + "<TH>" +currMnM4 + "</TH>" + "<TH>" + currMnM3 + "</TH>" + "<TH>" + currMnM2 + "</TH>" + "<TH>" + currMnM1 + "</TH>" + "<TH>" + currMn + "</TH>" + "<TH>" + currMnP1 + "</TH>" + "<TH>" + currMnP2 + "</TH>" + "<TH>" + currMnP3 + "</TH>" + "<TH>" + currMnP4 + "</TH>" + "<TH>" + currMnP5 + "</TH>" + "<TH>" + currMnP6 + "</TH>" + "</TR>" THEAD3 = THEAD3.replace("<TH", "<TH class='font bold'") # adding CSS to 3d header current = "" Rn_Record = "<TR> " Main_TBODY = "" Code = "" for rID in range(len(rows[0])): for cID in range(len(rows)-1): if cID == 0: # blank ROW between every region block if rows[cID][rID] != current and rows[cID][rID] != "Dom" and rows[cID][rID] != "Diff.(Total)": Main_TBODY += "<TR> " + "<TD class='bold rthinbor'> </TD> " + "<TD> </TD> " + "<TD class='rthickbor'> </TD> " + "<TD> </TD> " * 5 + "<TD class='LeftRightThinBor'> </TD> " + "<TD> </TD> " * 6 + "<TD class='thinbor rthickbor'> </TD> " + "<TD> </TD> " * 5 + "<TD class='LeftRightThinBor'> </TD> " + "<TD> </TD> " * 6 + " </TR>" # region name ROW in every block if rows[cID][rID] != "Grand Total" and rows[cID][rID] != "Diff.(Total)": if rows[cID][rID] != current: Main_TBODY += "<TR> " + "<TD class='bold italic main rthinbor'>" + rows[cID][rID] + "</TD> " + "<TD> </TD> " + "<TD class='rthickbor'> </TD> " + "<TD> </TD> " * 5 + "<TD class='LeftRightThinBor'> </TD> " + "<TD> </TD> " * 6 + "<TD class='thinbor rthickbor'> </TD> " + "<TD> </TD> " * 5 + "<TD class='LeftRightThinBor'> </TD> " + "<TD> </TD> " * 6 + " </TR>" current = rows[cID][rID] if cID == 1: Code = rows[cID][rID] # carrier code and carrier name combined COLUMN if cID == 2: if rows[0][rID] != "Tots" and rows[cID][rID] != 'Other' and rows[cID][rID] != 'Total' and rows[cID][rID] != 'Diff.(Total)' and rows[cID][rID] != 'Grand Total' and rows[cID][rID] != 'Total': if rows[cID][rID] == "Tesla": # Checking for Tesla for Bordering the ROW CSS Rn_Record = "<TR class='TeslaRow'> " Rn_Record += "<TD class='bold main rthinbor'>" + Code + " " + rows[cID][rID] + "</TD> " else: if rows[cID][rID] == "Total" or rows[cID][rID] == "Total": # Checking for Total Row for Bordering the ROW CSS Rn_Record = "<TR class='bold Total_Row_Border'> " elif rows[0][rID] == "Diff.(Total)" and rows[cID][rID] == "Diff.(Total)": # Checking for Total Row for Bordering the ROW CSS Rn_Record = "<TR class='Diff_Total_Row_Border'> " elif rows[cID][rID] == "Grand Total": Rn_Record = "<TR class='bold '>" Rn_Record += "<TD class='bold main rthinbor'>" + rows[cID][rID] + "</TD> " if cID > 2: if cID == 3: if rows[1][rID] == "Diff.(Total)": Rn_Record += "<TD>" + "." + "</TD> " # dot for reporting month for Diff.(Total) elif rows[1][rID] != "Diff.(Total)": Rn_Record += "<TD>" + empty_to_dash(rows[cID][rID]) + "</TD> " elif cID == 4: # pct Share COLUMN CSS if rows[1][rID] == "Diff.(Total)": Rn_Record += "<TD class='rthickbor'>" + "." + "</TD> " # dot for pct share for Diff.(Total) else: Rn_Record += "<TD class='rthickbor'>" + empty_to_dash(rows[cID][rID]) + "</TD> " elif cID == 10: # AM YoY Current Month COLUMN CSS if rows[cID][rID] != '(Empty)' and float(rows[cID][rID]) < 0: # red font for negative number Rn_Record += "<TD class='redfont LeftRightThinBor'>" + negFormat(rows[cID][rID]) + "</TD> " else: Rn_Record += "<TD class='LeftRightThinBor'>" + negFormat(rows[cID][rID]) + "</TD> " elif cID == 17: # 12M Avg Month COLUMN CSS if rows[cID][rID] != '(Empty)' and float(rows[cID][rID]) < 0: # red font for negative number Rn_Record += "<TD class='redfont thinbor rthickbor'>" + negFormat(rows[cID][rID]) + "</TD> " else: Rn_Record += "<TD class='thinbor rthickbor'>" + negFormat(rows[cID][rID]) + "</TD> " elif cID == 23: # AM Theory Current Month COLUMN CSS if cID != july_index: # NOT july month - normal colour coding CSS if rows[cID][rID] !='(Empty)': if float(rows[cID][rID])== 0: Rn_Record += "<TD class='LeftRightThinBor yellow'>" + negFormat(rows[cID][rID]) + "</TD> " elif float(rows[cID][rID])>0 and float(rows[cID][rID]) <= 10: Rn_Record += "<TD class='LeftRightThinBor lightgreen'>" + negFormat(rows[cID][rID]) + "</TD> " elif float(rows[cID][rID])>10 and float(rows[cID][rID]) <= 20: Rn_Record += "<TD class='LeftRightThinBor darkgreen'>" + negFormat(rows[cID][rID]) + "</TD> " elif float(rows[cID][rID])>20: Rn_Record += "<TD class='LeftRightThinBor darkergreen'>" + negFormat(rows[cID][rID]) + "</TD> " elif float(rows[cID][rID])<0 and float(rows[cID][rID]) >= -10: Rn_Record += "<TD class='LeftRightThinBor lightred'>" + negFormat(rows[cID][rID]) + "</TD> " elif float(rows[cID][rID])<-10 and float(rows[cID][rID]) >= -20: Rn_Record += "<TD class='LeftRightThinBor darkred'>" + negFormat(rows[cID][rID]) + "</TD> " elif float(rows[cID][rID])<-20: Rn_Record += "<TD class='LeftRightThinBor darkerred'>" + negFormat(rows[cID][rID]) + "</TD> " elif rows[cID][rID] =='(Empty)': Rn_Record += "<TD class='LeftRightThinBor'>" + negFormat(rows[cID][rID]) + "</TD> " elif cID == july_index: #July month green colour coding CSS if rows[cID][rID] !='(Empty)': if float(rows[cID][rID])== 0: Rn_Record += "<TD class='LeftRightThinBor julymonth'>" + negFormat(rows[cID][rID]) + "</TD> " elif rows[cID][rID] =='(Empty)': Rn_Record += "<TD class='LeftRightThinBor'>" + negFormat(rows[cID][rID]) + "</TD> " elif cID in (18,19,20,21,22,24,25,26,27,28,29): # AM Theory Current Month COLUMN CSS if cID != july_index: # NOT july month - normal colour coding CSS if rows[cID][rID] !='(Empty)': if float(rows[cID][rID])== 0: Rn_Record += "<TD class='yellow'>" + negFormat(rows[cID][rID]) + "</TD> " elif float(rows[cID][rID])>0 and float(rows[cID][rID]) <= 10: Rn_Record += "<TD class='lightgreen'>" + negFormat(rows[cID][rID]) + "</TD> " elif float(rows[cID][rID])>10 and float(rows[cID][rID]) <= 20: Rn_Record += "<TD class='darkgreen'>" + negFormat(rows[cID][rID]) + "</TD> " elif float(rows[cID][rID])>20: Rn_Record += "<TD class='darkergreen'>" + negFormat(rows[cID][rID]) + "</TD> " elif float(rows[cID][rID])<0 and float(rows[cID][rID]) >= -10: Rn_Record += "<TD class='lightred'>" + negFormat(rows[cID][rID]) + "</TD> " elif float(rows[cID][rID])<-10 and float(rows[cID][rID]) >= -20: Rn_Record += "<TD class='darkred'>" + negFormat(rows[cID][rID]) + "</TD> " elif float(rows[cID][rID])<-20: Rn_Record += "<TD class='darkerred'>" + negFormat(rows[cID][rID]) + "</TD> " elif rows[cID][rID] =='(Empty)': Rn_Record += "<TD>" + negFormat(rows[cID][rID]) + "</TD> " elif cID == july_index: #July month green colour coding CSS if rows[cID][rID] !='(Empty)': if float(rows[cID][rID])== 0: Rn_Record += "<TD class='julymonth'>" + negFormat(rows[cID][rID]) + "</TD> " elif rows[cID][rID] =='(Empty)': Rn_Record += "<TD>" + negFormat(rows[cID][rID]) + "</TD> " elif cID >= 5: # for negative formatting if cID in (5,6,7,8,9,10,11,12,13,14,15,16): if rows[cID][rID] != '(Empty)' and float(rows[cID][rID]) < 0: # red font for negative number Rn_Record += "<TD class='redfont'>" + negFormat(rows[cID][rID]) + "</TD> " else: Rn_Record += "<TD>" + negFormat(rows[cID][rID]) + "</TD> " else: Rn_Record += "<TD>" + negFormat(rows[cID][rID]) + "</TD> " else: Rn_Record += "<TD>" + rows[cID][rID] + "</TD> " Main_TBODY += Rn_Record + "</TR> " Rn_Record = "<TR> " THEAD = "<thead>" + THEAD1 + THEAD2 + THEAD3 + "</thead>" TBODY = "<tbody>" + Main_TBODY + "</tbody>" STYLE = """ <STYLE> table, th { border: 2px solid black; border-collapse: collapse; text-align: center; } th { background-color: rgb(255, 204, 153); color: black; } td, th { font-family: Arial; } td { font-size: 10; color: black; } .fontTop { font-size: 11; } .fontSub { font-size: 12 } .font { font-size: 9 } .main { text-align: left; } .bold { font-weight: bold } .thickbor { border-left: 2px solid black } .rthickbor { border-right: 2px solid black } .thinbor { border-left: 1px solid black } .rthinbor { border-right: 1px solid black } .LeftRightThinBor { border-right: 1px solid black; border-left: 1px solid black; } .green { background-color: rgb(204, 255, 204); color: green } .redfont { color: red } .darkerred { background-color: RGB(255, 100, 75); } .darkred { background-color: RGB(255, 150, 100); } .lightred { background-color: RGB(250, 168, 154); } .yellow { background-color: RGB(255, 255, 51); } .darkgreen { background-color: RGB(144, 238, 144); } .darkergreen { background-color: RGB(50, 205, 50); } .lightgreen { background-color: RGB(152, 251, 152); } .julymonth { } .space td { padding-bottom: 20px } .TeslaRow { border-top: 1px solid black; border-bottom: 1px solid black; } .Total_Row_Border { border-top: 2px solid black; } .Diff_Total_Row_Border { border-top: 1px solid black; } .italic { font-style: italic; } </STYLE> """ HTML = "<HTML> <HEAD> " + STYLE + "</HEAD>" + "<BODY>" + "<table>" + THEAD + TBODY + "</table>" + "</BODY>" + "</HTML>" # print HTML AMTextArea.As[HtmlTextArea]().HtmlContent = HTML
Â
Â
Â
Â
Recommended Comments
There are no comments to display.