Convert Dataset to Excel Using Apache POI module

Ignition
,
6

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