Hello, I’m currently developing on Ignition version 8.0.9 and I am having issues exporting a chart dataset to excel by using both functions “system.dataset.exportExcel” and “system.dataset.toExcel”. The main problem is that the dataset has a column in Date format; Ignition exports such column transforming it in the following way:
01/02/2020 07:00:00 ===> 43862,2916666667
This causes the excel spreadsheet to require dedicated cell formatting to re-transform such value into a readable date. The same functions in version 7.9 didn’t cause this transformation.
How can I avoid this conversion from occurring?
Is this error again happens in 8.0.15? I’m facing the same issue. It comes in unix milliseconds format.
Is there any way to convert to DD-MM-YYYY while we export to excel?
Table JSON is converted to dataset and I used system.dataset.formatDates as suggested. But no conversion. I still get in unix milliseconds timestamp.
data = self.parent.parent.getChild("TableConsumption").props.data
# TableEXPORTFIELDCHOOSER is a custom made table for users to choose the columns and order of column to export.
tablefieldds = self.getSibling("TableEXPORTFIELDCHOOSER").props.data
sorttablefieldds = system.dataset.sort(tablefieldds, 2)
#Get the chosen columns
tablefieldheader = []
for row in range(sorttablefieldds.rowCount):
if sorttablefieldds.getValueAt(row, "CHOOSE") == True:
tablefieldheader.append(sorttablefieldds.getValueAt(row, "COLUMNS"))
system.perspective.print(tablefieldheader)
system.perspective.print(len(tablefieldheader))
tpc = self.parent.parent.getChild("TableConsumption").props.columns
newheader = []
#Get the field name for columns
for title in range(len(tablefieldheader)):
for row in range(len(tpc)):
if tablefieldheader[title] == tpc[row]["header"]["title"] :
tpcfield = tpc[row]["field"]
newheader.append(tpcfield)
system.perspective.print(newheader)
system.perspective.print(len(newheader))
#convert JSON table output to dataset
datasetData = []
for row in range(len(data)):
rowData = []
for col in range(len(newheader)):
cell = data[row][newheader[col]]
if hasattr(cell, 'value'):
rowData.append(cell.value)
else:
rowData.append(cell)
#system.perspective.print(rowData)
datasetData.append(rowData)
#export to excel
finalDataset = system.dataset.toDataSet(tablefieldheader,datasetData)
formatdateds = system.dataset.formatDates(finalDataset, "dd-mm-yyyy")
XLSFormat = system.dataset.dataSetToExcel(True,[formatdateds], True)
timenow = system.date.format(system.date.now(), 'dd_MM_yyyy_HH_mm')
filename = 'T2_SHIFTPARAMS_' + timenow + '.xls'
system.perspective.download(filename,XLSFormat)
Here is a method that can export a list of Tables in excel. I did fix the formatting of the date using the column render method and dateFormat
This method only exports visible columns of Tables
'''
exportToExcel(fileName, dataTable, sheetName):
Generate an excel file containing the exact representation of the data shown in the table.
input:
fileName : Name of the file without date and extension. Date is added automatically to the filename.
dataTables : List of Table Object to export
sheetNames : List of sheets names
'''
def exportToExcel(fileName, dataTables, sheetNames):
xlsxData=[]
for dataTable in dataTables:
data = dataTable.props.data
tableColumns = dataTable.props.columns
headers = []
rows = []
for r, row in enumerate(data):
newRow = []
for c, col in enumerate(tableColumns):
if col["visible"]:
if r==0:
headers.append(col["header"]["title"])
#format date
if col["render"]=="date":
dateValue = system.date.fromMillis(row[col["field"]])
dateFormat = col["dateFormat"]
if dateFormat == "":
dateFormat = 'yyyy-MM-dd HH:mm:ss'
newRow.append(system.date.format(dateValue,dateFormat))
else:
newRow.append(row[col["field"]])
rows.append(newRow)
ds = system.dataset.toDataSet(headers, rows)
xlsxData.append(ds)
xlsxBinary = system.dataset.toExcel(True, xlsxData, True, sheetNames)
filename = '%s_%s.xlsx' % (fileName, system.date.now())
system.perspective.download(filename, xlsxBinary)
