Cells get Portion with different Format

Aspose.Cells Product Family
1

Hello,
I’m writing you to ask of little favour. (It’s not strongly requirement, just little request)
The deal is that we use set of aspose libraries. And for words and cells we can get so little portion of text, that contains characters formatted only with one fonts, or only one language.
For example:“hiragana ひらがな” will be splitted to “hiragana” and “ひらがな” parts.

There is any possibility to get similar opportunity for aspose-cells? I found only cell.getStringValue() but it returns the whole “hiragana ひらがな” from one cell.

Best Regards, Akane

2

Hi Akane,

Thanks for your posting and using Aspose.Cells.

Is this feature available in Aspose.Words already? If yes, could you please provide a sample word document containing your sample text and sample code accessing this text in portions as you are describing.

Please also provide us an Excel workbook containing your sample text so that we could investigate this issue at our end.

We will look into it and log a New Feature request in our database to support this issue in our future versions. Thanks for your cooperation.

3

We use further code for aspose-words:

…
WordFontChanger fc = new WordFontChanger ();
document.accept(fontChanger);
…
public class WordFontChanger extends DocumentVisitor {
…
public int visitRun(Run run) throws Exception {
…
run.getText(); //<<HERE WE GET SEPARATELY PARTS FOR “hiragana” and “<span style=“font-family:“MS Gothic”;mso-bidi-font-family:“MS Gothic”;mso-fareast-language:
JA” lang=“JA”>ひらが<span style=“font-family:
“MS Mincho”;mso-bidi-font-family:“MS Mincho”;mso-fareast-language:JA” lang=“JA”>な”
…
return VisitorAction.CONTINUE;
}

For aspose-cells:
Worksheet sheet = book.getWorksheets().get(i);
Cells cells = sheet.getCells();
for (int j = 0; j < cells.getCount(); j++) {
Cell cell = cells.get(j);
cell.getStringValue(); <<HERE WE GET “hiragana<span style=“font-family:“MS Gothic”;mso-bidi-font-family:“MS Gothic”;mso-fareast-language:
JA” lang=“JA”>ひらが<span style=“font-family:
“MS Mincho”;mso-bidi-font-family:“MS Mincho”;mso-fareast-language:JA” lang=“JA”>な”
…
}

Well, actually I can be wrong using this way to get cell’s text. What do you think? Could you be so kind to give some advice for that?

Thank you in advance
4

Hi Akane,

Thanks for your posting and using Aspose.Cells.

We have logged your requirement in our database for evaluation. We will look into it and see if this feature is implementable or not. Once, there is some fix or some other update for you, we will let you know asap.

This issue has been logged as

  • CELLSJAVA-41269 - Get portion of the text with different formats

5

Hi,

Please use Cell.getCharacters() method for your needs. See the following sample code:
e.g.
Sample code:


Workbook workbook = new Workbook(dataDir + "2.xlsx");
Cells cells = workbook.getWorksheets().get(0).getCells();
for(Object cell0 : cells)
{

Cell cell = (Cell)cell0;
if (cell.isRichText())
{

String str = cell.getStringValue();
FontSetting[] fs = cell.getCharacters();
for (int i = 0 ; i < fs.length; i++)
{

FontSetting f = fs[i];
System.out.println(f.getFont().getName() + ":" + str.substring(f.getStartIndex(), f.getStartIndex() + f.getLength() - 1));
}
}
else
{

System.out.println(cell.getStringValue());

}

Thank you.

6

Hello
Sorry that I have not reply so long.
Thank you very much. Your solution works perfectly.
But now I found another problem.
I found that I can’t change font manually if it’s missing.

For example, please see attached document.
There is 6 cells.
Three cells (B1,2,3) have only “Calibri” font, which is absent on my system.
Three
cells (A1,2,3) have set of fonts for different parts of text, e.g A2:
“구성 파일을 로드할 수 없습니다”. - ‘Batang’ font, “(Batang)” - ‘Calibri’ font. Both fonts are absent.
I perform conversion with changing fonts:

Cells cells = sheet.getCells();
if (cells != null) {
cells.setDefaultRowHeightMatched(true);
for (Object cell0 : cells) {
Cell cell = (Cell) cell0;
Style style = cell.getStyle();
Font f = style.getFont();
f.setColor(Color.getRed());
cell.setStyle(style);
…
}
}

But result looks like in attached file.
May be I make mistake or there is bug?
And a little more:
Actually I would like to set font for every part of text manually like:
FontSetting[] fs = cell.getCharacters();

for (int i = 0 ; i < fs.length; i++)

{
FontSetting f = fs[i];
Font font = f.getFont();
font.setName(“New Name”);

}

Is it correct?

Best Regards,
Akane

7

Hi Akane,


Thank you for writing back,

I have evaluated your recently shared scenario a bit, and I think you are correct. Changing the font for a cell as whole or part of the text in a cell does not seem to take effect. I have tried two different methods as elaborated below.

Java

Workbook book = new Workbook(“D:/book20.xlsx”);
Cells cells = book.getWorksheets().get(0).getCells();
for(int row=0;row<cells.getMaxDataRow()+1;row++)
{
for(int col=0;col<cells.getMaxDataColumn()+1;col++)
{
Cell cell = cells.get(row, col);
System.out.println(cell.getName());
if(cell.getDisplayStringValue() != “” && cell.isRichText())
{
FontSetting[] fs = cell.getCharacters();
for (int i = 0 ; i < fs.length; i++)
{
FontSetting f = fs[i];
Font font = f.getFont();
System.out.println(f.getFont().getName() + “:” + cell.getDisplayStringValue().substring(f.getStartIndex(), f.getStartIndex() + f.getLength()));
font.setName(“Arial”);
System.out.println(f.getFont().getName() + “:” + cell.getDisplayStringValue().substring(f.getStartIndex(), f.getStartIndex() + f.getLength()));
}
}
//Setting style of Cell
/*
Style style = cell.getStyle();
Font font = style.getFont();
font.setName(“Arial”);
cell.setStyle(style);
*/
}
}
book.save(“D:/output.pdf”, SaveFormat.PDF);
book.save(“D:/output.xlsx”, SaveFormat.XLSX);

Please note, I have logged this incident for product team's review under the ticket CELLSJAVA-41350. Please spare us some time to further evaluate the case and get back to you with updates in this regard.
8

Hi,

Thanks for using Aspose.Cells.

This is to inform you that we have fixed your issue CELLSJAVA-41350 now. We will soon provide the fix after performing QA and including other enhancements and fixes.

9

Hi,

Thanks for using Aspose.Cells for Java.

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

10

Hello,
I’m apologize if I’m wrong but could you be so kind to check if changing of text color works?
I’ve tried:
FontSetting[] fontSettings = cell.getCharacters();
Style s = cell.getStyle();
Font p = s.getFont();
p.setColor(Color.getRed());
cell.setStyle(s);
if (fontSettings != null) {
for (FontSetting fontSetting : fontSettings) {
font = fontSetting != null ? fontSetting.getFont() : null;
if (font != null) {
font.setName(“Arial”);
font.setColor(Color.getRed());
font.setBold(true);
font.setItalic(true);
}
}
}
But I don’t see that style was changed.
Probably I’m missing something? Could you please help?
Thank you in advance

11

Hi Akane,

Thank you for writing back.

If you wish to apply the style to a whole cell then the following code is working perfectly on my side while using the latest version of Aspose.Cells for Java 8.6.0.6 against a sample of my own.

Java

Style s = cell.getStyle();
Font p = s.getFont();
p.setColor(Color.getRed());
cell.setStyle(s);
cell = cells.get(“A2”);

If you wish to format a particular portion of a cell then use the following piece of code. Please also have a look at the relevant article on this subject.

Java

Font font = cell.characters(0, 5).getFont(); font.setName("Arial"); font.setColor(Color.getRed()); font.setBold(true); font.setItalic(true);

12

Hello,
Sorry for bothering. Now I’ve catched my main problem with null for FontSettings (I’ve already mentioned of it in another thread)
But I still don’t see successful result of original issue CELLSJAVA-41350. For cells contained complecated text of two different missing fonts the fonts are not changed at all (because of further font substitution, I suppose)
(Files, results and code are tha same as mentioned before in the thread)

Best Regards

13

Hi,


Have you tried the code snippet provided in my previous post? Please note, there are two API offered by Cell class, that are; Cell.getCharacters & Cell.characters where first should be used for inspection and second can be used to manipulate the styles for a potion of text in a cell. Please go through the relevant article on formatting selected characters and give it a try on your side.

That said, I have also logged your concerns to the aforementioned ticket, and have requested the product team to provide the justification on this scenario. As soon as we receive further updates, we will let you know here.

14

Hi,

Thanks for using Aspose.Cells.

For your issue

  • CELLSJAVA-41350 - Changing the font of the cell or part of the text in a cell does not take effect

Please call Cell.SetCharacters() methods if you want to update them.



Please see the following code


Java

Cell cell = cells.get(row, col);


System.out.println(cell.getName());


if(cell.getDisplayStringValue() != “” && cell.isRichText())


{


FontSetting[] fs = cell.getCharacters();


for (int i = 0 ; i < fs.length; i++)


{


FontSetting f = fs[i];


Font font = f.getFont();


System.out.println(f.getFont().getName() + “:” +
cell.getDisplayStringValue().substring(f.getStartIndex(),
f.getStartIndex() + f.getLength()));


font.setName(“Arial”);


System.out.println(f.getFont().getName() + “:” +
cell.getDisplayStringValue().substring(f.getStartIndex(),
f.getStartIndex() + f.getLength()));


}


cell.setCharacters(fs)


}

15

Hello,
Thank you for expanded explanation, I’ve missed setCharacters. So sorry for bothering.
Now it works
Best Regards

16

Hi Akane,


You are most welcome. Please feel free to contact us back in case you need our further assistance with Aspose APIs.
17

The issues you have found earlier (filed as CELLSJAVA-41350) have been fixed in .

Aspose.Cells for Java (Download | Maven).

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

Hello,
Sorry for so big delay. I’d just like to confirm that the issue is fixed (we’ve tested it on our end).
Best Regards

19

Hi again,


Thank you for the confirmation. Please feel free to contact us back in case you need our further assistance with Aspose APIs.
20

The issues you have found earlier (filed as ) have been fixed in this update. This message was posted using BugNotificationTool from Downloads module by MuzammilKhan