Plotting barchart in Excel with Python, dynamic ca…

I try to get a chart in Excel using Python. The code below works for now but next month a new line will be added. As you can see I now only select until line 64 (categories and values) so if I want to add the next month to the chart, I have to change 64 into 65. Is there a way to automatically add this line next month?

# import xlsxwriter module
import xlsxwriter
 
# Select file and add worksheet
workbook = xlsxwriter.Workbook(Bestands_naam)
 
worksheet = workbook.add_worksheet('Data FI')
 
# Create bold format object 
bold = workbook.add_format({'bold': 1})
 
# create a data list 
headings = list(df.columns.values)
 
data = [
    list(df['col1']),
    list(df['col2']),
    list(df['col3']),
]
 
# Write a row of data 
worksheet.write_row('A1', headings, bold)
datetime_format = workbook.add_format({'num_format': 'dd/mm/yyyy'})
 
# Write a column of data 
worksheet.write_column('A2', data[0],datetime_format)
worksheet.write_column('B2', data[1])
worksheet.write_column('C2', data[2])

# Add new worksheet for chart
worksheet = workbook.add_worksheet('Grafiek')

# Create a bar chart object 
chart1 = workbook.add_chart({'type': 'column'})
 
# Add a data series to a chart
 
# Configure the first series.
chart1.add_series({
    'name':       ['Data FI', 0, 1],
    'categories': ['Data FI', 1, 0, 64 , 0],
    'values':     ['Data FI', 1, 1, 64 , 1],
})
 
# Configure a second series.
chart1.add_series({
    'name':       ['Data FI', 0, 2],
    'categories': ['Data FI', 1, 0, 64, 0],
    'values':     ['Data FI', 1, 2, 64, 2],
})
 
# Add a chart title
chart1.set_title ({'name': '  '})
 
# add chart to the worksheet
worksheet.insert_chart('B2', chart1, {'x_scale': 2, 'y_scale': 2})
 
# Close the Excel file
workbook.close()

Answer

You could calculate the last row from the df.shape or len() of the data. Like this:

max_row = len(data[0])

chart1.add_series({
    'name':       ['Data FI', 0, 1],
    'categories': ['Data FI', 1, 0, max_row , 0],
    'values':     ['Data FI', 1, 1, max_row , 1],
})
 
# Configure a second series.
chart1.add_series({
    'name':       ['Data FI', 0, 2],
    'categories': ['Data FI', 1, 0, max_row, 0],
    'values':     ['Data FI', 1, 2, max_row, 2],
})

Plotting barchart in Excel with Python, dynamic categories

Advertisement

Answer