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')