Python XlsxWriter - Chart Formatting

The default appearance of chart can be customized to make it more appeapng, explanatory and user friendly. With XlsxWriter, we can do following enhancements to a Chart object −

Set and format chart title

Set the X and Y axis titles and other parameters

Configure the chart legends

Chat layout options

Setting borders and patterns

Title

You can set and configure the main title of a chart object by calpng its set_title() method. Various parameters that can be are as follows −

Name − Set the name (title) for the chart to be displayed above the chart. The name property is optional. The default is to have no chart title.

name_font − Set the font properties for the chart title.

Overlay − Allow the title to be overlaid on the chart.

Layout − Set the (x, y) position of the title in chart relative units.

None − Excel adds an automatic chart title. The none option turns this default title off. It also turns off all other set_title() options.

X and Y axis

The two methods set_x_axis() and set_y_axis() are used to axis titles, the name_font to be used for the title text, the num_font to be used for numbers displayed on the X and Y axis.

name − Set the title or caption for the axis.

name_font − Set the font properties for the axis title.

num_font − Set the font properties for the axis numbers.

num_format − Set the number format for the axis.

major_gridpnes − Configure the major gridpnes for the axis.

display_units − Set the display units for the axis.

In the previous example, where the data of markpst has been shown in the form of a column chart, we set up the chart formatting options such as the chart title and X as well as Y axis captions and their other display properties as follows −

chart1.set_x_axis(
   { name :  Students ,  name_font :{ name : Arial ,  size :16,  bold :True},})
chart1.set_y_axis(
   {
       name :  Marks ,  name_font :
      { name : Arial ,  size :16,  bold :True},  num_font :{ name : Arial ,  itapc :True}
   }
)

Example

Add the above snippet in the complete code. It now looks as given below −

import xlsxwriter

wb = xlsxwriter.Workbook( hello.xlsx )
worksheet = wb.add_worksheet()
chart1 = wb.add_chart({ type :  column })

# Add the worksheet data that the charts will refer to.
headings = [ Name ,  Phy ,  Maths ]

data = [
   ["Jay", 30, 60],
   ["Mohan", 40, 50],
   ["Veeru", 60, 70],
]

worksheet.write_row(0,0, headings)
worksheet.write_row(1,0, data[0])
worksheet.write_row(2,0, data[1])
worksheet.write_row(3,0, data[2])

chart1.add_series({
    name :  =Sheet1!$B$1 ,
    categories :  =Sheet1!$A$2:$A$4 ,
    values :  =Sheet1!$B$2:$B$4 ,
})

chart1.add_series({
    name : [ Sheet1 , 0, 2],
    categories : [ Sheet1 , 1, 0, 3, 0],
    values : [ Sheet1 , 1, 2, 3, 2],
})
chart1.set_title ({ name :  Markpst ,
    name_font : { name : Times New Roman ,  size :24}
})
chart1.set_x_axis({ name :  Students ,
    name_font : { name : Arial ,  size :16,  bold :True},
})
chart1.set_y_axis({ name :  Marks ,
    name_font :{ name : Arial ,  size :16,  bold :True},
    num_font :{ name : Arial ,  itapc :True}
})
worksheet.insert_chart( B7 , chart1)

wb.close()

Output

The chart shows the title and axes captions as follows −