# OnFileReceived - Excel Workbook

**URL:** <https://forum.inductiveautomation.com/t/onfilereceived-excel-workbook/83319>\
**Category:** Ignition\
**Created:** [December 19, 2023, 4:23pm UTC](https://forum.inductiveautomation.com/t/onfilereceived-excel-workbook/83319 "2023-12-19T16:23:08Z")\
**Posts on this page:** 17\
**Page:** 1

<div class="post-metadata">

**Author:** ![HelloWorld](https://avatars.discourse-cdn.com/v4/letter/h/b782af/32.png) [@HelloWorld](https://forum.inductiveautomation.com/u/HelloWorld)\
**Post date:** [December 19, 2023, 4:23pm UTC](https://forum.inductiveautomation.com/t/onfilereceived-excel-workbook/83319/1 "2023-12-19T16:23:08Z")

</div>

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

```auto
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.

---

<div class="post-metadata">

**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\
**Post date:** [December 19, 2023, 4:27pm UTC](https://forum.inductiveautomation.com/t/onfilereceived-excel-workbook/83319/2 "2023-12-19T16:27:03Z")

</div>

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:

> [@Copying an Excel file to a table's dataset](https://forum.inductiveautomation.com/t/copying-a-excel-file-to-a-tables-dataset/34942/18):
>
> Untested, but you should just be able to use ByteArrayInputStream instead. def excelBytesToDataSet(bytesIn, hasHeaders = False): import org.apache.poi.ss.usermodel.WorkbookFactory as WorkbookFactory import org.apache.poi.ss.usermodel.DateUtil as DateUtil from java.io import ByteArrayInputStream fileStream = ByteArrayInputStream(bytesIn) ...

---

<div class="post-metadata">

**Author:** ![HelloWorld](https://avatars.discourse-cdn.com/v4/letter/h/b782af/32.png) [@HelloWorld](https://forum.inductiveautomation.com/u/HelloWorld)\
**Post date:** [December 19, 2023, 5:14pm UTC](https://forum.inductiveautomation.com/t/onfilereceived-excel-workbook/83319/3 "2023-12-19T17:14:29Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![HelloWorld](https://avatars.discourse-cdn.com/v4/letter/h/b782af/32.png) [@HelloWorld](https://forum.inductiveautomation.com/u/HelloWorld)\
**Post date:** [December 19, 2023, 5:15pm UTC](https://forum.inductiveautomation.com/t/onfilereceived-excel-workbook/83319/4 "2023-12-19T17:15:27Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\
**Post date:** [December 19, 2023, 5:21pm UTC](https://forum.inductiveautomation.com/t/onfilereceived-excel-workbook/83319/5 "2023-12-19T17:21:51Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![HelloWorld](https://avatars.discourse-cdn.com/v4/letter/h/b782af/32.png) [@HelloWorld](https://forum.inductiveautomation.com/u/HelloWorld)\
**Post date:** [December 19, 2023, 5:23pm UTC](https://forum.inductiveautomation.com/t/onfilereceived-excel-workbook/83319/6 "2023-12-19T17:23:04Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![HelloWorld](https://avatars.discourse-cdn.com/v4/letter/h/b782af/32.png) [@HelloWorld](https://forum.inductiveautomation.com/u/HelloWorld)\
**Post date:** [December 19, 2023, 6:59pm UTC](https://forum.inductiveautomation.com/t/onfilereceived-excel-workbook/83319/7 "2023-12-19T18:59:08Z")

</div>

I was actually able to achieve what I wanted with:

```auto
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

```

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

```

Seems a bit simpler

---

<div class="post-metadata">

**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\
**Post date:** [December 19, 2023, 7:03pm UTC](https://forum.inductiveautomation.com/t/onfilereceived-excel-workbook/83319/8 "2023-12-19T19:03:50Z")

</div>

> [@HelloWorld](#):
>
> Seems a bit simpler

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.

---

<div class="post-metadata">

**Author:** ![HelloWorld](https://avatars.discourse-cdn.com/v4/letter/h/b782af/32.png) [@HelloWorld](https://forum.inductiveautomation.com/u/HelloWorld)\
**Post date:** [December 19, 2023, 7:11pm UTC](https://forum.inductiveautomation.com/t/onfilereceived-excel-workbook/83319/9 "2023-12-19T19:11:44Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![dkhayes117](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/dkhayes117/32/68344_2.png) [@dkhayes117](https://forum.inductiveautomation.com/u/dkhayes117)\
**Post date:** [December 19, 2023, 7:39pm UTC](https://forum.inductiveautomation.com/t/onfilereceived-excel-workbook/83319/10 "2023-12-19T19:39:27Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![HelloWorld](https://avatars.discourse-cdn.com/v4/letter/h/b782af/32.png) [@HelloWorld](https://forum.inductiveautomation.com/u/HelloWorld)\
**Post date:** [December 19, 2023, 7:42pm UTC](https://forum.inductiveautomation.com/t/onfilereceived-excel-workbook/83319/11 "2023-12-19T19:42:16Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![dkhayes117](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/dkhayes117/32/68344_2.png) [@dkhayes117](https://forum.inductiveautomation.com/u/dkhayes117)\
**Post date:** [December 19, 2023, 7:42pm UTC](https://forum.inductiveautomation.com/t/onfilereceived-excel-workbook/83319/12 "2023-12-19T19:42:44Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![dkhayes117](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/dkhayes117/32/68344_2.png) [@dkhayes117](https://forum.inductiveautomation.com/u/dkhayes117)\
**Post date:** [December 19, 2023, 7:48pm UTC](https://forum.inductiveautomation.com/t/onfilereceived-excel-workbook/83319/13 "2023-12-19T19:48:51Z")

</div>

You didn't do this?

> [@Copying a Excel file to a tables Dataset](https://forum.inductiveautomation.com/t/copying-a-excel-file-to-a-tables-dataset/34942/38):
>
> I just tried the `openpyxl` library and it works in Ignition 8.1.  
> Version 2.6.4 is the last one to support Python 2.7.  
> If anyone wants to try…  
> [openpyxl-2.6.4.zip](https://forum.inductiveautomation.com/uploads/short-url/jo7uXmP9CZYEjBWW3OVdiT9TPtv.zip) (1.1 MB)
> 
> Just copy everything in the .zip file into the `C:\Program Files\Inductive Automation\Ignition\user-lib\pylib` folder under Windows.
> 
> [https://openpyxl.readthedocs.io/en/stable/](https://openpyxl.readthedocs.io/en/stable/)

---

<div class="post-metadata">

**Author:** ![HelloWorld](https://avatars.discourse-cdn.com/v4/letter/h/b782af/32.png) [@HelloWorld](https://forum.inductiveautomation.com/u/HelloWorld)\
**Post date:** [December 19, 2023, 7:50pm UTC](https://forum.inductiveautomation.com/t/onfilereceived-excel-workbook/83319/14 "2023-12-19T19:50:40Z")

</div>

I personally did not but I did include:

```auto
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?

---

<div class="post-metadata">

**Author:** ![HelloWorld](https://avatars.discourse-cdn.com/v4/letter/h/b782af/32.png) [@HelloWorld](https://forum.inductiveautomation.com/u/HelloWorld)\
**Post date:** [December 19, 2023, 7:52pm UTC](https://forum.inductiveautomation.com/t/onfilereceived-excel-workbook/83319/15 "2023-12-19T19:52:23Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![HelloWorld](https://avatars.discourse-cdn.com/v4/letter/h/b782af/32.png) [@HelloWorld](https://forum.inductiveautomation.com/u/HelloWorld)\
**Post date:** [December 19, 2023, 7:53pm UTC](https://forum.inductiveautomation.com/t/onfilereceived-excel-workbook/83319/16 "2023-12-19T19:53:33Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![dkhayes117](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/dkhayes117/32/68344_2.png) [@dkhayes117](https://forum.inductiveautomation.com/u/dkhayes117)\
**Post date:** [December 19, 2023, 7:55pm UTC](https://forum.inductiveautomation.com/t/onfilereceived-excel-workbook/83319/17 "2023-12-19T19:55:10Z")

</div>

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