python excel 模块 openpyxl 的基本使用

基本操作

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