Hi,
We have an excel file that is pre-created with fancy charts and formulae
that cross-reference sheets. It also contains macros. Source data is
supposed to be copy-pasted into one sheet and the rest of workbook populates
itself from pre-existing wiring.
Note: I really don't care about reading / modifying other sheets in this
workbook, I just need to save the pains of copy-pasting raw data every time
to this data sheet in this workbook.
I'm using Apache POI and trying to create a XSSFWorkbook instance from this
"template" excel file. However, it takes a long time (almost a minute
consistently across many runs) for this object to be constructed. The excel
file itself is just around 400KB, not a big file. I profiled this using
jconsole, it seems like it is not limited by either CPU or Heap - It uses
just around 90 MB heap memory (I had started it up with 2GB committed heap)
and around 52% CPU.
It takes a very short time to actually populate the data sheet with raw data
and write the final updated file out (roughly 3-4 seconds). Here is my
startup code:
public static void startup() throws FileNotFoundException, IOException {
long start = System.nanoTime();
System.out.println("Started...");
TEMPLATE_WORKBOOK = new XSSFWorkbook(new FileInputStream(new
File(TEMPLATE)));
long end = (System.nanoTime() - start) / NANOS;
System.out.println("It took " + end + " seconds..");
}
I thought about loading up this TEMPLATE_WORKBOOK once and then reuse the
same handle to write new data for every subsequent request - I simulated
this with a sleep and a forever-while in my main class. But I can't do this
apparently, I got an exception "Exception in thread "main"
org.apache.xmlbeans.impl.values.XmlValueDisconnectedException". The
TEMPLATE_WORKBOOK object is not re-usable.
I do see that there is an event-based API, but before I get into it, I
wanted to see if I'm missing something here! Again, memory / CPU is not an
issue here, we have plenty of heap to spare. I'm trying to reduce time.
Any tips / insights would be deeply appreciated!
--
View this message in context:
http://apache-poi.1045710.n5.nabble.com/Construction-of-XSSFWorkbook-very-slow-for-xlsm-workbooks-tp5710769.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
