openpyxl 操作excel文档

1.打开已存在文档

import openyxl

wb = openpyxl.load_workbook(file_path)
ws = wb[sheet_name]

 

2.修改单元格数据

# 单元格文本
ws.cell(1,1).value = "test"
# 插入公式
ws.cell(2,1).value = "=SUM(B2:B10)"

 

3.设置单元格样式(边框，字体，格式)

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

side = Side(style='thin',color='000000')
border = Border(bottom=border_side,top=border_side,left=border_side,right=border_side)
font = Font(name='新宋体',size=10,bold=False,italic=False,vertAlign=None,underline='none',strike=False,color='000000')
alignment = Alignment(horizontal='center',vertical='center',wrapText=True) # 水平居中 垂直居中 自动换行

ws.cell(1,1).border = border
ws.cell(1,1).font = font
ws.cell(1,1).alignment = alignment

 

4.设置行列

# 在第2行之前插入一行
ws.insert_rows(2) 
# 删除第4行开始的1行
ws.delete_rows(4,1)

# 第2行设置为隐藏1/True，取消隐藏设置为0/False
ws.row_dimensions[2].hidden=1
# 第2行设置行高
ws.row_dimensions[2].height=18
# 第2列设置列宽
ws.row_dimensions['B'].width=50

 

5.将dataframe数据插入excel表中

from openpyxl.utils.dataframe import dataframe_to_rows
import pandas as pd

df = pd.DataFrame([[1,2,3],[1,2,3]],columns=['a','b'])
for row in dataframe_to_rows(df, index=False, header=False):
    ws.append(row)

 

5.在excel中插入曲线图

from openpyxl.chart import (
    LineChart,
    Reference,
)
from openpyxl.chart.axis import DateAxis

line_chart = LineChart()
line_chart.title = "char_title"
line_chart.style = 13
# x y轴的名称
line_chart.y_axis.title = 'Y label'
line_chart.x_axis.title = 'X label'
# 图表所依据的数据位置
data = Reference(ws, min_col=2, min_row=1, max_col=2, max_row=10)
line_chart.add_data(data, titles_from_data=True)
# x轴坐标标签所依据的数据位置
x_labels = Reference(ws, min_col=1, min_row=2, max_row=10)
line_chart.set_categories(x_labels)
line_chart.width = 19
line_chart.height = 7
# 设置线条样式
line1 = line_chart.series[0]
line1.smooth = True # 线条光滑
line1.marker.symbol = "triangle"
line1.graphicalProperties.line.width = 20000 #线条粗细
# 插入图表的左上角位置
ws.add_chart(line_chart, "G2")