Hitachi Vantara Pentaho Community Forums
Results 1 to 5 of 5

Thread: Error when using xslx file as template in Microsoft Excel Writer step

  1. #1
    Join Date
    Apr 2016
    Posts
    4

    Default Error when using xslx file as template in Microsoft Excel Writer step

    Previously I was using version 5.4, and had no problem using xslx files as template file in Microsoft Excel Writer step. Now I upgraded to version 7.1, and get the following error:

    Code:
    org.apache.commons.vfs2.FileSystemException: File closed.
            at org.apache.commons.vfs2.util.MonitorOutputStream.assertOpen(MonitorOutputStream.java:162)
            at org.apache.commons.vfs2.util.MonitorOutputStream.flush(MonitorOutputStream.java:133)
            at java.io.BufferedOutputStream.flush(BufferedOutputStream.java:141)
            at java.io.FilterOutputStream.close(FilterOutputStream.java:158)
            at org.pentaho.di.trans.steps.excelwriter.ExcelWriterStep.copyFile(ExcelWriterStep.java:643)
            at org.pentaho.di.trans.steps.excelwriter.ExcelWriterStep.prepareNextOutputFile(ExcelWriterStep.java:717)
            at org.pentaho.di.trans.steps.excelwriter.ExcelWriterStep.processRow(ExcelWriterStep.java:109)
            at org.pentaho.di.trans.step.RunThread.run(RunThread.java:62)
            at java.lang.Thread.run(Thread.java:748)
    Nov 17, 2017 2:52:33 PM org.apache.cxf.endpoint.ServerImpl initDestination
    INFO: Setting the server's publish address to be /marketplace
    Nov 17, 2017 2:52:33 PM org.apache.cxf.endpoint.ServerImpl initDestination
    INFO: Setting the server's publish address to be /lineage
    Nov 17, 2017 2:52:33 PM org.apache.cxf.endpoint.ServerImpl initDestination
    INFO: Setting the server's publish address to be /i18n
    SLF4J: Class path contains multiple SLF4J bindings.
    SLF4J: Found binding in [jar:file:/opt/pentaho/data-integration/launcher/../lib/slf4j-log4j12-1.7.7.jar!/org/slf4j/impl/StaticLoggerBinder.class]
    SLF4J: Found binding in [jar:file:/opt/pentaho/data-integration/plugins/pentaho-big-data-plugin/lib/slf4j-log4j12-1.7.7.jar!/org/slf4j/impl/StaticLoggerBinder.class]
    SLF4J: See http://www.slf4j.org/codes.html#multiple_bindings for an explanation.
    SLF4J: Actual binding is of type [org.slf4j.impl.Log4jLoggerFactory]
    2017/11/17 14:52:33 - Microsoft Excel Writer.0 - ERROR (version 7.1.0.0-12, build 1 from 2017-05-16 17.18.02 by buildguy) : Error opening new file
    2017/11/17 14:52:33 - Microsoft Excel Writer.0 - ERROR (version 7.1.0.0-12, build 1 from 2017-05-16 17.18.02 by buildguy) : org.pentaho.di.core.exception.KettleException:
    2017/11/17 14:52:33 - Microsoft Excel Writer.0 - java.lang.IllegalArgumentException: Attempting to write a row[0] in the range [0,0] that is already written to disk.
    2017/11/17 14:52:33 - Microsoft Excel Writer.0 - Attempting to write a row[0] in the range [0,0] that is already written to disk.
    2017/11/17 14:52:33 - Microsoft Excel Writer.0 -
    2017/11/17 14:52:33 - Microsoft Excel Writer.0 -        at org.pentaho.di.trans.steps.excelwriter.ExcelWriterStep.writeHeader(ExcelWriterStep.java:919)
    2017/11/17 14:52:33 - Microsoft Excel Writer.0 -        at org.pentaho.di.trans.steps.excelwriter.ExcelWriterStep.prepareNextOutputFile(ExcelWriterStep.java:858)
    2017/11/17 14:52:33 - Microsoft Excel Writer.0 -        at org.pentaho.di.trans.steps.excelwriter.ExcelWriterStep.processRow(ExcelWriterStep.java:109)
    2017/11/17 14:52:33 - Microsoft Excel Writer.0 -        at org.pentaho.di.trans.step.RunThread.run(RunThread.java:62)
    2017/11/17 14:52:33 - Microsoft Excel Writer.0 -        at java.lang.Thread.run(Thread.java:748)
    2017/11/17 14:52:33 - Microsoft Excel Writer.0 - Caused by: java.lang.IllegalArgumentException: Attempting to write a row[0] in the range [0,0] that is already written to disk.
    2017/11/17 14:52:33 - Microsoft Excel Writer.0 -        at org.apache.poi.xssf.streaming.SXSSFSheet.createRow(SXSSFSheet.java:133)
    2017/11/17 14:52:33 - Microsoft Excel Writer.0 -        at org.apache.poi.xssf.streaming.SXSSFSheet.createRow(SXSSFSheet.java:62)
    2017/11/17 14:52:33 - Microsoft Excel Writer.0 -        at org.pentaho.di.trans.steps.excelwriter.ExcelWriterStep.writeHeader(ExcelWriterStep.java:890)
    2017/11/17 14:52:33 - Microsoft Excel Writer.0 -        ... 4 more
    2017/11/17 14:52:33 - Microsoft Excel Writer.0 - ERROR (version 7.1.0.0-12, build 1 from 2017-05-16 17.18.02 by buildguy) : Could not prepare output file /tmp/test
    2017/11/17 14:52:33 - Microsoft Excel Writer.0 - Finished processing (I=0, O=0, R=1, W=0, U=0, E=1)
    2017/11/17 14:52:33 - test - Transformation detected one or more steps with errors.
    2017/11/17 14:52:33 - Pan - Finished!
    2017/11/17 14:52:33 - test - Transformation is killing the other steps!
    2017/11/17 14:52:33 - Pan - Start=2017/11/17 14:52:32.038, Stop=2017/11/17 14:52:33.534
    2017/11/17 14:52:33 - Pan - Processing ended after 1 seconds.
    According to PDI-12270 this issue was fixed in version 6.0.1/6.1.0, but I have this problem in version 7.1.

    To reproduce error:

    1. Make a new Excel file. Change the background color of cell A1 to yellow. Save file as mytemplate.xlsx.
    2. Make a new Transformation. Add a Generate Rows Step and a Excel Writer Step with a hop between them.
    3. In the Generate Rows Step, define a field: set Name=foo, Type=String and Value=test
    4. In the Excel Writer Step, set Filename, set Extension to xlsx, check 'Stream XSLX data', set 'If sheet exists in output file' to 'write to existing sheet', check 'Use template when creating new files', set 'Template file' to /path/to/mytemplate.xlsx and click 'Get Fields' in Content.
    5. Save and run transformation.


    My environment: Ubuntu 16.04, Java version 1.8.0_144, Pentaho 7.1

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

    Default

    Have you considered to attach a zip file containing everything necessary to reproduce the problem?
    So long, and thanks for all the fish.

  3. #3
    Join Date
    Apr 2016
    Posts
    4

    Default

    Have you considered to attach a zip file containing everything necessary to reproduce the problem?
    Good idea

    See attached zip-file. Change the template path as appropriate.
    Attached Files Attached Files

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

    Default

    Thanks for the test case.
    You can specify filenames relative to the ktr file by using variable reference ${Internal.Transformation.Filename.Directory}/filename (or ${Internal.Entry.Current.Directory}/filename since version 7).

    There seems to be a bug with the streaming output.
    A workaround would be to disable option "Stream XLSX data".

    If you open a Jira case and link it here, chance is that someone will vote for it thus raising its priority.
    You'll have to register for Jira separately, though.

  5. #5
    Join Date
    Apr 2016
    Posts
    4

    Default

    Thanks for taking a look at this. I just opened Jira issue PDI-16803

Tags for this Thread

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.