Page 1 of 2

Formatting cells of a DataGridView best place for the code?

Posted: Tue Aug 06, 2019 11:39 pm
by FrankAndrew
Hi Everyone,

Now that I have really dived into the Windows Forms usage through PowerShell Studio I have a question in regards to the best code loaction placement for formatting the cell contents in a DataGridView based on the cells value.

The place where I current have the formatting and the setting of the cell colors is in the GREAT SAPIEN function: Update-DataGridView
But I also need and want to have the column sorting functionality of the DataTable so I am also using that other GREAT SAPEIN function: ConvertTo-DataTable

When I first load my DataGridView by converting it to a DataTable using ConvertTo-DataTable and then updating the DataGridView using Update-DataGridView the coloring logic that I added to the bottom of the later function works just fine until I click on one of the column headers to re-sort the columns to the new desired order.

I have tried to look at the hundereds of Events and Methods and such that the DataGridView offers for handling these object and I don't have all the time needed to test through each and every one of these until I find the right one.

So can someone help me to find the Right Event or method that I should use when I want the cell Contents to be conditionally formatted based on the cells value?

I don't want to post the actual code here due to sensitive Information in it. I don't actually use the column numbers for the coloring as listed below but the column header text so that the column number does NOT matter.

Examples:
For columns 6, 7, 8 and 9 the default colors are Background 'White', Foreground 'Black'.

These four columns have an integer value that I would also like to have formatted for the display purposes like a string "{0:N0}" but for the sorting it should use the integer value because string sorting of such value is not nice at all as you know.
I have set the cell Alignment to: 'MiddleRight'

If column 6 has a value of 0 then the foreground Color should be 'Green' with the default Background Color, if it is greater than zero then the normal default Colors.

If column 7 is greater than 0 then the Background Color should be 'Green' and the Foreground Color 'White',

If column 8 if the value is greater than 0 then the Background Color should be 'Yellow' foreground 'Black',

If column 9 if the value is greater than o then the Background Color should be 'Red' and the foreground Color 'White'.

Thanks in Advance...

Re: Formatting cells of a DataGridView best place for the code?

Posted: Wed Aug 07, 2019 5:41 am
by jvierra
Use the cells "formatting" event to change cells color.

Code: Select all

$datagridview1_CellFormatting=[System.Windows.Forms.DataGridViewCellFormattingEventHandler]{
#Event Argument: $_ = [System.Windows.Forms.DataGridViewCellFormattingEventArgs]
    if($_.Value -eq 0){
	    $_.CellStyle.BackColor = 'Red'
        $_.CellStyle.ForeColor = 'White'
    }else{
        $_.CellStyle.BackColor = 'Blue'
        $_.CellStyle.ForeColor = 'Pink'
    }
}

Re: Formatting cells of a DataGridView best place for the code?

Posted: Wed Aug 07, 2019 5:54 am
by FrankAndrew
Hi jvierra,

Thanks for the quick reply.

Do I see that correctly that this is a new defined function that could be called from anywhere?
If this is a free standing function how do I associate it to a specific cell or column of cells?

Re: Formatting cells of a DataGridView best place for the code?

Posted: Wed Aug 07, 2019 6:05 am
by jvierra

Re: Formatting cells of a DataGridView best place for the code?

Posted: Wed Aug 07, 2019 6:48 am
by FrankAndrew
Hi jvierra,

Sorry to bother you again with a beginners question in the MSDN help for the DataGridView_CellFormatting function it shows:

Code: Select all

 void dataGridView1_CellFormatting( Object^ /*sender*/, DataGridViewCellFormattingEventArgs^ e )
How do I get that information for the DataGridViewCellFormattingEventArgs Parameter inside the function like you wrote it so that I can check which column I need to handle, being as I have differnet colors for differnet columns and there is only one CellFormatting event for the entire DataGridView or maybe not just one??

Do I just have to define a PARAM() section at the top of it for those two Parameters or are they just there and available through the $this variable?

Re: Formatting cells of a DataGridView best place for the code?

Posted: Wed Aug 07, 2019 7:07 am
by FrankAndrew
I think I now understand why you added this line:

Code: Select all

#Event Argument: $_ = [System.Windows.Forms.DataGridViewCellFormattingEventArgs]
That is the e-> from the MSDN help.

Thanks for your help

Re: Formatting cells of a DataGridView best place for the code?

Posted: Wed Aug 07, 2019 7:13 am
by jvierra
What I posted is not a function. It is the script block that Sapien adds to the controls event when the scrip is built.

The comment in the script block explains what is available. PLease read the two links I posted very carefully until you understand what is being discussed and why. Until you understand how Forms work with PowerShell you will have a very hard time knowing how to use MSDN docs and how to design forms.

Consider the following:

sender = $this
EventArgs = $_


When an event is tied to a form in PSS then intellisense and autocomplete work as expected.

Re: Formatting cells of a DataGridView best place for the code?

Posted: Wed Aug 07, 2019 7:16 am
by jvierra
FrankAndrew wrote: Wed Aug 07, 2019 7:07 am I think I now understand why you added this line:

Code: Select all

#Event Argument: $_ = [System.Windows.Forms.DataGridViewCellFormattingEventArgs]
That is the e-> from the MSDN help.

Thanks for your help
I didn't add that. I just created the event in PSS and added the color assignments. Everything else is done by the PSS IDE.

Re: Formatting cells of a DataGridView best place for the code?

Posted: Wed Aug 07, 2019 7:36 am
by jvierra
Please read the following short document:

Re: Formatting cells of a DataGridView best place for the code?

Posted: Wed Aug 07, 2019 11:04 am
by DrewEaston
Hi jvierra,

SUPER thanks for the help and tips. This will make me a MORE effective programmer with Windows Forms.

Using your examples I was able to get the formatting working as I need it.

In case others are looking for an example as I was, here is what I build based on your example and the MSDN help:

Code: Select all

$datagridview1_CellFormatting=[System.Windows.Forms.DataGridViewCellFormattingEventHandler]{
   #Event Argument: $_ = [System.Windows.Forms.DataGridViewCellFormattingEventArgs]
   If ($this.Columns[$_.ColumnIndex].Name.ToUpper().Equals("ARTIST")) {
      If ($_.Value -eq 0){
         $_.CellStyle.BackColor = 'Red'
         $_.CellStyle.ForeColor = 'White'
      } Else {
         $_.CellStyle.BackColor = 'Blue'
         $_.CellStyle.ForeColor = 'Pink'
      }
   }
}
That is my give back for this problem that I was having.

I hope this shows others how to do column specific color formatting.

GREAT Help thanks again!