Character Level Formatting Problem

Aspose.Cells Product Family
1

I'm attempting to implement a limited inclusion of HTML formatting on on a cell and I seem to have come up against a problem. Specifically the Aspose.Cells component doesn't seem to correctly apply formatting when the character groups do not match up. Take the following string example:

"Table: 1 Table:"

What you want to end up with is:

Table: 1 Table:

However, that is not what I'm getting in Excel. In Excel we get:

Table: 6 Table:

I'm processing each layer of formatting seperately and it seems that when the formatting falls across different sets of characters it doesn't combine correctly.

aCell.PutValue("Table: 1 Table:");

aCell.Characters(0, 6).Font.IsBold = true;

aCell.Characters(9. 6).Font.IsBold = true;

aCell.Characters(4, 9).Font.Underline = Aspose.Cells.FontUnderlineType.Single;

aCell.Characters(0, 6).Font.IsItalic = true;

Currently using Aspose.Cells version: 4.5.0.26

Am I doing something wrong? Or is this a bug?


2

Hi,

Thanks for providing us the details.

We have found the issue after implementing your scenario and using your code.

We will figure it out soon.

Thank you.

3

Hi,

After investigating and exploring your issue thoroughly, we come to know that it 's not a bug. Actually, according to your code (for your scenario), some characters with their attributes may cross some other characters with their formattings, so, there might be a clash on formattings b/w characters.

So, please change your code to, it will suit your need:

aCell.PutValue("Table: 1 Table:");
Characters characters = aCell.Characters(0, 3);
characters.Font.IsItalic = true;
characters.Font.IsBold = true;

characters = aCell.Characters(3, 3);
characters.Font.IsItalic = true;
characters.Font.IsBold = true;
characters.Font.Underline = Aspose.Cells.FontUnderlineType.Single;

characters = aCell.Characters(6, 3);
characters.Font.Underline = Aspose.Cells.FontUnderlineType.Single;

characters = aCell.Characters(9, 3);
characters.Font.IsBold = true;
characters.Font.Underline = Aspose.Cells.FontUnderlineType.Single;

characters = aCell.Characters(12, 3);
characters.Font.IsBold = true;

Thank you.

4

That would work if I was alsways going to process the exact same text. However, I provided the example string and code as a method to easily replicate the issue. The string will not be static and may or may not contain , or tags at any location within the text. The current full function looks like:

private void write_Annotation(String sAnnotation, Int32 iStartRow, Int32 iColumn, Aspose.Cells.TextAlignmentType aAlign)
{
String[] sLines;
Int32 iCurrentRow;
String sCleanedLine;
Aspose.Cells.Cell aCell;
Int32 iStart;
Int32 iEnd;
String sTargetFragment;
String sPreFragment;
string[] sTagArray = { "b", "u", "i" };

if (sAnnotation.Length > 0)
{
sLines = sAnnotation.Split('\n');
iCurrentRow = iStartRow;

foreach (String sLine in sLines)
{
sCleanedLine = Strip_Html(sLine);

aCell = Worksheet.Cells[iCurrentRow, iColumn];
aCell.PutValue(sCleanedLine);
aCell.Style.Font.Size = 8;
aCell.Style.HorizontalAlignment = aAlign;

foreach (String sTag in sTagArray)
{
Int32 iStartIndex = 0;
while (sLine.IndexOf("<" + sTag + ">", iStartIndex, StringComparison.CurrentCultureIgnoreCase) != -1)
{
iStart = sLine.IndexOf("<" + sTag + ">", iStartIndex, StringComparison.CurrentCultureIgnoreCase);
iEnd = sLine.IndexOf("", iStart + 1, StringComparison.CurrentCultureIgnoreCase);
sTargetFragment = Strip_Html(sLine.Substring(iStart, iEnd - iStart + 4));
if (iStart > 0)
{
sPreFragment = Strip_Html(sLine.Substring(0, iStart));
}
else
{
sPreFragment = "";
}

switch (sTag)
{
case "b":
aCell.Characters(sPreFragment.Length, sTargetFragment.Length).Font.IsBold = true;
break;
case "u":
aCell.Characters(sPreFragment.Length, sTargetFragment.Length).Font.Underline = Aspose.Cells.FontUnderlineType.Single;
break;
case "i":
aCell.Characters(sPreFragment.Length, sTargetFragment.Length).Font.IsItalic = true;
break;
}
iStartIndex = iStart + 1;
}
}

iCurrentRow++;
}
}
}

What you can see here is that we pass a string into the function that may contain HTML code. We strip the HTML and load the cleaned text into the cell. Finally we look at the original string to identify characters that should have formatting applied.

5

I tried to look at an alternative method to implement this feature. Rather than setting formatting on a range of characters I'm applying the formatting to each character individually. I think this is more of a get-around than a good implementation, however it seems to yield slightly better results.

However, it has spawned another problem. XLS files generated open fine, however XLSX files now contain invalid characters in the XML. I've attached the template file. If you generate and XLSX file from this you should get either:

a) Unable to convert in Office 2003

b) File contains invalid characters in Office 2007

6

Hi,

Thank you for considering Aspose.

Well, we are not very clear about the problem you have mentioned. Can you please provide some more details. Also, it will be very appreciated, if you can create a sample console application regarding this issue and post it. This way it will be a lot easier for us to figure out the problem.

Thank you & Best Regards,

7

You can replicate my current problem using the following code:

Dim Workbook As New Aspose.Cells.Workbook
Dim Worksheet As Aspose.Cells.Worksheet
Dim Cell As Aspose.Cells.Cell
Dim Index As Integer


Worksheet = Workbook.Worksheets(0)
Cell = Worksheet.Cells(1, 5)
Cell.PutValue("Table: 1 Table:")

For Index = 0 To 6
Cell.Characters(Index, 1).Font.IsBold = True
Next

For Index = 9 To 14
Cell.Characters(Index, 1).Font.IsBold = True
Next

For Index = 3 To 11
Cell.Characters(Index, 1).Font.Underline = Aspose.Cells.FontUnderlineType.Single
Next

For Index = 0 To 6
Cell.Characters(Index, 1).Font.IsItalic = True
Next

'Workbook.Save("C:\Documents and Settings\SPSS\Desktop\Test2003.Xls", Aspose.Cells.FileFormatType.Excel2003)
Workbook.Save("C:\Documents and Settings\SPSS\Desktop\Test2007.Xlsx", Aspose.Cells.FileFormatType.Excel2007Xlsx)
MessageBox.Show("Done")

When you run the "2007" export the file does not open correctly. If you comment that out and export to "2003" it opens fine.

8

Hi,

Thanks for providing us the sample code.

Yes, after an initial test, we can reproduce the issue applying your code, we will figure it out soon.

Thank you.

9

Hi,

Please try the attached version (4.5.1.14).

Thank you.

10

That looks to have solved my problem. Thanks

11

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

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