11 Replies Latest reply: Jan 23, 2007 3:25 PM by 807596 RSS

    Java and Excel : POI vs JExcel API?

    807596
      Hello,

      I am hesitating between using the JExcel API and the Jakarta POI API to manipulate (rather simply) excel files with java.
      Has anyone experimented both? What are the drawbacks and the advantages of both?

      Thanks a lot,

      Laetitia
        • 1. Re: Java and Excel : POI vs JExcel API?
          807596

          Laetitia,

          Hello! I've written a significant app (J2EE, EJB using WebLogic) utilizing first the POI api and then the jexcel API to produce MS excel output financial reports (for online content provider) . The application processed 80K plus lines of subscriber information, calculated monthly subscription delivery charges for 100+ publications, each with custom contracts and wrote the calculated data to 'reporting tables'. Basically a specialized datamart of sorts (I know little about 'true' data marting). App would then generate the ms excel spreadsheet reports, one per publisher, utilizing the excel API. The largest publisher report would be over 10Mb in size, containing some 40K rows of data, 30 columns wide.

          I utilized the POI api first because it looked to be a more substantial product with firmer support, more developers, etc. However I soon became confused on how different modules worked, received slow feedback from the development team, and found the documentation to be horribly out of date and misleading. Additionally, the larger the generated excel file became the less stable the server would be until it would crash with memory problems. I'm not saying that the memory errors were within the POI code, but after I switched to jexcel the server crashing went away (perhaps the jexcel code is more forgiving).

          I then switched to use the jexcel API and was immediately pleased. The documentation was accurate and clearer. The functionality I needed was easy to find and worked first time. When I had problems I posted my questions to the yahoo message board and was immediately helped. As an aside on the message board I'm an old internet hack with message boards and am used to stupid posts (and posters) being immediately 'flamed' and derided by those on the list 'in the know'. I was amazed by all the posts from people asking 'I just downloaded jexcel and need help' who actually got thoughtful and helpful posts, not just rude 'RTFM you @#%$$@%' replies.

          Jexcel API was written by one guy but seems to be supported by several. I'd recommend it strongly - new releases come out continually and it's easy to get answers to questions on how to use it. Plus, and this is a biggy with me, the documentation is accurate and useful.

          Email me for more.
          -wcrighton
          wcrighton@wacconsulting.com
          • 2. Re: Java and Excel : POI vs JExcel API?
            807596
            wcrighton,

            my experience:

            I have to choose a API for an application which needs to read an existing excel file (quite complex, with many format and formula).

            I tried POI, more than half of the sheets in the excel file can not be read. (i have to remove each sheet from the excel and tried again and again) but all formats and rules are remain unchanged (only for the successed sheets).

            I use jexcel, only 1 of the sheet cannot read. the formulas and rules are remain unchanged. but some of the format lost. (i think it is because the colour provided by jexcel is not enough).

            but at least it can read most of the sheet.

            wish this can help you

            Regards,

            Kin
            • 3. Re: Java and Excel : POI vs JExcel API?
              807596
              Now you can also try jXLS - working with excel through XLS templates.
              It uses POI to read/modify excel files.
              Its API is very simple.
              • 4. Re: Java and Excel : POI vs JExcel API?
                807596
                While we are in the topic of accessing Excel from Java . . . there's one other product called J-Integra that provides a Java API for Excel that allows manipulation of Excel files. J-Integra exposes Excel objects and functions similar to those accessible via Excel VBA.

                For more information, refer to:
                http://j-integra.intrinsyc.com/support/com/doc/excel_example.html

                Sincerely,
                Frankie Ragasa
                J-Integra Interoperability Solutions
                http://j-integra.intrinsyc.com/
                high performance interop middleware for java, corba, com & .net
                • 5. Re: Java and Excel : POI vs JExcel API?
                  807596
                  Hi,

                  Can anyone let me know if it is possible to populate data into excel sheet taking inputs from web interface with out disturbing the formulae and macros of the excel.

                  The requirement is i wrote an excel tool which has formulae and macros. User has to input the values manually and my excel sheet tool will calculate the values based on formulae and output the values at the bottom of sheet. Now i dont want the user to enter values into excel manually instead i want user to use web inerface or some interface to enter values which should be populated to my excel tool and the formulae should work fine.

                  Please reply as soon as possible.

                  Thanks in advance
                  Raj.
                  • 6. Re: Java and Excel : POI vs JExcel API?
                    807596
                    can u guide me to use POI to access Excel spreadsheet. how to use POI..? i am very new to this..
                    • 7. Re: Java and Excel : POI vs JExcel API?
                      807596
                      I had no problem figuring out how to use POI to read and write Excel sheets.
                      The only issue with POI was the need for much memory when preparing an output sheet. I limited the #of lines for the Excel format and write a CSV-file instead when there are too many.
                      • 8. Re: Java and Excel : POI vs JExcel API?
                        807596
                        Just wanna ask if JExcel or POI will work with SWING? I need to create an application that will get data from excel file and display it on JTable.

                        Do you guys have any idea with this? Since my project needs to display excel data to a table and then manipulate the data as well as changing the arrangements of data in JTable and throw it to an external CSV file.

                        If you have ideas or suggestions, please feel free to share it hehehe...

                        take care and god bless.
                        • 9. Re: Java and Excel : POI vs JExcel API?
                          807596
                          They both work with Swing as neither of them provide graphical displays- they are engines that work behind the interface you design. You construct a table model for JTable and you can populate this from the various APIs with methods to get rows and then get cells and then get cell values.

                          Somewhere I had sample code but couldn't find it...

                          There are some really good introductions on the net that tell you how to populate a JTable. Try this one:
                          http://www.ociweb.com/jnb/jnbMar2004.html

                          It is for POI only, I have not used JExcel one but am considering using it after reading these posts. I have used POI for >70k data files in neuroscience data files and it worked but when I pushed it beyond 80k it got short of memory and crashed...

                          Hope this helps.
                          Jason Barraclough.
                          • 10. Re: Java and Excel : POI vs JExcel API?
                            807596
                            Hi,
                            I'm very desperate... I have to do following things: read Excel file with macros. I can read xls without macros, but i haven't ideas what to do, so correctly read records with macros in xls.
                            I use following method to listen for incoming records and handles them as required.
                            public void processRecord(Record record)
                            {
                            switch (record.getSid())
                            {
                            // the BOFRecord can represent either the beginning of a sheet or the workbook
                            case BOFRecord.sid:
                            BOFRecord bof = (BOFRecord) record;
                            if (bof.getType() == bof.TYPE_WORKBOOK)
                            {
                            System.out.println("Encountered workbook");
                            // assigned to the class level member
                            } else if (bof.getType() == bof.TYPE_WORKSHEET)
                            {
                            System.out.println("Encountered sheet reference");
                            }
                            break;
                            case BoundSheetRecord.sid:
                            BoundSheetRecord bsr = (BoundSheetRecord) record;
                            System.out.println("New sheet named: " + bsr.getSheetname());
                            break;
                            case RowRecord.sid:
                            RowRecord rowrec = (RowRecord) record;
                            System.out.println("Row found, first column at "
                            + rowrec.getFirstCol() + " last column at " + rowrec.getLastCol());
                            break;
                            case NumberRecord.sid:
                            NumberRecord numrec = (NumberRecord) record;
                            System.out.println("Cell found with value " + numrec.getValue()
                            + " at row " + numrec.getRow() + " and column " + numrec.getColumn());
                            break;
                            // SSTRecords store a array of unique strings used in Excel.
                            case SSTRecord.sid:
                            sstrec = (SSTRecord) record;
                            for (int k = 0; k < sstrec.getNumUniqueStrings(); k++)
                            {
                            System.out.println("String table value " + k + " = " + sstrec.getString(k));
                            }
                            break;
                            case LabelSSTRecord.sid:
                            LabelSSTRecord lrec = (LabelSSTRecord) record;
                            System.out.println("String cell found with value "
                            + sstrec.getString(lrec.getSSTIndex()));
                            break;

                            case FormulaRecord.sid:
                            FormulaRecord fre = (FormulaRecord) record;
                            System.out.println("Formula: "+ fre.getValue());
                            break;
                            }
                            }


                            I don't know, how check, that incoming record is joined with macro and I don't know how handle it.

                            Please help me...

                            Margaret
                            • 11. Re: Java and Excel : POI vs JExcel API?
                              807596
                              Hi Everyone,
                              ********************************************************
                              Can anyone let me know if it is possible to populate data into excel sheet taking inputs from web interface?
                              The requirement is i wrote an excel tool which has formulae and macros. User has to input the values manually and my excel sheet will generate an xml. Now i dont want the user to enter values into excel manually instead i want user to use web inerface or some interface to enter values which will be converted to an xml file for further processing
                              ********************************************************
                              Can someone please guide me in this regard? I am working on Medical domain project and the hospital needs this. I explored many softwares like
                              1) EZ JCom
                              2) Google spreadsheet data
                              3) Active xls
                              4) Jakarta POI
                              5) Aspose
                              but none match my requirement
                              -Vijay