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