使用openpyxl将文本框添加到Excel图表 [英] Adding a text box to an excel chart using openpyxl

问题描述

我正在尝试在使用openpyxl生成的图表中添加文本框，但是找不到文档或示例来说明如何执行此操作. openpyxl支持吗?

I'm trying to add a text box to a chart I've generated with openpyxl, but can't find documentation or examples showing how to do so. Does openpyxl support it?

推荐答案

我也无法弄清楚如何通过OpenPyXL做到这一点，但是您可以使用

I also haven't been able to figure out how to do this via OpenPyXL, but you can add a textbox using XLSXWriter.

请注意，您可以同时使用OpenPyXL和XLSXWriter，但是在XLSXWriter中重新打开工作簿并添加文本框之前，请完成在OpenPyXL中所做的更改并保存/关闭工作簿，这一点很重要. (否则，很明显，您可以保存第二个程序包所做的任何更改(例如，文本框).)

Note that you can use both OpenPyXL and XLSXWriter at the same time, but it is important to complete the changes you are making in OpenPyXL and save/close the workbook before re-opening it in XLSXWriter and adding the textbox. (Otherwise, stating the obvious, you could save over any changes (e.g. the textbox) made by the second package.)

FWIW，至少在我的笔记本电脑上，测试工作簿在XLSXWriter中的打开速度比在OpenPyXL中更快.包含10个单页工作表的工作簿，其中充斥着公式，格式和数据-在OpenPyXL中加载需要8秒，在XLSXWriter中加载需要大约0.003秒.因此，即使处理数十个工作簿，第二次重新打开工作簿也没什么大不了的.

FWIW, on my laptop at least, the test workbook opens waaaaaay faster in XLSXWriter than it does in OpenPyXL. A workbook with 10 single-page worksheets, crammed with formulas, formatting, data - takes 8 secs to load in OpenPyXL and about .003 secs to load in XLSXWriter. So re-opening the workbook a second time is not that big a deal, even if processing dozens of workbooks.

以下是使用XLSXWriter创建文本框的简单代码示例:

Here is a simple code example for creating a textbox using XLSXWriter:

import xlsxwriter as xlwr
xlwrwb = xlwr.Workbook('c:\myworkbook.xlsx')
xlwrsheet = xlwrwb.get_worksheet_by_name(sheet.title)

rrow = 10
ccol = 8
ttxt = "This is an example textbox created by OpenPyXL." 
xlwropts = {
    'width': 400,
    'height':200,
    'fill': {'color': '#EEE8AA'},
    'align': {'vertical': 'middle', 'horizontal': 'center'},
    'font': {'bold': True, 'name': 'Arial', 'size': 16, 'color':'orange'},
}
xlwrsheet.insert_textbox(rrow,ccol,ttxt, xlwropts)
xlwrwb.close()

更新:

自从上面的代码发布以来，我一直在研究这个示例，我已经纠正了. XLSXWriter文档当然演示了按照上述代码添加文本框，但是XLSXWriter无法对现有Excel工作簿进行更改.它只能创建新的工作簿-甚至不能使用现有的Excel工作簿作为模板.

UPDATE:

Have been working on this example since posting above code and I stand corrected. The XLSXWriter docs certainly demonstrate adding a textbox as per above code, but XLSXWriter cannot make changes to an existing Excel workbook. It only can create new workbooks - and it cannot use an existing Excel workbook as a template, even.

我将尝试找到另一个可以完成此操作的包，但目前为止看起来不太好.

I will attempt to find another package that can do this, but at this point it doesn't look good.

希望使用 DrawingML规范，OpenPyXL(Charlie?)的好心人能够提供一种几乎足够的解决方案，但目前为止，这超出了我的能力范围.

Here's hoping the good minds at OpenPyXL (Charlie?) can provide a near-enough solution using the DrawingML specification, but at this point that is beyond my own abilities.

https://xlsxwriter.readthedocs.io/faq.html

这篇关于使用openpyxl将文本框添加到Excel图表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！