Read and update values from ActiveX controls

Aspose.Cells Product Family
1

Hello,
I saw many demo code to create controls but none to read it.

I want to open a excel and get a collection of all available labels and/or textboxes.
Then I want to read their values and also update them.

My approach is that someone create a excel and place controls where some information will be set from a workflow automatically added to the document.
So the control are there as placeholder and I want to read if they are exists (unique name) and then write their values.

Is there any demo code for this?

Thanks in advance, Yves

—
ADD:
So far I found soem code to load a worksheet and collect the text boxes.
But with Aspose.Cells.Drawing.TextBoxCollection I only the FormFields textboxes which can not be renamed inside Excel (for some very stupid reason I do not know).
So how can I access the ActiveX label and textboxes the same way??

2

Hi Yves,


Thank you for contacting Aspose support.

You may access the ShapeCollection using the Worksheet.Shapes property and iterate over it to retrieve the control name, Id and other generic properties. However, please note that the current implementation of Aspose.Cells APIs do not support to manipulate or retrieve control’s specific properties if the control is of type ActiveX. Aspose.Cells APIs can only retrieve the ActiveX controls’ data in byte array if you can make any use of it.

In case of form controls, you may access all relevant properties by casting the control to appropriate type from Aspose.Cells.Drawing namespace. Please check the following piece of code for better understanding.

C#

var book = new Workbook(inputFilePath);
var sheet = book.Worksheets[0];
var shapes = sheet.Shapes;
foreach (Shape shape in shapes)
{
if (shape.ControlData != null)
{
Console.WriteLine(shape.Id + " " + shape.Name + " is ActiveX control");
}
else
{
Console.WriteLine(shape.Id + " " + shape.Name + " is Form control");
if (shape.MsoDrawingType == MsoDrawingType.CheckBox)
{
((CheckBox)shape).CheckedValue = CheckValueType.Checked;
}
else if (shape.MsoDrawingType == MsoDrawingType.Button)
{
((Button)shape).Text = “Button Text”;
}
else if (shape.MsoDrawingType == MsoDrawingType.ListBox)
{
((ListBox)shape).SelectionType = SelectionType.Multi;
((ListBox)shape).SelectedItem(0, true);
((ListBox)shape).SelectedItem(1, true);
}
else if (shape.MsoDrawingType == MsoDrawingType.RadioButton)
{
((RadioButton)shape).IsChecked = true;
}
}
}
3

Hello,
thank you for your answer.
FormFields are “ok” to use for me, but in fact your sample does not include the most important one: TextField.

My requirement is to replace placeholder textbox values like in a “header like” environment, like author, title, etc.

I thought it would be way easier to simple set some textboxes, select them by name and the update their values…

4

Hi again,


By TextField, if you meant TextBox then you may easily update the text while using the following piece of code, otherwise, please share a sample containing the control in question. Moreover, if you know the exact name of the control, you may directly access it from the ShapeCollection rather iterating over the collection.

C#

var book = new Workbook(inputFilePath);
var sheet = book.Worksheets[0];
var shapes = sheet.Shapes;
var textBox = shapes[“TextBox 1”];
((TextBox)textBox).Text = “Some Text”;
5

I ran into a bug I guess:

var oldName = “Textfield 1”;
var newName = “Field4711”;
var workbookSheets = _workbook.Worksheets;
foreach (Worksheet sheet in workbookSheets)
{
var boxes = sheet.TextBoxes;
foreach (var box in boxes)
{
if (box.Name == oldName) box.Name = newName;
}
}

If I save the document and open it via Excel it loads, but try to save it in Excel popup a error message that the file is corrupted!

This seems to work:

var shapes = sheet.Shapes;
foreach (var shape in shapes)
{
if (shape.MsoDrawingType == MsoDrawingType.TextBox && shape.Name == oldName)
shape.Name = newName;
}

6

Yes, exactly. This is what I want to address. I will work on this.
For some reason I do not understand the TextField shape name is not available to set in Excel itself (only via VB) which makes it very hard to use for a normal user…
But it seems I can not either use ActiveX-Textfield controls as you mentioned they are only read-able.

7

Add, to give feedback.

Works:

var workbookSheets = _workbook.Worksheets;
foreach (Worksheet sheet in workbookSheets)
{
var shapes = sheet.Shapes;
foreach (var shape in shapes)
{
if (shape.MsoDrawingType == MsoDrawingType.TextBox && shape.Name == name)
shape.Text = value;
}
}

The only thing now I need to find out is a solution to proper set the names of the fields…
And you might check on the bug I reported.

Thanks for help.

8
rausch:
I ran into a bug I guess:

var oldName = "Textfield 1";
var newName = "Field4711";
var workbookSheets = _workbook.Worksheets;
foreach (Worksheet sheet in workbookSheets)
{
var boxes = sheet.TextBoxes;
foreach (var box in boxes)
{
if (box.Name == oldName) box.Name = newName;
}
}

If I save the document and open it via Excel it loads, but try to save it in Excel popup a error message that the file is corrupted!

This seems to work:

var shapes = sheet.Shapes;
foreach (var shape in shapes)
{
if (shape.MsoDrawingType == MsoDrawingType.TextBox && shape.Name == oldName)
shape.Name = newName;
}


Hi,

We need the input spreadsheet to your process for our testing so please provide it here so we could figure out the problem cause.
9

Attached you find an example.
There is a “Textfeld 4” field. Use box.Name and save the document. You will be able to load it and on save click you get an error message.

10

Hi Yves,


Thank you for the sample.

I have evaluated the presented scenario while using the latest version of Aspose.Cells for .NET 8.6.2 and the following piece of code. I am afraid, I am not able to see any problem while loading the resultant spreadsheet in Excel application. If you are using any older revision of the API then please give the latest version a try on your side.

C#

var book = new Workbook(inputFilePath);
var oldName = “Textfeld 4”;
var newName = “Field4711”;
var workbookSheets = book.Worksheets;
foreach (Worksheet sheet in workbookSheets)
{
var boxes = sheet.TextBoxes;
foreach (TextBox box in boxes)
{
if (box.Name == oldName) box.Name = newName;
}
}
book.Save(“C:/temp/output.xlsx”);

11

Hello,

I retried the same code and it do not produce the error now. Using the same version. So I do not know why it is working now, sorry for any circumstances :slight_smile:
It seems that something else caused the effect…

12

Hi Yves,


It is good to know that you are able to get correct results on your side as well. Please feel free to contact us back in case you need our further assistance with Aspose APIs.