Hi!
I’m working with Aspose.Cell 8.5.2.0 dll version.
The platform I use is .Net framework 4.0, c# language.
I need to populate an excel template in which is present a ComboBox control.
The placement of the comboBox depends on data processed.
My problem is that I can’t manage to move the ComboBox.
In my template the combobox is placed on column “H” and there it remains even if I use the MoveToRange method and the UpperLeftColumn, LowerRightColumn, UpperLeftRow and LowerRightRow property.
With this instruction I access to the combobox control:
Aspose.Cells.Drawing.Shape cbTrim = sheet.Shapes[7]; //I know it doesn’t look good but this is the only way to access to the control
//with following code lines I try to move it
cbTrim.IsLocked = false;
cbTrim.Placement = PlacementType.Move;
sheet.Cells.InsertColumns(4, 10, true); //I insert the number of desired columns starting from column “E” so the ComboBox that is placed in column H has to be moved on the right for 10 columns
cbTrim.MoveToRange(cbTrim.UpperLeftRow, cbTrim.UpperLeftColumn + 10, cbTrim.LowerRightRow, cbTrim.UpperLeftColumn + 10 + 1);
Even if I try to set properly the property UpperLeftColumn, etc… the combobox is always in the same position (the one of the template).
I would be happy to be able to move it in any other position…!!
Thank you very much for your support and you patience for my bad english.
Giovanna
Hi Giovanna,
Thank you for contacting Aspose support.
I have evaluated the presented scenario while using the latest version of Aspose.Cells for .NET 8.6.1.2 and the code provided at the bottom of this post. I believe the Shape.MoveToRange method is working fine as I am able to move the ComboBox to another space on the worksheet. Please check the attached resultant files for your reference. Please give a try to the latest version of the API. In case the problem persists, please provide us an executable sample application along with your input & output files for further investigation.
C#
//Create a new Workbook.
Workbook workbook = new Workbook();
//Get the first worksheet.
Worksheet sheet = workbook.Worksheets[0];
//Get the worksheet cells collection.
var cells = sheet.Cells;
//Input a value.
cells[“B3”].PutValue(“Employee:”);
//Set it bold.
cells[“B3”].GetStyle().Font.IsBold = true;
//Input some values that denote the input range
//for the combo box.
cells[“A2”].PutValue(“Emp001”);
cells[“A3”].PutValue(“Emp002”);
cells[“A4”].PutValue(“Emp003”);
cells[“A5”].PutValue(“Emp004”);
cells[“A6”].PutValue(“Emp005”);
cells[“A7”].PutValue(“Emp006”);
//Add a new combo box.
Aspose.Cells.Drawing.ComboBox comboBox = sheet.Shapes.AddComboBox(2, 0, 2, 0, 22, 100);
//Set the linked cell;
comboBox.LinkedCell = “A1”;
//Set the input range.
comboBox.InputRange = “A2:A7”;
//Set no. of list lines displayed in the combo
//box’s list portion.
comboBox.DropDownLines = 5;
//Set the combo box with 3-D shading.
comboBox.Shadow = true;
//AutoFit Columns
sheet.AutoFitColumns();
//Saves the file.
workbook.Save(“C:/temp/before.xls”);
//with following code lines I try to move it
comboBox.IsLocked = false;
comboBox.Placement = PlacementType.Move;
sheet.Cells.InsertColumns(4, 10, true); //I insert the number of desired columns starting from column “E” so the ComboBox that is placed in column H has to be moved on the right for 10 columns
comboBox.MoveToRange(comboBox.UpperLeftRow, comboBox.UpperLeftColumn + 10, comboBox.LowerRightRow, comboBox.UpperLeftColumn + 10 + 1);
workbook.Save(“C:/temp/after.xls”);
The problem is still present.
In your example you added a comboBox via .NET.
Could the problem be this one?
That the comboBox I want to move is placed into a template file made with Microsoft Office Excel and not made server side by a .NET application?
Maybe the correct test would be to create a template via Microsoft Excel, put in it a comboBox and then try to move it by accessing to the workbook via aspose?
Thanks in advance
Hi Giovanna,
I have tested the scenario again, this time by moving the ComboBox (Drop Down) created by Excel application, and I am able to get the expected results with latest revision of the API. Please check the attached spreadsheets and following piece of code for your reference. Based on these tests, I suspect that the problem is sample specific that is the reason I asked you to share the sample from your side. I request you again to provide us an executable standalone sample application along with it’s dependencies and the input/output spreadsheets for further investigation.
C#
var workbook = new Workbook(fileName);
var comboBox = workbook.Worksheets[0].Shapes[0];
comboBox.IsLocked = false;
comboBox.Placement = PlacementType.Move;
comboBox.MoveToRange(comboBox.UpperLeftRow, comboBox.UpperLeftColumn + 10, comboBox.LowerRightRow, comboBox.UpperLeftColumn + 10 + 1);
workbook.Save(“C:/temp/output.xls”);