Errors when wrangling Spreadsheet through python script

[Gabriel Gustafsson]

Hello all,

I'm new to DWSIM, so I have probably misused the software somehow. But, anyhow, I run into trouble when i want to format a worksheet.

The original worksheet was pulling and summarising some stream data. But I can reproduce the errors using the example below.

The first error I run into is when i want to set the DataFormat of some cells. The program lets me do it, but if I try to save the simulation after that it crashes, with a long error message.

The second issue I have is when I try to resize the columns to fit. The script then terminates prematurely with a short error message.

import clr
clr.AddReference('unvell.ReoGrid')

from unvell.ReoGrid import DataFormat

ws = Spreadsheet.GetWorksheetByName('Example')
if ws is None:
    ws = Spreadsheet.NewWorksheet('Example')
ws.Reset()

ws.Cells[1,0].Data = "Example header name"
ws.Cells[1,1].Data = "Second example header name"

# First Error
#ws.SetRangeDataFormat("B3:E18",DataFormat.CellDataFormatFlag.Number)

# Second error
#ws.AutoFitColumnWidth(0)
#ws.AutoFitColumnWidth(1)

""
Second short error message printed to prompt:
6 2022-04-07 15:30:27 Error Error running script '': Traceback (most recent call last):
File "<string>", line 21, in <module>
SystemError: Cross-thread operation not valid: Control '' accessed from a thread other than the thread it was created on.
""
The first, longer error message is attached. </module></string>

[Gabriel Gustafsson]

Has anyone else tried to do the same thing as me?

If yes, is there a more correct way of doing this?

[Daniel Medeiros]

These commands must run from a UI thread. try encapsulating them in a function and then do something this:

def myfunc(w):
    w.SetRangeDataFormat("B3:E18",DataFormat.CellDataFormatFlag.Number)
    w.AutoFitColumnWidth(0)
    w.AutoFitColumnWidth(1)

Flowsheet.RunCodeOnUIThread(lambda: myfunc(ws))

[Gabriel Gustafsson]

Cool!
Thanks for the reply!

That construction lets me do the

w.AutoFitColumnWidth(0)
w.AutoFitColumnWidth(1)

without any error or interruption.

However if i include the

w.SetRangeDataFormat("B3:E18",DataFormat.CellDataFormatFlag.Number)

and then attempt to save the simulation after running it still throws an exception with the same error message that i attached above.

"System.NullReferenceException: Object reference not set to an instance of an object."

My current theory is that maybe I have imported DataFormat from the wrong location?

Many thanks for the assistance!

[Gabriel Gustafsson]

I found the error,

I had omitted to provide the dataformat arguments. For some reason it allows me to format using some default arguments then. But to be able to save the flowsheet afterwards it needs them explicitly apparently.

I.e to solve my problem I added two lines under the imports like this:

from unvell.ReoGrid import DataFormat

formatflag = DataFormat.CellDataFormatFlag(1)
number_args = DataFormat.NumberDataFormatter.NumberFormatArgs()

and then corrected the SetRangeDataFormat call to this:

ws.SetRangeDataFormat("B3:E18",formatflag,number_args)

My other error was solved by using Daniels construction above.

Yay!