Python使用openpyxl操作Excel文件

Python使用openpyxl操作Excel文件

# Python使用openpyxl操作Excel文件:
# openpyxl安装命令：pip install openpyxl
# openpyxl支持格式:.xlsx  .xlsm  .xltx	.xltm (xls的格式，需手动改为.xlsx)


from openpyxl import Workbook, load_workbook
from openpyxl.styles import Font, PatternFill, Alignment, Border, fills, colors, Side


class excel_openpyxl:
    def __init__(self, file_name, sheet_index):
        self.file_name = file_name
        self.sheet_index = sheet_index

    def create_excel_file(self):
        try:
            # 实例化Workbook
            self.work_book = Workbook()
            # 保存Excel文件
            self.work_book.save(self.file_name)
        except Exception as error:
            print("Function create_excel_file error:", error)

    def load_work_book(self):
        try:
            # 打开Excel文件
            self.work_book = load_workbook(self.file_name)
            # 根据索引值,获取sheet对象
            self.work_sheet = self.work_book.worksheets[self.sheet_index]
        except Exception as error:
            print("Function load_work_book error=", error)

    def append_excel_row(self, rows_contents):
        try:
            # 在末尾,向表中插入行数据
            for i in range(len(rows_contents)):
                self.work_sheet.append(rows_contents[i])
            # 保存Excel文件
            self.work_book.save(self.file_name)
        except Exception as error:
            print("Function append_excel_row error=", error)

    def get_cell_data_by_coord(self, coord):
        try:
            # 获取单元格内容，如coord="B2"
            cell_value = self.work_sheet[coord].value
            # 返回单元格内容
            return cell_value
        except Exception as error:
            print("Function get_cell_data_by_coord error", error)

    def get_cell_data_by_num(self, row_num, col_num):
        try:
            # 根据单元格坐标，获取单元格对象，如：获取单元格"A1",row_num=1, col_num=1
            cell = self.work_sheet.cell(row_num, col_num)
            # print(cell, cell.value)
            # 返回单元格内容
            return cell.value
        except Exception as error:
            print("Function get_cell_data_by_num error=", error)

    def get_cells_data(self, coord):
        # 获取指定区域单元格的内容，
        # 指定区域，如：area_coord="A1:C2"（区域对角坐标(A1，C2))
        # 指定一行，如：area_coord="2" （第二行）
        # 指定一列，如：area_coord="A"  (第A列)
        # 指定多行，如：area_coord="1:3" (第1、2、3行)
        # 指定多列，如：area_coord="A:C"  (第A、B、C列)
        try:
            # 获取指定区域单元格内容
            cells = self.work_sheet[coord]
            # 返回指定区域单元格内容
            return cells
        except Exception as error:
            print("Function get_cells_data error=", error)

    def get_all_rows_data(self):
        try:
            # 按行获取所有数据
            cells = self.work_sheet.rows
            # 返回所有数据
            return cells
        except Exception as error:
            print("Function get_all_rows_data error=", error)

    def get_all_cols_data(self):
        try:
            # 按列获取所有数据
            cells = self.work_sheet.columns
            # 返回所有数据
            return cells
        except Exception as error:
            print("Function get_all_cols_data error=", error)

    def get_iter_rows_data(self, min_row, max_row, min_col, max_col):
        try:
            # 获取指定行的数据（注意：索引的最小值都是从1开始的）
            cells = self.work_sheet.iter_rows(min_row, max_row, min_col, max_col)
            # 返回指定行的数据
            return cells
        except Exception as error:
            print("Function get_iter_rows_data error=", error)

    def get_iter_cols_data(self, min_col, max_col, min_row, max_row):
        try:
            # 获取指定列的数据（注意：索引的最小值都是从1开始的）
            cells = self.work_sheet.iter_cols(min_col, max_col, min_row, max_row)
            # 返回指定列的数据
            return cells
        except Exception as error:
            print("Function get_iter_cols_data error=", error)

    def modify_cell_data(self, coord, content):
        # 修改指定单格的内容, 如：coord="B2"
        # 方式一：
        # self.work_sheet[coord] = content
        # 方式二：
        try:
            # 获取单元格对象
            cell = self.work_sheet[coord]
            # 修改单元格内容
            cell.value = content
            # 保存数据
            self.work_book.save(self.file_name)
        except Exception as error:
            print("Function modify_cell_data error=", error)

    def insert_rows(self, row_index, amount):
        try:
            # 插入空行，如：在第2行的位置，插入2行空数据，row_index=2,amount=2
            self.work_sheet.insert_rows(row_index, amount)
            # 保存数据
            self.work_book.save(self.file_name)
        except Exception as error:
            print("Function insert_rows error=", error)

    def insert_cols(self, col_index, amount):
        try:
            # 插入空列，如：在第2列的位置，插入2列空数据，row_index=2, amount=2
            self.work_sheet.insert_cols(col_index, amount)
            # 保存数据
            self.work_book.save(self.file_name)
        except Exception as error:
            print("Function insert_rows error=", error)

    def delete_rows(self, row_index, amount):
        try:
            # 删除行，如：在第2行的位置，删除2行数据，row_index=2,amount=2
            self.work_sheet.delete_rows(row_index, amount)
            # 保存数据
            self.work_book.save(self.file_name)
        except Exception as error:
            print("Function insert_rows error=", error)

    def delete_cols(self, col_index, amount):
        try:
            # 删除列，如：在第2列的位置，删除2列数据，row_index=2, amount=2
            self.work_sheet.delete_cols(col_index, amount)
            # 保存数据
            self.work_book.save(self.file_name)
        except Exception as error:
            print("Function insert_rows error=", error)

    def create_sheet(self, sheet_name):
        try:
            # 新建sheet
            self.work_book.create_sheet(sheet_name)
            # 保存数据
            self.work_book.save(self.file_name)
        except Exception as error:
            print("Function create_sheet error=", error)

    def remove_sheet(self, sheet_name):
        try:
            # 获取sheet对象
            sheet = self.work_book[sheet_name]
            # 删除sheet对象
            self.work_book.remove(sheet)
            # 保存数据
            self.work_book.save(self.file_name)
        except Exception as error:
            print("Function remove_sheet error=", error)

    def set_font(self, sheet_index):
        try:
            # 获取sheet对象
            sheet = self.work_book.worksheets[sheet_index]
            # 设置列宽
            sheet.column_dimensions["A"].width = 20
            sheet.column_dimensions["B"].width = 20
            sheet.column_dimensions["C"].width = 20
            # 设置单元格格式
            font = Font("微软雅黑", size=20, color=colors.BLACK, bold=False)
            fill = PatternFill(
                fill_type="solid", start_color="CDCDCD", end_color="CDCDCD"
            )  # CDCDCD浅灰色
            # 文字换行
            # wrap_text = True
            # 自适应宽度
            # shrink_to_fit = True
            # 单元格对齐方式
            alignment = Alignment(horizontal="center", vertical="center", indent=0)
            # 单元格边框
            bd = Border(
                left=Side(border_style="thin", color=colors.BLACK),
                right=Side(border_style="thin", color=colors.BLACK),
                top=Side(border_style="thin", color=colors.BLACK),
                bottom=Side(border_style="thin", color=colors.BLACK),
                outline=Side(border_style="thin", color=colors.BLACK),
                vertical=Side(border_style="thin", color=colors.BLACK),
                horizontal=Side(border_style="thin", color=colors.BLACK),
            )

            # 遍历数据,设置格式
            for irow, row in enumerate(sheet.rows, start=1):
                font = font
                fill = fill
                alignment = alignment
                border = bd
                for cell in row:
                    cell.font = font
                    cell.fill = fill
                    cell.alignment = alignment
                    cell.border = bd

            # 设置表头字体格式 # italic=True斜体
            header_font = Font("宋体", size=12, color=colors.BLUE, bold=True)
            sheet["A1"].font = header_font
            sheet["B1"].font = header_font
            sheet["C1"].font = header_font

            # 保存数据
            self.work_book.save(self.file_name)

        except Exception as error:
            print("Function set_font error=", error)


file_name = "openpyxl.xlsx"
rows_content = [["header1", "header2", "header3"], ["content1", "content2", "content3"]]

myExcel = excel_openpyxl(file_name, 0)
myExcel.create_excel_file()
myExcel.load_work_book()
myExcel.append_excel_row(rows_content)

cells = myExcel.get_cells_data("A1:C2")
for i in cells:
    for j in i:
        print(j.value)

cells = myExcel.get_cells_data("2")
for i in cells:
    print(i.value)

cells = myExcel.get_cells_data("A")
for i in cells:
    print(i.value)

cells = myExcel.get_cells_data("1:3")
for i in cells:
    for j in i:
        print(j.value)

cells = myExcel.get_cells_data("A:B")
for i in cells:
    for j in i:
        print(j.value)

myExcel.insert_rows(2, 2)
cells = myExcel.get_all_rows_data()
for i in cells:
    for j in i:
        print(j.value)

myExcel.insert_cols(2, 2)
cells = myExcel.get_all_cols_data()
for i in cells:
    for j in i:
        print(j.value)

myExcel.delete_rows(2, 2)
cells = myExcel.get_iter_rows_data(1, 2, 1, 3)
for i in cells:
    for j in i:
        print(j.value)

myExcel.delete_cols(2, 2)
cells = myExcel.get_iter_cols_data(1, 3, 1, 2)
for i in cells:
    for j in i:
        print(j.value)


print(myExcel.get_cell_data_by_coord("B2"))
myExcel.modify_cell_data("B2", "NewContent")
print(myExcel.get_cell_data_by_coord("B2"))

print(myExcel.work_book.sheetnames)
myExcel.create_sheet("New_Sheet")
print(myExcel.work_book.sheetnames)
myExcel.remove_sheet("New_Sheet")
print(myExcel.work_book.sheetnames)
myExcel.set_font(0)