Bug 52484 - Superscript font works for XSSFWorkbook but not SXSSFWorkbook
Summary: Superscript font works for XSSFWorkbook but not SXSSFWorkbook
Status: RESOLVED WONTFIX
Alias: None
Product: POI
Classification: Unclassified
Component: SXSSF (show other bugs)
Version: 3.8-dev
Hardware: PC All
: P2 enhancement (vote)
Target Milestone: ---
Assignee: POI Developers List
URL:
Keywords:
Depends on: 57401
Blocks:
  Show dependency tree
 
Reported: 2012-01-18 16:27 UTC by Greg Jungman
Modified: 2015-11-30 09:53 UTC (History)
1 user (show)



Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description Greg Jungman 2012-01-18 16:27:13 UTC
I changed my code to use SXSSFWorkbook (no other change) and my footnotes (which I superscript) stopped super-scripting.  It worked in XSSFWorkbook and I'm having to roll back.

The below code does the super-scripting.  I can send you the Excel files if you need.  Thanks.



     footnoteFont = wb.createFont();
     footnoteFontSuper = wb.createFont();
     footnoteFontSuper.setTypeOffset(Font.SS_SUPER);

...

     String fs = footnoteString.toString();
     String totalString = header + fs;
     RichTextString richString = createHelper.createRichTextString(totalString);

     richString.applyFont(header.length(), totalString.length(), footnoteFontSuper);
     richString.applyFont(0, header.length(), headerFont);
     cell.setCellValue(richString);
Comment 1 Yegor Kozlov 2012-01-19 17:26:20 UTC
Rich text in SXSSF is not supported. All formatting applies via RichTextString.applyFont() will be lost.

However you can style cells as a whole. Create a cell style with a superscript font and pass it to Cell.setCellStyle:

CellStyle style = wb.createStyle();
style.setFont(footnoteFontSuper);

...

cell.setCellStyle(style);


Yegor
Comment 2 Greg Jungman 2012-01-19 18:13:45 UTC
Thanks for the reply, Yegor.  

Will it ever be supported?  

Your suggestion wouldn't look like a footnote, as either the entire cell would be superscripted, or the footnotes would float off away from the text in their own superscript column, which defeats the logic behind a super-script.

In HTML and Java Swing you can superscipt:

My Row Header <sup>(1)</sup)
My Second Row Header
Third <sup>(2)></sup>
Comment 3 Jim Talbut 2012-01-19 19:18:32 UTC
Yegor,
Is it a vast amount of work to get RichText support in SXSSF?
I can't use SXSSF without it :(

I'm swamped at the moment, but I should be able to give some time to it soon(ish) if you can lay out what needs doing.
Jim
Comment 4 Yegor Kozlov 2012-01-20 07:07:53 UTC
It is a conceptual limitation. In a normal Excel file strings are stored in a special file called Shared String Table (SST) and it acts as a cache of strings. Cells don't store string values, instead they store a reference into the SST and two cells holding the same string will have the same reference into the SST.  
An alternative way to store strings is inside cell but in this case only plain text can be written. Rich text is supported only in SST.


SXSSF is a streaming, forward-only API and it writes data immediately as it is passed to the API. This means that SXSSF  does not cache strings. When a rich text string is passed it is converted into plain text and written as a plain text cell value. I don't see an easy way to improve it.

If you want all Excel features then give more memory to Java and use XSSF. 

P.S. I'm going to reflect it in the documentation. The Spreadsheet API feature summary (http://poi.apache.org/spreadsheet/index.html) should explicitly say that rich text is not supported in SXSSF.


Yegor

(In reply to comment #2)
> Thanks for the reply, Yegor.  
> 
> Will it ever be supported?  
> 
> Your suggestion wouldn't look like a footnote, as either the entire cell would
> be superscripted, or the footnotes would float off away from the text in their
> own superscript column, which defeats the logic behind a super-script.
> 
> In HTML and Java Swing you can superscipt:
> 
> My Row Header <sup>(1)</sup)
> My Second Row Header
> Third <sup>(2)></sup>
Comment 5 Jim Talbut 2012-01-20 09:30:19 UTC
How does the SST differ from the Styles table, which is also maintained in memory and referenced by the streaming SXSSF?
In other words, whilst SXSSF obviusly couldn't work if all cells were RichText (because the SST would have to be in memory and SXSSF wouldn't gain you anything) couldn't it still be beneficial if only a minority of the cells are RichText?

We'd need to have an option to tell SXSSF which way to handle RichText, but for my circumstance it would work (I usually have either huge sheets or fancy sheets, but I don't know which until after I've started outputting them).
Comment 6 Yegor Kozlov 2012-01-20 11:06:46 UTC
In theory we can stream SST too. In this case it wouldn't be a cache of strings - we will simply write out strings and set the SST index in the cell. 

It is not trivial and will take some time to implement. The starting point is SSTDataWriter which will convert rich text to xml and write into a temp file. The implementation should be similar to SheetDataWriter which does this job for cells and rows. SSTDataWriter  should keep track of the last written index and total number of entries in the SST. 

Next we will tweak SXSSF to delegate string data to SSTDataWriter and update the SST index in the cell.   

This is something to plan for POI-3.9. Until we release 3.8-final I would stay on the safe side and keep SXSSF as it is. 

Yegor

(In reply to comment #5)
> How does the SST differ from the Styles table, which is also maintained in
> memory and referenced by the streaming SXSSF?
> In other words, whilst SXSSF obviusly couldn't work if all cells were RichText
> (because the SST would have to be in memory and SXSSF wouldn't gain you
> anything) couldn't it still be beneficial if only a minority of the cells are
> RichText?
> 
> We'd need to have an option to tell SXSSF which way to handle RichText, but for
> my circumstance it would work (I usually have either huge sheets or fancy
> sheets, but I don't know which until after I've started outputting them).
Comment 7 Javen O'Neal 2015-11-30 09:53:29 UTC
There's a solution posted to bug 57401 that implements a Shared Strings Table in a memory heap backed by a database on disk (MapDB). This would hopefully allow the storage of RichTextStrings in a memory-efficient way (at the cost of speed). It's be a good idea to let the user pick the strings storage mechanism so they can make POI meet their needs.