We recently upgraded to the July 2018 Service Release 20181.177, and we are now having some issues with applying cell formatting in Ultraspreadsheets.
We load information from a database, and based on the SQL data type we apply a column format string to each cell (all cells in an individual column have the same format).
For example if the database field is a decimal/float/real in the database we specify a format of:
Column_Format_String = "#,##0.00;[red](#,##0.00)";
and when the cell is populated the code would be:
------------------------------------------------------------------------------
Inf_Active_Worksheet.Rows[iDataRow].Cells[theColumn_Num].CellFormat.FormatString = Column_Format;Inf_Active_Worksheet.Rows[iDataRow].Cells[theColumn_Num].CellFormat.FormatOptions = WorksheetCellFormatOptions.ApplyNumberFormatting; Inf_Active_Worksheet.Rows[iDataRow].Cells[theColumn_Num].Value = theColumn_Value;
----------------------------------------------------------------------------
The issue that we are having is that the cell formatting does not take effect until we double click on the cell. for instance, when the spreadsheet is first displayed a numeric cell is displayed as 48 and after double clicking it is 48.00. This is a little annoying, but the real problem is that until this is performed, formulas do not work, and anything the is exported to excel also has this same generic formatting. This behavior is also seen for other formats such as Date/Time and Boolean.
Once a manual double click is performed on every one of the cells, everything is perfect, both in our application and in excel.
We did not notice this behavior in prior versions. If we need to call an additional Ultraspreadsheet function for the entire worksheet (or individual cells), that would not be an issue.
Recommendations?
Thank you. By passing each cell value as the native type (e.g. Boolean, DateTime, etc. ) when setting the cell value the display and parsing is now correct without having to manually edit each cell.
Hello Mark,
I have been investigating into this behavior you are seeing where cell formats are not shown in the UltraSpreadsheet in version 18.1.20181.177, and I am curious as to what the type of "theColumn_Value" is in the case of your above code-snippet.
I am able to see the behavior you are referring to if "theColumn_Value" is a string in this case, but if it is a numeric type, the numeric formatting is applied correctly on my end. This is because when you try to edit a cell and then exit "edit mode" on it, we will internally see if the cell's value parses to a numeric/DateTime/etc. type. If it does, then the CellFormat.FormatString will be applied if it is able to parse to the type that the FormatString is applicable on.
You had mentioned above that you recently upgraded to version 18.1.20181.177. Which version did you upgrade from where this behavior was not seen on your end?
I am attaching the sample project I used to test this. If you change the statement on the Random.Next() to be Random.Next.ToString(), the behavior is reproducible for the reasons mentioned above.
Please let me know if you have any other questions or concerns on this matter.
UltraSpreadsheetFormattingTest.zip