Script Keeps Writing Over Old Data

I have a script where the goal is to input the new data into an Excel spreadsheet. The script keeps writing over the old data instead of appending to the sheet. Anyone know what the issue is with this script?

# Import necessary modules
from org.apache.poi.ss.usermodel import WorkbookFactory
from org.apache.poi.xssf.usermodel import XSSFWorkbook
from java.io import FileInputStream, FileOutputStream, File
from system.file import writeFile

# Define the file path where you want to save the Excel file
filePath = "PATH TO FILE"

# Define a function to append data to Excel
def appendToExcel(event):
    try:
        # Get the selected text values from the dropdowns
        value1 = event.source.parent.getComponent('SawSelector').selectedStringValue
        value2 = event.source.parent.getComponent('TypeSelector').selectedStringValue
        value3 = event.source.parent.getComponent('BrandSelector').selectedStringValue
        value4 = event.source.parent.getComponent('ReasonSelector').selectedStringValue

        # Get the current date and time
        value5 = system.date.now()

        # Open the existing Excel file for reading
        fileInputStream = FileInputStream(File(filePath))
        workbook = WorkbookFactory.create(fileInputStream)
        sheet = workbook.getSheetAt(0)  # Assuming you're working with the first sheet

        # Find the next available row index
        nextRowIndex = sheet.getLastRowNum() + 1

        # Create a new row for the new data
        newRow = sheet.createRow(nextRowIndex)
        newRow.createCell(0).setCellValue(value1)
        newRow.createCell(1).setCellValue(value2)
        newRow.createCell(2).setCellValue(value3)
        newRow.createCell(3).setCellValue(value4)
        newRow.createCell(4).setCellValue(value5)

        # Save the changes to the existing Excel file without prompting the user
        fileInputStream.close()
        tempFilePath = filePath + ".tmp"
        fileOutputStream = FileOutputStream(File(tempFilePath))
        workbook.write(fileOutputStream)
        fileOutputStream.close()

        # Move the temporary file to replace the original file
        File(tempFilePath).renameTo(File(filePath))

    except Exception as e:
        # Handle any exceptions here
        print("Error:", str(e))

# Bind the button's action event to the appendToExcel function
event.source.parent.getComponent('Button 1').setActionCommand("append")
event.source.parent.getComponent('Button 1').addActionListener(appendToExcel)

Are you certain it's overwriting old data? renameTo() will not work if the destination file already exists.

Hi Jordan,

I've come up with a different solution that does not rewrite over any old data. The destination file did already exist so I see that the renameTo() wasn't going to work with how I coded it.

Here is my new code:

# Import necessary modules
from org.apache.poi.ss.usermodel import WorkbookFactory
from org.apache.poi.ss.usermodel import Row
from java.io import FileInputStream, FileOutputStream
from java.io import File

# Define the file path where you want to save the Excel file
filePath = "PATH TO FILE"

# Define a function to export data to Excel
def exportToExcel(event):
    try:
        # Get the selected text values from the dropdowns
        value1 = event.source.parent.getComponent('SawSelector').selectedStringValue
        value2 = event.source.parent.getComponent('TypeSelector').selectedStringValue
        value3 = event.source.parent.getComponent('BrandSelector').selectedStringValue
        value4 = event.source.parent.getComponent('ReasonSelector').selectedStringValue

        # Get the current date and time
        value5 = system.date.now()

        # Open the Excel file for reading
        fileInputStream = FileInputStream(File(filePath))
        workbook = WorkbookFactory.create(fileInputStream)
        sheet = workbook.getSheetAt(0)  # Assuming you're working with the first sheet

        # Create a new row
        newRow = sheet.createRow(sheet.getLastRowNum() + 1)

        # Set cell values
        newRow.createCell(0).setCellValue(value1)
        newRow.createCell(1).setCellValue(value2)
        newRow.createCell(2).setCellValue(value3)
        newRow.createCell(3).setCellValue(value4)
        newRow.createCell(4).setCellValue(value5)

        # Save the changes to the Excel file
        fileOutputStream = FileOutputStream(File(filePath))
        workbook.write(fileOutputStream)
        fileOutputStream.close()

    except Exception as e:
        # Handle any exceptions here
        print("Error:", str(e))

# Bind the button's action event to the exportToExcel function
event.source.parent.getComponent('Button 1').setActionCommand("export")
event.source.parent.getComponent('Button 1').addActionListener(exportToExcel)

Now it is working except when I press the button in Ignition once, it saves the data in the drop down to the Excel sheet way too many times. Is the issue in the try block?

I'd say it's in trying to add an extra listener to the button.

I suggest putting the function into a project script and calling it from the button event. That will also make it reusable.

Example: if I make a project script called project, then put this slightly modified version of your function in it.

from org.apache.poi.ss.usermodel import WorkbookFactory
from org.apache.poi.ss.usermodel import Row
from java.io import FileInputStream, FileOutputStream
from java.io import File

def exportToExcel(filePath, values):
    logger = system.util.getLogger('exportToExcel')
    try:
        # Open the Excel file for reading
        fileInputStream = FileInputStream(File(filePath))
        workbook = WorkbookFactory.create(fileInputStream)
        sheet = workbook.getSheetAt(0)  # Assuming you're working with the first sheet

        # Create a new row
        newRow = sheet.createRow(sheet.getLastRowNum() + 1)

        # Set cell values        
        for i, value in enumerate(values):               
            newRow.createCell(i).setCellValue(value)

        # Save the changes to the Excel file
        fileOutputStream = FileOutputStream(File(filePath))
        workbook.write(fileOutputStream)
        fileOutputStream.close()
        logger.info('export to file {} successful'.format(filePath))

    except Exception as e:
        logger.warn('Error: {}').format(str(e))

Then, in the button's action performed script:

fileName = '/path/to/the/file.xlsx'
values = [
          event.source.parent.getComponent('SawSelector').selectedStringValue,
          event.source.parent.getComponent('TypeSelector').selectedStringValue,
          event.source.parent.getComponent('BrandSelector').selectedStringValue,
          event.source.parent.getComponent('ReasonSelector').selectedStringValue,
          system.date.now()
         ]

project.exportToExcel(fileName, values)
4 Likes

I'm going to try this out! Thanks for your help so far. I'll let you know how it turns out.

Hey Jordan,
I took your suggestion but am still having the issue of the data logging multiple times. And ideas as to what could be causing this?

Sounds like a side effect of improperly using a propertyChange event instead of the button's actionPerformed event.

1 Like

I have the button script in the actionPerformed event... any other ideas?
image

Try it on a fresh button, if you're still using the original.

Suspicion being that added action listener still getting triggered right?

Yep.

That did the trick! Thanks again!

1 Like