1 2 Previous Next 25 Replies Latest reply on Jan 28, 2016 7:52 PM by Manish.Sharma_82 Go to original post
      • 16. Re: Java Exception: java.lang.reflect.InvocationTargetException: during call of java.lang.reflect.Method .invoke.
        Sasank Vemana

        Manish and Jim - Thanks for a great discussion. Learnt a lot of things about server side javascript in action! I am excited to try this out!

         

        @Manish - Not sure if your requirement is to create an excel file on the app/batch server for some other interaction outside of sending the excel to the client (end user). By looking at your code, I see that you are writing the file to the app/batch server? Is that the end result or do you need to provide that file to the client?

         

        Anyway, if the requirement is to create an excel file and then send to the client (user's browser) for download then I have another option for you. Just wanted to throw it out there because this option uses just peoplecode.

         

        I worked on a requirement where users could decide to select specific rows in a grid (data from the component buffer) and download the data to excel (with some formatting). Since the user's needed a choice to select some or all rows in the grid, I could not use the delivered download to excel function and had to write my own IScript powered push button code to prep up the data (add styling and also transform to multiple worksheets) and then send as an excel to the user.

         

        I used an open source project created by David Vandiver (can be downloaded from SourceForge). David has built a great library of PeopleCode functions which I used as a base (we can tweak it for additional custom styling, calculations, etc.). I built the Excel XML file using David's libraries (all PeopleCode functions) and then sent the file to the client using the %Response class.

         

        Additionally, we can set the content disposition as a header so that the file gets downloaded in the right format:

        %Response.SetHeader("Content-Disposition", "inline; filename=""test.xls""");

         

        I can vouch for this method since I have used this in a production environment and it has been running without any issues for over a year (users downloading thousands of rows of data). I don't know if this approach would also run into issues if we are dealing with 100,000+ rows but I am hoping not since it is all in peoplecode!

         

        As I said, I just wanted to throw it out there in case you are continuing to have issues with memory and if this solution is applicable/relevant to your requirement. Thanks!

        1 person found this helpful
        • 17. Re: Java Exception: java.lang.reflect.InvocationTargetException: during call of java.lang.reflect.Method .invoke.
          Manish.Sharma_82

          Hi Sasank

           

          I appreciate your suggestions, but our scenario is that we have a report for Month End processing for users which is critical for them as it consists of account analysis and prepay vouchers information and then we have our own business logic to process the analysis.

           

          Technically this is a MS Excel report which has 3 worksheets, which we have created through AE by using a predefined Excel template via CreateObject("COM", "Excel.Application").

           

          in Code we were Opening the template and save it as different name at different location and generate the data in that file. and the users can download it through report manager from front end.

           

          But since we have upgraded to Ptools8.54, this report is not generating  with MSExcel 2013, it get stuck and never finish. and as its a Third party issue(MS Excel). We have raised issue with Microsoft about it but got the response that they cant help much at this moment of time because the product is getting stable with PT 8.54.  also they have advised to disable other feature of Excel and try it but still we are facing issue with that.

           

          So i have decided to do this with Apache POI, as i have gone through Jim's Blog and thought of going with this approach and generate the report. as through this we have several advantages of writing the code easily which we might have to code heavily through David's Excel XML library, like traverse through different sheets of a file and print data based on calculation and using delivered function of Apache POI to massage the report before displaying as we were  doing earlier with OLE Automation of MS Excel(by putting the formatted excel template and printing the data in that and save it as to diff location).

           

          So that's the whole reason of doing with this approach instead of David's Excel XML. but yea if its an online reporting thing, low volume of data and less formatting required then definitely that's better Option, i have downloaded that project and gone through it, its really a great task done by him, to help us.

           

          @Jim I have used explicit GC by using java.lang.System.gc() in code, but still getting below errors

           

          Java Exception: java.lang.OutOfMemoryError: Java heap space: during call of javax.script.ScriptEngine.eval.

          or some time the same error as

          Java Exception: java.lang.OutOfMemoryError: GC overhead limit exceeded: during call of javax.script.ScriptEngine.eval.

           

          I am in process with sysadmin to increase the JAVA Heap size from 512mb, and see whether that helps to process 100K rows. as in PROD we have 4 gigs but in dev and test we have around 512mb of java Heap size. did you know that what's the Ideal Heap size for an env.

           

          Thanks

          Manish

          • 18. Re: Java Exception: java.lang.reflect.InvocationTargetException: during call of java.lang.reflect.Method .invoke.
            Sasank Vemana

            Gotcha! Thanks for clarifying.

             

            Learnt a lot from this discussion!

             

            Would be interested to see how you tackle the memory issue! Keep us posted if you figure out a way!

            • 19. Re: Java Exception: java.lang.reflect.InvocationTargetException: during call of java.lang.reflect.Method .invoke.
              Jim.Marion-Oracle

              What Sasank mentioned was my first thought as well. If I were doing this, I would first start with an Excel XML template and then convert that to XSL. I would then transform a result set into Excel using the XSL template. Excel XML SHOULD handle all formatting, etc, but I can understand that there might be cases where POI would be better.

              • 20. Re: Re: Java Exception: java.lang.reflect.InvocationTargetException: during call of java.lang.reflect.Method .invoke.
                Manish.Sharma_82

                Hi Jim and Sasank,

                 

                At last i am able to complete this requirement!! Thanks a Lot for your guidance and support!

                 

                i have used another class of Apache POI to create the large file as Packages.org.apache.poi.xssf.streaming.SXSSFWorkbook; and it works great and we can create really a large file with no Memory issues or anything like that. even i have created a generic class now in Peoplecode to create Excel reports through Application Engine, to avoid Microsoft OLE Automation issues, and to reduce the memory processing issues.

                 

                var result = (function() { 

                    // import statements 

                    var SXSSFWorkbook = Packages.org.apache.poi.xssf.streaming.SXSSFWorkbook; 

                    var FileOutputStream = Packages.java.io.FileOutputStream;

                    // variable declarations 

                    var workbook = new SXSSFWorkbook(); 

                    var sheet = workbook.createSheet("Countries"); 

                    var fileName = "/psoft/fs9devt/UserUpload/ap/real123.xlsx";

                 

                   for (i = 0; i < 130000; i++) {

                var row = sheet.createRow(i);

                   for(t=0; t < 10; t++){

                var cell = row.createCell(t);

                cell.setCellValue(t+" in the "+i);

                cell=null;

                }

                row=null;

                }

                    var fos = new FileOutputStream(fileName); 

                    workbook.write(fos); 

                    fos.close(); 

                   

                    return "Created workbook " + fileName;

                 

                }());

                 

                 

                Also i am attaching the Generic Excel Application Class which i have created to support the Excel File creation and a sample code. which might help Others if they are looking for large excel file creation through Apache POI in PT 8.54.

                 

                Thanks

                Manish

                • 22. Re: Java Exception: java.lang.reflect.InvocationTargetException: during call of java.lang.reflect.Method .invoke.
                  Manish.Sharma_82

                  Nope i have not increased the heap size, and this streaming class of Apache POI SXSSFWorkbook instead of XSSFWorkbook handles that memory issue, as its for creating the large excel file. so i have changed to this, and it works fine for huge no of rows.

                  • 24. Re: Java Exception: java.lang.reflect.InvocationTargetException: during call of java.lang.reflect.Method .invoke.
                    2988129

                    Hi Manish,

                     

                    We have done similar development by using query definition using  API, sending through mail using excel work book basing on query out put.

                    we have case where we are not able to process Nvision report drill down more than 65K data.

                     

                    regards

                     

                    Parthasarathy

                    • 25. Re: Java Exception: java.lang.reflect.InvocationTargetException: during call of java.lang.reflect.Method .invoke.
                      Manish.Sharma_82

                      Hey Parthasarathy,

                       

                      we too have other report where we are using the same query API approach to generate the report in excel, but this was the different requirement where we have to populate the data in different way, well about your issue for more than 65k rows, this approach works for any number of rows.

                       

                      Thanks

                      Manish

                      1 2 Previous Next