Close a workbook with openpyxl

Ignition
,
1

Hello there!

as @zxcslo indicated in Copying a Excel file to a tables Dataset - #38 by zxcslo the openpyxl worked also for me.

But I am having issues when it comes to close or save the file. The code I am using is:

from openpyxl import Workbook

from openpyxl import load_workbook

import os

upload_path = “” #whatever
sheetName = “” #whatever

wb = load_workbook(upload_path)
ws = wb[sheetName]
cellValue = ws[“C6”]
wb.close() #necessary? alternatives?
self.getSibling(“Label”).props.text = cellValue.value

I got the cell value correctly, but then if I navigate to the folder and try to delete the file, I can´t.
I get the message from Windows: “action could not be completed because java.exe has the file opened”

Anyone has a clue? I do not know where the error comes from.

Thanks in advance,

2

The function you want to call seems to be wb.save($filepath):
https://openpyxl.readthedocs.io/en/stable/index.html

But I don’t know if the API is exactly the same on whatever version you have.

3

Dear @PGriffith ,

thanks for the quick response. I will try soon, but what “$” stands for?

Thanks again,

4

Sorry, that’s just meant as “placeholder” syntax. Use whatever filepath you actually want to save to as a string.

5

Hello,

I just tried with this code, and I got the cell value correctly showed in the label. However, I can´t still delete the file.

from openpyxl import Workbook

from openpyxl import load_workbook

import os

upload_path = “” #whatever
sheetName = “” #whatever

wb = load_workbook(upload_path)
ws = wb[sheetName]
cellValue = ws[“C6”]
wb.save(upload_path) #using both wb.save(upload_path) and wb.close() it does not work
self.getSibling(“Label”).props.text = cellValue.value

The “upload_path” variable is using the format: C:\Users\GENIOTIC\Desktop\PruebasIgnition\Filename.xlsx

Two more notes:

  • I also wanted to try with the pylight library, but I am stuck because I do not know exactly what files/folders I need to copy to the Ignition directory.

  • I am already using the function created by @JordanCClark Copying a Excel file to a tables Dataset - #13 by JordanCClark. But I am not able to find out how to get the value of a specific cell indicating the address (i.e. “C6”) instead of row and column numbers. I have been reading some of the Apache POI documentation and I think I need to create a cell reference but I still do not know how.

Thanks !!

6

Add this to your imports

import org.apache.poi.ss.util.CellReference as CellReference

Then you should be able to use something like:

ref = CellReference('C6')
refRow = sheet.getRow(ref.getRow())
cellValue = refRow.getCell(ref.getCol())
1 Like
7

Yep! That works, I was missing the correct syntax for the import sentence.

Thanks again @JordanCClark

1 Like