1.打开已存在文档

import openyxl

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

 

2.修改单元格数据

ws.cell(1,1).value = "test"

 

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

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

border = Border(left=Side(style='medium',color='FF000000'),right=Side(style='medium',color='FF000000'))
font = Font(name='新宋体',size=10,bold=False,italic=False,vertAlign=None,underline='none',strike=False,color='FF000000')

ws.cell(1,1).border = border
ws.cell(1,1).font = font
# 自动换行
ws.cell(1,1).alignment = Alignment(wrapText=True)

 

4.将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)