# Copying an Excel file to a table's dataset **URL:** \ **Category:** Ignition\ **Created:** [May 21, 2020, 3:38pm UTC](https://forum.inductiveautomation.com/t/copying-an-excel-file-to-a-tables-dataset/34942 "2020-05-21T15:38:18Z")\ **Posts on this page:** 20\ **Page:** 1
**Author:** ![jshaffer09](https://avatars.discourse-cdn.com/v4/letter/j/df788c/32.png) [@jshaffer09](https://forum.inductiveautomation.com/u/jshaffer09)\ **Post date:** [May 21, 2020, 3:38pm UTC](https://forum.inductiveautomation.com/t/copying-an-excel-file-to-a-tables-dataset/34942/1 "2020-05-21T15:38:18Z")
Is there a easy way to copy a Excel File to a tables Dataset? ---
**Author:** ![jshaffer09](https://avatars.discourse-cdn.com/v4/letter/j/df788c/32.png) [@jshaffer09](https://forum.inductiveautomation.com/u/jshaffer09)\ **Post date:** [May 21, 2020, 6:35pm UTC](https://forum.inductiveautomation.com/t/copying-an-excel-file-to-a-tables-dataset/34942/2 "2020-05-21T18:35:16Z")
any ideas? I have looked at the manual but don’t understand the steps. ---
**Author:** ![nminchin](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/nminchin/32/6911_2.png) [@nminchin](https://forum.inductiveautomation.com/u/nminchin)\ **Post date:** [May 22, 2020, 11:43am UTC](https://forum.inductiveautomation.com/t/copying-an-excel-file-to-a-tables-dataset/34942/3 "2020-05-22T11:43:57Z")
Have you looked at [this](http://forum.inductiveautomation.com/t/excel-xls-data-import/9022) topic? There’s a module for ignition that will read Excel sheets ---
**Author:** ![jshaffer09](https://avatars.discourse-cdn.com/v4/letter/j/df788c/32.png) [@jshaffer09](https://forum.inductiveautomation.com/u/jshaffer09)\ **Post date:** [May 26, 2020, 11:25am UTC](https://forum.inductiveautomation.com/t/copying-an-excel-file-to-a-tables-dataset/34942/4 "2020-05-26T11:25:44Z")
Ok, Thanks for the info. I will check it out. I really didn’t want to pay for something but that might be the only option if there isn’t any other way. ---
**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:** [May 26, 2020, 1:27pm UTC](https://forum.inductiveautomation.com/t/copying-an-excel-file-to-a-tables-dataset/34942/5 "2020-05-26T13:27:29Z")
xlrd works with Jython 2.7. Unzip this into the _user-lib/pylib/site-packages_ folder. [xlrd.zip](https://forum.inductiveautomation.com/uploads/short-url/rZTrGR58x9kIfTHEBhHHkzyJsLw.zip) (283.0 KB) [Documentation](https://xlrd.readthedocs.io/en/latest/) ---
**Author:** ![nader.chinichian](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/nader.chinichian/32/9261_2.png) [@nader.chinichian](https://forum.inductiveautomation.com/u/nader.chinichian)\ **Post date:** [January 22, 2021, 11:16am UTC](https://forum.inductiveautomation.com/t/copying-an-excel-file-to-a-tables-dataset/34942/6 "2021-01-22T11:16:48Z")
Hi Jordan Is there any option to read and write xlsx in ignition? ---
**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:** [January 22, 2021, 2:03pm UTC](https://forum.inductiveautomation.com/t/copying-an-excel-file-to-a-tables-dataset/34942/7 "2021-01-22T14:03:49Z")
The [Apache POI](https://poi.apache.org/apidocs/4.1/index.html) libraries are included with Ignition. At the time I was only needing to read files, so xlrd was all that was necessary. I'm just starting to dig into POI, myself, but here's a start: EDIT 2021-08-23: Updated to allow JsonDataSets. Changed to allow for a ByteArray output instead of writing to a file. EDIT 2023-01-20: Added option for date formats. Default is 'yyyy-MM-dd hh:mm:ss'. EDIT 2023-05-12: Added options for first row and column, if needed to offset from A1 ```python def datasetToExcel(dsIn, fileName = '', sheetName = 'Sheet1', firstRow = 0, firstCol= 0, dateFormat = 'yyyy-mm-dd hh:mm:ss'): ''' Convert a dataset to Excel params: dsIn: The dataset 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) sheetName: The tab name of the workbook. Default is Sheet1. firstRow: Starting row for the export. Default is 0 (Row 1) firstCol: Starting column for the export. Default is 0 (Col A) 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 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') if fileName == '': output = ByteArrayOutputStream() else: output = FileOutputStream(fileName) # Create workbook wb = XSSFWorkbook() # Create Sheet sheet = wb.createSheet(sheetName) # 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(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 # -------------------------------------------------------- # Sample dataset SampleHeaders = ['t_stamp', 'sValue', 'iValue', 'fValue'] SampleData = [[system.date.parse('2021-01-21 08:00:00'), 'A', 1, 1.23], [system.date.parse('2021-01-22 08:30:00'), 'B', 2, 4.56], [system.date.parse('2021-01-23 09:24:00'), 'C', 3, 7.89], [system.date.parse('2021-01-24 19:37:00'), 'D', 4, 0.12], [system.date.parse('2021-01-25 18:42:00'), 'E', 5, 3.45] ] ds = system.dataset.toDataSet(SampleHeaders, SampleData) datasetToExcel(ds, 'c:/test/poi1.xlsx', 'Dataset') pds = system.dataset.toPyDataSet(ds) datasetToExcel(pds, 'c:/test/poi2.xlsx', 'PyDataset') ``` ---
**Author:** ![jlandwerlen](https://avatars.discourse-cdn.com/v4/letter/j/bc8723/32.png) [@jlandwerlen](https://forum.inductiveautomation.com/u/jlandwerlen)\ **Post date:** [January 22, 2021, 4:46pm UTC](https://forum.inductiveautomation.com/t/copying-an-excel-file-to-a-tables-dataset/34942/8 "2021-01-22T16:46:21Z")
Awesome Jordan. Have you tried reading xlsx files yet? ---
**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:** [January 22, 2021, 4:51pm UTC](https://forum.inductiveautomation.com/t/copying-an-excel-file-to-a-tables-dataset/34942/9 "2021-01-22T16:51:44Z")
As stated elsewhere, I’ve been using xlrd for it, but I may have some time this weekend to play a bit. Unless my lovely bride has other ideas. Then all bets are off. ---
**Author:** ![nader.chinichian](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/nader.chinichian/32/9261_2.png) [@nader.chinichian](https://forum.inductiveautomation.com/u/nader.chinichian)\ **Post date:** [January 22, 2021, 6:19pm UTC](https://forum.inductiveautomation.com/t/copying-an-excel-file-to-a-tables-dataset/34942/10 "2021-01-22T18:19:19Z")
Can you explain what is the benefits of using the PA Office module from market when we can use and call appachi poi directly as you show here? ---
**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:** [January 22, 2021, 6:38pm UTC](https://forum.inductiveautomation.com/t/copying-an-excel-file-to-a-tables-dataset/34942/11 "2021-01-22T18:38:49Z")
As stated in other threads, in v7 the POI libraries were not included. the module was a wrapper for POI and (I think) added some useful programming hooks. Now that POI is included in v8, it’s not really needed, IMO. Also, since Nick doesn’t really do Ignition stuff anymore, I don’t think there is a v8 of it. Kymera’s, on the other hand, does have a v8 version of their module, though I’ve not used it. ---
**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:** [January 22, 2021, 6:43pm UTC](https://forum.inductiveautomation.com/t/copying-an-excel-file-to-a-tables-dataset/34942/12 "2021-01-22T18:43:38Z")
For another option re: reading Excel files, I started and never finished an Ignition module that brings in the [`fastexcel`](https://github.com/dhatim/fastexcel) library: [https://github.com/paul-griffith/kotlin-module](https://github.com/paul-griffith/kotlin-module) ---
**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:** [January 22, 2021, 9:26pm UTC](https://forum.inductiveautomation.com/t/copying-an-excel-file-to-a-tables-dataset/34942/13 "2021-01-22T21:26:46Z")
Got my paperwork done early, so here's a sample to read from an Excel file **EDIT** : _Reworked a bit to work with both .xls and .xlsx files_ **EDIT** : _Modified to properly cast data types_ **EDIT** : _Added handler for formula cell types_ **EDIT** : _Added parameters to optionally specify sheet, row, and column numbers_ Reading back a file created from my other example: ```python def excelToDataSet(fileName, hasHeaders = False, sheetNum = 0, firstRow = None, lastRow = None, firstCol = None, lastCol = None): import org.apache.poi.ss.usermodel.WorkbookFactory as WorkbookFactory import org.apache.poi.ss.usermodel.DateUtil as DateUtil from java.io import FileInputStream from java.util import Date """ Function to create a dataset from an Excel spreadsheet. It will try to automatically detect the boundaries of the data, but helper parameters are available: params: fileName - The path to the Excel spreadsheet. (required) hasHeaders - If true, uses the first row of the spreadsheet as column names. sheetNum - select the sheet to process. defaults to the first sheet. firstRow - select first row to process. lastRow - select last row to process. firstCol - select first column to process lastCol - select last column toprocess """ fileStream = FileInputStream(fileName) wb = WorkbookFactory.create(fileStream) sheet = wb.getSheetAt(sheetNum) if firstRow is None: firstRow = sheet.getFirstRowNum() if lastRow is None: lastRow = sheet.getLastRowNum() data = [] for i in range(firstRow , lastRow + 1): row = sheet.getRow(i) print str(i).zfill(3), list(row) if i == firstRow: if firstCol is None: firstCol = row.getFirstCellNum() if lastCol is None: lastCol = row.getLastCellNum() else: # if lastCol is specified add 1 to it. lastCol += 1 if hasHeaders: headers = list(row)[firstCol:lastCol] else: headers = ['Col'+str(cNum) for cNum in range(firstCol, lastCol)] rowOut = [] for j in range(firstCol, lastCol): if i == firstRow and hasHeaders: pass else: cell = row.getCell(j) cellType = cell.getCellType().toString() if cellType == 'NUMERIC': if DateUtil.isCellDateFormatted(cell): value = cell.dateCellValue else: value = cell.getNumericCellValue() if value == int(value): value = int(value) elif cellType == 'STRING': value = cell.getStringCellValue() elif cellType == 'BOOLEAN': value = cell.getBooleanCellValue() elif cellType == 'BLANK': value = None elif cellType == 'FORMULA': formulatype=str(cell.getCachedFormulaResultType()) if formulatype == 'NUMERIC': if DateUtil.isCellDateFormatted(cell): value = cell.dateCellValue else: value = cell.getNumericCellValue() if value == int(value): value = int(value) elif formulatype == 'STRING': value = cell.getStringCellValue() elif formulatype == 'BOOLEAN': value = cell.getBooleanCellValue() elif formulatype == 'BLANK': value = None else: value = None rowOut.append(value) if len(rowOut) > 0: data.append(rowOut) fileStream.close() return system.dataset.toDataSet(headers, data) # Read .xlsx file fName = 'C:/Test/poi1.xlsx' ds1 = excelToDataSet(fName, True) util.printDataSet(ds1) #Read .xls file fName = 'C:/Test/poi2.xls' ds2 = excelToDataSet(fName, True) util.printDataSet(ds2) ``` ```plaintext row | t_stamp | sValue | iValue | fValue ------------------------------------------------------------- 0 | Thu Jan 21 08:00:00 EST 2021 | A | 1 | 1.23 1 | Fri Jan 22 08:30:00 EST 2021 | B | 2 | 4.56 2 | Sat Jan 23 09:24:00 EST 2021 | C | 3 | 7.89 3 | Sun Jan 24 19:37:00 EST 2021 | D | 4 | 0.12 4 | Mon Jan 25 18:42:00 EST 2021 | E | 5 | 3.45 row | t_stamp | sValue | iValue | fValue ------------------------------------------------------------- 0 | Thu Jan 21 08:00:00 EST 2021 | Z | 5 | 3.45 1 | Fri Jan 22 08:30:00 EST 2021 | Y | 3 | 0.12 2 | Sat Jan 23 09:24:00 EST 2021 | X | 2 | 7.89 3 | Sun Jan 24 19:37:00 EST 2021 | W | 4 | 4.56 4 | Mon Jan 25 18:42:00 EST 2021 | V | 1 | 1.23 ``` ---
**Author:** ![dkhayes117](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/dkhayes117/32/68344_2.png) [@dkhayes117](https://forum.inductiveautomation.com/u/dkhayes117)\ **Post date:** [January 22, 2021, 9:59pm UTC](https://forum.inductiveautomation.com/t/copying-an-excel-file-to-a-tables-dataset/34942/14 "2021-01-22T21:59:06Z")
I like the this 🧐 `util.printDataSet()` I assume that is a custom script module? ---
**Author:** ![jlandwerlen](https://avatars.discourse-cdn.com/v4/letter/j/bc8723/32.png) [@jlandwerlen](https://forum.inductiveautomation.com/u/jlandwerlen)\ **Post date:** [January 22, 2021, 10:03pm UTC](https://forum.inductiveautomation.com/t/copying-an-excel-file-to-a-tables-dataset/34942/15 "2021-01-22T22:03:25Z")
Thanks, this may come in handy for a project in the works. ---
**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:** [January 23, 2021, 11:48pm UTC](https://forum.inductiveautomation.com/t/copying-an-excel-file-to-a-tables-dataset/34942/16 "2021-01-23T23:48:36Z")
Here you go. 🙂 > [@View Dataset from script console](http://forum.inductiveautomation.com/t/view-dataset-from-script-console/36704/4): > > Sorry, I'm on vacation, but family is napping, so I get to play. Yes, this is how I relax. Don't judge. EDIT: Added option for printing from Perspective. Sorry, not quite ready for prime time. roll_eyes def printDataSet(datasetIn): from itertools import izip import ast if 'PropertyTreeScriptWrapper$ArrayWrapper' in str(type(datasetIn)): headers = datasetIn[0].keys() data = [] for row in datasetIn: data.append([item.value for item in row.values()]) datasetIn = system.dataset… ---
**Author:** ![nader.chinichian](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/nader.chinichian/32/9261_2.png) [@nader.chinichian](https://forum.inductiveautomation.com/u/nader.chinichian)\ **Post date:** [January 24, 2021, 2:23pm UTC](https://forum.inductiveautomation.com/t/copying-an-excel-file-to-a-tables-dataset/34942/17 "2021-01-24T14:23:05Z")
Hi Jordan Thanks for the code. Could you please update the code so it get file bytes instead of file path. I need this because in perspective we only can upload file to server and deal with file stream. ---
**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:** [January 25, 2021, 11:43am UTC](https://forum.inductiveautomation.com/t/copying-an-excel-file-to-a-tables-dataset/34942/18 "2021-01-25T11:43:55Z")
Untested, but you should just be able to use ByteArrayInputStream instead. ```python def excelBytesToDataSet(bytesIn, hasHeaders = False): import org.apache.poi.ss.usermodel.WorkbookFactory as WorkbookFactory import org.apache.poi.ss.usermodel.DateUtil as DateUtil from java.io import ByteArrayInputStream fileStream = ByteArrayInputStream(bytesIn) ... ``` ---
**Author:** ![nader.chinichian](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/nader.chinichian/32/9261_2.png) [@nader.chinichian](https://forum.inductiveautomation.com/u/nader.chinichian)\ **Post date:** [January 26, 2021, 5:03am UTC](https://forum.inductiveautomation.com/t/copying-an-excel-file-to-a-tables-dataset/34942/19 "2021-01-26T05:03:13Z")
Hi Jordan, When I use your code it work perfectly but when I want to save the window I always get following error: > SerializationException: Error during serialization for property 'contentPane' on object '[FPMIWindow]Main Window' > caused by SerializationException: Error during serialization for property 'data' on object '[PMITable]PMITable[Table]' > caused by SerializationException: Unable to create clean copy of class org.apache.poi.xssf.usermodel.XSSFCell > > Ignition v8.1.1 (b2020120808) > Java: Azul Systems, Inc. 11.0.7 And after that the script doesn't work without any error until I close designer and open it again. ---
**Author:** ![zxcslo](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/zxcslo/32/61610_2.png) [@zxcslo](https://forum.inductiveautomation.com/u/zxcslo)\ **Post date:** [January 26, 2021, 6:22am UTC](https://forum.inductiveautomation.com/t/copying-an-excel-file-to-a-tables-dataset/34942/20 "2021-01-26T06:22:15Z")
Same here… [Next page](https://forum.inductiveautomation.com/t/copying-an-excel-file-to-a-tables-dataset/34942.md?page=2)