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