python和Excel
基本工作表工作薄读取操作
# 相对路径
wb1 = xlrd.open_workbook('2-4.xls')
#绝对路径
wb2 = xlrd.open_workbook(r'F:\Pycharmproject\当Excel遇到Python教程素材\PyObject\第2章\2-4\2-4.xls')
#读取所有工作表对象
All_ws1 = wb1.sheets()
#获取所有工作表名称
print(wb1.sheet_names())
#根据工作表名称索引来获取工作表
print(wb1.sheet_names()[0])
print(wb1.sheet_names()[1])
#根据工作表名称索引来获取工作表
print(wb1.sheet_by_index(1))
#根据工作表名称来获取工作表
print(wb1.sheet_by_name('雪豹队'))
读取行列单元格的值
import xlrd
wb = xlrd.open_workbook('2-5.xlsx')
ws = wb.sheet_by_name('飞龙队')
row_conut = ws.nrows# 返回已使用的行数
col_count = ws.ncols #返回已使用的列数
row_obj = ws.row(1)#行数从0开始
row_val = ws.row_values(1) #指定行的数值
col_obj = ws.col(0) #第一列
col_val = ws.col_values(0) #第一列的值
cell_obj = ws.cell(3,1)#第4行第一列的单元格的值
cell_val = ws.cell_value(3,1)
print(row_obj)
print(row_val)
print(col_obj)
print(col_val)
print(cell_obj)
print(cell_val)
[text:'小明', number:98.0]
['小明', 98.0]
[text:'姓名', text:'小明', text:'小超', text:'大张', text:'小杰']
['姓名', '小明', '小超', '大张', '小杰']
number:99.0
99.0
xlwt修改工作薄，工作表，仅能新建
import xlwt
nwb = xlwt.Workbook('utf-8') # 新建工作表
nws = nwb.add_sheet('工资表') # 新建工作表
nws.write(0, 0, '张三：9000元')
nwb.save('2-7wwh.xls') # 保存仅支持xls
修改工作簿、表、单元格
import xlrd
import xlwt
from xlutils.copy import copy
#直接使用import xlutils,然后xlutils.copy会报错，解决https://www.crifan.com/python_xlutils_copy_attributeerror_str_object_has_no_attribute_datemode/
wb = xlrd.open_workbook('2-9.xls')
nwb = copy(wb) # 复制工作薄
ws1 = nwb.get_sheet(0) #使用索引读取工作表
ws2 =nwb.get_sheet('工资表') #使用工作表名读取工作表
ws3 = nwb.add_sheet('汇总表') # 新建工作表
ws3.write(0,0,'总计')
ws3.write(0,1,1200)
nwb.save('2-9-1wwh.xls')
for循环批量生成工作薄
import xlwt
for month in range(1,13):
month_name = '{}月份.xls'.format(month) # 格式化输出
print(month_name)
# month_name = 's%月.xls' % (month) #字符串的格式化
nwb = xlwt.Workbook(encoding='utf-8')
nws = nwb.add_sheet(str(month)) #需要将month转化为字符串
nwb.save('2022年\\' + month_name) #文件路径+文件名
#或者
import xlwt
for month in range(1,13):
#month_name = '{}月份.xls'.format(month) # 格式化输出
month_name = '%d月.xls' % (month) #字符串的格式化
print(month_name)
nwb = xlwt.Workbook(encoding='utf-8')
nws = nwb.add_sheet(str(month))
nwb.save('2022年/' + month_name)
for x in range(1,10):
for y in range(1,10):
txt = '%d × %d = %d' % (x,y,x*y)
print(txt, end='\t')#\t代表制表符，横行
print()#默认换行
#先循环里面，在循环外面，外面=1，里面1-9，外面=2，里面1-9
写入Excel
import xlwt
nwb = xlwt.Workbook(encoding='utf-8')
nws = nwb.add_sheet('乘法表')
for y in range(1, 10):
for x in range(1, 10):
txt = '%d × %d = %d ' % (y, x, y * x) #格式化
nws.write(y - 1, x - 1, txt) #行列都是从0开始索引的
nwb.save('wwh_3_5.xls')
#这才是真正的乘法表
import xlwt
nwb = xlwt.Workbook(encoding='utf-8')
nws = nwb.add_sheet('乘法表')
for y in range(1, 10):
for x in range(1, y+1):
#for x in range(1, 10):
txt = '%d × %d = %d ' % (x, y, y * x)
nws.write(y-1, x-1, txt)
nwb.save('wwh_3_5.xls')
| 1 × 1 = 1 | ||||||||
|---|---|---|---|---|---|---|---|---|
| 1 × 2 = 2 | 2 × 2 = 4 | |||||||
| 1 × 3 = 3 | 2 × 3 = 6 | 3 × 3 = 9 | ||||||
| 1 × 4 = 4 | 2 × 4 = 8 | 3 × 4 = 12 | 4 × 4 = 16 | |||||
| 1 × 5 = 5 | 2 × 5 = 10 | 3 × 5 = 15 | 4 × 5 = 20 | 5 × 5 = 25 | ||||
| 1 × 6 = 6 | 2 × 6 = 12 | 3 × 6 = 18 | 4 × 6 = 24 | 5 × 6 = 30 | 6 × 6 = 36 | |||
| 1 × 7 = 7 | 2 × 7 = 14 | 3 × 7 = 21 | 4 × 7 = 28 | 5 × 7 = 35 | 6 × 7 = 42 | 7 × 7 = 49 | ||
| 1 × 8 = 8 | 2 × 8 = 16 | 3 × 8 = 24 | 4 × 8 = 32 | 5 × 8 = 40 | 6 × 8 = 48 | 7 × 8 = 56 | 8 × 8 = 64 | |
| 1 × 9 = 9 | 2 × 9 = 18 | 3 × 9 = 27 | 4 × 9 = 36 | 5 × 9 = 45 | 6 × 9 = 54 | 7 × 9 = 63 | 8 × 9 = 72 | 9 × 9 = 81 |
批量新建工作表
import xlwt
year = 2010
wb = xlwt.Workbook('utf-8')
while year<2020:
sheetnames = '%d年业绩' % (year) #格式化的主要 原因是，工作表名需要是str
wbs = wb.add_sheet(sheetnames)
year = year + 1 #这个条件写到这里的最主要原因是，前面的year需要从2010开始
wb.save('wwh3-8.xls')
批量新建工作表工作薄
#与九九乘法表一样的道理和逻辑
import xlwt
year,month = 2015,1
while year <2020:
wb_name = '{}年.xls'.format(year) #格式化Excel名称
year = year + 1
wb = xlwt.Workbook('utf-8')
while month <13:
sheetname = '{}月'.format(month)
ws = wb.add_sheet(sheetname)
month = month + 1#month在上面执行完成以后再+1，主要是因为month是从1开始的。
month = 1 #month循环完成之后，month=12，想要继续内循环就必须让month=1，重新开始
wb.save('nian\\'+ wb_name) #内循环执行完成，上方执行完成，需要保存文件
#与九九乘法表一样的道理和逻辑
import xlwt
year = 2015
while year <2020:
wb_name = '{}年.xls'.format(year) #格式化Excel名称
year = year + 1
wb = xlwt.Workbook('utf-8')
month = 1 #初始化month
while month <13:
sheetname = '{}月'.format(month)
ws = wb.add_sheet(sheetname)
month = month + 1#month在上面执行完成以后再+1，主要是因为month是从1开始的。
wb.save('nian\\'+ wb_name) #内循环执行完成，上方执行完成，需要保存文件
if判断
import xlrd
wb = xlrd.open_workbook('3-12.xls')
ws = wb.sheet_by_name('成绩表')
col_vals = ws.col_values(1)
#我本来写的是for score in range(col_vals[1],col_vals[5]),但是这样行不通，最主要是因为range()参数必须为整型int，
#另外，我这种写法，如果数据量大，就没办法用了。
for score in col_vals:
if type(score) == float and score >= 90: #判断数据类型和分数
print(score,'优秀')
#print(col_vals)
if多条件分类
import xlrd
from xlutils.copy import copy
wb = xlrd.open_workbook('3-16.xls')
ws = wb.sheet_by_name('分数表')
col_vals = ws.col_values(1) # list
nwb = copy(wb) #新创建一个工作薄
nws = nwb.get_sheet('分数表')#新创建一个sheet
row_num = 1
for num in col_vals:
if type(num)== float:
if num >=90:
print(num,'优')
nws.write(row_num,2,'优')
elif num >=80:
print(num,'良')
nws.write(row_num, 2,'良')
elif num >=60:
print(num,'中')
nws.write(row_num, 2,'中')
else:
print(num,'不合格')
nws.write(row_num, 2,'不合格')
row_num+=1 # 不放到if里面，放到外面的话，就得从0开始，而且如果中间还有字符串，就会导致串行，
nwb.save('3-16-2.xls')
break语句（案例-标记达标时的首个月份）
import xlrd
from xlutils.copy import copy
wb = xlrd.open_workbook('3-18.xls')
ws = wb.sheet_by_name('业绩表')
# print(ws.nrows)#看看有多少行
nwb = copy(wb)
nws = nwb.get_sheet('业绩表')
num = 0
for row_num in range(1, ws.nrows): # 从第一行到最后一行
num =0 #某行的和大于100以后，要将求和归零，从零重新开始
# print(row_num)
for col_num in range(1, ws.ncols): # 从第一列到最后一列
# print(row_num,col_num)
# print(ws.cell_value(row_num,col_num))#获取单元格的值
num += ws.cell_value(row_num, col_num)
if num >= 100:
nws.write(row_num, ws.ncols - 1, ws.cell_value(0, col_num))
break
#print('---------')
nwb.save('wwh3-18.xls')
continue的用法
num=0
while num<6:
num +=1
if num%2==1:
continue #跳过，不执行后续代码
print(num)
for num in [90,85,99,78,100]:
if num<90:
continue
txt='{}优秀'.format(num)
print(txt)
#累计值大于100，重新累计
num =0
c = 0
for i in [41,5,41,78,51,68,34,64,49,21]:
num = num + i
if num< 100:
continue
c+=1
print(num,'第{}次累积达标业绩为：{}'.format(c,num))#打印累计值
num=0#重新累计
字符串操作
切片--判断身份证号
#第一种方法
import xlrd
import xlwt
wb = xlrd.open_workbook('4-2.xls')
ws = wb.sheet_by_name('员工信息表')
nwb = xlwt.Workbook('utf-8')
nws = nwb.add_sheet('员工信息表')
# print(ws.nrows)
row_num = 0
nws.write(0, 0, '姓名')
nws.write(0, 1, '身份证号')
nws.write(0, 2, '性别')
while row_num < ws.nrows - 1:
row_num += 1
card = ws.cell_value(row_num, 1)
sex_num = int(card[14:17][-1])
sex = '男' if sex_num % 2 == 1 else '女'
name = ws.cell_value(row_num, 0)
nws.write(row_num, 0, name)
nws.write(row_num, 1, card)
nws.write(row_num, 2, sex)
nwb.save('wwh4-2.xls')
#第二种方法
import xlrd
from xlutils.copy import copy
wb = xlrd.open_workbook('4-2.xls')
ws = wb.sheet_by_name('员工信息表')
nwb = copy(wb)
nws = nwb.get_sheet('员工信息表')
nws.write(0,2,'性别')
for row_num in range(1,ws.nrows):
card = ws.cell_value(row_num,1)#获取身份证号
sex_num = int(card[14:17][-1])#获取代表性别的数字，card是str类型，要做转换
if sex_num%2==1:#判断性别
sex = '男'
else:
sex = '女'
nws.write(row_num,2,sex)
nwb.save('4-2kaobei.xls')
本文来自博客园，作者：我的前进日志，转载请注明原文链接：https://www.cnblogs.com/sunshinewang/p/16301068.html
