writing large excel spreadsheets

Question

has anybody found a library that works well with large spreadsheets?

I've tried apache's POI but it fails miserably working with large files - both reading and writing. It uses massive amounts of memory leaving you needing a supercomputer to parse or create a 20+mb spreadsheet.

Surely there is a more memory efficient way and someone has written it?!

Are you sure you need a spreadsheet, not a (embedded) database? Or maybe even CSV? — BalusC, Dec 03 '09 at 23:13
Well, then you're practically lost. Or, wait .. Is `xlsx` allowed? Or must it really be `xls`? — BalusC, Dec 04 '09 at 11:51
xlsx is fine .. anything that can have multiple worksheets, formulas, formatting etc and open in excel. — pstanton, Dec 05 '09 at 05:38
I have updated BigGridDemo to support multiple sheets. Answer at [http://stackoverflow.com/questions/7274076/writing-a-large-resultset-to-an-excel-file-using-poi/7417316#7417316][1] [1]: http://stackoverflow.com/questions/7274076/writing-a-large-resultset-to-an-excel-file-using-poi/7417316#7417316 — John B, Sep 14 '11 at 13:44
I could recomend use SXSSF poi 3.8 [http://stackoverflow.com/a/11919450/403999][1] [1]: http://stackoverflow.com/a/11919450/403999 — Juan Rojas, Aug 12 '12 at 03:18

score 2 · Answer 1 · edited Aug 10 '17 at 07:29

2

@pstanton.. I was working on a similar solution and was able to write large excel 2007 files with hundreds of rows exported from database. Here is the link to it: http://vikramvkamath.blogspot.com/2010/07/writing-large-excel-files-excel-2007.html

My solution is an extension on Yegor Koslov's SheetWriter class follow this http link and it works very well for me. Let me know in case you face any issues.

~Vikram

edited Aug 10 '17 at 07:29

Guruprasad J Rao

29,410
14
101
200

answered Jul 28 '10 at 22:47

Vikram

4,162
8
43
65

thanks vikram ... your blog post is interesting and i will have a detailed read of it when i find time. – pstanton Jul 29 '10 at 03:42

score 1 · Answer 2 · answered Dec 03 '09 at 22:32

1

I cannot really recommend a library to you. But when you need the best performance, it might be worth a try to go to the people who came up with Excel in the first place. I guess the APIs that are available from .NET are much more efficient in handling Excel files. So the idea would be to implement a web service or similiar component in .NET that does most of the Excel-related grunt work for you and just invoke that from Java.

answered Dec 03 '09 at 22:32

Jannik Jochem

1,516
1
14
28

This is the best solution, since earlier she said that it cannot be any kind of database or CSV, but must be excel. So use Microsoft technologies if you're in love with Excel and cant use anything else. The .NET stuff really works pretty great together. – Karl Jan 11 '10 at 08:49

score 1 · Answer 3 · edited May 23 '17 at 10:32

1

This is basically the same idea as Jannik's, but you use the Java COM Bridge to access the Excel APIs directly from Java. We have had good success doing this with Word. Obvious downside is that it only works on Windows.

edited May 23 '17 at 10:32

Community

1
1

answered Jun 08 '10 at 06:02

David Sykes

7,131
4
36
39

score 0 · Answer 4 · answered Dec 03 '09 at 20:04

0

Have you tried JExcelAPI as an alternative to POI ? I confess I can't comment on it's memory efficiency.

answered Dec 03 '09 at 20:04

Brian Agnew

268,207
37
334
440

pretty sure it reads a whole worksheet at a time, which would run into memory issues. – pstanton Dec 03 '09 at 21:03
Well, I think the issue is *how efficient* JExcelAPI is at reading that spreadsheet, compared to POI. – Brian Agnew Dec 03 '09 at 21:09

score 0 · Accepted Answer · answered Jun 08 '10 at 05:25

0

at time of posting, there is no pure java scalable solution for reading and writing large excel files.

answered Jun 08 '10 at 05:25

pstanton

35,033
24
126
168

Use the new SXSSF poi 3.8 for xlsx files http://stackoverflow.com/a/11919450/403999 – Juan Rojas Aug 13 '12 at 21:35

score -1 · Answer 6 · answered Jun 08 '10 at 06:07

-1

May be CSV file format can help you. You just need to seperate each value by comma and save file with .csv extension.

answered Jun 08 '10 at 06:07

Shekhar

11,438
36
130
186

writing large excel spreadsheets

6 Answers6

Linked