Custom naming excel sheets

Ignition
1

Hey guys, hope all is well, I just figured out how to export different datasets to different worksheets in an excel file which is very exciting since I’d only have to do one export than 10 :smiley:
Now my only question would be: is there a way to name the excel worksheets? right now when I export it shows as dataset1 dataset2, It would be really great if I could name them individually when exporting the data.
for anyone that want to know how to do the export you can just do the following:
system.dataset.exportExcel(“Name.xls”,1,[table1.data,table2.data,table3.data])
that will give you one excel file with 3 worksheets with your data.
Thanks in advance!:+1:

1 Like
2

This is not currently possible, but is a pretty good idea. I’ve filed an internal feature request so the developers can take a look at putting this into a future release.

1 Like
3

Yes I agree, It would be a great addition to excel exporting especially when we are talking about saving time and resources and instead of having 10+ files you end up with one.

4

Any progress on this front?

Alternatively, would anyone have a workaround?

5

Bit of a work around

esp = event.source.parent
path = system.dataset.exportExcel("test",1,[esp.getComponent("Table").data,esp.getComponent("Table").data])

text = system.file.readFileAsString(path)
for i,sheetName in enumerate(["Sheet 1", "Sheet 2"]): # make up your sheetnames in a list
	text = text.replace("Dataset %s"%(i+1), sheetName)
	
system.file.writeFile(path, text)
3 Likes
6

You could use system.dataset.datasetToExcel to format the dataset into an Excel-compatible string, then manipulate that string to rename the sheets.

2 Likes
7

@Stevenson.Yuan

Thanks this was really useful!

8

Hi, this function is no longer supported in Ignition 8.0.12.
Instead there is the function system.dataset.toExcel but it returns a byte array and not a string.
Is there any way to name the sheets using this function ?

9

In (hopefully) 8.0.16, or possibly 8.0.17, toExcel is getting an additional parameter to allow you to directly supply worksheet names. The datasetToExcel function still exists and will still work, it’s just deprecated, since it returns non-compliant XML documents that “pretend” to be XLS files; toExcel returns “true” XLSX documents.

10

Thanks for theses informations.
I tried using datasetToExcel function and save the file in xlsx but it could not open so i guess it only works with xls files.

11

Update: It made 8.0.16, so coming soon to a nightly/RC/final build, you’ll be able to provide worksheet names as a final parameter to system.dataset.toExcel.

4 Likes
12

hi PGriffith,

Tried changing the sheet names using system.dataset.toExcel but still the sheet names are not dynamically changed.

1 Like
13

Show your code.

2 Likes
14

datasetload = system.dataset.toDataSet(headers, load)
spreadsheet = system.dataset.toExcel( True , [datasetload],['DATA SHEET'])
filePath = "C:\\results.xls"
system. file .writeFile(filePath, spreadsheet)

1 Like
15

You are providing your list of sheet names to the nullsEmpty argument. Which makes nullsEmpty a True. You haven't provided your sheet names list to the argument for sheetNames.

In the docs: system.dataset.toExcel()

4 Likes