Java Code Examples for jxl.write.WritableCellFormat#setVerticalAlignment()

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

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

/**
 * @return the format to use for normal cells
 * @throws WriteException if the format could not be created
 */
private WritableCellFormat getStandardFormat() throws WriteException {
  WritableCellFormat standardFormat = new WritableCellFormat(getStandardFont());
  standardFormat.setVerticalAlignment(VerticalAlignment.TOP);
  return standardFormat;
}
 

/**
 * @param workSheet to add the help to
 * @param table to fetch metadata from
 * @param startRow to start adding rows at
 * @param helpTextRowNumbers - map to insert row numbers for each help field into
 * @return the index of the next row to use
 * @throws WriteException if any of the writes to workSheet failed
 */
private int outputHelp(WritableSheet workSheet, Table table, final int startRow, final Map<String, Integer> helpTextRowNumbers) throws WriteException {
  int currentRow = startRow;

  // Title for the descriptions
  Label dataLabel = new Label(0, currentRow, "Column Descriptions");
  dataLabel.setCellFormat(getBoldFormat());
  workSheet.addCell(dataLabel);
  currentRow++;

  int currentColumn = 0;

  for (Column column : table.columns()) {
    if (!column.getName().equals("id") && !column.getName().equals("version")) {
      // Field name to go with the description
      Label fieldName = new Label(0, currentRow, spreadsheetifyName(column.getName()));
      fieldName.setCellFormat(getBoldFormat());
      workSheet.addCell(fieldName);

      // The type/width
      String typeString = column.getType() + "(" + column.getWidth() + (column.getScale() == 0 ? "" : "," + column.getScale()) + ")";
      Label fieldType = new Label(1, currentRow, typeString);
      fieldType.setCellFormat(getStandardFormat());
      workSheet.addCell(fieldType);

      // The default
      String defaultValue = additionalSchemaData.columnDefaultValue(table, column.getName());
      Label fieldDefault = new Label(2, currentRow, defaultValue);
      fieldDefault.setCellFormat(getStandardFormat());
      workSheet.addCell(fieldDefault);

      // The field documentation
      workSheet.mergeCells(3, currentRow, 12, currentRow);
      String documentation = additionalSchemaData.columnDocumentation(table, column.getName());
      Label documentationLabel = new Label(3, currentRow, documentation);
      WritableCellFormat format = new WritableCellFormat(getStandardFormat());
      format.setWrap(true);
      format.setVerticalAlignment(VerticalAlignment.TOP);
      documentationLabel.setCellFormat(format);
      workSheet.addCell(documentationLabel);

      //If we've exceed the maximum number of columns - then output truncated warnings
      if(currentColumn >= MAX_EXCEL_COLUMNS) {
        Label truncatedWarning = new Label(13, currentRow, "[TRUNCATED]");
        truncatedWarning.setCellFormat(getBoldFormat());
        workSheet.addCell(truncatedWarning);
      }

      // We are aiming for 150px. 1px is 15 Excel "Units"
      workSheet.setRowView(currentRow, 150 * 15);

      // Remember at what row we created the help text for this column
      helpTextRowNumbers.put(column.getName(), currentRow);

      currentRow++;
      currentColumn++;
    }

  }

  // Group all the help rows together
  workSheet.setRowGroup(startRow + 1, currentRow - 1, true);

  // Some extra blank space for neatness
  currentRow++;

  return currentRow;
}
 

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