Aspose Cells > Replace Text in Rectangle Shape

I have a rectangle shape on my sheet. I need to change its text in runtime.
Can I do it? How?

I’m using Aspose.Cells and I haven’t seen any code like it yet.

Thanks,

Ilan

Hi Ilan,

Thanks for considering Aspose.

Are you using Excel2007 .xlsx template file which contains Rectangle shape(s)? Well, currently we don't support to manipulate rectangle shapes for Excel2007, we will support it in our future versions of Aspose.Cells component soon.

Thank you.

No my template comes from xls. Excel 2003

It has a button (which is a rectangle shape). I need to change the text of the button.

Hi,

Yes you can do it.

May the following sample code help you for your need.

Workbook workbook = new Workbook();
workbook.Open("d:\\test\\bk_button.xls");
Worksheet sheet = workbook.Worksheets[0];
Get the first shape in the sheet which is a button.
Aspose.Cells.Button btn = (Aspose.Cells.Button)sheet.Shapes[0];
//Change the existing label text of the button.
btn.Text = "TestCells";
workbook.Save("d:\\test\\bk_button_new.xls");
For further reference about shapes, please check:

http://www.aspose.com/Wiki/default.aspx/Aspose.Cells/OtherDrawingObjects.html

Thank you.

Doesn’t work…

It’s RectangleShape object and there’s mo text property

See the attached file.

Hi,

Which version do you use? Please try the attached fix with the following codes:

if (shape.MsoDrawingType == MsoDrawingType.Rectangle)
{
((RectangleShape)shape).Text = "RectangleShape";
}

we use 4.4.0.21

I tried the fix. The code crashes at runtime:

Cell startCell = sheetRef.Cells[startRowIndex, startColIndex];
Names names = workBook.Worksheets.Names;
Name name = names[names.Add(referTo)];
name.RefersTo = String.Format("=OFFSET({0}!{1},0,0,{2})", sheetRef.Name, ConvertCellToAddress(startCell), endRowIndex - startRowIndex);

This part was working ok. It’s not related to the shape issue.
Seems that there’s a bug in this fix.

No backward compatibility -

Again, it crashes in another place in runtime (when I export my file which has dynamic validations):
System.Exception was unhandled by user code
Message=“Invalid formula in data validation settings.”
Source=“Aspose.Cells”
StackTrace:
at Aspose.Cells.Record.ᒔ.ᒚ(String ગ)
at Aspose.Cells.Record.ᒔ.ᒗ(Validation ᑱ, Int32 ᒘ)
at Aspose.Cells.Record.ᒔ.ᒖ(Validation ᑱ)
at Aspose.Cells.Worksheets.શ()
at Aspose.Cells.Worksheets.સ(ॽ ۳)
at Aspose.Cells.Worksheets.઼(FileFormatType ઺)
at Aspose.Cells.Worksheets.ॾ(String ા, SaveType િ, FileFormatType ઺, HttpResponse ી)
at Aspose.Cells.Workbook.Save(String fileName, SaveType saveType, FileFormatType fileFormatType, HttpResponse response)
at Aspose.Cells.Workbook.Save(String fileName, FileFormatType fileFormatType, SaveType saveType, HttpResponse response)
at myFunc.Export(HttpResponse httpResponse, String fileName) …

Again this is not related to the shape issue.

Thanks,

Ilan


Hi,

Could you post your template file with details

Thank you.

I understand. Could you try it on my previous sample code I sent?
I haven’t tried it on it but since it crashes in this place, hopefully you will reproduce the bug.

Hi,

I tried the following codes with the fix in the previous post. It works fine. Could you post your template file and codes? The error is caused by Validation. We should know which formula your template file contains.

Workbook workbook = new Workbook();

Worksheet sheetRef = workbook.Worksheets[0];
Aspose.Cells.Cell startCell = sheetRef.Cells[0, 0];
Names names = workbook.Worksheets.Names;
string referTo = "test";
Name name = names[names.Add(referTo)];
name.RefersTo = String.Format("=OFFSET({0}!{1},0,0,{2})",
sheetRef.Name, startCell.Name, 5);

workbook.Save(@"F:\FileTemp\dest.xls");

Here’s my code.
It falls in the UseCase1 with version 4.4.0.24

If I recall well, Lawrence has dealt with this bug previously, and solved it in version 4.4.0.21 (the version I’m currently using)
If you change the reference to 4.4.0.21, then the UseCase4 fails.

Hi,

Please try this fix.

In the old version, we only support Range/NamedRange as the list condition of data validation.

In the attached code, I’m already using 4.4.0.24
Please try the attached code you will see in crashes (no backward compatibility for dynamic range)

Please try to run the code attached previously.
In this new version GetRangeByName is okay and RectangleShape is supported but the dynamic validation test fails.

Thanks,

Ilan

Hi Ilan,

Sorry, we forget to change the version Id.

We have changed it. Please retry this fix.

Ok, but I think it’s something else.
could you run the code, and tell me why it crashes with the new version (4.4.0.24)

Hi,

In version 4.4.0.24, we only support range and named rang as the value or expression associated with the data validation. We do not support the formula "INDIRECT(\"Section\"&VLOOKUP(A2,FruitLookup,2,0))" as the expression associated with the data validation.

As I told you earlier I arlready tested 4.4.0.24 and it fails in dynamic validation test.
I attached previously a code which fails on validations UseCase1 but succeed in GetRangeByName and RectangleShape
All I’m saying is there’s some backward compatibility broken.

Let me know if you understand the problem.

Hi,

There's a bug of dynamic validation in version 4.4.0.24. We have fixed it since 4.4.0.25.

So please try this fix(4.4.0.26).