Convert Dataset to Excel Using Apache POI module

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

You can skip the workbook.close() at the end.

I should also say that the designer sometimes has a tendency to hang on to a file where a client doesn't. You didn't say where you were running your code, but I figured I would mention it.

1 Like

Hi @JordanCClark,

I have created a window to show details about some glass details. So, I have given one download icon on that. When I press that icon this will execute. One more thing I wrote that script in the Project library and I called that function Download icon Mousecliked event.

If I skip this line, I can able to generate multiple files, but those files contain the same values. I generated 3 different types of files but those files contain same values.
I'll be given the Excel image below.

Taking a closer look, the workbook definition needs to happen for every workbook you are creating. Try moving it to inside the Excel_data function.

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

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

	workbook = XSSFWorkbook()

	header_style, row_style = create_styles(workbook)

	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()
1 Like

Hi @JordanCClark,

Yeah it's a good idea, now it's working. Thank you sir...