for item in results:
titleformat = book.add_format({'bold': True, 'align': 'center'})
center = book.add_format({'align' : 'center'})
usersheet.write(0, 0, "#" , titleformat)
usersheet.write(0, 1, "User" , titleformat)
usersheet.write(0, 2, "Group" , titleformat)
usersheet.write(0, 3, "SVN Commits" , titleformat)
usersheet.write(0, 4, "GIT Commits" , titleformat)
usersheet.write(0, 5, "GITLAB Commits", titleformat)
usersheet.write(0, 6, "Gerrit Review" , titleformat)
.
.
.
.
#### set column widths
usersheet.set_column(userrowno, 0, 5)
for i in range(1,26):
usersheet.set_column(userrowno, i, 20)
userrowno = userrowno + 1
With this piece of code, we dynamically create two Excel sheets. However, because the data is always changing, the last row looks different for each data. How can I dynamically write the total data at the end of each column in Python?The TOTAL text in the picture should be generated as generic in the code and should be written at the end of each column.
The easiest way would be to read this into a dataframe in pandas, sum on columns, then rewrite your sheet with the titles.
import pandas as pd
df = pd.read_excel(path_to_sheet)
df.loc["Total"] = df.sum()
writer = pd.ExcelWriter(path_where_you_want)
...do what you're doing above with pandas writer object...
df.to_excel(writer)
https://pandas.pydata.org/docs/reference/api/pandas.ExcelWriter.html
https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.to_excel.html
Edit: you may need to set your index if you have non-numeric columns