sort dgv and keep color + export with color

[HL_1992]

Hello,

I have a dgv and can sort this dgv with click at header.

Some rows are formatted (background color, font color).

How can I keep the rows formatted at sort?
How can I export the formatted dgv (xlsx or csv)? With "... | select -expand DataBoundItem | export-csv ..." I can export the dgv without formatted rows.

Thanks for help

Regards, Hannes

[jvierra]

You have to apply formatting during the CellFormatting event. It will be applied when the grid is reloaded after a sort.

A CSV file has no capability of being formatted as it is plain text. TO export to an Excel file you will need to write a custom export function to capture and convert the formatting as required by your design. There is no direct translator.

[rmckay9969]

This is something I had been contemplating a while back but as of yet haven't had a chance to investigate. I had the idea that a CSV could contain a column or columns created containing cell formatting data (which you would have to define) that are not loaded into the datagridview, but are instead called upon to provide the formatting based off of cell index/back color and so on. Of course this would all have to be created from scratch but in theory it seems possible. If I have any success I will post back.

[HL_1992]

I managed to export in xlsx with PSExcel.
First Export without color, then I looped every cell in dgv (style) and formatted the xlsx with PSExcel.

Now I look at applystyle.

Regards, Hannes

edit:
Applystyle didnt work, after sort the style is empty. I formatted the cells and execute this code:

foreach ($a in $datagridview.Rows)
{
foreach ($b in $a.Cells)
{
$b.Style.ApplyStyle($b.Style)
}
}

Any ideas?

[jvierra]

As noted above. We normally set formatting in the "CellFormatting" event. The design of the control is to use Cell Formatting to apply a format to the cell every time too cell is changed. If you use the control correctly this will keep the formatting even on a sort or reload.

We usually design the formatting of a cell to be dependent on values within a row and/or values available externally to the control. If you design it that way everything else will be handled automatically.

Using this method the format of the cell never needs to be saved. Just save the configuration data that drives the formatting in an XML file or an INI file and reload with the data.

Things that drive formatting can be values, Booleans, value ranges, dates, date ranges, text matches or any other logical or mathematical test.

[HL_1992]

I misunderstood you.

With the "CellFormatting" event it works, but this event will execute multiple times.
I used the VisibleChanged event.

But now it works

Thanks

[jvierra]

It executes any time a cell is rebuilt. That is why it is able to keep the formatting when a DGV is sorted. That is why you need to have rules that determine the cells format..

[HL_1992]

you're right, but I dont like it

I've added "Sorted" event. This is executed ones.

[jvierra]

you're right, but I dont like it

I've added "Sorted" event. This is executed ones.

Except that that won't work for any but the narrowest set of grids. It is not how the grid was designed to be used.

The interesting thing is that you thought the format cell was going to be called many times. If you set the cell format in the "sorted" event it will cause more than twice as many cell formatting calls. cellFormatting is called for every cell every time the grid id loaded or sorted and any time a cell is changed. By managing the CellFormatting we can capture and save the formatting and reapply it when needed on a cell specific basis.

If what you are doing works for you then use it. Just keep in mind that it will not work correctly in other situations or if you change the grid.

If you look at the properties you will see that the cell format and style can be tied to a data table or object. This is how we can also persist format information.

Using the data values and rules is the easiest and most efficient method for maintaining style.

[HL_1992]

Ok, thanks for advice.

The problem is solved .