python 封裝openpyxl實現(xiàn)excel讀寫功能

hdzgx 2020-01-06

展開全文

說到python讀寫excel,，想到的的就是xlrd,、xlwt,、openpyxl,、pandas這幾個庫吧,，最近用到讀寫excel，于是初略看了下這幾個庫的使用,，xlrd用于讀取excel的,，xlwt用于寫excel的，但不能寫后輟為.xlsx的文件,，openpyxl既能讀也能寫,，感覺是前面兩個的結(jié)合以及結(jié)合，用法也基本雷同,，pandas功能比較強大,，能讀能寫，還能各種數(shù)據(jù)分析,，在excel表格中能操作的,，幾乎在pandas中都能做到。xlrd讀取數(shù)據(jù)時,，可以直接到表格的一行轉(zhuǎn)成一個列表,，比較方便，openpyxl讀來每行是以cell為元素的列表,，還要再轉(zhuǎn)換一次(或許我還不知道怎么直接轉(zhuǎn)成列表的),，所以寫用了xlrd。由于xlwt不能寫.xlsx的文件,，于用寫用openpyxl,，pandas躍然功能強大，但對我來說感覺pandas還是比較復雜,，不好操作,，前兩個已滿足我現(xiàn)在的讀寫的功能需求。不管是xlrd還是openpyxl，讀寫的流程基本是一樣的,，寫初始化一個workbook,，再獲取一個sheet，然后讀或者寫數(shù)據(jù),，如果寫,，還要設置表格的樣式，最后workbook.save()下,。每次都這樣嫌麻煩,，于是想寫兩個方法封裝下。

def readExcel(path, sheetName=None):

第一個讀的方法,，接受一個已存在的Excel路徑，還有一個可選參數(shù)sheetName,，如果不指定,，默認讀第一個表格，返回一個兩層的列表[ [], [] ,....],第二層的列表每一個表示一行的數(shù)據(jù),，得到每行的數(shù)據(jù)后,，想怎么操作就看自己的了。

def writeExcel(path, data, title=None, row_height=18, merge_row_col=[]):

第二個寫的方法

path：寫入excel的文件路徑,，不存在新建,，存在的話就覆蓋

data：寫入excel的數(shù)據(jù)，格式為[[], [],...]或([], [],...)或[(), (),...]或((),，( ),...),，第二層每個元素表示一行數(shù)據(jù)，可以長度不一致,，表格的列數(shù)以最長的那個元素為準,，值為None或者長度達不到最達長度的，寫入excel時為空

title：表格名稱,，就是我們excel中的sheet1,sheet2,類型為字符串

row_height：設置表格行高,，默認為18，

merge_row_col：合并單元格的起止位置組合,，格式為({},{},...)或[{},{},...],，其中元素{}的key-value如下：

{
    "start_row": 2,
    "start_column": 4,
    "end_row": 3,
    "end_column": 4
}

然后這個方法把樣式寫死了，有需求自己改下,，把樣式的值放方法參數(shù)中,，可在外進行設置。

def judge_merge(merge_row_col):

這個方法是判斷傳入的merge_row_col是否符合合并的要求,，要求是兩次合并不能重疊

其他幾個方法是設置表格樣式的,，字體，對齊方式，邊框,，表格寬以該列最長的符的最高為標準來設置,，最小為8，最大為38,，若列寬需要寬度大于38,，則自動縮小文字至適應。

import xlrd
import openpyxl as xl
from openpyxl.styles import Alignment, Font, colors, Side, Border
import re
def readExcel(path, sheetName=None):
    workbook = xlrd.open_workbook(path)
    sheet = workbook.sheet_by_index(0)
    if sheetName:
        sheet = workbook.sheet_by_name(sheetName)
    data = []
    for ri in range(sheet.nrows):
        row = sheet.row_values(ri)
        data.append(row)
    return data
def writeExcel(path, data, title=None, row_height=18, merge_row_col=[]):
    if not isinstance(data, list) and not isinstance(data, tuple):
        raise BaseException("data只能是列表或元組")
    if len(data) > 0 and (not isinstance(data[0], list) and not isinstance(data[0], tuple)):
        raise BaseException("data只能是列表或元組")
    if len(merge_row_col) > 0:
        if not isinstance(merge_row_col, tuple) and not isinstance(merge_row_col, list):
            raise BaseException("merge_row_col只能是以dict為元素的列表或元組")
        elif not isinstance(merge_row_col[0], dict):
            raise BaseException("merge_row_col只能是以dict為元素的列表或元組")
        if not judge_merge(merge_row_col):
            raise BaseException("merge_row_col不符合要求,，不能合并")
    workbook = xl.Workbook()
    sheet = workbook.active
    if title:
        sheet = workbook.create_sheet(title=title, index=0)
    maxRow = len(data)
    maxCol = 0
    for row in data:
        maxCol = len(row) if maxCol < len(row) else maxCol
        sheet.append(row)
    col_length = {
    }
    '''
    合并單元格,放在設置單元格樣式之前,，否則無邊框
    '''
    for row_col in merge_row_col:
        sheet.merge_cells(
            start_row=row_col["start_row"],
            start_column=row_col["start_column"],
            end_row=row_col["end_row"],
            end_column=row_col["end_column"]
        )
    '''
    設置單元格樣式
    '''
    for r in range(1, maxRow + 1):
        sheet.row_dimensions[r].height = row_height
        for c in range(1, maxCol + 1):
            cell = sheet.cell(row=r, column=c)
            # 設置邊框
            cell.border = getBorder()
            re_hanzi = re.compile(r"[\u4E00-\u9FFF]")
            w = len(str(cell.value))
            # 有漢字乘以2，否則乘以1.5
            if re_hanzi.search(str(cell.value)):
                w *= 2
            else:
                w *= 1.5
            # 列寬最小為8,，最大為38,，超過38文字縮小至適合大小
            w = 8 if w < 8 else w
            if w > 38:
                w = 38
                cell.alignment = getAlignment(shrink_to_fit=True)
            else:
                cell.alignment = getAlignment()
            if col_length.get(cell.column):
                col_length[cell.column] = w if col_length[cell.column] < w else col_length[cell.column]
            else:
                col_length[cell.column] = w
            # 第一行加粗
            if r == 1:
                cell.font = getFont(bold=True)
            else:
                cell.font = getFont()
    '''
    設置列寬
    '''
    for key, val in col_length.items():
        sheet.column_dimensions[key].width = val + 0.7
    workbook.save(path)
'''
判斷合并單元格是否符合要求
'''
def judge_merge(merge_row_col):
    if len(merge_row_col) == 1:
        if merge_row_col[0]["start_row"] <= merge_row_col[0]["end_row"] and \
                merge_row_col[0]["start_column"] <= merge_row_col[0]["end_column"]:
            return True
        else:
            return False
    for i in range(len(merge_row_col)):
        tempi = merge_row_col[i]
        if not (tempi["start_row"] <= tempi["end_row"] and tempi["start_column"] <= tempi["end_column"]):
            return False
        if i == len(merge_row_col) - 1:
            break
        for j in range(i + 1, len(merge_row_col)):
            tempj = merge_row_col[j]
            if not (tempj["start_column"] > tempi["end_column"] or \
                    tempj["end_column"] < tempi["start_column"] or \
                    tempj["start_row"] > tempi["end_row"] or \
                    tempj["end_row"] < tempi["start_row"]):
                return False
    return True
def getAlignment(horizontal='center', vertical='center', shrink_to_fit=False, wrap_text=False, text_rotation=0,
                 indent=0):
    agt = Alignment(
        horizontal=horizontal,
        vertical=vertical,
        shrink_to_fit=shrink_to_fit,
        wrap_text=wrap_text,
        text_rotation=text_rotation,
        indent=indent
    )
    return agt
def getFont(name='宋體', size=12, bold=False, italic=False, vertAlign=None, underline=None, strike=False,
            color=colors.BLACK):
    font = Font(
        name=name,
        size=size,
        bold=bold,
        italic=italic,
        vertAlign=vertAlign,
        underline=underline,
        strike=strike,
        color=color
    )
    return font
'''
style的值為：
{'double', 'dashDot', 'mediumDashDot', 'dotted', 'medium', 'thick', 'dashDotDot', 'thin', 
'mediumDashDotDot', 'mediumDashed', 'slantDashDot', 'dashed', 'hair'}
'''
def getBorder():
    border = Border(
        left=Side(style='thin', color='FF000000'),
        right=Side(style='thin', color='FF000000'),
        top=Side(style='thin', color='FF000000'),
        bottom=Side(style='thin', color='FF000000')
    )
    return border

測試：

if __name__ == "__main__":
    data = (
        ["ID", "name", "age", "createDate", "title1", "title2"],
        [1, "楊", 23, "2018-12-09","rewf0"],
        [2, "張", 33, "2018-12-09", None, "很長很長的中文文字很長很長的中文文字很長很長的中文文字很長很長的中文文字",],
        [3, "王", 42, "2018-12-10","長中文文字長中文文字長中文文字長中文文字"]
    )
    merge_row_col = (
        {
            "start_row": 2,
            "start_column": 4,
            "end_row": 3,
            "end_column": 4
        },
    )
    writeExcel(path=r"C:\Users\dpyang\Desktop\temp\test.xlsx", data=data, merge_row_col=merge_row_col)