Hitachi Vantara Pentaho Community Forums
Results 1 to 12 of 12

Thread: Excel Writer with charts in the template

  1. #1
    Join Date
    Dec 2009
    Posts
    332

    Default Excel Writer with charts in the template

    I have a request to generate an excel spreadsheet that has a worksheet tab for each grouping of the extracted results. I have done this many times using Pentaho PDI and the Excel Writer step using a template tab which is hidden in the template spreadsheet. A new tab is generated using a passed-in variable which also drives the Table Input query. This allows the end result to have a variable number of of result worksheet tabs.

    The complexity tossed into the mix today was that each of these template-driven generated worksheet tabs also requires a chart. This is failing one way or the other.

    If I use an "xls" file format, the process fails with a message relating to the POI not including a clone feature for charts:
    java.lang.RuntimeException: The class org.apache.poi.hssf.record.chart.ChartFRTInfoRecord needs to define a clone method

    If I use an "xlsx" file format, the process succeeds, but the chart is not copied to each new tab and remains only on the template.

    I am at a loss and may have to abandon the PDI for this project.

    Any suggestions?

  2. #2
    Join Date
    Feb 2011
    Posts
    840

    Default

    I don't know if I have understood correctly what you're trying to accomplish...
    but I have a process where, at the end, I use a template populated by PDI to supply information to a chart...

    PDI populates a sheet with all the "brute" information I need. then, a second sheet uses PROCV to deal with the first part of the process, and a third sheet, containing the charts, references to this second sheet's results.

    I may eventually hit a problem, since the PROCV search area is predefined, and the first sheet may eventually go over that area, but so far I haven't had that problem, and all the charts show up nicely.
    Join us on IRC! =)

    Twitter / Google+ / Timezone: BRT-BRST
    BI Server & PDI 5.4 / MS SQL 2012 / Learning CDE & CTools
    Windows 8 64-bit / Java 7 (jdk1.8.0_75)

    Quote Originally Posted by gutlez
    PLEASE NOTE: No forum member is going to do your work for you. We will help you sort out how to do a specific part of the work, as best we can, in the timelines that our work will allow us.

    I'm no expert.Take my comments at your own risk.

  3. #3
    Join Date
    Dec 2009
    Posts
    332

    Default

    Thank you for your response.

    The main problem is that I have an unknown/variable number of charts that must be generated using a template. The spreadsheets cannot include macros. Their titles and cells they reference must be dynamically defined.

    I am not familiar with PROCV and all the google results for PROCV are non-english language webpages. Is PROCV a localized Excel function name?

  4. #4
    Join Date
    Jun 2012
    Posts
    5,534

    Default

    Quote Originally Posted by khelms View Post
    Is PROCV a localized Excel function name?
    Portuguese for VLOOKUP...
    So long, and thanks for all the fish.

  5. #5
    Join Date
    Feb 2011
    Posts
    840

    Default

    Quote Originally Posted by marabu View Post
    Portuguese for VLOOKUP...
    ops, my bad. didn't realized it was pt-br localization.

    how do you get to that variable number of charts? Depending on how that's done, you could create one transformation that would have the entries for generating these charts first, then back to the job level, create a second one which would run with "Execute for every input row" option enabled.
    Join us on IRC! =)

    Twitter / Google+ / Timezone: BRT-BRST
    BI Server & PDI 5.4 / MS SQL 2012 / Learning CDE & CTools
    Windows 8 64-bit / Java 7 (jdk1.8.0_75)

    Quote Originally Posted by gutlez
    PLEASE NOTE: No forum member is going to do your work for you. We will help you sort out how to do a specific part of the work, as best we can, in the timelines that our work will allow us.

    I'm no expert.Take my comments at your own risk.

  6. #6
    Join Date
    Dec 2009
    Posts
    332

    Default

    I have several transforms which work just fine generating new worksheet tabs for groupings of data output. That part is not difficult. The challenge is creating a chart on each new worksheet tab.

  7. #7
    Join Date
    Feb 2011
    Posts
    840

    Default

    each chart is exclusive to a type of input, that'd be the problem? You could map all the different types and make the same aamount of templates... And point them all to the same file, setting the Excel Writer to use if already exists, instead of blanking the original?
    Join us on IRC! =)

    Twitter / Google+ / Timezone: BRT-BRST
    BI Server & PDI 5.4 / MS SQL 2012 / Learning CDE & CTools
    Windows 8 64-bit / Java 7 (jdk1.8.0_75)

    Quote Originally Posted by gutlez
    PLEASE NOTE: No forum member is going to do your work for you. We will help you sort out how to do a specific part of the work, as best we can, in the timelines that our work will allow us.

    I'm no expert.Take my comments at your own risk.

  8. #8
    Join Date
    Dec 2009
    Posts
    332

    Default

    Quote Originally Posted by joao.ciocca View Post
    each chart is exclusive to a type of input, that'd be the problem? You could map all the different types and make the same aamount of templates... And point them all to the same file, setting the Excel Writer to use if already exists, instead of blanking the original?
    The challenge is that the Excel Writer step does not successfully replicate the chart from a template worksheet tab to each new tab. Attached is a non-database driven example that generates some nonsense data and outputs it to an Excel Writer step. The output goes to a new tab which is named based on the transform's single defaulted parameter. The template worksheet tab has a chart and a sample data set. The generated output spreadsheet retains the original template tab with the chart and includes the new result tab with the data but no chart.

    How can we get the chart to replicate?
    Attached Files Attached Files

  9. #9
    Join Date
    Apr 2008
    Posts
    1,771

    Default

    Hi.
    I think that the charting part is created by Excel, therefore you either need a macro to run after you have populated the sheets, or create a template with as many sheets and charts "pre-created" as you need.

    Mick

  10. #10
    Join Date
    Dec 2009
    Posts
    332

    Default

    Mick - I think you are correct, but neither of your suggestions (unfortunately) will match the report requirements at this time. Excel Macros are verboten and there are an unknown number of charts each morning. However the number of charts does not vary too often and we could in theory manually update the template each day. This manual process would rapidly, and with reasonably certainly, lead to a loss of sanity and workplace violence.

    I attempted to use Pentaho Reporting to resolve the issue, but unfortunately charts in Pentaho Reporting export to Excel apparently as images. The resulting charts were arguably illegible.

    Another option I have been exploring is generating each result as a separate file and then combining the separate files into a single workbook. There seems to be a fair number of java applications out there that do this sort of thing, but this is going to hugely expand the complexity and not provide anyone here with a solution.

    I have sent the same sample into Pentaho enterprise support and will let you know when they come back with a resolution.

  11. #11
    Join Date
    Feb 2011
    Posts
    840

    Default

    I'm thinking you could do something like using a filename without time, only date, and set the output not to create a new file/overwrite an existing file.

    That way, each sheet tab would have it's own name, but they'd all be in a single file...

    tried that, didn't work as expected, but now I understand a little more your problem! It seems to me that it's actually a bug on Excel Writer step itself, it doesn't recognizes the chart as an item of the template, an item that should be replicated to the new sheets.

    if Excel Writer would replicate the chart, you could hide the template sheet (you'd need to create a cover sheet in order for the template one to be hidden)... and you'd have a result somewhat like the attached "PentahoChart26522_120920.xlsx" - except that, in case Excel Writer was fixed for it, sheets test1, test2 and test3 would show their charts correctly.

    I believe Slawo could give us a hand here =) if he's reading... anywhere...?
    Attached Files Attached Files
    Last edited by joao.ciocca; 09-20-2012 at 02:46 PM. Reason: ops! forgot the attachments!
    Join us on IRC! =)

    Twitter / Google+ / Timezone: BRT-BRST
    BI Server & PDI 5.4 / MS SQL 2012 / Learning CDE & CTools
    Windows 8 64-bit / Java 7 (jdk1.8.0_75)

    Quote Originally Posted by gutlez
    PLEASE NOTE: No forum member is going to do your work for you. We will help you sort out how to do a specific part of the work, as best we can, in the timelines that our work will allow us.

    I'm no expert.Take my comments at your own risk.

  12. #12
    Join Date
    Dec 2009
    Posts
    332

    Default

    Pentaho Support - Man Shing Yau had this response:

    I have the following response from engineering which confirms the problem is on the Excel side.
    It appears to be a semi-known (certainly previously reported) issue with worksheet.cloneSheet - something beyond our control. Research indicates that the workarounds are pretty complex (
    http://stackoverflow.com/questions/9...taining-charts ).
    This is reported on their bug tracker here:
    https://issues.apache.org/bugzilla/s...g.cgi?id=47352


    The downside is that Pentaho Support has not provided a solution, but at least I know where to go from here.

    I am going to try this approach:
    1 - Create a template workbook with the chart defined using named ranges
    2 - Use PDI to generate individual worksheets named uniquely for each required result chart
    3 - Use custom java application with Apache POI using HSSF to rename the tabs in these generated workbooks to match the required result name
    4 - Use custom java application with Apache POI using HSSF to iteratively combine the multiple result worksheets into a single workbook in-order.
    5 - Use PDI to attach and email the resulting report workbook.

    This process is a pain, but appears to be feasible.

    I am also going to request that items 3 and 4 above be added to the PDI so everyone can do this.

    Anyone have some other solutions?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.