问题描述
我有一个xlsx,它有两张纸:on在G1:O25中有一些数据(我们称之为数据"),另一张在G1:O25中的单元格中有一些图像插入了(我们称其为图像").
我的目标是使用Python使用图像过滤数据.我想要一个弹出窗口,显示我来自单元格G1的图像以及一个复选框或包含/排除此数据点的内容.然后使用包含的数据点创建一个新的工作表(过滤的数据").
我是Python的新手,请多多包涵,但是我从搜索中发现了几点:
- 我可以将数据加载到列表中.
- xlsx文件实际上是zip文件,因此我可以使用zipfile和matplotlib从显示它们的子目录中读取图像.
- 添加复选框并进行过滤应该不难.
我遇到的问题:
- 由于openpyxl在读取/写入工作簿时不会保留图像,因此在附加过滤的数据"表时会丢失图像.也许有一种解决方法,例如保存到单独的工作表并使用COM?
- 尽管我可以使用zip方法加载图像,但是我丢失了与图像关联的单元的信息.它们在xlsx/zip文件中按逻辑顺序排列,但是有时会丢失图像(即说单元格K11没有图像),所以我不能仅仅假设image1.jpeg对应于单元格G1,依此类推,依此类推向前).我不确定在excel文件中的哪里可以找到将图像与电子表格中各个单元相关联的信息.
提前谢谢
根据
import win32com.clientx = win32com.client.Dispatch("Excel.Application")wb = x.Workbooks.Open(< path_to.xlsx>")ws = wb.Sheets("Sheet1")对于ws.Shapes中的我:打印i.TopLeftCell.Address
打印:
$ B $ 2$ B $ 5$ D $ 3
I have an xlsx that has two sheets: on has some data in G1:O25 (let's call this "data") and one that has some images inserted into cells in G1:O25 (let's call this one "images").
My goal is to use Python to filter the data using images. I want a popup that shows me image from cell G1 along with a checkbox or something to include/exclude this data point. Then create a new sheet ("filtered data") with the included data points.
I'm new to Python so bear with me, but I've figured out a couple things from searching:
- I can load the data into a list.
- xlsx files are actually zip files so I can use zipfile and matplotlib to read the images from subdirectories display them.
- It shouldn't be hard to add the checkbox thing and do the filtering.
The issues I am having:
- Since openpyxl does not preserve the images when reading/writing to a workbook, I would loose the images when I append my "filtered data" sheet. Maybe there is a workaround like saving to a seperete sheet and using COM?
- Although I can load the images using the zip method, I lose information on which cell they are associated with. They are in a logical order inside the xlsx/zip file, but sometimes there will be a missing image (i.e. say cell K11 does not have an image) so I cannot just assume that image1.jpeg corresponds to cell G1 and so on and so forth). I am not sure where in the excel file I can find info associating images to their respective cells in the spreadsheet.
Thank you in advance
As per how to get the relative position of shapes within a worksheet , in Excel object model, you get the cell adjacent to an image by its .TopLeftCell
property:
import win32com.client
x=win32com.client.Dispatch("Excel.Application")
wb=x.Workbooks.Open("<path_to.xlsx>")
ws=wb.Sheets("Sheet1")
for i in ws.Shapes:
print i.TopLeftCell.Address
prints:
$B$2
$B$5
$D$3
这篇关于使用Python在XLSX中处理图像的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!