Velocity Template merge method takes in a StringWriter but buff size not enough

Question

I am trying to read from an excel file that contains more than 5000 rows and more than 10 worksheets using Apache POI , I get all the columns and row values and store it in a ArrayListMultiMap>, this logic works perfectly fine , then I store this object in the VelocityContext like this :

       VelocityContext context = new VelocityContext();
        context.put("excelMap", excelMap);

I need to use this collection in a velocity template, to populate a XML file with the values by iterating through excelMap, for that :

     VelocityEngine vEngine = new VelocityEngine();
    vEngine.setProperty("resource.loader", "class");
    vEngine.setProperty("class.resource.loader.class",  "org.apache.velocity.runtime.resource.loader.ClasspathResourceLoader");
    Template t = vEngine.getTemplate(vmTemplate);
    **StringWriter sw = new StringWriter(excelMap.size());**
    **t.merge(context,sw)**

However , the StringWriter capacity is always 1024 , and have already tried PrintWriter in combination with BufferedWriter, StringBuilderWriter, when the application reaches the merge line, it hangs and finally shows OutOfMemory exception.

Can anyone please help me , I have already ready all posts related to StringWriter and PrintWriter , but no luck.

Thank you

score 0 · Accepted Answer · answered Apr 15 '15 at 11:19

0

The 1024 limitation is a red herring. You're not getting out of memory error because of that. If the Writer exceeds the initial capacity more memory will be allocated. Your real problem is the spreadsheet and data, not Velocity.

From the StringWriter javadocs:

initialSize - The number of char values that will fit into this buffer before it is automatically expanded (emphasis mine)

Instead of reading the entire spreadsheet into memory, you should try converting one worksheet or one entry at a time into XML. It might be that Velocity isn't your best choice.

answered Apr 15 '15 at 11:19

duffymo

305,152
44
369
561

I am also thinking of similar lines , but I can't read only selected sheets, it has to be complete to populate all the nodes in the XML, I also tried to split the ArrayListMap into smaller Maps but it gets complicated , it took me great effort to link the sheet to the column - values in the multi map. Is there an alternative I can use to StringWriter? Thank you – Sindhu Kodoor Apr 15 '15 at 12:26
"Has to" - only because of the way you've chosen to generate the XML. You have no choice - out of memory can't be negotiated with. I'd recommend not using POI. Andy Khan's JExcel is a better choice. Don't parse the data into Maps; they belong in a better abstraction like JExcel. Once you have that, deal with the data in a workbook. – duffymo Apr 15 '15 at 12:54
Thanks, I agree POI is not good, but with JExcel as well I will run into the same issue because everything is stored in memory, I have decided to split the job and run in a single thread asynchronously, that should solve the problem. – Sindhu Kodoor Apr 16 '15 at 04:16
JExcel will be better - here's why: you can interleave reading worksheets and generating XML in such a way that you never have the whole spreadsheet in memory at once. Instead of read-write, you'll have multiple read-write steps that loop over all available worksheets. Only one in memory at a time. – duffymo Apr 16 '15 at 12:48
Hi @duffymo thanks, I have resolved by converting 1 testcase to 1 XML at a time. Sorry for the late comment :) – Sindhu Kodoor May 06 '15 at 06:47

Velocity Template merge method takes in a StringWriter but buff size not enough

1 Answers1