Openpyxl minor gridlines

Question

I am working on a Python application where I am collecting data from a device, and attempting to plot it in an excel file by using the Openpyxl library. I am successfully able to do everything including plotting the data, and formatting the scatter plot that I made, but I am having some trouble in adding minor gridlines to the plot.

I feel like this is definitely possible because in the API, I can see under the openpyxl.chart.axis module, there is a “minorGridlines” attribute, but it is not a boolean input (ON/OFF), rather it takes a Chartlines class. I tried going a bit down the rabbit-hole of seeing how I would do this, but I am wondering what the most straightforward way of adding the minor-gridlines would be? Do you have to construct chart lines manually, or is there a simple way of doing this?

I would really appreciate any help!

Answer 1

I think I answered my own question, but I will post it here if anybody else needs this (as I don’t see any other answers to this question on the forum).

Example Code (see lines 4, 38):

# Imports for script
from openpyxl import Workbook  # For plotting things in excel
from openpyxl.chart import ScatterChart, Reference, Series
from openpyxl.chart.axis import ChartLines
from math import log10

# Variables for script
fileName = 'testFile.xlsx'
dataPoints = 100

# Generating a workbook to test with
wb = Workbook()
ws = wb.active  # Fill data into the first sheet
ws_name = ws.title

# We will just generate a logarithmic plot, and scale the axis logarithmically (will look linear)
x_data = []
y_data = []
for i in range(dataPoints):
    x_data.append(i + 1)
    y_data.append(log10(i + 1))

# Go back through the data, and place the data into the sheet
ws['A1'] = 'x_data'
ws['B1'] = 'y_data'

for i in range(dataPoints):
    ws['A%d' % (i + 2)] = x_data[i]
    ws['B%d' % (i + 2)] = y_data[i]

# Generate a reference to the cells that we can plot
x_axis = Reference(ws, range_string='%s!A2:A%d' % (ws_name, dataPoints + 1))
y_axis = Reference(ws, range_string='%s!B2:B%d' % (ws_name, dataPoints + 1))
function = Series(xvalues=x_axis, values=y_axis)

# Actually create the scatter plot, and append all of the plots to it
ScatterPlot = ScatterChart()
ScatterPlot.x_axis.minorGridlines = ChartLines()

ScatterPlot.x_axis.scaling.logBase = 10
ScatterPlot.series.append(function)
ScatterPlot.x_axis.title = 'X_Data'
ScatterPlot.y_axis.title = 'Y_Data'
ScatterPlot.title = 'Openpyxl Plotting Test'
ws.add_chart(ScatterPlot, 'D2')

# Save the file at the end to output it
wb.save(fileName)

Background on solution:

I looked at how the code for Openpyxl generates the Major axis gridlines, which seems to follow a similar convention as the Minor axis gridlines, and I found that in the ‘NumericAxis’ class, they generated the major gridlines with the following line (labeled ‘##### This Line #####’ which is originally copied from the ‘openpyxl->chart->axis’ file):

class NumericAxis(_BaseAxis):

tagname = "valAx"

axId = _BaseAxis.axId
scaling = _BaseAxis.scaling
delete = _BaseAxis.delete
axPos = _BaseAxis.axPos
majorGridlines = _BaseAxis.majorGridlines
minorGridlines = _BaseAxis.minorGridlines
title = _BaseAxis.title
numFmt = _BaseAxis.numFmt
majorTickMark = _BaseAxis.majorTickMark
minorTickMark = _BaseAxis.minorTickMark
tickLblPos = _BaseAxis.tickLblPos
spPr = _BaseAxis.spPr
txPr = _BaseAxis.txPr
crossAx = _BaseAxis.crossAx
crosses = _BaseAxis.crosses
crossesAt = _BaseAxis.crossesAt

crossBetween = NestedNoneSet(values=(['between', 'midCat']))
majorUnit = NestedFloat(allow_none=True)
minorUnit = NestedFloat(allow_none=True)
dispUnits = Typed(expected_type=DisplayUnitsLabelList, allow_none=True)
extLst = Typed(expected_type=ExtensionList, allow_none=True)

__elements__ = _BaseAxis.__elements__ + ('crossBetween', 'majorUnit',
                                         'minorUnit', 'dispUnits',)


def __init__(self,
             crossBetween=None,
             majorUnit=None,
             minorUnit=None,
             dispUnits=None,
             extLst=None,
             **kw
            ):
    self.crossBetween = crossBetween
    self.majorUnit = majorUnit
    self.minorUnit = minorUnit
    self.dispUnits = dispUnits
    kw.setdefault('majorGridlines', ChartLines()) ######## THIS Line #######
    kw.setdefault('axId', 100)
    kw.setdefault('crossAx', 10)
    super(NumericAxis, self).__init__(**kw)


@classmethod
def from_tree(cls, node):
    """
    Special case value axes with no gridlines
    """
    self = super(NumericAxis, cls).from_tree(node)
    gridlines = node.find("{%s}majorGridlines" % CHART_NS)
    if gridlines is None:
        self.majorGridlines = None
    return self

I took a stab, and after importing the ‘Chartlines’  class like so:

from openpyxl.chart.axis import ChartLines

  I was able to add minor gridlines to the x-axis like so:

ScatterPlot.x_axis.minorGridlines = ChartLines()

As far as formatting the minor gridlines, I’m at a bit of a loss, and personally have no need, but this at least is a good start.