python 操作 Excel 的基本操作——模块化

因最近工作需要将数据写入excel，并设置格式，编写此Excel操作模块，以便未来还要用到。

python复制代码# !/usr/bin/env python
# -*- coding: utf-8 -*-
"""
@Time       : 2021/7/21 11:31
@Author     : luoshixiang
@Email      : luoshixiang@shsnc.com
@File       : Parse_Excel.py
"""
 
import os
import openpyxl
from openpyxl.styles import PatternFill, Border, Side, Alignment, Protection, Font,colors
from openpyxl.drawing.image import Image
from openpyxl.chart import Series,LineChart,Reference
 
def create_excel(sheetname, filename):
    # 创建一个workbook对象
    wb = openpyxl.Workbook()
    # 激活一个sheet
    sheet = wb.active
    # 为sheet设置表名sheetname
    # sheet.title = 'Sheet1'
    for i in range(len(sheetname)):
        wb.create_sheet(sheetname[i])
    ws = wb['Sheet']
    wb.remove(ws)
    wb.save(filename)
 
import openpyxl
def write_excel(title, sheet_name, data, filename):
    # 创建一个workbook对象
    wb = openpyxl.Workbook()
    # 激活一个sheet
    sheet = wb.active
    # 为sheet设置一个表名sheetname
    sheet.title = sheet_name
    # 添加表头（不需要表头可以不用加）
    for i in range(len(title)):
        sheet.cell(row=1, column=i + 1, value=title[i])
    # 添加表内容
    for row_index, row_item in enumerate(data):  # 获取索引和内容
        for col_index, col_item in enumerate(row_item):
            sheet.cell(row=row_index + 2, column=col_index + 1, value=col_item)  # 写入
    wb.save(filename)
    return filename
 
class Parse_Excel(object):
    """解析excel文件"""
    def __init__(self, filename, sheet_name = None):
        try:
            if os.path.exists(filename) == True:
                # 获取绝对路径下的文件名称
                self.filename = os.path.realpath(filename)
                # 打开文件，获取excel文件的workbook（工作簿）对象
                self.__wb = openpyxl.load_workbook(self.filename, data_only=True)
                if sheet_name:
                    self.__sheet_name = sheet_name
                else:
                    # 获取活跃表单
                    self.__ws = self.__wb.active  # self.__ws的值与self.__wb[sheet_name]的值相同
                    # 获取活跃表名称
                    self.__sheet_name = self.__ws.title
            else:
                # 创建一个workbook对象
                self.__wb = openpyxl.Workbook()
                # 激活一个sheet
                self.__ws = self.__wb.active
                # 为sheet设置表名sheetname
                if sheet_name:
                    self.__wb.create_sheet(sheet_name)
                    self.__sheet_name = sheet_name
                    # self.__wb.save(filename)
                else:
                    self.__ws.title = 'Sheet1'
                self.__ws = self.__wb['Sheet']
                self.__wb.remove(self.__ws)
                self.__wb.save(filename)
                # 获取绝对路径下的文件名称
                self.filename = os.path.realpath(filename)
        except FileNotFoundError as e:
            raise e
    def save(self):
        self.__wb.save(self.filename)
 
    def create_sheets(self, sheet_name):
        for i in range(len(sheet_name)):
            self.__wb.create_sheet(sheet_name[i])
 
    def remove_sheets(self, sheet_name):
        for i in range(len(sheet_name)):
            self.__wb.remove(sheet_name[i])
 
    def get_max_row_num(self):
        """获取最大行号"""
        sheet_name = self.__sheet_name
        max_row_num = self.__wb[sheet_name].max_row
        return max_row_num
    def get_max_column_num(self):
        """获取最大列号"""
        sheet_name = self.__sheet_name
        max_column = self.__wb[sheet_name].max_column
        return max_column
    def get_row_value(self, row):
        """获取某一行的数据"""
        sheet_name = self.__sheet_name
        column_num = self.get_max_column_num()
        row_value = []
        if isinstance(row, int):
            for column in range(1, column_num + 1):
                values_row = self.__wb[sheet_name].cell(row, column).value
                row_value.append(values_row)
            # while None in values_row:   # 去除列表中的None值和nall值
            #     values_row.remove(None)
            return row_value
        else:
            raise TypeError('row must be type int')
 
    def get_column_value(self, column):
        """获取某一列数据"""
        sheet_name = self.__sheet_name
        row_num = self.get_max_row_num()
        column_value = []
        if isinstance(column, int):
            for row in range(1, row_num + 1):
                values_column = self.__wb[sheet_name].cell(row, column).value
                column_value.append(values_column)
            # while None in column_value:   # 去除列表中的None值和nall值
            #     column_value.remove(None)
            return column_value
        else:
            raise TypeError('column must be type int')
    def get_all_value(self):
        """获取指定表单的所有数据(除去表头)"""
        sheet_name = self.__sheet_name
        max_row_num = self.get_max_row_num()
        max_column = self.get_max_column_num()
        values = []
        for row in range(2, max_row_num + 1):
            value_list = []
            for column in range(1, max_column + 1):
                value = self.__wb[sheet_name].cell(row, column).value
                value_list.append(value)
            values.append(value_list)
        return values
 
    def get_all_value2(self):
        """获取指定表单的所有数据(包含表头)"""
        sheet_name = self.__sheet_name
        max_row_num = self.get_max_row_num()
        max_column = self.get_max_column_num()
        values = []
        for row in range(1, max_row_num + 1):
            value_list = []
            for column in range(1, max_column + 1):
                value = self.__wb[sheet_name].cell(row, column).value
                value_list.append(value)
            values.append(value_list)
        return values
    def get_excel_title(self):
        """获取sheet表头"""
        sheet_name = self.__sheet_name
        title_key = tuple(self.__wb[sheet_name].iter_rows(max_row=1, values_only=True))[0]
        return title_key
    def get_cell(self,location):
        sheet_name = self.__sheet_name
        cell_value = self.__wb[sheet_name][location].value
        return cell_value
    def get_cell_value(self,row,column):
        sheet_name = self.__sheet_name
        cell_value = self.__wb[sheet_name].cell(row,column).value
        return cell_value
 
    def my_border(self,t_border, b_border, l_border, r_border):
        # 定义边框样式
        border = Border(top=Side(border_style=t_border, color=colors.BLACK),
                        bottom=Side(border_style=b_border, color=colors.BLACK),
                        left=Side(border_style=l_border, color=colors.BLACK),
                        right=Side(border_style=r_border, color=colors.BLACK))
        return border
    # 初始化制定区域边框为所有框线
    def format_border(self,s_column, s_index, e_column, e_index):
        sheet_name = self.__sheet_name
        for row in tuple(self.__wb[sheet_name][s_column + str(s_index):e_column + str(e_index)]):
            for cell in row:
                cell.border = self.my_border('thin', 'thin', 'thin', 'thin')
 
    def set_solid_border(self,area_list):
        """给指定区域设置粗匣框线"""
        # 调用方式：pe.set_solid_border([['A', 3, 'D', 5], ['A', 6, 'D', 7],['A', 3, 'A', 10], ['B', 3, 'C', 10]])
        sheet_name = self.__sheet_name
        for area in area_list:
            s_column = area[0]
            s_index = area[1]
            e_column = area[2]
            e_index = area[3]
            #设置左粗框线
            for cell in self.__wb[sheet_name][s_column][s_index - 1:e_index]:
                cell.border = self.my_border(cell.border.top.style, cell.border.bottom.style,
                                        'medium', cell.border.right.style)
            # 设置右粗框线
            for cell in self.__wb[sheet_name][e_column][s_index - 1:e_index]:
                cell.border = self.my_border(cell.border.top.style, cell.border.bottom.style,
                                        cell.border.left.style, 'medium')
            # 设置上粗框线
            for row in tuple(self.__wb[sheet_name][s_column + str(s_index):e_column + str(s_index)]):
                for cell in row:
                    cell.border = self.my_border('medium', cell.border.bottom.style,
                                            cell.border.left.style, cell.border.right.style)
            # 设置下粗框线
            for row in tuple(self.__wb[sheet_name][s_column + str(e_index):e_column + str(e_index)]):
                for cell in row:
                    cell.border = self.my_border(cell.border.top.style, 'medium',
                                            cell.border.left.style, cell.border.right.style)
 
    def pattern_fill(self,rows,columns):
        """将单元格背景填充红色"""
        sheet_name = self.__sheet_name
        fill_set = PatternFill("solid", fgColor="00FF0000")
        # fill_set = PatternFill("solid", fgColor="00FFFFFF")
        font_set = Font(bold=True)
        for i in range(2, columns ):
            if int(self.__wb[sheet_name].cell(row = rows, column = i).value) >= 1:
                self.__wb[sheet_name].cell(row = rows, column = i).fill = fill_set
                self.__wb[sheet_name].cell(row=rows, column=i).font = font_set
 
    def pattern_fill_cell(self, row, column):
        # 将单元格标红
        sheet_name = self.__sheet_name
        fill_set = PatternFill("solid", fgColor="00FF0000")
        font_set = Font(bold=True)
        self.__wb[sheet_name].cell(row, column).border = self.my_border('thin', 'thin', 'thin', 'thin')
        self.__wb[sheet_name].cell(row, column).fill = fill_set
        self.__wb[sheet_name].cell(row, column).font = font_set
 
    def set_border_cells(self,rows,columns):
        """将rows行加粗"""
        sheet_name = self.__sheet_name
        fill_set = PatternFill("solid", fgColor="00FF0000")
        font_set = Font(bold=True)
        for i in range(1, columns + 1):
            self.__wb[sheet_name].cell(row=rows, column=i).font = font_set
 
    def write_cell(self, row, column, value):
        sheet_name = self.__sheet_name
        if isinstance(row, int) and isinstance(column, int):
            try:
                cell_obj = self.__wb[sheet_name].cell(row, column)
                cell_obj.value = value
            except Exception as e:
                raise e
        else:
            raise TypeError('row and column must be type int')
 
    def write_sheet(self, data, title=None, value=None, bold=False):
        sheet_name = self.__sheet_name
        if title:
            # 添加表头（不需要表头可以不用加）
            self.write_sheet_title(title)
        # 开始遍历数组
        for row_index, row_item in enumerate(data):   # 获取索引和内容
            for col_index, col_item in enumerate(row_item):
                # 写入
                self.__wb[sheet_name].cell(row=row_index + 2, column=col_index + 1, value=col_item)
 
 
    def write_sheet_from_column(self, data, column, title=None, value=None, bold=False):
        sheet_name = self.__sheet_name
        if title:
            # 添加表头（不需要表头可以不用加）
            self.write_sheet_title(title)
        # 开始遍历数组
        for row_index, row_item in enumerate(data):   # 获取索引和内容
            for col_index, col_item in enumerate(row_item):
                # 写入
                self.__wb[sheet_name].cell(row=row_index + 2, column=col_index +1 + column, value=col_item)
 
 
    def write_sheet_from_positive_cell_row(self, data, row=2, column=1, title=None, value=None, bold=False):
        sheet_name = self.__sheet_name
        if title:
            # 添加表头（不需要表头可以不用加）
            self.write_sheet_title(title)
        # 开始遍历数组
        for row_index, row_item in enumerate(data):   # 获取索引和内容
            for col_index, col_item in enumerate(row_item):
                # 写入
                self.__wb[sheet_name].cell(row=row_index + row, column=col_index + column, value=col_item)
 
    def write_sheet_from_positive_cell_col(self, data, row=2, column=1, title=None, value=None, bold=False):
        sheet_name = self.__sheet_name
        if title:
            # 添加表头（不需要表头可以不用加）
            self.write_sheet_title(title)
        # 开始遍历数组
        for col_index, row_item in enumerate(data):   # 获取索引和内容
            for row_index, col_item in enumerate(row_item):
                # 写入
                self.__wb[sheet_name].cell(row=row_index + row, column=col_index + column, value=col_item)
 
 
    def write_sheet_title(self,sheet_title):
        # 添加表头
        sheet_name = self.__sheet_name
        for i in range(len(sheet_title)):
            self.__wb[sheet_name].cell(row=1, column=i + 1, value=sheet_title[i])
 
    def write_row_data(self,rows, sheet_data):
        # 追加一行数据
        sheet_name = self.__sheet_name
        for i in range(len(sheet_data)):
            self.__wb[sheet_name].cell(row=rows + 1, column=i+1, value=sheet_data[i])
 
    def write_column_data(self,columns, sheet_data):
        # 向max_column右边追加一列数据,从第二行开始写
        sheet_name = self.__sheet_name
        for i in range(len(sheet_data)):
            self.__wb[sheet_name].cell(row=i + 2, column=columns+1, value=sheet_data[i])
 
    def write_column_data2(self,columns, sheet_data):
        # 向max_column右边追加一列数据,从第一行开始写
        sheet_name = self.__sheet_name
        for i in range(len(sheet_data)):
            self.__wb[sheet_name].cell(row=i + 1, column=columns+1, value=sheet_data[i])
 
    def delete_row_v(self, rows, columns, values):
        sheet_name = self.__sheet_name
        for i in range(2, rows +1):
            if self.__wb[sheet_name].cell(row = i, column = columns).value == values:
                self.__wb[sheet_name].delete_rows(i, 1)
 
    def delete_row(self, row):
        sheet_name = self.__sheet_name
        self.__wb[sheet_name].delete_rows(row, 1)
 
    def delete_all_value(self,row=1):
        sheet_name = self.__sheet_name
        max_row = self.get_max_row_num()
        self.__wb[sheet_name].delete_rows(row,max_row)
        # for i in range(max_row+1):
        #     self.__wb[sheet_name].delete_rows(i)
 
    def get_tail10_value(self, row_num , num):
        """获取后10行的数据"""
        sheet_name = self.__sheet_name
        column_num = self.get_max_column_num()
        if isinstance(row_num, int):
            if row_num < num:
                for row in range(2, row_num + 1):
                    row_value = []
                    for column in range(1, column_num + 1):
                        values_row = self.__wb[sheet_name].cell(row, column).value
                        row_value.append(values_row)
                    print(row_value)
            else:
                for row in range(row_num - num, row_num + 1):
                    row_value = []
                    for column in range(1, column_num + 1):
                        values_row = self.__wb[sheet_name].cell(row, column).value
                        row_value.append(values_row)
                    print(row_value)
        else:
            raise TypeError('row must be type int')
 
    def get_bf_value(self, row_num):
        """获取后10行的数据"""
        sheet_name = self.__sheet_name
        column_num = self.get_max_column_num()
        if isinstance(row_num, int):
            row_value = []
            for row in range(row_num - 11, row_num + 1):
                col_value = []
                for column in range(1, column_num + 1):
                    value_col = self.__wb[sheet_name].cell(row, column).value
                    col_value.append(value_col)
                row_value.append(col_value)
            return row_value
        else:
            raise TypeError('row must be type int')
 
    def get_bf_value2(self, row_num):
        """获取后10行的数据"""
        sheet_name = self.__sheet_name
        column_num = self.get_max_column_num()
        if isinstance(row_num, int):
            row_value = []
            for row in range(row_num - 11, row_num + 1):
                col_value = []
                for column in range(1, column_num + 1):
                    value_col = self.__wb[sheet_name].cell(row, column).value
                    if type(value_col) == int:
                        value_col = "%.2f%%" % (value_col * 100)
                    col_value.append(value_col)
                row_value.append(col_value)
            return row_value
        else:
            raise TypeError('row must be type int')
 
    def get_bf_value3(self,row_begin, row_end, col_begin, col_end):
        """获取后10行的数据"""
        sheet_name = self.__sheet_name
        # column_num = self.get_max_column_num()
        if isinstance(row_end, int):
            row_value = []
            for row in range(row_begin, row_end + 1):
                col_value = []
                for column in range(col_begin, col_end + 1):
                    value_col = self.__wb[sheet_name].cell(row, column).value
                    if type(value_col) == float:
                        value_col = "%.2f%%" % (value_col * 100)
                    col_value.append(value_col)
                row_value.append(col_value)
            return row_value
        else:
            raise TypeError('row must be type int')
 
    def set_percent(self,row,column,row2=None, column2=None):
        # 将单元格格式设置为百分比显示
        sheet_name = self.__sheet_name
        fill_set = PatternFill("solid", fgColor="00FF0000")
        font_set = Font(bold=True)
        if not row2:
            row2 = self.get_max_row_num()
        if not column2:
            column2 = self.get_max_column_num()
        for i in range(row, row2 + 1):
            for j in range(column, column2 +1):
                self.__wb[sheet_name].cell(i, j).number_format = '0.00%'
 
    def set_percent_column(self,column):
        # 将单元格格式设置为百分比显示
        sheet_name = self.__sheet_name
        max_row =self.get_max_row_num()
        for i in range(1,max_row + 1):
            self.__wb[sheet_name].cell(i, column).number_format = '0.00%'
 
    def sum_column(self,column):
        # 对列中数字类型单元格进行求和，将结果写在列的最后一行
        data = self.get_column_value(column)
        sum = 0
        for i in data:
            if type(i) == int:
                sum += i
        return sum
 
    def set_date_row(self,row):
        # 将单元格格式设置为百分比显示
        sheet_name = self.__sheet_name
        # 设置字体
        font_set = Font(name='宋体', color='000000', size=12, bold=True)
        max_col =self.get_max_column_num()
        for i in range(1,max_col + 1):
            self.__wb[sheet_name].cell(row, i).font = font_set
            self.__wb[sheet_name].cell(row, i).number_format = 'mm月dd日'
 
    def merge_cells(self,loc1,loc2): # loc1示例：'A1'
        # 合并单元格
        sheet_name = self.__sheet_name
        # 设置对齐方式，水平是右对齐，垂直是居中
        align = Alignment(horizontal='center', vertical='center', wrap_text=True)
        # 设置字体
        font_set = Font(name='宋体', color='000000', size=11, bold=False)
        self.__wb[sheet_name].merge_cells(loc1+":"+loc2)
        # self.__wb[sheet_name].merge_cells(start_row=2, start_column=1, end_row=4, end_column=4)   # 与上一条等价
        self.__wb[sheet_name][loc1].alignment = align
        self.__wb[sheet_name][loc1].font = font_set
 
    def unmerge_cells(self,sr,sc,er,ec):
        # 分割单元格
        sheet_name = self.__sheet_name
        # self.__wb[sheet_name].unmerge_cells('A2:D2')
        # self.__wb[sheet_name].unmerge_cells(start_row=2, start_column=1, end_row=4, end_column=4)   # 与上一条等价
        self.__wb[sheet_name].unmerge_cells(start_row=sr, start_column=sc, end_row=er, end_column=ec)
 
    def add_image(self,img_path,loc):  # loc示例：'A1'
        # 插入图像
        sheet_name = self.__sheet_name
        # 制作一个图片
        img = Image(img_path)
        # 在单元格旁边添加工作表和锚
        self.__wb[sheet_name].add_image(img, loc)
 
    def column_dimensions(self,col_name1,col_name2):    # col_name1示例：'A'
        # 折叠列
        sheet_name = self.__sheet_name
        self.__wb[sheet_name].column_dimensions.group(col_name1,col_name2, hidden=True)
 
    def add_formula(self,loc,formula):
        # 添加公式
        sheet_name = self.__sheet_name
        self.__wb[sheet_name][loc] = formula  # ws["A1"] = "=SUM(1, 1)"
 
 
    def add_line_chart(self,title, min_row, min_col, line_name, save_img, max_row=None, max_col=None):
        sheet_name = self.__sheet_name
        self.chart = LineChart()
        self.chart.title = title  # 图的标题
        self.chart.style = 2  # 线条的style
        self.chart.width = 17
        self.chart.height = 6.5
        # self.chart.y_axis.title = '次数'  # y坐标的标题
        # self.chart.x_axis.number_format = 'mm-dd'  # 规定日期格式  这是月,年格式
        # self.chart.x_axis.majorTimeUnit = "Months"  # 规定日期间隔 注意days；Months大写
        # self.chart.x_axis.title = "时间点"  # x坐标的标题
        if not max_row:
            max_row = self.get_max_row_num()
        if not max_col:
            max_col = self.get_max_column_num()
 
        for i in range(2,max_col+1):
            # 选中要画图的数据列(Y轴)
            data = Reference(self.__wb[sheet_name], min_col=i, min_row=min_row, max_col=i,
                                max_row=max_row)  # 图像的数据 起始行、起始列、终止行、终止列
            seriesObj = Series(data, title="'" + line_name[i - 2])
            self.chart.append(seriesObj)
        # self.chart.add_data(data, titles_from_data=True, from_rows=True)
        # 指定X轴选取的数据列
        dates = Reference(self.__wb[sheet_name], min_col=1, min_row=min_row, max_col=1, max_row=max_row)
        self.chart.set_categories(dates)
        self.__wb[sheet_name].add_chart(self.chart, save_img)  # 将图表添加到 sheet中的：A1

其中有一部分内容：向excel中输入公式，仅适用于结论，不适用于中间处理过程，若excel公式处理结果还被调用的话，需要另想他法。

本文转载自: 掘金

开发者博客 – 和开发相关的 这里全都有