细节经常忘记,所以重新整理下xlrd,xlwt的使用
文件有3个sheet页,每页数据如下:
A、读取Excel数据
1、打开Excel文件
data = xlrd.open_workbook(r'E:\报名\101.xlsx')
2、获取某sheet页的,用sheet页的索引顺序(索引从0开始)或名称获取
# 通过索引顺序获取sheet页
table1 = data.sheets()[0]
table2 = data.sheet_by_index(1)
# 通过sheet页名称获取sheet页
table3 = data.sheet_by_name(u'3班')
print(table1,type(table1))
注意:获取sheet页,获取到的type类型是<class 'xlrd.sheet.Sheet'>
3、获取sheet页的数据的总行数和总列数
print(table1.nrows,table1.ncols)
4、按行获取数据:
for i in range(table1.nrows):
a = table1.row_values(i)
print(a)
5、按列获取数据:
for j in range(table1.ncols):
b = table1.col_values(j)
print(b)
6、获取单元格数据:
for m in range(table1.nrows):
for n in range(table1.ncols):
print(table1.cell_value(m, n))
或者
for m in range(table1.nrows):
for n in range(table1.ncols):
print(table1.cell(m, n).value)
注:table1.cell(m, n)获取的数据类型是<class 'xlrd.sheet.Cell'>
B、写入Excel文件
1、新建一个workbook
wb = xlwt.Workbook(encoding='utf-8')
注:encoding='utf-8',可以在excel中输出中文,不然会乱码
2、新增一个sheet页,并命名sheet
ws = wb.add_sheet('sheet名称',cell_overwrite_ok=True)
注:cell_overwrite_ok,表示是否可以覆盖单元格,默认值是False
3、写入
for x in range(5):
for y in range(5):
ws.write(x, y, "data")
注:ws.write(行号, 列号, data)
4、保存
wb.save(r'E:\Python\write.xlsx')
整合:
import xlrd,xlwt
# 打开Excel文件读取数据
data = xlrd.open_workbook(r'E:\报名\101.xlsx')
#通过索引顺序或名称获取sheet页
# 通过索引顺序获取sheet页
table1 = data.sheets()[0]
table2 = data.sheet_by_index(1)
# 通过sheet页名称获取sheet页
table3 = data.sheet_by_name(u'3班')
print(table1,type(table1))
print(table1.nrows,table1.ncols)
for i in range(table1.nrows):
a = table1.row_values(i)
print(a)
for j in range(table1.ncols):
b = table1.col_values(j)
print(b)
for m in range(table1.nrows):
for n in range(table1.ncols):
# print(table1.cell_value(m, n))
print(table1.cell(m, n).value)
value = table1.cell(2, 2)
print(value,type(value))
print(table1.cell(2, 2).value)
value = table1.cell(0, 0)
print(value,type(value))
print(table1.cell(0, 0).value)
#写Excel
wb = xlwt.Workbook(encoding='utf-8')
ws = wb.add_sheet('数据7',cell_overwrite_ok=True)
for x in range(5):
for y in range(5):
ws.write(x, y, "data")
wb.save(r'E:\Python\write.xlsx')