Draw a line between two excel cells using openpyxl

0
votes

I would like to draw a line between the centers of two ( non-adjacent ) cells in an Excel work sheet using openpyxl.

Using openpyxl I have created a fairly large lookup table. Many of the points in the lookup table are interpolated from a hand full of known points.

I would like to draw lines between the cells that were created using the known points. These lines would sort of circle the areas that are interpolated.

Expected Result:
(This is the actual Excel generated spread sheet, The lines were added by hand in Excel. I want to automate the line drawing. )

enter image description here

In this case the white cells are known data points. Green(ish) cells are inside of the bounding trianges. Redish-Blueish are outside. All of data on this sheet was populated via a new sheet using openpyxl.

The openpyxl documention hints that this is possible but I do not understand how.

Something along the lines of:

ws.Line['A1':'P17].style['heavy','black']

I think is what I am looking for.

[A bit more data ]

Using Excel and win32com I can automate drawing these lines. 

line = ws.Shapes.Addline(3,4,70,80).Line

However do to other limitations in Excel I have to create this offline using openpyxl. Other_Limitations
So to re-phrase my question:
Can openpyxl even draw lines?

I am beginning to think that I will have to create the spreadsheet with openpyxl then open the newly created workbook with Excel and draw the lines with Excel.

2
pythonexcelopenpyxl

2 Answers

0
votes

I really don't think Excel is suitable for this. The drawing subsystem uses a completely different coordinate system to the worksheet itself. Thus, although it is possible to "anchor" a drawing between two cells, the proportions will be extremely hard to calculate.

I'm sure matplotlib, seaborn or other graphics libraries have tools more suitable for this job.

0
votes

So my original Question was if this can be done with Openpyxl.
That question still stands.

However here is my solution to draw lines with win32com / Python in Excel directly.
This is not ideal for my situation but it works.

def Drawline(Sheet,Start,End):
    StartCell = Sheet.Cells(Start[0],Start[1])
    StartAdjacent = Sheet.Cells(Start[0]+1,Start[1]+1)
    EndCell = Sheet.Cells(End[0],End[1])
    EndAdjacent = Sheet.Cells(End[0]+1,End[1]+1)
    Y1 = ( StartCell.Top + StartAdjacent.Top ) / 2
    X1 = ( StartCell.Left + StartAdjacent.Left ) / 2
    Y2 = ( EndCell.Top + EndAdjacent.Top ) / 2
    X2 = ( EndCell.Left + EndAdjacent.Left ) / 2
    Sheet.Shapes.AddLine(X1,Y1,X2,Y2)

This will draw a line from the center of Start to the center of End on Sheet.