python-openpyxl库常用方法封装

提示：文章写完后，目录可以自动生成，如何生成可参考右边的帮助文档

前言

平时工作中需要整理数据的场景比较多，所以常用到openpyxl库的方法，为了自己方便，就把常用的一些方法封装到一起。
后续想法直接连接数据源，通过连接数据源，执行sql后将数据根据sql语句中列写入到excel中。当前版本只是excel一些方法的封装

---

一、使用库

1.openpyxl
2.os
3.sys

二、使用步骤

1.主要方法

数据的写入、读取、合并单元格和增加边框线

2.具体实现

代码如下：

import os
import sys

from openpyxl import Workbook
from openpyxl.styles import Border, Side

PATH = os.path.dirname(os.path.dirname(__file__))
test_path = os.path.dirname(__file__)
import openpyxl as openpyxl
'封装openxy库'

class OpenXlTool():
    '初始化excel数据'
    def __init__(self,filepath):
        self.filepath =filepath
        self.open_excel = openpyxl.load_workbook(filename=self.filepath)

    #根据下标获取excel列表sheet数据
    def getSheetsData(self,index):
        try:
            sheet_data = self.open_excel.worksheets[index-1]

        except IndexError:

            print( '当前excel共有{0}个sheet，输入的下标{1}'.format(len(self.getSheetsList),index-1))
            sys.exit()
        return sheet_data

    '创建sheet页'
    def add_sheet_name(self,sheet_name):
        sheet_list = self.getSheetsList
        if sheet_name in sheet_list:
            sys.exit('sheet名称已存在，当前excel，sheet名称%s'%(sheet_list))
        '获取当前excel表sheet总长，在最后插入新sheet'
        len_sheet = len(self.getSheetsList)-1
        self.open_excel.create_sheet(sheet_name,len_sheet+1)
        self.saveExcel(self.filepath)


    '创建新excel表格'
    @staticmethod
    def addExcel(filename):
        '创建工作表对象'
        create_work = Workbook()
        create_work.create_sheet('result')
        curr_path = os.getcwd()
        file_name = curr_path+ '\\' +filename
        # if os.path.exists(file_name):
        #     return '文件名称重复'
        # else:
        create_work.save(file_name)


    '获取表格sheet信息,返回list'
    @property
    def getSheetsList(self):
        return self.open_excel.sheetnames

    '读取excel表头数据--按列表输出。示例：[[1,2,3],[4,5,6]]'
    def getExcelDataList(self,index):
        row_line = self.getSheetsData(index).rows
        excel_data_list = []
        for row in row_line:
            excel_list =[]
            for cell in row:
                cell_values = str(cell.value)
                excel_list.append(cell_values)
            excel_data_list.append(excel_list)

        return excel_data_list
    '读取excel数据--按字典数据，示例:{[a:1,b:2,c:3],[a:4]}'
    def getExcelDataDict(self,index):
        excel_data = self.getExcelDataList(index)
        first_title = excel_data[0]
        data_list = []
        for i in range(1,len(excel_data)):
            data_dict = {}
            for j in range(len(excel_data[i])):
                data_dict[first_title[j]] =excel_data[i][j]
            data_list.append(data_dict)
        return data_list
    '获取指定行数据'
    def getRowData(self,row,index):
        excel_row = self.getExcelCol(index=index)
        if row > excel_row:
            sys.exit('不存在行，当前excel总行数为：{0},输入列数：{1}'.format(excel_row,row))
        row_list =[]
        for i in range(excel_row):
            row_list.append(self.getSheetsData(index).cell(row=row, column=i+1).value)
        return row_list
    '获取指定列数据'
    def getColData(self,col,index):
        excel_col = self.getExcelCol(index=index)
        if col > excel_col:
            sys.exit( '不存在列，当前excel总列数为：{0},输入列数：{1}'.format(excel_col,col))
        else:
            col_list =[]
            for i in range(excel_col):
                col_list.append(self.getSheetsData(index).cell(row=i+1, column=col).value)
            return col_list

    '获取excel表格总行数'
    def getExcelRow(self,index):
        total_row = self.getSheetsData(index).max_row
        return total_row

    '获取excel表格纵列数'
    def getExcelCol(self,index):
        total_cell = self.getSheetsData(index).max_column
        return total_cell

    '根据行与列读取excel数据'
    def getIndexData(self,row,col,index):
        try:
            cell_value = self.getSheetsData(index).cell(row=row, column=col).value
        except ValueError as e:
            return ("数据行与列值错误：{0}").format(e)
        except TypeError as e:
            return ("数据行与列类型错误：{0}").format(e)
        return cell_value
    '保存excel文件'
    def saveExcel(self,file_path):
        self.open_excel.save(file_path)


    '根据行与列写入数据'
    def writeIndexData(self,index,data=None):
        for i in range(len(data)):
            for j in range(len(data[i])):
                self.getSheetsData(index=index).cell(row=i+1,column=j+1).value=data[i][j]
        self.saveExcel(self.filepath)

    '比较单个excel表格两行数据是否相同，相同返回True，不同返回False'
    def compareRowData(self,l_col,r_col,index):
        self.getSheetsData(index=index)
        left_data = self.getColData(col=l_col,index=index)
        right_data = self.getColData(col=r_col,index=index)
        len_l = len(left_data)
        temp =0
        res_list = []
        while temp < len_l:
            if left_data[temp] == right_data[temp] and left_data[temp] !=None:
                res_list.append(True)
                temp +=1
            else:
                res_list.append(False)
                temp +=1
        return res_list


    #处理合并单元格的行数据
    def mergeRowData(self,index,merge_width):
        # 获取当前excel最大行和列，校验是否越界
        row_len = self.getExcelRow(index=index)
        row_merge_list = []
        # 根据合并宽度分解数据成二元列表
        if merge_width == 1:
            for i in range(1, row_len + 1, merge_width + 1):
                list = []
                for j in range(1):
                    list.append(i)
                    list.append(i + merge_width)
                row_merge_list.append(list)
        else:
            for i in range(1, row_len + 1, merge_width):
                list = []
                for j in range(1):
                    list.append(i)
                    list.append(i + merge_width - 1)
                row_merge_list.append(list)
        return row_merge_list
    #过滤列表中的空列表，返回新列表
    @staticmethod
    def filterData(data):
        filer_list =[]
        for i in range(len(data)):
            if data[i] !=[]:
                filer_list.append(data[i])
        return filer_list
    #处理合并单元格列数据
    def mergeColData(self,index,col_l,col_r,merge_width):
        # 获取当前excel最大行和列，校验是否越界
        col_len = self.getExcelCol(index=index)
        col_merge_list = []
        #列表推导式获取26个英文字母
        col_title = [chr(i) for i in range(65,91)]
        col_l_index = col_title.index(col_l)
        col_r_index = col_title.index(col_r)
        merge_col = col_title[col_l_index:col_r_index + 1]
        res = len(merge_col) // merge_width
        if col_len >= len(merge_col) and len(merge_col) >= merge_width:
            # 根据合并宽度分解数据成二元列表
            if merge_width == 1:
                inde_l = 0
                index_r = merge_width+1
                for i in range(res):
                    col_merge_list.append(merge_col[inde_l:index_r])
                    inde_l += 2
                    index_r += 2
            else:
                inde_l = 0
                index_r = merge_width
                for i in range(res):
                    col_merge_list.append(merge_col[inde_l:index_r])
                    inde_l += 2
                    index_r += 2

            return self.filterData(col_merge_list)
        else:
            return '当前列超过excel表格最大列，当前表格总列数为：%d,传入数据列：%d,分割列表的列为：%d'%(col_len,len(merge_col),merge_width)
  #合并单元格
    def merge_cell(self,index,col_l=None,col_r=None,flag=1,merge_width=None):
        temp = 0
        #根据flag判断是行合并还是列合并，1：行。2：列
        if flag ==1:
            #根据行合并单元格
                mergeRow = self.mergeRowData(index=index,merge_width=merge_width)
                while temp < len(mergeRow):
                    self.getSheetsData(index=index).merge_cells('{0}{1}:{0}{2}'.format(col_l,mergeRow[temp][0],mergeRow[temp][1]))
                    temp +=1
            #根据列合并单元格
        else:
            col_data = self.mergeColData(index=index,col_l=col_l,col_r=col_r,merge_width=merge_width)
            if isinstance(col_data,list):
                while temp < len(col_data):
                    #列默认只对首行进行合并
                    self.getSheetsData(index=index).merge_cells('{0}{2}:{1}{2}'.format(col_data[temp][0],col_data[temp][1],1))
                    print(col_data)
                    print(col_data[temp][0],col_data[temp][1])
                    temp +=1
            else:
                raise IndexError(col_data)
        self.saveExcel(self.filepath)


    #增加边框线
    def border(self,index):
        col_data = self.getExcelCol(index)
        row_data = self.getExcelRow(index)
        thin_broder = Border(left=Side(style='thin'),
                             right=Side(style='thin'),
                             top=Side(style='thin'),
                             bottom=Side(style='thin'))
        for i in range(1,col_data+1):
            for j in range(1,row_data+1):
                self.getSheetsData(index).cell(j,i).border=thin_broder
        self.saveExcel(self.filepath)

if __name__ == '__main__':

    oo = OpenXlTool('./testttttt.xlsx')

    # ww = oo.merge_cell(index=0,col_l='A',col_r='D',merge_width=1,flag=2)
    # zz = oo.mergeColData(index=0,col_l="A",col_r='D',merge_width=1)
    oo.writeIndexData(index=0)
    # print(ww)

---