导入xlrd模块读取excel
#!/usr/bin/ven python
# Author: Hawkeye
import xlrd #专门用来读取excel的
#一个workbook就是一个sheet
workbook = xlrd.open_workbook("读.xlsx")
#找到要读取的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")
标签:sheet,name,读写操作,python,excel,value,cell,lst,row
来源: https://www.cnblogs.com/sniepr/p/12486971.html