读写excel

读取excel

import xlrd

file = "../data/聊天记录1.xlsx"

def read_excel():
    wb = xlrd.open_workbook(filename=file)
    sheet1 = wb.sheet_by_index(0)
    nrows = sheet1.nrows # 行数
    ncols = sheet1.ncols #列数
    print(nrows)
    print(ncols)
    # rows = sheet1.row_values(2) #获取行内容
    # cols = sheet1.col_values(3)  # 获取列内容
    # print(rows)
    # print(cols)
    print(sheet1.cell(1, 0).value)#获取表格里的内容

if __name__ == "__main__":
    read_excel()

写excel

from tempfile import TemporaryFile
from xlwt import Workbook

book = Workbook()
sheet1 = book.add_sheet('Sheet 1')

# sheet1.write(0,0,'A1')
# sheet1.write(0,1,'B1')


data = {
    "1": ["张三", 150, 120, 100],
    "2": ["李四", 90, 99, 95],
    "3": ["王五", 60, 66, 68]
}
# 字典数据

ldata = []
num = [a for a in data]
# for循环指定取出key值存入num中
num.sort()
# 字典数据取出后无需，需要先排序

for x in num:
    # for循环将data字典中的键和值分批的保存在ldata中
    t = [int(x)]
    for a in data[x]:
        t.append(a)
    ldata.append(t)

#ldata的形式就是[[1,张三,150,120,100],[2,李四,90,99,95]...]
for i, p in enumerate(ldata):
    # 将数据写入文件,i是enumerate()函数返回的序号数
    for j, q in enumerate(p):
        sheet1.write(i, j, q)

sheet1.flush_row_data()
book.save('simple2.xls')
book.save(TemporaryFile())

最后写入的格式如下图：