Export Xamdatagrıd with bacground color of cells

430

Onur posted over 11 years ago

Hello

I have a grid with different cell background color which are defined with styles. But when I export grid to excel the below part of the grid in the screen does not have background color. I exported to excel after CVP created. I also set RecordContainerGenerationMode="PreLoad" but nothing changed. Here my xaml and export function. Suppose My grid has 1000 rows. You can see first 24 rows if you dont use scroll bar. When I export only the first 24 rows have the colors. 25th and below rows have null cellvaluepresenters.

Thanks


tempGrid = (XamDataGrid)gridArrayList[0];
 foreach (var item in tempGrid.DataSource)
 {
 
 var Record = tempGrid.GetRecordFromDataItem(item, false);//
DataRecord dr = Record as DataRecord;
 
 foreach (Cell c in dr.Cells)
 {
CellValuePresenter cvp1 = CellValuePresenter.FromRecordAndField(dr, c.Field);
 if (cvp1 != null)
 {
 var color = cvp1.Background;
 SolidColorBrush colorSolid = (SolidColorBrush)cvp1.Background;
 System.Drawing.Color drawingColor = System.Drawing.Color.FromArgb(colorSolid.Color.A, colorSolid.Color.R, colorSolid.Color.G, colorSolid.Color.B);
 worksheet.Rows[dr.Index].Cells[c.Field.Index].CellFormat.FillPatternForegroundColor = drawingColor;
 }
 else
 {
 var color = Colors.White;
 System.Drawing.Color drawingColor = System.Drawing.Color.FromArgb(255, 255, 255, 255);
 worksheet.Rows[dr.Index].Cells[c.Field.Index].CellFormat.FillPatternForegroundColor = drawingColor;
 }

 }
 }


<UserControl.Resources>
 <Style x:Key="AlisEmirBG" TargetType="{x:Type igDP:CellValuePresenter}">
 <Setter Property="Background" Value="LightCyan"/>
 <Style.Triggers>
 <DataTrigger Binding="{Binding RelativeSource={RelativeSource Self}, Path=Record.DataItem[15]}" Value="ISLEM">
 <Setter Property="Background" Value="Turquoise" />
 </DataTrigger>
 </Style.Triggers>
 </Style>
<Style x:Key="SatisEmirBG" TargetType="{x:Type igDP:CellValuePresenter}">
 <Setter Property="Background" Value="Orange"/>
 <Style.Triggers>
 <DataTrigger Binding="{Binding RelativeSource={RelativeSource Self}, Path=Record.DataItem[15]}" Value="ISLEM">
 <Setter Property="Background" Value="Turquoise" />
 </DataTrigger>
 </Style.Triggers>
 </Style>
<Style x:Key="IslemBG" TargetType="{x:Type igDP:CellValuePresenter}">
 <Setter Property="Background" Value="LightGreen"/>
 <Style.Triggers>
 <DataTrigger Binding="{Binding RelativeSource={RelativeSource Self}, Path=Record.DataItem[15]}" Value="ISLEM">
 <Setter Property="Background" Value="Turquoise" />
 </DataTrigger>
 </Style.Triggers>
 </Style>
<Style x:Key="DecimalStyle" TargetType="{x:Type igEditors:XamCurrencyEditor}">
 <Setter Property="Format" Value="N"/>
 </Style>
 
 <Style x:Key="DecimalStyleMiktar" TargetType="{x:Type igEditors:XamCurrencyEditor}">
 <Setter Property="Format" Value="N0"/>
 </Style>
 
 <Style x:Key="NumberStyle" TargetType="{x:Type igEditors:XamCurrencyEditor}">
 <Setter Property="Format" Value="G"></Setter>
 </Style>
<Style x:Key="ButtonField" TargetType="{x:Type igDP:CellValuePresenter}">
 <Setter Property="Template">
 <Setter.Value>
 <ControlTemplate TargetType="{x:Type igDP:CellValuePresenter}">
 <Button Tag="{Binding RelativeSource={RelativeSource TemplatedParent}, Path=Record.DataItem[18]}" Click="Button_Click" Width="15" Height="15" ToolTip="Bekleyen Emirler" >
 <Image Source="/ResourcesLib;component/Images/zoomIn.png"/>
 </Button>
 </ControlTemplate>
 </Setter.Value>
 </Setter>
 </Style>
 </UserControl.Resources>
 <Grid x:Name="LayoutRoot">
 <Grid.ContextMenu>
 <ContextMenu>
 <MenuItem Name="btnExport" Header="Excele Aktar" Click="btnExport_Click" />
 </ContextMenu>
 </Grid.ContextMenu>
 <igDP:XamDataGrid Theme="IGTheme" RecordContainerGenerationMode="PreLoad" x:Name="seansSimulasyonuXamdatagrid" DataSource="{Binding Result}" FontSize="10">
 <igDP:XamDataGrid.FieldSettings>
 <igDP:FieldSettings AllowRecordFiltering="true" AllowEdit="False" LabelTextWrapping="WrapWithOverflow"
 LabelTextAlignment="Center" AllowLabelVirtualization="True" LabelTextTrimming="None"
 Width="Auto" Height="Auto" AutoSizeOptions="All" FilterOperatorDefaultValue="Contains"/>
 </igDP:XamDataGrid.FieldSettings>
 <igDP:XamDataGrid.FieldLayoutSettings>
 <igDP:FieldLayoutSettings FilterUIType="FilterRecord"
 HeaderPrefixAreaDisplayMode="FieldChooserButton"
 AllowClipboardOperations="Copy" AutoGenerateFields="False" AutoFitMode="Always" >
 </igDP:FieldLayoutSettings>
 </igDP:XamDataGrid.FieldLayoutSettings>
 <igDP:XamDataGrid.FieldLayouts>
 <igDP:FieldLayout>
 <igDP:FieldLayout.Fields>
 <igDP:UnboundField Label="#" FixedLocation="FixedToNearEdge" IsExpandable="False">
 <igDP:UnboundField.Settings>
 <igDP:FieldSettings CellValuePresenterStyle="{StaticResource ButtonField}" />
 </igDP:UnboundField.Settings>
 </igDP:UnboundField>
 <igDP:Field Name="ZAMAN" Label="Zaman"/>
 <igDP:Field Name="ISL_NO" Label="İşlem No">
 <igDP:Field.Settings>
 <igDP:FieldSettings AllowEdit="False" EditorStyle="{StaticResource NumberStyle}">
 </igDP:FieldSettings>
 </igDP:Field.Settings>
 </igDP:Field>
<igDP:Field Name="A_EM_FYT" Label="A.Emir Fiyat">
 <igDP:Field.Settings>
 <igDP:FieldSettings AllowEdit="False" CellValuePresenterStyle="{StaticResource AlisEmirBG}" EditorStyle="{StaticResource DecimalStyle}">
 </igDP:FieldSettings>
 </igDP:Field.Settings>
 </igDP:Field>
 <igDP:Field Name="A_EM_MKT" Label="A.Emir Miktar">
 <igDP:Field.Settings>
 <igDP:FieldSettings AllowEdit="False" CellValuePresenterStyle="{StaticResource AlisEmirBG}" EditorStyle="{StaticResource DecimalStyleMiktar}">
 </igDP:FieldSettings>
 </igDP:Field.Settings>
 </igDP:Field>
<igDP:Field Name="A_UYEHESNO" Label="A.ÜyeHesapNo">
 <igDP:Field.Settings>
 <igDP:FieldSettings AllowEdit="False" CellValuePresenterStyle="{StaticResource AlisEmirBG}">
 </igDP:FieldSettings>
 </igDP:Field.Settings>
 </igDP:Field>
<igDP:Field Name="A_ISIM" Label="A.İsim">
 <igDP:Field.Settings>
 <igDP:FieldSettings AllowEdit="False" CellValuePresenterStyle="{StaticResource AlisEmirBG}">
 </igDP:FieldSettings>
 </igDP:Field.Settings>
 </igDP:Field>
<igDP:Field Name="A_EM_NO" Label="A.Emir No">
 <igDP:Field.Settings>
 <igDP:FieldSettings AllowEdit="False" CellValuePresenterStyle="{StaticResource AlisEmirBG}" EditorStyle="{StaticResource NumberStyle}">
 </igDP:FieldSettings>
 </igDP:Field.Settings>
 </igDP:Field>
<igDP:Field Name="I_FYT" Label="İ.Fiyat">
 <igDP:Field.Settings>
 <igDP:FieldSettings AllowEdit="False" CellValuePresenterStyle="{StaticResource IslemBG}" EditorStyle="{StaticResource DecimalStyle}">
 </igDP:FieldSettings>
 </igDP:Field.Settings>
 </igDP:Field>
 <igDP:Field Name="I_MKT" Label="İ.Miktar">
 <igDP:Field.Settings>
 <igDP:FieldSettings AllowEdit="False" CellValuePresenterStyle="{StaticResource IslemBG}" EditorStyle="{StaticResource DecimalStyleMiktar}">
 </igDP:FieldSettings>
 </igDP:Field.Settings>
 </igDP:Field>
 
 <igDP:Field Name="S_UYEHESNO" Label="S.ÜyeHesapNo">
 <igDP:Field.Settings>
 <igDP:FieldSettings AllowEdit="False" CellValuePresenterStyle="{StaticResource SatisEmirBG}">
 </igDP:FieldSettings>
 </igDP:Field.Settings>
 </igDP:Field>
<igDP:Field Name="S_ISIM" Label="S.İsim">
 <igDP:Field.Settings>
 <igDP:FieldSettings AllowEdit="False" CellValuePresenterStyle="{StaticResource SatisEmirBG}">
 </igDP:FieldSettings>
 </igDP:Field.Settings>
 </igDP:Field>
<igDP:Field Name="S_EM_NO" Label="S.Emir No">
 <igDP:Field.Settings>
 <igDP:FieldSettings AllowEdit="False" CellValuePresenterStyle="{StaticResource SatisEmirBG}" EditorStyle="{StaticResource NumberStyle}">
 </igDP:FieldSettings>
 </igDP:Field.Settings>
 </igDP:Field>
<igDP:Field Name="S_EM_FYT" Label="S.Emir Fiyat">
 <igDP:Field.Settings>
 <igDP:FieldSettings AllowEdit="False" CellValuePresenterStyle="{StaticResource SatisEmirBG}" EditorStyle="{StaticResource DecimalStyle}">
 </igDP:FieldSettings>
 </igDP:Field.Settings>
 </igDP:Field>
<igDP:Field Name="S_EM_MKT" Label="S.Emir Miktar">
 <igDP:Field.Settings>
 <igDP:FieldSettings AllowEdit="False" CellValuePresenterStyle="{StaticResource SatisEmirBG}" EditorStyle="{StaticResource DecimalStyleMiktar}">
 </igDP:FieldSettings>
 </igDP:Field.Settings>
 </igDP:Field>
<igDP:Field x:Name="TipElement" Name="TIP" Label="Tip"/>
 </igDP:FieldLayout.Fields>
 </igDP:FieldLayout>
 </igDP:XamDataGrid.FieldLayouts>
 </igDP:XamDataGrid>
 <extToolkit:BusyIndicator IsBusy="{Binding Path=IsBusy}" BusyContent="Simülasyon Hazırlanıyor..." />
 </Grid>

Parents

138253

Offline Stefan posted over 11 years ago

Hello,

Thank you for your post. I have been looking into it and I can suggest you see the sample from the Feature Browser under xamDataGrid / Style / Custom Excel Export Cell Styling section where it shown how to set Cell’s Colors to Excel sheet. You can set each Field’s ExcelCellFormatSettings. Please let me know if this helps you or you need further assistance on this matter.

Looking forward for your reply.
Cancel
Reply

Cancel
430

Onur posted over 11 years ago in reply to Stefan

Hello,

I am using net advantage v11.1. There is a sample in feature browser under xamdatagrid / Excel Exporter / Custom cell styling. But It is not about exporting cell background color which are setted with cellvaluepresenters like in my previous post.

Thanks
Cancel
Reply

Cancel
265

Kishore Kodru posted over 11 years ago in reply to Stefan

I think we are not addressing the problem here - Let me elobarate, and I think the feature is not there to fit my need.

1. We have a data grid that loads data and a meta service that compares the current data with historical data and colors the cells that are changed/updated/created.

2. We have written (pretty much duplicated) code in the cell exporting to export to the excel and it works great (as you mentioned in your previous post).

--- we have this working just fine.

Recently, the user asked a change that we changed in the WPF grid and accidentally forgot to update the changes in the duplicated code that is used to export to excel.

We decided to either have a unit test that can break when something is changed in one side and not the other end - But practically it is not possible. So, I wrote a helper class that takes the original XamDataGrid and tie a style to it before exporting.

Options: & Questions

1. CellValuePresenter is not available for those cells that are not visible to the application (Hence the CVP is null and is useless for exporting styles). We really don't want to turn off the virtualization - (I even tried that at the grid itself AllowCellVirt - false, RecordLoadMode - Preload,..... , but it still doesn't initialize the CVP for the cells that are not visible to the application, and I understand that part). Is there a way to initialize the CVP even though it is not being drawn to the user's screen?

2. On researching more I found the DataPresenterExcelExporter class with ExcelCellFormatsettings - This works great - only problem is this is at the field level, not cell level. Is there a excel export class that is available to set it at cell level?

What we are trying to acheive is to use be able to use the MetaService for both styling the grid and exporting to the excel. We have invested a lot of time on this already. Hopefully there is a an answer :).
Cancel
Reply

Cancel

138253

Offline Stefan posted over 11 years ago in reply to Kishore Kodru

Hello Kishore,

If you handle DataPresenterExcelExporter's CellExporting event you will be able to color each Cell in the Excel sheet based on the XamDataGrid's Cell Value via the CellExporting eventArgs object. With the follwoing code you can see that Cells from same Field are colord diferently:

void exporter_CellExporting(object sender, CellExportingEventArgs e)
{
    if (e.CurrentRowIndex % 2 == 0)
    {
        e.FormatSettings.FillPattern = FillPatternStyle.Solid;
        e.FormatSettings.FillPatternForegroundColor = Colors.Orange;
    }
    else
    {
        e.FormatSettings.FillPattern = FillPatternStyle.Solid;
        e.FormatSettings.FillPatternForegroundColor = Colors.YellowGreen;
    }
}

Hope this helps you.

265

Kishore Kodru posted over 11 years ago in reply to Stefan

Thank you for your reply - At the moment that is what we are doing hard coding or duplicating our logic for WPF XamDataGrid and during ExcelExport.

Here is my question, is there a way to pass the XamDataGrid and CellValuePresenters initialized when passing it as parameter for ExcelExport so I can set the CVP in the CellExporting event and export it. If there is something like this - Please let me know how to acheive this. It would really help...

Right now - it only initializes the CVP if it is actually viewed or scrolled to - the rest does not exist.

My previous post question 1

1. CellValuePresenter is not available for those cells that are not visible to the application (Hence the CVP is null and is useless for exporting styles). We really don't want to turn off the virtualization - (I even tried that at the grid itself AllowCellVirt - false, RecordLoadMode - Preload,..... , but it still doesn't initialize the CVP for the cells that are not visible to the application, and I understand that part). Is there a way to initialize the CVP even though it is not being drawn to the user's screen?
Cancel
Reply

Cancel
138253

Offline Stefan posted over 11 years ago in reply to Kishore Kodru

Hello Kishore,

I have created a private support ticket on you behalf, where I updated you. The cese number is (CAS-106157-Z2N8Q5).
Cancel
Reply

Cancel

Reply

265

Kishore Kodru posted over 11 years ago in reply to Stefan

How can i see it - i received an email, but I appear to be pressing wrong link.
Cancel
Reply

Cancel

Children

No Data