使用Python对Excel进行读写

太过爱你忘了你带给我的痛 2022-04-25 02:20 337阅读 0赞

Python读取Excel

import xlrd
    
    xlsx = xlrd.open_workbook('7月下旬入库表.xlsx')
    
    table = xlsx.sheet_by_index(0)
    # 通过sheet名查找：xlsx.sheet_by_name("7月下旬入库表")
    # 通过索引查找：xlsx.sheet_by_index(3)
    print(table.cell_value(0, 0))
    
    # table.cell_value(1, 2)
    # print(table.cell(1, 2).value)
    # print(table.row(1)[2].value)
    
    
    # 循环遍历有几个sheet
    for i in range(0, xlsx.nsheets):
        # 根据索引获取相应sheet
        table = xlsx.sheet_by_index(i)
        print(table.nrows,table.ncols)
        # 获取cell值
        print(table.cell_value(0, 0))
    
    # 获取所有sheet名字：xlsx.sheet_names()
    # 获取sheet数量：xlsx.nsheets
    
    for i in xlsx.sheet_names():
        table = xlsx.sheet_by_name(i)
        print(table.cell_value(3, 3))

Python写入Excel

import xlwt
    #创建工作簿
    new_workbook = xlwt.Workbook()
    #创建工作表
    worksheet = new_workbook.add_sheet('new_test')
    #定位写数据
    worksheet.write(0, 0, 'test')
    #保存
    new_workbook.save('test.xls')

Python读取Excel中的内容到另外一个Excel（方法一）

import xlrd, xlwt
    
    # 打开目标文件
    xlsx = xlrd.open_workbook("7月下旬入库表.xlsx")
    
    # 新建一个文档
    
    new_xlsx = xlwt.Workbook()
    # 添加一个工作表
    work_sheet = new_xlsx.add_sheet("new_sheet")
    
    table = xlsx.sheet_by_index(0)
    
    for i in range(0, table.nrows):
        for j in range(0, table.ncols):
            print(table.cell_value(i, j))
            work_sheet.write(i, j, table.cell_value(i, j))
    
    new_xlsx.save('new_7月下旬入库表.xlsx')

Python读取Excel中的内容到另外一个Excel（方法二）

import xlrd,xlsxwriter
    xlsx = xlrd.open_workbook('7月下旬入库表.xlsx')
    new_workbook = xlsxwriter.Workbook('test.xlsx')
    worksheet = new_workbook.add_worksheet()
    table = xlsx.sheet_by_index(0)
    for i in range(0,table.nrows):
        for j in range(0,table.ncols):
            print(table.cell_value(i, j))
            worksheet.write(i, j, table.cell_value(i, j))
    new_workbook.close()