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
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.
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
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
Can you please update on this, it is very critical.
Provide a solution.
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.
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)
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.
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
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.