用python對(duì)excel文件進(jìn)行一般的處理，十幾行代碼就能做很多事了。
xlrd和xlwt這兩個(gè)庫(kù)可以操作excel，其中xlrd是讀excel，xlwt是寫excel的庫(kù)，但是相比而言我還是更喜歡大一統(tǒng)的openpyxl。
openpyxl可以很方便的讀寫excel文件，包括單元格樣式，合并等，這里只是簡(jiǎn)單寫個(gè)demo入一下門。話不多說(shuō)，直接上代碼。
待讀取的excel文件內(nèi)容如下：

image.png

# -*- coding: UTF-8 -*-
import os

import openpyxl


def read_excel(filepath, attrOpt=None, rownumOfHeader=1):
    """
    讀取excel數(shù)據(jù)
    :param filepath: excel文件路徑
    :param attrOpt: 屬性字典，key為屬性名稱，value為列序號(hào)（從1開(kāi)始）；對(duì)于每行數(shù)據(jù)會(huì)根據(jù)其序號(hào)賦值給對(duì)應(yīng)的屬性名，封裝成一個(gè)字典類型；不傳或傳空則是一個(gè)有序數(shù)組
    :param rownumOfHeader: 表頭占用的行數(shù)，數(shù)據(jù)讀取會(huì)跳過(guò)表頭，默認(rèn)表頭占1行
    :return:
    """
    wb = openpyxl.load_workbook(filepath)
    ws = wb.active
    dataArr = []
    rownumStart = rownumOfHeader
    for r in range(rownumStart, ws.max_row):
        if attrOpt is None or len(attrOpt) == 0:
            rowData = []
            for c in range(ws.max_column):
                rowData.append(ws.cell(r + 1, c + 1).value)
            dataArr.append(rowData)
        else:
            rowData = {}
            for attrName, index in attrOpt.items():
                rowData[attrName] = ws.cell(r + 1, index).value
            dataArr.append(rowData)
    return dataArr


def write_excel(filepath, dataArr):
    """
    寫excel
    :param filepath: 文件路徑
    :param dataArr: 數(shù)據(jù)集
    :return:
    """
    wb = openpyxl.Workbook()
    ws = wb.worksheets[0]
    for i in range(0, len(dataArr)):
        rowData = dataArr[i]
        if rowData is not None and len(rowData) > 0:
            for j in range(0, len(rowData)):
                ws.cell(i + 1, j + 1).value = rowData[j]
    wb.save(filepath)


def append_rows(filepath, dataArr):
    """
    追加數(shù)據(jù)行，若文件不存在則創(chuàng)建并寫入
    :param filepath: 文件路徑
    :param dataArr: 數(shù)據(jù)集
    :return: 
    """
    if not os.path.exists(filepath):
        write_excel(filepath, dataArr)
        return
    wb = openpyxl.load_workbook(filepath)
    ws = wb.worksheets[0]
    maxRow = ws.max_row
    for i in range(0, len(dataArr)):
        rowData = dataArr[i]
        if rowData is not None and len(rowData) > 0:
            for j in range(0, len(rowData)):
                ws.cell(maxRow + i + 1, j + 1).value = rowData[j]
    wb.save(filepath)


if __name__ == '__main__':
    attrs = {'id': 1, 'username': 2, 'email': 3, 'mobile': 4, 'registerTime': 5}
    dataArr = read_excel('E:/test/demo.xlsx', attrs)
    print(dataArr)
    datas = [['123', '張三三', 'zhangsansan@qq.com'], ['124', '李思思', 'lisisi@qq.com']]
    write_excel('E:/test/writedemo.xlsx', datas)

上述代碼執(zhí)行之后，讀取后的數(shù)據(jù)如下：

[{'id': 111, 'username': '張三', 'email': 'zhangshan@abc.com', 'mobile': 13111121113, 'registerTime': datetime.datetime(2019, 3, 15, 12, 15, 12, 4)}, {'id': 112, 'username': '李四', 'email': 'lisi@abc.com', 'mobile': 15912535642, 'registerTime': datetime.datetime(2019, 3, 16, 12, 15, 12, 4)}, {'id': 113, 'username': '王五', 'email': 'wangwu@abc.com', 'mobile': 15841415252, 'registerTime': datetime.datetime(2019, 3, 17, 12, 15, 12, 4)}, {'id': 114, 'username': '趙六', 'email': 'zhaoliu@abc.com', 'mobile': 17712453698, 'registerTime': datetime.datetime(2019, 3, 18, 12, 15, 12, 4)}]