- 单元格嵌入图片 使图片随单元格变化缩放
You can position charts using one of three different kinds of anchor:
OneCell – where the top-left of a chart is anchored to a single cell. This is the default for openpyxl and corresponds to the layout option “Move but don’t size with cells”.
TwoCell – where the top-left of a chart is anchored to one cell, and the bottom-right to another cell. This corresponds to the layout option “Move and size with cells”.
Absolute – where the chart is placed relative to the worksheet’s top-left corner and not any particular cell.
您可以使用三种不同类型的锚点之一定位图表：
OneCell–图表的左上角固定到单个单元格。这是openpyxl的默认设置，对应于布局选项“移动但不随单元格调整大小”。
TwoCell–图表的左上角固定到一个单元格，右下角固定到另一个单元格。这对应于布局选项“随单元格移动和调整大小”。
绝对–图表相对于工作表左上角而非任何特定单元格的位置。
你可以很容易地在这样的图表上更改锚点。让我们假设我们已经使用示例代码创建了一个条形图：
安装
pip install openpyxl
pip install pandas
pip install requests
引用
from io import BytesIO
import requests
from openpyxl import load_workbook
from openpyxl.drawing.image import Image
from openpyxl.drawing.spreadsheet_drawing import AnchorMarker, TwoCellAnchor
from openpyxl.utils import get_column_letter
from pandas import read_csv
方法
def write_export_excel(filepath, file_name, header):
"""
:param filepath:
:param file_name:
:param header:
:return:
"""
# csv 转xlsx
df = read_csv(filepath, header=header, index_col=1)
col = len(df.columns)
data = df.loc[:, ["Id"]].values
df.to_excel(file_name, index=False)
# 根据列的标号 获取列头标识 比如 0 -- A
col_name = get_column_letter(col)
# 读取excel
wb = load_workbook(file_name, data_only=True)
# 获取激活的 Sheet 默认第一个
sheet = wb.active
# 设置指定列的宽度
sheet.column_dimensions[col_name].width = 30.0
for index, item in enumerate(data):
# 获取 Excel 数据标识 Id
row = Series(item, index=["Id"])
# 图片网络地址
image = 'url'
if image:
# 设置行高
sheet.row_dimensions[index + 2].height = 100
# 加载网络图片
response = requests.get(_image)
image_bytes = BytesIO()
image_bytes.write(response.content)
img = Image(image_bytes)
# 左上角固定到一个单元格，
_from = AnchorMarker(col - 1, 50000, index + 1, 50000)
# 右下角固定到另一个单元格
# AnchorMarker(列，微调，行，微调)
to = AnchorMarker(col, -50000, index + 2, -50000)
img.anchor = TwoCellAnchor('twoCell', _from, to)
sheet.add_image(img)
wb.save(file_name)