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)
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))
