1##############################################################################
2#
3# An example of converting a Pandas dataframe to an xlsx file
4# with column formats using Pandas and XlsxWriter.
5#
6# Copyright 2013-2021, John McNamara, jmcnamara@cpan.org
7#
8
9import pandas as pd
10
11# Create a Pandas dataframe from some data.
12df = pd.DataFrame({'Numbers':    [1010, 2020, 3030, 2020, 1515, 3030, 4545],
13                   'Percentage': [.1,   .2,   .33,  .25,  .5,   .75,  .45 ],
14})
15
16# Create a Pandas Excel writer using XlsxWriter as the engine.
17writer = pd.ExcelWriter("pandas_column_formats.xlsx", engine='xlsxwriter')
18
19# Convert the dataframe to an XlsxWriter Excel object.
20df.to_excel(writer, sheet_name='Sheet1')
21
22# Get the xlsxwriter workbook and worksheet objects.
23workbook  = writer.book
24worksheet = writer.sheets['Sheet1']
25
26# Add some cell formats.
27format1 = workbook.add_format({'num_format': '#,##0.00'})
28format2 = workbook.add_format({'num_format': '0%'})
29
30# Note: It isn't possible to format any cells that already have a format such
31# as the index or headers or any cells that contain dates or datetimes.
32
33# Set the column width and format.
34worksheet.set_column('B:B', 18, format1)
35
36# Set the format but not the column width.
37worksheet.set_column('C:C', None, format2)
38
39# Close the Pandas Excel writer and output the Excel file.
40writer.save()
41