python实现对Excle表格的读写

Z时代
2024-01-10
分类：综合

python实现对Excle表格的读写[Python基础]

1、使用xlrd模块读取数据

# 将excel表格内容导入到tables列表中
def import_excel(tab):
# 创建一个空列表，存储Excel的数据
    tables = []
for rown in range(1, tab.nrows):
        array = {"设备名称": "", "框": "", "槽": "", "端口": "", "onuid": "", "认证密码": "", "load": "", "checkcode": ""}
        array["设备名称"] = tab.cell_value(rown, 0)
        array["框"] = tab.cell_value(rown, 1)
        array["槽"] = tab.cell_value(rown, 2)
        array["端口"] = tab.cell_value(rown, 3)
        array["onuid"] = tab.cell_value(rown, 4)
        array["认证密码"] = tab.cell_value(rown, 9)
        array["load"] = tab.cell_value(rown, 10)
        array["checkcode"] = tab.cell_value(rown, 11)
        tables.append(array)
return tables
# 导入需要读取Excel表格的路径
data = xlrd.open_workbook(r"G:	est.xlsx")
table = data.sheets()[0]
for i in import_excel(table):
print(i)

2、使用xlwt和openpyxl进行写出

import pandas as pd
# 要事先下载好xlwt和openpyxl模块
def export_excel(tab):
# 将字典列表转换为DataFrame
    pf = pd.DataFrame(list(tab))
# 指定字段顺序
    order = ["设备名称", "框", "槽", "端口", "onuid", "认证密码", "load", "checkcode"]
    pf = pf[order]
# 将列名替换为中文
    columns_map = {
"设备名称": "设备名称",
"框": "框",
"槽": "槽",
"端口": "端口",
"onuid": "onuid",
"认证密码": "认证密码",
"load": "load",
"checkcode": "checkcode"
    }
    pf.rename(columns=columns_map, inplace=True)
# 指定生成的Excel表格路径
    file_path = pd.ExcelWriter("G:	est1.xlsx")
# 替换空单元格
    pf.fillna("", inplace=True)
# 输出
    pf.to_excel(file_path, encoding="utf-8", index=False)
# 保存表格
    file_path.save()
export_excel(tables)

3、使用xlsxwriter写出

def export_excel(data, fileName):  # xlsxwriter库储存数据到excel
    workbook = xw.Workbook(fileName)  # 创建工作簿
    worksheet1 = workbook.add_worksheet("sheet1")  # 创建子表
    worksheet1.activate()  # 激活表
    title = ["设备名称", "框", "槽", "端口", "onuid", "认证密码", "load", "checkcode"]  # 设置表头
    worksheet1.write_row("A1", title)  # 从A1单元格开始写入表头
    i = 2  # 从第二行开始写入数据
for j in range(len(data)):
        insertData = [data[j]["设备名称"], data[j]["框"], data[j]["槽"], data[j]["端口"], data[j]["onuid"], data[j]["认证密码"],
                      data[j]["load"], data[j]["checkcode"]]
        row = "A" + str(i)
        worksheet1.write_row(row, insertData)
        i += 1
    workbook.close()  # 关闭表
 export_excel(import_excel(table), "G:	est1.xlsx")