# Export dataset and operator name to excel in vision

**URL:** <https://forum.inductiveautomation.com/t/export-dataset-and-operator-name-to-excel-in-vision/73828>\
**Category:** Ignition\
**Tags:** ignition81, vision, scripting, database\
**Created:** [May 17, 2023, 7:06am UTC](https://forum.inductiveautomation.com/t/export-dataset-and-operator-name-to-excel-in-vision/73828 "2023-05-17T07:06:58Z")\
**Posts on this page:** 9\
**Page:** 1

<div class="post-metadata">

**Author:** ![Priyanka.Khandge](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/priyanka.khandge/32/35989_2.png) [@Priyanka.Khandge](https://forum.inductiveautomation.com/u/Priyanka.Khandge)\
**Post date:** [May 17, 2023, 7:06am UTC](https://forum.inductiveautomation.com/t/export-dataset-and-operator-name-to-excel-in-vision/73828/1 "2023-05-17T07:06:59Z")

</div>

Hi guys,

I have tried to export the dataset with the operator name in Excel. export dataset successfully using the below script but how to export the operator name in cell **A1** any idea?

```auto
def datasetToExcel(dsIn,fileName = '',sheetName = 'Sheet1',firstRow = 0, firstCol = 0,dateFormat = 'yyyy-mm-dd hh:mm:ss'):
	import org.apache.poi.ss.usermodel.Cell as Cell
	import org.apache.poi.ss.usermodel.Row as Row
	import org.apache.poi.ss.usermodel.Sheet as Sheet
	import org.apache.poi.ss.usermodel.Workbook as Workbook
	import org.apache.poi.xssf.usermodel.XSSFWorkbook as XSSFWorkbook
	import org.apache.poi.xssf.usermodel.XSSFDataFormat as XSSFDataFormat
	from java.io import FileOutputStream, ByteArrayOutputStream
	dsType = str(type(dsIn))

	if 'com.inductiveautomation.ignition.common.BasicDataset' in dsType:
		pyDS = system.dataset.toPyDataSet(dsIn)
	elif 'com.inductiveautomation.ignition.common.JsonDataset' in dsType:
		pyDS = system.dataset.toPyDataSet(dsIn)
	elif 'com.inductiveautomation.ignition.common.script.builtin.DatasetUtilities$PyDataSet' in dsType:
		pyDS = dsIn
	else:
			raise Exception('Not a valid DataSet')
	
	if fileName == '':
		output = ByteArrayOutputStream()
	else:
		output = FileOutputStream(fileName)
	# Create workbook
	wb = XSSFWorkbook()

	# Create Sheet
	sheet = wb.createSheet(sheetName)

	# Create formatter
	fmt = wb.createDataFormat()

	workbook = xlsxwriter.Workbook('hello.xlsx')
	worksheet = workbook.add_worksheet()
	
	worksheet.write('A1', 'Hello world')

	# Create style for headers
	headerStyle = wb.createCellStyle()
	headerFont = wb.createFont()
	headerFont.setBold(True)
	headerFont.setFontHeightInPoints(10)
	headerFont.setFontName('Arial')
	headerStyle.setFont(headerFont)

	# Create style for data
	rowStyle = wb.createCellStyle()
	rowFont = wb.createFont()
	rowFont.setBold(False)
	rowFont.setFontHeightInPoints(10)
	rowFont.setFontName('Arial')
	rowStyle.setFont(rowFont)

	# Create style for dates.
	dateStyle = wb.createCellStyle()
	dateFont = wb.createFont()
	dateFont.setBold(False)
	dateFont.setFontHeightInPoints(10)
	dateFont.setFontName('Arial')
	dateStyle.setFont(dateFont)
	dateStyle.setDataFormat(fmt.getFormat(dateFormat.lower()))

	# Create header row in the sheet
	headerRow = sheet.createRow(firstRow)
	for j, col in enumerate(pyDS.getColumnNames()):
		cell = headerRow.createCell(j+firstCol)
		cell.setCellStyle(headerStyle)
		cell.setCellValue(col)
	# Create data rows	
	for i, row in enumerate(pyDS):
		dataRow = sheet.createRow(i+1+firstRow)
		for j, col in enumerate(list(row)):
			cell = dataRow.createCell(j+firstCol)
			cell.setCellValue(col)
			cell.setCellStyle(rowStyle)
			# Check if it's a date, and set cell format accordingly 
			if 'java.util.Date' in str(type(col)):
				cell.setCellStyle(dateStyle)
	

	# Resize the columns		
	for i in range(pyDS.getColumnCount()):
		sheet.autoSizeColumn(i)	
	wb.write(output)
	output.close()
	if fileName == '':
		return output.toByteArray()
	else:
		return 	

today = system.date.now()
date = system.date.format(today, "dd.MM.yyyy")
time = system.date.format(today, "HHmm")

#if filePathAllFormulas is not None:
selectedLabel = event.source.parent.getComponent('Dropdown 1').selectedLabel
if selectedLabel == 'Exporting all formulas':
	query = "Select * from Test"
	pyDataset = system.db.runPrepQuery(query)
	ds = system.dataset.toDataSet(pyDataset)
	# Create file name
	fileName = "AllFormulas_" +date+ "_" +time+ ".xlsx" 
	filePathAllFormulas = system.file.saveFile(fileName)
	
	

	# Create Excel file
	datasetToExcel(ds, filePathAllFormulas, firstRow=3)
	# Open the Excel file
	system.net.openURL("file://"+filePathAllFormulas)
	
elif selectedLabel != 'Exporting all formulas':
	query = "Select * from Test where Name = ?"
	Name = event.source.parent.getComponent('Dropdown 1').selectedLabel
	args = [Name]
	pyDataset = system.db.runPrepQuery(query, args)
	dsIn = system.dataset.toDataSet(pyDataset)
	# Create file name
	fileNames = "SelectedFormulas_" +date+ "_" +time+ ".xlsx" 
	filePathFormula = system.file.saveFile(fileNames)

	# Create Excel file
	datasetToExcel(dsIn,filePathFormula, firstRow=3)
	# Open the Excel file
	system.net.openURL("file://"+filePathFormula)
		  

```

Thank you,  
Priyanka

---

<div class="post-metadata">

**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\
**Post date:** [May 17, 2023, 11:33am UTC](https://forum.inductiveautomation.com/t/export-dataset-and-operator-name-to-excel-in-vision/73828/2 "2023-05-17T11:33:43Z")

</div>

You will have to alter your `datasetToExcel()` function to have an operator name argument. Use that argument where you currently have `'Hello World'`. Then, where you call `datasetToExcel()`, include the desired string. (From reading a tag, or from the logged in user, whatever.)

---

<div class="post-metadata">

**Author:** ![Priyanka.Khandge](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/priyanka.khandge/32/35989_2.png) [@Priyanka.Khandge](https://forum.inductiveautomation.com/u/Priyanka.Khandge)\
**Post date:** [May 17, 2023, 12:07pm UTC](https://forum.inductiveautomation.com/t/export-dataset-and-operator-name-to-excel-in-vision/73828/3 "2023-05-17T12:07:51Z")

</div>

I have tried in this way but I'm not able to fix this.

---

<div class="post-metadata">

**Author:** ![paul-griffith](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/paul-griffith/32/37249_2.png) [@paul-griffith](https://forum.inductiveautomation.com/u/paul-griffith)\
**Post date:** [May 17, 2023, 2:51pm UTC](https://forum.inductiveautomation.com/t/export-dataset-and-operator-name-to-excel-in-vision/73828/4 "2023-05-17T14:51:35Z")

</div>

Post what you've tried and what isn't working/what errors are being thrown (in detail)/more information.

---

<div class="post-metadata">

**Author:** ![Tim\_Carlton](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/tim_carlton/32/52835_2.png) [@Tim\_Carlton](https://forum.inductiveautomation.com/u/Tim_Carlton)\
**Post date:** [May 17, 2023, 6:05pm UTC](https://forum.inductiveautomation.com/t/export-dataset-and-operator-name-to-excel-in-vision/73828/5 "2023-05-17T18:05:07Z")

</div>

I can't reproduce your code - getting  
`datasetToExcel (<type 'exceptions.ImportError'>, ImportError('No module named xlsxwriter',), <traceback object at 0x6>)` so I am guessing here...

1. You create a sheet
2. You write to 'A1'
3. You create a row for the header and add it to the sheet. By default this will be row 1 (and overwrite step 2?) but your test would put this in 4.
4. You create cells and add them to the row for the header
5. You create rows and you create cells that are added for the details

Steps 1, 3, 4, and 5 work.  
Step 2 doesn't.

Step 2 didn't create a row and didn't create a cell (or two cells - label and value)

What happens if you replace your `worksheet.write('A1','Hello world')` with the following:

```auto
		if firstRow > 0:
			nameRow = sheet.createRow(0)
			labelCell = nameRow.createCell(0)
			labelCell.setCellValue("Name")
						
			valueCell = nameRow.createCell(1)
			valueCell.setCellValue("Priyanka")
	

```

---

<div class="post-metadata">

**Author:** ![Priyanka.Khandge](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/priyanka.khandge/32/35989_2.png) [@Priyanka.Khandge](https://forum.inductiveautomation.com/u/Priyanka.Khandge)\
**Post date:** [May 18, 2023, 5:31am UTC](https://forum.inductiveautomation.com/t/export-dataset-and-operator-name-to-excel-in-vision/73828/6 "2023-05-18T05:31:50Z")

</div>

@Tim_Carlton Thank you so much for your help. I have updated my code as per your post and worked perfectly.

---

<div class="post-metadata">

**Author:** ![Priyanka.Khandge](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/priyanka.khandge/32/35989_2.png) [@Priyanka.Khandge](https://forum.inductiveautomation.com/u/Priyanka.Khandge)\
**Post date:** [May 18, 2023, 6:32am UTC](https://forum.inductiveautomation.com/t/export-dataset-and-operator-name-to-excel-in-vision/73828/7 "2023-05-18T06:32:59Z")

</div>

Hi @Tim_Carlton,

export data successfully. From row 3, all cell styles are fixed but rows 0 and 1 are not fixed any idea?

Thanks,  
Priyanka

---

<div class="post-metadata">

**Author:** ![Tim\_Carlton](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/tim_carlton/32/52835_2.png) [@Tim\_Carlton](https://forum.inductiveautomation.com/u/Tim_Carlton)\
**Post date:** [May 18, 2023, 12:13pm UTC](https://forum.inductiveautomation.com/t/export-dataset-and-operator-name-to-excel-in-vision/73828/8 "2023-05-18T12:13:20Z")

</div>

> [@Priyanka.Khandge](#):
>
> `cell.setCellStyle(headerStyle)`

If you added only my sample code then you aren't setting the style for the new rows.

- Move the blocks `#Create style for headers` and `#Create style for data` before the new code
- Add `labelCell.setCellStyle(headerStyle)` after `labelCell.setCellvalue("Name")
- Add `valueCell.setCellStyle(headerStyle)` after `valueCell.setCellValue("Priyanka")`

Probably the same for the cell with "RECIPE #:" in it.

---

<div class="post-metadata">

**Author:** ![Priyanka.Khandge](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/priyanka.khandge/32/35989_2.png) [@Priyanka.Khandge](https://forum.inductiveautomation.com/u/Priyanka.Khandge)\
**Post date:** [May 18, 2023, 1:04pm UTC](https://forum.inductiveautomation.com/t/export-dataset-and-operator-name-to-excel-in-vision/73828/9 "2023-05-18T13:04:06Z")

</div>

@Tim_Carlton Thank you so much.

Good catch!

Worked perfectly.