What are the values for argument format methode SetCellAggregationFormat()?

Posted by: einkauf on 28 April 2022, 10:32 pm EST

  • Posted 28 April 2022, 10:32 pm EST

    Hello,

    the value for argument
    format
    the methode
    SetCellAggregationFormat()
    must minimal the string
    {0}
    contain. That's ok. Format for number, example
    {0:f2}
    , is ok, too. But format for date-time, example
    {0:dd.MM.yyyy HH:mm:ss}
    (German date-time format), show not the values in spread.

    Which date-time format for German region is valid?

    Regards

    ISD-Internet Dresden, Saxony
  • Replied 1 May 2022, 6:49 pm EST

    Hi,

    Could you please share a small sample replicating the issue? It'll help us investigate the issue at our end and help you accordingly.

    Regards
    Avnish
  • Replied 2 May 2022, 12:11 am EST

    Hello,

    thank you for your mail. I have added a small example programme to the appendix. I hope this will help you to understand my problem. Many thanks in advance.

    With kind regards

    ISD-Internet Dresden, Saxony

    HelloSpreadGrouping.zip
  • Replied 3 May 2022, 9:29 pm EST

    Hi,

    The Aggregation result is a double value i.e the OLE Date that is why the format is not being applied as it would on a DateTime value. We have escalated the issue to the development team to get their insights on the issue and how your requirement can be achieved. We will let you know as soon as we have an update.

    Regards
    Avnish

    [Internal Tracking ID : SPNET-23859]
  • Replied 5 May 2022, 8:36 pm EST

    Hi,

    As per the development team, since the aggregate result is a double value, the date format cannot be applied to it.
    As a workaround, to achieve your requirements, you can change the formula of the cell of the group footer and transform the result of the subtotal to a date value. Please refer to the sample attached.

    When you set the aggregate type for a cell of the group footer, the relevant formula is also set in that cell. So we can get the formula using the GetFormula() method of the AggregationDataModel and set the modified formula using the SetFormula() method.
    Please note that if you use the GetCellAggregarionType() method after the formula modification then it will return AggregationType as custom.

    Regards
    Avnish

    spread_mod.zip
  • Replied 16 May 2022, 12:55 am EST

    Hello Avnish,

    Thank you very much for your efforts. I did not immediately understand the use of the formulas and through trial and error came up with a simple working solution. Suppose there is a table with 30 rows:


    | ColumnType: Number | ColumnType: String | ColumnType: Date | ColumnType: String | ColumnType: String | ColumnType: Number |
    | ID | EDITTYPE | EDITA | EDITBY | EDITLOCATION | ColumnA |
    |----------------------|----------------------|----------------------|----------------------|----------------------|----------------------|
    | 1 | 0 | 15.05.2022 | S.Sample man | MADS | 1 |
    | 2 | 0 | 14.05.2022 | A.Sample man | MADS | 8 |
    | 3 | 0 | 13.05.2022 | M.Sample man | MADS | 27 |
    | ... | ... | ... | ... | ... | ... |


    The content of the File Form1.vb:


    Public Class Form1
    Private m_Table As New DataTable()
    Sub CreateColumns(ByVal editable As Boolean)
    Me.m_Table.Columns.Add(New DataColumn() With {.ColumnName = "ID", .DataType = GetType(Long), .AutoIncrement = True, .AutoIncrementSeed = 1})
    Me.m_Table.Columns.Add(New DataColumn() With {.ColumnName = "EDITTYPE", .DataType = GetType(Char), .ReadOnly = Not editable})
    Me.m_Table.Columns.Add(New DataColumn() With {.ColumnName = "EDITA", .DataType = GetType(Date), .ReadOnly = Not editable})
    Me.m_Table.Columns.Add(New DataColumn() With {.ColumnName = "EDITBY", .DataType = GetType(String), .ReadOnly = Not editable})
    Me.m_Table.Columns.Add(New DataColumn() With {.ColumnName = "EDITLOCATION", .DataType = GetType(String), .ReadOnly = Not editable})
    Me.m_Table.Columns.Add(New DataColumn() With {.ColumnName = "ColumnA", .DataType = GetType(Decimal), .ReadOnly = Not editable})
    End Sub
    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    Me.CreateColumns(editable:=False)
    Me.spr_Main_Sheet1.DataSource = (New Testdata(Me.m_Table, rowCount:=30).Table)
    Dim gdm As New GroupDataModel(Me.spr_Main_Sheet1.Models.Data)
    Dim sortInfos As New List(Of SortInfo)()
    sortInfos.Add(New SortInfo(3, ascending:=True)) ' Sorted by column EDITBY
    gdm.Group(sortInfos.ToArray())
    gdm.GroupFooterVisible = True
    Me.spr_Main_Sheet1.Models.Data = gdm
    End Sub
    Private Sub Form1_Shown(sender As Object, e As EventArgs) Handles Me.Shown
    Dim gdm = DirectCast(Me.spr_Main_Sheet1.Models.Data, GroupDataModel)
    ' The format dd.mm.yyyy using dots (which denote ordinal numbering) is the traditional German date format, see here https://en.wikipedia.org/wiki/Date_format_by_country
    Dim dateFormat = "dd.MM.yyyy"
    Me.spr_Main_Sheet1.Columns(2).CellType = New CellType.DateTimeCellType() With {.DateTimeFormat = CellType.DateTimeFormat.UserDefined, .UserDefinedFormat = dateFormat}

    For i = 0 To gdm.Groups.Count - 1
    Dim group = DirectCast(gdm.Groups(i), Group)
    DirectCast(group.GroupFooter.DataModel, IAggregationSupport).SetCellAggregationType(row:=0, col:=2, type:=AggregationType.Max) ' column EDITA
    DirectCast(group.GroupFooter.DataModel, IAggregationSupport).SetCellAggregationType(row:=0, col:=5, type:=AggregationType.Max) ' column ColumnA
    ' in German region is a decimal separator a , see here https://en.wikipedia.org/wiki/Decimal_separator and the , is also displayed. Example: "24389,00" This is ok.
    DirectCast(group.GroupFooter.DataModel, IAggregationSupport).SetCellAggregationFormat(row:=0, col:=5, format:="{0:0.00}")
    For primaryRowIndex = 0 To group.Rows.Count - 1
    If TypeOf group.Rows(primaryRowIndex) Is Integer Then
    Dim dm = DirectCast(group.GroupFooter.DataModel, AggregationDataModel)
    Dim value = dm.GetValue(row:=0, col:=2) ' column EDITA
    If TypeOf value Is Double Then
    dm.SetValue(row:=0, col:=2, Date.FromOADate(Convert.ToDouble(value)).ToString(dateFormat))
    End If
    End If
    Next
    Next
    End Sub
    End Class


    Grouping is done by the EDITBY column and the EDITA and ColumnA columns have group feet. These group footers are now perfectly formatted by my solution above.


    | ID | EDITTYPE | EDITA | EDITBY | EDITLOCATION | ColumnA |
    |----------------------|----------------------|----------------------|----------------------|----------------------|----------------------|
    | 2 | 0 | 14.05.2022 | A.Sample man | MADS | 8 |
    | 27 | 0 | 19.04.2022 | A.Sample man | MADS | 19683 |
    | 9 | 0 | 07.05.2022 | A.Sample man | MADS | 729 |
    | 11 | 0 | 05.05.2022 | A.Sample man | MADS | 1331 |
    | 20 | 0 | 26.04.2022 | A.Sample man | MADS | 8000 |
    | 18 | 0 | 28.04.2022 | A.Sample man | MADS | 5832 |
    | 29 | 0 | 17.04.2022 | A.Sample man | MADS | 24389 |
    | | | 14.05.2022 | | | 24389 |
    | EDITBY: E.Sample man | EDITBY: E.Sample man | EDITBY: E.Sample man | EDITBY: E.Sample man | EDITBY: E.Sample man | EDITBY: E.Sample man |
    | 15 | 0 | 01.05.2022 | E.Sample man | MADS | 3375 |
    | 6 | 0 | 10.05.2022 | E.Sample man | MADS | 216 |
    | 24 | 0 | 22.04.2022 | E.Sample man | MADS | 13824 |
    | | | 10.05.2022 | | | 13824 |
    | EDITBY: L.Sample man | EDITBY: L.Sample man | EDITBY: L.Sample man | EDITBY: L.Sample man | EDITBY: L.Sample man | EDITBY: L.Sample man |
    | 5 | 0 | 11.05.2022 | L.Sample man | MADS | 125 |
    | 23 | 0 | 23.04.2022 | L.Sample man | MADS | 12167 |
    | 14 | 0 | 02.05.2022 | L.Sample man | MADS | 2744 |
    | | | 11.05.2022 | | | 12167 |
    | EDITBY: M.Sample man | EDITBY: M.Sample man | EDITBY: M.Sample man | EDITBY: M.Sample man | EDITBY: M.Sample man | EDITBY: M.Sample man |
    | 26 | 0 | 20.04.2022 | M.Sample man | MADS | 17576 |
    | 21 | 0 | 25.04.2022 | M.Sample man | MADS | 9261 |
    | 17 | 0 | 29.04.2022 | M.Sample man | MADS | 4913 |
    | 30 | 0 | 16.04.2022 | M.Sample man | MADS | 27000 |
    | 12 | 0 | 04.05.2022 | M.Sample man | MADS | 1728 |
    | 8 | 0 | 08.05.2022 | M.Sample man | MADS | 512 |
    | 3 | 0 | 13.05.2022 | M.Sample man | MADS | 27 |
    | | | 13.05.2022 | | | 27000 |
    | EDITBY: P.Sample man | EDITBY: P.Sample man | EDITBY: P.Sample man | EDITBY: P.Sample man | EDITBY: P.Sample man | EDITBY: P.Sample man |
    | 13 | 0 | 03.05.2022 | P.Sample man | MADS | 2197 |
    | 22 | 0 | 24.04.2022 | P.Sample man | MADS | 10648 |
    | 4 | 0 | 12.05.2022 | P.Sample man | MADS | 64 |
    | | | 12.05.2022 | | | 10648 |
    | EDITBY: S.Sample man | EDITBY: S.Sample man | EDITBY: S.Sample man | EDITBY: S.Sample man | EDITBY: S.Sample man | EDITBY: S.Sample man |
    | 19 | 0 | 27.04.2022 | S.Sample man | MADS | 6859 |
    | 10 | 0 | 06.05.2022 | S.Sample man | MADS | 1000 |
    | 28 | 0 | 18.04.2022 | S.Sample man | MADS | 21952 |
    | 1 | 0 | 15.05.2022 | S.Sample man | MADS | 1 |
    | | | 15.05.2022 | | | 21952 |
    | EDITBY: X.Sample man | EDITBY: X.Sample man | EDITBY: X.Sample man | EDITBY: X.Sample man | EDITBY: X.Sample man | EDITBY: X.Sample man |
    | 7 | 0 | 09.05.2022 | X.Sample man | MADS | 343 |
    | 25 | 0 | 21.04.2022 | X.Sample man | MADS | 15625 |
    | 16 | 0 | 30.04.2022 | X.Sample man | MADS | 4096 |
    | | | 09.05.2022 | | | 15625 |



    My problem is therefore considered solved.

    With kind regards

    ISD-Internet Dresden, Saxony
  • Replied 17 May 2022, 2:26 pm EST

    Hi,

    Good to hear that you were able to come up with a working solution.
    Regarding the use of formulas, the Aggregate footer cells have a SUBTOTAL formula according to what the aggregate type and the cells included in the aggregation. In our solution, we used the TEXT formula to convert the result of the SUBTOTAL formula to date format.

    Regards
    Avnish
  • Marked as Answer

    Replied 18 May 2022, 12:59 am EST

    Hello Avnish,

    thank you very much for your efforts. I have now found out why your solution did not work for me. There is Babylonian confusion of languages (For what the Europeans mean by this, see: https://en.wikipedia.org/wiki/Tower_of_Babel#Confusion_of_tonguesv).

    You wrote the following line of code:
    Dim exp = $"TEXT({oldExp}, ""dd.MM.yyyy HH:mm:ss"")"


    By the above code line I get an following FarPoint.Win.Spread.Model.ParseException in the line
    dm.SetFormula(0, 2, exp)
    on a Windows with German language setting:

    'TEXT(TEILERGEBNIS(4;Sheet1!C2:C2;Sheet1!C27:C27;Sheet1!C9:C9;Sheet1!C11:C11;Sheet1!C20:C20;Sheet1!C18:C18;Sheet1!C29:C29) "dd.MM.yyyy HH:mm:ss")' is not a valid formula.


    Since I did not find the formula functions in the spread documentation (FarPoint.Win.Spread.15.0.chm), I use the formula functions on the Microsoft documentation pages. Formula function names (I have translated everything in the following table into English except for the formula names):

    # | english | german
    ---|-----------------------------------------------------------------|-----------------------------------------------------------
    1. | TEXT(Value you want to format, "Format code you want to apply") | TEXT(Value you want to format;"Format code to be applied")
    2. | SUBTOTAL(function_num,ref1,[ref2],...) | TEILERGEBNIS(Function_num, Reference1;[Reference2];...)


    [1 - english]: https://support.microsoft.com/en-us/office/text-function-20d5ac4d-7b94-49fd-bb38-93d29371225c
    [1 - german] : https://support.microsoft.com/de-de/office/text-funktion-20d5ac4d-7b94-49fd-bb38-93d29371225c
    [2 - english]: https://support.microsoft.com/en-us/office/subtotal-function-7b027003-f060-4ade-9040-e478765b9939
    [2 - german] : https://support.microsoft.com/de-de/office/teilergebnis-funktion-7b027003-f060-4ade-9040-e478765b9939

    By swapping the comma for the semicolon, Anvish's code finally worked:
    Dim exp As String = $"TEXT({oldExp}; ""dd.MM.yyyy HH:mm:ss"")"


    Anish, it wasn't you, but the poor documentation of FpSpread that I couldn't get your code to work right away.

    With kind regards

    ISD-Internet Dresden, Saxony
Need extra support?

Upgrade your support plan and get personal unlimited phone support with our customer engagement team

Learn More

Forum Channels