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 <markbrd...@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-unsubscr...@poi.apache.org
> For additional commands, e-mail: user-h...@poi.apache.org
>
>
