• 企业400电话
  • 微网小程序
  • AI电话机器人
  • 电商代运营
  • 全 部 栏 目

    企业400电话 网络优化推广 AI电话机器人 呼叫中心 网站建设 商标✡知产 微网小程序 电商运营 彩铃•短信 增值拓展业务
    Python办公自动化之Excel(中)

    准备

    首先,我们需要安装依赖包

    # 安装依赖包
    pip3 install openpyxl

    读取数据

    使用 openpyxl 中的 load_workbook(filepath) 加载本地一个 Excel 文件,返回结果是一个工作簿对象

    import openpyxl
    
    # 加载本地的Excel文件
    wb = openpyxl.load_workbook(file_path)

    利用工作簿对象,可以获取所有的 Sheet 名称及 Sheet 列表

    def get_all_sheet_names(wb):
        """
        获取所有sheet的名称
        :param wb:
        :return:
        """
        # sheet名称列表
        sheet_names = wb.sheetnames
        return sheet_names
    
    
    def get_all_sheet(wb):
        """
        获取所有的sheet
        :param wb:
        :return:
        """
        # sheet名称列表
        sheet_names = get_all_sheet_names(wb)
    
        # 所有sheet
        sheets = []
        for sheet_name in sheet_names:
            sheet = wb[sheet_name]
            sheets.append(sheet)
    
        return sheets

    工作簿对象提供了 active 属性,用于快速获取当前选择的 Sheet

    def get_current_sheet(wb):
        """
        获取当前选择的sheet,默认是最后一个sheet
        :param wb:
        :return:
        """
        # 当前选中的sheet
        current_sheet = wb.active
    
        return current_sheet

    另外,也可以通过 Sheet 名称去获取某一个特定的 Sheet 对象

    def get_sheet_by_name(wb, sheet_name):
        """
        通过sheetname去查找某一个sheet
        :param wb:
    ​    :param sheet_name:
        :return:
        """
        sheet_names = get_all_sheet_names(wb)
        if sheet_name in sheet_names:
            result = wb[sheet_name]
        else:
            result = None
        return result

    使用 sheet.max_row 和 sheet.max_column 可以获取当前 Sheet 中的数据行数和列数

    def get_row_and_column_num(sheet):
        """
        获取sheet的行数和列数
        :param sheet:
        :return:
        """
        # 行数
        row_count = sheet.max_row
        # 列数
        column_count = sheet.max_column
    
        return row_count, column_count
    
    # 行数和列数
    row_count, column_count = get_row_and_column_num(sheet)
    print('行数和列数分别为:', row_count, column_count)

    openpyxl 提供 2 种方式来定位一个单元格,分别是:

    数字索引:行数字索引、列数字索引

    比如:row_index=1,column_index=1

    字符串索引:列由字母组成 + 行索引

    比如:A1 对应第一行、第一列的单元格

    并且,openpyxl.utils 提供了方法,便于 列索引 在两者之间进行转换

    from openpyxl.utils import get_column_letter, column_index_from_string
    
    def column_num_to_str(num):
        """
        Excel索引列从数字转为字母
        :param num:
        :return:
        """
        return get_column_letter(num)
    
    
    def column_str_to_num(str):
        """
        Excel索引列,从字母转为数字
        :param str:
        :return:
        """
        return column_index_from_string(str)

    单元格的获取,同样可以通过上面 2 种索引方式来获取

    def get_cell(sheet, row_index, column_index):
        """
        获取单元格
        :param sheet:
        :param row_index:
        :param column_index:
        :return:
        """
        # openpyxl索引都是从1开始计数,这与xlrd有所不同
        # 获取某一个单元格(二选一)
        # 比如:获取A1单元格的数据,即第一个行、第一列的数据
        # cell_one = sheet['A1']
        cell_one = sheet.cell(row=row_index, column=column_index)
        return cell_one

    在日常处理 Excel 数据过程中,可能需要判断单元格数据类型,而 openpyxl 并没有提供现成的方法

    这里,我们可以通过单元格对象的 value 属性拿到值,接着使用 isinstance 方法判断数据类型

    def get_cell_value_and_type(cell):
        """
        获取某一个cell的内容及数据类型
        :param cell:
        :return:
        """
        # 单元格的值
        cell_value = cell.value
        # 单元格的类型
        cell_type = get_cell_value_type(cell_value)
    
        return cell_value, cell_type
    
    def get_cell_value_type(cell_value):
        """
        获取数据类型
        :param cell_value:
        :return:
        """
        # 其中
        # 0:空
        # 1:数字
        # 2:字符串
        # 3:日期
        # 4:其他
        if not cell_value:
            cell_type = 0
        elif isinstance(cell_value, int) or isinstance(cell_value, float):
            cell_type = 1
        elif isinstance(cell_value, str):
            cell_type = 2
        elif isinstance(cell_value, datetime.datetime):
            cell_type = 3
        else:
            cell_type = 4
        return cell_type

    单独获取某一行[列]的数据,可以使用下面的方式:

    def get_row_cells_by_index(sheet, row_index):
        """
        通过行索引,获取某一行的单元格
        :param row_index:
        :return:
        """
        # 注意:第一列从1开始
        row_cells = sheet[row_index]
        return row_cells
    
    
    def get_column_cells_by_index(sheet, column_index):
        """
        通过列索引,获取某一列的单元格
        """
        # 数字转为字母
        column_index_str = column_num_to_str(column_index)
        # 获取某一列的数据
        column_cells = sheet[column_index_str]
        return column_cells

    需要注意的是,获取某一行的数据需要传入数字索引;而对于列数据的获取,必须传入字符串索引

    和 Python 列表范围取值类似,openpyxl 同样支持使用 : 符号拿到某个范围内的数据行[列]

    def get_rows_by_range(sheet, row_index_start, row_index_end):
        """
        通过范围去选择行范围
        比如:选择第2行到第4行的所有数据,返回值为元组
        :param sheet:
        :param row_index_start:
        :param row_index_end:
        :return:
        """
        rows_range = sheet[row_index_start:row_index_end]
        return rows_range
    
    
    def get_columns_by_range(sheet, column_index_start, column_index_end):
        """
        通过范围去选择列范围
        比如:选择第2列到第4列的所有数据,返回值为元组
        :param sheet:
        :param column_index_start:
        :param column_index_end:
        :return:
        """
        columns_range = sheet[column_num_to_str(column_index_start):column_num_to_str(column_index_end)]
        return columns_range

    写入数据

    要写入数据到 Excel 表格

    首先,使用 openpyxl.Workbook() 创建一个 Excel 工作簿对象

    接着,使用工作簿对象的 create_sheet() 新建一个 Sheet

    # 创建一个Excel工作簿
    # 注意:每次新建一个Excel文件,都会默认生成一个名称为【Sheet】的工作表Sheet
    wb = openpyxl.Workbook()
    
    # 创建一个新的sheet,默认被插到尾部
    # new_sheet = wb.create_sheet('新的Sheet')
    # 也可以通过第二个参数:index来指定插入的位置
    # 比如:插入到开头
    new_sheet = wb.create_sheet('新的Sheet', 0)

    默认创建的 Sheet 被插入到最后一个位置,第 2 个参数可以指定 Sheet 插入的位置

    Sheet 标签的背景色同样支持修改,使用 sheet_properties.tabColor 指定 RGB 颜色值

    比如,要设置某一个 Sheet 的背景色为红色,只需要先查询到对应的 Sheet,然后指定颜色值为 FF0000 即可

    def set_sheet_bg_color(sheet, rgb_value):
        """
        设置Sheet标签的颜色
        :param rgb_value:
        :return:
        """
        # 设置Sheet底部按钮的颜色(RRGGBB)
        sheet.sheet_properties.tabColor = rgb_value
    
     # 设置Sheet的背景色(红色)
    set_sheet_bg_color(new_sheet, 'FF0000')

    openpyxl 支持行列数字索引、字符串索引以这 2 种方式写入数据到单元格中

    def write_value_to_cell_with_num(sheet, row_index, column_index, value):
        """
        按行索引、列索引写入数据
        :param shell:
        :param row_index: 行索引
        :param column_index: 列索引
        :param value:
        :return:
        """
        # 二选一
        sheet.cell(row=row_index, column=column_index, value=value)
        # shell.cell(row=row_index, column=column_index).value = value
    
    
    def write_value_to_cell_with_index_str(sheet, index_str, value):
        """
        按字母位置,写入数据到对应单元格
        :param shell:
        :param index_str: 字母对应的单元格位置
        :param value:
        :return:
        """
        sheet[index_str] = value

    在单元格中插入图片也很简单,openpyxl 提供的 add_image() 方法

    参数有 2 个,分别是:图片对象、单元格字符串索引

    为了便于使用,我们可以将列索引进行转换,然后封装成两个插入图片的方法

    from openpyxl.drawing.image import Image
    
    def insert_img_to_cell_with_num(sheet, image_path, row_index, column_index):
        """
        往单元格中插入图片
        :param sheet:
        :param image_path:
        :param row_index:
        :param column_index:
        :return:
        """
        # 通过行索引、列索引,获取到字母索引
        index_str = column_num_to_str(column_index) + str(row_index)
        insert_img_to_cell_with_str(sheet, image_path, index_str)
    
    def insert_img_to_cell_with_str(sheet, image_path, index_str):
        """
        往单元格中插入图片
        :param sheet:
        :param image_path:
        :param index_str:
        :return:
        """
        sheet.add_image((image_path), index_str)

    最后,调用工作簿对象的 save() 方法,将数据真实写入到 Excel 文件中

    # 注意:必须要写入,才能真实的保存到文件中
    wb.template = False
    wb.save('new.xlsx')

    修改数据

    修改数据包含:单元格数据的修改、单元格样式的修改

    对于单元格数据的修改,只需要先读取工作簿对象,查询到要操作的 Sheet 对象,然后调用上面的方法修改单元格数据,最后调用 save() 函数保存覆盖即可

    def modify_excel(self, file_path):
        """
        修改本地Excel文件中数据
        :param file_path:
        :return:
        """
        # 读取本地Excel文件
        wb = openpyxl.load_workbook(file_path)
    
        # 读取某一个sheet
        sheet = wb['第一个Sheet']
        print(sheet)
    
        # 直接修改某一个单元格的数据
        write_value_to_cell_with_num(sheet, 1, 1, '姓名1')
    
        # 保存并覆盖
        wb.save(file_path)

    单元格样式包含:字体样式、单元格背景样式、边框样式、对齐方式等

    以常见的字体样式、对齐方式为例

    首先,使用 openpyxl 中的 Font 类创建一个对象,指定字体名称、字体大小、是否加粗、是否斜体、颜色、下划线等

    from openpyxl.styles import Font
    
    # 字体格式
    # 指定字体类型、大小、是否加粗、颜色等
    font0 = Font(name='Calibri',
                 size=20,
                 bold=False,
                 italic=False,
                 vertAlign=None,  
                 underline='none', 
                 strike=False,
                 color='FF00FF00')

    接着,构建一个 Alignment 对象,指定单元格的对齐方式

    from openpyxl.styles import Font,Alignment
    
    # 单元格对齐方式
    alignment0 = Alignment(horizontal='center',
                           vertical='bottom',
                           text_rotation=0,
                           wrap_text=False,
                           shrink_to_fit=False,
                           indent=0)

    最后,使用单元格对象的 font/alignment 属性,将字体样式和对齐方式设置进去即可

    # 设置属性样式(字体、对齐方式)
    sheet['A1'].font = font0
    sheet['A1'].alignment = alignment0

    进阶用法

    接下来,聊聊几个常用的进阶用法

    1、获取可见及隐藏的 Sheet

    通过判断 Sheet 对象的 sheet_state 属性值,可以判断当前 Sheet 是显示还是隐藏

    当值为 visible 时,代表 Sheet 是显示的

    当值是 hidden 时,代表这个 Sheet 被隐藏了

    def get_all_visiable_sheets(wb):
        """
        获取工作簿中所有可见的sheet
        :param wb:
        :return:
        """
        return [sheet for sheet in get_all_sheet(wb) if sheet.sheet_state == 'visible']
    
    
    def get_all_hidden_sheets(wb):
        """
        获取工作簿中所有隐藏的sheet
        :param wb:
        :return:
        """
        return [sheet for sheet in get_all_sheet(wb) if sheet.sheet_state == 'hidden']

    2、获取隐藏/显示的行索引列表、列索引列表

    受限于篇幅,这里以获取所有显示/隐藏的行索引列表为例

    遍历 Sheet 对象的 row_dimensions 属性值,通过判断行属性的 hidden 值,判断当前行是否隐藏或显示

    def get_all_rows_index(sheet, hidden_or_visiable):
        """
        获取所有隐藏/显示的行
        :param hidden_or_visiable:  True:隐藏;False:显示
        :param sheet:
        :return:
        """
        # 遍历行
        # 隐藏的索引
        hidden_indexs = []
    
        # 所有隐藏的行索引
        for row_index, rowDimension in sheet.row_dimensions.items():
            if rowDimension.hidden:
                hidden_indexs.append(row_index)
    
        # 所有显示的行索引
        visiable_indexs = [index + 1 for index in range(get_row_and_column_num(sheet)[0]) if index + 1 not in hidden_indexs]
    
        # 隐藏或者显示的行索引列表
        return hidden_indexs if hidden_or_visiable else visiable_indexs

    3、获取单元格字体颜色及单元格背景颜色

    单元格对象的 font.color.rgb、fill.fgColor.rgb 属性值分别代表字体颜色值、单元格背景颜色

    def get_cell_font_color(sheet, row_index, column_index):
        """
        获取单元格字体的颜色
        :param sheet:
        :param row_index:行索引
        :param column_index:列索引
        :return:
        """
        cell_color = sheet.cell(row_index, column_index).font.color
        if cell_color:
            return sheet.cell(row_index, column_index).font.color.rgb
        else:
            # 颜色不存在,可能单元格没有数据
            return None
    
    
    def get_cell_bg_color(sheet, row_index, column_index):
        """
        获取单元格背景的颜色
        :param sheet:
        :param row_index:行索引
        :param column_index:列索引
        :return:
        """
        return sheet.cell(row_index, column_index).fill.fgColor.rgb

    最后

    可以发现,openpyxl 相比 xlrd/xlwt,提供了大量实用的 API,功能更强大,并且完美支持 xlsx!

    受限于篇幅,文中只展示了部分功能和代码,更加复杂的功能,比如:单元格合并、单元格完整样式操作,我已经封装成方法上传到后台

    代码地址:https://github.com/xingag/test_auto/tree/master/office_auto/Excel

    以上就是Python办公自动化之Excel(中)的详细内容,更多关于Python Excel自动化的资料请关注脚本之家其它相关文章!

    您可能感兴趣的文章:
    • python实现自动化办公邮件合并功能
    • Python办公自动化之将任意文件转为PDF格式
    • Python办公自动化之教你用Python批量识别发票并录入到Excel表格中
    • python 办公自动化——基于pyqt5和openpyxl统计符合要求的名单
    • 6个Python办公黑科技,助你提升工作效率
    上一篇:PyTorch梯度裁剪避免训练loss nan的操作
    下一篇:解决Pytorch半精度浮点型网络训练的问题
  • 相关文章
  • 

    © 2016-2020 巨人网络通讯 版权所有

    《增值电信业务经营许可证》 苏ICP备15040257号-8

    Python办公自动化之Excel(中) Python,办公自动化,之,Excel,