pythonopenpyxl

How to keep the existing axis title while changing the formats in Openpyxl?

I am trying to automate chart formatting for my work. Often times, we have to make 50+ charts with specific styles. The only difference with charts are axis titles, which have to be manually edited for client understanding. I already made a code that formats the chart to the exact format. The only problem is that I don't know how to specify formats only without specifying what the axis title should be. My code right is this.

    import openpyxl as opyxl
from openpyxl.styles import Font
from openpyxl.chart.text import RichText
from openpyxl.drawing.text import Paragraph, ParagraphProperties, CharacterProperties, Font, RegularTextRun
from openpyxl.xml.functions import fromstring
from openpyxl.chart.shapes import GraphicalProperties

#The Point of This Program is to create a cleaner program

def graph_formatting(file, font_style = 'Times New Roman', fill_color = "000000", xt_name = "Default", yt_name = "Default",  font_size = 1000, bold = '0'):

    wb = opyxl.load_workbook(file)


    for sheets in wb.chartsheets: #loops through available chartsheets

        dechart = sheets._charts[0]   

        #This specifies the axis format using XML;
        xml = f"""
        <txPr>
        <a:p xmlns:a="http://schemas.openxmlformats.org/drawingml/2006/main">
            <a:r>
            <a:rPr b="{bold}" i="0" sz="{font_size}" spc="-1" strike="noStrike">
                <a:solidFill>
                    <a:srgbClr val="{fill_color}" />
                </a:solidFill>
                <a:latin typeface="{font_style}" />
            </a:rPr>
            <a:t>{yt_name}</a:t>
            </a:r>
        </a:p>
        </txPr>
        """

        dechart.y_axis.title.tx.rich = RichText.from_tree(fromstring(xml)) #Change the format using specified XML; 

        #Same idea
        xml = f""" 
        <txPr>
        <a:p xmlns:a="http://schemas.openxmlformats.org/drawingml/2006/main">
            <a:r>
            <a:rPr b="{bold}" i="0" sz="{font_size}" spc="-1" strike="noStrike">
                <a:solidFill>
                    <a:srgbClr val="{fill_color}" />
                </a:solidFill>
                <a:latin typeface="{font_style}" />
            </a:rPr>
            <a:t>{xt_name}</a:t>
            </a:r>
        </a:p>
        </txPr>
        """
        dechart.x_axis.title.tx.rich = RichText.from_tree(fromstring(xml)) 

        #Changes rest of the of charts, uch as axis labels
        font = Font(typeface=font_style) #set the font
        size = font_size #set the font size
        cp = CharacterProperties(latin=font, sz=size, b=False)
        pp = ParagraphProperties(defRPr=cp)
        dechart.x_axis.txPr = RichText(p=[Paragraph(pPr=ParagraphProperties(defRPr=cp), endParaRPr=cp)]) #changes the x axis label font
        dechart.y_axis.txPr = RichText(p=[Paragraph(pPr=ParagraphProperties(defRPr=cp), endParaRPr=cp)]) #changes the y axis label font


        #chart styling for the minor details

        dechart.graphical_properties = GraphicalProperties()
        dechart.graphical_properties.line.noFill = True
        dechart.graphical_properties.line.prstDash = None

        dechart.x_axis.graphicalProperties.line.solidFill = fill_color #changes the line color

        dechart.y_axis.graphicalProperties.line.noFill=False #draws the y axis line

        dechart.y_axis.majorGridlines = None #gets rid of gridline

        dechart.y_axis.majorTickMark = 'out' #create tickmarks for x and y axis

        dechart.x_axis.majorTickMark = 'out'



        #wb.save(path)
        if dechart:
            wb.save(file)

It works for the most part. I just want to keep the existing axis title as is. For example, if the axis title says "Percent of Customer", then it should still say "Percent of Customer" after I run the program, not "Default". I tried to delete the line

<a:t>{yt_name}</a:t>

in the XML, but now it just erases the axis title. I tried to look through the openpyxl documentation, but I can't find a solution.

The ideal workflow with this program is:

  1. I create a chart on Excel.
  2. I format the chart on Excel using this program.
  3. Two days later, my boss ask me to create another chart in the same file.
  4. I create another chart.
  5. I format the chart using the same program.

The problem is when I use the program the second time, the older axis titles in older charts will get overridden by "Default"

Solution

  • Do you mean like grab the original titles and reset them after your formatting

    ...
    for sheets in wb.chartsheets: #loops through available chartsheets

        dechart = sheets._charts[0]   
        ### Get original X and Y Axis titles
        orig_x_title = dechart.x_axis.title.text.rich.paragraphs[0].text[0].value
        orig_y_title = dechart.y_axis.title.text.rich.paragraphs[0].text[0].value
        print(f"Original X AXIS Title text '{orig_x_title}'")
        print(f"Original Y AXIS Title text '{orig_y_title}'")

        #This specifies the axis format using XML;
        xml = f"""
        <txPr>
        <a:p xmlns:a="http://schemas.openxmlformats.org/drawingml/2006/main">
            <a:r>
            <a:rPr b="{bold}" i="0" sz="{font_size}" spc="-1" strike="noStrike">
                <a:solidFill>
                    <a:srgbClr val="{fill_color}" />
                </a:solidFill>
                <a:latin typeface="{font_style}" />
            </a:rPr>
            <a:t>{yt_name}</a:t>
            </a:r>
        </a:p>
        </txPr>
        """

        dechart.y_axis.title.tx.rich = RichText.from_tree(fromstring(xml)) #Change the format using specified XML; 

        #Same idea
        xml = f""" 
        <txPr>
        <a:p xmlns:a="http://schemas.openxmlformats.org/drawingml/2006/main">
            <a:r>
            <a:rPr b="{bold}" i="0" sz="{font_size}" spc="-1" strike="noStrike">
                <a:solidFill>
                    <a:srgbClr val="{fill_color}" />
                </a:solidFill>
                <a:latin typeface="{font_style}" />
            </a:rPr>
            <a:t>{xt_name}</a:t>
            </a:r>
        </a:p>
        </txPr>
        """
        #dechart.x_axis.title.tx.rich = RichText.from_tree(fromstring(xml)) 
        ### Reset Axis Titles text to orig
        dechart.x_axis.title = orig_x_title
        dechart.y_axis.title = orig_y_title

        #Changes rest of the of charts, uch as axis labels
        font = Font(typeface=font_style) #set the font
        size = font_size #set the font size

        ...