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()
Advertisement
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], })