org.apache.poi.ss.usermodel.DataFormat Java Examples

private CellStyle getDecimalNumberFormat(int j, Sheet sheet, CreationHelper createHelper, CellStyle dCellStyle) {

		if (decimalFormats.get(j) != null)
			return decimalFormats.get(j);
		String decimals = "";
		for (int i = 0; i < j; i++) {
			decimals += "0";
		}

		CellStyle cellStyleDoub = this.buildCellStyle(sheet); // cellStyleDoub is the default cell style for doubles
		cellStyleDoub.cloneStyleFrom(dCellStyle);
		DataFormat df = createHelper.createDataFormat();
		String format = "#,##0";
		if (decimals.length() > 0) {
			format += "." + decimals;
		}
		cellStyleDoub.setDataFormat(df.getFormat(format));

		decimalFormats.put(j, cellStyleDoub);
		return cellStyleDoub;
	}
 

/**
 * create the styles in the workbook
 */
private void createStyles(Workbook wb) {
	// create the styles
	this.checkboxStyle = wb.createCellStyle();
	this.checkboxStyle.setAlignment(CellStyle.ALIGN_CENTER);
	this.checkboxStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
	this.checkboxStyle.setBorderBottom(CellStyle.BORDER_THIN);
	this.checkboxStyle.setBorderLeft(CellStyle.BORDER_THIN);
	this.checkboxStyle.setBorderRight(CellStyle.BORDER_THIN);
	this.checkboxStyle.setBorderTop(CellStyle.BORDER_THIN);
	Font checkboxFont = wb.createFont();
	checkboxFont.setFontHeight(FONT_SIZE);
	checkboxFont.setFontName(CHECKBOX_FONT_NAME);
	this.checkboxStyle.setFont(checkboxFont);

	this.dateStyle = wb.createCellStyle();
	DataFormat df = wb.createDataFormat();
	this.dateStyle.setDataFormat(df.getFormat("m/d/yy h:mm"));
}
 

/**
 * 小数格式
 *
 * @return CellStyle
 */
public CellStyle getDecimalStyle() {
    if (buildInStyleMap.containsKey(DECIMAL_STYLE_KEY)) {
        return buildInStyleMap.get(DECIMAL_STYLE_KEY);
    }

    CellStyle decimalStyle = workbook.createCellStyle();//小数样式
    if (!buildInFormatMap.containsKey(DATA_FORMAT_KEY)) {
        DataFormat dataFormat = workbook.createDataFormat();
        buildInFormatMap.put(DATA_FORMAT_KEY, dataFormat);
    }
    decimalStyle.setDataFormat(buildInFormatMap.get(DATA_FORMAT_KEY).getFormat("0.00"));
    this.setCommonStyle(decimalStyle);
    buildInStyleMap.put(DECIMAL_STYLE_KEY, decimalStyle);
    return decimalStyle;
}
 

/**
 * 日期样式 yyyy-MM-dd HH:mm
 *
 * @return CellStyle
 */
public CellStyle getDateStyle() {
    if (buildInStyleMap.containsKey(DATE_STYLE_KEY)) {
        return buildInStyleMap.get(DATE_STYLE_KEY);
    }

    CellStyle dateStyle = workbook.createCellStyle();//日期样式
    if (!buildInFormatMap.containsKey(DATA_FORMAT_KEY)) {
        DataFormat dataFormat = workbook.createDataFormat();
        buildInFormatMap.put(DATA_FORMAT_KEY, dataFormat);
    }
    dateStyle.setDataFormat(buildInFormatMap.get(DATA_FORMAT_KEY).getFormat("yyyy-MM-dd HH:mm"));
    this.setCommonStyle(dateStyle);
    buildInStyleMap.put(DATE_STYLE_KEY, dateStyle);
    return dateStyle;
}
 

/**
 * 日期样式 yyyy/MM/dd
 *
 * @return CellStyle
 */
public CellStyle getDate8Style() {

    if (buildInStyleMap.containsKey(DATE_8_STYLE_KEY)) {
        return buildInStyleMap.get(DATE_8_STYLE_KEY);
    }

    CellStyle date8Style = workbook.createCellStyle();//年月日样式
    if (!buildInFormatMap.containsKey(DATA_FORMAT_KEY)) {
        DataFormat dataFormat = workbook.createDataFormat();
        buildInFormatMap.put(DATA_FORMAT_KEY, dataFormat);
    }
    date8Style.setDataFormat(buildInFormatMap.get(DATA_FORMAT_KEY).getFormat("yyyy/MM/dd"));
    this.setCommonStyle(date8Style);
    buildInStyleMap.put(DATE_8_STYLE_KEY, date8Style);
    return date8Style;
}
 

/**
 * 根据格式，创建返回样式对象
 *
 * @param format 格式
 * @return 样式对象
 */
public CellStyle getCustomFormatStyle(String format,HSSFColor color) {

    //存在对应格式直接返回
    if (customFormatStyleMap.containsKey(format) && color== null) {
        return customFormatStyleMap.get(format);
    }
    CellStyle customDateStyle = workbook.createCellStyle();
    if (!buildInFormatMap.containsKey(DATA_FORMAT_KEY)) {
        DataFormat dataFormat = workbook.createDataFormat();
        buildInFormatMap.put(DATA_FORMAT_KEY, dataFormat);
    }
    customDateStyle.setDataFormat(buildInFormatMap.get(DATA_FORMAT_KEY).getFormat(format));
    if (color == null){
        //放入map缓存
        customFormatStyleMap.put(format, customDateStyle);
    }else {
        customDateStyle.setFillForegroundColor(color.getIndex());
        customDateStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
    }
    this.setCommonStyle(customDateStyle);

    return customDateStyle;
}
 

protected void createFormats() {
    timeFormatCellStyle = wb.createCellStyle();
    String timeFormat = messages.getMainMessage("excelExporter.timeFormat");
    timeFormatCellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat(timeFormat));

    dateFormatCellStyle = wb.createCellStyle();
    String dateFormat = messages.getMainMessage("excelExporter.dateFormat");
    dateFormatCellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat(dateFormat));

    dateTimeFormatCellStyle = wb.createCellStyle();
    String dateTimeFormat = messages.getMainMessage("excelExporter.dateTimeFormat");
    dateTimeFormatCellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat(dateTimeFormat));

    integerFormatCellStyle = wb.createCellStyle();
    String integerFormat = messages.getMainMessage("excelExporter.integerFormat");
    integerFormatCellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat(integerFormat));

    DataFormat format = wb.createDataFormat();
    doubleFormatCellStyle = wb.createCellStyle();
    String doubleFormat = messages.getMainMessage("excelExporter.doubleFormat");
    doubleFormatCellStyle.setDataFormat(format.getFormat(doubleFormat));
}
 

/**
 * Set specified cell format
 *
 * @param excelFieldFormat the specified format
 * @param cell             the cell to set up format
 */
private void setDataFormat( String excelFieldFormat, Cell cell ) {
  if ( log.isDebug() ) {
    logDebug( BaseMessages.getString( PKG, "ExcelWriterTransform.Log.SetDataFormat", excelFieldFormat, CellReference.convertNumToColString( cell.getColumnIndex() ), cell.getRowIndex() ) );
  }

  DataFormat format = data.wb.createDataFormat();
  short formatIndex = format.getFormat( excelFieldFormat );
  CellStyle style = data.wb.createCellStyle();
  style.cloneStyleFrom( cell.getCellStyle() );
  style.setDataFormat( formatIndex );
  cell.setCellStyle( style );
}
 

/**
 * create the styles in the workbook
 */
private void createStyles(Workbook wb) {
	// create the styles
	this.checkboxStyle = wb.createCellStyle();
	this.checkboxStyle.setAlignment(HorizontalAlignment.CENTER);
	this.checkboxStyle.setVerticalAlignment(VerticalAlignment.CENTER);
	this.checkboxStyle.setBorderBottom(BorderStyle.THIN);
	this.checkboxStyle.setBorderLeft(BorderStyle.THIN);
	this.checkboxStyle.setBorderRight(BorderStyle.THIN);
	this.checkboxStyle.setBorderTop(BorderStyle.THIN);
	Font checkboxFont = wb.createFont();
	checkboxFont.setFontHeight(FONT_SIZE);
	checkboxFont.setFontName(CHECKBOX_FONT_NAME);
	this.checkboxStyle.setFont(checkboxFont);
	
	this.dateStyle = wb.createCellStyle();
	DataFormat df = wb.createDataFormat();
	this.dateStyle.setDataFormat(df.getFormat("m/d/yy h:mm"));
	
	this.greenWrapped = createLeftWrapStyle(wb);
	this.greenWrapped.setFillForegroundColor(HSSFColor.LIGHT_GREEN.index);
	this.greenWrapped.setFillPattern(FillPatternType.SOLID_FOREGROUND);
	this.greenWrapped.setFillPattern(FillPatternType.SOLID_FOREGROUND);
	
	this.yellowWrapped = createLeftWrapStyle(wb);
	this.yellowWrapped.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index);
	this.yellowWrapped.setFillPattern(FillPatternType.SOLID_FOREGROUND);
	
	this.redWrapped = createLeftWrapStyle(wb);
	this.redWrapped.setFillForegroundColor(HSSFColor.RED.index);
	this.redWrapped.setFillPattern(FillPatternType.SOLID_FOREGROUND);
}
 

/**
 * Set specified cell format
 *
 * @param excelFieldFormat the specified format
 * @param cell             the cell to set up format
 */
private void setDataFormat( String excelFieldFormat, Cell cell ) {
  if ( log.isDebug() ) {
    logDebug( BaseMessages.getString( PKG, "ExcelWriterStep.Log.SetDataFormat", excelFieldFormat, CellReference.convertNumToColString( cell.getColumnIndex() ), cell.getRowIndex() ) );
  }

  DataFormat format = data.wb.createDataFormat();
  short formatIndex = format.getFormat( excelFieldFormat );
  CellStyle style = data.wb.createCellStyle();
  style.cloneStyleFrom( cell.getCellStyle() );
  style.setDataFormat( formatIndex );
  cell.setCellStyle( style );
}
 

/**
 * Apply a BIRT number/date/time format to a POI CellStyle.
 * @param workbook
 * The workbook containing the CellStyle (needed to create a new DataFormat).
 * @param birtStyle
 * The BIRT style which may contain a number format.
 * @param poiStyle
 * The CellStyle that is to receive the number format.
 */
public void applyNumberFormat(Workbook workbook, BirtStyle birtStyle, CellStyle poiStyle, Locale locale) {
	String dataFormat = null;
	String format = getNumberFormat(birtStyle);
	if( format != null ) {
		log.debug( "BIRT number format == ", format);
		dataFormat = poiNumberFormatFromBirt(format);
	} else {
		format = getDateTimeFormat(birtStyle);
		if( format != null ) {
			log.debug( "BIRT date/time format == ", format );
			dataFormat = poiDateTimeFormatFromBirt( format, locale );
		} else {
			format = getTimeFormat(birtStyle);
			if( format != null ) {
				log.debug( "BIRT time format == ", format );
				dataFormat = poiDateTimeFormatFromBirt( format, locale );
			} else {
				format = getDateFormat(birtStyle);
				if( format != null ) {
					log.debug( "BIRT date format == ", format );
					dataFormat = poiDateTimeFormatFromBirt( format, locale );
				}
			}
		}
	}
	if( dataFormat != null ) {
		DataFormat poiFormat = workbook.createDataFormat();
		log.debug( "Setting POI data format to ", dataFormat);
		poiStyle.setDataFormat(poiFormat.getFormat(dataFormat));
	}
}
 

ExcelTestHelper(final String parent, boolean generateXls) throws Exception {
  this.xls = generateXls;

  // Create a test Excel sheet with all types of supported data
  Workbook wb = generateXls ? new HSSFWorkbook() : new XSSFWorkbook();

  CreationHelper creationHelper = wb.getCreationHelper();
  DataFormat dataFormat = creationHelper.createDataFormat();
  short fmt = dataFormat.getFormat("yyyy-mm-dd hh:mm:ss");
  CellStyle style = wb.createCellStyle();
  style.setDataFormat(fmt);

  Sheet sheetWithHeader = wb.createSheet("Sheet 1");

  // Create header row
  Row headerRow = sheetWithHeader.createRow((short) 0);
  headerRow.createCell(0).setCellValue("Number");
  headerRow.createCell(1).setCellValue("String1");
  headerRow.createCell(2).setCellValue("String2");
  headerRow.createCell(3).setCellValue("MyTime");
  headerRow.createCell(4).setCellValue("Formula");
  headerRow.createCell(5).setCellValue("Boolean");
  headerRow.createCell(6).setCellValue("Error");
  generateSheetData(sheetWithHeader, style, (short)1);

  Sheet sheetWithoutHeader = wb.createSheet("Sheet 2");
  generateSheetData(sheetWithoutHeader, style, (short)0);

  testFilePath = new File(parent, "excelTestFile").getPath();

  // Write the output to a file
  FileOutputStream fileOut = new FileOutputStream(testFilePath);
  wb.write(fileOut);
  fileOut.close();
}
 

/**
 * 添加一个单元格
 * @param row 添加的行
 * @param column 添加列号
 * @param val 添加值
 * @param align 对齐方式（1：靠左；2：居中；3：靠右）
 * @return 单元格对象
 */
public Cell addCell(Row row, int column, Object val, int align, Class<?> fieldType){
	Cell cell = row.createCell(column);
	CellStyle style = styles.get("data"+(align>=1&&align<=3?align:""));
	try {
		if (val == null){
			cell.setCellValue("");
		} else if (val instanceof String) {
			cell.setCellValue((String) val);
		} else if (val instanceof Integer) {
			cell.setCellValue((Integer) val);
		} else if (val instanceof Long) {
			cell.setCellValue((Long) val);
		} else if (val instanceof Double) {
			cell.setCellValue((Double) val);
		} else if (val instanceof Float) {
			cell.setCellValue((Float) val);
		} else if (val instanceof Date) {
			DataFormat format = wb.createDataFormat();
            style.setDataFormat(format.getFormat("yyyy-MM-dd"));
			cell.setCellValue((Date) val);
		} else {
			if (fieldType != Class.class){
				cell.setCellValue((String)fieldType.getMethod("setValue", Object.class).invoke(null, val));
			}else{
				cell.setCellValue((String)Class.forName(this.getClass().getName().replaceAll(this.getClass().getSimpleName(), 
					"fieldtype."+val.getClass().getSimpleName()+"Type")).getMethod("setValue", Object.class).invoke(null, val));
			}
		}
	} catch (Exception ex) {
		log.info("Set cell value ["+row.getRowNum()+","+column+"] error: " + ex.toString());
		cell.setCellValue(val.toString());
	}
	cell.setCellStyle(style);
	return cell;
}
 

public CellStyle getCustomDataFormatStyle(String dataFormat) {
  CellStyle cellStyle = wb.createCellStyle();
  DataFormat df = wb.createDataFormat();
  cellStyle.setDataFormat(df.getFormat(dataFormat));
  return cellStyle;
}
 

/**
 * Setup the data necessary for Excel Writer step
 *
 * @param fileType
 * @throws KettleException
 */
private void createStepData( String fileType ) throws KettleException {
  stepData = new ExcelWriterStepData();
  stepData.inputRowMeta = step.getInputRowMeta().clone();
  stepData.outputRowMeta = step.getInputRowMeta().clone();

  // we don't run transformation so ExcelWriterStep.processRow() doesn't get executed
  // we populate the ExcelWriterStepData with bare minimum required values
  CellReference cellRef = new CellReference( stepMeta.getStartingCell() );
  stepData.startingRow = cellRef.getRow();
  stepData.startingCol = cellRef.getCol();
  stepData.posX = stepData.startingCol;
  stepData.posY = stepData.startingRow;

  int numOfFields = stepData.inputRowMeta.size();
  stepData.fieldnrs = new int[numOfFields];
  stepData.linkfieldnrs = new int[numOfFields];
  stepData.commentfieldnrs = new int[numOfFields];
  for ( int i = 0; i < numOfFields; i++ ) {
    stepData.fieldnrs[i] = i;
    stepData.linkfieldnrs[i] = -1;
    stepData.commentfieldnrs[i] = -1;
  }

  // we avoid reading/writing Excel files, so ExcelWriterStep.prepareNextOutputFile() doesn't get executed
  // create Excel workbook object
  stepData.wb = stepMeta.getExtension().equalsIgnoreCase( "xlsx" ) ? new XSSFWorkbook() : new HSSFWorkbook();
  stepData.sheet = stepData.wb.createSheet();
  stepData.file = null;
  stepData.clearStyleCache( numOfFields );

  // we avoid reading template file from disk
  // so set beforehand cells with custom style and formatting
  DataFormat format = stepData.wb.createDataFormat();
  Row xlsRow = stepData.sheet.createRow( 0 );

  // Cell F1 has custom style applied, used as template
  Cell cell = xlsRow.createCell( 5 );
  CellStyle cellStyle = stepData.wb.createCellStyle();
  cellStyle.setBorderRight( BorderStyle.THICK );
  cellStyle.setFillPattern( FillPatternType.FINE_DOTS );
  cell.setCellStyle( cellStyle );

  // Cell G1 has same style, but also a custom data format
  cellStyle = stepData.wb.createCellStyle();
  cellStyle.cloneStyleFrom( cell.getCellStyle() );
  cell = xlsRow.createCell( 6 );
  cellStyle.setDataFormat( format.getFormat( "##0,000.0" ) );
  cell.setCellStyle( cellStyle );
}
 

/**
 * Not supported
 */
@Override
public DataFormat createDataFormat() {
  throw new UnsupportedOperationException();
}
 

/**
 * Test applying Format and Style from cell (from a template) when writing fields
 *
 * @param fileType
 * @throws Exception
 */
private void testStyleFormat( String fileType ) throws Exception {
  setupStepMock( fileType );
  createStepMeta( fileType );
  createStepData( fileType );
  step.init( stepMeta, stepData );

  // We do not run transformation or executing the whole step
  // instead we just execute ExcelWriterStepData.writeNextLine() to write to Excel workbook object
  // Values are written in A2:D2 and A3:D3 rows
  List<Object[]> rows = createRowData();
  for ( int i = 0; i < rows.size(); i++ ) {
    step.writeNextLine( rows.get( i ) );
  }

  // Custom styles are loaded from G1 cell
  Row xlsRow = stepData.sheet.getRow( 0 );
  Cell baseCell = xlsRow.getCell( 6 );
  CellStyle baseCellStyle = baseCell.getCellStyle();
  DataFormat format = stepData.wb.createDataFormat();

  // Check style of the exported values in A3:D3
  xlsRow = stepData.sheet.getRow( 2 );
  for ( int i = 0; i < stepData.inputRowMeta.size(); i++ ) {
    Cell cell = xlsRow.getCell( i );
    CellStyle cellStyle = cell.getCellStyle();

    if ( i > 0 ) {
      assertEquals( cellStyle.getBorderRight(), baseCellStyle.getBorderRight() );
      assertEquals( cellStyle.getFillPattern(), baseCellStyle.getFillPattern() );
    } else {
      // cell A2/A3 has no custom style
      assertFalse( cellStyle.getBorderRight() == baseCellStyle.getBorderRight() );
      assertFalse( cellStyle.getFillPattern() == baseCellStyle.getFillPattern() );
    }

    if ( i != 1 ) {
      assertEquals( format.getFormat( cellStyle.getDataFormat() ), "0.00000" );
    } else {
      // cell B2/B3 use different format from the custom style
      assertEquals( format.getFormat( cellStyle.getDataFormat() ), "##0,000.0" );
    }
  }
}
 

/**
 * Test applying Format and Style from cell (from a template) when writing fields
 *
 * @param fileType
 * @throws Exception
 */
private void testStyleFormat( String fileType ) throws Exception {
  setupTransformMock( fileType );
  createTransformMeta( fileType );
  createTransformData( fileType );
  transform.init();

  // We do not run pipeline or executing the whole transform
  // instead we just execute ExcelWriterData.writeNextLine() to write to Excel workbook object
  // Values are written in A2:D2 and A3:D3 rows
  List<Object[]> rows = createRowData();
  for ( int i = 0; i < rows.size(); i++ ) {
    transform.writeNextLine( rows.get( i ) );
  }

  // Custom styles are loaded from G1 cell
  Row xlsRow = transformData.sheet.getRow( 0 );
  Cell baseCell = xlsRow.getCell( 6 );
  CellStyle baseCellStyle = baseCell.getCellStyle();
  DataFormat format = transformData.wb.createDataFormat();

  // Check style of the exported values in A3:D3
  xlsRow = transformData.sheet.getRow( 2 );
  for ( int i = 0; i < transformData.inputRowMeta.size(); i++ ) {
    Cell cell = xlsRow.getCell( i );
    CellStyle cellStyle = cell.getCellStyle();

    if ( i > 0 ) {
      assertEquals( cellStyle.getBorderRight(), baseCellStyle.getBorderRight() );
      assertEquals( cellStyle.getFillPattern(), baseCellStyle.getFillPattern() );
    } else {
      // cell A2/A3 has no custom style
      assertFalse( cellStyle.getBorderRight() == baseCellStyle.getBorderRight() );
      assertFalse( cellStyle.getFillPattern() == baseCellStyle.getFillPattern() );
    }

    if ( i != 1 ) {
      assertEquals( format.getFormat( cellStyle.getDataFormat() ), "0.00000" );
    } else {
      // cell B2/B3 use different format from the custom style
      assertEquals( format.getFormat( cellStyle.getDataFormat() ), "##0,000.0" );
    }
  }
}
 

private void createDateFormatStyle() {
  dateStyle = wb.createCellStyle();
  DataFormat df = wb.createDataFormat();
  dateStyle.setDataFormat(df.getFormat(DEFAULT_DATE_FORMAT));
}
 

/**
 * Not supported
 */
@Override
public DataFormat createDataFormat() {
    throw new UnsupportedOperationException();
}
 

public CellStyle getNumberFormat(int j, Map<Integer, CellStyle> decimalFormats, Sheet sheet, CreationHelper createHelper, CellType celltype) {

		int mapPosition = j;

		if (celltype.equals(CellType.TOTAL)) {
			mapPosition = j + 90000;
		} else if (celltype.equals(CellType.SUBTOTAL)) {
			mapPosition = j + 80000;
		} else if (celltype.equals(CellType.CF)) {
			mapPosition = j + 60000;
		}

		if (decimalFormats.get(mapPosition) != null)
			return decimalFormats.get(mapPosition);

		if (celltype.equals(CellType.CF)) {
			j = this.getCalculatedFieldDecimals();
		}

		String decimals = "";

		for (int i = 0; i < j; i++) {
			decimals += "0";
		}

		CellStyle cellStyle = this.buildDataCellStyle(sheet);
		DataFormat df = createHelper.createDataFormat();
		String format = "#,##0";
		if (decimals.length() > 0) {
			format += "." + decimals;
		}
		cellStyle.setDataFormat(df.getFormat(format));

		if (celltype.equals(CellType.TOTAL)) {
			cellStyle.setFillForegroundColor(IndexedColors.GREY_40_PERCENT.getIndex());
		}
		if (celltype.equals(CellType.CF)) {
			cellStyle.setFillForegroundColor(IndexedColors.DARK_YELLOW.getIndex());
		}
		if (celltype.equals(CellType.SUBTOTAL)) {
			cellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
		}

		decimalFormats.put(mapPosition, cellStyle);
		return cellStyle;
	}
 

private static void copyCell(Cell oldCell, Cell newCell, List<CellStyle> styleList) {
	if (styleList != null) {
		if (oldCell.getSheet().getWorkbook() == newCell.getSheet().getWorkbook()) {
			newCell.setCellStyle(oldCell.getCellStyle());
		} else {
			DataFormat newDataFormat = newCell.getSheet().getWorkbook().createDataFormat();

			CellStyle newCellStyle = getSameCellStyle(oldCell, newCell, styleList);
			if (newCellStyle == null) {

				Font oldFont = oldCell.getSheet().getWorkbook().getFontAt(oldCell.getCellStyle().getFontIndex());

				Font newFont = newCell.getSheet().getWorkbook().findFont(oldFont.getBoldweight(), oldFont.getColor(), oldFont.getFontHeight(),
						oldFont.getFontName(), oldFont.getItalic(), oldFont.getStrikeout(), oldFont.getTypeOffset(), oldFont.getUnderline());
				if (newFont == null) {
					newFont = newCell.getSheet().getWorkbook().createFont();
					newFont.setBoldweight(oldFont.getBoldweight());
					newFont.setColor(oldFont.getColor());
					newFont.setFontHeight(oldFont.getFontHeight());
					newFont.setFontName(oldFont.getFontName());
					newFont.setItalic(oldFont.getItalic());
					newFont.setStrikeout(oldFont.getStrikeout());
					newFont.setTypeOffset(oldFont.getTypeOffset());
					newFont.setUnderline(oldFont.getUnderline());
					newFont.setCharSet(oldFont.getCharSet());
				}

				short newFormat = newDataFormat.getFormat(oldCell.getCellStyle().getDataFormatString());
				newCellStyle = newCell.getSheet().getWorkbook().createCellStyle();
				newCellStyle.setFont(newFont);
				newCellStyle.setDataFormat(newFormat);

				newCellStyle.setAlignment(oldCell.getCellStyle().getAlignment());
				newCellStyle.setHidden(oldCell.getCellStyle().getHidden());
				newCellStyle.setLocked(oldCell.getCellStyle().getLocked());
				newCellStyle.setWrapText(oldCell.getCellStyle().getWrapText());
				newCellStyle.setBorderBottom(oldCell.getCellStyle().getBorderBottom());
				newCellStyle.setBorderLeft(oldCell.getCellStyle().getBorderLeft());
				newCellStyle.setBorderRight(oldCell.getCellStyle().getBorderRight());
				newCellStyle.setBorderTop(oldCell.getCellStyle().getBorderTop());
				newCellStyle.setBottomBorderColor(oldCell.getCellStyle().getBottomBorderColor());
				newCellStyle.setFillBackgroundColor(oldCell.getCellStyle().getFillBackgroundColor());
				newCellStyle.setFillForegroundColor(oldCell.getCellStyle().getFillForegroundColor());
				newCellStyle.setFillPattern(oldCell.getCellStyle().getFillPattern());
				newCellStyle.setIndention(oldCell.getCellStyle().getIndention());
				newCellStyle.setLeftBorderColor(oldCell.getCellStyle().getLeftBorderColor());
				newCellStyle.setRightBorderColor(oldCell.getCellStyle().getRightBorderColor());
				newCellStyle.setRotation(oldCell.getCellStyle().getRotation());
				newCellStyle.setTopBorderColor(oldCell.getCellStyle().getTopBorderColor());
				newCellStyle.setVerticalAlignment(oldCell.getCellStyle().getVerticalAlignment());

				styleList.add(newCellStyle);
			}
			newCell.setCellStyle(newCellStyle);
		}
	}
	switch (oldCell.getCellType()) {
	case Cell.CELL_TYPE_STRING:
		newCell.setCellValue(oldCell.getStringCellValue());
		break;
	case Cell.CELL_TYPE_NUMERIC:
		newCell.setCellValue(oldCell.getNumericCellValue());
		break;
	case Cell.CELL_TYPE_BLANK:
		newCell.setCellType(Cell.CELL_TYPE_BLANK);
		break;
	case Cell.CELL_TYPE_BOOLEAN:
		newCell.setCellValue(oldCell.getBooleanCellValue());
		break;
	case Cell.CELL_TYPE_ERROR:
		newCell.setCellErrorValue(oldCell.getErrorCellValue());
		break;
	case Cell.CELL_TYPE_FORMULA:
		newCell.setCellFormula(oldCell.getCellFormula());
		formulaInfoList.add(new FormulaInfo(oldCell.getSheet().getSheetName(), oldCell.getRowIndex(), oldCell.getColumnIndex(), oldCell.getCellFormula()));
		break;
	default:
		break;
	}
}
 

/**
 * Setup the data necessary for Excel Writer transform
 *
 * @param fileType
 * @throws HopException
 */
private void createTransformData( String fileType ) throws HopException {
  data = new ExcelWriterTransformData();
  data.inputRowMeta = inputRowMeta.clone();
  data.outputRowMeta = inputRowMeta.clone();

  // we don't run pipeline so ExcelWriterTransform.processRow() doesn't get executed
  // we populate the ExcelWriterTransformData with bare minimum required values
  CellReference cellRef = new CellReference( meta.getStartingCell() );
  data.startingRow = cellRef.getRow();
  data.startingCol = cellRef.getCol();
  data.posX = data.startingCol;
  data.posY = data.startingRow;

  int numOfFields = data.inputRowMeta.size();
  data.fieldnrs = new int[ numOfFields ];
  data.linkfieldnrs = new int[ numOfFields ];
  data.commentfieldnrs = new int[ numOfFields ];
  for ( int i = 0; i < numOfFields; i++ ) {
    data.fieldnrs[ i ] = i;
    data.linkfieldnrs[ i ] = -1;
    data.commentfieldnrs[ i ] = -1;
  }

  // we avoid reading/writing Excel files, so ExcelWriterTransform.prepareNextOutputFile() doesn't get executed
  // create Excel workbook object
  data.wb = meta.getExtension().equalsIgnoreCase( "xlsx" ) ? new XSSFWorkbook() : new HSSFWorkbook();
  data.sheet = data.wb.createSheet();
  data.file = null;
  data.clearStyleCache( numOfFields );

  // we avoid reading template file from disk
  // so set beforehand cells with custom style and formatting
  DataFormat format = data.wb.createDataFormat();
  Row xlsRow = data.sheet.createRow( 0 );

  // Cell F1 has custom style applied, used as template
  Cell cell = xlsRow.createCell( 5 );
  CellStyle cellStyle = data.wb.createCellStyle();
  cellStyle.setBorderRight( BorderStyle.THICK );
  cellStyle.setFillPattern( FillPatternType.FINE_DOTS );
  cell.setCellStyle( cellStyle );

  // Cell G1 has same style, but also a custom data format
  cellStyle = data.wb.createCellStyle();
  cellStyle.cloneStyleFrom( cell.getCellStyle() );
  cell = xlsRow.createCell( 6 );
  cellStyle.setDataFormat( format.getFormat( "##0,000.0" ) );
  cell.setCellStyle( cellStyle );
}
 

/**
 * Test applying Format and Style from cell (from a template) when writing fields
 *
 * @param fileType
 * @throws Exception
 */
private void testStyleFormat( String fileType ) throws Exception {
  setupInputOutput( fileType );
  createTransformMeta( fileType );
  createTransformData( fileType );
  setupTransformMock( fileType );
  transform.init();

  // We do not run pipeline or executing the whole transform
  // instead we just execute ExcelWriterTransformData.writeNextLine() to write to Excel workbook object
  // Values are written in A2:D2 and A3:D3 rows
  List<Object[]> rows = createRowData();
  for ( int i = 0; i < rows.size(); i++ ) {
    transform.writeNextLine( rows.get( i ) );
  }

  // Custom styles are loaded from G1 cell
  Row xlsRow = data.sheet.getRow( 0 );
  Cell baseCell = xlsRow.getCell( 6 );
  CellStyle baseCellStyle = baseCell.getCellStyle();
  DataFormat format = data.wb.createDataFormat();

  // Check style of the exported values in A3:D3
  xlsRow = data.sheet.getRow( 2 );
  for ( int i = 0; i < data.inputRowMeta.size(); i++ ) {
    Cell cell = xlsRow.getCell( i );
    CellStyle cellStyle = cell.getCellStyle();

    if ( i > 0 ) {
      assertEquals( cellStyle.getBorderRight(), baseCellStyle.getBorderRight() );
      assertEquals( cellStyle.getFillPattern(), baseCellStyle.getFillPattern() );
    } else {
      // cell A2/A3 has no custom style
      assertFalse( cellStyle.getBorderRight() == baseCellStyle.getBorderRight() );
      assertFalse( cellStyle.getFillPattern() == baseCellStyle.getFillPattern() );
    }

    if ( i != 1 ) {
      assertEquals( format.getFormat( cellStyle.getDataFormat() ), "0.00000" );
    } else {
      // cell B2/B3 use different format from the custom style
      assertEquals( format.getFormat( cellStyle.getDataFormat() ), "##0,000.0" );
    }
  }
}
 

/**
 * Setup the data necessary for Excel Writer transform
 *
 * @param fileType
 * @throws HopException
 */
private void createTransformData( String fileType ) throws HopException {
  transformData = new ExcelWriterData();
  transformData.inputRowMeta = transform.getInputRowMeta().clone();
  transformData.outputRowMeta = transform.getInputRowMeta().clone();

  // we don't run pipeline so ExcelWriter.processRow() doesn't get executed
  // we populate the ExcelWriterData with bare minimum required values
  //
  CellReference cellRef = new CellReference( transformMeta.getStartingCell() );
  transformData.startingRow = cellRef.getRow();
  transformData.startingCol = cellRef.getCol();
  transformData.posX = transformData.startingCol;
  transformData.posY = transformData.startingRow;

  int numOfFields = transformData.inputRowMeta.size();
  transformData.fieldnrs = new int[ numOfFields ];
  transformData.linkfieldnrs = new int[ numOfFields ];
  transformData.commentfieldnrs = new int[ numOfFields ];
  for ( int i = 0; i < numOfFields; i++ ) {
    transformData.fieldnrs[ i ] = i;
    transformData.linkfieldnrs[ i ] = -1;
    transformData.commentfieldnrs[ i ] = -1;
  }

  // we avoid reading/writing Excel files, so ExcelWriter.prepareNextOutputFile() doesn't get executed
  // create Excel workbook object
  //
  transformData.wb = transformMeta.getExtension().equalsIgnoreCase( "xlsx" ) ? new XSSFWorkbook() : new HSSFWorkbook();
  transformData.sheet = transformData.wb.createSheet();
  transformData.file = null;
  transformData.clearStyleCache( numOfFields );

  // we avoid reading template file from disk
  // so set beforehand cells with custom style and formatting
  DataFormat format = transformData.wb.createDataFormat();
  Row xlsRow = transformData.sheet.createRow( 0 );

  // Cell F1 has custom style applied, used as template
  Cell cell = xlsRow.createCell( 5 );
  CellStyle cellStyle = transformData.wb.createCellStyle();
  cellStyle.setBorderRight( BorderStyle.THICK );
  cellStyle.setFillPattern( FillPatternType.FINE_DOTS );
  cell.setCellStyle( cellStyle );

  // Cell G1 has same style, but also a custom data format
  cellStyle = transformData.wb.createCellStyle();
  cellStyle.cloneStyleFrom( cell.getCellStyle() );
  cell = xlsRow.createCell( 6 );
  cellStyle.setDataFormat( format.getFormat( "##0,000.0" ) );
  cell.setCellStyle( cellStyle );
}