英文:
trying to add sparklines to multiple rows with xlsxwriter
问题
I'm 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()
英文:
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()
答案1
得分: 2
只需使用一个循环。从起始行(在这种情况下是第3行)到结束行,您可以根据df的大小或其他设置来确定结束行,具体取决于您想应用的行数。然后使用计数来在每次循环中替换行的值。
...
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'})
...
英文:
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'})
...
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论