基本操作
import os,openpyxl
from openpyxl.utils import get_column_letter, column_index_from_string
wb=openpyxl.load_workbook(os.path.join('E:\\','path','abc.xlsx'))
print(wb) #
# sheet_list=wb.get_sheet_names() # 不推荐写法
# print(sheet_list)
sheet_names=wb.sheetnames
print('Sheet列表:',end=' ')
print(sheet_names)
# sheet=wb.get_sheet_by_name('Sheet1') # 不推荐写法
# print(sheet)
sheet=wb['Sheet1']
print(sheet)
title=sheet.title
print('Sheet1标题:'+title)
# active_sheet=wb.get_active_sheet() # 不推荐写法
# print(active_sheet)
active_sheet=wb.active
print('当前活动Sheet:',end=' ')
print(active_sheet)
# 获取单元格数据
cell=active_sheet['A1'] # 使用字母序列获取
print(cell)
val=cell.value
print(val)
print(active_sheet.cell(row=1,column=2).value) # 使用数字获取 第一行第二列 'B1'
for i in range(1,4):
print(active_sheet.cell(row=1,column=i).value,end=' ')
print()
rows=active_sheet.rows
cols=active_sheet.columns
print('行数:%s 列数:%s'%(len(list(rows)),len(list(cols))))
# 工具方法:数字与字母标记的转换
print(get_column_letter(1)+get_column_letter(2)+get_column_letter(3)+get_column_letter(10))
print(column_index_from_string('A'))
print(wb.sheetnames)
wb.remove(wb['mysheet'])
wb.save(os.path.join('E:\\','path','abc.xlsx'))
sheet 的读取和创建
import os,openpyxl
# 加载已存在的excel
wb=openpyxl.load_workbook(os.path.join('E:\\','path','abc.xlsx'))
# 读取活动页所有数据
# sheet=wb.active
# for row in sheet.iter_rows():
# for cell in row:
# print(cell.value,end=' ')
# print()
# 创建Sheet及数据
# csheet=wb.create_sheet('mysheet')
# >>> ws1 = wb.create_sheet("Mysheet") # insert at the end (default)
# or
# >>> ws2 = wb.create_sheet("Mysheet", 0) # insert at first position
# csheet.sheet_properties.tabColor='1072BA'
# for i in range(1,101):
# for j in range(1,101):
# csheet.cell(row=i,column=j,value=i*j)
# wb.save(os.path.join('E:\\','path','abc.xlsx'))
# 创建excel 数据为9*9乘法表
# wb=openpyxl.Workbook()
# sheet=wb.active
# for i in range(1,10): # row从1开始
# for j in range(1,10): #col从1开始
# sheet.cell(row=i,column=j,value=i*j)
# wb.save(os.path.join('E:\\','path','new.xlsx'))
# 读取范围数据
sheet=wb.active
cell_range=sheet['A1':'B2'] # 得到行列组成的嵌套元组
for row in cell_range:
for column in row:
print(column.value)
if 'd' in str(column.value): # 修改原来数据
column.value='的'
wb.save(os.path.join('E:\\','path','abc.xlsx'))
练习:插入空行
import os,openpyxl
# 插入空行 从row_begin起始处,插入row_num行空行
def insert_blank_row(row_begin,row_num):
wb=openpyxl.Workbook()
sheet=wb.active;
blank_index=[]
for i in range(0,row_num):
blank_index.append(row_begin+i)
for row in range(1,21):
for col in range(1,11):
if row not in blank_index:
sheet.cell(row,col,row*col)
wb.save(os.path.join('E:\\','path','abc.xlsx'))
insert_blank_row(5,6)
练习:根据 excel 统计人员信息
import os,openpyxl,pprint
wb=openpyxl.load_workbook(os.path.join('E:\\','path','people.xlsx'))
sheet=wb.active
result={}
# for row in sheet.iter_rows():
# result.setdefault(row[1].value,{}) # 省份设为字典
# result[row[1].value].setdefault(row[2].value,0) # 市级人数统计,默认0
# result[row[1].value][row[2].value]+=1
# print(result)
# {'湖北省': {'武汉': 1, '鄂州': 1}, '浙江省': {'杭州': 2, '温州': 1}}
for row in sheet.iter_rows():
result.setdefault(row[1].value,{})
result[row[1].value].setdefault(row[2].value,[])
result[row[1].value][row[2].value].append(row[0].value)
print(result)
file=open(os.path.join('E:\\','path','people.py'),'w')
file.write('peos='+pprint.pformat(result))
file.close()
# {'湖北省': {'武汉': ['小米'], '鄂州': ['小李']}, '浙江省': {'杭州': ['小王', '小张'], '温州': ['小古']}}
练习:将excel纵向内容变为横向内容
import os,openpyxl,copy
# 将excel纵向内容变为横向内容
def col_to_row():
wb=openpyxl.load_workbook(os.path.join('E:\\','path','abc.xlsx')) # load_workbook是加载已有的数据并修改(原数据还在)
# wb=openpyxl.Workbook()
sheet=wb.active
# 构成list[[],[]]
# 如果不用list直接使用cell.value赋值和清除值会发现cell一直是引用
rows=[]
for col in sheet.iter_cols():
cols=[]
for cell in col:
cols.append(cell.value) # 保存值
cell.value='' # cell是对单元格的引用,可直接清除数据
rows.append(cols)
row_index=0
col_index=0
for row in rows:
row_index+=1
col_index=0
for val in row:
col_index+=1
sheet.cell(row_index,col_index,val)
wb.save(os.path.join('E:\\','path','abc.xlsx'))
col_to_row()
练习:读取 txt 文件中的内容并存入到 excel
import os,openpyxl,re
# 读取abc.txt文件中的内容并存入到excel
file = open(os.path.join('E:\\', 'path', 'abc.txt'))
text_list=file.readlines() # ['a b c d\n', '1 2 3 4\n']
if text_list:
wb = openpyxl.Workbook()
sheet=wb.active
row=0
col=0
for text in text_list:
row+=1
col=0
t_list=text.split(' ')
for t in t_list:
col+=1
sheet.cell(row,col,t)
wb.save(os.path.join('E:\\', 'path', 'abc.xlsx'))
# 去掉换行符
# def re_blank(text):
# regex=re.compile(r'\s*')
# return regex.sub('',text)
#
# # 读取abc.xlsx的内容存入到abc.txt中
# wb=openpyxl.load_workbook(os.path.join('E:\\','path','abc.xlsx'))
# sheet=wb.active
# file=open(os.path.join('E:\\','path','abc.txt'),'w')
# for row in sheet.iter_rows():
# text_list=[]
# for cell in row:
# text_list.append(re_blank(cell.value))
# file.write(' '.join(text_list)+'\n')
# file.close()