Excel working inconsistently

🚀 Support (Q&A) 🐛 Bug & Error Reports
1

Which tool versions are you using?

SDK: v13.0.0
Platform: v2022.08.5
Python: v3.7
Isolation mode: venv

Current Behavior

I am sending some data to Excel using SpreadsheetTemplate.
Sometimes it works and the results are sent, and sometimes when opening Excel, the cells are empty.
I havent changed anything between tries and the same data is being used/sent each time.
I have used a print statement to make sure that the InputCells have the data in.

Expected Behavior

I would expect that the code would work consistently and fill the data in excel every time.

2

Hi Natalie,

We would like to solve this problem. Are you able to share a (small) code snippet, so we are able to recreate the problem on our side.

thanks, Maarten

3

Hi Maarten
Sorry for the delay in responding, I was on holiday.

Including a snippet that makes sense is somewhat hard to do.
The code basically reads in results from 3 json files, manipulates them and then sends them to a excel template.
the code below is the sending to excel.
cells = … is where it manipulates the data and does all the InputCellRange etc.

    cells = temp_excel_input(model)
    excel_path = Path(__file__).parent / "plaxis_results.xlsx"

    ATTEMPTS = 5
    for attempt in range(ATTEMPTS):
        try:
            excel_result = SpreadsheetTemplate.from_path(excel_path, input_cells=cells).render()
        except InternalError:
            continue
        else:
            break

    if attempt == ATTEMPTS - 1:
        raise UserException("Spreadsheet template kon niet gevuld worden (back-end issue)")

    zip_output = BytesIO()
    with zipfile.ZipFile(zip_output, "w") as output_file:
        output_file.writestr("xxxxxx.xlsx", excel_result.file_content)

    timestamp = get_timestamp()
    project = model.project_info.project_name
    onderdeel = model.project_info.part_name
    filename = f'xxxx{project} {onderdeel} {str(timestamp)}.zip'

    download_result = DownloadResult(file_content=zip_output.getvalue(), file_name=filename)
    return ViktorResult(download_result=download_result)
4

Hi @mweehuizen
Have you had a chance to look at this?
Any success?

Natalie

5

Hi Natalie,

I have looked at it, but see no obvious error.
Furthermore, we don’t have reports from other people that the spreadsheet functionality is not working properly.

Couple of things you can try:

  • Excluce the zipfile and download the excel directly as xlsx

  • Is there any indication that the empty spreadsheets are caused by a second or later attempt?, maybe print the final attempt number of remove that functionality for now. Maybe retrying is manipulating the cells variable or something.

greetings,

Maarten

6

Hi Maarten
I have taken out the zip file and the attempts. i occassionally get an Internal Error but mostly it runs the code but returns an empty excel.

    model = create_model(params)
    cells = temp_excel_input(model)
    excel_path = Path(__file__).parent / "plaxis_results.xlsx"
    print(cells)

    excel_result = SpreadsheetTemplate.from_path(excel_path, input_cells=cells).render()
    download_result = DownloadResult(file_content=excel_result.file_content, file_name="Test.xlsx")
    return ViktorResult(download_result=download_result)

print statement:

[<viktor.external.spreadsheet.DirectInputCell object at 0x000001EE735FFB48>, <viktor.external.spreadsheet.DirectInputCell object at 0x000001EE735FFB88>, <viktor.external.spreadsheet.DirectInputCell object at 0x000001EE735FFBC8>, <vikto
r.external.spreadsheet.DirectInputCell object at 0x000001EE735FFC08>, <viktor.external.spreadsheet.DirectInputCell object at 0x000001EE735FFC88>, <viktor.external.spreadsheet.DirectInputCell object at 0x000001EE735FFD08>, <viktor.exter
nal.spreadsheet.DirectInputCell object at 0x000001EE735FFD48>, <viktor.external.spreadsheet.DirectInputCell object at 0x000001EE735FFE48>, <viktor.external.spreadsheet.DirectInputCell object at 0x000001EE735FFEC8>, <viktor.external.spr
eadsheet.DirectInputCell object at 0x000001EE735FFF08>, <viktor.external.spreadsheet.DirectInputCell object at 0x000001EE735FFF88>, <viktor.external.spreadsheet.DirectInputCell object at 0x000001EE7360D048>, <viktor.external.spreadshee
t.DirectInputCell object at 0x000001EE7360D088>, <viktor.external.spreadsheet.DirectInputCell object at 0x000001EE7360D0C8>, <viktor.external.spreadsheet.DirectInputCell object at 0x000001EE7360D148>, <viktor.external.spreadsheet.Direc
tInputCell object at 0x000001EE7360D188>, <viktor.external.spreadsheet.DirectInputCell object at 0x000001EE7360D1C8>, <viktor.external.spreadsheet.DirectInputCell object at 0x000001EE7360D208>, <viktor.external.spreadsheet.DirectInputC
ell object at 0x000001EE7360D248>, <viktor.external.spreadsheet.DirectInputCell object at 0x000001EE7360D308>, <viktor.external.spreadsheet.InputCellRange object at 0x000001EE7360D348>, <viktor.external.spreadsheet.DirectInputCell obje
ct at 0x000001EE7360D408>, <viktor.external.spreadsheet.InputCellRange object at 0x000001EE7360D448>, <viktor.external.spreadsheet.DirectInputCell object at 0x000001EE7360D508>, <viktor.external.spreadsheet.InputCellRange object at 0x0
00001EE7360D548>, <viktor.external.spreadsheet.InputCellRange object at 0x000001EE7360D608>, <viktor.external.spreadsheet.InputCellRange object at 0x000001EE73610E08>, <viktor.external.spreadsheet.InputCellRange object at 0x000001EE736
18648>, <viktor.external.spreadsheet.DirectInputCell object at 0x000001EE73618708>, <viktor.external.spreadsheet.InputCellRange object at 0x000001EE73618748>, <viktor.external.spreadsheet.DirectInputCell object at 0x000001EE73618808>,
<viktor.external.spreadsheet.InputCellRange object at 0x000001EE73618848>, <viktor.external.spreadsheet.DirectInputCell object at 0x000001EE73618908>, <viktor.external.spreadsheet.InputCellRange object at 0x000001EE73618948>, <viktor.e
xternal.spreadsheet.InputCellRange object at 0x000001EE73618A08>, <viktor.external.spreadsheet.InputCellRange object at 0x000001EE73621248>, <viktor.external.spreadsheet.InputCellRange object at 0x000001EE73625A48>, <viktor.external.sp
readsheet.DirectInputCell object at 0x000001EE73625AC8>, <viktor.external.spreadsheet.DirectInputCell object at 0x000001EE73625B08>, <viktor.external.spreadsheet.DirectInputCell object at 0x000001EE73625B48>, <viktor.external.spreadshe
et.DirectInputCell object at 0x000001EE73625B88>, <viktor.external.spreadsheet.DirectInputCell object at 0x000001EE73625BC8>, <viktor.external.spreadsheet.DirectInputCell object at 0x000001EE73625C08>]
7

Hi Natalie,

Is the excel now always empty or still sometimes?

Can you add the following code below print(cells)

        with open('cells.txt', 'w') as fp:
            json.dump([cell.serialize() for cell in cells], fp)

and send ‘cells.txt’ and ‘plaxis_results.xlsx’ to me? (i have send my email in a dm) thanks

8

Maarten
Sometimes it fills. mostly when it hasnt been run in a few minutes, but that is also not consistent.

9

Hi Natalie,

I managed to recreate the problem. So this is a huge step forward to fix the problem.

It seems that shortly after a succesfull render, the spreadsheet templater throws an InternalError. When retrying after the InternalError, the templater returns a empty/unfilled template.

So this is a bug in the platfom and the platfom team is on it. I will report back when it is fixed.

10

Thanks Maarten
Nice to know I’m not going crazy!

Fijn weekend