jxl.write.WritableCellFormat Java Examples

public static void ExportForwardLookupsToXLS(String filename, List<Object> data) throws IOException {
    try {
        WritableFont titleFont = new WritableFont(WritableFont.ARIAL, 10, WritableFont.BOLD, true);
        WritableCellFormat titleformat = new WritableCellFormat(titleFont);
        WritableWorkbook workbook = Workbook.createWorkbook(new File(filename));
        WritableSheet sheet = workbook.createSheet("Forward lookups", 0);
        sheet.addCell(new Label(0, 0, "Domain name", titleformat));
        sheet.addCell(new Label(1, 0, "Host name", titleformat));
        sheet.addCell(new Label(2, 0, "IP address", titleformat));
        sheet.addCell(new Label(3, 0, "Type", titleformat));
        int nextRow = 1;
        Iterator i = data.iterator();
        while (i.hasNext()) {
            ForwardLookupResult res = (ForwardLookupResult) i.next();
            sheet.addCell(new Label(0, nextRow, res.getDomainName()));
            sheet.addCell(new Label(1, nextRow, res.getHostName()));
            sheet.addCell(new Label(2, nextRow, res.getIpAddress()));
            sheet.addCell(new Label(3, nextRow, res.getLookupType()));
            nextRow++;
        }
        workbook.write();
        workbook.close();
    } catch (WriteException ex) {
        Logger.getLogger("resultExport.ExportForwardLookupsToXLS").log(Level.SEVERE, null, ex);
    }
}
 

public static void ExportTLDToXLS(String filename, ArrayList<Object> data) throws IOException {
    try {
        WritableFont titleFont = new WritableFont(WritableFont.ARIAL, 10, WritableFont.BOLD, true);
        WritableCellFormat titleformat = new WritableCellFormat(titleFont);
        WritableWorkbook workbook = Workbook.createWorkbook(new File(filename));
        WritableSheet sheet = workbook.createSheet("TLD Expand", 0);
        sheet.addCell(new Label(0, 0, "Domain name", titleformat));
        sheet.addCell(new Label(1, 0, "Name server", titleformat));
        sheet.addCell(new Label(2, 0, "Admin name", titleformat));
        sheet.addCell(new Label(3, 0, "Registrant", titleformat));
        int nextRow = 1;
        Iterator i = data.iterator();
        while (i.hasNext()) {
            DomainResult res = (DomainResult) i.next();
            sheet.addCell(new Label(0, nextRow, res.getDomainName()));
            sheet.addCell(new Label(1, nextRow, res.getNameServer()));
            sheet.addCell(new Label(2, nextRow, res.getAdminName()));
            sheet.addCell(new Label(3, nextRow, res.getRegistrant()));
            nextRow++;
        }
        workbook.write();
        workbook.close();
    } catch (WriteException ex) {
        Logger.getLogger("resultExport.ExportForwardLookupsToXLS").log(Level.SEVERE, null, ex);
    }
}
 

public static void ExportCertToXLS(String filename, ArrayList<Object> data) throws IOException {
    try {
        WritableFont titleFont = new WritableFont(WritableFont.ARIAL, 10, WritableFont.BOLD, true);
        WritableCellFormat titleformat = new WritableCellFormat(titleFont);
        WritableWorkbook workbook = Workbook.createWorkbook(new File(filename));
        WritableSheet sheet = workbook.createSheet("SSLCert CN", 0);
        sheet.addCell(new Label(0, 0, "IP address", titleformat));
        sheet.addCell(new Label(1, 0, "Host name", titleformat));
        sheet.addCell(new Label(2, 0, "Domain name", titleformat));
        int nextRow = 1;
        Iterator i = data.iterator();
        while (i.hasNext()) {
            CertResult res = (CertResult) i.next();
            sheet.addCell(new Label(0, nextRow, res.getIpAddress()));
            sheet.addCell(new Label(1, nextRow, res.getHostName()));
            sheet.addCell(new Label(2, nextRow, res.getDomainName()));
            nextRow++;
        }
        workbook.write();
        workbook.close();
    } catch (WriteException ex) {
        Logger.getLogger("resultExport.ExportForwardLookupsToXLS").log(Level.SEVERE, null, ex);
    }
}
 

/**
 * Adds the cell.
 *
 * @param writablesheet the writablesheet
 * @param issueDetail the issue detail
 * @param cellFormat the cell format
 * @param columnIndex the column index
 * @param columns the columns
 * @throws WriteException the write exception
 */
private void addCell(WritableSheet writablesheet, JsonObject issueDetail, WritableCellFormat cellFormat,
        int columnIndex,List<String>columns) throws WriteException {
    int rowIndex = 0;

    for (String clm : columns) {
        if (issueDetail.has(clm)) {
            if (issueDetail.get(clm).isJsonNull()) {
                writablesheet.addCell(new Label(rowIndex++, 1 + columnIndex, "No Data", cellFormat));
            } else {
                writablesheet.addCell(new Label(rowIndex++, 1 + columnIndex, issueDetail.get(clm).getAsString(),
                        cellFormat));
            }

        }
    }
}
 

/**
 * Format writable sheet.
 *
 * @param writablesheet the writable sheet
 * @param columns the columns
 * @throws WriteException the write exception
 */
private void formatWritableSheet(WritableSheet writablesheet,List<String>columns) throws WriteException {
    WritableFont cellFonts = new WritableFont(WritableFont.createFont("Calibri"), ELEVEN, WritableFont.BOLD, false,
            UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.BLACK);
    WritableCellFormat cellFormats = new WritableCellFormat(cellFonts);
    cellFormats.setBorder(Border.ALL, BorderLineStyle.THIN);
    cellFormats.setBackground(Colour.WHITE);
    int labelIndex = 0;
    for (String clm : columns) {
        writablesheet.addCell(new Label(labelIndex, 0, clm.replaceAll("_", ""), cellFormats));
        CellView cell = writablesheet.getColumnView(labelIndex);
        cell.setAutosize(true);
        writablesheet.setColumnView(labelIndex, cell);
        labelIndex++;
    }
}
 

public static void ExportReverseToXLS(String filename, ArrayList<Object> data) throws IOException {
    try {
        WritableFont titleFont = new WritableFont(WritableFont.ARIAL, 10, WritableFont.BOLD, true);
        WritableCellFormat titleformat = new WritableCellFormat(titleFont);
        WritableWorkbook workbook = Workbook.createWorkbook(new File(filename));
        WritableSheet sheet = workbook.createSheet("Bing IP search", 0);
        sheet.addCell(new Label(0, 0, "IP address", titleformat));
        sheet.addCell(new Label(1, 0, "Domain name", titleformat));
        sheet.addCell(new Label(2, 0, "Host name", titleformat));
        int nextRow = 1;
        Iterator i = data.iterator();
        while (i.hasNext()) {
            ReverseLookupResult res = (ReverseLookupResult) i.next();
            sheet.addCell(new Label(0, nextRow, res.getIpAddress()));
            sheet.addCell(new Label(1, nextRow, res.getDomainName()));
            sheet.addCell(new Label(2, nextRow, res.getHostName()));
            nextRow++;
        }
        workbook.write();
        workbook.close();
    } catch (WriteException ex) {
        Logger.getLogger("resultExport.ExportReverseToXLS").log(Level.SEVERE, null, ex);
    }
}
 

private void writeFieldRow(WritableSheet spreadSheet, HashMap<String, Object> fieldRow, int maxIndexedColumnIndex, int r, ExcelCellFormat f,
		HashMap<String, WritableCellFormat> cellFormats) throws Exception {
	if (fieldRow != null && distinctColumnNames != null && distinctColumnNames.size() > 0) {
		int c = maxIndexedColumnIndex;
		for (int d = 0; d < distinctColumnNames.size(); d++) {
			String columnName = distinctColumnNames.get(d);
			Object fieldValue = fieldRow.get(columnName);
			Integer columnIndex = getColumnIndex(columnName);
			if (columnIndex == null) {
				c++;
				columnIndex = c;
			}
			if (fieldValue != null) {
				ExcelUtil.writeCell(spreadSheet, columnIndex.intValue(), r, fieldValue.getClass(), fieldValue, f, cellFormats);
			} else {
				ExcelUtil.writeCell(spreadSheet, columnIndex.intValue(), r, String.class, null, f, cellFormats);
			}
		}
	}
}
 

public static WritableCellFormat getHeader() {
	WritableFont font = new WritableFont(WritableFont.TIMES, 10,
			WritableFont.BOLD);// 定义字体
	try {
		font.setColour(Colour.BLUE);// 蓝色字体
	} catch (WriteException e1) {
		e1.printStackTrace();
	}
	WritableCellFormat format = new WritableCellFormat(font);
	try {
		format.setAlignment(jxl.format.Alignment.CENTRE);// 左右居中
		format.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);// 上下居中
		// format.setBorder(Border.ALL, BorderLineStyle.THIN,
		// Colour.BLACK);// 黑色边框
		// format.setBackground(Colour.YELLOW);// 黄色背景
	} catch (WriteException e) {
		e.printStackTrace();
	}
	return format;
}
 

/**
 * Prepares cell format depending on field format and locale
 * 
 * @param field
 * @return
 */
@SuppressWarnings("deprecation")
private static WritableCellFormat getCellFormat(DataFieldMetadata field){
	if (!(field.getDataType().isNumeric() || field.getDataType() == DataFieldType.DATE || field.getDataType() == DataFieldType.DATETIME)){
		return null;
	}
	String format = field.getFormat();
	if (field.getDataType().isNumeric()) {
		return format != null ? new WritableCellFormat(new NumberFormat(format)) : null;
	}
	//DateDataField
	if (format != null) {
		return new WritableCellFormat(new DateFormat(format));
	}
	//format is null
	if (field.getLocaleStr() != null) {
		format = ((SimpleDateFormat) java.text.DateFormat.getDateInstance(java.text.DateFormat.DEFAULT,
						MiscUtils.createLocale(field.getLocaleStr()))).toPattern();
	}else{
		format = new SimpleDateFormat().toPattern();
	}
	return new WritableCellFormat(new DateFormat(format));
}
 

public static WritableCellFormat getAlignment( int transformValue, WritableCellFormat format ) throws WriteException {
  if ( transformValue != ExcelOutputMeta.FONT_ALIGNMENT_LEFT ) {
    switch ( transformValue ) {
      case ExcelOutputMeta.FONT_ALIGNMENT_RIGHT:
        format.setAlignment( jxl.format.Alignment.RIGHT );
        break;
      case ExcelOutputMeta.FONT_ALIGNMENT_CENTER:
        format.setAlignment( jxl.format.Alignment.CENTRE );
        break;
      case ExcelOutputMeta.FONT_ALIGNMENT_FILL:
        format.setAlignment( jxl.format.Alignment.FILL );
        break;
      case ExcelOutputMeta.FONT_ALIGNMENT_GENERAL:
        format.setAlignment( jxl.format.Alignment.GENERAL );
        break;
      case ExcelOutputMeta.FONT_ALIGNMENT_JUSTIFY:
        format.setAlignment( jxl.format.Alignment.JUSTIFY );
        break;
      default:
        break;
    }
  }
  return format;
}
 

public static WritableCellFormat getAlignment( int stepValue, WritableCellFormat format ) throws WriteException {
  if ( stepValue != ExcelOutputMeta.FONT_ALIGNMENT_LEFT ) {
    switch ( stepValue ) {
      case ExcelOutputMeta.FONT_ALIGNMENT_RIGHT:
        format.setAlignment( jxl.format.Alignment.RIGHT );
        break;
      case ExcelOutputMeta.FONT_ALIGNMENT_CENTER:
        format.setAlignment( jxl.format.Alignment.CENTRE );
        break;
      case ExcelOutputMeta.FONT_ALIGNMENT_FILL:
        format.setAlignment( jxl.format.Alignment.FILL );
        break;
      case ExcelOutputMeta.FONT_ALIGNMENT_GENERAL:
        format.setAlignment( jxl.format.Alignment.GENERAL );
        break;
      case ExcelOutputMeta.FONT_ALIGNMENT_JUSTIFY:
        format.setAlignment( jxl.format.Alignment.JUSTIFY );
        break;
      default:
        break;
    }
  }
  return format;
}
 

public ExcelOutputData() {
  super();

  formats = new Hashtable<String, WritableCellFormat>();
  oneFileOpened = false;
  file = null;
  realSheetname = null;
  headerWrote = false;
}
 

public static WritableCellFormat getOrientation( int stepValue, WritableCellFormat cellFormat ) throws WriteException {
  if ( stepValue != ExcelOutputMeta.FONT_ORIENTATION_HORIZONTAL ) {
    switch ( stepValue ) {
      case ExcelOutputMeta.FONT_ORIENTATION_MINUS_45:
        cellFormat.setOrientation( jxl.format.Orientation.MINUS_45 );
        break;
      case ExcelOutputMeta.FONT_ORIENTATION_MINUS_90:
        cellFormat.setOrientation( jxl.format.Orientation.MINUS_90 );
        break;
      case ExcelOutputMeta.FONT_ORIENTATION_PLUS_45:
        cellFormat.setOrientation( jxl.format.Orientation.PLUS_45 );
        break;
      case ExcelOutputMeta.FONT_ORIENTATION_PLUS_90:
        cellFormat.setOrientation( jxl.format.Orientation.PLUS_90 );
        break;
      case ExcelOutputMeta.FONT_ORIENTATION_STACKED:
        cellFormat.setOrientation( jxl.format.Orientation.STACKED );
        break;
      case ExcelOutputMeta.FONT_ORIENTATION_VERTICAL:
        cellFormat.setOrientation( jxl.format.Orientation.VERTICAL );
        break;
      default:
        break;
    }
  }
  return cellFormat;
}
 

private void createLabel(WritableSheet sheet)
        throws WriteException {
    // Create a times font
    WritableFont times10pt = new WritableFont(WritableFont.TIMES, 10);
    // Define the cell format
    times = new WritableCellFormat(times10pt);
    // Lets automatically wrap the cells
    times.setWrap(true);

    // create create a bold font with unterlines
    WritableFont times10ptBoldUnderline = new WritableFont(WritableFont.TIMES, 10, WritableFont.BOLD, false,
            UnderlineStyle.SINGLE);
    timesBoldUnderline = new WritableCellFormat(times10ptBoldUnderline);
    // Lets automatically wrap the cells
    timesBoldUnderline.setWrap(true);

    CellView cv = new CellView();
    cv.setFormat(times);
    cv.setFormat(timesBoldUnderline);
    cv.setAutosize(true);

    // Write a few headers
    addCaption(sheet, 0, 0, "Writer");
    addCaption(sheet, 1, 0, "Date");
    addCaption(sheet, 2, 0, "Guide");
    addCaption(sheet, 3, 0, "Description");
    addCaption(sheet, 4, 0, "Status");
}
 

public static String createTempFile(String[] names, String title, List<String[]> objects) throws Exception {
	File excelFile = File.createTempFile(System.currentTimeMillis() + "", ".xls");
	WritableWorkbook wtwb = Workbook.createWorkbook(excelFile);
	WritableSheet sheet = wtwb.createSheet(title, 0);
	sheet.getSettings().setDefaultColumnWidth(20);
	WritableFont wfont = new WritableFont(WritableFont.createFont("楷书"), 15);
	WritableCellFormat format = new WritableCellFormat(wfont);
	WritableFont wfc = new WritableFont(WritableFont.ARIAL, 20,
			WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE,
			jxl.format.Colour.BLACK);
	WritableCellFormat wcfFC = new WritableCellFormat(wfc);
	wcfFC.setAlignment(Alignment.CENTRE);
	wcfFC.setVerticalAlignment(VerticalAlignment.CENTRE);
	// CellView cellView = new CellView();
	// cellView.setAutosize(true); //设置自动大小
	format.setAlignment(Alignment.LEFT);
	format.setVerticalAlignment(VerticalAlignment.TOP);
	sheet.mergeCells(0, 0, names.length - 1, 0);
	sheet.addCell(new Label(0, 0, title, wcfFC));
	int rowNum = 2;
	for (int i = 0; i < names.length; i++) {
		sheet.addCell(new Label(i, 1, names[i], format));
	}
	for (int j = 0; j < objects.size(); j++) {
		String[] obj = objects.get(j);
		for (int h = 0; h < obj.length; h++) {
			sheet.addCell(new Label(h, rowNum, obj[h], format));
		}
		rowNum = rowNum + 1;
	}
	wtwb.write();
	wtwb.close();
	return excelFile.getName();
}
 

public static File exportObjects(String fileName, String[] names,
		String title, List<String[]> objects) throws Exception {
	File excelFile = new File("fileName.xls");
	WritableWorkbook wtwb = Workbook.createWorkbook(excelFile);
	WritableSheet sheet = wtwb.createSheet(title, 0);
	sheet.getSettings().setDefaultColumnWidth(20);
	WritableFont wfont = new WritableFont(WritableFont.createFont("楷书"), 15);
	WritableCellFormat format = new WritableCellFormat(wfont);
	WritableFont wfc = new WritableFont(WritableFont.ARIAL, 20,
			WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE,
			jxl.format.Colour.BLACK);
	WritableCellFormat wcfFC = new WritableCellFormat(wfc);
	wcfFC.setAlignment(Alignment.CENTRE);
	wcfFC.setVerticalAlignment(VerticalAlignment.CENTRE);
	// CellView cellView = new CellView();
	// cellView.setAutosize(true); //设置自动大小
	format.setAlignment(Alignment.LEFT);
	format.setVerticalAlignment(VerticalAlignment.TOP);
	sheet.mergeCells(0, 0, names.length - 1, 0);
	sheet.addCell(new Label(0, 0, title, wcfFC));
	int rowNum = 2;
	for (int i = 0; i < names.length; i++) {
		sheet.addCell(new Label(i, 1, names[i], format));
	}
	for (int j = 0; j < objects.size(); j++) {
		String[] obj = objects.get(j);
		for (int h = 0; h < obj.length; h++) {
			sheet.addCell(new Label(h, rowNum, obj[h], format));
		}
		rowNum = rowNum + 1;

	}
	wtwb.write();
	wtwb.close();
	return excelFile;
}
 

@Test
/**
 * Tests http://jira.pentaho.com/browse/PDI-13487 issue
 */
public void testClosingFile() throws Exception {

  IValueMeta vmi = new ValueMetaString( "new_row" );

  ExcelOutputData data = new ExcelOutputData();
  data.fieldnrs = new int[] { 0 };
  String testColumnName = "testColumnName";
  data.formats.put( testColumnName, new WritableCellFormat() );
  RowMeta rowMetaToBeReturned = Mockito.spy( new RowMeta() );
  rowMetaToBeReturned.addValueMeta( 0, vmi );

  data.previousMeta = rowMetaToBeReturned;
  ExcelOutput excelOutput =
    Mockito.spy( new ExcelOutput( helper.transformMeta, data, 0, helper.pipelineMeta, helper.pipeline ) );
  excelOutput.first = false;

  Object[] row = { new Date() };
  doReturn( row ).when( excelOutput ).getRow();
  doReturn( rowMetaToBeReturned ).when( excelOutput ).getInputRowMeta();
  doReturn( 1L ).when( excelOutput ).getLinesOutput();

  String excelFileFullPath = buildFilePath();
  File excelFile = new File( excelFileFullPath );
  excelFile.deleteOnExit();
  ExcelOutputMeta meta = createTransformMeta( excelFileFullPath, null, true );

  meta.setSplitEvery( 1 );

  excelOutput.init();
  excelOutput.init();
  Assert.assertNull( data.formats.get( testColumnName ) );
}
 

/**
 * @return the format to use for bold cells
 * @throws WriteException if the format could not be created
 */
private WritableCellFormat getBoldFormat() throws WriteException {
  WritableFont boldFont = new WritableFont(WritableFont.ARIAL, 8, WritableFont.BOLD);
  WritableCellFormat boldHeading = new WritableCellFormat(boldFont);
  boldHeading.setBorder(Border.BOTTOM, BorderLineStyle.MEDIUM);
  boldHeading.setVerticalAlignment(VerticalAlignment.CENTRE);
  boldHeading.setBackground(Colour.GRAY_25);


  WritableCellFormat boldFormat = new WritableCellFormat(boldFont);
  boldFormat.setVerticalAlignment(VerticalAlignment.TOP);
  return boldFormat;
}
 

/**
 * Create the index worksheet.
 *
 * <p>This also creates links back to the index in each of the worksheets.</p>
 */
public void createIndex() {
  WritableSheet sheet = workbook.createSheet(spreadsheetifyName("Index"), 0);
  createTitle(sheet, "Index");

  try {
    // Create links for each worksheet, apart from the first sheet which is the
    // index we're currently creating
    final String[] names = workbook.getSheetNames();
    for (int currentSheet = 1; currentSheet < names.length; currentSheet++) {
      // Create the link from the index to the table's worksheet
      WritableHyperlink link = new WritableHyperlink(0, currentSheet - 1 + NUMBER_OF_ROWS_IN_TITLE, names[currentSheet], workbook.getSheet(currentSheet), 0, 0);
      sheet.addHyperlink(link);

      //Add the filename in column B (stored in cell B2 of each sheet)
      String fileName = workbook.getSheet(currentSheet).getCell(1, 1).getContents();
      Label fileNameLabel = new Label(1, currentSheet - 1 + NUMBER_OF_ROWS_IN_TITLE, fileName);
      WritableFont fileNameFont = new WritableFont(WritableFont.ARIAL,10,WritableFont.NO_BOLD,false,UnderlineStyle.NO_UNDERLINE,Colour.BLACK);
      WritableCellFormat fileNameFormat = new WritableCellFormat(fileNameFont);
      fileNameLabel.setCellFormat(fileNameFormat);
      sheet.addCell(fileNameLabel);

      // Create the link back to the index
      link = new WritableHyperlink(0, 1, "Back to index", sheet, 0, currentSheet + NUMBER_OF_ROWS_IN_TITLE - 1);
      workbook.getSheet(currentSheet).addHyperlink(link);
      //Set column A of each sheet to be wide enough to show "Back to index"
      workbook.getSheet(currentSheet).setColumnView(0, 13);
    }

    // Make Column A fairly wide to show tab names and hide column B
    sheet.setColumnView(0, 35);
    sheet.setColumnView(1, 0);

  } catch (Exception e) {
    throw new RuntimeException(e);
  }
}
 

@Test
/**
 * Tests http://jira.pentaho.com/browse/PDI-13487 issue
 */
public void testClosingFile() throws Exception {

  ValueMetaInterface vmi = new ValueMetaString( "new_row" );

  ExcelOutputData data = new ExcelOutputData();
  data.fieldnrs = new int[] { 0 };
  String testColumnName = "testColumnName";
  data.formats.put( testColumnName, new WritableCellFormat() );
  RowMeta rowMetaToBeReturned = Mockito.spy( new RowMeta() );
  rowMetaToBeReturned.addValueMeta( 0, vmi );

  data.previousMeta = rowMetaToBeReturned;
  ExcelOutput excelOutput =
    Mockito.spy( new ExcelOutput( helper.stepMeta, data, 0, helper.transMeta, helper.trans ) );
  excelOutput.first = false;

  Object[] row = { new Date() };
  doReturn( row ).when( excelOutput ).getRow();
  doReturn( rowMetaToBeReturned ).when( excelOutput ).getInputRowMeta();
  doReturn( 1L ).when( excelOutput ).getLinesOutput();

  String excelFileFullPath = buildFilePath();
  File excelFile = new File( excelFileFullPath );
  excelFile.deleteOnExit();
  ExcelOutputMeta meta = createStepMeta( excelFileFullPath, null, true );

  meta.setSplitEvery( 1 );

  excelOutput.init( meta, data );
  excelOutput.processRow( meta, data );
  Assert.assertNull( data.formats.get( testColumnName ) );
}
 

public static void writeHead(WritableSheet spreadSheet, int c, int r, String columnName, ExcelCellFormat f, HashMap<String, WritableCellFormat> cellFormats) throws Exception {
	if (spreadSheet != null) {
		WritableCell cell;
		if (f.isOverrideFormat()) {
			cell = new jxl.write.Label(c, r, columnName, getHeadCellFormat(f, cellFormats));
		} else {
			cell = new jxl.write.Label(c, r, columnName);
		}
		addCell(cell, spreadSheet, c, r, f);
	}
}
 

public ExcelOutputData() {
  super();

  formats = new Hashtable<String, WritableCellFormat>();
  oneFileOpened = false;
  file = null;
  realSheetname = null;
  headerWrote = false;
}
 

public static WritableCellFormat getOrientation( int transformValue, WritableCellFormat cellFormat ) throws WriteException {
  if ( transformValue != ExcelOutputMeta.FONT_ORIENTATION_HORIZONTAL ) {
    switch ( transformValue ) {
      case ExcelOutputMeta.FONT_ORIENTATION_MINUS_45:
        cellFormat.setOrientation( jxl.format.Orientation.MINUS_45 );
        break;
      case ExcelOutputMeta.FONT_ORIENTATION_MINUS_90:
        cellFormat.setOrientation( jxl.format.Orientation.MINUS_90 );
        break;
      case ExcelOutputMeta.FONT_ORIENTATION_PLUS_45:
        cellFormat.setOrientation( jxl.format.Orientation.PLUS_45 );
        break;
      case ExcelOutputMeta.FONT_ORIENTATION_PLUS_90:
        cellFormat.setOrientation( jxl.format.Orientation.PLUS_90 );
        break;
      case ExcelOutputMeta.FONT_ORIENTATION_STACKED:
        cellFormat.setOrientation( jxl.format.Orientation.STACKED );
        break;
      case ExcelOutputMeta.FONT_ORIENTATION_VERTICAL:
        cellFormat.setOrientation( jxl.format.Orientation.VERTICAL );
        break;
      default:
        break;
    }
  }
  return cellFormat;
}
 

@Test
public void testClosingFile() throws Exception {

  IValueMeta vmi = new ValueMetaString( "new_row" );

  ExcelOutputData data = new ExcelOutputData();
  data.fieldnrs = new int[] { 0 };
  String testColumnName = "testColumnName";
  data.formats.put( testColumnName, new WritableCellFormat() );
  RowMeta rowMetaToBeReturned = Mockito.spy( new RowMeta() );
  rowMetaToBeReturned.addValueMeta( 0, vmi );

  String excelFileFullPath = buildFilePath();
  File excelFile = new File( excelFileFullPath );
  excelFile.deleteOnExit();
  ExcelOutputMeta meta = createTransformMeta( excelFileFullPath, null, true );
  meta.setSplitEvery( 1 );


  data.previousMeta = rowMetaToBeReturned;
  ExcelOutput excelOutput = Mockito.spy( new ExcelOutput( helper.transformMeta, meta, data, 0, helper.pipelineMeta, helper.pipeline ) );
  excelOutput.first = false;

  Object[] row = { new Date() };
  doReturn( row ).when( excelOutput ).getRow();
  doReturn( rowMetaToBeReturned ).when( excelOutput ).getInputRowMeta();
  doReturn( 1L ).when( excelOutput ).getLinesOutput();

  excelOutput.init();
  excelOutput.processRow();
  Assert.assertNull( data.formats.get( testColumnName ) );
}
 

/**
 * Adds the writable sheet cells.
 *
 * @param writablesheet the writablesheet
 * @param issueDetails the issue details
 * @param columns the columns
 * @throws WriteException the write exception
 */
private void addWritableSheetCells(WritableSheet writablesheet, JsonArray issueDetails,List<String>columns) throws WriteException {
    WritableFont cellFont = new WritableFont(WritableFont.createFont("Calibri"), TWELVE);
    cellFont.setColour(Colour.BLACK);
    WritableCellFormat cellFormat = new WritableCellFormat(cellFont);
    cellFormat.setBackground(Colour.WHITE);
    cellFormat.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.GRAY_25);
    for (int columnIndex = 0; columnIndex < issueDetails.size(); columnIndex++) {
        JsonObject issueDetail = issueDetails.get(columnIndex).getAsJsonObject();
        addCell(writablesheet, issueDetail, cellFormat, columnIndex,columns);
    }
}
 

public static void appendColumns(ArrayList<VOColumn> columns, Class vo, HashMap<Integer, WritableCellFormat> rowCellFormats, int depth,
		boolean omitFields,
		boolean enumerateReferences,
		boolean enumerateCollections,
		boolean enumerateMaps) throws Exception {
	ArrayList<Accessor> getterChain = new ArrayList<Accessor>();
	getColumns(columns, vo, getterChain, rowCellFormats, depth, omitFields, enumerateReferences, enumerateCollections, enumerateMaps);
}
 

private static void getColumns(ArrayList<VOColumn> columns, Class vo, ArrayList<Accessor> getterChain, HashMap<Integer, WritableCellFormat> rowCellFormats, int depth,
		boolean omitFields,
		boolean enumerateReferences,
		boolean enumerateCollections,
		boolean enumerateMaps) throws Exception {
	appendProperties(org.phoenixctms.ctsms.excel.VOColumn.class, columns, vo, getterChain, rowCellFormats, depth,
			EXCEL_VO_METHOD_TRANSFILTER,
			EXCEL_VO_COLLECTION_VALUES_COMPARATOR,
			omitFields,
			null,
			enumerateReferences,
			enumerateCollections,
			enumerateMaps,
			ExcelUtil.COLUMN_NAME_ASSOCIATION_PATH_SEPARATOR);
}
 

public static ArrayList<VOColumn> getColumns(Class vo, HashMap<Integer, WritableCellFormat> rowCellFormats, int depth,
		boolean omitFields,
		boolean enumerateReferences,
		boolean enumerateCollections,
		boolean enumerateMaps) throws Exception {
	ArrayList<VOColumn> columns = new ArrayList<VOColumn>();
	ArrayList<Accessor> getterChain = new ArrayList<Accessor>();
	getColumns(columns, vo, getterChain, rowCellFormats, depth, omitFields, enumerateReferences, enumerateCollections, enumerateMaps);
	return columns;
}
 

private static WritableCellFormat getRowCellFormat(WritableSheet spreadSheet, int c, int r) {
	if (spreadSheet != null) {
		Cell cell = spreadSheet.getCell(c, r);
		if (cell != null) {
			CellFormat cellFormat = cell.getCellFormat();
			if (cellFormat != null) {
				return new WritableCellFormat(cellFormat);
			}
		}
	}
	return null;
}
 

private static void setBgColor(WritableCellFormat cellFormat, Color bgColor) {
	Colour colour = convertColor(bgColor);
	if (colour != null) {
		try {
			cellFormat.setBackground(colour);
		} catch (WriteException e) {
		}
	}
}