python Excel处理

Python

前言：

我是一个菜鸡，写文章是为了记录自己成长的过程，如有错误，请各位大佬轻喷，欢迎大家指正。基础知识不做赘述，下文介绍Excel办公自动化。

openpyxl导入

安装步骤 win+R 输入cmd
然后打出pip install openpyxl
测试方式 命令行输入python 如果没有报错 则安装成功

Python打开Excel文件_获取指定单元格内容

打开工作簿
load_workbook(文件名)
可以获取指定的工作表
workbook[sheet名称]
只有一个工作表的时候去使用
workbook.active
获取列表尺寸
sheet.dimensions
获取行
sheet.rows
获取列
sheet.columns
获取表格内的值
.value
.iter_rows(min_row = 最低行数 ， max_row = 最高行数 ， min_col = 最低行数 ， max_col = 最高列数)

# 首先导入模块
import openpyxl
# 打开一个Excel表格
workbook = openpyxl.load_workbook('C:\\Users\\Dell\\Desktop\\demo01.xlsx')
# 查看excel表格中sheet的名字
print(workbook.sheetnames)
sheet = workbook.active
cell = sheet['A5']
print(cell.value)
# 获取一系列单元格
cells = sheet['A':'E']
for cell in cells:
    print(cell[2].value)

rows = sheet.iter_rows(min_row=1,max_row=5,min_col=1,max_col=4)
for row in rows:
    for cell in row:
        print(cell.value)

查询值所在位置，以及统计出现次数

# 首先导入模块
import openpyxl

# 打开一个Excel表格
workbook = openpyxl.load_workbook('C:\\Users\\Dell\\Desktop\\demo01.xlsx')
sheet = workbook.active
cols = sheet['I']
count = 0
for col in cols:
    if col.value == '商学院':  # 对比值是否相等
        count += 1
        print('I' + str(col.row))
        print('出现次数为' + count)

Python在Excel中修改数据

需要注意的是，每次修改时都需要关闭表格，否则报错
向某个格子写入内容
sheet[‘A1’] = ‘hello world’
用某个格子写入内容
cell = sheet[?]
cell.value = ‘hello,python’
使用python列表数据插入一行
sheet.append(python列表)
插入公式
直接输入公式内容
插入一列
.insert_cols(idx = 数字编号）
插入多列
.insert_cols（idx = 数字编号，ammount = 要插入的列数）
插入一行
.insert_rows(idx = 数字编号）
插入多行
.insert_rows(idx = 数字编号，amount = 要插入的行数）
删除列
.delete_cols(idx = 数字编号，amount = 要删除的列数）
删除除行
.delete_rows(idx = 数字编号，amount = 要删除的行数)
移动格子
.move_range('A3:B8,rows = 2,cols = -2)
正整数为向下或向右，负整数为向左或者向上
创建新的sheet
workbook.create_sheet(sheet名称）
删除一个sheet
workbook.remove(sheet实例）
复制一个sheet
workbook.copy_worksheet(sheet实例)
修改表格名称
sheet.title
冻结窗格 即保持某一行始终处在第一列
sheet.freeze_panes = ‘D2’
添加筛选
sheet.auto_filter.ref = sheet.dimensions

import openpyxl
workbook = openpyxl.Workbook() #创建一个新的工作簿，Excel文件
# 创建sheet工作表
sheet = workbook.create_sheet()
# 添加数据方法1
sheet['A1'] = 'hello，Python'
# 添加数据方法2
cell = sheet['A2']
cell.value = "Java"
# 保存数据
workbook.save('excel01.xlsx')

插入数据

import openpyxl
workbook = openpyxl.load_workbook('excel01.xlsx') # 打开新表
# 添加单行
sheet = workbook['Sheet1']
lst = ['姓名', '分数']
sheet.append(lst)
# 添加多行
stu_lst = [
    ['张三',89],
    ['李四',87],
    ['王五',93],
    ['陈六',85]
]
for row in stu_lst:
    sheet.append(row)
# 保存数据
workbook.save('excel01.xlsx')

插入公式

import openpyxl
workbook = openpyxl.load_workbook('excel01.xlsx') # 打开新表
# 直接输入公式
sheet = workbook['Sheet1']
sheet['B9'] = '=sum(B5:B8)'
# 保存数据
workbook.save('excel01.xlsx')

查询excel公式

from openpyxl.utils import FORMULAE
print(FORMULAE)

插入多列

import openpyxl
workbook = openpyxl.load_workbook('excel01.xlsx') # 打开新表
# 直接输入公式
sheet = workbook['Sheet1']
sheet.insert_rows(idx=3,amount=5)
# 保存数据
workbook.save('excel01.xlsx')

sheet相关操作

import openpyxl
workbook = openpyxl.load_workbook('excel01.xlsx') # 打开新表
# 创建sheet
workbook.create_sheet('mysheet')
# 删除sheet
sheet = workbook['mysheet']
workbook.remove(sheet)
# 复制工作表对象
sheet = workbook['mysheet']
workbook.copy_worksheet(sheet)
# 重命名
sheet.title = 'mysheet2'
print(workbook.sheetnames)
# 保存数据
workbook.save('excel01.xlsx')

查找保存数据

import openpyxl
workbook = openpyxl.load_workbook('C:\\Users\\Dell\\Desktop\\demo01.xlsx') # 打开新表
sheet = workbook['Sheet1']
rows = sheet.rows
lst = []
for row in rows:
    if row[8].value == '商学院':
        sub_lst = []
        for i in range(0,4):
            sub_lst.append(row[i].value)
        lst.append(sub_lst)
print(lst)
#存储到一个新的Excel文件中
new_workbook = openpyxl.Workbook()
new_sheet = new_workbook.active
for row in lst:
    new_sheet.append(row)

new_workbook.save('心协商学院花名册.xlsx')

操作Excel表格样式

修改字体样式
Font(name = 字体名字，size = 字体大小，bold = 是否加粗，italic = 是否斜体， color = 字体颜色）
获取表格中字体的样式
cell.font.属性
设置对齐样式
Alignment(horizontal = 水平对齐方式，vertical = 垂直对齐方式，text_rotaition = 旋转角度，wrap_text = 是否自动换行）

import openpyxl
from openpyxl.styles import Font, Alignment, Side, Border

workbook = openpyxl.load_workbook('C:\\Users\\Dell\\Desktop\\demo01.xlsx')  # 打开新表
sheet = workbook['Sheet1']
cell = sheet['C5']
# 修改单元格样式
font = Font(name='微软雅黑', size=20, bold=True, italic=True, color='ff0000')
cell.font = font
# 获取单元格样式
font = cell.font
print(font.name, font.size, font.bold, font.color)
# 设置对齐格式
algin = Alignment(horizontal='center', vertical='center')
cell.alignment = algin
# 设置线的样式
side = Side(style='thin', color='ff0000')
border = Border(left=side, top=side, right=side, bottom=side)
cell.border = border
workbook.save('C:\\Users\\Dell\\Desktop\\demo01.xlsx')