Python读写Xlsx的常用方法

'''
import sys
sys.path.append('本文件所在的目录，如 D:\Fanjc\PythonProjects')
# .py文件（模块）名称是ReadAndWriteXlsx
from ReadAndWriteXlsx import readXlsx, readXlsxTargetSheet, saveAsXlsx, getCurDir, showSheetData, showXlsxData
'''

from openpyxl import load_workbook
from openpyxl import Workbook
import os
import sys

def readXlsx(path):
    '''读入xlsx'''
    # 输出字典：sheet名-sheet内容
    ret = {}
    wb = load_workbook(path)
    for sheetName in wb.sheetnames:
        ret[sheetName] = xlsxSheetDataToList(wb[sheetName])
    return ret
    
def readXlsxTargetSheet(path, sheetName):
    '''读入xlsx中具体某个sheet'''
    # 输出二维列表：sheet内容
    wb = load_workbook(path)
    return xlsxSheetDataToList(wb[sheetName])

def xlsxSheetDataToList(sheetData):
    '''将sheetData内容转为二维列表'''
    ret = []
    # true表示直接获取值，false表示获取的是cell，需通过cell.value获取值
    for row in sheetData.iter_rows(values_only=True):
        rowData = []
        for v in row:
            rowData.append(v)
        ret.append(rowData)
    return ret

def saveAsXlsx(path, content):
    '''创建xlsx，将内容存入'''
    defaultSheetName = 'Sheet1'
    # 字典：sheet名-sheet内容（二维列表）
    inputData = content
    if not isinstance(inputData,dict):
        inputData = {defaultSheetName:inputData}
    # 创建表格
    wb = Workbook()
    for sheetName in inputData.keys():
        ws = wb.create_sheet(title=sheetName)
        for row in inputData[sheetName]:
            ws.append(row)
    # 保存
    wb.remove(wb[wb.sheetnames[0]])
    os.makedirs(os.path.dirname(path),exist_ok=True)
    wb.save(path)
    print('保存了文件：',os.path.basename(path),'\n',path)

def getCurDir():
    '''获取当前文件所在目录'''
    return os.path.dirname(sys.argv[0])

def showSheetData(data):
    '''二维数据'''
    for row in data:
        print(row)
def showXlsxData(data):
    '''以字典形式存储的表数据'''
    for sheetName in data.keys():
        print("Sheet:",sheetName)
        showSheetData(data[sheetName])
        print('-')

if __name__ == "__main__":
    '''测试上述方法'''
    # path = os.path.join(getCurDir(),'测试.xlsx')
    # 读写全表
    # data = readXlsx(path)
    # showXlsxData(data)
    # saveAsXlsx(os.path.join(getCurDir(),'导出.xlsx'),data)

    # 读单一Sheet
    # data =readXlsxTargetSheet(path,'手术室')
    # showSheetData(data)
    # saveAsXlsx(os.path.join(getCurDir(),'导出.xlsx'),data)