class ExcelWriterStep extends BaseStep implements StepInterface {

  public void prepareNextOutputFile() throws KettleException {
    try {
      // sheet name shouldn't exceed 31 character
      if ( data.realSheetname != null && data.realSheetname.length() > 31 ) {
        throw new KettleException( BaseMessages.getString( PKG, "ExcelWriterStep.Exception.MaxSheetName", data.realSheetname ) );
      }
      // clear style cache
      int numOfFields = meta.getOutputFields() != null && meta.getOutputFields().length > 0 ? meta.getOutputFields().length : 0;
      if ( numOfFields == 0 ) {
        numOfFields = data.inputRowMeta != null ? data.inputRowMeta.size() : 0;
      }
      data.clearStyleCache( numOfFields );

      // build new filename
      String buildFilename = buildFilename( data.splitnr );

      data.file = KettleVFS.getFileObject( buildFilename, getTransMeta() );

      if ( log.isDebug() ) {
        logDebug( BaseMessages.getString( PKG, "ExcelWriterStep.Log.OpeningFile", buildFilename ) );
      }

      // determine whether existing file must be deleted
      if ( data.file.exists() && data.createNewFile ) {
        if ( !data.file.delete() ) {
          if ( log.isBasic() ) {
            logBasic( BaseMessages.getString( PKG, "ExcelWriterStep.Log.CouldNotDeleteStaleFile", buildFilename ) );
          }
          setErrors( 1 );
          throw new KettleException( "Could not delete stale file " + buildFilename );
        }
      }

      // adding filename to result
      if ( meta.isAddToResultFiles() ) {
        // Add this to the result file names...
        ResultFile resultFile = new ResultFile( ResultFile.FILE_TYPE_GENERAL, data.file, getTransMeta().getName(), getStepname() );
        resultFile.setComment( "This file was created with an Excel writer step by Pentaho Data Integration" );
        addResultFile( resultFile );
      }
      boolean appendingToSheet = true;
      // if now no file exists we must create it as indicated by user
      if ( !data.file.exists() ) {
        // if template file is enabled
        if ( meta.isTemplateEnabled() ) {
          // handle template case (must have same format)
          // ensure extensions match
          String templateExt = KettleVFS.getFileObject( data.realTemplateFileName ).getName().getExtension();
          if ( !meta.getExtension().equalsIgnoreCase( templateExt ) ) {
            throw new KettleException( "Template Format Mismatch: Template has extension: "
              + templateExt + ", but output file has extension: " + meta.getExtension()
              + ". Template and output file must share the same format!" );
          }

          if ( KettleVFS.getFileObject( data.realTemplateFileName ).exists() ) {
            // if the template exists just copy the template in place
            copyFile( KettleVFS.getFileObject( data.realTemplateFileName, getTransMeta() ), data.file );
          } else {
            // template is missing, log it and get out
            if ( log.isBasic() ) {
              logBasic( BaseMessages.getString( PKG, "ExcelWriterStep.Log.TemplateMissing", data.realTemplateFileName ) );
            }
            setErrors( 1 );
            throw new KettleException( "Template file missing: " + data.realTemplateFileName );
          }
        } else {
          // handle fresh file case, just create a fresh workbook
          Workbook wb = meta.getExtension().equalsIgnoreCase( "xlsx" ) ? new XSSFWorkbook() : new HSSFWorkbook();
          BufferedOutputStreamWithCloseDetection out = new BufferedOutputStreamWithCloseDetection( KettleVFS.getOutputStream( data.file, false ) );
          wb.createSheet( data.realSheetname );
          wb.write( out );
          out.close();
          wb.close();
        }
        appendingToSheet = false;
      }

      // file is guaranteed to be in place now
      if ( meta.getExtension().equalsIgnoreCase( "xlsx" ) ) {
        XSSFWorkbook xssfWorkbook = new XSSFWorkbook( KettleVFS.getInputStream( data.file ) );
        if ( meta.isStreamingData() && !meta.isTemplateEnabled() ) {
          data.wb = new SXSSFWorkbook( xssfWorkbook, 100 );
        } else {
          //Initialize it later after writing header/template because SXSSFWorkbook can't read/rewrite existing data,
          // only append.
          data.wb = xssfWorkbook;
        }
      } else {
        data.wb = new HSSFWorkbook( KettleVFS.getInputStream( data.file ) );
      }

      int existingActiveSheetIndex = data.wb.getActiveSheetIndex();
      int replacingSheetAt = -1;

      if ( data.wb.getSheet( data.realSheetname ) != null ) {
        // sheet exists, replace or reuse as indicated by user
        if ( data.createNewSheet ) {
          replacingSheetAt = data.wb.getSheetIndex( data.wb.getSheet( data.realSheetname ) );
          data.wb.removeSheetAt( replacingSheetAt );
        }
      }

      // if sheet is now missing, we need to create a new one
      if ( data.wb.getSheet( data.realSheetname ) == null ) {
        if ( meta.isTemplateSheetEnabled() ) {
          Sheet ts = data.wb.getSheet( data.realTemplateSheetName );
          // if template sheet is missing, break
          if ( ts == null ) {
            throw new KettleException( BaseMessages.getString( PKG, "ExcelWriterStep.Exception.TemplateNotFound", data.realTemplateSheetName ) );
          }
          data.sheet = data.wb.cloneSheet( data.wb.getSheetIndex( ts ) );
          data.wb.setSheetName( data.wb.getSheetIndex( data.sheet ), data.realSheetname );
          // unhide sheet in case it was hidden
          data.wb.setSheetHidden( data.wb.getSheetIndex( data.sheet ), false );
          if ( meta.isTemplateSheetHidden() ) {
            data.wb.setSheetHidden( data.wb.getSheetIndex( ts ), true );
          }
        } else {
          // no template to use, simply create a new sheet
          data.sheet = data.wb.createSheet( data.realSheetname );
        }
        if ( replacingSheetAt > -1 ) {
          data.wb.setSheetOrder( data.sheet.getSheetName(), replacingSheetAt );
        }
        // preserves active sheet selection in workbook
        data.wb.setActiveSheet( existingActiveSheetIndex );
        data.wb.setSelectedTab( existingActiveSheetIndex );
        appendingToSheet = false;
      } else {
        // sheet is there and should be reused
        data.sheet = data.wb.getSheet( data.realSheetname );
      }
      // if use chose to make the current sheet active, do so
      if ( meta.isMakeSheetActive() ) {
        int sheetIndex = data.wb.getSheetIndex( data.sheet );
        data.wb.setActiveSheet( sheetIndex );
        data.wb.setSelectedTab( sheetIndex );
      }
      // handle write protection
      if ( meta.isSheetProtected() ) {
        protectSheet( data.sheet, data.realPassword );
      }

      // starting cell support
      data.startingRow = 0;
      data.startingCol = 0;
      if ( !Utils.isEmpty( data.realStartingCell ) ) {
        CellReference cellRef = new CellReference( data.realStartingCell );
        data.startingRow = cellRef.getRow();
        data.startingCol = cellRef.getCol();
      }

      data.posX = data.startingCol;
      data.posY = data.startingRow;

      // Find last row and append accordingly
      if ( !data.createNewSheet && meta.isAppendLines() && appendingToSheet ) {
        data.posY = 0;
        if ( data.sheet.getPhysicalNumberOfRows() > 0 ) {
          data.posY = data.sheet.getLastRowNum();
          data.posY++;
        }
      }

      // offset by configured value
      // Find last row and append accordingly
      if ( !data.createNewSheet && meta.getAppendOffset() != 0 && appendingToSheet ) {
        data.posY += meta.getAppendOffset();
      }

      // may have to write a few empty lines
      if ( !data.createNewSheet && meta.getAppendEmpty() > 0 && appendingToSheet ) {
        for ( int i = 0; i < meta.getAppendEmpty(); i++ ) {
          openLine();
          if ( !data.shiftExistingCells || meta.isAppendLines() ) {
            data.posY++;
          }
        }
      }

      // may have to write a header here
      if ( meta.isHeaderEnabled() && !( !data.createNewSheet && meta.isAppendOmitHeader() && appendingToSheet ) ) {
        writeHeader();
      }
      if ( meta.isStreamingData() && meta.isTemplateEnabled() ) {
        data.wb = new SXSSFWorkbook( (XSSFWorkbook) data.wb, 100 );
        data.sheet = data.wb.getSheet( data.realSheetname );
      }
      if ( log.isDebug() ) {
        logDebug( BaseMessages.getString( PKG, "ExcelWriterStep.Log.FileOpened", buildFilename ) );
      }
      // this is the number of the new output file
      data.splitnr++;
    } catch ( Exception e ) {
      logError( "Error opening new file", e );
      setErrors( 1 );
      throw new KettleException( e );
    }
  }

  public  ExcelWriterStep( StepMeta s, StepDataInterface stepDataInterface, int c, TransMeta t, Trans dis );

}