Python使用openpyxl画表格

初始化工作区间

from openpyxl import Workbook

# new一个新的wb工作区间
wb = Workbook()

# 激活，默认第一个sheet
ws1 = wb.active
# 其他的sheet，使用如下方式创建
# ws2 = wb.create_sheet(title="Profiling纬度-自有标签")

# 设置sheet名称
ws1.title = "Sheet-Name"

数据追加

# 按照顺序追加新的数据到工作sheet区间
ws2.append(["会员沟通相应", "", "绝对值"])
ws2.append(["生日礼", "参与/领取率", ""])
ws2.append(["", "领取购买率", ""])
ws2.append(["NCC", "参与/领取率", ""])
ws2.append(["", "领取购买率", ""])

样式

from openpyxl.styles import Alignment, Border, Side 


# 列宽
ws2.column_dimensions["A"].width = 26

# 合并单元格【从A1到H2合并为一个单元格】
ws2.merge_cells("A1:H2")  # 左上角到右下角的坐标

# 文字显示方式，居中显示，以合并之后左上角的位置
ws2["A1"].alignment = Alignment(horizontal='left', vertical='bottom')  

# 字体样式以及颜色
ws2["A1"].font = Font(u'微软雅黑', bold=True, italic=False, strike=False, color="ff0000") 

# 单元格边框
side = Side(border_style='thin', color='FF000000')
ws2["A1"].border = Border(top=side, right=side, bottom=side, left=side)

文件保存

wb.save(filename='xxx.xlsx')

数字格式化

from openpyxl import Workbook

filename = 'number_format.xlsx'
wb = Workbook()
ws = wb.active
ws.column_dimensions['A'].width = 35
ws['A1'] = 246416416
ws['A1'].number_format = '#,##0'
ws['A2'] = 54564564.41521564
ws['A2'].number_format = '"￥"#,##0.00'

ws['A3'] = 0.565464
ws['A3'].number_format = '0.0%'

wb.save(filename)

excel网格线

ws.views.sheetView[0].showGridLines = False  # 设置不显示网格线