尝试在多行中使用xlsxwriter添加迷你趋势图。

huangapple go评论91阅读模式
英文:

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'})
...

huangapple
  • 本文由 发表于 2023年5月11日 09:19:31
  • 转载请务必保留本文链接:https://go.coder-hub.com/76223526.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定