openpyxl生成excel表格

直接上代码

from openpyxl import Workbook
from openpyxl.styles import Alignment
from openpyxl.worksheet.table import Table, TableStyleInfo

wb = Workbook()
ws = wb.active

data = [
['Apples', 10000, 5000, 8000, 6000],
['Pears',   2000, 3000, 4000, 5000],
['Bananas', 6000, 6000, 6500, 6000],
['Oranges',  500,  300,  200,  700],
]

# add column headings. NB. these must be strings
ws.append(["Fruit", "2011", "2012", "2013", "2014"])

for row in data:
    ws.append(row)

# 合并单元格
ws.merge_cells(start_row=1, start_column=2, end_row=1, end_column=5)
ws.merge_cells(start_row=2, start_column=1, end_row=5, end_column=1)


tab = Table(displayName="Table1", ref="A1:E5")
tab.headerRowCount = False

# Add a default style with striped rows and banded columns
style = TableStyleInfo(name="TableStyleMedium9", showFirstColumn=True,
        showLastColumn=False, showRowStripes=True, showColumnStripes=False)

tab.tableStyleInfo = style


# 实现居中对齐
nrows = ws.max_row
ncols = ws.max_column
for i in range(nrows):
    for j in range(ncols):
        ws.cell(row=i+1, column=j+1).alignment = Alignment(horizontal='center', vertical='center')

ws.add_table(tab)
wb.save("table.xlsx")

效果图：