Manipulate tabs inside an excel

KNIME Analytics Platform
2

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)

image
image1488×830 122 KB

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