【python学习笔记】openpyxl操作excel高阶操作

import openpyxl
import os
from openpyxl.styles import Font, PatternFill
from openpyxl import chart

path = os.path.join(os.getcwd(), '项目1', '问题单总体汇总.xlsx')
wb = openpyxl.load_workbook(path, data_only=True)  # 增加data_only=True参数，读取单元格的内容不会显示为公式
ws = wb.active
'插入公式'
ws['B82'] = '=sum(B2:B81)'
print(ws['B82'].value)  # 公式计算成功后要保存一次excel文件，要不然读取的为None,保存一次重新运行获取正常24548

'''设置行高列宽'''
ws.row_dimensions[1].height = 30  # 行高1个数值表示1/72英寸，大约0.35mm  注意行为数字而非字符串
ws.column_dimensions['A'].width = 70 # 列宽1个数值表示一个字符大小

'''合并和拆分单元格'''
ws.merge_cells('C2:D2')
ws['C2'] = '合并'
ws.unmerge_cells('C2:D2')

'''冻结窗口:冻结对应单元格上一行和左边一列'''
ws.freeze_panes = 'A2'  # 冻结首行 两条命令后覆盖
ws.freeze_panes = 'B1'  # 冻结首列
ws.freeze_panes = 'B2'  # 冻结首行首列 注意没有解冻

'''创建图表 柱状图'''
'''创建图表的步骤：
1、读取数据
2、创建如表对象
3、将数据添加到图表
4、将图表添加到对应的表里面'''
maxCol = openpyxl.utils.column_index_from_string('J')
minCol = openpyxl.utils.column_index_from_string('D')
values = openpyxl.chart.Reference(ws, min_row=4, min_col=5, max_col=maxCol, max_row=18) # 引用工作表的单元范围，用作图表添加数据
labels = openpyxl.chart.Reference(ws, min_row=5, min_col=minCol, max_row=18) # 引用工作表的单元范围，用作X轴标签使用
# chart = openpyxl.chart.BarChart3D() # 柱状3D图
chart = openpyxl.chart.BarChart() # 柱状图
chart.title = '异常问题单分布情况'
# chart.y_axis.title = '异常问题数量'  # 设置y轴标题
# chart.x_axis.title = '组件' # 设置x轴标题
# chart.legend = None  #取消图表图例
chart.add_data(values, titles_from_data=True) # 图表添加一个范围数据，每个列视为一个数据系列
chart.set_categories(labels) # 图表设置类别，X轴标签
# chart.style = 13 # 图标样式 绿色
chart.grouping = "stacked"  # 堆积
# chart.shape = 4
# chart.grouping = "percentStacked"
# chart.type = "col"  # 定义垂直条形图 默认值
# chart.type = "bar"  # 定义水平条形图
chart.overlap = 100  # 层叠图时需要设置重叠为100 来定义使用堆叠图表
ws.add_chart(chart, 'D21') # 在表中的D21位置插入图表
wb.save(path)   # 生成excel文件

"插入和删除行列"
wb = openpyxl.load_workbook('test.xlsx')
ws = wb.active
ws1 = wb.get_sheet_by_name("Sheet1")
"excel写入内容"
rows = [
    ('Number', 'Batch 1', 'Batch 2'),
    (2, 10, 30),
    (3, 40, 60),
    (4, 50, 70),
    (5, 20, 10),
    (6, 10, 40),
    (7, 50, 30),
]
for row in rows:
    print(row)
    ws.append(row)
"在表格中插入空行、空列"
ws.insert_rows(3, 2)# 插入的位置、插入的数量:第三行插入两行
# ws.insert_cols(4) # 插入的位置、插入的数量:第四列插入一列
ws.insert_cols(2, 10) # 插入的位置、插入的数量:第2行插入10列

"在表格中删除空行、空列"
ws.delete_cols(2, 10)
ws.delete_rows(3, 2)
col = openpyxl.utils.column_index_from_string('D')  # 将字母转换为数字 100
print(col)
ws.delete_cols(col, 10)

"移动单元格位置"
ws.move_range('A2:B2', rows=-1, cols=1) # 移动单元格范围、rows参数大于0，向下移动，小于0向上移动，cols大于0向右移动，小于0向左移动；

"保存修改"
wb.save('test.xlsx')

　

"openpyxl提供numpy和pandas交互的接口，pandas基于numpy的基础，主要使用数据结构是一维数据与二维数据DataFrame"
'''
import pandas as pd
from openpyxl.utils.dataframe import dataframe_to_rows
import numpy as np
'''
dates = pd.date_range("20211122", periods=6)
'''
np.random.randn(6, 4): dn表格每个维度，返回值为指定维度的array 6行4列
'''
df = pd.DataFrame(np.random.randn(6, 4), index=dates, columns=list('ABCD')) # 行标和列标
print(df)
'''
                   A         B         C         D
2021-11-22 -1.159974 -0.082023 -0.486664 -1.815281
2021-11-23 -0.517931 -1.503639 -0.220602 -0.352718
2021-11-24 -0.150872  0.662365 -0.431215 -0.343600
2021-11-25 -1.298853 -1.774166  0.294895  0.426494
2021-11-26 -3.734309  2.097675 -0.779000 -0.174263
2021-11-27 -1.508636 -0.898556 -1.164526 -1.378445

'''
for r in dataframe_to_rows(df, index=True, header=True):
    ws1.append(r)  # 写入到excel表格中
wb.save('test.xlsx')
df = pd.DataFrame(ws1.values)
print(df)
'''
           0         1         2         3         4
0        NaT         A         B         C         D
1        NaT      None      None      None      None
2 2021-11-22 -1.159974 -0.082023 -0.486664 -1.815281
3 2021-11-23 -0.517931 -1.503639 -0.220602 -0.352718
4 2021-11-24 -0.150872  0.662365 -0.431215   -0.3436
5 2021-11-25 -1.298853 -1.774166  0.294895  0.426494
6 2021-11-26 -3.734309  2.097675    -0.779 -0.174263
7 2021-11-27 -1.508636 -0.898556 -1.164526 -1.378445
'''

"单元格样式"
from openpyxl.styles import Border, Side, PatternFill

filepath = os.path.join(os.getcwd(), '项目3', 'LLT-10月.xlsx')
wb = openpyxl.load_workbook(filepath)
ws = wb.get_sheet_by_name('1002')

# 设置边框样式
th = Side(style='thin', color='000000') #创建一个Side对象，其中框类型为细，颜色RGB为000000  细黑线
# db = Side(style='double', color='ff0000') # 双层红线
db = Side(style='thick', color='000000') # 粗线黑色

'''单元格边框设置'''
# ws['M3'].border = Border(top=db)
# ws['M5'].border = Border(top=db, left=th, right=th, bottom=db)

'''批量边框填充'''
line_num = 1
"获取表的大小"
max_row = ws.max_row
max_column = ws.max_column
print(max_row, max_column)
a = openpyxl.utils.get_column_letter(max_column)  # 将数字转换为字母  CV
end_num = a + str(max_row)
print(end_num)

for line in ws['A1':end_num]:
    for cell in line:
        if line_num == 1:
            cell.fill = PatternFill('solid',fgColor='00CDCD')
            cell.border = Border(left=th, right=th, top=th, bottom=db)
        else:
            cell.border = Border(left=th, top=th, right=th, bottom=th)
    line_num += 1

wb.save(filepath)