matlib-openpyxls.py

matlib-openpyxls.py

# https://qiita.com/quryu/items/7e281dcb11b0e3db3a99
# https://tonari-it.com/python-openpyxl-cell-size/
# https://stackoverflow.com/questions/54641637/how-to-change-size-of-picture-using-openpyxl
import io
import openpyxl
import matplotlib.pyplot as plt

def plot():
	fig, ax = plt.subplots(figsize=(4, 3))
	x=[1,2,3,4,5]
	y=[2,4,5,1,2]
	ax.plot(x, y)
	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
	print(img.width, img.height)
	return img

workbook =  openpyxl.Workbook()
worksheet = workbook.worksheets[0]

img = plot()

# https://pg-chain.com/python-excel-coordinate
# https://gammasoft.jp/support/how-to-use-openpyxl-for-excel-file/#get-sheet
col = 2
row = 3

worksheet.cell(column=col, row=row-1).value = "test"
worksheet.cell(column=col-1, row=row).value = "test2"

# https://gammasoft.jp/blog/text-center-alignment-with-openpyxl/
worksheet.cell(column=col-1, row=row).alignment = openpyxl.styles.Alignment(horizontal = 'left', vertical = 'top', wrap_text = False)

worksheet.add_image(img, worksheet.cell(column=col, row=row).coordinate)

col_letter = openpyxl.utils.get_column_letter(col)
print(worksheet.column_dimensions[col_letter].width)
print(worksheet.row_dimensions[row].height    )

worksheet.column_dimensions[col_letter].width     = 60
worksheet.row_dimensions[row].height              = 260

file = 'excel.xlsx'
workbook.save(file)