You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by ofer <of...@gmail.com> on 2011/03/07 16:54:37 UTC
BigGridDemo for a RichText
Hi all,
I need to create an excel document that contains 20,000 records or so, and
contains rich text: links and cells that has part of the text in different font.
Since the memory consumption during the creation is very big (reaches 800MB) i
want to create it in a more efficient way, like the BigGridDemo.
Has anybody implemented a more "full" SpreadsheetWriter that can handle more
that plain inline text?
Is there any way to do it that i'm missing?
If not, can someone point me to how to implement something like this?
Thanks for any help
ofer
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: BigGridDemo for a RichText
Posted by Mark Beardsley <ma...@tiscali.co.uk>.
This morning, I had the chance to play around just a little and hope this
will explain a little better my post from yesterday.
Using Excel, I created a workbook with a single sheet in it. That sheet had
a couple of cells populated with Strings and one of these strings I
formatted
so that one word was emboldened, another had a different font face and size,
etc.
Saving the to disc and then unzipping it, I looked inside the
sharedStrings.xml file and found this fragment amoungst the mark-up
Rich
Text
String
What I would like to try is writing this mark-up - or something very like it
- directly
into the cell record that exists within the sheetn.xml file. If we are
lucky, Excel
will be able to interpret the mark-up correctly and render the results so
that the cell
appears to hold a rich text stirng - to borrow POI's terminology. If you
have the time, it might be
worth repeating the exercise, snipping from the xml in the sharedStrings.xml
file a chunk of mark-up
and then using the BigGridDemo code to write this mark-up directly into a
cell. Save the file and open it in Excel to see what the results are. Should
prove instructive if nothing else.
Yours
Mark B
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/BigGridDemo-for-a-RichText-tp3412641p3413864.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: BigGridDemo for a RichText
Posted by gagan <ga...@gmail.com>.
I am also looking out for the solution for this proble. Applying differet
font/styles to sub part of cell values.
In SXSSF,has anything been discovered to handle such scenarios?
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/BigGridDemo-for-a-RichText-tp3412641p5724980.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: BigGridDemo for a RichText
Posted by Ratna <ra...@gmail.com>.
Hi Nick,
I will explain the issue i faced,kindly take a look and suggest me.
We need an java export for nearly 1lakh records,in which in one cell in
the worksheet needs a multiple styling text(Say for example - "BLUE WIDGET"-
Blue in bold and Red color,widget in green color).
As i implemented SXSSF workbok ,iam able to create export but i am unable to
view the style as applyfont method wont work it out.
So iam currently working out in a way like biggriddemo in which for multiple
styling cell i used SST ,buit its is not at all working.
If any body having exact XML format including TAGS please share so that i
will use it.
Thanks in advance
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/BigGridDemo-for-a-RichText-tp3412641p5724968.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: BigGridDemo for a RichText
Posted by Ratna <ra...@gmail.com>.
As RICH text string wont work it out in SXSSF,i came back to biggriddemo,do u
have any sample xml,please attach it for rich textstring
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/BigGridDemo-for-a-RichText-tp3412641p5724967.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: BigGridDemo for a RichText
Posted by Nick Burch <ap...@gagravarr.org>.
On Sun, 2 Oct 2016, Ratna wrote:
> Could you please specify the XML format for each cell,as mine is not working
Don't use BigGridDemo any more! You should use SXSSF instead
Nick
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: BigGridDemo for a RichText
Posted by Ratna <ra...@gmail.com>.
Could you please specify the XML format for each cell,as mine is not working
<?xml version="1.0" encoding="UTF-8"?><worksheet
xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"><sheetData>
<row r="1">
<c r="A1" t="inlineStr" s="5"><is><t>Title</t></is></c><c r="B1"
t="inlineStr" s="5"><is><t>% Change</t></is></c><c r="C1" t="inlineStr"
s="5"><is><t>Ratio</t></is></c><c r="D1" t="inlineStr"
s="5"><is><t>Expenses</t></is></c><c r="E1" t="inlineStr"
s="5"><is><t>Date</t></is></c></row>
<row r="2">
<c r="B2"><si><r><rPr><sz val="11"/><color theme="4"/><rFont
val="Calibri"/><family val="2"/><scheme
val="minor"/></rPr><t>Blue</t></r><r><rPr><sz val="11"/><color
theme="1"/><rFont val="Calibri"/><family val="2"/><scheme
val="minor"/></rPr><t xml:space="preserve"> </t></r><r><rPr><u/><sz
val="11"/> <color theme="9"/> <rFont val="Calibri"/> <family val="2"/>
<scheme val="minor"/> </rPr> <t>Widget</t> </r></si> </c><c r="C2"
t="inlineStr"><is><t>V</t></is></c><c r="D2"
t="inlineStr"><is><t>K</t></is></c></row>
</sheetData></worksheet>
My requirement is like in one cell "Blue Widget" with murltiple tesxt
style,remaining three cells with V and K
I formatted XML like this,multiple style is not working.Please give me as
ample XML format for multiple styling in one cell and no styling in another
cell for a sinlge workssheet in SPreadsheet xml format
Please help me as iam strucked.
Thanks in advance
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/BigGridDemo-for-a-RichText-tp3412641p5724962.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: BigGridDemo for a RichText
Posted by Mark Beardsley <ma...@tiscali.co.uk>.
Good news and yes, the shared strings table does sound like the correct
approach. I supect the answer to the encoding question lies within the code
of the XSSFRichTextString class. It will use other classes from the xmlbeans
layer I am sure but if you have a dig through the source for that class, it
may ilustrate how the mark-up is encoded. It may even be worth posting a
second question onto the list to ask specifically about this in the hopes
that Yegor - who authored XSSFRichTextString, will pick it up.
In the short term, is it possible to identify the particular use cases for
your current requirement? If so, you may be able to use Excel to create
string parts that encapsulate those requirements and, for the moment, hard
code the mark-uo into your application. For instance, if you identify how to
set the font face, the size of the font, how to embolden and how to
underline using examples created in Excel, then it may be possible to write
a class that uses these examples to create the mark-up yourself. You could
use the rich text string approach yourself and create a class that both
encapsulates the text and allows you to apply transformations to parts of it
before returning the correct mark-up when asked for it. I can easilly
imagine methods like embolden(int from, int to) where the two indices
identify that portion of the string that must be made bold. Also
underline(int from, int to) is fairly obvious and you could include
setFont(String face, int size, int from, int to), etc, etc. The most
important method would probably be getMarkup() or something similar that
returns the actual mark-up that would be written to the shared strings
table.
Yours
Mark B
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/BigGridDemo-for-a-RichText-tp3412641p3414185.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: BigGridDemo for a RichText
Posted by Ofer Fort <of...@tra.cx>.
just tried adding a formatted string into the sharedString and referenced it
from the sheetn.xml and it worked, while putting the same string in the
sheetn.xml as an inlineStr didn't, i think that's what i'll try to do, build
the sharedString.xml and fill it with data, not i just need to see how to
create the formatted string...
On Tue, Mar 8, 2011 at 3:03 PM, Mark Beardsley <ma...@tiscali.co.uk>wrote:
> Yes, you can do that of course. The impetus behind my idea was to preclude
> the requirement for the sharedStrings.xml file in the first place and to
> 'stuff' everything into the sheetn.xml file. If that were possible, you
> would not need to make any changes to the way BigGridDemo currently works,
> just find way to successfully encode the formatting information.
>
> Yours
>
> Mark B
>
> --
> View this message in context:
> http://apache-poi.1045710.n5.nabble.com/BigGridDemo-for-a-RichText-tp3412641p3413899.html
> Sent from the POI - User mailing list archive at Nabble.com.
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
>
Re: BigGridDemo for a RichText
Posted by Mark Beardsley <ma...@tiscali.co.uk>.
Yes, you can do that of course. The impetus behind my idea was to preclude
the requirement for the sharedStrings.xml file in the first place and to
'stuff' everything into the sheetn.xml file. If that were possible, you
would not need to make any changes to the way BigGridDemo currently works,
just find way to successfully encode the formatting information.
Yours
Mark B
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/BigGridDemo-for-a-RichText-tp3412641p3413899.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: BigGridDemo for a RichText
Posted by Ofer Fort <of...@tra.cx>.
Not sure i understand,
what i thought was needed to be done is change the cell type to s and not
inlineStr, and give it an id, and then build the sharedString.xml to include
this string with the given id?
On Tue, Mar 8, 2011 at 2:38 PM, Mark Beardsley <ma...@tiscali.co.uk>wrote:
> ..and that failed as well. Suffice it to say there is mark-up in the
> sharedStrings.xml file that defines the contents for the cell and the
> format
> of the same. There are tags that define the fonts and other characteristcs
> and this chunk of mark-up is then referenced from the cell record in the
> sheetn.xml file. If it is possible to combine the two - stuffing the marup
> straight into the cell and bypassing the need for the sharedStrings.xml
> file
> in this case - then it would offer you a possible path to proceed along.
> The
> only challenge then is generating the correct mark-up and I am windering if
> the XSSFRichTextString class might offer a solution. I do not know this for
> a fact but am assuming that at some point that class or one it
> encapsulates,
> must contain the necessary code to cretae the xml mark-up. Tapping into
> this, if it is possible, would make creating the mark-up a trivial task.
>
> Yours
>
> Mark B
>
> --
> View this message in context:
> http://apache-poi.1045710.n5.nabble.com/BigGridDemo-for-a-RichText-tp3412641p3413871.html
> Sent from the POI - User mailing list archive at Nabble.com.
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
>
Re: BigGridDemo for a RichText
Posted by Mark Beardsley <ma...@tiscali.co.uk>.
..and that failed as well. Suffice it to say there is mark-up in the
sharedStrings.xml file that defines the contents for the cell and the format
of the same. There are tags that define the fonts and other characteristcs
and this chunk of mark-up is then referenced from the cell record in the
sheetn.xml file. If it is possible to combine the two - stuffing the marup
straight into the cell and bypassing the need for the sharedStrings.xml file
in this case - then it would offer you a possible path to proceed along. The
only challenge then is generating the correct mark-up and I am windering if
the XSSFRichTextString class might offer a solution. I do not know this for
a fact but am assuming that at some point that class or one it encapsulates,
must contain the necessary code to cretae the xml mark-up. Tapping into
this, if it is possible, would make creating the mark-up a trivial task.
Yours
Mark B
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/BigGridDemo-for-a-RichText-tp3412641p3413871.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: BigGridDemo for a RichText
Posted by Mark Beardsley <ma...@tiscali.co.uk>.
No real advice from me I am afraid other than a way to make a start on the
problem. As you possibly know, xlsx files are simply zipped xml and so my
first step would be to create a very simple file using Excel, change the
extension to .zip and then unzip it. After that, you can have a dig around
in the file to see how the mark-up is structured during the creation of the
file. With luck, it may be possible to simply write a string of xml markup
into a cell rather than a string of text. That markup would follow Excel's
format of course and contain the font and formatting information for the
text. Whether this is possib,e I do not know and fear that you may be
heading for a rendezvous with the shared strings file but I cxannot comment
with any authority on this. It would certainly be worth creating a test file
using the BigGridDemo approach and then unzippig this to dig around within
it as well. It may be that Excel and BigGridDemo both save the data away
successfuly but using a sightly different approach - does BigGridDemo make
use of the shared strings file is the question I would like answered.
Have you looked into what the rich text string classes in the api produce as
output? It may be possible to use them to encode you text and then all you
would need to do is to identify a way to write this information into the
file.
All the best
Yours
Mark B
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/BigGridDemo-for-a-RichText-tp3412641p3412728.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: BigGridDemo for a RichText
Posted by Mark Beardsley <ma...@tiscali.co.uk>.
Well that failed. Lets see if I can get the mark-up into the message by
smuggling it in as a String of text
"
Rich
Text
String
"
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/BigGridDemo-for-a-RichText-tp3412641p3413866.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org