How to change the formatting of the URL fields when exporting from pandas to excel using xlsxwriter?

user_18032685 0
Viewed 39

I try to change the formatting of the URL fields which I got from saving pandas to excel

Code below should work for all usable area of my excel file but for some reason first column which contain web adresses didn't changed its style. Instead it still use the default excel formatting

writer = pd.ExcelWriter(f"{date_filename}.xlsx", engine="xlsxwriter")

df.to_excel(writer, index=False, sheet_name='current')

workbook = writer.book
worksheet = writer.sheets['current']

font_format = workbook.add_format({'font_name': 'Calibri', 'font_size': 13})
header_format = workbook.add_format({'font_name': 'Calibri', 'font_size': 13, 'bold': True})

worksheet.set_column('A:H', None, font_format)
worksheet.set_row(0, None, header_format)


writer.save()

My df have a few columns where the first one is a url one (an object Dtype)

#   Column                Non-Null Count  Dtype  
---  ------                --------------  -----  
 0   url                   2 non-null      object

Where I store w.g.

https://google.com in a first row, 
https://amazon.com in a second etc

I am wondering why it keeps the default excel formatting.

xlsxwriter pandas dataframe python-3.x python
1 Answers

i really don't see the problem, but to be sure, it is recommended that you don't declare the variable writer = pd.ExcelWriter instead use whit

example code

import pandas as pd

tops = [
    {"name": "google", "url": "https://www.google.com"},
    {"name": "amazon", "url": "https://www.amazon.com"},
    {"name": "apple", "url": "https://www.apple.com"},
]

df = pd.DataFrame(tops)

with pd.ExcelWriter("excel.xlsx", engine="xlsxwriter") as writer:
    df.to_excel(writer, index=False, sheet_name="current")

    workbook = writer.book
    worksheet = writer.sheets["current"]

    font_format = workbook.add_format({"font_name": "Calibri", "font_size": 13})
    header_format = workbook.add_format(
        {"font_name": "Calibri", "font_size": 13, "bold": True}
    )

    worksheet.set_column("A:H", None, font_format)
    worksheet.set_row(0, None, header_format)

print("save")

response

enter image description here

this it worked!

user_92421050
Related