🐙
matplotlib+openpyxl で マージンを入れつつExcel のセルにplot する
matplotlib+openpyxl で マージンを入れつつExcel のセルにplot する
結果
コード
'''
https://stackoverflow.com/questions/55309671/more-precise-image-placement-possible-with-openpyxl-pixel-coordinates-instead
'''
import os
import sys
import io
try:
import openpyxl
import numpy as np
import matplotlib.pyplot as plt
except:
print("pip install openpyxl numpy matplotlib")
sys.exit(1)
from openpyxl.drawing.xdr import XDRPositiveSize2D
from openpyxl.utils.units import pixels_to_EMU, cm_to_EMU, pixels_to_points
from openpyxl.drawing.spreadsheet_drawing import OneCellAnchor, AnchorMarker
from openpyxl.utils import get_column_letter
c2e = cm_to_EMU
p2e = pixels_to_EMU
def cellh(x):
"""
convert from cell height
1 cm = 360000 EMUs
"""
return c2e((x * 49.77)/99)
def cellw(x):
"""
convert from cell width
"""
return c2e((x * (18.65-1.71))/10)
def save_to_image(fig):
img_data = io.BytesIO()
fig.savefig(img_data, format='png')
img = openpyxl.drawing.image.Image(img_data)
# https://matplotlib.org/stable/api/figure_api.html#matplotlib.figure.Figure.figimage
# https://www.unitconverters.net/typography/inch-to-pixel-x.htm
img.width = fig.get_figwidth() * fig.dpi
img.height = fig.get_figheight() * fig.dpi
return img
def plot_to_excel_cell(worksheet, row, column, fig):
"""
plot to an excel cell
row : 0-based
column: 0-based
"""
img = save_to_image(fig)
height = img.height
width = img.width
size = XDRPositiveSize2D(p2e(width), p2e(height))
# https://openpyxl.readthedocs.io/en/stable/api/openpyxl.cell.cell.html
# col, row : from 1
cell = worksheet.cell(row=row+1,column=column+1)
width_in_font = 2.2 * int( pixels_to_points(width) / cell.font.size + 1)
worksheet.column_dimensions[get_column_letter(column + 1)].width = width_in_font
worksheet.row_dimensions[row+1].height = pixels_to_points(height * 1.1)
coloffset= cellw(0.2)
rowoffset= cellh(0.2)
# https://openpyxl.readthedocs.io/en/latest/api/openpyxl.drawing.spreadsheet_drawing.html
# col, row : from 0
marker= AnchorMarker(col=column, row=row, colOff=coloffset, rowOff=rowoffset)
img.anchor= OneCellAnchor(_from=marker, ext=size)
worksheet.add_image(img)
def insert_plt(worksheet, num=10, start_row=1, start_column=1):
"""
Insert matplotlit plot to EXCEL sheet.
"""
side = openpyxl.styles.borders.Side(style='thin', color='000000')
border = openpyxl.styles.borders.Border(top=side, bottom=side, left=side, right=side)
for i in range(1, num+1):
multiple = i + 1
x=np.array([1,2,3,4,5])
y=np.array([2,4,5,1,2]) * multiple
fig, ax = plt.subplots(figsize=(4, 3))
ax.plot(x, y)
row = start_row + i - 1
column = start_column
plot_to_excel_cell(worksheet, row, column, fig)
cell = worksheet.cell(row=row+1,column=column)
cell.border = border
cell = worksheet.cell(row=row+1,column=column+1)
cell.border = border
output_xlsx = "output_ " + os.path.basename(sys.argv[0]) + '.xlsx'
wb1 = openpyxl.Workbook()
ws1 = wb1.worksheets[0]
insert_plt(ws1)
wb1.save(output_xlsx)
参考サイト
Discussion