jxl.format.Colour 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 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;
}
 

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

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

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

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

public void writeJExcel() throws IOException, WriteException {
    WritableWorkbook workbook = null;
    try {
        File currDir = new File(".");
        String path = currDir.getAbsolutePath();
        String fileLocation = path.substring(0, path.length() - 1) + "temp.xls";

        workbook = Workbook.createWorkbook(new File(fileLocation));

        WritableSheet sheet = workbook.createSheet("Sheet 1", 0);

        WritableCellFormat headerFormat = new WritableCellFormat();
        WritableFont font = new WritableFont(WritableFont.ARIAL, 16, WritableFont.BOLD);
        headerFormat.setFont(font);
        headerFormat.setBackground(Colour.LIGHT_BLUE);
        headerFormat.setWrap(true);
        Label headerLabel = new Label(0, 0, "Name", headerFormat);
        sheet.setColumnView(0, 60);
        sheet.addCell(headerLabel);

        headerLabel = new Label(1, 0, "Age", headerFormat);
        sheet.setColumnView(0, 40);
        sheet.addCell(headerLabel);

        WritableCellFormat cellFormat = new WritableCellFormat();
        cellFormat.setWrap(true);

        Label cellLabel = new Label(0, 2, "John Smith", cellFormat);
        sheet.addCell(cellLabel);
        Number cellNumber = new Number(1, 2, 20, cellFormat);
        sheet.addCell(cellNumber);
			
        workbook.write();
    } finally {
        if (workbook != null) {
            workbook.close();
        }
    }

}
 

public static Colour convertColor(org.phoenixctms.ctsms.enumeration.Color color) {
	return COLOR_MAPPING.get(color);
}
 

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