Cell.setHtmlString does not apply the font name style properly

Aspose.Cells Product Family
1

Hi Team,

I am trying to set a html string for a cell.

Example text – "Hello world, <b>how are you ?</b><hl>http://google.co.in</hl>"

In this cutomized text exmaple, whatever text is enclosed within <hl> and </hl>, we are trying to make that text as hyperlink for the remaining text.
And whatever enclosed within <b>and </b> needs to appear as bold.

And we need to apply “Avenir Next LT Pro” font name for the entire text and color should be red.

So we trying to first apply all the required styles to a cell and then setting the htmlString.

On doing the cell.SetHtmlString, we are able to get the hyperlink, bold text and font colour. But font name is always showing as “Arial” instead of “Avenir Next LT Pro” in the downloaded excel file.

Sample code which I used -

final String HYPERLINK_START_DELIMETER = "<hl>";
    final String HYPERLINK_END_DELIMETER = "</hl>";

    // Instantiating a Workbook object
    Workbook workbook = new Workbook();
    // Adding a new worksheet to the Workbook object
    workbook.getWorksheets().add();
    // Obtaining the reference of the newly added worksheet by passing its sheet index
    Worksheet worksheet = workbook.getWorksheets().get(0);

    String content = "Hello world, <b>how are you ?</b><hl>http://google.co.in</hl>";

    Cell cell = worksheet.getCells().get("A5");

    applyTextStyle(cell);

    cell.setHtmlString(content.substring(0, content.indexOf(HYPERLINK_START_DELIMETER)));
    worksheet.getHyperlinks().add(CellsHelper.cellIndexToName(4, 0), 1, 1,
        content.substring(
            content.indexOf(HYPERLINK_START_DELIMETER) + HYPERLINK_START_DELIMETER.length(),
            content.indexOf(HYPERLINK_END_DELIMETER)));
    // Saving the Excel file
    workbook.save("D:\\test.xlsx");

  }

  public static void applyTextStyle(Cell cell) {
    Style style = cell.getStyle();
    style.getFont().setBold(false);
    style.getFont().setUnderline(FontUnderlineType.DASH);
    style.getFont().setName("Avenir Next LT Pro");
    final Color color = Color.decode("#FF0000");
    style.getFont().setColor(
        com.aspose.cells.Color.fromArgb(255, color.getRed(), color.getGreen(), color.getBlue()));
    style.getFont().setDoubleSize(10);
    style.setHorizontalAlignment(TextAlignmentType.CENTER);
    cell.setStyle(style);
  }

And the downloaded file is test.zip (7.5 KB)

Could you please let us know why the applied font name is not coming properly in downloaded excel ? is it a bug in aspose or should I change something in my code. ?

2

@Thilakbabu,

I have evaluated your issue a bit. It seems when you apply the html string to the cell, it sets the formatting with default font. For your needs, you should move the line of code, i.e., applyTextStyle(cell); after the following line(s):

cell.setHtmlString(content.substring(0, content.indexOf(HYPERLINK_START_DELIMETER)));
        worksheet.getHyperlinks().add(CellsHelper.cellIndexToName(4, 0), 1, 1, content.substring(content.indexOf(HYPERLINK_START_DELIMETER) + HYPERLINK_START_DELIMETER.length(), content.indexOf(HYPERLINK_END_DELIMETER)));

it will work ok.

3

Hi @Amjad_Sahi,

If I move the applyTextStyle(cell); after the mentioned line, BOLD style is not getting applied. I already tried this. Please check and update.

image.png (9.6 KB)

4

@Thilakbabu,

I found it is not an issue even using your original code, so you do not need to move the line of code “applyTextStyle(cell);”. When you open the output file into MS Excel, the cell is in correct font but shows as it is “Arial”. Please select the cell (via arrow key) and then click in the formula bar (via mouse) and it will show correct font name in above tool textbox now.

5

@Thilakbabu
1,If you add a hyperlink, the style of the cell will be changed as hyperlink style.
2,If a cell contains Hyperlink , rich formatted text will be ignored in MS Excel, so you can not see Bold.

6

Hi @Amjad_Sahi,

We have an use case, where we need to support an angular brace “>” for text in setHtmlString .

Could you please let us know how to support the angular braces and also any other special characters ?

Example string -

 String content = "Hello world, <b>how are you >= myself ?</b><hl>http://google.co.in</hl>";

Thanks in advance

7

@Thilakbabu,

You may use “&gt;” character entity in html string. See the following sample line of code that you may try:

cell.setHtmlString("<html>Hello world, <b>how are you &gt;= myself ?</b><hl>http://google.co.in</hl></html>");

Hope, this helps a bit.

8

Thanks for the quick response @Amjad_Sahi

9

@Thilakbabu,

You are welcome.