April 1, 2014
Specifying datatypes in Spreadsheets
Comments
(2)
April 1, 2014
Specifying datatypes in Spreadsheets
Newbie 12 posts
Followers: 0 people
(2)

We have often encountered bugs around datatype of data being written in spreadsheets. For example string like 8E45000 getting converted into a floating type or a string like 4D or 4F getting converted to numeric etc.

The internal logic used to extract the data type of the data sometimes isnt that accurate and we would end up in writing corrupted data in spreadsheets.

With Splendor now we provide users with the option of specifying the datatype of the data being written in spreadsheets. In this way we can avoid the internal logic of determining the datatype which sometimes used to corrupt the data.

This option will be supported in these 3 mehotds : SpreadSheetAddRow,SpreadSheetAddRows,SpreadSheetSetCellValue

Possible datatype of a cell can be : STRING, NUMERIC or DATE

Here is the syntax :

SpreadSheetAddRow( ExcelInfo excelvar, String value, int row, int column, boolean insert,String datatype )

where ‘datatype’ is an expression which describes the datatype of data.

Here are examples :

STRING:1,3;NUMERIC:2 –> Data in 1st and 3rd column of this row will be of type string, and that of 2nd column of type numeric

NUMERIC:1;STRING –> Data in 1st column of this row will be of type numeric, rest all will be of type string

NUMERIC:2-5;STRING:1,6;DATE –> Data in 1st and 6th column of this row will be of type string and data from 2nd to 5th column will be numeric and rest of type date.

STRING –> All cells in this row will be of type string

string;numeric:3,4 –> This is an invalid expression. Only last in the sequence can skip mentioning column numbers. In that case we will assume rest all columns will be of that datatype. For example this will work :NUMERIC:3,4;STRING

If for any cell datatype is not specified, it will fallback to old way of extracting the datatype.

Similarly we take datatype attribute in other methods also :

SpreadSheetAddRows( ExcelInfo excelvar, Object q, int startrow, int startcolumn, boolean insert,Object datatypes )

Where ‘datatypes’ will be an array of datatype expressions

SpreadSheetSetCellValue( ExcelInfo excelvar, String value, int row, int column, String datatype )

Where ‘datatype’ is datatype of the cell

 

2 Comments
2018-11-07 16:44:48
2018-11-07 16:44:48

I am amending the last reply. I discovered the formats in the help document, https://szo.us/2RHH8VP are not compatible with xlsx. Once I corrected the formats, then they worked correctly.

Like
2018-11-07 13:44:50
2018-11-07 13:44:50

I’ve been working for several days with the spreadsheet functions. I am able to successfully generate an excel file with custom headers. I can apply a format to the header and separate format to the remaining cells. Where I am running into a problem is applying a datatype to the cells. If I have a US currency column, nothing ever gets applied. I was using spreadsheetFormatCellRange() because the format column formats the header row cell. Am I going about this right? What am I missing?

 

variables.cellFormat = StructNew();
variables.cellFormat.dataformat = “($#,##0_($#,##0) “; //The excel data format
spreadsheetFormatCellRange(variables.spreadsheetObj, variables.cellFormat, 2, 6, 21, 6);

Like
Add Comment