Hi everyone,
I have a lot of excel files with multiple sheets, and I would like to do some manipulation into the Sheets.
For example I would like to copy some row or column with paste value to remove the formulas. I need also to remove some sheets.
But I don’t know how to do this. Do you think It’s possible to manipulate excel sheets ?
Thank you a lot for helping.
Well yes that is possible. You could use the mighty Python library OpenPyXL. Yes it is some work but it is doable (you have a few links to examples attached).
Then there are the Continental Nodes for KNIME that deal mainly with formatting. They might be useful and possibly easier to handle than Python.
Python Excel Drop Down
Python Excel manipulation
Python Excel table object
Thank you for your helping. But unfortunatelly, I have any knowledge in programming with python.
To be honest I think it is not possible to do extensive manipulations in Excel from KNIME without Python. But the good news is, Python I think is not that hard to master to a point where you could do some useful things.
Also Python is becoming more and more popular so if you would invest a little effort you gain a lot of competence.
And you do not need to learn the whole of Python just as much as the typical data scientist would use.
Best way to learn a new programming language or software package might be to solve a real life problem. So this Excel challenge might be the perfect opportunity 
Thank you, I’m going to look for this solution.
I hope I can find what I need. I’m very frustrated because, I manage to do lot of work, but there is a last thing witch I can’t get it.
I just want to copy a Sheet in value without formulas and remove an another sheet.
Hmm you can import, export and overwrite Excel sheets from KNIME without any Python code. If you want to drop a sheet that is not so easily done. You could export all sheets you want to save to a new Excel file then delete the old one and rename the file with the sheets you want to keep.
For me it sounds like you will have to make some sort of plan what you want to do. Your original posting sounded like you wanted to edit some formulas maybe on Excel sheets or move data there; for that you would need Python.
Hello, hope I can help. You’re in luck, I enjoy python and happen to be learning a bit of it right now. However, i bet you could do all of it with KNIME.
I’m visual, need to break apart your sentence to help solve.
1. I want to copy a Sheet…
Have you considered calling the new source output the previous file and potentially forget about doing the remove? Maybe another way of thinking about it. Not sure the complete use case.
You will be surprised how easy python is when you don’t find the wrong blog post.
For example, I write about web scraping with python and before learning this, I ran into a few bad blogs…
So below, I’m linking you to w3school, where they have good code, good examples, and enough examples/code you need to do what you’ve requested.
Knime can package the python code OR be used to kick off the code. (im still learning, im sure there’s more)
Knime can help you build the data for your python service, it is great at building lists or arrays [ x ]
The python code you need:
#import the openpyxl library, google “how to pip install openpyxl”
import openpyxl
workbook=openpyxl.load_workbook(‘testdel.xlsx’)
workbook.get_sheet_names()
[‘Sheet1’, ‘Sheet2’, ‘Sheet3’]
std=workbook.get_sheet_by_name(‘Sheet2’)
workbook.remove_sheet(std)
workbook.get_sheet_names()
[‘Sheet1’, ‘Sheet3’]
workbook.save(‘testdel.xlsx’)
#byebye sheet2, byebye
I recommend you build a function with this, to make it easier for you, & once completed, you can turn it into a module and give it away here.
Functions are rather helpful. Here’s a demo of how you can build a function with the above code, to limit the amount of handholding you or your code will need. I like writing these because it makes my code easier to read or easier to teach/support.
Here’s an example of how I’m using variables + math.
def a(x,y):
___ return 1 + x + y
print ( “using return” )
print (a(0,0))
print (a(1,1))
print (a(2,2))
print (a(3,3))
def b(m):
___ print (1 + m)
print ( “using print” )
b(0)
b(1)
b(2)
b(3)
Goodluck mate.
This topic was automatically closed 182 days after the last reply. New replies are no longer allowed.
