Excel文件操作

 Excel文件操作

1、读取Excel文件

 

import xlrd



def read_excel():
    workbook=xlrd.open_workbook("data.xls")
    namelist=workbook.sheet_names()
    print(namelist)
    tableone=workbook.sheet_by_name(namelist[0])
    rows=tableone.nrows
    cols=tableone.ncols


    for i in range(0,rows):
        row_value=tableone.row_values(i)
        print(row_value)

    for i in range(0,cols):
        col_value=tableone.col_values(i)
        print(col_value)

    cell_value=tableone.cell_value(1,1)
    print(cell_value)


read_excel()

 

2、往Excel写数据

productlist = [{

"sku": 1,
            "name": "iphone 12 pro max",
            "price": 12999,
            "brand": "苹果"
        }, {
            "sku": 2,
            "name": "mate 40",
            "price": 7999,
            "brand": "华为"
        }, {
            "sku": 3,
            "name": "vivo r9",
            "price": 3999,
            "brand": "vivo"
        },{
            "sku": 4,
            "name": "mi",
            "price": 1999,
            "brand": "小米"
        }]
#(1)引入模块
import xlwt
# (2)工作簿
wb = xlwt. Workbook()
#(3)表
sheet = wb.add_sheet("商品列表")
# (4)写数据:一行一行的写
#表头
list = ["sku", "name", "price", "brand"]
for (i,v) in enumerate(list):
    sheet .write(0,i,v)

for (i,v) in enumerate(productlist): #进行
    for (j, k) in enumerate(list):
    # 第i+1(头)行;第j列
    #  值v (字典)中，k头就是键v[k]
        sheet.write(i + 1, j,v[k])
# (5)保存
wb. save("马冬梅1.xls")

 

3、修改Excel数据

productlist = [{

"sku": 1,
            "name": "iphone 12 pro max",
            "price": 12999,
            "brand": "苹果"
        }, {
            "sku": 2,
            "name": "mate 40",
            "price": 7999,
            "brand": "华为"
        }, {
            "sku": 3,
            "name": "vivo r9",
            "price": 3999,
            "brand": "vivo"
        },{
            "sku": 4,
            "name": "mi",
            "price": 1999,
            "brand": "小米"
        }]
import xlrd
from xlutils.copy import copy
# (1)打开工作簿
wb = xlrd.open_workbook("马冬梅1.xls")
# (2)复制一份工作簿，用来写入
copyWb = copy(wb)
# (3)通过索引获取表
sheet = copyWb.get_sheet(0)
sheet . write(4,1, "小米10")
# (4)保存,如果文件名和之前一样:覆盖;
#文件名不存在:新的文件
copyWb . save ("马冬梅1.xls")