Python 读写excel文件

示例

# excel库xlsx
import openpyxl
import xlrd
import tkinter.filedialog as filedialog
import os

def parseXLSX(filename, sheetNum=1):
    """
    解析excel文件, 并返回列表
    param: filename 文件名, sheetNum sheet页序号
    return:[{title:value},{title:value}]
    """
    rslt = []
    try:
        # 通过 openpyxl 加载
        wb = openpyxl.load_workbook(filename)
        # 获取sheet页对象 编号从0开始
        sheet = wb._sheets[sheetNum-1]
        print(sheet.max_row, sheet.max_column)

        title = []
        # 获取列标题
        for y in range(0, sheet.max_column):
            colname = str(sheet[1][y].value)
            title.append(colname)
        # print(title)
        # 遍历
        for row in sheet.rows:
            i = 0
            tmp = {}
            for cell in row:
                tmp[title[i]] = cell.value
                i = i+1
            rslt.append(tmp)
    except Exception as e:
        print(e)
    return rslt


def parseXLS(filename, sheetNum=1):
    rslt = []
    try:
        wb = xlrd.open_workbook(filename)
        # 获取sheet页对象 编号从0开始
        sheet = wb.sheet_by_index(0)

        rows = sheet.nrows
        cols = sheet.ncols
        print(rows, cols)

        # 遍历
        for rowi in range(rows):
            row = []
            for coli in range(cols):
                row.append(sheet.cell_value(rowi, coli))
            rslt.append(row)
    except Exception as e:
        print(e)
    return rslt

def appendXLSX(data,filename,sheetNum=1):
    """
    向xlsx填加新数据
    data: 二维数组
    filename: 文件名
    sheetNum: 页序号
    """
    try:
        # 通过 openpyxl 加载
        wb = openpyxl.load_workbook(filename)
        # 获取sheet页对象 编号从0开始
        sheet = wb._sheets[sheetNum-1]
        # print(sheet.max_row, sheet.max_column)
        for row in data:
            sheet.append(row)
        wb.save(filename)
        # print(sheet.max_row, sheet.max_column)
    except Exception as e:
        print(e)

if __name__ == "__main__":
    # 选择文件夹中所有xls写入xlsx
    dir = filedialog.askdirectory(initialdir="d:\\")
    # print(vfilename)
    filelist = os.listdir(dir)
    for fn in filelist:
        data = parseXLS(os.path.join(dir,fn))
        # print(data)
        appendXLSX(data,'C:/Users/CXL2021_PC/Desktop/新建文件夹/1.xlsx')

if __name__ == "__main__":
    # 读取选中的xlsx
    vfilename = filedialog.askopenfilename(initialdir="d:\\")
    # print(vfilename)
    data = parseXLSX(vfilename)
    print(data)