# Export Multiple Tables to Single Excel spreadsheet

**URL:** <https://forum.inductiveautomation.com/t/export-multiple-tables-to-single-excel-spreadsheet/9234>\
**Category:** Ignition\
**Created:** [October 27, 2014, 2:11pm UTC](https://forum.inductiveautomation.com/t/export-multiple-tables-to-single-excel-spreadsheet/9234 "2014-10-27T14:11:33Z")\
**Posts on this page:** 16\
**Page:** 1

<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:** [October 27, 2014, 2:11pm UTC](https://forum.inductiveautomation.com/t/export-multiple-tables-to-single-excel-spreadsheet/9234/1 "2014-10-27T14:11:33Z")

</div>

From [this topic](https://www.inductiveautomation.com/forum/viewtopic.php?f=70&t=13102).

Sample window attached. The script in the button will export multiple tables and put them in separate sheets.

You may either download it from there or from here. Your choice! 😉

EDIT: renamed topic to something making more sense… 😆  
[Export Multiple Table to Excel\_2014-10-27\_0935.proj](https://us1.discourse-cdn.com/inductiveautomation/original/2X/8/8d55fa2a85f6c9e672264f741952d1682dd10ed4.proj) (20.6 KB)

---

<div class="post-metadata">

**Author:** ![abdullahaljandali99](https://avatars.discourse-cdn.com/v4/letter/a/c67d28/32.png) [@abdullahaljandali99](https://forum.inductiveautomation.com/u/abdullahaljandali99)\
**Post date:** [August 17, 2018, 8:54pm UTC](https://forum.inductiveautomation.com/t/export-multiple-tables-to-single-excel-spreadsheet/9234/2 "2018-08-17T20:54:39Z")

</div>

This link you provided is not working.

---

<div class="post-metadata">

**Author:** ![rbrown](https://avatars.discourse-cdn.com/v4/letter/r/13edae/32.png) [@rbrown](https://forum.inductiveautomation.com/u/rbrown)\
**Post date:** [September 3, 2019, 7:37pm UTC](https://forum.inductiveautomation.com/t/export-multiple-tables-to-single-excel-spreadsheet/9234/3 "2019-09-03T19:37:59Z")

</div>

This is close to what I am looking for and was hoping to look at the project to help figure out what I needed. Can you possibly fix the download link?

---

<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:** [September 3, 2019, 8:21pm UTC](https://forum.inductiveautomation.com/t/export-multiple-tables-to-single-excel-spreadsheet/9234/4 "2019-09-03T20:21:58Z")

</div>

Rats! I hate it when the forum drops my stuff.

Actually, it’s not needed anymore, since [system.dataset.dataSetToExcel()](https://docs.inductiveautomation.com/display/DOC79/system.dataset.dataSetToExcel) has it built in (as of 7.9.8). You will need to make a list out of the datasets you use:

```python
# get datasets
dataIn_1 = event.source.parent.getComponent('Table 1').data
dataIn_2 = event.source.parent.getComponent('Table 2').data

# create spreadsheet
spreadsheet = system.dataset.dataSetToExcel(1, [dataIn_1, dataIn_2])

# set file path to save the spreadsheet
filepath = 'c:/path/to/save/results.xls'

# write the file
system.file.writeFile(filepath, spreadsheet)

```

---

<div class="post-metadata">

**Author:** ![Kalidasan.V](https://avatars.discourse-cdn.com/v4/letter/k/ccd318/32.png) [@Kalidasan.V](https://forum.inductiveautomation.com/u/Kalidasan.V)\
**Post date:** [August 9, 2021, 7:27am UTC](https://forum.inductiveautomation.com/t/export-multiple-tables-to-single-excel-spreadsheet/9234/5 "2021-08-09T07:27:52Z")

</div>

Hi,  
I Have Same Doubts In Perspective. How should i do this in perspective. i’ve multiple tables i want to export this tables one after another in a single excel sheet.  
i using this Code…

```
data_1 = self.parent.getChild("FlexContainer").getChild("Table").props.data
data_2 = self.parent.getChild("FlexContainer").getChild("Table_0").props.data
fileName = 'Example File'
data_tab = [data_1,data_2]
datasetData = []
for tabName in data_tab:
	data = tabName
	headers = data[0].keys()
	#system.perspective.print(data)
	for row in range(len(data)):
		rowData = []
		for col in range(len(headers)):
			cell = data[row][headers[col]]
			if hasattr(cell, 'value'):
				rowData.append(cell.value)
				system.perspective.print(headers)
			else:
				rowData.append(cell)
				#system.perspective.print(rowData)
		datasetData.append(rowData)
	       finalDataset = system.dataset.toDataSet(headers,datasetData)		
   
excelFileName = str(fileName) + ".xlsx"
excelBytes = system.dataset.toExcel(True,[finalDataset]) #returns .xls file as bytes
system.perspective.download(excelFileName, excelBytes)

```

i not get my desired output…  
How should i need to modify this code…  
i want…  
each table print one after another with some space(One Column)…

i am new in this environment…  
Thanks For Advance 🙂

---

<div class="post-metadata">

**Author:** ![GOKULAKANNAN\_P](https://avatars.discourse-cdn.com/v4/letter/g/0ea827/32.png) [@GOKULAKANNAN\_P](https://forum.inductiveautomation.com/u/GOKULAKANNAN_P)\
**Post date:** [June 29, 2023, 9:08am UTC](https://forum.inductiveautomation.com/t/export-multiple-tables-to-single-excel-spreadsheet/9234/6 "2023-06-29T09:08:27Z")

</div>

Hi @JordanCClark ,

I have created excel with 2 spreadsheet as you mentioned.Now the Spreadsheets names are (dataset,dateset1). But I need to Change that spreadsheet names.

I tried this one ,

```auto
sheetNames = ["Production", "Downtime"]
excelData = system.dataset.exportExcel(1,[Dataset, Dataset1], sheetNames)

```

But it is not working.

Any One Have A Idea Please Provide.

Thanks in advance!.

---

<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 29, 2023, 10:14am UTC](https://forum.inductiveautomation.com/t/export-multiple-tables-to-single-excel-spreadsheet/9234/7 "2023-06-29T10:14:49Z")

</div>

> [@GOKULAKANNAN\_P](#):
>
> I tried this one ,
> 
> ```auto
> sheetNames = ["Production", "Downtime"]
> excelData = system.dataset.exportExcel(1,[Dataset, Dataset1], sheetNames)
> 
> ```
> 
> But it is not working.

Not sure where the idea came from, but exportExcel() is not documented to accept sheet names.

Here is a modified version of a [function I wrote in this thread](https://forum.inductiveautomation.com/t/copying-a-excel-file-to-a-tables-dataset/34942/7):

```python
def listToExcel(datasetList, fileName = '', sheetNames = 'Sheet {}', dateFormat = 'yyyy-mm-dd hh:mm:ss'):
	''' Convert a list of datasets to Excel
			params:
	    	datasetList:	The list of datasets to convert. Valid types are:
	        				- com.inductiveautomation.ignition.common.BasicDataset
							- com.inductiveautomation.ignition.common.script.builtin.DatasetUtilities$PyDataSet
							- com.inductiveautomation.ignition.common.JsonDataset
			fileName:	The name to save the Excel file. If omitted, the output will be a byte array.
						This is useful to use with perspective (e.g. system.perspective.download)
			sheetNames:	List of names The tab name of the workbook. Default is Sheet 1, Sheet 2, etc.
			dateFormat:	How dates should be formatted, e.g. 'yyyy-mm-dd hh:mm:ss'. 
			            Note that Excel does not use capital letters, because... Microsoft.
			            The script will auto lower-case your format.       
	'''
	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
	
	if fileName == '':
		output = ByteArrayOutputStream()
	else:
		output = FileOutputStream(fileName)
	# Create workbook
	wb = XSSFWorkbook()
	
	for sheetIndex, dsIn in enumerate(datasetList):
		dsType = str(type(dsIn))
		
		# Convert to PyDataSet, if needed
		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')
	
		# Create Sheet
		if sheetNames == 'Sheet {}':
			sheet = wb.createSheet(sheetNames.format(sheetIndex + 1))
		else:
			sheet = wb.createSheet(sheetNames[sheetIndex])
	
		# Create formatter
		fmt = wb.createDataFormat()
	
		# 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(0)
		for i, col in enumerate(pyDS.getColumnNames()):
			cell = headerRow.createCell(i)
			cell.setCellStyle(headerStyle)
			cell.setCellValue(col)
		# Create data rows	
		for i, row in enumerate(pyDS):
			dataRow = sheet.createRow(i+1)
			for j, col in enumerate(list(row)):
				cell = dataRow.createCell(j)
				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

```

Sample usage:

```python
sampleHeaders1 = ['col1', 'col2', 'col3']
sampleData1 = [[system.date.parse('2023-06-01 00:00:00'), 'Row1', 1],
               [system.date.parse('2023-06-02 00:00:00'), 'Row2', 2],
               [system.date.parse('2023-06-03 00:00:00'), 'Row3', 3]
              ]

sampleHeaders2 = ['col10', 'col20', 'col30']
sampleData2 = [[system.date.parse('2023-06-11 00:00:00'), 'Row11', 11],
               [system.date.parse('2023-06-12 00:00:00'), 'Row12', 12],
               [system.date.parse('2023-06-13 00:00:00'), 'Row13', 13]
              ]

dataset1 = system.dataset.toDataSet(sampleHeaders1, sampleData1)
dataset2 = system.dataset.toDataSet(sampleHeaders2, sampleData2)

util.dataset.listToExcel([dataset1, dataset2], fileName='C:/Test/multisheetTest.xlsx', sheetNames=['MySheet1', 'TheSecondSheet'])

```

 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/d/b/db42f64ab55b9fb1ec3e864e309dfc2ea59c64f9.png)

![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/4/9/49bbee80b93abb3bc11d025251fb814a5eaa9e85.png)

![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/c/9/c9b9abd5dcb56d6bd36169f2f2d1ef8e31c7d09a.png)

---

<div class="post-metadata">

**Author:** ![GOKULAKANNAN\_P](https://avatars.discourse-cdn.com/v4/letter/g/0ea827/32.png) [@GOKULAKANNAN\_P](https://forum.inductiveautomation.com/u/GOKULAKANNAN_P)\
**Post date:** [June 29, 2023, 10:48am UTC](https://forum.inductiveautomation.com/t/export-multiple-tables-to-single-excel-spreadsheet/9234/8 "2023-06-29T10:48:38Z")

</div>

Hi @JordanCClark ,

I Found that idea from Chat GPT.

thanks for Your Quick response.

But , I need this in perspective.i am using \> system.perspective.download(filename, data)

this is my script:

```auto
ds = system.db.runNamedQuery("My_named_Query",parameters)
ds1 = system.db.runNamedQuery("My_named_Query1",parameters)
data = system.dataset.toExcel(True,[ds,ds1])
filename = 'Data.xlsx'
system.perspective.download(filename, data)

```

---

<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 29, 2023, 11:21am UTC](https://forum.inductiveautomation.com/t/export-multiple-tables-to-single-excel-spreadsheet/9234/9 "2023-06-29T11:21:09Z")

</div>

With no filename in the function I supplied, it returns a byte array.

```python
ds = system.db.runNamedQuery("My_named_Query",parameters)
ds1 = system.db.runNamedQuery("My_named_Query1",parameters)
data = util.dataset.listToExcel([ds, ds1], sheetNames = ["Production", "Downtime"])
filename = 'Data.xlsx'
system.perspective.download(filename, data)

```

---

<div class="post-metadata">

**Author:** ![GOKULAKANNAN\_P](https://avatars.discourse-cdn.com/v4/letter/g/0ea827/32.png) [@GOKULAKANNAN\_P](https://forum.inductiveautomation.com/u/GOKULAKANNAN_P)\
**Post date:** [June 29, 2023, 11:51am UTC](https://forum.inductiveautomation.com/t/export-multiple-tables-to-single-excel-spreadsheet/9234/10 "2023-06-29T11:51:56Z")

</div>

i need to import any library?

because I got this error:

```auto
NameError: global name 'util' is not defined

```

---

<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 29, 2023, 12:33pm UTC](https://forum.inductiveautomation.com/t/export-multiple-tables-to-single-excel-spreadsheet/9234/11 "2023-06-29T12:33:55Z")

</div>

Copy. Paste into a project script of your choosing. Set your path to the function accordingly.

---

<div class="post-metadata">

**Author:** ![GOKULAKANNAN\_P](https://avatars.discourse-cdn.com/v4/letter/g/0ea827/32.png) [@GOKULAKANNAN\_P](https://forum.inductiveautomation.com/u/GOKULAKANNAN_P)\
**Post date:** [June 29, 2023, 3:47pm UTC](https://forum.inductiveautomation.com/t/export-multiple-tables-to-single-excel-spreadsheet/9234/12 "2023-06-29T15:47:37Z")

</div>

Thanks @JordanCClark .It's working. but the date is not formated.

![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/0/9/099075aeaea2e0b7f0cc0dec9fb18d1b87929fc5.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 29, 2023, 5:16pm UTC](https://forum.inductiveautomation.com/t/export-multiple-tables-to-single-excel-spreadsheet/9234/13 "2023-06-29T17:16:42Z")

</div>

Without seeing any data, I would say that the times are coming in as milliseconds and not a date

---

<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:** [June 29, 2023, 5:19pm UTC](https://forum.inductiveautomation.com/t/export-multiple-tables-to-single-excel-spreadsheet/9234/14 "2023-06-29T17:19:48Z")

</div>

> [@JordanCClark](#):
>
> Not sure where the idea came from, but exportExcel() is not documented to accept sheet names.

exportExcel doesn't, but toExcel does:  
[https://docs.inductiveautomation.com/display/DOC81/system.dataset.toExcel](https://docs.inductiveautomation.com/display/DOC81/system.dataset.toExcel)

Easy enough to pair with system.file.saveFile if you wanted.

---

<div class="post-metadata">

**Author:** ![GOKULAKANNAN\_P](https://avatars.discourse-cdn.com/v4/letter/g/0ea827/32.png) [@GOKULAKANNAN\_P](https://forum.inductiveautomation.com/u/GOKULAKANNAN_P)\
**Post date:** [June 30, 2023, 4:37am UTC](https://forum.inductiveautomation.com/t/export-multiple-tables-to-single-excel-spreadsheet/9234/15 "2023-06-30T04:37:15Z")

</div>

Yes.  
the times are coming in as milliseconds .  
I Changed to Date. now it is working !..

Thanks @JordanCClark for Your Quick responses.

Have a great day 🙂 ...

---

<div class="post-metadata">

**Author:** ![GOKULAKANNAN\_P](https://avatars.discourse-cdn.com/v4/letter/g/0ea827/32.png) [@GOKULAKANNAN\_P](https://forum.inductiveautomation.com/u/GOKULAKANNAN_P)\
**Post date:** [June 30, 2023, 4:47am UTC](https://forum.inductiveautomation.com/t/export-multiple-tables-to-single-excel-spreadsheet/9234/16 "2023-06-30T04:47:41Z")

</div>

Thanks @paul-griffith

This one also working. I think it is very simple and easy way.

This is the code:

```auto
ds = system.db.runNamedQuery("My_named_Query",parameters)
ds1 = system.db.runNamedQuery("My_named_Query1",parameters)
data = system.dataset.toExcel(1,[ds, ds1], sheetNames = ["sheetName1", "sheetName2"])
filename = 'Data.xlsx'
system.perspective.download(filename, data)

```

Thank again 🙂