Python - Excel adding custom column headings

user_18623019 0
Viewed 64

I am using the Coinmarketcap REST Api for having the latest cryptocurrency categories.
The code prints the JSON response into a DataFrame which is used for the transfer to excel (df.to_excel). Im using the "for column in df" for a bigger width in excel.
Now I would like to know how I can add costum column headings into the excel sheet.

def Cryptocurrencies():
    url = 'https://pro-api.coinmarketcap.com/v1/cryptocurrency/categories'
    parameters = {

    }
    headers = {
        'Accept': 'application/json',
        'X-CMC_PRO_API_KEY': 'mypersonalapikey',
    }

    session = Session()
    session.headers.update(headers)
    data = session.get(url, params=parameters)

    response = session.get(url, params=parameters)
    f = (json.loads(response.text)['data'])
    df = pd.DataFrame(f)
    writer = pd.ExcelWriter("Workingpaper1D.xlsx",
                            engine='xlsxwriter',
                            datetime_format='mmm d yyyy hh:mm:ss',
                            date_format='mmmm dd yyyy')
    df.to_excel(writer, sheet_name='Sheet1',
                startrow=1, header=False, index=False, )
    (max_row, max_col) = df.shape
    worksheet = writer.sheets['Sheet1']
    worksheet.autofilter(0, 0, max_row, max_col - 1)
    for column in df:
        column_length = max(df[column].astype(str).map(len).max(), len(column))
        col_idx = df.columns.get_loc(column)
        writer.sheets['Sheet1'].set_column(col_idx, col_idx, column_length)
    writer.save()
    print(df)
    return
xlsxwriter xlsx rest python
1 Answers

You can set a custom header like this:

# Convert the dataframe to an XlsxWriter Excel object. Note that we turn off
# the default header and skip one row to allow us to insert a user defined
# header.
df.to_excel(writer, sheet_name='Sheet1', startrow=1, header=False)

# Get the xlsxwriter workbook and worksheet objects.
workbook  = writer.book
worksheet = writer.sheets['Sheet1']

# Add a header format.
header_format = workbook.add_format({
    'bold': True,
    'text_wrap': True,
    'valign': 'top',
    'fg_color': '#D7E4BC',
    'border': 1})

# Write the column headers with the defined format.
for col_num, value in enumerate(df.columns.values):
    worksheet.write(0, col_num + 1, value, header_format)

See also this section of the XlsxWriter docs on Formatting of the Dataframe headers.

user_102380
Related