You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by David Durham <da...@gmail.com> on 2010/10/19 23:23:47 UTC

large xssf files

Hi all,

I understand that there is a good solution for writing large .xlsx files:

  http://svn.apache.org/repos/asf/poi/trunk/src/examples/src/org/apache/poi/xssf/usermodel/examples/BigGridDemo.java

My issue is that it uses the filesystem.  I don't even have much in
the way of filesystem resources.  Does anyone have ideas about how to
do this with just an OutputStream?

Thanks,
Dave

---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org

Re: large xssf files

Posted by David Durham <da...@gmail.com>.
That's interesting information, but the solution I described works.  I
can hold the initial empty xlsx in memory, then write out the full
document to any output stream.

Here's what it looks like (in groovy).  permOut could be any output
stream, wb is a workbook loaded with the styles needed, sheet is the
sheet that will be replaced with a large xml doc.


        def permOut = new FileOutputStream("target/ooxml.xlsx")
        ZipOutputStream zipOut = new ZipOutputStream(permOut)


        ByteArrayOutputStream tempOut = new ByteArrayOutputStream()
        wb.write(tempOut)

        ZipInputStream zipIn = new ZipInputStream(new
ByteArrayInputStream(tempOut.toByteArray()))
        ZipEntry entry = zipIn.nextEntry

        String sheetName = sheet.packagePart.partName.name.substring(1)
        while (entry != null) {
            if (!entry.name.equals(sheetName)) {
                zipOut.putNextEntry(new ZipEntry(entry.name))
                copyStream(zipIn, zipOut)
                zipOut.closeEntry()
            }
            entry = zipIn.nextEntry
        }

        zipOut.putNextEntry(new ZipEntry(sheetName))

        // write large sheet xml

        zipOut.closeEntry()
        zipOut.close()



On Wed, Oct 20, 2010 at 10:04 AM, Mark Beardsley
<ma...@tiscali.co.uk> wrote:
>
> How complex is the formatting? If you open a blank excel document, set the
> widths of columns and apply formatting to them then import a CSV file into
> this document, the formatting rules will be applied to the data as the sheet
> is populated. This will only work for simple formatting of course, if you
> need to have, for example, a totals row at the end of the document and the
> values for this row must be calculatyed from the data you are reading in
> then this presents more of a problem.
>
> You might want to look at using an Excel macro to open the CSV file and
> populate the workbook. A macro would be able to ocate a file on your
> machine, to open it, read the data and populate the workbook; indeed, macros
> can do anything you - a user - could. Further, this macro can be set to run
> when the user opens the workbook and it could be 'signed' so that they were
> unawar of it. The macro recorder could help you get started with this and
> there are plenty of web sites offering VBA code and advice on the web.
> --
> View this message in context: http://apache-poi.1045710.n5.nabble.com/large-xssf-files-tp3227625p3228800.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
>
>

---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org

Re: large xssf files

Posted by Mark Beardsley <ma...@tiscali.co.uk>.
How complex is the formatting? If you open a blank excel document, set the
widths of columns and apply formatting to them then import a CSV file into
this document, the formatting rules will be applied to the data as the sheet
is populated. This will only work for simple formatting of course, if you
need to have, for example, a totals row at the end of the document and the
values for this row must be calculatyed from the data you are reading in
then this presents more of a problem.

You might want to look at using an Excel macro to open the CSV file and
populate the workbook. A macro would be able to ocate a file on your
machine, to open it, read the data and populate the workbook; indeed, macros
can do anything you - a user - could. Further, this macro can be set to run
when the user opens the workbook and it could be 'signed' so that they were
unawar of it. The macro recorder could help you get started with this and
there are plenty of web sites offering VBA code and advice on the web.
-- 
View this message in context: http://apache-poi.1045710.n5.nabble.com/large-xssf-files-tp3227625p3228800.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: large xssf files

Posted by David Durham <da...@gmail.com>.
I have to format it nicely, so no way around that.

I am working through something that I think will work, which is
basically to write the initial, mostly empty, workbook to an in memory
byte array, then create a zipinput stream from that, iterate through
each entry writing to a zipoutputstream which is connected to any kind
of output stream, then when I get to the zip entry for my sheet, I'll
write something besides what's in the in memory representation.

On Tue, Oct 19, 2010 at 4:27 PM, Nick Burch <ni...@alfresco.com> wrote:
> On Tue, 19 Oct 2010, David Durham wrote:
>>
>> My issue is that it uses the filesystem.  I don't even have much in the
>> way of filesystem resources.  Does anyone have ideas about how to do this
>> with just an OutputStream?
>
> CSV. Seriously. If you have loads of data, and not much in the way of
> resources, just use a CSV. You can't format it nicely, but if you're low on
> resources then you probably don't want to anyway...
>
> Nick
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
>

---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org

Re: large xssf files

Posted by Nick Burch <ni...@alfresco.com>.
On Tue, 19 Oct 2010, David Durham wrote:
> My issue is that it uses the filesystem.  I don't even have much in the 
> way of filesystem resources.  Does anyone have ideas about how to do 
> this with just an OutputStream?

CSV. Seriously. If you have loads of data, and not much in the way of 
resources, just use a CSV. You can't format it nicely, but if you're low 
on resources then you probably don't want to anyway...

Nick

---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org