使用Python在XLSX中处理图像 [英] Processing images in XLSX using Python

问题描述

我有一个xlsx，它有两张纸:on在G1:O25中有一些数据(我们称之为数据")，另一张在G1:O25中的单元格中有一些图像插入了(我们称其为图像").

我的目标是使用Python使用图像过滤数据.我想要一个弹出窗口，显示我来自单元格G1的图像以及一个复选框或包含/排除此数据点的内容.然后使用包含的数据点创建一个新的工作表(过滤的数据").

我是Python的新手，请多多包涵，但是我从搜索中发现了几点:

1. 我可以将数据加载到列表中.
2. xlsx文件实际上是zip文件，因此我可以使用zipfile和matplotlib从显示它们的子目录中读取图像.
3. 添加复选框并进行过滤应该不难.

我遇到的问题:

1. 由于openpyxl在读取/写入工作簿时不会保留图像，因此在附加过滤的数据"表时会丢失图像.也许有一种解决方法，例如保存到单独的工作表并使用COM?
2. 尽管我可以使用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:

1. I can load the data into a list.
2. xlsx files are actually zip files so I can use zipfile and matplotlib to read the images from subdirectories display them.
3. It shouldn't be hard to add the checkbox thing and do the filtering.

The issues I am having:

1. 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?
2. 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中处理图像的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！