Topic
33 replies Latest Post - ‏2009-12-16T06:10:16Z by wudongf
SystemAdmin
SystemAdmin
801 Posts
ACCEPTED ANSWER

Pinned topic Excel files And Dashboards

‏2006-10-04T14:47:05Z |
Hi all,

i want to load data from an excel file but i find that the excel file must be in the dashboard.

Is it possible to have that file in the file system or in document manager of the WSE and invoke it dinamically?

Thanks

Luis Inacio
Updated on 2009-12-16T06:10:16Z at 2009-12-16T06:10:16Z by wudongf
  • silval
    silval
    7 Posts
    ACCEPTED ANSWER

    Re: Excel files And Dashboards

    ‏2006-10-04T19:40:52Z  in response to SystemAdmin
    Hi Luis,

    The Excel Import builder in WDF v2.6.5 (and older) requires the Excel file to be part of the deployed app directory structure. In the next WDF release (1Q07), the builder will allow developers to reference an excel file from a directory outside of the deployed application.

    In the meantime, a solution for you to upload an updated version of the excel files into your app is to use the File Uploader builder. I'm attaching a sample model for reference purposes.

    Note: for the file upload to work, you have to enable it in config/cluster.properties. Please take a look at the File Upload builder's help file for more information on that.

    Regards,
    Luciano Silva
    Updated on 2006-10-04T19:40:52Z at 2006-10-04T19:40:52Z by silval
    • SystemAdmin
      SystemAdmin
      801 Posts
      ACCEPTED ANSWER

      Re: Excel files And Dashboards

      ‏2006-10-04T21:09:25Z  in response to silval
      Thanks Luciano

      I'll see the file and as far as i can i'll tell you how it works.

      Regards

      Luis Inacio
      • SystemAdmin
        SystemAdmin
        801 Posts
        ACCEPTED ANSWER

        Re: Excel files And Dashboards

        ‏2006-10-18T15:41:30Z  in response to SystemAdmin
        Hi Luciano,

        i've tried the model that you send to me. I had to do some configurations about file upload.

        Now, it uploads the file but i don't know to where...

        Can you give me some help?

        Thanks

        Luis
        • silval
          silval
          7 Posts
          ACCEPTED ANSWER

          Re: Excel files And Dashboards

          ‏2006-10-18T15:55:30Z  in response to SystemAdmin
          The configuration properties for the file upload feature are in file WEB-INF/config/cluster.propertis.

          Search for "File Upload" in there and you will find them.

          The specific property for the file upload location is bowstreet.upload.destinationPath.

          Hope it helps.

          Regards.
    • SystemAdmin
      SystemAdmin
      801 Posts
      ACCEPTED ANSWER

      Re: Excel files And Dashboards

      ‏2007-07-16T20:28:37Z  in response to silval
      Hi.

      I'm not expecting Luciano still hanging around but maybe someone else can point me to the right direction... Being quite unexperienced with dashboard framework, I was wondering how to fetch the content of the excel file and create a drilldown table with chart.
      sort of like the sample available in redp4313 but instead of a hardcoded excel table, I'd like to upload the file dinamically.

      I'd appreciate any help on this - TIA
      • wudongf
        wudongf
        289 Posts
        ACCEPTED ANSWER

        Re: Excel files And Dashboards

        ‏2007-07-17T08:21:17Z  in response to SystemAdmin
        Hi,

        You can use Excel Import(Enhanced) builder(In the builder inputs, profile File To Import input ) to import the data, and use Summary Drilldown to generate table and chart.

        And use Portlet Customizer to edit File To Import input in the portlet config/edit mode.

        You also need File Upload to upload your excel file.

        Any question, feel free to let me know.
        • SystemAdmin
          SystemAdmin
          801 Posts
          ACCEPTED ANSWER

          Re: Excel files And Dashboards

          ‏2007-07-17T10:27:48Z  in response to wudongf
          Thanks for the quick reply.

          despite having the following settings in cluster.properties:

          bowstreet.upload.enabled=true
          bowstreet.upload.destinationPath=${bowstreet.rootDirectory}/upload

          I have no excel file in this folder (the folder exists and there's a temp directory in it but no files)
          • wudongf
            wudongf
            289 Posts
            ACCEPTED ANSWER

            Re: Excel files And Dashboards

            ‏2007-07-17T10:30:52Z  in response to SystemAdmin
            Do you mean that you've add File Upload builder in your model,and after you upload the file in the page, you could not find the uploaded file?

            Can you share me your model?
            • SystemAdmin
              SystemAdmin
              801 Posts
              ACCEPTED ANSWER

              Re: Excel files And Dashboards

              ‏2007-07-17T11:03:56Z  in response to wudongf
              yes that's it - the folder is there, but no uploaded excelsheet available. I'll attach my properties file as well, just in case...

              (additionally, I've left the Excel Import Enhanced - File to Import in the builder empty)

              thank you!
              Updated on 2007-07-17T11:03:56Z at 2007-07-17T11:03:56Z by SystemAdmin
            • SystemAdmin
              SystemAdmin
              801 Posts
              ACCEPTED ANSWER

              Re: Excel files And Dashboards

              ‏2007-07-17T11:41:06Z  in response to wudongf
              In the meantime I've made some additional tests, and the excel file does show up in the upload directory (no idea, why it didn't before). in fact, if I upload the file several times, it won't get overwritten but instead several copies exist next to each other.

              Could you give me a hint, how can I reference the file in the "File to Import" if it doesn't exist at compile time?
              • wudongf
                wudongf
                289 Posts
                ACCEPTED ANSWER

                Re: Excel files And Dashboards

                ‏2007-07-18T06:11:29Z  in response to SystemAdmin
                Hi,

                You can input one indirect reference(E.g. Variable filePath) for "File to Import".
                After file upload, your program should be able to automatically change filePath with the path of new uploaded file.

                You also need to prepare a sample file to generate schema at compile time.(When you select a indirect reference in "File to Import", input Sample File will be visible)
                • SystemAdmin
                  SystemAdmin
                  801 Posts
                  ACCEPTED ANSWER

                  Re: Excel files And Dashboards

                  ‏2007-07-18T08:20:43Z  in response to wudongf
                  Hi Wudongf,
                  thanks very much for all the tips. I have created the whole nine yard in the meantime but I've bumped into the same problem like before: the uploaded file won't show up inside upload directory. there's a "temp" directory created inside upload but that's it.

                  I was wondering if it has something to do with the sample excel file being in a different subdirectory outside WEB-INF (prj root/fileDepot) instead of the upload directory.
                  however, I've tried that option too unfortunatelly it didn't help...

                  you'd find attached the models, profile set and sample excel file maybe you'd find something that I couldn't.

                  again, thanks a lot for babysitting me
                  Updated on 2007-07-18T08:20:43Z at 2007-07-18T08:20:43Z by SystemAdmin
                  • wudongf
                    wudongf
                    289 Posts
                    ACCEPTED ANSWER

                    Re: Excel files And Dashboards

                    ‏2007-07-19T05:35:44Z  in response to SystemAdmin
                    Hi,
                    File Upload must be placed inside a HTML form. But in table page, there's no HTML form.
                    Place it in summary page will fix the problem.

                    Please check my modified model.
                    Updated on 2007-07-19T05:35:44Z at 2007-07-19T05:35:44Z by wudongf
                    • SystemAdmin
                      SystemAdmin
                      801 Posts
                      ACCEPTED ANSWER

                      Re: Excel files And Dashboards

                      ‏2007-07-19T07:43:55Z  in response to wudongf
                      yes, that was the trick.

                      btw. do you know an easy way to get rid of the surplus in the upload directory?

                      I've realized, when I upload files with the same name, they wouldn't overwrite the last one but they'd get numbered and for instance after 5 uploads there is a filename.xls, filename1.xls... filename4.xls existing next to each other in the subdirectory - very confusing and the user normally has no direct access to the files so they cannot be deleted.

                      (if it's too much effort, I think I'd be perfectly satisfied when the new file about to be uploaded would just simply overwrite the one sitting in the upload directory)

                      thank you again, you're helping me a great deal here!
                      • wudongf
                        wudongf
                        289 Posts
                        ACCEPTED ANSWER

                        Re: Excel files And Dashboards

                        ‏2007-07-19T08:28:38Z  in response to SystemAdmin
                        Hi,

                        You probably need to write some java code to remove old xls files.
                        E.g. Each time, after upload your file, you can clear old xls files in submit action(method doNothing in your model)
                      • DGawron
                        DGawron
                        34 Posts
                        ACCEPTED ANSWER

                        Re: Excel files And Dashboards

                        ‏2007-07-19T17:23:57Z  in response to SystemAdmin
                        Currently, the FileUpload builder does not overwrite existing files; it cannot assume that an existing file is unused therefore it creates a uniquely named file. A good enhancement to the builder may be to add an input that instructs the builder to overwrite an existing file of the same name. That would address part of your requirement.

                        As for clean up, that is the responsibility of the model that included the FileUpload builder. Think of it this way, how could the builder know when the model is done with the file and it can be deleted? At the end of the request? Maybe the file needs to be held longer.

                        I think there is a possible best practice here and I'll throw it out for discussion: "The model / action that enables a file to be uploaded is the owner of the file and therefore is responsible for removing it when it's no longer needed (or delegating the removal to another entity that assumes ownership of the file)."
                        • SystemAdmin
                          SystemAdmin
                          801 Posts
                          ACCEPTED ANSWER

                          Re: Excel files And Dashboards

                          ‏2007-07-22T07:22:05Z  in response to DGawron
                          totally agree

                          additionally, there's another issue when the upload can overwrite files: what if two user uploads a particular file at the same time?

                          I think I need to give it a thought once more, what would be more beneficial in my case...

                          thanks a lot!
                          • SystemAdmin
                            SystemAdmin
                            801 Posts
                            ACCEPTED ANSWER

                            Re: Excel files And Dashboards

                            ‏2007-07-24T08:23:07Z  in response to SystemAdmin
                            I think a possible workaround scenario would be if I could write out the unique file name on the screen for the user. trouble is, the

                            String filename = webAppAccess.getRequestInputs().getInputValue("upload");

                            line just doesn't seem to work.
                            any inputs would be much appreciated
                            • wudongf
                              wudongf
                              289 Posts
                              ACCEPTED ANSWER

                              Re: Excel files And Dashboards

                              ‏2007-07-25T09:02:56Z  in response to SystemAdmin
                              Hi,

                              Can you tell more detail what do you want to do by display unique file name on the screen for user.
                              And what exactly does not work?
                              • SystemAdmin
                                SystemAdmin
                                801 Posts
                                ACCEPTED ANSWER

                                Re: Excel files And Dashboards

                                ‏2007-07-25T20:36:59Z  in response to wudongf
                                to be a little more specific, here's an example. let's say, we have user A and user B both trying to upload the same samplesheet.xls file

                                since by default the system doesn't overwrite the old file while uploading the new file, it will create the following files after both users have uploaded the same file after each other:

                                samplesheet.xls (the old/original file)
                                samplesheet1.xls (new file uploaded by user A)
                                samplesheet2.xls (new file uploaded by user B)

                                the trouble is, how would user A know, what file he/she has just uploaded hence no access to the "installedApps" on the portal server? therefore, it would be a nice feature to provide the user with the name of the uploaded file.
                                I've made a sort-of-workaround to this with a java routine requesting the name of the youngest file in the subdirectory - but it's obviously not a real solution.

                                (sorry about the long post)
                                • wudongf
                                  wudongf
                                  289 Posts
                                  ACCEPTED ANSWER

                                  Re: Excel files And Dashboards

                                  ‏2007-07-26T03:19:58Z  in response to SystemAdmin
                                  Do you mean that each user will have his owner xls file?
                                  If so, you can move the file to user specific folder, and each folder contains only the new uploaded file.

                                  If all the users share one file, you need only keep the latest file. Which means you can remove all the files except the new uploaded one.
                                  • SystemAdmin
                                    SystemAdmin
                                    801 Posts
                                    ACCEPTED ANSWER

                                    Re: Excel files And Dashboards

                                    ‏2007-07-26T07:46:36Z  in response to wudongf
                                    great idea, thanks!

                                    I'll have user specific subdirectories under the "upload" directory. that should solve the problem.
                                    • SystemAdmin
                                      SystemAdmin
                                      801 Posts
                                      ACCEPTED ANSWER

                                      Re: Excel files And Dashboards

                                      ‏2007-08-02T10:51:49Z  in response to SystemAdmin
                                      I have gone through sample models provided here but i still cannot get my table and graph to change when i select a fresh excel file. When you hit the "upload" button i would have expected to see changes, but this is not coded anywhere so im not sure what to do to make the table and graph change when a new file is uploaded. Please help... Thanks in advance
                                      • wudongf
                                        wudongf
                                        289 Posts
                                        ACCEPTED ANSWER

                                        Re: Excel files And Dashboards

                                        ‏2007-08-06T05:01:14Z  in response to SystemAdmin
                                        Please note: upload one excel file with File Upload builder will not update any excel file used in Excel Import builder.

                                        After you upload the excel file,you'll need to update the excel file used in Excel Import builder.

                                        This can be done in two ways:
                                        1. Remove the old excel file with the uploaded one
                                        2. Change the "File to Import" in Excel Import builder to reference to the new uploaded file.

                                        Please let me know if you've any questions regarding the above two ways.
                                        • SystemAdmin
                                          SystemAdmin
                                          801 Posts
                                          ACCEPTED ANSWER

                                          Re: Excel files And Dashboards

                                          ‏2007-08-07T08:35:11Z  in response to wudongf
                                          Thank you for your reply.

                                          The problem i have now is that i cannot delete or overwrite the old file as it's deemed "open". I have tried using some Java code but no success. I have profiled the file to import but again to no avail. If you have an example could you please provide me with one, Im hitting a dead end here.

                                          Many thanks
    • SystemAdmin
      SystemAdmin
      801 Posts
      ACCEPTED ANSWER

      Re: Excel files And Dashboards

      ‏2009-12-15T12:08:47Z  in response to silval
      Is this support available in the newer versions of portlet factory? For instance, can an excel file be loaded from a SharePoint site (without having to upload it to the app directory structure? Thanks.
      • wudongf
        wudongf
        289 Posts
        ACCEPTED ANSWER

        Re: Excel files And Dashboards

        ‏2009-12-16T06:10:16Z  in response to SystemAdmin
        I don't think so.

        Maybe you can get confirmed in Portlet Factory's forum -->
        https://www.ibm.com/developerworks/forums/thread.jspa?messageID=14417842&#14417842
  • SystemAdmin
    SystemAdmin
    801 Posts
    ACCEPTED ANSWER

    Re: Excel files And Dashboards

    ‏2007-07-22T07:35:30Z  in response to SystemAdmin
    although this is not an answer for Luis, still I'd like to use the same topic for the following problem:

    when I create an excelsheet in a different language version of Microsoft Excel, the dashboard gets confused and doesn't know automatically what would be the decimal sign.
    therefore it treats those numbers with decimals as a string and the chart gets messed up.

    same thing for percentual numbers (i.e. 56%, 24.7%, etc.)

    I've tried to manipulate the data in MS-Excel under Format > Cells > Numbers but it didn't help. Is it possible that I have to configure the Portal environment somewhere to get those numbers interpreted right?

    thank you in advance
    • wudongf
      wudongf
      289 Posts
      ACCEPTED ANSWER

      Re: Excel files And Dashboards

      ‏2007-07-25T09:13:14Z  in response to SystemAdmin
      This seems a limitation of Excel Import builder.
      When import data, it imports the formatted data.
      In your case, 56% turn out to be a String in the result xml.

      If you can change the xls, please change it.

      Otherwise, you may need to write one method to transform the result xml.
      Sample code may looks like:
      public IXml transform(IXml src){
      for(IXml row = src.getFirstChildElement();row != null;row = row.getNextSiblingElement()){
      String abc = row.getText("ABC");
      double abcNumber = Double.parseDouble(abc.substring(0,abc.length()-1))*100;//remove %
      row.setText(abcNumber+"");
      }
      return src;
      }
      • SystemAdmin
        SystemAdmin
        801 Posts
        ACCEPTED ANSWER

        Re: Excel files And Dashboards

        ‏2007-07-26T07:43:43Z  in response to wudongf
        yes I have access to the xls sheets, so I can change the % numbers. but what about numbers with decimal characters?
        I have to support multiple languages and unfortunatelly some countries use a period, some others use commas. Therefore, when I create an excelsheet with english MS Excel and someone logs in for instance with german profile, the whole portal layout is in german and with that, the dashboard is trying to put out the sheet with german decimal characters.

        that works fine on the table but the chart is messed up 'cause the german formatted "35,4" is not unterstood as numeric "35.4" but as a string and the chart remains empty.
        • wudongf
          wudongf
          289 Posts
          ACCEPTED ANSWER

          Re: Excel files And Dashboards

          ‏2007-07-30T03:36:43Z  in response to SystemAdmin
          Hi,
          I was wrong about the way to fix the % problem.
          In the builder UIof Excel Import/Enhanced, there is one builder input: Preserve Cell Formatting, if you uncheck it, the % problem can be fixed.

          Can you verify whether uncheck "Preserve Cell Formatting" can fix your german formatted "35,4" problem?

          If not, please attach your excel file, and let me have a check what's the problem here.
  • SystemAdmin
    SystemAdmin
    801 Posts
    ACCEPTED ANSWER

    Re: Excel files And Dashboards

    ‏2009-10-20T19:50:47Z  in response to SystemAdmin
    I see last post for this question is back in 2007. Can I please know whether this is fixed in recent versions of WDF.
    Replacing old file with new file when we upload with same name. If this is not fixed , can I please know whether we have any sample portlet code to delete the old file. Thanks.