poi-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From MSB <markbrd...@tiscali.co.uk>
Subject Re: Question on XSSF memory workarounds (i.e. BigGridDemo)
Date Fri, 16 Oct 2009 06:08:49 GMT

Pleased to hear that you managed to find a workaround to your problem and
even happier that you chose to post the solution here. Thanks for that,
pieces of code such as this often prove very valuable to other list members
and I am very grateful for your contribution.

Yours

Mark B


Alex Ciarlillo-2 wrote:
> 
> Thanks for the heads up about Yegor. I actually found a way to accomplish
> what I wanted after a fresh look and a cup of coffee this morning. I am
> still wondering if this is the preferred way of doing this, but it works
> great for me and is damn fast for the amount of data. If anyone is
> interested in the changes I made the full modified BigGridDemo can be seen
> here: http://www.pastie.org/656050
> 
> For archive purposes (pastie only lasts a month or so), here are the
> methods
> that were modified or added:
> 
> // modified for multiple sheets using a Map and addSheet()
> public static void main(String[] args) throws Exception {
> 
>         // Step 1. Create a template file. Setup sheets and workbook-level
> objects such as
>         // cell styles, number formats, etc.
> 
>         XSSFWorkbook wb = new XSSFWorkbook();
>         Map<String, File> sheets = new HashMap<String, File>();
> 
>         addSheet("test1", wb, sheets);
>         addSheet("test2", wb, sheets);
> 
>         Map<String, XSSFCellStyle> styles = createStyles(wb);
> 
>         //save the template
>         FileOutputStream os = new FileOutputStream("template.xlsx");
>         wb.write(os);
>         os.close();
> 
>         // generate data for each sheet
>         for (Map.Entry<String, File> entry : sheets.entrySet()) {
>             Writer fw = new FileWriter(entry.getValue());
>             generate(fw, styles);
>             fw.close();
>         }
> 
>         //Step 3. Substitute the template entry with the generated data
>         FileOutputStream out = new FileOutputStream("big-grid.xlsx");
>         substitute(new File("template.xlsx"), sheets, out);
>         out.close();
>     }
> 
> 
>    // modified for multiple sheets
>    private static void substitute(File zipfile, Map<String, File> sheets,
> OutputStream out) throws IOException {
>         ZipFile zip = new ZipFile(zipfile);
> 
>         ZipOutputStream zos = new ZipOutputStream(out);
> 
>         @SuppressWarnings("unchecked")
>         Enumeration<ZipEntry> en = (Enumeration<ZipEntry>) zip.entries();
>         while (en.hasMoreElements()) {
>             ZipEntry ze = en.nextElement();
>             System.out.println(ze.getName());
>             if(!sheets.containsKey(ze.getName())){
>                 zos.putNextEntry(new ZipEntry(ze.getName()));
>                 InputStream is = zip.getInputStream(ze);
>                 copyStream(is, zos);
>                 is.close();
>             }
>         }
> 
> 
>      // added this method to make creating all these
> sheets/refnames/files/etc easier
>      private static void addSheet(String name, XSSFWorkbook book,
> Map<String, File> sheets) throws IOException {
>         // create the sheet
>         XSSFSheet sheet = book.createSheet(name);
>         // get the refname. do the substring() here since we done need the
> full name anywhere else
>         String ref =
> sheet.getPackagePart().getPartName().getName().substring(1);
>         // create the temp file as sheet#.xml using the map size to get
> the
> right sheet index
>         File tmp = File.createTempFile("sheet" + (sheets.size() + 1),
> ".xml");
>         sheets.put(ref, tmp);
>     }
> 
> Java is definitely not my strong suit so any suggestions are welcome. I
> just
> put this together this morning to see what I could get working.
> 
> Thanks
> Alex C
> 
> On Thu, Oct 15, 2009 at 3:55 AM, MSB <markbrdsly@tiscali.co.uk> wrote:
> 
>>
>> That is (was?) very much Yegor's baby. He write it as a proof to
>> demonstrate
>> a way around memory limitations when bulding large sheets and he is by
>> far
>> the best person to answer this question. Unfortunately he is, as I
>> understand from David's post to another question, on holiday at the
>> moment
>> and so cannot reply to your question.
>>
>> What I am trying to say in a roudnabout way is do not be surprised of you
>> do
>> not recieve a positive response to your post for a few days; I am
>> confident
>> Yegor will see this meassage and reply when he returns to work. Of
>> course,
>> that ssumes no one else has done what youa er seeking to accomplish
>> already.
>>
>> Yours
>>
>> Mark B
>>
>>
>> Alex Ciarlillo-2 wrote:
>> >
>> > I have been messing around with POI again a bit since with XSSF it can
>> now
>> > support more than 65536 rows. Previously I was using ruby and the
>> win32ole
>> > to solve my problems but it's slow so I'm back and looking for a better
>> > solution. Of course, when hitting such high numbers of rows I instantly
>> > ran
>> > into JVM heap issues. Then I found the BigGridDemo and it seemed to
>> work
>> > nicely. I started trying to adapt it into a simple class I can use for
>> > outputting plain text data. No cell formats or fonts or anything fancy,
>> > just
>> > about ~100k rows of data. The problem is I have 3-4 sheets I need to
>> > create
>> > per workbook. I'm trying to figure out the inner workings of the
>> > BigGridDemo
>> > and what it's doing with the zip files and streams to get all this
>> magic
>> > happening but I'm not having much luck. So my question is firstly - is
>> the
>> > BigGridDemo method still the way to go for this? The last time I see
>> > mention
>> > of it is back in January and I'm not sure if there is a more recent
>> 'best
>> > practice' for large data sets. If it is still relevant, can anyone give
>> me
>> > an idea of how I can adapt this to multiple sheets? I've stepped
>> through
>> > it
>> > quite a few times but I still cannot get a handle on what is happening
>> in
>> > the substitute() function.
>> >
>> > thanks
>> > Alex
>> >
>> >
>>
>> --
>> View this message in context:
>> http://www.nabble.com/Question-on-XSSF-memory-workarounds-%28i.e.-BigGridDemo%29-tp25898474p25904351.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
>>
>>
> 
> 

-- 
View this message in context: http://www.nabble.com/Question-on-XSSF-memory-workarounds-%28i.e.-BigGridDemo%29-tp25898474p25920268.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


Mime
View raw message