Print Colored Font Into Excel Using Pandas

Question

I'm trying to print a green, bold font into an excel spreadsheet.

I can print this in jupyter notebook without a problem, but this is what I get in the spreadsheet: [1m[92mHello

import pandas as pd
import numpy as np

writer = pd.ExcelWriter('out.xlsx')
pd.DataFrame([1,'\033[1m' + '\033[92m'+ 'Hello',3]).to_excel(writer, sheet_name= 'sheet1')
writer.save()

Any reason why you are using pandas for this? Why don't you use xlwings (Docs) — najeem
– najeem, Commented Jan 7, 2019 at 3:48
I retrieve the data from a database then write to excel. That's the final output. I'm a newbie with pandas — Don Cheeto
– Don Cheeto, Commented Jan 7, 2019 at 3:51

najeem · Accepted Answer · 2019-01-07 07:15:10Z

3

You can use the ExcelWriter classes and methods to do many things in the workbook/worksheet. To do what you are intending to do, do the following.

import pandas as pd
import numpy as np

writer = pd.ExcelWriter('out.xlsx', engine='xlsxwriter')
pd.DataFrame([1,'Hello',3]).to_excel(writer, sheet_name= 'sheet1')
worksheet = writer.sheets['sheet1']
workbook = writer.book
cell_format = workbook.add_format({'bold':True, 'font_color': 'green'})
worksheet.set_row(2,None,cell_format)
writer.save()

Documentation of ExcelWriter

Also, If you are trying to change the format of the header, you have to reset the header style first. Put the following before defining the writer

pd.io.formats.excel.header_style = None

edited Jan 7, 2019 at 7:15

answered Jan 7, 2019 at 4:50

najeem

1,92116 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

ycx · Accepted Answer · 2019-01-07 04:23:28Z

1

pandas cannot achieve that.
You can use openpyxl to do so.
So what you have to do is export your data into excel using pandas, and then load the workbook using openpyxl and handle the coloring and other visualisation aspects from there.

from openpyxl.styles import colors
from openpyxl.styles import Font, Color
from openpyxl import Workbook
wb = load_workbook('yourworkbookname.xlsx')
ws = wb.active
a1 = ws['A1']
d4 = ws['D4']
ft = Font(color=colors.GREEN, bold=True)
a1.font = ft
d4.font = ft
wb.save()

For more documentation into openpyxl, visit here

edited Jan 7, 2019 at 4:23

answered Jan 7, 2019 at 4:18

ycx

3,2193 gold badges16 silver badges26 bronze badges

2 Comments

Don Cheeto Over a year ago

is there a function to close the workbook once I'm done writing to it?

ycx Over a year ago

@DonCheeto Yeah included wb.save(). I'm slightly rusty but there could be a wb.close() too. You can test that out haha

Collectives™ on Stack Overflow

Print Colored Font Into Excel Using Pandas

2 Answers 2

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related