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?
- Rename a specific tab inside an excel file
- 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