This should be fundamental, but I guess it is not.
We are designing Filters for the UltraSpreadsheet for Windows Forms ---NOT THE WINGRID-- and since the filtering is based on type (Date, Number, Text, ext), how do we set the data type for each column in the spreadsheet? And does it need to be applied to the WorkBook, WorkSheet, WorksheetTable, WorkSheetColumn or WHAT?
Please just provide a clear example to set a column to a DateTime format, so the sample can be extrapolated to other data types.
Thank you for posting to our forum.
The wingrid filtering is different than the spreadsheet filtering because in spreadsheet filtering there's no such thing as a DataType for a spreadsheet columns. Every cell has a value that can be any data type.
For the spreadsheet filtering you can go through this online document provided all the information with code examples:
Please let me know if you need further assistance.
Alright, I understand about polymorphic cells, and I certainly understand how to create the filters. So two followup questions:
1. Almost all of the filter types seem to be working....except for Dates, and numbers. Text and Boolean type of filters work just fine.
1a. Dates: when the column contains the following, none of the date filters return any successful results
2/25/2018 10:58:39 AM2/10/2018 1:18:28 PM2/3/2018 4:20:58 PM1/7/2018 9:23:19 AM1/6/2018 4:57:37 PM1/6/2018 4:56:10 PM1/6/2018 3:55:04 PM1/6/2018 3:51:48 PM1/6/2018 2:41:18 PM1/6/2018 2:28:44 PM1/6/2018 2:28:35 PM1/6/2018 2:20:42 PM1/6/2018 2:19:58 PM11/11/2017 11:48:06 AM
1b. When the column contains the following, none of the numeric filters return any successful results:
2. When we try to apply a format string to cells, there does not appear to be any change to the way that the cell contents are displayed. For example, we know the the information being set for a column would be numeric, so we supply the following format mask:
theDisplay_Type = "Decimal"; Column_Format_String = "#,##0.00";
Inf_Active_Worksheet.Rows [ iDataRow ].Cells [ iDataCol ].CellFormat.FormatString = Column_Format_String;
Inf_Active_Worksheet.Rows [ iDataRow ].Cells [ iDataCol ].Value = the_Login.Dummy_Numeric_Field;
Reading the current format string from the spreadsheet indicates that the column format agrees with what we set. However the cell shows the number as.
12345678.1234 rather than as 12,345,678.1234
thoughts or suggestions?
Ok...after further experimentation it appears that the key to success lies in populating the values into the Cells as native C# variables rather than converting them to strings first. When that is done, both the formatting and numeric filters work correctly.
However, the Date filters do not work with the example above. Since these values are actually datetime variables rather then pure data variables would that prevent the Data Filters from operating correctly (example 1a above) ?
Yes in Excel and our spreadsheet controls, numeric formats will have no effect on the display if the values of the cell are strings so you should use numeric types (e.g. double, int) for numeric values and numeric values or DateTime for dates. Note though that dates in Excel are really just numeric values with the appropriate formatting applied. You can set the cell value to a DateTime though and our workbook will convert those to the corresponding numerical value as needed for its usage and serialization. Also since the values serialized out are just numeric if you reload the workbook you will just have a numeric (e.g. double) value returned from the cell's Value. There are some helper methods on the ExcelCalcValue class to helper convert between the two if needed such as DateTimeToExcelDate and ExcelDateToDateTime. So assuming you are setting the value of the cell to a number (e.g. double) or a DateTime, then the date filters should work. Perhaps the criteria you are using for the filter doesn't match the dates? Or are those still strings? They would appear to be in the original sample as they are left aligned but I'm not sure if you tried using DateTime when you switch the numeric values to using double, etc. If you are still having an issue then please post a sample of the data and filter criteria (or even the resulting workbook).