My results from spreadsheet calculator is always [-]

heyimsp · 2 July 2024 10:37

Hi there,

I’m trying to read results from my spreadsheet application, but the results are always [-] as shown below. I’m new to viktor and not really sure what is causing this. It only occurs when the results in the output cells are a formula, if it is a direct value, it is able to read the information just fine.

This is my excel output sheet where the values colum has simple formula that is reading values from another sheet. I can assure that the cell has the correct value when I download the evaluated spreadsheet.

This is my code:

# Data views
    @DataView('Results', duration_guess=1)
    def get_data_view(self, params, **kwargs):
        result = self.get_evaluated_spreadsheet(params)
        total_co2_one = result.values['total_co2_one']
        total_co2_two = result.values['total_co2_two']
        total_co2_three = result.values['total_co2_three']
        total_co2_four = result.values['total_co2_four']
        data = DataGroup(
            total_co2_1 = DataItem('Co2_1', total_co2_one, suffix='CO2', number_of_decimals=2),
            total_co2_2 = DataItem('Co2_2', total_co2_two, suffix='CO2', number_of_decimals=2),
            total_co2_3 = DataItem('Co2_3', total_co2_three, suffix='CO2', number_of_decimals=2),
            total_co2_4 = DataItem('Co2_4', total_co2_four, suffix='CO2', number_of_decimals=2)
        ) 
        return DataResult(data)

mslootweg · 2 July 2024 11:40

Hi @heyimsp ,

Could you check what the results are for these values by printing them?

E.g.:

# Data views
    @DataView('Results', duration_guess=1)
    def get_data_view(self, params, **kwargs):
        result = self.get_evaluated_spreadsheet(params)
        total_co2_one = result.values['total_co2_one']
        total_co2_two = result.values['total_co2_two']
        total_co2_three = result.values['total_co2_three']
        total_co2_four = result.values['total_co2_four']
        print("Total CO2 one", total_co2_one)
        print("Total CO2 two", total_co2_two)
        print("Total CO2 three", total_co2_three)
        print("Total CO2 four", total_co2_four)
        data = DataGroup(
            total_co2_1 = DataItem('Co2_1', total_co2_one, suffix='CO2', number_of_decimals=2),
            total_co2_2 = DataItem('Co2_2', total_co2_two, suffix='CO2', number_of_decimals=2),
            total_co2_3 = DataItem('Co2_3', total_co2_three, suffix='CO2', number_of_decimals=2),
            total_co2_4 = DataItem('Co2_4', total_co2_four, suffix='CO2', number_of_decimals=2)
        ) 
        return DataResult(data)

heyimsp · 2 July 2024 12:49

Hi @mslootweg ,

It prints None for all.

Total CO2 one None
Total CO2 two None
Total CO2 three None
Total CO2 four None

mslootweg · 2 July 2024 13:57

Hmmm… Let’s continue investigating. Could you print all values of the variable result.values?

Also, could you share the self.get_evaluated_spreadsheet logic?

heyimsp · 2 July 2024 14:08

I have updated my code to also read the contents of the excel using pandas and print it to the console:

def get_evaluated_spreadsheet(self, params):
        inputs = [
            SpreadsheetCalculationInput('project_name', params.project.pname),
            SpreadsheetCalculationInput('project_number', params.project.number),
            SpreadsheetCalculationInput('client', params.project.client),
            SpreadsheetCalculationInput('design_stage', params.project.design_stage),
            SpreadsheetCalculationInput('beam_length', params.geometryDefn.beamLength),
            SpreadsheetCalculationInput('bridge_deck_width', params.geometryDefn.deckWidth),
            SpreadsheetCalculationInput('max_construction_depth', params.geometryDefn.maxConstructionDepth),
            SpreadsheetCalculationInput('cantilever_allowed', params.geometryDefn.cantileverAllowed),
            SpreadsheetCalculationInput('max_cantilever_length', params.geometryDefn.maxCantileverLength),
            SpreadsheetCalculationInput('insitu_deck_slab_concrete_strength', params.deckParams.insituDeckSlab_concStrength),
            SpreadsheetCalculationInput('insitu_deck_slab_thickness', params.deckParams.insituDeckSlab_thickness),
            SpreadsheetCalculationInput('insitu_solid_slab_concrete_strength', params.deckParams.insituSolidSlab_concStrength),
            SpreadsheetCalculationInput('insitu_solid_slab_thickness', params.deckParams.insituSolidSlab_thickness)
        ]
        sheet_path = Path(__file__).parent / 'WSP_Net Zero_Prilim Beam Selection tool_v1.2.xlsx'
        sheet = SpreadsheetCalculation.from_path(sheet_path, inputs=inputs)
        result = sheet.evaluate(include_filled_file=True)

        read_excel_into_dataframe(sheet_path, 'viktor-output-sheet')

        return result

# Read excel into dataframe
def read_excel_into_dataframe(excel, sheet_name):
    df = pd.read_excel(excel, sheet_name=sheet_name)
    print(df)

This reads the contents of my excel and shows that my excel actually has values in the cell. However, viktor can’t read it yet.

2024-07-02 15:05:26.720 INFO    : App is ready
2024-07-02 15:05:29.607 INFO    : Job (uid: 922352) received - EntityType: Controller - call: parametrization
2024-07-02 15:05:29.613 INFO    : Job (uid: 922352) completed in 1ms
2024-07-02 15:05:29.934 INFO    : Job (uid: 922353) received - EntityType: Controller - call: get_data_view
        Parameters  Units  Comment        Values
0    total_co2_one    NaN      NaN  61629.162006
1    total_co2_two    NaN      NaN  61848.829986
2  total_co2_three    NaN      NaN  64044.540932
3   total_co2_four    NaN      NaN  64094.529649
Total CO2 one None
Total CO2 two None
Total CO2 three None
Total CO2 four None
2024-07-02 15:05:34.956 INFO    : Job (uid: 922353) completed in 5021ms

mslootweg · 3 July 2024 09:48

For me it is not yet clear what the issue is. I can, however, see that when printing your dataframe, it refers to the original sheet, and not the evaluated one. Could you change your code to read the evaluated Excel sheet and print that dataframe?

Maybe also double check it by downloading the evaluated Excel sheet.

heyimsp · 3 July 2024 13:33

How can I read data frame from the evaluated sheet? I have tried to read from the excel in dataframe after get_evaluated_spreadsheet method call. But it doesn’t seem like evaluated sheet has the updated inputs yet.

heyimsp · 3 July 2024 14:14

I have sort and filter formula applied in my excel table that doesn’t allow me to read the values in VIKTOR. When i change the value to read from another cell that contains basic arithmetic operations, it allows me to read the value.

Is sort and filter not supported?

mslootweg · 4 July 2024 07:16

To answer your question on how to read data from the evaluated sheet. You could extract the file_content and convert to a dataframe in the following way (basically just passing the file content as a BytesIO object in the argument where the sheet_path currently is fed):

excel_file_buffer = BytesIO(result.file_content)
df = pd.read_excel(excel_file_buffer, sheet_name=sheet_name)
print(df)

heyimsp · 4 July 2024 11:07

So I have updated the code slightly and noticed that as soon as viktor evaluates the spreadsheet, all of my results becomes NaN

Any way to solve this? Here’s my updated code and the log:

# Send inputs to the excel sheet
    def get_evaluated_spreadsheet(self, params):
        inputs = [
            SpreadsheetCalculationInput('project_name', params.project.pname),
            SpreadsheetCalculationInput('project_number', params.project.number),
            SpreadsheetCalculationInput('client', params.project.client),
            SpreadsheetCalculationInput('design_stage', params.project.design_stage),
            SpreadsheetCalculationInput('beam_length', params.geometryDefn.beamLength),
            SpreadsheetCalculationInput('bridge_deck_width', params.geometryDefn.deckWidth),
            SpreadsheetCalculationInput('max_construction_depth', params.geometryDefn.maxConstructionDepth),
            SpreadsheetCalculationInput('cantilever_allowed', params.geometryDefn.cantileverAllowed),
            SpreadsheetCalculationInput('max_cantilever_length', params.geometryDefn.maxCantileverLength),
            SpreadsheetCalculationInput('insitu_deck_slab_concrete_strength', params.deckParams.insituDeckSlab_concStrength),
            SpreadsheetCalculationInput('insitu_deck_slab_thickness', params.deckParams.insituDeckSlab_thickness),
            SpreadsheetCalculationInput('insitu_solid_slab_concrete_strength', params.deckParams.insituSolidSlab_concStrength),
            SpreadsheetCalculationInput('insitu_solid_slab_thickness', params.deckParams.insituSolidSlab_thickness)
        ]
        sheet_path = Path(__file__).parent / 'WSP_Net Zero_Prilim Beam Selection tool_v1.2.xlsx'
        sheet = SpreadsheetCalculation.from_path(sheet_path, inputs=inputs)
        result = sheet.evaluate(include_filled_file=True)

        raw_df = read_excel_into_dataframe(sheet_path, 'viktor-output-sheet')
        print('Raw DataFrame:')
        print(raw_df)

        excel_file_buffer = BytesIO(result.file_content)
        evaluated_df = pd.read_excel(excel_file_buffer, sheet_name='viktor-output-sheet')
        print('Evaluated DataFrame:')
        print(evaluated_df)

        return result

Log:

2024-07-04 12:03:40.777 INFO    : Job (uid: 924906) received - EntityType: Controller - call: get_optimum_beam_slab_result
Raw DataFrame:
               Parameters  Units  Comment                   Values
0                supplier    NaN      NaN                 Banagher
1        bridge_beam_type    NaN      NaN  TY Beams with Deck Slab
2         number_of_beams    NaN      NaN                        8
3            Beam_spacing    NaN      NaN                     1500
4               beam_type    NaN      NaN                      TY5
5     beam_concrete_grade    NaN      NaN                   C57/70
6               deck_type    NaN      NaN                     Slab
7     slab_concrete_grade    NaN      NaN     C40/50 - No addition
8   number_of_cantilevers    NaN      NaN                      NaN
9       cantilever_length    NaN      NaN                      NaN
10     construction_depth    NaN      NaN                      450
11     revised_deck_width    NaN      NaN                    12000
12                beam_id    NaN      NaN      08xTY5(C57/70)@1500
13              beam_eco2    NaN      NaN                7123.3128
14              slab_eco2    NaN      NaN             13660.704444
15      beam_eco2_percent    NaN      NaN                34.273032
16      slab_eco2_percent    NaN      NaN                65.726968
17             total_eco2    NaN      NaN             20784.017244
Evaluated DataFrame:
               Parameters  Units  Comment  Values
0                supplier    NaN      NaN     NaN
1        bridge_beam_type    NaN      NaN     NaN
2         number_of_beams    NaN      NaN     NaN
3            Beam_spacing    NaN      NaN     NaN
4               beam_type    NaN      NaN     NaN
5     beam_concrete_grade    NaN      NaN     NaN
6               deck_type    NaN      NaN     NaN
7     slab_concrete_grade    NaN      NaN     NaN
8   number_of_cantilevers    NaN      NaN     NaN
9       cantilever_length    NaN      NaN     NaN
10     construction_depth    NaN      NaN     NaN
11     revised_deck_width    NaN      NaN     NaN
12                beam_id    NaN      NaN     NaN
13              beam_eco2    NaN      NaN     NaN
14              slab_eco2    NaN      NaN     NaN
15      beam_eco2_percent    NaN      NaN     NaN
16      slab_eco2_percent    NaN      NaN     NaN
17             total_eco2    NaN      NaN     NaN
2024-07-04 12:03:53.797 INFO    : Job (uid: 924906) completed in 13018ms

Wichard · 4 July 2024 11:26

Hi,

This is similar to a problem we recently had with the ‘INDEX()’ formula. It was quite a rabbit hole, though: for us the problem turned out to be that the values (cells) the formula was supposed to work with, were numbers stored as text values, and apparently that resulted in None output values when running it on the ‘spreadsheet evaluation’ server [ I suspect some kind of libreoffice(?) → office difference]. After manually converting them to numbers, the sheet evaluation worked again.

Regards, Wichard

mslootweg · 4 July 2024 11:33

You are correct @Wichard , there seems to be some functions that are not yet working with the LibreOffice (which runs these calculations). This will be updated with the next release of LibreOffice:

https://wiki.documentfoundation.org/ReleaseNotes/24.8

heyimsp · 4 July 2024 11:45

@mslootweg @Wichard - Thank you for your response.

However, I’m stuck now. Is there any workaround for this?

mslootweg · 4 July 2024 15:06

Not the most elegant solution, but maybe you can for now use a more "primitive ways to recreate these functions such as:

SORT

=INDEX(A2:B10, MATCH(ROW(A2:A10)-ROW(A2)+1, COUNTIF(A2:A10, "<"&A2:A10)+1, 0), 1)

And FILTER

=IF(A2:A10>50, A2:A10, "")

Let me know if this would help.

khameeteman · 9 September 2024 15:58

Hi all, we have updated our service to use LibreOffice v24.8. This adds support for Excel array functions such as SORT, FILTER, XLOOKUP etc.