【pyxls】openpyxl.worksheet.table.TableStyleInfo格式效果

 TableStyleMedium1

Fruit	2011	2012	2013	2014
Apples	10000	5000	8000	6000
Pears	2000	3000	4000	5000
Bananas	6000	6000	6500	6000
Oranges	500	300	200	700

TableStyleMedium2

Fruit	2011	2012	2013	2014
Apples	10000	5000	8000	6000
Pears	2000	3000	4000	5000
Bananas	6000	6000	6500	6000
Oranges	500	300	200	700

TableStyleMedium3

Fruit	2011	2012	2013	2014
Apples	10000	5000	8000	6000
Pears	2000	3000	4000	5000
Bananas	6000	6000	6500	6000
Oranges	500	300	200	700

TableStyleMedium4

Fruit	2011	2012	2013	2014
Apples	10000	5000	8000	6000
Pears	2000	3000	4000	5000
Bananas	6000	6000	6500	6000
Oranges	500	300	200	700

TableStyleMedium5

Fruit	2011	2012	2013	2014
Apples	10000	5000	8000	6000
Pears	2000	3000	4000	5000
Bananas	6000	6000	6500	6000
Oranges	500	300	200	700

TableStyleMedium6

Fruit	2011	2012	2013	2014
Apples	10000	5000	8000	6000
Pears	2000	3000	4000	5000
Bananas	6000	6000	6500	6000
Oranges	500	300	200	700

TableStyleMedium7

Fruit	2011	2012	2013	2014
Apples	10000	5000	8000	6000
Pears	2000	3000	4000	5000
Bananas	6000	6000	6500	6000
Oranges	500	300	200	700

TableStyleMedium8

Fruit	2011	2012	2013	2014
Apples	10000	5000	8000	6000
Pears	2000	3000	4000	5000
Bananas	6000	6000	6500	6000
Oranges	500	300	200	700

TableStyleMedium9

Fruit	2011	2012	2013	2014
Apples	10000	5000	8000	6000
Pears	2000	3000	4000	5000
Bananas	6000	6000	6500	6000
Oranges	500	300	200	700

TableStyleMedium10

Fruit	2011	2012	2013	2014
Apples	10000	5000	8000	6000
Pears	2000	3000	4000	5000
Bananas	6000	6000	6500	6000
Oranges	500	300	200	700

TableStyleMedium11

Fruit	2011	2012	2013	2014
Apples	10000	5000	8000	6000
Pears	2000	3000	4000	5000
Bananas	6000	6000	6500	6000
Oranges	500	300	200	700

TableStyleMedium12

Fruit	2011	2012	2013	2014
Apples	10000	5000	8000	6000
Pears	2000	3000	4000	5000
Bananas	6000	6000	6500	6000
Oranges	500	300	200	700

TableStyleMedium13

Fruit	2011	2012	2013	2014
Apples	10000	5000	8000	6000
Pears	2000	3000	4000	5000
Bananas	6000	6000	6500	6000
Oranges	500	300	200	700

TableStyleMedium14

Fruit	2011	2012	2013	2014
Apples	10000	5000	8000	6000
Pears	2000	3000	4000	5000
Bananas	6000	6000	6500	6000
Oranges	500	300	200	700

TableStyleMedium15

Fruit	2011	2012	2013	2014
Apples	10000	5000	8000	6000
Pears	2000	3000	4000	5000
Bananas	6000	6000	6500	6000
Oranges	500	300	200	700

 

TableStyleMedium16

Fruit	2011	2012	2013	2014
Apples	10000	5000	8000	6000
Pears	2000	3000	4000	5000
Bananas	6000	6000	6500	6000
Oranges	500	300	200	700

TableStyleMedium17

Fruit	2011	2012	2013	2014
Apples	10000	5000	8000	6000
Pears	2000	3000	4000	5000
Bananas	6000	6000	6500	6000
Oranges	500	300	200	700

TableStyleMedium18

Fruit	2011	2012	2013	2014
Apples	10000	5000	8000	6000
Pears	2000	3000	4000	5000
Bananas	6000	6000	6500	6000
Oranges	500	300	200	700

TableStyleMedium19

Fruit	2011	2012	2013	2014
Apples	10000	5000	8000	6000
Pears	2000	3000	4000	5000
Bananas	6000	6000	6500	6000
Oranges	500	300	200	700

TableStyleMedium20

Fruit	2011	2012	2013	2014
Apples	10000	5000	8000	6000
Pears	2000	3000	4000	5000
Bananas	6000	6000	6500	6000
Oranges	500	300	200	700

TableStyleMedium21

Fruit	2011	2012	2013	2014
Apples	10000	5000	8000	6000
Pears	2000	3000	4000	5000
Bananas	6000	6000	6500	6000
Oranges	500	300	200	700

22

Fruit	2011	2012	2013	2014
Apples	10000	5000	8000	6000
Pears	2000	3000	4000	5000
Bananas	6000	6000	6500	6000
Oranges	500	300	200	700

23

Fruit	2011	2012	2013	2014
Apples	10000	5000	8000	6000
Pears	2000	3000	4000	5000
Bananas	6000	6000	6500	6000
Oranges	500	300	200	700

24

Fruit	2011	2012	2013	2014
Apples	10000	5000	8000	6000
Pears	2000	3000	4000	5000
Bananas	6000	6000	6500	6000
Oranges	500	300	200	700

25

Fruit	2011	2012	2013	2014
Apples	10000	5000	8000	6000
Pears	2000	3000	4000	5000
Bananas	6000	6000	6500	6000
Oranges	500	300	200	700

26

Fruit	2011	2012	2013	2014
Apples	10000	5000	8000	6000
Pears	2000	3000	4000	5000
Bananas	6000	6000	6500	6000
Oranges	500	300	200	700

27

Fruit	2011	2012	2013	2014
Apples	10000	5000	8000	6000
Pears	2000	3000	4000	5000
Bananas	6000	6000	6500	6000
Oranges	500	300	200	700

28

Fruit	2011	2012	2013	2014
Apples	10000	5000	8000	6000
Pears	2000	3000	4000	5000
Bananas	6000	6000	6500	6000
Oranges	500	300	200	700

29

Fruit	2011	2012	2013	2014
Apples	10000	5000	8000	6000
Pears	2000	3000	4000	5000
Bananas	6000	6000	6500	6000
Oranges	500	300	200	700

30

Fruit	2011	2012	2013	2014
Apples	10000	5000	8000	6000
Pears	2000	3000	4000	5000
Bananas	6000	6000	6500	6000
Oranges	500	300	200	700

31

Fruit	2011	2012	2013	2014
Apples	10000	5000	8000	6000
Pears	2000	3000	4000	5000
Bananas	6000	6000	6500	6000
Oranges	500	300	200	700

32

Fruit	2011	2012	2013	2014
Apples	10000	5000	8000	6000
Pears	2000	3000	4000	5000
Bananas	6000	6000	6500	6000
Oranges	500	300	200	700

33

Fruit	2011	2012	2013	2014
Apples	10000	5000	8000	6000
Pears	2000	3000	4000	5000
Bananas	6000	6000	6500	6000
Oranges	500	300	200	700

34

Fruit	2011	2012	2013	2014
Apples	10000	5000	8000	6000
Pears	2000	3000	4000	5000
Bananas	6000	6000	6500	6000
Oranges	500	300	200	700

 

...

 

 

 

测试代码

# -*- coding: utf-8 -*-
import os
from openpyxl import load_workbook
from openpyxl import Workbook
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],
]

ws1 =wb.create_sheet(index=1)
ws2 =wb.create_sheet(index=2)
ws3 =wb.create_sheet(index=3)
ws4 =wb.create_sheet(index=4)
ws5 =wb.create_sheet(index=5)

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

for row in data:
    ws.append(row)
    ws1.append(row)
    ws2.append(row)
    ws3.append(row)
    ws4.append(row)
    ws5.append(row)
    
# Add a default style with striped rows and banded columns
style =  TableStyleInfo(name="TableStyleMedium19", showFirstColumn=True,showLastColumn=True, showRowStripes=True, showColumnStripes=True)
style1 = TableStyleInfo(name="TableStyleMedium20", showFirstColumn=True,showLastColumn=True, showRowStripes=True, showColumnStripes=True)
style2 = TableStyleInfo(name="TableStyleMedium21", showFirstColumn=True,showLastColumn=True, showRowStripes=True, showColumnStripes=True)
style3 = TableStyleInfo(name="TableStyleMedium22", showFirstColumn=True,showLastColumn=True, showRowStripes=True, showColumnStripes=True)
style4 = TableStyleInfo(name="TableStyleMedium23", showFirstColumn=True,showLastColumn=True, showRowStripes=True, showColumnStripes=True)
style5 = TableStyleInfo(name="TableStyleMedium24", showFirstColumn=True,showLastColumn=True, showRowStripes=True, showColumnStripes=True)

#第一列是否和样式第一行颜色一行，第二列是否···
#是否隔行换色，是否隔列换色
tab = Table(displayName="Table", ref="A1:E5")
tab1 = Table(displayName="Table1", ref="A1:E5")
tab2 = Table(displayName="Table2", ref="A1:E5")
tab3 = Table(displayName="Table3", ref="A1:E5")
tab4 = Table(displayName="Table4", ref="A1:E5")
tab5 = Table(displayName="Table5", ref="A1:E5")

tab.tableStyleInfo = style
ws.add_table(tab)
tab1.tableStyleInfo = style1
ws1.add_table(tab1)
tab2.tableStyleInfo = style2
ws2.add_table(tab2)
tab3.tableStyleInfo = style3
ws3.add_table(tab3)
tab4.tableStyleInfo = style4
ws4.add_table(tab4)
tab5.tableStyleInfo = style5
ws5.add_table(tab5)

# Save the file
os.remove("sample.xlsx")
wb.save("sample.xlsx")