I just want to ask if you have other ways on applying the expression on excel through Knime? I know we can already use the Math formula node or Column expression Node for this but our client wants to see the expression used in calculating the data. Here`s my way of doing this, I used String replacer node to write the formula in the cells but I think this is not a practical solution because I need to do the expression repeatedly for every cell. I can use some tricks here but its not practical. If you have any solutions, suggestions or ideas, it will be a great help
Thank you for the information:blush:
But after installing the Openpyxl package through AnacondaPrompt, the Python Script Node is still error. It says that there`s no openpyxl module. Is there any other things that I need to set up or install? Sorry but I am new to this field
I think I installed the Openpyxl correctly because there’s no error on the Python Script node when I am executing the line. I always get an error on the xlsxwriter. I used the Jupyter to create the .py file but when I am installing it on the py36_knime environment through Anaconda Prompt, it always says that there’s no available channel for the xlsxwriter. Do I need to install something or change the file type of xlswriter? And also, after fixing this problem, I want to append the excel output of the python script node to the other excel output using excel sheet appender node. I have an example output that I want to achieve.
Or you convince the client to drop the request or you simply say how costly it is (obviously very costly because it’s complicated to do which you see is true.
they might want the formulas so that they can d it themselves next time just another reason to charge generously. But then who am I? Not a consultant but that’s what i would to for questionable requests.
Ok. Then copy Excel file, format columns that contain formulas as text (this still shows values in my Excel version - maybe newer one automatically display formula so you are ready for KNIME here), if not double-click (or F2) on first row to show formula, click enter and drag it down till the column end. After importing into KNIME continue with manipulation according to customer requirements…
It needs to do a calculation. And the very hard part of this is there’s already the calculated columns that I need to put the expressions. Actually, the formula/expression that I need to put is very simple. It’s just the way on how to do it. Worst case scenario is to add new sheet for the calculation, but it’s also very tough task. Currently I am able to write the expression on a new excel sheet. After figuring out how to apply this expression on multi rows and columns, I still need to figure out how to append this on an existing excel file. Because for I now, I can just write an expression on just one cell I apologize for my lack of knowledge on these things. But I really appreciate your help and suggestions. Thank you very much.
Now, I am able to write the expression on the excel but on a new file. I am still figuring it out on how to write expression on an existing file. Maybe the file path setting is the one that I need to solve. Thank you very much for your help
I am reading this link now
That’s a good idea. The problem on this is I need to do this on an existing columns with calculated values. If I do this, it will overwrite the existing values. Unless I do this on different sheet. Also, if this works, I noticed that I still need to double click the cells for the expressions to become ok. Because at first it will show the strings you wrote, but after double clicking the cell, it will be ok and I don’t know the problem with this But thank you for this, It adds to my solutions
I have created another Example since the last one was a mixture of Openpyxl and xlsxwriter now just the first one.
You could add formulas just like that:
ws['B2'] = "=SUM(A1:A100)"
The thing is you have to load the Excel file in Python, load the Sheet (or create a new one) and then add the formulas. If you want to add a formula into a range of cells you would have to write additional Python code.
I made a sample Knime workflow earlier. It’s very simple but the problem is when I open the output file, I still need to double click every cell to show the value. Do you have any opinion regarding this? Thank you