jxl.format.UnderlineStyle Java Examples

/**
 * 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 UnderlineStyle getUnderlineStyle( int transformValue ) {

    UnderlineStyle underline = UnderlineStyle.NO_UNDERLINE;
    switch ( transformValue ) {
      case ExcelOutputMeta.FONT_UNDERLINE_SINGLE:
        underline = UnderlineStyle.SINGLE;
        break;
      case ExcelOutputMeta.FONT_UNDERLINE_SINGLE_ACCOUNTING:
        underline = UnderlineStyle.SINGLE_ACCOUNTING;
        break;
      case ExcelOutputMeta.FONT_UNDERLINE_DOUBLE:
        underline = UnderlineStyle.DOUBLE;
        break;
      case ExcelOutputMeta.FONT_UNDERLINE_DOUBLE_ACCOUNTING:
        underline = UnderlineStyle.DOUBLE_ACCOUNTING;
        break;
      default:
        break;
    }

    return underline;
  }
 

public static UnderlineStyle getUnderlineStyle( int stepValue ) {

    UnderlineStyle underline = UnderlineStyle.NO_UNDERLINE;
    switch ( stepValue ) {
      case ExcelOutputMeta.FONT_UNDERLINE_SINGLE:
        underline = UnderlineStyle.SINGLE;
        break;
      case ExcelOutputMeta.FONT_UNDERLINE_SINGLE_ACCOUNTING:
        underline = UnderlineStyle.SINGLE_ACCOUNTING;
        break;
      case ExcelOutputMeta.FONT_UNDERLINE_DOUBLE:
        underline = UnderlineStyle.DOUBLE;
        break;
      case ExcelOutputMeta.FONT_UNDERLINE_DOUBLE_ACCOUNTING:
        underline = UnderlineStyle.DOUBLE_ACCOUNTING;
        break;
      default:
        break;
    }

    return underline;
  }
 

/**
 * 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);
  }
}
 

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");
}
 

/**
 * Outputs the data headings row.
 *
 * @param workSheet to add the row to
 * @param table to fetch metadata from
 * @param startRow to add the headings at
 * @param helpTextRowNumbers - the map of column names to row index for each
 *   bit of help text
 * @throws WriteException if any of the writes to workSheet failed
 * @return the row to carry on inserting at
 */
private int outputDataHeadings(WritableSheet workSheet, Table table, final int startRow, final Map<String, Integer> helpTextRowNumbers) throws WriteException {
  int currentRow = startRow;

  int columnNumber = 0;
  final WritableCellFormat columnHeadingFormat = getBoldFormat();

  columnHeadingFormat.setBackground(Colour.VERY_LIGHT_YELLOW);
  WritableFont font = new WritableFont(WritableFont.ARIAL, 8, WritableFont.BOLD);
  font.setColour(Colour.BLUE);
  font.setUnderlineStyle(UnderlineStyle.SINGLE);
  columnHeadingFormat.setFont(font);

  for (Column column : table.columns()) {

    if(columnNumber < MAX_EXCEL_COLUMNS && !column.getName().equals("id") && !column.getName().equals("version")) {
      // Data heading is a link back to the help text
      WritableHyperlink linkToHelp = new WritableHyperlink(
        columnNumber, currentRow,
        spreadsheetifyName(column.getName()),
        workSheet, 0, helpTextRowNumbers.get(column.getName()));
      workSheet.addHyperlink(linkToHelp);
      WritableCell label = workSheet.getWritableCell(columnNumber, currentRow);
      label.setCellFormat(columnHeadingFormat);

      // Update the help text such that it is a link to the heading
      Cell helpCell = workSheet.getCell(0, helpTextRowNumbers.get(column.getName()));
      WritableHyperlink linkFromHelp = new WritableHyperlink(
        0, helpTextRowNumbers.get(column.getName()),
        helpCell.getContents(),
        workSheet, columnNumber, currentRow);
      workSheet.addHyperlink(linkFromHelp);

      columnNumber++;
    }
  }

  currentRow++;

  return currentRow;
}