jxl.write.WritableFont 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 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);
    }
}
 

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

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

public static FontName getFontName( int stepValue ) {

    FontName headerFontName = WritableFont.ARIAL;
    switch ( stepValue ) {
      case ExcelOutputMeta.FONT_NAME_COURIER:
        headerFontName = WritableFont.COURIER;
        break;
      case ExcelOutputMeta.FONT_NAME_TAHOMA:
        headerFontName = WritableFont.TAHOMA;
        break;
      case ExcelOutputMeta.FONT_NAME_TIMES:
        headerFontName = WritableFont.TIMES;
        break;
      default:
        break;
    }

    return headerFontName;
  }
 

public static FontName getFontName( int transformValue ) {

    FontName headerFontName = WritableFont.ARIAL;
    switch ( transformValue ) {
      case ExcelOutputMeta.FONT_NAME_COURIER:
        headerFontName = WritableFont.COURIER;
        break;
      case ExcelOutputMeta.FONT_NAME_TAHOMA:
        headerFontName = WritableFont.TAHOMA;
        break;
      case ExcelOutputMeta.FONT_NAME_TIMES:
        headerFontName = WritableFont.TIMES;
        break;
      default:
        break;
    }

    return headerFontName;
  }
 

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

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

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

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

public static ExcelCellFormat fromString(String fontName, String fontSize, String bold, String bgColor, String overrideFormat) throws Exception {
	return new ExcelCellFormat(
			jxl.write.WritableFont.createFont(fontName),
			Integer.parseInt(fontSize),
			Boolean.parseBoolean(bold),
			bgColor != null && bgColor.length() > 0 && !CommonUtil.getIsValueNull(bgColor) ? Color.fromString(bgColor) : null,
			Boolean.parseBoolean(overrideFormat));
}
 

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

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

/**
 * @return the standard font to use
 */
private WritableFont getStandardFont() {
  return new WritableFont(WritableFont.ARIAL, 8);
}
 

public static String createCheckRandomTempFile(String[] names, String title, List<String[]> objects,Map<String,String> infoMap) 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("楷书"), 14);

	WritableCellFormat format = new WritableCellFormat(wfont);
	format.setBorder(Border.ALL, BorderLineStyle.THIN);
	format.setAlignment(Alignment.CENTRE);
	format.setVerticalAlignment(VerticalAlignment.CENTRE);

	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.LEFT);
	wcfFC.setVerticalAlignment(VerticalAlignment.CENTRE);

	WritableFont nameWfc = new WritableFont(WritableFont.ARIAL, 14,
			WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE,
			jxl.format.Colour.BLACK);
	WritableCellFormat nameFormat = new WritableCellFormat(nameWfc);
	nameFormat.setBorder(Border.ALL, BorderLineStyle.THIN);
	nameFormat.setAlignment(Alignment.CENTRE);
	nameFormat.setVerticalAlignment(VerticalAlignment.CENTRE);

	WritableCellFormat infoFormat = new WritableCellFormat(wfont);
	infoFormat.setAlignment(Alignment.LEFT);
	infoFormat.setVerticalAlignment(VerticalAlignment.CENTRE);


	sheet.mergeCells(0, 0, names.length - 1, 0);
	sheet.addCell(new Label(0, 0, infoMap.get("title"), wcfFC));

	sheet.addCell(new Label(0, 2, infoMap.get("info"), infoFormat));
	sheet.addCell(new Label(2, 2, infoMap.get("dvrnvr"), infoFormat));
	sheet.addCell(new Label(4, 2, infoMap.get("char"), infoFormat));
	sheet.addCell(new Label(0, 3, infoMap.get("infoPercent"), infoFormat));
	sheet.addCell(new Label(2, 3, infoMap.get("dvrnvrPercent"), infoFormat));
	sheet.addCell(new Label(4, 3, infoMap.get("charPercent"), infoFormat));

	int rowNum = 5;
	for (int i = 0; i < names.length; i++) {
		sheet.addCell(new Label(i, 4, names[i], nameFormat));
	}
	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();
}
 

private void updateFont() {
	font = new WritableFont(fontName, fontSize, bold ? WritableFont.BOLD : WritableFont.NO_BOLD);
}
 

public WritableFont getFont() {
	return font;
}