def TopNSummary(N, Matrix = None):
"""Adding Backwards compatibility support for v4.2 script
Use transformations.series.insert_topN_into_series(N)
"""
if Matrix == None:
from globals import Matrix
import transformations as tr
from transformations.utils.logger import logger
#do not run if N too big.
if (Matrix.Count < N):
logger("Not enough rows in table to insert a Top " + str(N) + " series")
return
m = tr.MatrixManipulator(Matrix)
m.insert_topN_into_series(N)
#v4.2 script inserted the row into a different position,
#This is placeing the inserted row at the end of the Matrix.
newRow = Matrix.InsertBlankRowAfter(Matrix.SideAxis.DataMembers[Matrix.Count-1], "TopN", "Top " + str(N))
Matrix.SwitchRows(0, Matrix.Count-1)
Matrix.DeleteRow(0)
def _move_topN_shape(Chart, Shape, column_number):
"""Move shape to the correct location on the Chart"""
from transformations.utils.logger import logger
number_series = Chart.SeriesCollection().Count
number_categories = Chart.SeriesCollection(number_series).Points().Count
axis = Chart.Axes(1)
#column clustered or stacked
_column_list = [51, 52, 53]
_bar_list = [57, 58, 59]
if Chart.ChartType in _column_list:
Shape.Width = Chart.PlotArea.InsideWidth / number_categories
Shape.Height = Chart.PlotArea.Height / number_series
Shape.Top = Chart.PlotArea.Top - Shape.Height
if axis.ReversePlotOrder:
Shape.Left = Chart.ChartArea.Left + Chart.PlotArea.InsideLeft + (number_categories - column_number+1) * Shape.Width
else:
Shape.Left = Chart.ChartArea.Left + Chart.PlotArea.InsideLeft + (column_number) * Shape.Width
# bar clustered or stacked
elif Chart.ChartType in _bar_list:
Shape.Height = Chart.PlotArea.InsideHeight / number_categories
Shape.Left = Chart.PlotArea.Left + Chart.PlotArea.Width
Shape.Width = Chart.PlotArea.Width / number_series
if axis.ReversePlotOrder:
Shape.Top = Chart.PlotArea.InsideTop + Chart.ChartArea.Top + ((column_number) * Shape.Height)
else:
Shape.Top = Chart.PlotArea.InsideTop + Chart.ChartArea.Top + ((number_categories - column_number-1) * Shape.Height)
else:
logger(str(Chart.ChartType) + ": Cannot add TopN to this chart type")
def replace_row_labels_with_group_names(Table, Matrix=None):
"""Prepend every row in the table with the Group Name.
This function is typically used when making a summary table, for example,
displaying Top 2 scores from a selection of scores or tables. Rather than
displaying Top 2 for each row, display the group label.
:param Table: Table shape
:param Matrix: Matrix associated with the Table. Default is None
"""
if Matrix is None:
from globals import Matrix
from transformations.utils.logger import logger
_row_count = Table.Rows.Count
try:
for row in range(2, _row_count + 1):
_cell = Table.Cell(row, 1) # 1st column
_label = Matrix.SideAxis.DataMembers[row - 2].Group.Label
_text = _cell.Shape.TextFrame.TextRange.Text
_cell.Shape.TextFrame.TextRange.Text = _label + " - " + _text
logger("Updating row labels with group names")
except:
logger("replace_row_labels_with_group_names failed to run")
def _move_topN_shape(_shape):
"""Move shape to the correct location on the Chart"""
#column clustered or stacked
_column_list = [51, 52, 53]
_bar_list = [57, 58, 59]
if Chart.ChartType in _column_list:
_shape.Width = Chart.PlotArea.InsideWidth / _number_categories
_shape.Height = Chart.PlotArea.Height / _number_series
_shape.Top = Chart.PlotArea.Top - _shape.Height
if _axis.ReversePlotOrder:
_shape.Left = Chart.ChartArea.Left + Chart.PlotArea.InsideLeft + (
_number_categories - column_number + 1) * _shape.Width
else:
_shape.Left = Chart.ChartArea.Left + Chart.PlotArea.InsideLeft + (
column_number) * _shape.Width
# bar clustered or stacked
elif Chart.ChartType in _bar_list:
_shape.Height = Chart.PlotArea.InsideHeight / _number_categories
_shape.Left = Chart.PlotArea.Left + Chart.PlotArea.Width
_shape.Width = Chart.PlotArea.Width / _number_series
if _axis.ReversePlotOrder:
_shape.Top = Chart.PlotArea.InsideTop + Chart.ChartArea.Top + (
(column_number) * _shape.Height)
else:
_shape.Top = Chart.PlotArea.InsideTop + Chart.ChartArea.Top + (
(_number_categories - column_number - 1) *
_shape.Height)
else:
logger(
str(Chart.ChartType) +
": Cannot add TopN to this chart type")
def _which_rows_to_insert():
"""Calculate which rows need to be inserted for each outer nest"""
_rows_for_group = dict()
# Outer groups
_group_text = ""
_previous_group_text = ""
for row in Matrix:
try:
_parent = row.Member.ParentMember.ParentMember.Group.Label
_parent_grp = row.Member.ParentMember.Group.Label
_grp = row.Member.Group.Label
_group_text = _parent + " - " + _parent_grp + " - " + _grp
except:
try:
_parent_grp = row.Member.ParentMember.Group.Label
_grp = row.Member.Group.Label
_group_text = _parent_grp + " - " + _grp
except:
_group_text = row.Member.Group.Label
if _previous_group_text != _group_text:
# Update dict
_rows_for_group[row.Member.DataIndex + 1] = _group_text
_previous_group_text = _group_text
logger("Rows to insert into table " + str(_rows_for_group))
return _rows_for_group
def _unset_bold(Table, Log):
"""unset Bold Font of group headings on refresh."""
from transformations.utils.logger import logger
for row in range(2, Table.Rows.Count + 1):
_cell = Table.Cell(row, 1) # 1st column
_cell.Shape.TextFrame.TextRange.Font.Bold = False
logger("resetting fonts Bold = False")
def convert_glyphs_to_color_wingdings(Chart, Matrix=None, rgb=list()):
r"""Convert your significant results into up and down arrows within the
chart.
:param Chart: Chart shape
:param Matrix: Matrix associated with the selection, default = None
:param rgb: list containing RGB values. Default is [255,255,255], which
will display as white.
Used in conjunction with a transformation script,
convert_significance_results_to_arrows, which sets the significant
results to display the character values, chr(0xE9), chr(0xEA) which will
then be converted into arrows when this function is run.
Example, in the transformations script:
| tr = transformations.MatrixManipulator(Matrix)
| tr.convert_significance_results_to_arrows()
And in the afterfill action:
| import charts
| charts.convert_glyphs_to_color_wingdings(Chart, Matrix, rgb=[0,0,0])
"""
if Matrix is None:
from globals import Matrix
from shapes import RGB
from transformations.utils.logger import logger
_series_collections = Chart.SeriesCollection()
_significanceChars = [chr(0xE9), chr(0xEA)]
for _iSeries in range(1, _series_collections.Count + 1): # rows in Table
_series = _series_collections.Item(_iSeries) # rows in Table
_xVals = _series.XValues # columns
_pointNumber = 1
for _item in Matrix.TopAxis.DataMembers: # columns
_pt = _series_collections(_iSeries).Points(_pointNumber)
_DataLabel = _pt.DataLabel.Text
i = 1
for character in _DataLabel:
for sig in _significanceChars:
if sig == character:
_char = _pt.DataLabel.Characters(i, 1)
_char.Font.Name = "Wingdings"
if rgb != []: # user defined
_char.Font.Color = RGB(rgb[0], rgb[1], rgb[2])
else:
_char.Font.Color = RGB(255, 255, 255) #white
i += 1
_pointNumber += 1
logger("Updated arrows to Wingdings")
def _merge_header_cells():
"""Then merge the cells for the group headings"""
for col in range(2, _col_count + 1):
_cell = Table.Cell(1, col) # 1st row, columns start at 2.
if _cell.Shape.TextFrame.TextRange.Text == "":
try:
_cell.Merge(Table.Cell(1, col - 1))
except:
pass
logger("Merged group Header Cells")
def _set_colours():
"""Set the colour onto the chart"""
from transformations.utils.logger import logger
_series_collections = Chart.SeriesCollection()
# pie chart - one series
if _series_collections.Count == 1:
_xVals = Chart.SeriesCollection(1).XValues
_matching = [
_item for _item in _xVals if _item in _color_dict.keys()
]
_series = _series_collections(1)
counter = 1
for _item in _xVals:
if _item in _matching:
_point = _series.Points(counter)
_format = _point.Format
_fill = _format.Fill
_fill.Visible = 1
_foreColor = _fill.ForeColor
_foreColor.RGB = int(_color_dict[_item])
_point.Border.Color = int(_color_dict[_item])
counter += 1
else:
# Loop through SeriesCollection
for _iSeries in range(1, _series_collections.Count + 1):
_series = _series_collections.Item(_iSeries)
_xVals = _series.XValues
if _series.Name in _color_dict.keys():
# This is a multi series chart. Colour entire series.
_format = _series.Format
_fill = _format.Fill
_fill.Visible = 1
_foreColor = _fill.ForeColor
# bar
_foreColor.RGB = int(_color_dict[_series.Name])
# line
_col = _color_dict[_series.Name]
try:
_series.MarkerBackgroundColor = int(_col)
_series.MarkerForegroundColor = int(_col)
except:
pass
_series.Border.Color = int(_col)
logger("Updated colours for chart")
def _insert_rows_and_text():
"""Insert rows, and set the text to the nested group label"""
from transformations.utils.logger import logger
keys = sorted(_rows_for_group.keys())
for i in reversed(keys):
# Insert row
Table.Rows.Add(i + 1)
_cell = Table.Cell(i + 1, 1) # 1st column
_cell.Shape.TextFrame.TextRange.Text = _rows_for_group[i]
# make font bold for this _cell.
_cell.Shape.TextFrame.TextRange.Font.Bold = True
logger("set text to Parent Grp Label - Grp Label or Group Label")
def _add_group_headers():
"""Add the group headings to the first column per group"""
# Add a new first row for group headers
Table.Rows.Add(1)
_previous_group = ""
for col in range(0, Matrix.TopAxis.DataMembers.Count):
if _col_count < col + 2:
logger("not enough cols: ", str(col + 2))
return
_cell = Table.Cell(1, col + 2) # row1, col starting from 2.
if Matrix.TopAxis.DataMembers[col].Group.Label != _previous_group:
_label = Matrix.TopAxis.DataMembers[col].Group.Label
_cell.Shape.TextFrame.TextRange.Text = _label
_previous_group = _label
logger("Added Group Labels to row 1")
def delete_table_row_before_fill():
"""Manipulate the table before it is filled. Delete first row so that any
merged cells are removed before the fill.
NOTE: This needs to be run from the transformations script (not after fill),
and is run before any script that will merge cells in the first row.
Note, the Table object is not available from the Transformation
script, and therefore the active object is used to find the Table.
"""
from transformations.utils.logger import logger
import System
_ppt_pap = System.Type.GetTypeFromProgID("PowerPoint.Application")
app = System.Activator.CreateInstance(_ppt_pap)
activeShape = app.ActiveWindow.Selection.ShapeRange[1]
activeShape.Table.Rows[1].Delete()
logger("First Row deleted")
def _make_color_dict_for_series():
"""Return a dictionary of colours to set on chart based on Series"""
def _get_csv_value(label):
"""Return the RGB value for the label from csv txt file."""
import _csv
global _f
_f = _csv.reader(open(fileName, "r"))
for _row in _f:
if (label == _row[0]):
_r = int(_row[1])
_g = int(_row[2])
_b = int(_row[3])
return str(RGB(_r, _g, _b))
return
_colors = dict()
try:
_series_count = Chart.SeriesCollection().Count
except:
try: # unit tests where chart has come from python-pptx
_series_count = chart.series.__len__()
except:
_series_count = 0
if _series_count > 1: # This is a multi series chart
# for each brand column, look up the csv file, return the RGB value
for _item in Matrix.SideAxis.DataMembers:
if _get_csv_value(_item.Label) is not None:
_colors[_item.Label] = _get_csv_value(_item.Label)
else:
logger("There is no color match for label " + _item.Label)
else: # This is a single series chart, eg pie.
# for each brand column, look up the csv file, return the RGB value
for _item in Matrix.TopAxis.DataMembers:
if _get_csv_value(_item.Label) is not None:
_colors[_item.Label] = _get_csv_value(_item.Label)
else:
logger("There is no color match for label " + _item.Label)
logger("Brands and colors: " + str(_colors))
return _colors