python对excel的读写操作

导入xlrd模块读取excel

#！/usr/bin/ven python
# Author: Hawkeye
import xlrd  #专门用来读取excel的

#一个workbook就是一个sheet
workbook = xlrd.open_workbook("读.xlsx")

num = workbook.nsheets  #表单数
sheet_name = workbook.sheet_names() #sheet表名称

print("共有表单数：",num)
print("表单名称分别为：",sheet_name)

#找到要读取的sheet
sheet = workbook.sheet_by_name("Sheet1")

#从表格中受限读取表头
title_row = sheet.row(0)#表头行

print(title_row[0].value)  #取得第一行第一列 数据

print(sheet.nrows) #最大行号
print(sheet.ncols) #最大列号

lst = [] #最终存放数据的列表
for i in range(1,sheet.nrows):
    data_row = sheet.row(i) #拿到每行数据
#   print(data_row)  #可以按行打印出数据
    dic = {} # 准备一个字典，用来放每行的数据
    for j in range(sheet.ncols):
        cell = data_row[j]  #每一个单元格数据
        # print(cell.value)  #打印每个单元格数据
        #如果是时间格式要单独处理
        if cell.ctype == xlrd.XL_CELL_DATE:
            value = xlrd.xldate_as_datetime(cell.value,0).strftime("%Y-%m-%d") #时间格式
        elif cell.ctype == xlrd.XL_CELL_NUMBER:
            value = int(cell.value)
        else:
            value = cell.value
        # print(value)

        dic[title_row[j].value] = value #把数据放入字典
    lst.append(dic)
print(lst)

导入xlwt模块写入excel

#！/usr/bin/ven python
# Author: Hawkeye
import xlwt

#建立表格
workbook = xlwt.Workbook()
sheet = workbook.add_sheet("写入")

#准备写入数据
lst = [
    {"id:" : 1,"name" : "张无忌"},
    {"id:" : 2,"name" : "周芷若"},
    {"id:" : 3,"name" : "丁敏君"},
    {"id:" : 4,"name" : "灭绝师太"},
    {"id:" : 5,"name" : "范瑶"},
    {"id:" : 6,"name" : "金毛狮王"}
]

#写入表格
sheet.write(0,0,"id")
sheet.write(0,1,"name")

for i in range(len(lst)):
    dic = lst[i]
    value_lst = list(dic.values())
    for j in range(len(value_lst)):
        sheet.write(i+1,j,value_lst[j])
workbook.save("写.xlsx")