[Python知识库]用python对excel进行批量处理

前言

事情是这样的，npy说今天的工作里有一个900个项目的excel，表格的样子大概如下图所示。需求呢是将下图中的url全部替换成对应的图片添加到excel中。 作为一个程序员，这样的任务要是手动做真是太难为人了。 所以打算帮npy减轻一下负担。

一开始我搜excel的python处理都是给我推荐xlrd包，但是这个包只能读取不能写入。然后继续搜搜到了xlwt， 但是它又只能写入不能修改。最后找到了openpyxl这个包。

处理

不想写心路历程了， 总之就是request下载图片，openpyxl添加图片， 以及对于excel中合并单元格的一些处理。
因为感觉以后npy可能还会有这样的活，所以花了一晚上造了一点轮子，在博客上存一手代码，准备下次再用。

from openpyxl import *
from openpyxl.drawing.image import Image
from openpyxl.cell import MergedCell
from openpyxl.drawing.spreadsheet_drawing import OneCellAnchor, AnchorMarker
from openpyxl.utils.units import pixels_to_EMU, cm_to_EMU
from openpyxl.drawing.xdr import XDRPoint2D, XDRPositiveSize2D

from io import BytesIO
import requests
from tqdm import tqdm
import traceback


class ExcelOp(object):
    def __init__(self, file, sheet_name="Sheet1"):
        self.file = file
        self.wb = load_workbook(self.file)
        self.ws = self.wb[sheet_name]
        # self.max_rows, self.max_cols = self.ws.max_row, self.ws.max_column

    # 获取某个单元格的值
    def get_cell_value(self, row, column):
        cell = self._parser_merged_cell(row, column)
        cell_value = cell.value
        return cell_value

    # 获取某列的所有值
    def get_col_value(self, column):
        rows = self.ws.max_row
        column_data = []
        for i in range(1, rows + 1):
            cell_value = self.get_cell_value(row=i, column=column)
            column_data.append(cell_value)
        return column_data

    # 获取某行所有值
    def get_row_value(self, row):
        columns = self.ws.max_column
        row_data = []
        for i in range(1, columns + 1):
            cell_value = self.get_cell_value(row=row, column=i)
            row_data.append(cell_value)
        return row_data

    # 设置某个单元格的值
    def set_cell_value(self, row, colunm, cellvalue):
        cell = self._parser_merged_cell(row, colunm)
        try:
            cell.value = cellvalue
        except:
            cell.value = "ERROR:writefail"

    # 在某个单元格上添加一张图
    def set_image(self, fp: BytesIO, row: int, column: int, img_pixel_height=None, img_pixel_width=None):
        cell = self._parser_merged_cell(row, column)
        image_data = Image(fp)
        w = image_data.width if img_pixel_width is None else img_pixel_width
        h = image_data.height if img_pixel_height is None else img_pixel_height
        size = XDRPositiveSize2D(pixels_to_EMU(w), pixels_to_EMU(h))
        # https://stackoverflow.com/questions/55309671/more-precise-image-placement-possible-with-openpyxl-pixel-coordinates-instead
        # AnchorMarker 它的row和col 又从0开始数了 好烦
        marker = AnchorMarker(col=cell.column-1, row=cell.row-1)
        image_data.anchor = OneCellAnchor(_from=marker, ext=size)
        # image_data.anchor = "A1"
        self.ws.add_image(image_data)

    def _parser_merged_cell(self, row, col):
        """
        检查是否为合并单元格并获取对应行列单元格的值。
        如果是合并单元格，则取合并区域左上角单元格的值作为当前单元格的值,否则直接返回该单元格的值
        :param sheet: 当前工作表对象
        :param row: 需要获取的单元格所在行
        :param col: 需要获取的单元格所在列
        :return: 
        """
        cell = self.ws.cell(row=row, column=col)
        if isinstance(cell, MergedCell):  # 判断该单元格是否为合并单元格
            for merged_range in self.ws.merged_cells.ranges:  # 循环查找该单元格所属的合并区域
                if cell.coordinate in merged_range:
                    # 获取合并区域左上角的单元格作为该单元格的值返回
                    cell = self.ws.cell(
                        row=merged_range.min_row, column=merged_range.min_col)
                    break
        return cell


if __name__ == "__main__":
    excel = ExcelOp(file="./1.xlsx")
    excel.ws.column_dimensions['G'].width = 65  # 设置单元格宽
    urls = excel.get_col_value(7)  # excel行和列都是从1开始数  7对应G
    try:
        for row, url in tqdm(enumerate(urls[1:])):
            row = row+2  # python从0开始数  excel从1开始  且excel第一行是title 所以加2
            if url.endswith('_30x30.jpg'):
                url = url[0:-10]
            excel.ws.row_dimensions[row].height = 250  # 设置单元格高
            excel.set_cell_value(row, 7, '')  # 删除原有的url
            r = requests.get(url=url, headers={"user-agent": "Mozilla/5.0"})
            if r.status_code != 200:
                print(r.status_code, " in line ", row)
            # 文件操作open()的返回值就是BytesIO 或 StringIO
            excel.set_image(BytesIO(r.content), row, 7, 305.5, 405.8)
    except:
        print(traceback.format_exc())
        excel.wb.save("2_witherror.xlsx")
    excel.wb.save("2.xlsx")