1

I am building a little automatic reporting tool for my job. I am trying to make my code work to create another sheet every time (each day) that I run the program and generate the report. 

date_time = time.strftime('%b %d %Y')

writer = pd.ExcelWriter('BrokerRisk.xlsx', engine='xlsxwriter')
df.to_excel(writer,'DataFrame-' + date_time)
sums.to_excel(writer,'TotalByCounterparty-' + date_time)
sums_sort.to_excel(writer,'SortedRank-' + date_time)

workbook  = writer.book
worksheet1 = writer.sheets['DataFrame-' + date_time]
worksheet2 = writer.sheets['TotalByCounterparty-' + date_time]
worksheet3 = writer.sheets['SortedRank-' + date_time]
writer.save()

I tried implementing the date feature so that it would change the name technically every day, but this doesn't seem to work either. Can anyone suggest a simple fix?

sgerbhctim
  • 3,420
  • 7
  • 38
  • 60
  • You can't. Current workaround is to open the workbook, copy it, append whatever you have, and overwrite. – ayhan Jun 14 '17 at 17:53
  • What do you mean? Is there no append functionality within pandas or python or something like that? – sgerbhctim Jun 14 '17 at 17:53
  • 1
    That's more of an Excel issue rather than Python/pandas. [Here](https://stackoverflow.com/questions/20219254/how-to-write-to-an-existing-excel-file-without-overwriting-data-using-pandas) are some workarounds but note that these will also do the copying and create a new file under the hood (see also the comments). – ayhan Jun 14 '17 at 17:59

1 Answers1

1

Using engine=openpyxl as writer will do what you want, for instance:

from openpyxl import load_workbook
from copy import copy
import time

class CopyWorkbook(object):
    def __init__(self, fname):
        self.fname = fname
        self.wb = load_workbook(fname)

    def save(self):
        self.wb.save(self.fname)

    def copy_worksheet(self, from_worksheet):
        # Create new empty sheet and append it to self(Workbook)
        ws = self.wb.create_sheet( title=from_worksheet.title )

        for row, row_data in enumerate(from_worksheet.rows,1):
            for column, from_cell in enumerate(row_data,1):
                cell = ws.cell(row=row, column=column)
                cell.value = from_cell.value
                cell.font = copy(from_cell.font)

date_time = time.strftime('%b %d %Y')
writer = pd.ExcelWriter('dummy.xlsx', engine='openpyxl')
df.to_excel(writer,'DataFrame-' + date_time)
# ... Other DataFrame's .to_excel(writer, ...

wb = CopyWorkbook('BrokerRisk.xlsx')
for ws_new in writer.book.worksheets:
    wb.copy_worksheet(ws_new)

wb.save()

Tested with Python: 3.4.2 - openpyxl: 2.4.1 - LibreOffice: 4.3.3.2

stovfl
  • 14,998
  • 7
  • 24
  • 51