Ultra Win Grid Row cell formating

We are using ultrawinGrid.WinGrid in my windows form and we are not able to set format for a particular rows’s cell value. Please find the below scenario :

	Value 1	Value 2
row 1	10,000.00	10,000.00
row 2	16,849.00	89,236.00
row 3	21,235.00	87,329.00
row 4	88%	74%
row 5	14,234.00	25,547.00

In the above table all the rows will be in decimal format but we need display 4th row in percentage format.

Kindly provide soultion for this.

Hello Thangachan,

You should apply an editor to the cells you want to format. The Editor is the object that handles the value in the cell and its value has to be of a type that the cell's editor can handle. For example, if you want to format the cells of the third row as percentage, you should do something like this:

private void ultraGrid1_InitializeRow(object sender, InitializeRowEventArgs e)
        {
            if (e.Row.Index == 3)
            {
                var editorSettings = new DefaultEditorOwnerSettings();
                editorSettings.DataType = typeof(Decimal);
                editorSettings.Format = "0'%";
                var editor = new EditorWithText(new DefaultEditorOwner(editorSettings));
                e.Row.Cells["Value 1"].Editor = editor;
                e.Row.Cells["Value 2"].Editor = editor;

            }
        }

Please note that formatting only applies to a cell that is NOT in edit mode. If the cell is editable by the user and you need different masks based on the row, then you would have to use an editor – like an UltraMaskEditor or UltraNumericEditor control and set the Editor/EditorComponent on the cell.

Hi,

We have tried the sample code which have provided in  C#.net and converted to VB.net and tried we are not getting the expected format.

can you please provide sample vb.net project code for the same.

Hi Thangachan,

Here you are 

 Private Sub UltraGrid1_InitializeRow(sender As Object, e As Infragistics.Win.UltraWinGrid.InitializeRowEventArgs) Handles UltraGrid1.InitializeRow
        If e.Row.Index = 3 Then
            Dim EditorSettings As New DefaultEditorOwnerSettings
            EditorSettings.DataType = GetType([Decimal])
            EditorSettings.Format = "0'%"
            Dim editor = New EditorWithText(New DefaultEditorOwner(EditorSettings))
            e.Row.Cells("Value 1").Editor = editor
            e.Row.Cells("Value 2").Editor = editor
        End If
    End Sub

Hi,

Now we are getting the percenage format in the Grid but while exporting the grid percentage format is not coming in the excel.

Kindly send sample project for the same.

Hello Thangachan,

The grid cannot automatically export applied formats to Excel because Excel uses a different set of format strings. In your case since particular cells have the percentage format applied, you can handle the CellExporting event and translate the .Net format into the one that Excel can handle.

I have attached a sample VB project that uses the above approach and exports the grid to Excel applying percentage format to the first cell of the first row of the grid.

You may find more information about translating .NET FormatString to Excel FormatString in the following article in our online documentation ‘Transposing Rows and Columns using WinGrid Excel Exporter'

We have tried the below code provided by you in VB.NET:

Private Sub MyGridExporter_CellExporting(ByVal sender As Object, ByVal e As Infragistics.Win.UltraWinGrid.ExcelExport.CellExportingEventArgs) 'Handles MyGridExporter.CellExporting
Dim cell As Infragistics.Win.UltraWinGrid.UltraGridCell = e.GridRow.Cells(e.GridColumn)
Dim _theCellFormat As IWorksheetCellFormat = Nothing

If cell.Text = "LTIRA" Then
Dim theHeaderFormat As IWorksheetCellFormat = e.Workbook.CreateNewWorksheetCellFormat()
theHeaderFormat.FormatString = "0\%"
e.CurrentWorksheet.Rows(41).Cells(9).CellFormat.SetFormatting(theHeaderFormat)

End If

End Sub

But still we are not getting the percentage format in excel. We are following this to show percentage in a particular row.

Kindly provide sample VB.NET project with more detail code

Hi,

Can you please update on this, it is very critical.

Provide a solution.

Hi,

Looking in the provided code snippet, I understand that the cell that would be presented in percentage format is of type string and the value is “LTIRA”. Is that correct? There is no way to format text value as percentage format, because it has to be numeric format.

Looking forward to hear from you.

Hi,

The word LTIRA a keyword to search to set Percentage format but for the format we have set for the column as decimal, below is the code snippet:

Dim EditorSettings As New DefaultEditorOwnerSettings
EditorSettings.DataType = GetType([Decimal])
EditorSettings.Format = "###,###,###,##0.00%"
Dim editor = New EditorWithText(New DefaultEditorOwner(EditorSettings))

e.Row.Cells("fqtd_act_qtd").Editor = editor

The above code is in the Grid Event : InitializeRow and we are getting the format properly while export we have added the below code:

Private Sub MyGridExporter_CellExporting(ByVal sender As Object, ByVal e As Infragistics.Win.UltraWinGrid.ExcelExport.CellExportingEventArgs) 'Handles MyGridExporter.CellExporting
Dim cell As Infragistics.Win.UltraWinGrid.UltraGridCell = e.GridRow.Cells(e.GridColumn)

Dim _theCellFormat As IWorksheetCellFormat = Nothing
Dim theHeaderFormat As IWorksheetCellFormat = e.Workbook.CreateNewWorksheetCellFormat()
theHeaderFormat.FormatString = "0.00%;[Red](0.00%)"

e.CurrentWorksheet.Rows(41).Cells(2).CellFormat.SetFormatting(theHeaderFormat)

End Sub

In the CellExporting event how we need find for each cell what is the format assigned in the grid.

Please let us know and provide sample project in VB.net.

Hello Thangachan,

You cannot check the cell format in the CellExporting event, but you can get the CurrentColumnIndex and the CurrentRowIndex and check the cell based on this as in the sample provided.

Private Sub UltraGridExcelExporter1_CellExporting(sender As Object, e As UltraWinGrid.ExcelExport.CellExportingEventArgs) Handles UltraGridExcelExporter1.CellExporting

        If e.CurrentColumnIndex = 2 And e.CurrentRowIndex = 41 Then
            e.Cancel = True
            If _theCellFormat Is Nothing Then
                _theCellFormat = e.Workbook.CreateNewWorksheetCellFormat()
                _theCellFormat.FormatString = "0.00\% [Red]"
            End If

            Dim Worksheet = TryCast(e.CurrentWorksheet, Worksheet)
            Worksheet.Rows(41).Cells(2).Value = e.GridValue
            Worksheet.Rows(41).Cells(2).CellFormat.SetFormatting(_theCellFormat)

        End If
    End Sub

Also, have in mind that the CellExporting event occurs before the cell is exported in excel. So, you can apply formatting on the currently exported cell. If you have to check the value of another cell of the same row as in the previous example with the "LTIRA" cell, you can handle the RowExporting event.