Manipulate tabs inside an excel

Hi experts, this is a beginner question since I couldn’t find this in Google and/or in the documentation. Can you let me know if KNIME has node(s) that can achieve below?

  1. Rename a specific tab inside an excel file
  2. Copy/duplicate a specific tab inside an excel file

Thanks for your help.
Best,
Tom

You can do several manipulations for Excel with the Python library openpyxl. 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 (57.1 KB)

Also the concept of Continental 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.

Example how to rename an existing Excel sheet

# https://openpyxl.readthedocs.io/en/stable/tutorial.html#loading-from-a-file

# conda install -c anaconda openpyxl

from openpyxl import load_workbook
import openpyxl
import os

var_data_path = flow_variables['v_path_data_folder']
print("var_data_path : ", var_data_path )

var_path_excel_file = var_data_path + os.path.sep + "my_data.xlsx"
print("var_path_excel_file : ", var_path_excel_file )

var_old_sheet_name = 'data'
var_new_sheet_name = 'data_new'

wb = load_workbook(var_path_excel_file)

ws = wb[var_old_sheet_name]

# activate the ws 'data'
for s in range(len(wb.sheetnames)):
    if wb.sheetnames[s] == ws:
        break
wb.active = s


v_list_sheetnames = wb.sheetnames
print(v_list_sheetnames)

for i in v_list_sheetnames:
	if i == var_new_sheet_name:
		# wb.active = i
		wb.remove_sheet(var_new_sheet_name)

ws.title = var_new_sheet_name

v_list_sheetnames = wb.sheetnames
print(v_list_sheetnames)

wb.save(var_path_excel_file)

output_table = input_table.copy()
2 Likes