Hi everyone,
I hope all of you well. I have some data that I get from reading the tag history value based on the time and that value I got as a list. So, I have used the ignition default function of the System. dataset.ToDatset() to convert that list into the dataset. My team has prepared a script to generate an Excel file using the Apache POI module. Then I pass the dataset to this function. When I run the script for the first time, it will generate and save that file in a specified location. Then I again generated another file that time I got an error. But the file has been generated but not opened and I can't delete that. When I close the client then I can able to delete that file. Can help me to resolve the error Please... I'll be given the given Apache POI script and error.
Code :
from java.io import ByteArrayOutputStream, FileOutputStream, File
from org.apache.poi.xssf.usermodel import XSSFWorkbook
from org.apache.poi.ss.usermodel import CellStyle, BorderStyle, FillPatternType, IndexedColors
workbook = XSSFWorkbook()
def Excel_data(dataset, Carrier_id,Function_Name):
# Define styles for the header and rows
def create_styles(workbook):
header_style = workbook.createCellStyle()
header_font = workbook.createFont()
header_font.setFontHeightInPoints(12)
header_style.setFont(header_font)
header_style.setBorderTop(BorderStyle.THIN)
header_style.setBorderBottom(BorderStyle.THIN)
header_style.setBorderLeft(BorderStyle.THIN)
header_style.setBorderRight(BorderStyle.THIN)
header_style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex())
header_style.setFillPattern(FillPatternType.SOLID_FOREGROUND)
row_style = workbook.createCellStyle()
row_font = workbook.createFont()
row_font.setFontHeightInPoints(12)
row_style.setFont(row_font)
row_style.setWrapText(True)
row_style.setBorderTop(BorderStyle.THIN)
row_style.setBorderBottom(BorderStyle.THIN)
row_style.setBorderLeft(BorderStyle.THIN)
row_style.setBorderRight(BorderStyle.THIN)
return header_style, row_style
header_style, row_style = create_styles(workbook)
# Function to add dataset to sheet
def add_dataset_to_sheet(workbook, sheet_name, dataset, header_style, row_style):
sheet = workbook.createSheet(sheet_name)
headers = dataset.getColumnNames()
# Create header row
header_row = sheet.createRow(0)
for col_num, header in enumerate(headers):
cell = header_row.createCell(col_num)
cell.setCellValue(header)
cell.setCellStyle(header_style)
# Create data rows
for row_num in range(dataset.getRowCount()):
row = sheet.createRow(row_num + 1)
for col_num, header in enumerate(headers):
cell_value = dataset.getValueAt(row_num, col_num)
cell = row.createCell(col_num)
cell.setCellValue(str(cell_value))
cell.setCellStyle(row_style)
row.setHeightInPoints(15)
for col_num in range(len(headers)):
max_width = 20 * 256
sheet.setColumnWidth(col_num, max_width)
sheet.autoSizeColumn(col_num, False)
sheet_name = str(Carrier_id)+"_"+Function_Name
add_dataset_to_sheet(workbook, sheet_name, dataset, header_style, row_style)
time = system.date.format(system.date.now(),"yyyy_MM_dd_hhmmdd")
filename = Function_Name+"_"+str(Carrier_id)+"_"+str(time)+".xlsx"
excel_file_path = "C:\Users\J2948974\Desktop\{}".format(file_name)
excel_file_path = system.file.saveFile(filename)
file_out = FileOutputStream(excel_file_path)
workbook.write(file_out)
file_out.close()
workbook.close()
Error :
Traceback (most recent call last):
File "<event:mouseClicked>", line 18, in <module>
File "<module:Digital_Twin.Cold_Coater.ProcessData_Calc>", line 210, in AVG_data
File "<module:Digital_Twin.Common.Apache_POI_Excel_Script>", line 79, in Excel_data
File "<module:Digital_Twin.Common.Apache_POI_Excel_Script>", line 79, in Excel_data
java.io.IOException: java.io.IOException: Cannot write data, document seems to have been closed already
caused by IOException: Cannot write data, document seems to have been closed already
Thanks & Regards ,
Muniyandi D