Problems with autosize

Aspose.Cells Product Family
36

Hi Shakeel,

Re justify fill - it is a standard tool in Excel - best I refer you to this link to see the basics of what it does:

Regarding the outstanding issue, we are very disappointed that this bug has not yet been resolved and more so that there is no scheduled date for a resolution from Aspose.

This bug has been outstanding for some time now.

As mentioned previously, the method at issue returning correct results/values is a vital requirement for our application and critical for our users, and thus key to our reliance and continued use of Aspose Cells.

I would respectfully ask you to do everything you can to escalate this bug to the highest level and hopefully have fixed asap.

I look forward to your feedback and hopefully an expected date for the bug’s resolution.

Many thanks,

Mike

37

Hi Mike,

Thanks for using Aspose.Cells.

For this new feature (i.e using Justify tool in Excel), please create a new thread. We will look into it and see if such a feature could be implemented and update you asap.

Currently, your issue is logged as a Normal User. You can consider to purchase enhanced support (priority or enterprise support) and then your issue’s priority will be escalated to enhanced priority.

Please see the following link to see different priority options.

http://www.aspose.com/corporate/services/default.aspx

I have also requested the development team to look into your issue as soon as possible and once again logged your comment. Once, there is some fix or update for you, we will let you know asap.

38

Hi,


We also seem the have the same issue here.

We want to accomplish the following; we want to have a textbox shape on our first cell which has a fixed width and a variable height. The rows under this cell have some data in it. The input text should be completely displayed, make the textbox longer and should not overflow to the right. The cell should also grow vertically so the data is not overlapped with the shape.

In our input (attached) file already has the data and the textboxshape.

The I run this code*;

Dim objLicense As New Aspose.Cells.License()
objLicense.SetLicense(“Aspose.Cells.lic”)

Dim objWorkbook As New Workbook(“C:\input.xls”)

Dim objWorksheet As Worksheet = objWorkbook.Worksheets(0)

Dim objShape As Aspose.Cells.Drawing.Shape = objWorksheet.Shapes(“tb”)
objShape.Text = String.Format(“very long text the first line is the longest, longer, longer, very very long {0} not as long {0} even shorter {0} or was it”, vbCrLf)
objShape.TextFrame.AutoSize = True

objWorksheet.Cells.Rows(0).Height = objShape.CalculateTextSize()(1)

objWorkbook.Save(“C:\output.xls”, SaveFormat.Excel97To2003)

I observed the following;
- objShape.Height property is still the original height
- the objShape.CalculateTextSize function** returns an array of two variables of which (I assume) the first one is the new height. But this value was to large.

Please see the attached “output” file

Could you please provide a workaround or link us to the existing issue so we will also be up to date?

Also I would like the know why I do not get any data when I call the “LinkedCell” property of the objShape? I would have expected “A1”…

With warm regards,

Sjoerd van Loon
Senior Software Engineer
Infoland BV

* tested against aspose.cells 8.0.0
** I could not find any documentation about this function using google or the internal search of your website.
39

Hi,

Thanks for your posting and using Aspose.Cells.

We have found that Aspose.Cells (CalculateTextSize) does not return the height of the textbox correctly. This issue is already logged in our database with the issue id: CELLSNET-42401

You will have to get the new height using CalculateTextSize() method, the height of the shape will not change automatically.

We have also logged your comments against it. Once, there is some fix or update for you, we will let you know asap.

For your linked cell, kindly post your question in a separate thread so that we could address your issue appropriately. Also, please provide us your sample code replicating the issue for our reference.

40

Hi Shakeel Faiz,


In when could we expect this to be fixed? Is it necessary to invoke our priority support status for this issue?

With kinds regards,

Sjoerd van Loon
Senior Software Engineer
Infoland BV
41

Hi Sjoerd,

Thanks for your posting and using Aspose.Cells.

This issue is logged as a Normal issue. If you like, you can post in Priority Forum, then we will increase the priority of this issue. However, increasing the priority does not guarantee a fix but escalates the issue. Priority issues are processed first than Normal issues.

We cannot provide you any expected date for this issue to be fixed at this moment. However, we have already logged your comments and requested the development team to provide any ETA or update regarding this issue.

42

Hi,

Thanks for using Aspose.Cells.

We have fixed the issues CELLSNET-42104 and CELLSNET-42401.

Please download and try this fix: Aspose.Cells
for .NET v8.0.1.3 and let us know your feedback.

We have fixed the bugs except related to output file xlsb.

43

Hi,

Thanks for using Aspose.Cells.

Please download and try this fix: Aspose.Cells
for .NET v8.0.1.3 for the issue id: CELLSNET-42235.

You can resize the text using following.

1. If set IsTextWrapped to false. Don’t specify limited scope, the text will not be wrapped until meets newline character.


textBox.TextFrame.AutoSize =
true;

textBox.IsTextWrapped = false;

int[] size = textBox.CalculateTextSize();

textBox.Height = size[1];

textBox.Width = size[0];



2. If set IsTextWrapped to true. At the same time sets width of shape.CalculateTextSize() method will ensure the text does not extend out of the fixed width and the height is a variable value.

 textBox.TextFrame.AutoSize = true;

textBox.IsTextWrapped = true;

//If IsTextWrapped is true, must set the width of shape to a proper fixed value.

//In this case, CalculateTextSize()[0] is equals shape.Width.

textbox.Width = 400;

int[] size = textBox.CalculateTextSize();

textBox.Height = size[1];

textBox.Width = size[0];

44

Hi Shakeel,

The attached version 8.0.1.3 is for .NET 2.

I can’t compile against it.

Could you please send me through the version for .NET 3.5 please.

Thanks,

Mike

45

Hi Mike,

Thanks for your posting and using Aspose.Cells.

I have forwarded your request to development team to provide you NET 3.5 Aspose.Cells Dll. Once, it is available for you, we will let you know asap.

46

Hi Mike,

Thanks for using Aspose.Cells.

Please download and try the latest .NET 3.5 version: Aspose.Cells
for .NET v8.0.1.3 (NET 3.5) for your needs.

47

Hi Shakeel,

I have tested this latest version against the original attachment (see earlier in this post) demonstrating the problem, with the changes you suggested re setting the Width of the Text Box.

I note the same problems and thus believe it is still not fixed.

Regards,

Mike

48

Hi Mike,

Thanks for your feedback and using Aspose.Cells.

I have tested this issue with the latest version: Aspose.Cells
for .NET v8.0.1.3 and found that all the output files (xlsx, xps, pdf) except (xlsb) are working fine. Which is correct. Because we are still working on xlsb issue.

I have attached the output files for your reference.

49

Hi Shakeel,

I tried the following code, and the Text Box height when you open it (and click on it when you open the file in MS Excel) is still not correct. Saving to .Xlsx.

This is with version 8.0.1.3 of Aspose Cells

Am I doing something wrong…???

private void TextBoxShapeAutoHeightIssue()
{
Workbook testWorkbook = new Workbook();
Worksheet testWorksheet = testWorkbook.Worksheets[0];
Cells testCells = testWorksheet.Cells;
int iWidth = testCells.GetColumnWidthPixel(1);
iWidth += testCells.GetColumnWidthPixel(2);
Shape testShape = testWorksheet.Shapes.AddTextBox(1, 0, 1, 0, 0, 0);
testCells.SetColumnWidthPixel(5, 300);
testCells[15, 5].Value = “Text Box Height - After Creation”;
testCells[15, 6].Value = testShape.Height;
MsoTextFrame testTextFrame = testShape.TextFrame;
testTextFrame.BottomMarginPt = 0;
testTextFrame.LeftMarginPt = 0;
testTextFrame.RightMarginPt = 0;
testTextFrame.TopMarginPt = 0;
// SET AUTO SIZE
testTextFrame.AutoSize = true;
testTextFrame.IsAutoMargin = false;
testShape.Text = “The TextBox in the created spreadsheet does not set the height of the TextBox correctly when the TextFrame.AutoSize property is set to true.”;
testShape.Placement = PlacementType.Move;
testShape.IsLocked = false;
testShape.IsPrintable = true;
testShape.IsLocked = false;
testShape.IsLockAspectRatio = true;
testShape.IsTextWrapped = true;
testShape.LineFormat.IsVisible = false;
testShape.TextOrientationType = TextOrientationType.NoRotation;
testShape.TextHorizontalAlignment = TextAlignmentType.Justify;
testShape.TextVerticalAlignment = TextAlignmentType.Top;
testShape.Width = iWidth;
int[] size = testShape.CalculateTextSize();
// SET THE Height and Width as returned by .CalculateTextSize()
testShape.Height = size[1];
// testShape.Width = size[0];
testCells[17, 5].Value = “Text Box Height - After Setting Properties”;
testCells[17, 6].Value = testShape.Height;
// testWorkbook.Save(@“C:\TextBoxHeightIssue.Xlsb”, SaveFormat.Xlsb);
// Process.Start(@“C:\TextBoxHeightIssue.Xlsb”);

        testWorkbook.Save(@"C:\TextBoxHeightIssue.Xlsx", SaveFormat.Xlsx);
        Process.Start(@"C:\TextBoxHeightIssue.Xlsx");
    }
50

Hi Mike,

Thanks for your sample code and using Aspose.Cells.

We were able to observe this issue using your sample code with the latest version: Aspose.Cells
for .NET v8.0.1.3. Shape.CalculateTextSize() does not return correct height when TextFrame.AutoSize property is set to true.

Although, the sample code mentioned in this post: 504550 generates correct results.

We have logged this issue in our database for investigation. We will look into it and fix this issue. Once, the issue is resolved or we have some other update for you, we will let you know asap.

I have attached the output xlsx file and screenshot for a reference.

C#


private void TextBoxShapeAutoHeightIssue()

{

Workbook testWorkbook = new Workbook();

Worksheet testWorksheet = testWorkbook.Worksheets[0];

Cells testCells = testWorksheet.Cells;

int iWidth = testCells.GetColumnWidthPixel(1);

iWidth += testCells.GetColumnWidthPixel(2);

Shape testShape = testWorksheet.Shapes.AddTextBox(1, 0, 1, 0, 0, 0);

testCells.SetColumnWidthPixel(5, 300);

testCells[15, 5].Value = “Text Box Height - After Creation”;

testCells[15, 6].Value = testShape.Height;

MsoTextFrame testTextFrame = testShape.TextFrame;

testTextFrame.BottomMarginPt = 0;

testTextFrame.LeftMarginPt = 0;

testTextFrame.RightMarginPt = 0;

testTextFrame.TopMarginPt = 0;

// SET AUTO SIZE

testTextFrame.AutoSize = true;

testTextFrame.IsAutoMargin = false;

testShape.Text = “The TextBox in the created spreadsheet does not set the height of the TextBox correctly when the TextFrame.AutoSize property is set to true.”;

testShape.Placement = PlacementType.Move;

testShape.IsLocked = false;

testShape.IsPrintable = true;

testShape.IsLocked = false;

testShape.IsLockAspectRatio = true;

testShape.IsTextWrapped = true;

testShape.LineFormat.IsVisible = false;

testShape.TextOrientationType = TextOrientationType.NoRotation;

testShape.TextHorizontalAlignment = TextAlignmentType.Justify;

testShape.TextVerticalAlignment = TextAlignmentType.Top;

testShape.Width = iWidth;

int[] size = testShape.CalculateTextSize();

// SET THE Height and Width as returned by .CalculateTextSize()

testShape.Height = size[1];

// testShape.Width = size[0];

testCells[17, 5].Value = “Text Box Height - After Setting Properties”;

testCells[17, 6].Value = testShape.Height;

// testWorkbook.Save(@“C:\TextBoxHeightIssue.Xlsb”, SaveFormat.Xlsb);

// Process.Start(@“C:\TextBoxHeightIssue.Xlsb”);


testWorkbook.Save(@“TextBoxHeightIssue.Xlsx”, SaveFormat.Xlsx);

// Process.Start(@“C:\TextBoxHeightIssue.Xlsx”);

}

51

Thanks Shakeel.

Best wishes to the team finding out what is going wrong here and getting a solution.

As always,

Best Regards,

Mike

52

Hi Mike,

Thanks for your posting and using Aspose.Cells.

As the workbook and the shape are created by Aspose.Cells API, we can’t know proper font of shape when CalculateTextSize() method is called.

So we can fix the issue by setting the font before calling CalculateTextSize() method.

C#


testShape.Font.Name = “Calibri”;

testShape.Font.Size = 11;

int[] size = testShape.CalculateTextSize();



Here is a complete code. I have attached the output file for your reference.

Complete Code:
private void TextBoxShapeAutoHeightIssue()
{
Workbook testWorkbook = new Workbook();
Worksheet testWorksheet = testWorkbook.Worksheets[0];
Cells testCells = testWorksheet.Cells;
int iWidth = testCells.GetColumnWidthPixel(1);
iWidth += testCells.GetColumnWidthPixel(2);
Shape testShape = testWorksheet.Shapes.AddTextBox(1, 0, 1, 0, 0, 0);
testCells.SetColumnWidthPixel(5, 300);
testCells[15, 5].Value = "Text Box Height - After Creation";
testCells[15, 6].Value = testShape.Height;
MsoTextFrame testTextFrame = testShape.TextFrame;
testTextFrame.BottomMarginPt = 0;
testTextFrame.LeftMarginPt = 0;
testTextFrame.RightMarginPt = 0;
testTextFrame.TopMarginPt = 0;
// SET AUTO SIZE
testTextFrame.AutoSize = true;
testTextFrame.IsAutoMargin = false;
testShape.Text = "The TextBox in the created spreadsheet does not set the height of the TextBox correctly when the TextFrame.AutoSize property is set to true.";
testShape.Placement = PlacementType.Move;
testShape.IsLocked = false;
testShape.IsPrintable = true;
testShape.IsLocked = false;
testShape.IsLockAspectRatio = true;
testShape.IsTextWrapped = true;
testShape.LineFormat.IsVisible = false;
testShape.TextOrientationType = TextOrientationType.NoRotation;
testShape.TextHorizontalAlignment = TextAlignmentType.Justify;
testShape.TextVerticalAlignment = TextAlignmentType.Top;
testShape.Width = iWidth;
testShape.Font.Name = "Calibri";
testShape.Font.Size = 11;

int[] size = testShape.CalculateTextSize();
// SET THE Height and Width as returned by .CalculateTextSize()
testShape.Height = size[1];
// testShape.Width = size[0];
testCells[17, 5].Value = "Text Box Height - After Setting Properties";
testCells[17, 6].Value = testShape.Height;
//testWorkbook.Save(@"C:\TextBoxHeightIssue.Xlsb", SaveFormat.Xlsb);
//Process.Start(@"C:\TextBoxHeightIssue.Xlsb");

testWorkbook.Save(@"TextBoxHeightIssue.Xlsx", SaveFormat.Xlsx);
//Process.Start(@"C:\TextBoxHeightIssue.Xlsx");
}
53

The issues you have found earlier (filed as CELLSNET-42401;CELLSNET-42104) have been fixed in this update.

This message was posted using Notification2Forum from Downloads module by Aspose Notifier.
54

Hi Shakeel,

I have implemented Aspose Cells 8.0.2 and done some initial testing with this version in respect of the Text Box issue. So far all looks ok.

Will keep testing and let you know if any additional issues show up.

Thanks,

Mike

55

Hi Mike,

Thanks for your feedback and using Aspose.Cells.

It is good to know that everything is working good at your end and this issue is resolved with the latest version. Let us know if you encounter any other issue related to this issue, we will look into it and help you further.