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

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

    Re: Excel files And Dashboards

    ‏2006-10-04T19:40:52Z  
    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

    Re: Excel files And Dashboards

    ‏2006-10-04T21:09:25Z  
    • silval
    • ‏2006-10-04T19:40:52Z
    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
    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

    Re: Excel files And Dashboards

    ‏2006-10-18T15:41:30Z  
    Thanks Luciano

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

    Regards

    Luis Inacio
    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

    Re: Excel files And Dashboards

    ‏2006-10-18T15:55:30Z  
    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
    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

    Re: Excel files And Dashboards

    ‏2007-07-16T20:28:37Z  
    • silval
    • ‏2006-10-04T19:40:52Z
    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
    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

    Re: Excel files And Dashboards

    ‏2007-07-17T08:21:17Z  
    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
    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

    Re: Excel files And Dashboards

    ‏2007-07-17T10:27:48Z  
    • wudongf
    • ‏2007-07-17T08:21:17Z
    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.
    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

    Re: Excel files And Dashboards

    ‏2007-07-17T10:30:52Z  
    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)
    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

    Re: Excel files And Dashboards

    ‏2007-07-17T11:03:56Z  
    • wudongf
    • ‏2007-07-17T10:30:52Z
    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?
    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

    Re: Excel files And Dashboards

    ‏2007-07-17T11:41:06Z  
    • wudongf
    • ‏2007-07-17T10:30:52Z
    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?
    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

    Re: Excel files And Dashboards

    ‏2007-07-18T06:11:29Z  
    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?
    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

    Re: Excel files And Dashboards

    ‏2007-07-18T08:20:43Z  
    • wudongf
    • ‏2007-07-18T06:11:29Z
    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)
    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

    Re: Excel files And Dashboards

    ‏2007-07-19T05:35:44Z  
    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
    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

    Re: Excel files And Dashboards

    ‏2007-07-19T07:43:55Z  
    • wudongf
    • ‏2007-07-19T05:35:44Z
    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.
    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

    Re: Excel files And Dashboards

    ‏2007-07-19T08:28:38Z  
    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!
    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

    Re: Excel files And Dashboards

    ‏2007-07-19T17:23:57Z  
    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!
    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

    Re: Excel files And Dashboards

    ‏2007-07-22T07:22:05Z  
    • DGawron
    • ‏2007-07-19T17:23:57Z
    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)."
    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

    Re: Excel files And Dashboards

    ‏2007-07-22T07:35:30Z  
    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
  • SystemAdmin
    SystemAdmin
    801 Posts

    Re: Excel files And Dashboards

    ‏2007-07-24T08:23:07Z  
    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!
    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

    Re: Excel files And Dashboards

    ‏2007-07-25T09:02:56Z  
    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
    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?
  • wudongf
    wudongf
    289 Posts

    Re: Excel files And Dashboards

    ‏2007-07-25T09:13:14Z  
    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
    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

    Re: Excel files And Dashboards

    ‏2007-07-25T20:36:59Z  
    • wudongf
    • ‏2007-07-25T09:02:56Z
    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?
    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

    Re: Excel files And Dashboards

    ‏2007-07-26T03:19:58Z  
    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)
    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

    Re: Excel files And Dashboards

    ‏2007-07-26T07:43:43Z  
    • wudongf
    • ‏2007-07-25T09:13:14Z
    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;
    }
    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.