In [34]:

import pandas as pd
import openpyxl
from openpyxl import Workbook, load_workbook
from openpyxl.styles import Color, PatternFill, Font, Border
from openpyxl.styles.borders import Border, Side, BORDER_THIN
from openpyxl.styles import colors
from openpyxl.cell import Cell
import io

In [33]:

openpyxl.__version__

Out[33]:

'2.6.2'

In [ ]:

#https://stackoverflow.com/questions/30484220/fill-cells-with-colors-using-openpyxl
#https://stackoverflow.com/questions/24917201/applying-borders-to-a-cell-in-openpyxl

In [5]:

df = pd.DataFrame([[1,2,3], [4,5,6]])
df.columns = ['a', 'b', 'c']

In [8]:

df.head()

Out[8]:

	a	b	c
0	1	2	3
1	4	5	6

In [26]:

output = io.BytesIO()
df.to_excel(output, index=False)

In [27]:

workbook = load_workbook(output)

In [28]:

s = workbook['Sheet1']

In [49]:

#styles
whitehex = 'ffffff'
fontWhite = Font(color=whitehex)
darkBlue = PatternFill(start_color='305496', end_color='305496', fill_type='solid')
lightBlue = PatternFill(start_color='e9ebf5', end_color='e9fbf5', fill_type='solid')
border = Border(
    left=Side(border_style=BORDER_THIN, color=whitehex),
    right=Side(border_style=BORDER_THIN, color=whitehex),
    top=Side(border_style=BORDER_THIN, color=whitehex),
    bottom=Side(border_style=BORDER_THIN, color=whitehex)
)

In [56]:

for idx, row in enumerate(s.iter_rows()):
    for cell in row:
        if idx == 0:
            cell.fill = darkBlue
            cell.font = fontWhite
        else:
            cell.fill = lightBlue
        cell.border = border
    print(idx, row)

0 (<Cell 'Sheet1'.A1>, <Cell 'Sheet1'.B1>, <Cell 'Sheet1'.C1>)
1 (<Cell 'Sheet1'.A2>, <Cell 'Sheet1'.B2>, <Cell 'Sheet1'.C2>)
2 (<Cell 'Sheet1'.A3>, <Cell 'Sheet1'.B3>, <Cell 'Sheet1'.C3>)

In [57]:

workbook.save("test.xlsx")

In [58]:

from IPython.display import display
from PIL import Image
display(Image.open('./image.png'))

In [ ]: