Python操作excel

openpyxl实现

python读取excel数据

import datetime
import openpyxl
import os
# 打印当前工作目录
# print(os.getcwd())
# 打印当前时间
print("当前时间：" + str(datetime.datetime.now()))
# 加载excel文件，'\'符号需要转义，使用'\\'符号替换'\'
wb = openpyxl.load_workbook("D:\\xuexi\\python\\table.xlsx")
# print(wb.sheetnames)
# 选择sheet页
sheet = wb["Sheet1"]

# 自定义文件写入函数
def writefile(text, fielname, path, isnotoverwrite):
    filepath = path + fielname
    # 写入文件，a:追加，w：覆盖
    with open(filepath, isnotoverwrite,encoding='utf-8') as f:
        f.write(text)
    f.close()
# %s %12 ：将数字12转化为字符串格式
flag = "%s" %sheet.cell(row=1,column=1).value
# 根据行号遍历sheet页数据
for hang in range(sheet.min_row, sheet.max_row):
    str = ""
    # 根据列数遍历sheet页数据
    for lie in range(sheet.min_column, sheet.max_column):
        # 打印遍历的单元格内的数据
        print(sheet.cell(row=hang, column=lie).value , end=" ")
        # print( " [" + type(sheet.cell(row=hang, column=lie).value) + "] ",end="")
        print( type(sheet.cell(row=hang, column=lie).value) , end="")
        if isinstance(sheet.cell(row=hang, column=lie).value,int):
            str = str + "%s" %sheet.cell(row=hang, column=lie).value
        else:
            str = str + sheet.cell(row=hang, column=lie).value
        if sheet.cell(row=hang,column=1) != flag:
            writefile(str, "a.txt", "D:\\xuexi\\python\\pythonProject1\\resource\\", "a")
            writefile("\n", "a.txt", "D:\\xuexi\\python\\pythonProject1\\resource\\", "a")
            flag = sheet.cell(row=hang,column=1)
        # if lie == 3:
        #     print("ok" ,end="  ")
        #     str + sheet.cell(row=hang, column=lie).value + ""
        # else :
        #     str = str + sheet.cell(row=hang, column=lie).value
    # writefile(str,"a.txt","D:\\xuexi\\python\\pythonProject1\\resource\\","a")
    writefile("\n", "a.txt", "D:\\xuexi\\python\\pythonProject1\\resource\\", "a")
    print("")

函数封装：读取excel，写入excel

import os
import openpyxl


def readExcel(path, fileName, sheetName):
    """
        path 要读取文件路径
        fileName 要读取的excel文件名称
        sheetName 要读取的sheet页名称
        data 返回的读取到的数据数组,list数组类型
    """
    lieData = []
    hangData = []
    if not os.path.exists(os.path.join(path, fileName)):
        print("文件【" + os.path.join(path, fileName) + "】不存在,")
    else:
        # 读取文件
        wb = openpyxl.load_workbook(os.path.join(path, fileName))
        # 选择sheet页
        sheet = wb[sheetName]
        for hang in range(sheet.min_row, sheet.max_row):
            for lie in range(sheet.min_column, sheet.max_column + 1):
                lieData.append(sheet.cell(row=hang, column=lie).value)
            hangData.append(lieData)
            lieData = []
    return hangData


def writeExcel1(path, fileName, sheetName, data):
    success = "失败"
    print("进入writeExcel函数")

    print("*******%%%%%")
    print(type(data))
    print("*******")
    # 判定数据data是否是数组列表类型
    if isinstance(data, list) or isinstance(data, tuple):
        print("写入数据")
        # 打开文件
        wb = openpyxl.load_workbook(os.path.join(path, fileName))
        # 选择sheet页
        sheet1 = wb[sheetName]
        # 循环每个单元格，写入数据
        for hang in range(len(data)):
            # print("  >> " + str(hang) + "  " + str(data[hang]))
            for lie in range(len(data[hang])):
                sheet1.cell(row=hang + 1, column=lie + 1, value=data[hang][lie])
        # 保存数据，同时关闭文件
        wb.save(os.path.join(path, fileName))
        success = "成功"
    else:
        print("文件格式错误")
    return success

def writeExcel(path, fileName, sheetName, data):
    """
        path 要写入文件路径，不存在会自动创建
        fileName 要写入的excel文件名称，没有会自动创建
        sheetName 要写入的sheet页名称，sheet页不存在会自动创建
        data 要写入的数据数组,list数组类型
    """
    flag = 0
    success = "失败"
    print("进入writeExcel函数")
    if not os.path.exists(os.path.join(path, fileName)):
        print("文件[" + os.path.join(path, fileName) + "]不存在,开始创建文件!")
        # 创建一个工作簿
        wb = openpyxl.Workbook()
        # 创建一个test_case的sheet表单
        wb.create_sheet(sheetName)
        # 保存为一个xlsx格式的文件
        wb.save(os.path.join(path, fileName))
        print("文件【" + os.path.join(path, fileName) + "】的sheet页[" + sheetName + "]创建成功")
    else:
        print("文件[" + os.path.join(path, fileName) + "]存在.")
    print("11文件【" + os.path.join(path, fileName) + "】存在,开始判定sheet页[" + sheetName + "]是否存在")
    # 第一步：打开工作簿
    wb = openpyxl.load_workbook(os.path.join(path, fileName))
    for sheet1 in wb:
        if sheet1.title == sheetName:
            flag = 1
            break
    if str(flag) == "1":
        # sheet页存在,
        print("文件【" + os.path.join(path, fileName) + "】的sheet页[" + sheetName + "]存在,开始写入数据")
    else:
        wb.create_sheet(sheetName)
        print("文件【" + os.path.join(path, fileName) + "】的sheet页[" + sheetName + "]创建成功,开始写入数据")
        wb.save(os.path.join(path, fileName))
    print("*******%%%%%")
    print(type(data))
    print("*******")
    # 判定数据data是否是数组列表类型
    if isinstance(data, list) or isinstance(data, tuple):
        print("写入数据")
        # 打开文件
        wb1 = openpyxl.load_workbook(os.path.join(path, fileName))
        # 选择sheet页
        sheet1 = wb1[sheetName]
        # 循环每个单元格，写入数据
        print("len(data) {}".format(len(data)))
        for hang in range(len(data)):
            # print("  >> " + str(hang) + "  " + str(data[hang]))
            for lie in range(len(data[hang])):
                sheet1.cell(row=hang + 1, column=lie + 1, value=data[hang][lie])
        # 保存数据，同时关闭文件
        wb1.save(os.path.join(path, fileName))
        success = "成功"
    else:
        print("文件格式错误")
    return success

# writeExcel1(path,fileName,sheetName,data)
basePath = os.getcwd()
filePath = os.path.join(basePath, "test.xlsx")
print("filePath [{}]".format(filePath))

readDatas = readExcel(basePath, 'test.xlsx', 'read')
print("--- readDatas ---")
print(readDatas)
print("-----------")
tu = (
    ('序号', '表英文名', '表中文名'),
    ('1', 'ISSUER_CARD_INFO', '发卡信息表'),
    ('2', 'H_CCS_CUSTER', '客户基本资料'),
    ('3', 'H_CCS_ADDR', '客户地址信息接口')
)
status = writeExcel(basePath, 'test.xlsx', 'write1', tu)
print("status : {}".format(status))

#
# print(tu)
# print(type(tu))
# print("-----------")
# print(tu[0][0])
# for i in range(len(tu)):
#     print(tu[i])
#     for j in range(len(tu[i])):
#         print(tu[i][j], end = "")
# print("-----------")
# for i in range(len(readDatas)):
#     print(tu[i])
#     for j in range(len(tu[i])):
#         print(tu[i][j], end = "")
#