excel表格的读取与读写
#!/usr/bin/python
# -*- coding: UTF-8 -*-
import xlwings as xw
excel3 = xw.Book(r'E:\Account registration sheet.xls')
sheet1 = excel3.sheets[0]
print(f"{sheet1.range('a1').value }")
改写
# 修改取单元值的值
sheet1.range('a1').value = ' fuck the world '
print(f"{sheet1.range('a1').value }")
结果:
fuck the world
读取批量
# 读取以 a1 为起点的一整个大方块的值,如果中间有空格,会间断的,一次只能读取一整块的值
print(f"{sheet1.range('a9').expand().value }")
# 读取以 a1 为起点的一整个大方块的值,指定范围
print(f"{sheet1.range('a9:b39').value }")
获取行数列数
# 获取行数列数
length_of_rows = sheet1.range('a9').expand().rows.count
width_of_columns = sheet1.range('a9').expand().columns.count
# 指定一单元格,up就是一直往上走,找到第一个有值的,down就是往下走
a = sheet1.range('a2000').end('up').row
print(f"%d" % a)
填入数据
setcell = sheet1.range('ac9')
setcell.value = 1
# 横向写入
setcell.value = [1, 2, 3]
# 纵向写入
setcell.value = [[1], [2], [3]]
# 横着的是1,2,3,下一行 4,5,6
setcell.value = [[1, 2, 3], [4, 5, 6], [7, 8, 9]]
# 清除setcell的内容
setcell.resize(3, 3).clear_contents()
# 在setcell上重新赋值
arr = sheet1.range('a9:b39').value
setcell.value = arr
brr = sheet1.range('a9').expand()
print("%s" % brr)
# 结果:<Range [原件-仠Account registration sheet.xls]Account registration sheet!$A$9:$B$39>
好吧,突然发现有个挺全的网站:
python读写excel利器:xlwings 从入门到精通 - chengjon - 博客园 (cnblogs.com)