python基于openpyxl读写excel

# !/usr/bin/env python 
# -*- coding: utf-8 -*- 
# @Time : 2021/4/18 13:45 
# @Author : 陈磊
"""
资源链接：https://download.csdn.net/download/qq_37754459/16755721
1. 支持表格不存在和存在两个情况,不存在则创建新的excel, 存在则加载
2. 实现对 xlsx 格式的 excel 表格的 增删改查&样式设置&宽高设置
3. 表格数据变化后实时可查(不用再次实例化)

依赖:
    1. openpyxl

全局变量:
    1. self.filename        文件名称
    2. self.wb              工作簿(excel)对象
    3. self.sheet_names     当前工作簿(excel)拥有sheet(表格)列表
    5. self.active_sheet    当前sheet(表格)对象

功能:
    基础:
        1. create_sheet()       创建sheet(表格)
        2. switch_sheet()       切换sheet(表格)
        3. save()               保存excel
    增加:
        1. add_row_data()       有效行下增加一行数据
        2. batch_add_row_data() 批量有效行下增加多行数据

    查询:
        1. get_row_data()       获取指定行数据
        2. get_col_data()       获取指定列数据
        3. get_cell_data()      获取指定单元格数据
        4. get_sheet_data()     获取整个表的数据
        5. find_data()          获取指定值在sheet(表格)中的坐标

    替换:
        1. replace_row_data()   替换指定行数据
        2. replace_cell_data()  替换指定单元格数据

    删除:
        1. delete_row()         删除整行数据
        2. delete_col()         删除整列数据
        3. delete_sheet()       删除整个表格
"""
from pathlib import Path
import openpyxl
from openpyxl.styles import PatternFill, Font, Border, Side, Alignment


class Excel(object):

    def __init__(self, filename, sheet):
        self.filename = filename
        self.wb = None
        self.active_sheet = None
        if not Path(self.filename).exists():
            # 目标表格不存在
            # 创建工作簿后会自动创建一个名为 Sheet 的表格
            wb = openpyxl.Workbook()
            # 创建||重命名 sheet
            if isinstance(sheet, list):
                # 如果传入的sheet是list,则以list中的值创建表格
                active_sheet = wb.active
                active_sheet.title = sheet[0]
                for sheet_name in sheet[1::]:
                    wb.create_sheet(sheet_name)
            else:
                # 重命名默认创建表格的名称为传入的sheet名称
                active_sheet = wb.active
                active_sheet.title = sheet
            self.wb = wb
            self.active_sheet = active_sheet
        else:
            # 目标表格已经存在
            wb = openpyxl.load_workbook(self.filename)
            if not isinstance(sheet, str):
                raise Exception("sheet {},类型错误,应该是表格sheet名称".format(sheet))

            try:
                index = wb.sheetnames.index(sheet)
            except ValueError:
                print("sheet {},不存在".format(sheet))
            else:
                wb._active_sheet_index = index
                self.active_sheet = wb.active
                self.wb = wb

        self.sheet_names = self.wb.sheetnames

    def switch_sheet(self, sheet_name):
        """
        切换当前操作的sheet
        :param sheet_name: sheet的 名称
        """
        try:
            index = self.wb.sheetnames.index(sheet_name)
        except ValueError:
            print("sheet {},不存在".format(sheet_name))
        else:
            self.wb._active_sheet_index = index
            self.active_sheet = self.wb.active

    def create_sheet(self, sheet_name):
        """
        创建一个新的sheet
        :param sheet_name: sheet的 名称
        :return list: 当前的 sheet 列表
        """
        self.wb.create_sheet(sheet_name)
        self.sheet_names = self.wb.sheetnames
        return self.sheet_names

    def save(self):
        """
        保存当前所有操作
        """

        def abc(index):
            """
            忽略它吧,它只是个字母表
            """
            _abc = ['A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T',
                    'U', 'V', 'W', 'X', 'Y', 'Z']
            if index >= 1 and index <= 26:
                return _abc[index - 1]
            else:
                return "{}{}".format(_abc[index // 26 - 1], _abc[index - 26 * (index // 26) - 1])

        # 背景色
        fill = PatternFill(fill_type='solid', fgColor="ffffff")

        # 字体
        font = Font(size=10, bold=True, color="2F0000")

        # 边框
        thin = Side(border_style="thin", color="2F0000")
        border = Border(top=thin, left=thin, right=thin, bottom=thin)

        # 对齐方式 horizontal 左右, vertical 上下, wrapText 自动换行
        alignment = Alignment(horizontal='left', vertical='center', wrapText=True)

        row_number = 1
        for row_data in self.active_sheet.rows:
            col_number = 1
            self.active_sheet.row_dimensions[row_number].height = 40  # 设置每行的行高
            for cell_data in row_data:
                self.active_sheet.cell(row_number, col_number, cell_data.value).fill = fill  # 设置背景色
                self.active_sheet.cell(row_number, col_number, cell_data.value).font = font  # 设置字体
                self.active_sheet.cell(row_number, col_number, cell_data.value).border = border  # 设置边框
                self.active_sheet.cell(row_number, col_number, cell_data.value).alignment = alignment  # 设置对齐方式
                self.active_sheet.column_dimensions[abc(col_number)].width = 40  # 设置每列的列宽
                col_number += 1
            row_number += 1
        self.wb.save(self.filename)

    def add_row_data(self, data):
        """
        增加一行数据
        :param data: [1,2,3,4]
        """
        self.active_sheet.append(data)

    def batch_add_row_data(self, data):
        """
        增加行行数据
        :param data: [[1,2,3,4],[1,2,3,4]]
        """
        for item in data:
            self.active_sheet.append(item)

    def get_row_data(self, row):
        """
        获取一行的数据
        :param row: 行号
        :return list: [1,2,3]
        """
        number = 1
        temp_list = []
        for rows in self.active_sheet.rows:
            if isinstance(row, int) and row == number:
                for cell_data in rows:
                    temp_list.append(cell_data.value if cell_data.value else "")
            number += 1
        return temp_list

    def get_col_data(self, col):
        """
        获取一列的数据
        :param col: 列号
        :return list: [1,2,3]
        """
        number = 1
        temp_list = []
        for columns in self.active_sheet.columns:
            if isinstance(col, int) and col == number:
                for cell_data in columns:
                    temp_list.append(cell_data.value if cell_data.value else "")
            number += 1
        return temp_list

    def get_sheet_data(self):
        """
        获取整个sheet的数据
        :return list: [[1,2,3][1,2,3]]
        """
        all_list = []
        for row_data in self.active_sheet.rows:
            row_list = []
            for cell_data in row_data:
                row_list.append(cell_data.value if cell_data.value else "")
            all_list.append(row_list)
        return all_list

    def get_cell_data(self, row, col):
        """
        获取指定单元格的数据
        :param row: 行号
        :param col: 列号
        :return str: 值
        """
        return self.active_sheet.cell(row=row, column=col).value

    def find_data(self, data):
        """
        查找数据
        :param data: 数据
        :return: [[row,col],[row,col]] 返回该数据在表格内的坐标
        """
        row = 1
        index_list = []
        for row_data in self.active_sheet.rows:
            col = 1
            for cell_data in row_data:
                if data == cell_data.value:
                    index_list.append([row, col])
                col += 1
            row += 1
        return index_list[0] if len(index_list) == 1 else index_list

    def replace_cell_data(self, row, col, data):
        """
        替换指定单元格数据
        :param row: 行号
        :param col: 列号
        :param data: 数据
        :return str: 替换后的数据
        """
        self.active_sheet.cell(row=row, column=col).value = data

        return self.active_sheet.cell(row=row, column=col).value

    def replace_row_data(self, row, data):
        """
        替换指定行数据
        :param row: 行号
        :param data: 数据
        :return: 替换后的数据
        """
        col = 1
        for cell_data in data:
            self.active_sheet.cell(row=row, column=col).value = cell_data
            col += 1
        return self.get_row_data(row)

    def delete_row(self, row):
        """
        删除整行内容
        :param row: 行号
        :return list: 删除后的所有数据
        """
        self.active_sheet.delete_rows(row)

        return self.get_sheet_data()

    def delete_col(self, col):
        """
        删除整行内容
        :param col: 列号
        :return list: 删除后的所有数据
        """
        self.active_sheet.delete_cols(col)

        return self.get_sheet_data()

    def delete_sheet(self, sheet_name):
        """
        删除整个sheet
        :param sheet_name: 表格名称
        :return list: 删除后的所有sheet列表
        """
        if len(self.sheet_names) <= 1:
            raise Exception("只有一个sheet了,不允许删除")
        self.wb.remove(self.wb[sheet_name])
        self.sheet_names = self.wb.sheetnames
        return self.sheet_names


if __name__ == '__main__':
    # 1. 实例excel
    e = Excel("test123.xlsx", "old_sheet")
    # 2. 增加一行
    e.add_row_data([1, 2, 3, 4, 5])
    # 3. 替换第一行 第二列 单元格的值为 hello
    e.replace_cell_data(1, 2, "hello")
    # 4. 创建一个新的sheet名字为 new_sheet
    e.create_sheet("new_sheet")
    # 5. 切换到new_sheet表格中
    e.switch_sheet("new_sheet")
    # 6. 增加一行
    e.add_row_data([5, 4, 3, 2, 1])
    # 7. 设置单元格样式&保存所有操作到excel
    e.save()