Export dataset and operator name to excel in vision

Ignition
, , ,
1

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?

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

2

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

3

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

4

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

1 Like
5

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:

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

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

7

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

8

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.

3 Likes
9

@Tim_Carlton Thank you so much.

Good catch!

Worked perfectly.