Jump to content
We've recently updated our Privacy Statement, available here. ×

  • IronPython script for creating a customized HTML table from an aggregated data table

     Share 
    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'> &nbsp; </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
     
     
     

     

     Share
    Go to articles

    User Feedback

    Recommended Comments

    There are no comments to display.


×
×
  • Create New...