# Manipulating excel Sheets **URL:** \ **Category:** KNIME Analytics Platform\ **Created:** [June 19, 2019, 8:22pm UTC](https://forum.knime.com/t/manipulating-excel-sheets/15541 "2019-06-19T20:22:44Z")\ **Posts on this page:** 9\ **Page:** 1
**Author:** ![Grayfox](https://forum-cdn.knime.com/letter_avatar_proxy/v4/letter/g/ecccb3/32.png) [@Grayfox](https://forum.knime.com/u/Grayfox)\ **Post date:** [June 19, 2019, 8:22pm UTC](https://forum.knime.com/t/manipulating-excel-sheets/15541/1 "2019-06-19T20:22:44Z")
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. ---
**Author:** ![mlauber71](https://forum-cdn.knime.com/user_avatar/forum.knime.com/mlauber71/32/73590_2.png) [@mlauber71](https://forum.knime.com/u/mlauber71)\ **Post date:** [June 19, 2019, 9:04pm UTC](https://forum.knime.com/t/manipulating-excel-sheets/15541/2 "2019-06-19T21:04:24Z")
Well yes that is possible. You could use the mighty Python library [OpenPyXL](https://openpyxl.readthedocs.io/en/stable/index.html). 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](https://www.knime.com/community/continental-nodes-for-knime-xls-formatter) that deal mainly with formatting. They might be useful and possibly easier to handle than Python. * * * Python Excel Drop Down > [@DataValidation Drop down list in Excel using Python Scrpt node](https://forum.knime.com/t/datavalidation-drop-down-list-in-excel-using-python-scrpt-node/12648/6): > > I attached a workflow that uses your example to create an Excel file via Python. And also one from the example from before. I think you have to provide the Python Script with a valid path (in this case I used the path of the workflow) and you have to close the file at the end. [image] [kn\_example\_python\_excel\_drop\_down.knwf](https://forum-cdn.knime.com/uploads/default/original/2X/3/3d87b7fc29d1017c801cafcd1bf67f72f7c49e9a.knwf) (21.4 KB) Python Excel manipulation > [@Manipulate tabs inside an excel](https://forum.knime.com/t/manipulate-tabs-inside-an-excel/14492/2): > > You can do several manipulations for Excel with the Python library [openpyxl](https://openpyxl.readthedocs.io/en/stable/). Please keep in mind that the syntax is not super easy and I encountered some quirks (they may or may not have to do with doing it on a Mac) [kn\_example\_python\_excel\_manipulate.knar](https://forum-cdn.knime.com/uploads/default/original/2X/b/b0418b772817b4efd0ff38c436215a9c7de0a38d.knar) (57.1 KB) Also the [concept of Continental](https://www.knime.com/blog/empower-your-own-experts-continental-wins-the-digital-leader-award) to spread the use of KNIME within the company is bearing some fruit, although I have not (yet) seen Excel Data manipulation nodes, but you already can do a lot of things with their nodes. … > [@write data to excel sheet on row 10](https://forum.knime.com/t/write-data-to-excel-sheet-on-row-10/12897/6): > > well … there is a way. You could use the Python module [openpyxl](https://openpyxl.readthedocs.io/en/stable/) and open the stored excel file in Python and do some manipulations and then save it again. Openpyxl offers a wide range of possibilities. Please be aware that this might not be the most stable way, and it could get tricky if you have large or complicated Excel files. [kn\_example\_python\_excel\_manipulate.knar](https://forum-cdn.knime.com/uploads/default/original/2X/2/2bd1b798f1fee3fa8d4d122c3b648bd5f8ab0de4.knar) (54.5 KB) Python Excel table object > [@Write KNIME table to Excel as table (proposal)](https://forum.knime.com/t/write-knime-table-to-excel-as-table-proposal/14044/10): > > OK I think now I get what you want. Yes it is possible to first save a table as a ‘pure’ excel file and then define a range as a table object “All” which you can later use to refer to it. In my example the no of lines that the object would have is assigned dynamically while the columns are fixed but one might also be able to change that to a dynamic setting. After the ‘raw’ KNIME export: After openpyxl got to work: (cf. [https://openpyxl.readthedocs.io/en/stable/…](https://openpyxl.readthedocs.io/en/stable/worksheet_tables.html) ---
**Author:** ![Grayfox](https://forum-cdn.knime.com/letter_avatar_proxy/v4/letter/g/ecccb3/32.png) [@Grayfox](https://forum.knime.com/u/Grayfox)\ **Post date:** [June 20, 2019, 9:02am UTC](https://forum.knime.com/t/manipulating-excel-sheets/15541/3 "2019-06-20T09:02:50Z")
Thank you for your helping. But unfortunatelly, I have any knowledge in programming with python. ---
**Author:** ![ipazin](https://forum-cdn.knime.com/user_avatar/forum.knime.com/ipazin/32/806_2.png) [@ipazin](https://forum.knime.com/u/ipazin)\ **Post date:** [June 21, 2019, 9:15am UTC](https://forum.knime.com/t/manipulating-excel-sheets/15541/4 "2019-06-21T09:15:56Z")
hi @Grayfox, then try out Continental nodes 😉 Br, Ivan ---
**Author:** ![mlauber71](https://forum-cdn.knime.com/user_avatar/forum.knime.com/mlauber71/32/73590_2.png) [@mlauber71](https://forum.knime.com/u/mlauber71)\ **Post date:** [June 21, 2019, 10:45am UTC](https://forum.knime.com/t/manipulating-excel-sheets/15541/5 "2019-06-21T10:45:26Z")
> [@Grayfox](#): > > 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 🙂 ---
**Author:** ![Grayfox](https://forum-cdn.knime.com/letter_avatar_proxy/v4/letter/g/ecccb3/32.png) [@Grayfox](https://forum.knime.com/u/Grayfox)\ **Post date:** [June 21, 2019, 7:15pm UTC](https://forum.knime.com/t/manipulating-excel-sheets/15541/6 "2019-06-21T19:15:48Z")
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. ---
**Author:** ![mlauber71](https://forum-cdn.knime.com/user_avatar/forum.knime.com/mlauber71/32/73590_2.png) [@mlauber71](https://forum.knime.com/u/mlauber71)\ **Post date:** [June 22, 2019, 10:49am UTC](https://forum.knime.com/t/manipulating-excel-sheets/15541/7 "2019-06-22T10:49:50Z")
> [@Grayfox](#): > > 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. ---
**Author:** ![Tyler](https://forum-cdn.knime.com/user_avatar/forum.knime.com/tyler/32/5220_2.png) [@Tyler](https://forum.knime.com/u/Tyler)\ **Post date:** [June 22, 2019, 12:47pm UTC](https://forum.knime.com/t/manipulating-excel-sheets/15541/8 "2019-06-22T12:47:58Z")
> [@Grayfox](#): > > I just want to copy a Sheet in value without formulas and remove an another sheet. 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…** ![25%20AM](https://forum-cdn.knime.com/uploads/default/original/2X/c/ccce787692d8e99e4254302bd0d4cb789df1c29f.png) With two nodes, I’m doing 1,2,3. Notice I write a new file, leaving the previous sheet to stay VS deleting it. **2. in value without formulas…** Screenshot above shows knime native does not take the =C8\*2 formula. and copies only values. **3. and remove an another sheet.** Most tools like KNIME query source systems, files, and LOAD that information to a target destination. Deleting content is not typical. 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](https://medium.com/@itylergarrett.tag/learning-web-scraping-with-python-requests-beautifulsoup-936e6445312) 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](https://www.w3schools.com/python/python_arrays.asp) [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](https://www.w3schools.com/python/python_functions.asp) with this, to make it easier for you, & once completed, you can turn it into a [module](https://www.w3schools.com/python/python_modules.asp) 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. ---
**Author:** ![system](https://forum-cdn.knime.com/uploads/default/original/1X/ab3ccf34482a0329361734a18199390177204f15.png) [@system](https://forum.knime.com/u/system)\ **Post date:** [December 22, 2019, 12:57am UTC](https://forum.knime.com/t/manipulating-excel-sheets/15541/9 "2019-12-22T00:57:40Z")
This topic was automatically closed 182 days after the last reply. New replies are no longer allowed.