OnFileReceived - Excel Workbook

Ignition
1

Is there something equivalent to this code for uploading a workbook from excel vs a .csv

fileContent = event.file.getString()
# Create a CSV reader
reader = csv.reader(io.StringIO(fileContent))

I want to be able to directly access a workbooks data in the onFileReceived event. I have a project library script that manipulates the data but the filepath must be passed manually to the function which is not ideal.

2

You can use the Apache POI libraries embedded into Ignition to do this. Take the event's file bytes, wrap with a ByteArrayInputStream and feed it to the POI library. You get an in-memory spreadsheet from which you can extract any arbitrary sheets and cell values (or iterate through them).

Start here:

1 Like
3

So would the function above be inside the onFileReceived action script? Then use the fileStream returned from the function within the script.

4

Or would I just define it in the project library and use it within the script onFileReceived? And pass the bytes of the file uploaded.

5

I almost always use one-liners in any kind of event to delegate to a project library script. Scattering code all over your project is a maintenance nightmare.

In case you haven't figured this out yet, project library scripts have no scope. They use the scope of the event or object that calls them.

2 Likes
6

Interesting, I did not know that, so one might as well define it in the Project Library for reusability.

1 Like
7

I was actually able to achieve what I wanted with:

def import_excel_data(byte_array):
    try:
        # Create a BytesIO object from the byte array
        bytes_io = BytesIO(byte_array)
            
        # Load the workbook using openpyxl
        workbook = openpyxl.load_workbook(bytes_io)
    
        # Get name of all the worksheets in the workbook
        worksheet_names = workbook.sheetnames

byte_array = event.file.getBytes()
MasterData.import_excel_data(byte_array)

Seems a bit simpler

8

But then you have to make sure openpyxl is installed and a compatible version. And you can suffer from jython libraries poor internationalization and date/time object support. Apache POI is supplied with Ignition. Picking the POI libraries over openpyxl is the obvious right answer.

9

Can you elaborate on making sure openpyxl is installed? Are you implying a client might not have it?

10

The implication being that openpyxl is not bundled with Ignition, so it must be added to Ignition's program files by the user making it a much less portable solution than using the built-in Apache POI lib.

If you update your current gateway or install a new one, openpyxl becomes a breaking point you can avoid by using Apache. I.E. it must work with Jython 2.7 or some future version and must be manually added to every gateway that needs it.

1 Like
11

Ok thanks that makes more sense. It must have come with my download of Ignition and is already in the program files?

12

Oh, you didn't install it manually? That is interesting...

1 Like
13

You didn't do this?

14

I personally did not but I did include:

from io import BytesIO
from openpyxl import load_workbook
import openpyxl

I did this all in my Project Library scripting..
It could have already been installed on the VM I am working on. What would the file location be?

15

what would the python source file be called in the pylib folder?

16

I see it. openpyxl folder in my pylib folder. Must have been installed by someone before me.

17

Yep, it must have been previosuly installed because it doesn't work on my 8.1.32 gateway.

2 Likes