Excel Document Generation:

Anvil Q&A
1

What I’m trying to do:
Hello! I am trying to generate a stylized Excel Document that will be able to take user input (from a button or dropdown) and create an Excel Sheet that can be downloaded. I have the code working in Python, but I am having trouble translating this into Anvil.

What I’ve tried and what’s not working:
I have tried a couple of things from the knowledge base, but I am not even sure my approach is correct.

  • I tried to create a Data Table that accepts a Media Type, to see if I could generate the file and export it to a table, and then download the file from the table. This ultimately led to a new row being created but the row was empty.
  • I tried the steps in this article Download excel file - #3 by javier which looks like it saves the created file to memory and then calls it, but I got serialization errors.

Code Sample: (This creates the spreadsheet)

WB = Workbook()
GRAY = "00C0C0C0"
LIGHT_BLUE = "CCE5FF"
FILL_TYPE = "solid"
CELL_STYLE = "Pandas"
CELL_FONT = Font(size=12)
CELL_HYPERLINK = "Hyperlink"
FILE_NAME = "C:/Users/Joshua/Downloads/compass_nc.xlsx"
sheet_names = []
df_sheets_dict = {}
def create_sheet_columns():
    """This function creates columns that will appear in all sheets defined in sheet_names"""
    for idx in range(len(sheet_names)):
    # for sheet_name in sheet_names:
        column_data = {
            "Column1": ['=HYPERLINK("#\'Table of Contents\'!A1", "Table of Contents")'],
            "Column2": ["Phase"],
            "Column3": ["Tasks"],
            "Column4": ["Subtask"],
            "Column5": ["Resource Assigned"],
        }
        df_sheets_dict[idx] = pd.DataFrame(column_data)
def write_sheets():
    """Creates a spreadsheet with worksheets defined in the sheet_names variable 
        and hyperlinks each worksheet to a Table of Contents page
    """
    for idx in range(len(df_sheets_dict)):
        work_sheet = WB.create_sheet(title=sheet_names[idx], index=idx)
        df_rows = dataframe_to_rows(df_sheets_dict[idx], index=False, header=False)
        for df_row in df_rows:
            work_sheet.append(df_row)
        for cell in work_sheet[1]:
            cell.style = CELL_STYLE
            cell.font = CELL_FONT
        row_fills = work_sheet.iter_rows(min_row=1, max_row=10, min_col=1, max_col=50)
        for row_fill in row_fills:
            for cell in row_fill:
                if cell.row == 1:
                    cell.fill = PatternFill(
                        start_color=GRAY, end_color=GRAY, fill_type=FILL_TYPE
                    )
    toc = WB.create_sheet(title="Table of Contents", index=0)
    toc["B2"] = "Table of Contents"
    toc["B2"].style = CELL_STYLE
    toc_rows = toc.iter_rows(min_row=1, max_row=100, min_col=1, max_col=100)
    for toc_row in toc_rows:
        for cell in toc_row:
            cell.fill = PatternFill(
                start_color=LIGHT_BLUE, end_color=LIGHT_BLUE, fill_type=FILL_TYPE
            )
    sqa = WB.create_sheet(title="Scope Questions", index=1)
    sqa["A1"] = '=HYPERLINK("#\'Table of Contents\'!A1", "Table of Contents")'
    sqa["B1"] = "Scope Questions"
    sqa["C1"] = "Answers"
    sqa["D1"] = "Next Steps"
    sqa["E1"] = "Notes"
    sqa["A1"].style = CELL_STYLE
    sqa["B1"].style = CELL_STYLE
    sqa["C1"].style = CELL_STYLE
    sqa["D1"].style = CELL_STYLE
    sqa["E1"].style = CELL_STYLE
    sqa_rows = sqa.iter_rows(min_row=1, max_row=100, min_col=1, max_col=100)
    for sqa_row in sqa_rows:
        for cell in sqa_row:
            if cell.row == 1:
                cell.fill = PatternFill(
                    start_color=GRAY, end_color=GRAY, fill_type=FILL_TYPE
                )
    sheet_names.insert(0, "Scope Questions")
    for idx in range(len(sheet_names)):
        sheet_ref = "#'{}'!{}".format(sheet_names[idx], "A1")
        toc.cell(row=idx + 3, column=2).value = '=HYPERLINK("{}", "{}")'.format(
            sheet_ref, sheet_names[idx]
        )
        toc.cell(row=idx + 3, column=2).style = CELL_HYPERLINK
    WB.save(FILE_NAME)
create_sheet_columns()
write_sheets() ```

Here is where I left off last night:

https://anvil.works/build#clone:QP2WDDDHMQUFKMHV=JIOB7YIANIQLFCWKZJLIRIT2

I am happy to dig in and google more, but I feel like I might be overcomplicating this and going in the wrong direction. Any ideas or suggestions?

2

Welcome to the forum! I was able to save the Excel file to the data table by just changing the last line of the server module (and importing anvil.media at the top):

app_tables.table_1.add_row(doc=anvil.media.from_file("doc.xlsx"))

edit: Instead of “doc.xlsx”, the clone link now uses “/tmp/doc.xlsx”, per Excel Document Generation: - #8 by hugetim

2 Likes
3

Thanks, that worked perfectly!

1 Like
4

Now if I wanted to pass data to this list in the Server-Side Code, from a client-facing text box, would I need to move this variable from the root of the Server-Side Code to a new Server-Side function?

sheet_names =

5

That would make sense, yes. It sometimes makes sense to use a server module global variable for something other than a constant, but most often not.

6

Hi @hugetim

I’ve recently started a similar project and so was excited to learn from your solution. The clone link you left however, produces an error:

OSError: [Errno 30] Read-only file system: 'doc.xlsx'
at /usr/local/lib/python3.10/zipfile.py:1251

Are my settings different somehow? Many thanks…

7

I get the same error. I found this thread by searching the error itself, as I have run into the same issue with an app that was previously working - no code or build changes made. Can’t see any other references to this error, which seems to occur in apps which were previously running fine? Is there an obvious fix that we are missing? Thanks.
Great Excel idea btw.

8

See:

9

Ok great, thanks a lot.

1 Like