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

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

Please login to follow topic

einkauf
- Post Options:
- Link
  Copy
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}

Which date-time format for German region is valid? Regards ISD-Internet Dresden, Saxony
avnish.kumar
- Post Options:
- Link
  Copy
Posted 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
einkauf
- Post Options:
- Link
  Copy
Posted 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
avnish.kumar
- Post Options:
- Link
  Copy
Posted 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]
avnish.kumar
- Post Options:
- Link
  Copy
Posted 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

einkauf

Post Options:
Link
Copy

Posted 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

avnish.kumar
- Post Options:
- Link
  Copy
Posted 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

einkauf

Post Options:
Link
Copy

Posted 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)

[i]
'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.
[/i] 

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

#	english	german

| TEXT(Value you want to format, “Format code you want to apply”) | TEXT(Value you want to format;“Format code to be applied”)
| 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 [b]comma [/b]for the [b]semicolon[/b], 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

Please login to reply to thread

Need extra support?

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

Learn More

Forum Channels

ComponentOne

Forums for all current editions of the ComponentOne .NET UI control product line, including ComponentOne Studio and ComponentOne Studio for Xamarin.
ActiveReports

Forums for all versions of ActiveReports and ActiveReports Server
Spread

Forums for all current versions of Spread .NET spreadsheets, SpreadJS JavaScript spreadsheets, and SpreadCOM spreadsheets.
Wijmo

Forums for all Wijmo products, including Wijmo Core, FinancialChart, FlexSheet, MultiRow, OLAP, and ReportViewer
- General Discussion
Document Solutions

Forums for all Document Solutions products, including Document Solutions for PDF, Word, Excel (.NET and Java), and Imaging.