Hi,
How I can control maximum value of horizontal scroll bar in the xamSpreadhsheet control? For what ever reasons I have around 5-10 columns in control but scrollbar shows very high value while all columns fits into the screen. How I can adjust this behavior?
Thanks in advance,
Tomas
Hello Tomas,
My team and I have done an initial review of this support case, and I have some questions for you relating to the XamSpreadsheet and the Excel file you are placing inside it, as the behavior you are currently seeing is not the default, and there does not exist a way to control the “max scroll” of the XamSpreadsheet as it depends on the allocated cells. The default behavior can be seen in just about any of the XamSpreadsheet samples within the WPF Samples Browser that is installable with the product.
Would it be possible for you to please answer the following?
1. What is the container that you have the XamSpreadsheet placed inside of? By chance is it within a ScrollViewer / StackPanel / other control that measures with an infinite height/width?
2. Are there other cells that may have had values in the Workbook that you are applying to the XamSpreadsheet than the ones in your screenshot? I ask this, as the XamSpreadsheet and the Excel Workbooks/Worksheets only actually allocate cells that have a value or have had a value and this is done for performance reasons, as allocating them all would be horrible for performance. A way you can check what cells (or how many) are allocated is to run the following foreach loop, where “sheet” is the active Worksheet of the XamSpreadsheet:
foreach(var row in sheet.Rows) { System.Diagnostics.Debug.WriteLine("Row " + rowCount);
foreach(var cell in row.Cells) { System.Diagnostics.Debug.WriteLine("Cell " + cellCount); cellCount++; }
rowCount++; }
Please let me know if you have any other questions or concerns on this matter.
Hi Andrew,
I've made minor corrections to the code to have smaller output and attached directly to spreadsheet activate workbook event:
private void SpreadSheet_OnActiveWorksheetChanged(object sender, SpreadsheetActiveWorksheetChangedEventArgs e) { System.Diagnostics.Debug.WriteLine("Worksheet name: " + e.NewValue.Name); var rowCount = 0; foreach (var row in e.NewValue.Rows) { var cellCount = 0; foreach (var cell in row.Cells) cellCount++; System.Diagnostics.Debug.WriteLine($"Row {rowCount} with {cellCount} Cells"); rowCount++; } }
This generated output for previously shown screenshot:
Worksheet name: Variables Row 0 with 8 Cells Row 1 with 0 Cells Row 2 with 1 Cells Row 3 with 0 Cells Row 4 with 1 Cells Row 5 with 1 Cells Row 6 with 1 Cells Row 7 with 1 Cells Row 8 with 1 Cells Row 9 with 1 Cells Row 10 with 1 Cells Row 11 with 1 Cells Row 12 with 0 Cells Row 13 with 0 Cells Row 14 with 0 Cells Row 15 with 0 Cells Row 16 with 0 Cells Row 17 with 0 Cells Row 18 with 0 Cells Row 19 with 0 Cells Row 20 with 0 Cells Row 21 with 0 Cells Row 22 with 0 Cells Row 23 with 0 Cells Row 24 with 0 Cells Row 25 with 0 Cells Row 26 with 0 Cells Row 27 with 0 Cells Row 28 with 0 Cells Row 29 with 0 Cells Row 30 with 0 Cells Row 31 with 0 Cells Row 32 with 0 Cells Row 33 with 0 Cells Row 34 with 0 Cells Row 35 with 0 Cells Row 36 with 0 Cells Row 37 with 0 Cells Row 38 with 0 Cells Row 39 with 0 Cells Row 40 with 0 Cells Row 41 with 0 Cells
The Xaml source code has own parents located in multiple levels of tabs then loaded as user control with fallowing xaml:
<UserControl xmlns:ig="http://schemas.infragistics.com/xaml" x:Class="EafClient.Editor.CellAutomation.EquipmentDictionary.Views.EquipmentDictionaryEditorView" xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation" xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml" xmlns:d="http://schemas.microsoft.com/expression/blend/2008" xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006" xmlns:i="http://schemas.microsoft.com/expression/2010/interactivity" xmlns:multiconverters="clr-namespace:EafClient.Core.Converters.MultiConverters;assembly=EafClient.Core" xmlns:igWindows="http://infragistics.com/Windows" xmlns:common="clr-namespace:EafClient.Core.Images.Common;assembly=EafClient.Core" xmlns:specificBehaviors="clr-namespace:EafClient.Editor.CellAutomation.EquipmentDictionary.Views.SpecificBehaviors" xmlns:edit="clr-namespace:EafClient.Outlook.IntegratorViews;assembly=EafClient.Outlook" mc:Ignorable="d"> <UserControl.Resources> <multiconverters:MultiBooleanOrConverter x:Key="BooleanOrConverter" /> <multiconverters:MultiBooleanToVisibilityConverter x:Key="MultiBooleanConverter" /> </UserControl.Resources> <i:Interaction.Behaviors> <specificBehaviors:EquipmentDictionaryWorkbookBehavior> <specificBehaviors:EquipmentDictionaryWorkbookBehavior.WorkbookData> <Binding Path="EquipmentDictionaryWorkbook" Mode="TwoWay" UpdateSourceTrigger="PropertyChanged"/> </specificBehaviors:EquipmentDictionaryWorkbookBehavior.WorkbookData> <specificBehaviors:EquipmentDictionaryWorkbookBehavior.IsSpreadsheetDirty> <Binding Path="UnsavedChangesExist" Mode="TwoWay" UpdateSourceTrigger="PropertyChanged"/> </specificBehaviors:EquipmentDictionaryWorkbookBehavior.IsSpreadsheetDirty> <specificBehaviors:EquipmentDictionaryWorkbookBehavior.ActiveWorksheetName> <Binding Path="ActiveWorksheetName" Mode="TwoWay" UpdateSourceTrigger="PropertyChanged"/> </specificBehaviors:EquipmentDictionaryWorkbookBehavior.ActiveWorksheetName> <specificBehaviors:EquipmentDictionaryWorkbookBehavior.AddWorksheetEvents> <Binding Path="AddWorksheetEvents" Mode="OneWay" UpdateSourceTrigger="PropertyChanged" /> </specificBehaviors:EquipmentDictionaryWorkbookBehavior.AddWorksheetEvents> <specificBehaviors:EquipmentDictionaryWorkbookBehavior.IsReadOnly> <MultiBinding Converter="{StaticResource BooleanOrConverter}"> <Binding Path="DataContext.IsFrozen" RelativeSource="{RelativeSource Mode=FindAncestor, AncestorType={x:Type edit:IViewAdapter}}" Mode="OneWay" /> <Binding Path="IsReadOnlyMode" Source="{x:Static Application.Current}" Mode="OneWay" /> </MultiBinding> </specificBehaviors:EquipmentDictionaryWorkbookBehavior.IsReadOnly> </specificBehaviors:EquipmentDictionaryWorkbookBehavior> </i:Interaction.Behaviors> <Grid> <DockPanel LastChildFill="True" x:Name="dockPanel"> <StackPanel Orientation="Horizontal" HorizontalAlignment="Right" DockPanel.Dock="Top"> <Button Opacity="100" Style="{DynamicResource ImageButton}" IsDefault="True" HorizontalAlignment="Right" VerticalAlignment="Bottom" Margin="3" ToolTip="Add a new Object worksheet based on the template." Command="{Binding CreateAndSelectObjectWorksheetCommand}" CommandParameter="{Binding EquipmentDictionaryWorkbook}"> <StackPanel> <igWindows:AutoDisabledImage Source="{Binding Source={x:Static common:GeneralIcon.NEW}}" Stretch="Fill" Height="15" Width="15"/> </StackPanel> </Button> <Button Opacity="100" Style="{DynamicResource ImageButton}" IsDefault="True" IsEnabled="{Binding IsObjectWorksheet}" HorizontalAlignment="Right" VerticalAlignment="Bottom" Margin="3" ToolTip="Rename active Worksheet." Command="{Binding RenameObjectWorksheetCommand}" CommandParameter="{Binding EquipmentDictionaryWorkbook}"> <StackPanel> <igWindows:AutoDisabledImage Source="{Binding Source={x:Static common:GeneralIcon.RENAME}}" Stretch="Fill" Height="15" Width="15"/> </StackPanel> </Button> <Button Opacity="100" Style="{DynamicResource ImageButton}" IsDefault="False" IsEnabled="{Binding IsObjectWorksheet}" HorizontalAlignment="Right" VerticalAlignment="Bottom" Margin="3" ToolTip="Delete the selected Worksheet." Command="{Binding DeleteObjectWorksheetCommand}" CommandParameter="{Binding EquipmentDictionaryWorkbook}"> <StackPanel> <igWindows:AutoDisabledImage Source="{Binding Source={x:Static common:GeneralIcon.DELETE}}" Stretch="Fill" Height="15" Width="15"/> </StackPanel> </Button> <StackPanel.Visibility> <MultiBinding Converter="{StaticResource MultiBooleanConverter}" ConverterParameter="inverse:1,2"> <Binding Path="DataContext.IsFrozen" RelativeSource="{RelativeSource Mode=FindAncestor, AncestorType={x:Type edit:IViewAdapter}}" Mode="OneWay" /> <Binding Path="IsReadOnlyMode" Source="{x:Static Application.Current}" Mode="OneWay" /> </MultiBinding> </StackPanel.Visibility> </StackPanel> <ig:XamSpreadsheet x:Name="SpreadSheet" ActiveWorksheetChanged="SpreadSheet_OnActiveWorksheetChanged" AllowAddWorksheet="False" AllowDeleteWorksheet="False" FontFamily="{Binding EquipmentDictionaryFontFamily}" FontSize="{Binding EquipmentDictionaryFontSize}"> <ig:XamSpreadsheet.ContextMenuProvider> <ig:SpreadsheetContextMenuProvider> <ig:SpreadsheetContextMenuProvider.ResourceOverrides> <ResourceDictionary> <!-- Empty Tab Context Menu to prevent Rename via context menu--> <ig:XamContextMenu x:Key="Tab"/> </ResourceDictionary> </ig:SpreadsheetContextMenuProvider.ResourceOverrides> </ig:SpreadsheetContextMenuProvider> </ig:XamSpreadsheet.ContextMenuProvider> </ig:XamSpreadsheet> </DockPanel> </Grid> </UserControl>
The location of the xamSpreadhsheet control is hard to describe but here screenshot of Xaml tree view to the control from application level (used tool Snoop v2.10.0.0):
I've highlighted various containers and other our controls. Hope this helps.
I have attempted to put together a sample project with a similar structure, although it appears you have multiple custom controls in your Snoop visual tree that I am unsure what they are, and I am unsure if they are having an effect here. I am also curious if the Workbook you are loading has an effect here or if the scroll behavior you are seeing is happening by default with the XamSpreadsheet?
I am attaching the sample project I created that has a somewhat similar structure to what is in your Snoop tree. It would be helpful if you can modify this sample such that it reproduces the behavior you are seeing and send it back. Alternatively, if you have an isolated sample that reproduces this behavior out of the box, I would gladly look at that instead.
XamSpreadsheetScrollIssueTest.zip
Thanks for a template, it worked for me fine too. However, we have much more complex handling before displaying the data, since we load an Excel template then we fill the data and adjust the formulas due various performance problems and so on. Nerveless I found already existing WPF sample reproducing this problem which was build specifically for Multiple owners reported problem. The thread does not contain this sample and unfortunately sample contains quit a lot of our code structure which I don't want to publish publicly.
I have created new support case with id C-00226087 under my account and attached the sample.
Thanks for support. it turned out problem causing this line when applying lock for headers in all sheets:
worksheet.Rows[0].CellFormat.Locked = ExcelDefaultableBoolean.True;
Thank you for the sample project in the private support case. Unfortunately, I am unable to see the XamSpreadsheet in the sample? Are there any other steps that I need to take with the sample project you provided to get it to show up? I was able to run it and select a tree item, but then nothing happens?
If you would like, we can also continue this conversation in the private support case if you don't wish to discuss code that you don't want published publicly.