I have a python program that loops through a dataframe that contains multiple cases in it.

First I create a workbook like this:

        file_stream = io.BytesIO()
        writer = pd.ExcelWriter(file_stream, engine='xlsxwriter')
        workbook = writer.book

I then iterate through a dataframe like this setting up page parameters and defining a footer:

        for index, row in case_df.iterrows():
            for column in case_df.columns:
                if row['Case No']!=current_case_no:
                    current_case_no=row['Case No']
                    footer=f'&C{agency} - Case No. {current_case_no}  Page &P of &N'
                    worksheet = workbook.add_worksheet(f'{current_case_no}')
                    worksheet.set_paper('Letter')
                    worksheet.set_margins(top=.5, bottom=.5, left=.5, right=.5)
                    worksheet.set_column(0, 15, 12) 
                    worksheet.set_default_row(13)
                    worksheet.set_footer(footer)

The code then writes lines of data to the worksheet and I finish up by closing the writer and converting the workbook to a PDF like this:

        writer.close()
        # RESET THE FILE STREAM TO THE BEGINNING
        file_stream.seek(0)
        pdf_stream = excel_to_pdf_stream(file_stream)

When I view the resulting PDF, it contains all of the worksheets as expected, but page numbering in the footer is off. Instead of starting over at page 1 for each worksheet, it treats all of the worksheets as a single document so that by the time I get to the first page of the last case, it will say something like Page 74 of 80 instead of starting over at Page 1 of N.

How do I define a footer in xlsxwriter so that the page numbering starts over on each new worksheet. I’m hoping not to have to count worksheets, lines, and pages and do my page numbering manually.