Search code examples
pythonpandasexcelxlsxwriter

trying to add sparklines to multiple rows with xlsxwriter

Im trying to add sparklines into a column with xlsxwriter to my excel. I got it to work with just one row but i wanted to ask how i would do this for multiple rows without writing out multiple lines of code for each row. My code so far is below. Any help appreciated.

import pandas as pd


df = pd.read_excel(r'C:\Users\Qtr2.xlsx')
df.to_excel(r'C:\Users\Test.xlsx', index=False, sheet_name='Sheet1')

writer= pd.ExcelWriter(r'C:\Users\Enhanced.xlsx', engine='xlsxwriter')
df.to_excel(writer, index=False, sheet_name='Sheet1')

wb= writer.book
ws= writer.sheets['Sheet1']

ws.add_sparkline('E2', {'range':'Sheet1!B2:D2'})

ws.add_sparkline('E3', {'range':'Sheet1!B3:D3', 'type':'column'})

writer.save()
Solution

  • Just use a loop. A range covering the start row, row 3 in this case, to an end row which you can determine from the df size or other setting depending on what the number of rows is that you want to apply to. Then use the count to substitute the row value on each loop.

    ...
max_rows = df.shape[0]

ws.add_sparkline('E2', {'range': 'Sheet1!B2:D2'})

for row in range(3, max_rows+1):
    ws.add_sparkline(f'E{row}', {'range': f'Sheet1!B{row}:D{row}', 'type': 'column'})
...