Overlapping of Text when Multiple text Boxes are Present

Hi Babar/Team,

As requested I am providing the Input file and the code used for reformatting text Boxes,Please help us out in the text Overlap issue facing after Processing it through aspose methods using following Code ,Unprocessed.xlsx is the input file and Processed.xlx is the Output File



private static Workbook AddCommentaryTextboxes(Workbook workbook)

{



FindOptions findOptions = new FindOptions();

findOptions.LookAtType = LookAtType.Contains;

findOptions.CaseSensitive = false;

foreach (Worksheet sheet in workbook.Worksheets)

{

Cell cell = sheet.Cells.Find("##Commentary##", null,findOptions);



while (cell != null)

{

string commentaryblock = cell.Value.ToString();

string[] separators = new string[] { “##” };

string[] parts = commentaryblock.Split(separators, StringSplitOptions.None);



int top = cell.Row;

int left = cell.Column;

int height = Convert.ToInt32(parts[2]);

int width = Convert.ToInt32(parts[3]);

string text = parts[4];



int box = sheet.TextBoxes.Add(top, left, height, width);







Style style;

style = cell.GetStyle();

sheet.TextBoxes[box].Font.Color = style.Font.Color; cell.GetStyle();

sheet.TextBoxes[box].Font.Name = style.Font.Name;

sheet.TextBoxes[box].Font.IsBold = style.Font.IsBold;

sheet.TextBoxes[box].Font.IsItalic = style.Font.IsItalic;

sheet.TextBoxes[box].Font.Size = style.Font.Size;

sheet.TextBoxes[box].Placement = PlacementType.FreeFloating;

sheet.TextBoxes[box].IsTextWrapped = true;

sheet.TextBoxes[box].LineFormat.IsVisible = false;

sheet.TextBoxes[box].TextFrame.AutoSize =true;

sheet.TextBoxes[box].Text = text;



sheet.Cells.ClearContents(cell.Row, cell.Column, cell.Row, cell.Column);

cell = sheet.Cells.Find("##Commentary##", cell,findOptions);

}



using (MemoryStream saveStream = new MemoryStream())

{

workbook.Save(saveStream, SaveFormat.Xlsx);

}



return workbook;

}

Hi,


Thanks for the template files and sample code.

Well, I think you need to re-calculate the Text area of the TextBoxes using Shape.CalculateTextSize() method to get the updated height and width and then set the height of and width of the Text Boxes accordingly. Finally, as you are inserting Textboxes in place of cell values, so you got to set the cell’s column width and row height in accordance with Textbox’s height and width, see the sample code for your reference, it works well as I tested. Please refer to the following code segment and you may update or write your own code accordingly for your needs.
e.g
Sample code:

Workbook workbook = new Workbook(“e:\test2\Unprocessed.xlsx”);
FindOptions findOptions = new FindOptions();
findOptions.LookAtType = LookAtType.Contains;
findOptions.CaseSensitive = false;
Worksheet sheet = workbook.Worksheets[0];
Cell cell = sheet.Cells.Find("##Commentary##", null, findOptions);

while (cell != null)
{
string commentaryblock = cell.Value.ToString();
string[] separators = new string[] { “##” };
string[] parts = commentaryblock.Split(separators, StringSplitOptions.None);
int top = cell.Row;
int left = cell.Column;
int height = Convert.ToInt32(parts[2]);
int width = Convert.ToInt32(parts[3]);
string text = parts[4];
int box = sheet.TextBoxes.Add(top, left, height, width);
Style style;
style = cell.GetStyle();
sheet.TextBoxes[box].Font.Color = style.Font.Color; cell.GetStyle();
sheet.TextBoxes[box].Font.Name = style.Font.Name;
sheet.TextBoxes[box].Font.IsBold = style.Font.IsBold;
sheet.TextBoxes[box].Font.IsItalic = style.Font.IsItalic;
sheet.TextBoxes[box].Font.Size = style.Font.Size;
sheet.TextBoxes[box].Placement = PlacementType.FreeFloating;
sheet.TextBoxes[box].IsTextWrapped = true;
sheet.TextBoxes[box].LineFormat.IsVisible = false;
sheet.TextBoxes[box].TextFrame.AutoSize = true;
sheet.TextBoxes[box].Text = text;
int[] size = sheet.TextBoxes[box].CalculateTextSize();
//Set width and height of the Textbox
sheet.TextBoxes[box].Width = size[0];
sheet.TextBoxes[box].Height = size[1];
sheet.Cells.ClearContents(cell.Row, cell.Column, cell.Row, cell.Column);
// sheet.Cells.SetColumnWidthPixel(cell.Column,size[0]);
sheet.Cells.SetRowHeightPixel(cell.Row, size[1]);
cell = sheet.Cells.Find("##Commentary##", cell, findOptions);
workbook.Save(“e:\test2\out1.xlsx”, SaveFormat.Xlsx);

Hope, this helps a bit

Thank you.

Thanks Amjad/Team For your suggestion though it fixes the Over lap issue with the above Code some of the above tables present in the sheet are elongated .

I found out the root cause of the issue and found out that allow Text to Over Flow Shape Text Box Property is getting Checked so text is out of the text Box.

Can you please let us know ,How to disable OverFlow property from Aspose as we do in excel Manually?
In excel we rightClick on the Text Box ,Click On the Size and Properties and uncheck Allow Text to OverFlow Property

Hi,


I am afraid, I think Aspose.Cells does not provide any API/means to disable “Allow Text to Overflow” option for the TextBox shape. Surely, when you extend the relevant columns’ width as per the updated width of the TextBox, it will extend the column(s) width of the above tables too. I think you should comment the following line of code as per my code segment in my previous reply:
e.g
Sample code:

sheet.Cells.SetColumnWidthPixel(cell.Column,size[0]);

it may suit your needs.

Thank you.

Thanks Amjad for your Sugestion ,if the Rowheight exceeds greater than 409 it will thrown an exception.If the row height is between 0 and 409 the textbox resize works fine.

One thing we observed in Old Aspose version Allow text to Overflow option was unchecked by default but in new Aspose it is getting checked by default.Any fix would be done fix the text Over Flow Issue ?

Hi,

Dharshan:
....if the Rowheight exceeds greater than 409 it will thrown an exception.If the row height is between 0 and 409 the textbox resize works fine.
Well, this is MS Excel limitation and nothing to do with Aspose.Cells APIs. You cannot extend a cell's height more than 409, you may confirm this in MS Excel manually. Aspose.Cells follows Ms Excel standards that throws such exception when you set a row's height > 409.

Dharshan:
One thing we observed in Old Aspose version Allow text to Overflow option was unchecked by default but in new Aspose it is getting checked by default.Any fix would be done fix the text Over Flow Issue ?

Could you provide more details which most recent (previous) version of Aspose.Cells disables Allow Text to Overflow option for shapes, we will check it soon.

Thank you.

Hi Amjad,
The version was 5.1.2.0 where disabling of Text Overflow was Happening

Hi,


Thanks for providing us further details.

I tried to figure out your issue (Disable “Allow text to overflow shape” option via Aspose.Cells API) but to no avail. When, I add a new text box, the “Allow text to overflow shape” option (MS Excel 2010/2013) is always checked for TextBox. I could not find any suitable API to make it unchecked. I have logged a ticket with an id “CELLSNET-44173” for your issue/ requirements. We will check if we could support this feature or provide your the code segment or workaround if this is already supported or make this option unchecked at least when we add a new TextBox to the sheet.

Once we have an update on it, we will let you know here.

Thank you.

Hi Team,
Is there any Update on the fix or work around?

Hi,


I am afraid, your issue logged earlier as “CELLSNET-44173” is not resolved. Please spare us a little time (a week or so), we will try to figure it out soon. I have also asked the concerned developer from product team to update on it, provide an ETA (if it takes more time than usual) or provide a workaround to cope with it.

Once we have an update on it, we will surely let you know here immediately.

Thank you.

Hi,


Please try to add the following line of code to your code segment as we have already supported this option in the Aspose.Cells APIs:
e.g
Sample code:
//Disable “Allow text to overflow shape” option for the Textbox shape.
sheet.TextBoxes[box].TextVerticalOverflow = TextOverflowType.Clip;


Let us know if you still have any issue.

Thank you.



Hi Amjad/Team,
After applying above Code also Overflow option is not getting Unchecked by default

Hi,


I have tested your scenario/case again with the latest fix/version v8.6.3.5, it works fine and as expected, see the screen shot attached here and find attached the output Excel file:
http://prntscr.com/9s15sl

I used the following sample code with your template file:
e.g
Sample code:

Workbook workbook = new Workbook(“e:\test2\Unprocessed.xlsx”);
FindOptions findOptions = new FindOptions();
findOptions.LookAtType = LookAtType.Contains;
findOptions.CaseSensitive = false;
Worksheet sheet = workbook.Worksheets[0];
Cell cell = sheet.Cells.Find("##Commentary##", null, findOptions);

while (cell != null)
{
string commentaryblock = cell.Value.ToString();
string[] separators = new string[] { “##” };
string[] parts = commentaryblock.Split(separators, StringSplitOptions.None);
int top = cell.Row;
int left = cell.Column;
int height = Convert.ToInt32(parts[2]);
int width = Convert.ToInt32(parts[3]);
string text = parts[4];
int box = sheet.TextBoxes.Add(top, left, height, width);
Style style;
style = cell.GetStyle();
sheet.TextBoxes[box].Font.Color = style.Font.Color; cell.GetStyle();
sheet.TextBoxes[box].Font.Name = style.Font.Name;
sheet.TextBoxes[box].Font.IsBold = style.Font.IsBold;
sheet.TextBoxes[box].Font.IsItalic = style.Font.IsItalic;
sheet.TextBoxes[box].Font.Size = style.Font.Size;
sheet.TextBoxes[box].Placement = PlacementType.FreeFloating;
sheet.TextBoxes[box].IsTextWrapped = true;
sheet.TextBoxes[box].LineFormat.IsVisible = false;
sheet.TextBoxes[box].TextFrame.AutoSize = true;
sheet.TextBoxes[box].Text = text;
sheet.TextBoxes[box].TextVerticalOverflow = TextOverflowType.Clip;

int[] size = sheet.TextBoxes[box].CalculateTextSize();
//Set width and height of the Textbox
sheet.TextBoxes[box].Width = size[0];
sheet.TextBoxes[box].Height = size[1];
sheet.Cells.ClearContents(cell.Row, cell.Column, cell.Row, cell.Column);
// sheet.Cells.SetColumnWidthPixel(cell.Column,size[0]);
sheet.Cells.SetRowHeightPixel(cell.Row, size[1]);
cell = sheet.Cells.Find("##Commentary##", cell, findOptions);


workbook.Save(“e:\test2\out1processed1.xlsx”, SaveFormat.Xlsx);


Please make sure you are using latest version/fix: v8.6.3.5.

Thank you.

I am unable download 8.6.3.5 version of Aspose dll,getting file not found error.

Can you please provide the right link to download aspose dll for Framework 3.5 and 4.0

Hi,


Well, this (v8.6.3.5) is .NET 2.0 compiled version of Aspose.Cells component, it will work fine on any .NET framework version >= 2.0 (e.g 2.x, 3.x, 4.0, 4.5 etc.). I have attached the fix/version: Aspose.Cells for .NET v8.6.3.5 to this post, could you get it now.

Thank you.

Thanks Amjad I am able to download this dll and will test and provide the feedback

If I try to Compile the Solution using the above dll 8.6.3.5 I am getting following error

Error 13 Could not load file or assembly ‘file:///C:\Corp ReportingBuild\Main\lib\Binaries\Aspose.Cells.dll’ or one of its dependencies. Operation is not supported. (Exception from HRESULT: 0x80131515) C:\TESTING\Corp ReportingVeracode - Copy\Main\src\BatchService\SGEN BatchService

So kindly provide dll with a Higher Framework version as 3 .5 or 4.0


Hi,

Well, you may try our latest official version: Aspose.Cells for .NET v8.6.3, please download and install it from here:
http://www.aspose.com/community/files/51/.net-components/aspose.cells-for-.net/entry678389.aspx
(After installation, you may pick your desired .NET framework compiled assembly @ your installation directory for your needs)

I have tested the scenario/ case using Aspose.Cells for .NET v8.6.3, it also works fine. I used the code segment pasted in my previous post:
https://forum.aspose.com/t/36570

Thank you.

Hi Amjad/Team,
It works when we save the File to Physical Location.But when we send it through email as an attatchment it is not working as expectes(Text to Overflow is getting checked by default)

Following is the code used for sending as an attatchment

using (Stream stm = new MemoryStream(processedWorkbookStream.ToArray()))
{
byte[] processedWorkbookStream1;
Workbook wBook = new Workbook(stm);

                                        using (MemoryStream saveStream = new MemoryStream())
                                        {

                                            wBook.Save(saveStream, SaveFormat.Xlsx);

                                            processedWorkbookStream1 = saveStream.ToArray();
                                        }

                                        TraceAdd("Attaching Excel Output to ReportMail");

                                        mail.Attachments.Add(new Attachment(new MemoryStream(processedWorkbookStream1), excelFileName, mimetype));
                                    }

Hi,


Thanks for providing us further details.

I am afraid, I am still unable to reproduce the issue on our end. I used the following sample code, I first save the output file to memory stream and then write to physical file stream from it, the output file (attached) is fine with “Allow text to overflow shape” option unchecked.
e.g
Sample code:

Workbook workbook = new Workbook(“e:\test2\Unprocessed.xlsx”);
FindOptions findOptions = new FindOptions();
findOptions.LookAtType = LookAtType.Contains;
findOptions.CaseSensitive = false;
Worksheet sheet = workbook.Worksheets[0];
Cell cell = sheet.Cells.Find("##Commentary##", null, findOptions);

while (cell != null)
{
string commentaryblock = cell.Value.ToString();
string[] separators = new string[] { “##” };
string[] parts = commentaryblock.Split(separators, StringSplitOptions.None);
int top = cell.Row;
int left = cell.Column;
int height = Convert.ToInt32(parts[2]);
int width = Convert.ToInt32(parts[3]);
string text = parts[4];
int box = sheet.TextBoxes.Add(top, left, height, width);
Style style;
style = cell.GetStyle();
sheet.TextBoxes[box].Font.Color = style.Font.Color; cell.GetStyle();
sheet.TextBoxes[box].Font.Name = style.Font.Name;
sheet.TextBoxes[box].Font.IsBold = style.Font.IsBold;
sheet.TextBoxes[box].Font.IsItalic = style.Font.IsItalic;
sheet.TextBoxes[box].Font.Size = style.Font.Size;
sheet.TextBoxes[box].Placement = PlacementType.FreeFloating;
sheet.TextBoxes[box].IsTextWrapped = true;
sheet.TextBoxes[box].LineFormat.IsVisible = false;
sheet.TextBoxes[box].TextFrame.AutoSize = true;
sheet.TextBoxes[box].Text = text;
sheet.TextBoxes[box].TextVerticalOverflow = TextOverflowType.Clip;

int[] size = sheet.TextBoxes[box].CalculateTextSize();
//Set width and height of the Textbox
sheet.TextBoxes[box].Width = size[0];
sheet.TextBoxes[box].Height = size[1];
sheet.Cells.ClearContents(cell.Row, cell.Column, cell.Row, cell.Column);
// sheet.Cells.SetColumnWidthPixel(cell.Column,size[0]);
sheet.Cells.SetRowHeightPixel(cell.Row, size[1]);
cell = sheet.Cells.Find("##Commentary##", cell, findOptions);
MemoryStream ms = new MemoryStream();
workbook.Save(ms, SaveFormat.Xlsx);
ms.Seek(0, SeekOrigin.Begin);
byte[] buffer = new byte[ms.Length];
buffer = ms.ToArray();
FileStream fs = new FileStream(“e:\test2\out1_processed1.xlsx”, FileMode.Create);
fs.Write(buffer, 0, buffer.Length);
fs.Close();
ms.Close();


I am not sure if there is any issue or something to do with Aspose.Cells APIs for your mentioned problem. I think you are also using Aspose.Email component to attach the output Excel file (by Aspose.Cells APIs) stream with the mail. Could you please create a separate standalone executable sample application along with the input and output Excel files here using Aspose.Cells for .NET v8.6.3.5 for further investigation, you may zip the project prior attaching here. Unfortunately, we might not evaluate your issue unless we reproduce the issue on our end.

Thank you.