# Convert Dataset to Excel Using Apache POI module

**URL:** <https://forum.inductiveautomation.com/t/convert-dataset-to-excel-using-apache-poi-module/92351>\
**Category:** Ignition\
**Tags:** ignition81, vision\
**Created:** [June 25, 2024, 5:37pm UTC](https://forum.inductiveautomation.com/t/convert-dataset-to-excel-using-apache-poi-module/92351 "2024-06-25T17:37:26Z")\
**Posts on this page:** 7\
**Page:** 1

<div class="post-metadata">

**Author:** ![MUNIYANDI\_D](https://avatars.discourse-cdn.com/v4/letter/m/48db29/32.png) [@MUNIYANDI\_D](https://forum.inductiveautomation.com/u/MUNIYANDI_D)\
**Post date:** [June 25, 2024, 5:37pm UTC](https://forum.inductiveautomation.com/t/convert-dataset-to-excel-using-apache-poi-module/92351/1 "2024-06-25T17:37:26Z")

</div>

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 :**

```python
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 :**

```python
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

---

<div class="post-metadata">

**Author:** ![JordanCClark](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jordancclark/32/83362_2.png) [@JordanCClark](https://forum.inductiveautomation.com/u/JordanCClark)\
**Post date:** [June 25, 2024, 7:51pm UTC](https://forum.inductiveautomation.com/t/convert-dataset-to-excel-using-apache-poi-module/92351/2 "2024-06-25T19:51:08Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![JordanCClark](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jordancclark/32/83362_2.png) [@JordanCClark](https://forum.inductiveautomation.com/u/JordanCClark)\
**Post date:** [June 25, 2024, 7:59pm UTC](https://forum.inductiveautomation.com/t/convert-dataset-to-excel-using-apache-poi-module/92351/3 "2024-06-25T19:59:35Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![MUNIYANDI\_D](https://avatars.discourse-cdn.com/v4/letter/m/48db29/32.png) [@MUNIYANDI\_D](https://forum.inductiveautomation.com/u/MUNIYANDI_D)\
**Post date:** [June 26, 2024, 3:41am UTC](https://forum.inductiveautomation.com/t/convert-dataset-to-excel-using-apache-poi-module/92351/4 "2024-06-26T03:41:00Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![MUNIYANDI\_D](https://avatars.discourse-cdn.com/v4/letter/m/48db29/32.png) [@MUNIYANDI\_D](https://forum.inductiveautomation.com/u/MUNIYANDI_D)\
**Post date:** [June 26, 2024, 3:57am UTC](https://forum.inductiveautomation.com/t/convert-dataset-to-excel-using-apache-poi-module/92351/5 "2024-06-26T03:57:12Z")

</div>

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.

 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/1/2/12badb34f3561e3455c4a87bfc7791077ed39d22.png)

---

<div class="post-metadata">

**Author:** ![JordanCClark](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jordancclark/32/83362_2.png) [@JordanCClark](https://forum.inductiveautomation.com/u/JordanCClark)\
**Post date:** [June 26, 2024, 1:09pm UTC](https://forum.inductiveautomation.com/t/convert-dataset-to-excel-using-apache-poi-module/92351/6 "2024-06-26T13:09:32Z")

</div>

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.

```python
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()

```

---

<div class="post-metadata">

**Author:** ![MUNIYANDI\_D](https://avatars.discourse-cdn.com/v4/letter/m/48db29/32.png) [@MUNIYANDI\_D](https://forum.inductiveautomation.com/u/MUNIYANDI_D)\
**Post date:** [June 26, 2024, 2:29pm UTC](https://forum.inductiveautomation.com/t/convert-dataset-to-excel-using-apache-poi-module/92351/7 "2024-06-26T14:29:03Z")

</div>

Hi @JordanCClark,

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