Python之Excel自动化处理（三）

一、Excel数据拆分-xlrd

1.1、代码

import xlrd
from xlutils.copy import copydef get_data():wb = xlrd.open_workbook('./base_data/data01.xlsx')sh = wb.sheet_by_index(0)'''{a: [{},{},{}],b:[{},{},{}],c:[{},{},{}],}'''all_data = {}for r in range(sh.nrows):d = {'type':sh.cell_value(r,1),'name':sh.cell_value(r,2),'count':sh.cell_value(r,3),'price':sh.cell_value(r,4)}key = sh.cell_value(r,0)if all_data.get(key):all_data[key].append(d)else:all_data[key] = [d]return all_data
def save(data):wb = xlrd.open_workbook('./base_data/data01.xlsx')wb2 = copy(wb)for key in data.keys():temp_sheet = wb2.add_sheet(key)for i, d in enumerate(data.get(key)):temp_sheet.write(i,0,d.get('type'))temp_sheet.write(i,1,d.get('name'))temp_sheet.write(i,2,d.get('count'))temp_sheet.write(i,3,d.get('price'))wb2.save('./create_data/06_表格的拆分.xlsx')if __name__ == "__main__":all_data = get_data()# save(all_data)print(all_data)

二、Excel读取数据-openpyxl

Python操作Excel的一个常用库openpyxl，openpyl是第3方模块库，可以方便的对excel执行读取与写入操作

2.1、安装

pip install openpyxl

2.2、常用方法与属性

函数名&属性	含义
openpyxl.load_workbook(path)	加载Excel文件
workbook.active	激活第1个工作薄
workbook[name]	获取根据名字工作薄
workbook.get_sheet_by_name(name)	获取根据名字工作薄
workbook.sheetnames	获取所有工作薄名字
sheet.title	获取工作薄名字
sheet.max_row	获取行数
sheet.max_column	获取列数
sheet.cell(row,col)	获取指定单元格
sheet[cell_name]	获取指定单元格,例如:C2
sheet[col/row_name]	获取整行或整列单元格:填写num为整行,row_name为整列
sheet[col:col]	获取指定范围整行单元格
sheet[cell_name:cell_name]	获取指定范围单元格
sheet.iter_rows(min_row =0 , max_row =0, min_col =0, max_col =0)	获取指定范围单元格
sheet.rows	获取所有行
sheet.columns	获取所有列
cell.value	获取单元格的值

2.3、代码

# pip install openpyxl
def open():from openpyxl import load_workbookwb = load_workbook('./base_data/data01.xlsx')# 获取工作薄sh1 = wb.activesh2 = wb['Sheet1']sh3 = wb.get_sheet_by_name('Sheet1')print(sh1 is sh2 is sh3)def show_sheets():from openpyxl import load_workbookwb = load_workbook('./base_data/data01.xlsx')print(wb.sheetnames)for sh in wb:print(sh.title)def get_one_value():from openpyxl import load_workbookwb = load_workbook('./base_data/data01.xlsx')sh1 = wb.activevalue1 = sh1.cell(2,3).valuevalue2 = sh1['c2'].valueprint(value1,value2)def get_many_value():from openpyxl import load_workbookwb = load_workbook('./base_data/data01.xlsx')sh1 = wb['Sheet1']# 切片cells1 = sh1['c2':'d3']# print(cells1)# 整行，整列cell_row3 = sh1[3]cell_col3 = sh1['c']print(cell_row3)print(cell_col3)cell_row3_5 = sh1[3:5]print(cell_row3_5)# 通过迭代获取数据# for row in sh1.iter_rows(min_row =2 , max_row =5, max_col =3):#   for cell in row:#     print(cell.value)for row in sh1.iter_rows(min_row =2 , max_row =5, min_col =2, max_col =4):for cell in row:print(cell.value)
def get_all_data():from openpyxl import load_workbookwb = load_workbook('./base_data/data01.xlsx')sh1 = wb['Sheet1']for row in sh1.rows:for cell in row:print(cell.value)for column in sh1.columns:for cell in column:print(cell.value)def get_num():from openpyxl import load_workbookwb = load_workbook('./base_data/data01.xlsx')sh1 = wb['Sheet1'] print(sh1.max_row)print(sh1.max_column)
if __name__ == "__main__":# open()# show_sheets()# get_one_value()# get_many_value()# get_all_data()get_num()

三、Excel写入数据-openpyxl

3.1、常用方法与属性

函数名&属性	含义
Workbook()	创建Excel文件
workbook.create_sheet(name,<num>)	在指定位置创建sheet表
cell.value	获取单元格值,也可直接赋值
cell.font	获取字体样式,也可直接赋值
cell.alignment	获取单元格内容位置,也可直接赋值
openpyxl.styles.Font()	创建字体样式
openpyxl.styles.Alignment()	创建单元格内容位置样式
sheet.row_dimensions[num].height	设置指定行高度
sheet.column_dimensions[col_name].width	设置指定列宽度

3.2、代码

from openpyxl import Workbook
def create_excel():# 创建excelwb = Workbook()# 激活第1个工作薄ws1 = wb.active# 创建工作薄ws2 = wb.create_sheet('数据')# 在指定位置创建工作薄、ws3 = wb.create_sheet('人员',0)# 保存数据wb.save('./create_data/09_创建excel文件.xlsx')def set_value1():# 创建excelwb = Workbook()# 激活第1个工作薄ws1 = wb.active# 写入数据ws1['A1'] = 'Hello'ws1['B2'] = 'Excel'ws1['C3'] = 'Python'# 保存数据wb.save('./create_data/10_创建excel文件_写入数据.xlsx')
def set_value2():# 创建excelwb = Workbook()# 激活第1个工作薄ws1 = wb.active# 需要写入的数据data = ['Hello','Excel','Python']for i,d in enumerate(data):ws1.cell(i+1,i+1).value = d # 注意 openpyxl里面 记数是从1开始# 保存数据wb.save('./create_data/11_创建excel文件_写入数据2.xlsx')def set_value3():# 创建excelwb = Workbook()# 激活第1个工作薄ws1 = wb.active# 需要写入的数据data = ['Hello','Excel','Python']# 把数据追加到工作薄ws1.append(data)# 保存数据wb.save('./create_data/12_创建excel文件_写入数据3.xlsx')
if __name__ == '__main__':# create_excel()# set_value1()# set_value2()set_value3()