Java Code Examples for org.apache.poi.ss.usermodel.Workbook#createFont()

The following examples show how to use org.apache.poi.ss.usermodel.Workbook#createFont() . You can vote up the ones you like or vote down the ones you don't like, and go to the original project or source file by following the links above each example. You may check out the related API usage on the sidebar.

Example 1

Source File: XLSXWriter.java From org.hl7.fhir.core with Apache License 2.0

6 votes

/**
 * create a library of cell styles
 */
private static Map<String, CellStyle> createStyles(Workbook wb){
  Map<String, CellStyle> styles = new HashMap<>();

  CellStyle style;
  Font headerFont = wb.createFont();
  headerFont.setBold(true);
  style = createBorderedStyle(wb);
  style.setFillForegroundColor(IndexedColors.LIGHT_CORNFLOWER_BLUE.getIndex());
  style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
  style.setVerticalAlignment(VerticalAlignment.TOP);
  style.setWrapText(true);
  style.setFont(headerFont);
  styles.put("header", style);

  style = createBorderedStyle(wb);
  style.setVerticalAlignment(VerticalAlignment.TOP);
  style.setWrapText(true);    
  styles.put("body", style);

  return styles;
}

Example 2

Source File: XLSXWriter.java From org.hl7.fhir.core with Apache License 2.0

6 votes

/**
 * create a library of cell styles
 */
private static Map<String, CellStyle> createStyles(Workbook wb){
  Map<String, CellStyle> styles = new HashMap<>();

  CellStyle style;
  Font headerFont = wb.createFont();
  headerFont.setBold(true);
  style = createBorderedStyle(wb);
  style.setFillForegroundColor(IndexedColors.LIGHT_CORNFLOWER_BLUE.getIndex());
  style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
  style.setVerticalAlignment(VerticalAlignment.TOP);
  style.setWrapText(true);
  style.setFont(headerFont);
  styles.put("header", style);

  style = createBorderedStyle(wb);
  style.setVerticalAlignment(VerticalAlignment.TOP);
  style.setWrapText(true);    
  styles.put("body", style);

  return styles;
}

Example 3

Source File: AbstractSheet.java From tools with Apache License 2.0

6 votes

/**
 * create the styles in the workbook
 */
private void createStyles(Workbook wb) {
	// create the styles
	this.checkboxStyle = wb.createCellStyle();
	this.checkboxStyle.setAlignment(CellStyle.ALIGN_CENTER);
	this.checkboxStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
	this.checkboxStyle.setBorderBottom(CellStyle.BORDER_THIN);
	this.checkboxStyle.setBorderLeft(CellStyle.BORDER_THIN);
	this.checkboxStyle.setBorderRight(CellStyle.BORDER_THIN);
	this.checkboxStyle.setBorderTop(CellStyle.BORDER_THIN);
	Font checkboxFont = wb.createFont();
	checkboxFont.setFontHeight(FONT_SIZE);
	checkboxFont.setFontName(CHECKBOX_FONT_NAME);
	this.checkboxStyle.setFont(checkboxFont);

	this.dateStyle = wb.createCellStyle();
	DataFormat df = wb.createDataFormat();
	this.dateStyle.setDataFormat(df.getFormat("m/d/yy h:mm"));
}

Example 4

Source File: AbstractStyleBuilder.java From bdf3 with Apache License 2.0

6 votes

public void setCellStyleFont(Workbook workbook, CellStyle style, int i) {
	Font font = workbook.createFont();
	if (i == 0) {
		// 正常
	} else if (i == 4) {
		// 下划线
		font.setUnderline(Font.U_SINGLE);
		style.setFont(font);
	} else if (i == 2) {
		// 倾斜
		font.setItalic(true);
		style.setFont(font);
	} else if (i == 1) {
		// 加粗
		font.setBoldweight(Font.BOLDWEIGHT_BOLD);
		style.setFont(font);
	}
}

Example 5

Source File: DefaultXlsTableExporter.java From olat with Apache License 2.0

5 votes

private CellStyle getHeaderCellStyle(final Workbook wb) {
    CellStyle cellStyle = wb.createCellStyle();
    Font boldFont = wb.createFont();
    boldFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
    cellStyle.setFont(boldFont);
    return cellStyle;
}

Example 6

Source File: ExcelUtil.java From supplierShop with MIT License

5 votes

/**
 * 创建表格样式
 * 
 * @param wb 工作薄对象
 * @return 样式列表
 */
private Map<String, CellStyle> createStyles(Workbook wb)
{
    // 写入各条记录,每条记录对应excel表中的一行
    Map<String, CellStyle> styles = new HashMap<String, CellStyle>();
    CellStyle style = wb.createCellStyle();
    style.setAlignment(HorizontalAlignment.CENTER);
    style.setVerticalAlignment(VerticalAlignment.CENTER);
    style.setBorderRight(BorderStyle.THIN);
    style.setRightBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
    style.setBorderLeft(BorderStyle.THIN);
    style.setLeftBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
    style.setBorderTop(BorderStyle.THIN);
    style.setTopBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
    style.setBorderBottom(BorderStyle.THIN);
    style.setBottomBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
    Font dataFont = wb.createFont();
    dataFont.setFontName("Arial");
    dataFont.setFontHeightInPoints((short) 10);
    style.setFont(dataFont);
    styles.put("data", style);

    style = wb.createCellStyle();
    style.cloneStyleFrom(styles.get("data"));
    style.setAlignment(HorizontalAlignment.CENTER);
    style.setVerticalAlignment(VerticalAlignment.CENTER);
    style.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex());
    style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
    Font headerFont = wb.createFont();
    headerFont.setFontName("Arial");
    headerFont.setFontHeightInPoints((short) 10);
    headerFont.setBold(true);
    headerFont.setColor(IndexedColors.WHITE.getIndex());
    style.setFont(headerFont);
    styles.put("header", style);

    return styles;
}

Example 7

Source File: ExcelHandler.java From development with Apache License 2.0

5 votes

private static CellStyle initializeSheet(Workbook wb, Sheet sheet) {
    PrintSetup printSetup = sheet.getPrintSetup();
    printSetup.setLandscape(true);
    sheet.setFitToPage(true);
    sheet.setHorizontallyCenter(true);

    CellStyle styleTitle;
    Font titleFont = wb.createFont();
    titleFont.setFontHeightInPoints((short) 12);
    titleFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
    titleFont.setFontName("Arial");
    styleTitle = wb.createCellStyle();
    styleTitle.setFont(titleFont);
    return styleTitle;
}

Example 8

Source File: AbstractSheet.java From tools with Apache License 2.0

5 votes

public static CellStyle createHeaderStyle(Workbook wb) {
	CellStyle headerStyle = wb.createCellStyle();
	headerStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
	headerStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
	Font headerFont = wb.createFont();
	headerFont.setFontName("Arial");
	headerFont.setFontHeight(FONT_SIZE);
	headerFont.setBold(true);
	headerStyle.setFont(headerFont);
	headerStyle.setAlignment(HorizontalAlignment.CENTER);
	headerStyle.setVerticalAlignment(VerticalAlignment.CENTER);
	headerStyle.setWrapText(true);
	return headerStyle;
}

Example 9

Source File: GridStyleBuilder.java From bdf3 with Apache License 2.0

5 votes

public CellStyle createIndentationCellStyle(Workbook workbook, int s) {
	CellStyle dataStyle1 = this.createBorderCellStyle(workbook, true);
	Font dataFont = workbook.createFont();
	dataFont.setColor((short) 12);
	dataFont.setFontHeightInPoints((short) 10);
	dataStyle1.setFillPattern(CellStyle.SOLID_FOREGROUND);
	dataStyle1.setFillForegroundColor((short) 11);
	dataStyle1.setFont(dataFont);
	dataStyle1.setVerticalAlignment(CellStyle.ALIGN_CENTER);
	dataStyle1.setAlignment(CellStyle.ALIGN_LEFT);
	dataStyle1.setIndention(Short.valueOf(String.valueOf((s))));
	return dataStyle1;
}

Example 10

Source File: ThDefaultCellStyle.java From myexcel with Apache License 2.0

5 votes

@Override
public CellStyle supply(Workbook workbook) {
    CellStyle style = workbook.createCellStyle();
    style.setAlignment(HorizontalAlignment.CENTER);
    style.setVerticalAlignment(VerticalAlignment.CENTER);
    style.setBorderBottom(BorderStyle.THIN);
    style.setBorderRight(BorderStyle.THIN);
    style.setBorderTop(BorderStyle.THIN);
    style.setBorderLeft(BorderStyle.THIN);
    Font font = workbook.createFont();
    font.setBold(true);
    style.setFont(font);
    return style;
}

Example 11

Source File: ExcelUtil.java From RuoYi-Vue with MIT License

5 votes

/**
 * 创建表格样式
 * 
 * @param wb 工作薄对象
 * @return 样式列表
 */
private Map<String, CellStyle> createStyles(Workbook wb)
{
    // 写入各条记录,每条记录对应excel表中的一行
    Map<String, CellStyle> styles = new HashMap<String, CellStyle>();
    CellStyle style = wb.createCellStyle();
    style.setAlignment(HorizontalAlignment.CENTER);
    style.setVerticalAlignment(VerticalAlignment.CENTER);
    style.setBorderRight(BorderStyle.THIN);
    style.setRightBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
    style.setBorderLeft(BorderStyle.THIN);
    style.setLeftBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
    style.setBorderTop(BorderStyle.THIN);
    style.setTopBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
    style.setBorderBottom(BorderStyle.THIN);
    style.setBottomBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
    Font dataFont = wb.createFont();
    dataFont.setFontName("Arial");
    dataFont.setFontHeightInPoints((short) 10);
    style.setFont(dataFont);
    styles.put("data", style);

    style = wb.createCellStyle();
    style.cloneStyleFrom(styles.get("data"));
    style.setAlignment(HorizontalAlignment.CENTER);
    style.setVerticalAlignment(VerticalAlignment.CENTER);
    style.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex());
    style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
    Font headerFont = wb.createFont();
    headerFont.setFontName("Arial");
    headerFont.setFontHeightInPoints((short) 10);
    headerFont.setBold(true);
    headerFont.setColor(IndexedColors.WHITE.getIndex());
    style.setFont(headerFont);
    styles.put("header", style);

    return styles;
}

Example 12

Source File: SpreadsheetDataFileWriterXlsx.java From sakai with Educational Community License v2.0

4 votes

private Workbook getAsWorkbook(List<List<Object>> spreadsheetData) {
	Workbook wb = new SXSSFWorkbook();
	Sheet sheet = wb.createSheet();
	CellStyle headerCs = wb.createCellStyle();
	Iterator<List<Object>> dataIter = spreadsheetData.iterator();
	
	// Set the header style
	headerCs.setBorderBottom(BorderStyle.THICK);
	headerCs.setFillBackgroundColor(IndexedColors.BLUE_GREY.getIndex());


	// Set the font
	CellStyle cellStyle = null;
	String fontName = ServerConfigurationService.getString("spreadsheet.font");
	if (fontName != null) {
		Font font = wb.createFont();
		font.setFontName(fontName);
		headerCs.setFont(font);
		cellStyle = wb.createCellStyle();
		cellStyle.setFont(font);
	}

	// By convention, the first list in the list contains column headers.
	Row headerRow = sheet.createRow((short)0);
	List<Object> headerList = dataIter.next();
	for (short i = 0; i < headerList.size(); i++) {
		Cell headerCell = createCell(headerRow, i);
		headerCell.setCellValue((String)headerList.get(i));
		headerCell.setCellStyle(headerCs);
		//TODO
		//sheet.autoSizeColumn(i);
	}
	
	short rowPos = 1;
	while (dataIter.hasNext()) {
		List<Object> rowData = dataIter.next();
		Row row = sheet.createRow(rowPos++);
		for (short i = 0; i < rowData.size(); i++) {
			Cell cell = createCell(row, i);
			Object data = rowData.get(i);
			if (data != null) {
				if (data instanceof Double) {
					cell.setCellValue(((Double)data).doubleValue());
				} else {
					cell.setCellValue(data.toString());
				}
				if (cellStyle != null) {
					cell.setCellStyle(cellStyle);
				}
			}
		}
	}
	
	return wb;
}

Example 13

Source File: SpreadSheetFormatOptions.java From openbd-core with GNU General Public License v3.0

4 votes

public static Font createCommentFont( Workbook workbook, cfStructData _struct ) throws Exception {
	Font font = workbook.createFont();
	
	if ( _struct.containsKey("bold") ){
		if ( _struct.getData("bold").getBoolean() )
			font.setBoldweight( Font.BOLDWEIGHT_BOLD );
		else
			font.setBoldweight( Font.BOLDWEIGHT_NORMAL );
	}
	
	if ( _struct.containsKey("color") ){
		String v 	= _struct.getData("color").getString();
		Short s 	= lookup_colors.get( v );
		if ( s == null ){
			throw new Exception( "invalid parameter for 'color' (" + v + ")" );
		}else
			font.setColor( s );
	}
	
	if ( _struct.containsKey("font") ){
		font.setFontName( _struct.getData("font").getString() );
	}
	
	if ( _struct.containsKey("italic") ){
		font.setItalic( _struct.getData("italic").getBoolean() );
	}
	
	if ( _struct.containsKey("strikeout") ){
		font.setStrikeout( _struct.getData("strikeout").getBoolean() );
	}
	
	if ( _struct.containsKey("underline") ){
		font.setUnderline( (byte)_struct.getData("underline").getInt() );
	}

	if ( _struct.containsKey("size") ){
		font.setFontHeightInPoints( (short)_struct.getData("size").getInt() );
	}
	
	return font;
}

Example 14

Source File: ExcelUtil.java From lams with GNU General Public License v2.0

4 votes

private static void initStyles(Workbook workbook) {
Font defaultFont = workbook.createFont();
defaultFont.setFontName(DEFAULT_FONT_NAME);

//create default style with default font name
defaultStyle = workbook.createCellStyle();
defaultStyle.setFont(defaultFont);

//create bold style
boldStyle = workbook.createCellStyle();
Font boldFont = workbook.createFont();
boldFont.setBold(true);
boldFont.setFontName(DEFAULT_FONT_NAME);
boldStyle.setFont(boldFont);

//create color style
blueColor = workbook.createCellStyle();
blueColor.setFillForegroundColor(IndexedColors.LIGHT_CORNFLOWER_BLUE.getIndex());
blueColor.setFillPattern(FillPatternType.SOLID_FOREGROUND);
blueColor.setFont(defaultFont);
redColor = workbook.createCellStyle();
redColor.setFillForegroundColor(IndexedColors.RED.getIndex());
redColor.setFillPattern(FillPatternType.SOLID_FOREGROUND);
redColor.setFont(defaultFont);
greenColor = workbook.createCellStyle();
greenColor.setFillForegroundColor(IndexedColors.LIME.getIndex());
greenColor.setFillPattern(FillPatternType.SOLID_FOREGROUND);
Font whiteFont = workbook.createFont();
whiteFont.setColor(IndexedColors.WHITE.getIndex());
whiteFont.setFontName(DEFAULT_FONT_NAME);
greenColor.setFont(whiteFont);
yellowColor = workbook.createCellStyle();
yellowColor.setFillForegroundColor(IndexedColors.GOLD.getIndex());
yellowColor.setFillPattern(FillPatternType.SOLID_FOREGROUND);
yellowColor.setFont(defaultFont);

// create data formats
floatFormat = workbook.createDataFormat().getFormat("0.00");
numberFormat = workbook.createDataFormat().getFormat("0");
dateFormat = (short)14;// built-in 0xe format - "m/d/yy"
timeFormat = (short)19;// built-in 0x13 format - "h:mm:ss AM/PM"
percentageFormat = workbook.createDataFormat().getFormat(FORMAT_PERCENTAGE);

//create border style
borderStyleLeftThin = workbook.createCellStyle();
borderStyleLeftThin.setBorderLeft(BorderStyle.THIN);
borderStyleLeftThin.setFont(defaultFont);
borderStyleLeftThick = workbook.createCellStyle();
borderStyleLeftThick.setBorderLeft(BorderStyle.THICK);
borderStyleLeftThick.setFont(defaultFont);
borderStyleRightThick = workbook.createCellStyle();
borderStyleRightThick.setBorderRight(BorderStyle.THICK);
borderStyleRightThick.setFont(defaultFont);
borderStyleLeftThinBoldFont = workbook.createCellStyle();
borderStyleLeftThinBoldFont.setBorderLeft(BorderStyle.THIN);
borderStyleLeftThinBoldFont.setFont(boldFont);
borderStyleLeftThickBoldFont = workbook.createCellStyle();
borderStyleLeftThickBoldFont.setBorderLeft(BorderStyle.THICK);
borderStyleLeftThickBoldFont.setFont(boldFont);
borderStyleRightThickBoldFont = workbook.createCellStyle();
borderStyleRightThickBoldFont.setBorderRight(BorderStyle.THICK);
borderStyleRightThickBoldFont.setFont(boldFont);
borderStyleBottomThin = workbook.createCellStyle();
borderStyleBottomThin.setBorderBottom(BorderStyle.THIN);
borderStyleBottomThin.setFont(defaultFont);
borderStyleBottomThinBoldFont = workbook.createCellStyle();
borderStyleBottomThinBoldFont.setBorderBottom(BorderStyle.THIN);
borderStyleBottomThinBoldFont.setFont(boldFont);
   }

Example 15

Source File: ExcelImportServer.java From jeasypoi with Apache License 2.0

4 votes

private void createErrorCellStyle(Workbook workbook) {
	errorCellStyle = workbook.createCellStyle();
	Font font = workbook.createFont();
	font.setColor(Font.COLOR_RED);
	errorCellStyle.setFont(font);
}

Example 16

Source File: StyleUtil.java From easyexcel with Apache License 2.0

4 votes

private static void buildFont(Workbook workbook, CellStyle cellStyle, WriteFont writeFont, boolean isHead) {
    Font font = null;
    if (isHead) {
        font = workbook.createFont();
        font.setFontName("宋体");
        font.setFontHeightInPoints((short)14);
        font.setBold(true);
        cellStyle.setFont(font);
    }
    if (writeFont == null) {
        return;
    }
    if (!isHead) {
        font = workbook.createFont();
        cellStyle.setFont(font);
    }
    if (writeFont.getFontName() != null) {
        font.setFontName(writeFont.getFontName());
    }
    if (writeFont.getFontHeightInPoints() != null) {
        font.setFontHeightInPoints(writeFont.getFontHeightInPoints());
    }
    if (writeFont.getItalic() != null) {
        font.setItalic(writeFont.getItalic());
    }
    if (writeFont.getStrikeout() != null) {
        font.setStrikeout(writeFont.getStrikeout());
    }
    if (writeFont.getColor() != null) {
        font.setColor(writeFont.getColor());
    }
    if (writeFont.getTypeOffset() != null) {
        font.setTypeOffset(writeFont.getTypeOffset());
    }
    if (writeFont.getUnderline() != null) {
        font.setUnderline(writeFont.getUnderline());
    }
    if (writeFont.getCharset() != null) {
        font.setCharSet(writeFont.getCharset());
    }
    if (writeFont.getBold() != null) {
        font.setBold(writeFont.getBold());
    }
}

Example 17

Source File: PoiExport.java From hrms with Apache License 2.0

4 votes

public InputStream exportExcel(String fileName, String[] headTitle,
		List<String> list) throws IOException {

	Workbook wb = new HSSFWorkbook();
	//FileOutputStream fileOut = new FileOutputStream(fileName);
	HSSFCellStyle style = (HSSFCellStyle) wb.createCellStyle();
	
	// 设置这些样式
	style.setFillForegroundColor(HSSFColor.SKY_BLUE.index);
	style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
	style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
	style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
	style.setBorderRight(HSSFCellStyle.BORDER_THIN);
	style.setBorderTop(HSSFCellStyle.BORDER_THIN);
	style.setAlignment(HSSFCellStyle.ALIGN_CENTER);

	// 生成一个字体
	HSSFFont font = (HSSFFont) wb.createFont();
	font.setColor(HSSFColor.VIOLET.index);
	font.setFontHeightInPoints((short) 12);
	font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);

	// 把字体应用到当前的样式
	style.setFont(font);
	// 创建工作簿
	Sheet sheet = wb.createSheet("sheet1");
	// sheet.setDefaultColumnWidth((short)15);

	// 创建头部
	Row row = sheet.createRow((short) 0);
	for (int i = 0; i < headTitle.length; i++) {
		row.createCell(i).setCellValue(headTitle[i]);
	}
	// 填充数据
	for (int i = 0; i < list.size(); i++) {
		Row r = sheet.createRow((short) (i + 1));
		String[] strArray = list.get(i).split(",");
		for (int j = 0; j < headTitle.length; j++) {
			r.createCell(j).setCellValue(strArray[j]);
			if (strArray[j].length()>3) {
				sheet.setColumnWidth((short)j, 5000);
			}
		}
		
	}
	ByteArrayOutputStream baos = new ByteArrayOutputStream();
	try {
		wb.write(baos);
	} catch (IOException e) {
		e.printStackTrace();
	}
	byte[] ba = baos.toByteArray();
	ByteArrayInputStream excelStream = new ByteArrayInputStream(ba);
	return excelStream;

}

Example 18

Source File: SpreadsheetDataFileWriterXlsx.java From sakai with Educational Community License v2.0

4 votes

private Workbook getAsWorkbook(List<List<Object>> spreadsheetData) {
	Workbook wb = new SXSSFWorkbook();
	Sheet sheet = wb.createSheet();
	CellStyle headerCs = wb.createCellStyle();
	Iterator<List<Object>> dataIter = spreadsheetData.iterator();
	
	// Set the header style
	headerCs.setBorderBottom(BorderStyle.THICK);
	headerCs.setFillBackgroundColor(IndexedColors.BLUE_GREY.getIndex());


	// Set the font
	CellStyle cellStyle = null;
	String fontName = ServerConfigurationService.getString("spreadsheet.font");
	if (fontName != null) {
		Font font = wb.createFont();
		font.setFontName(fontName);
		headerCs.setFont(font);
		cellStyle = wb.createCellStyle();
		cellStyle.setFont(font);
	}

	// By convention, the first list in the list contains column headers.
	Row headerRow = sheet.createRow((short)0);
	List<Object> headerList = dataIter.next();
	for (short i = 0; i < headerList.size(); i++) {
		Cell headerCell = createCell(headerRow, i);
		headerCell.setCellValue((String)headerList.get(i));
		headerCell.setCellStyle(headerCs);
		//TODO
		//sheet.autoSizeColumn(i);
	}
	
	short rowPos = 1;
	while (dataIter.hasNext()) {
		List<Object> rowData = dataIter.next();
		Row row = sheet.createRow(rowPos++);
		for (short i = 0; i < rowData.size(); i++) {
			Cell cell = createCell(row, i);
			Object data = rowData.get(i);
			if (data != null) {
				if (data instanceof Double) {
					cell.setCellValue(((Double)data).doubleValue());
				} else {
					cell.setCellValue(data.toString());
				}
				if (cellStyle != null) {
					cell.setCellStyle(cellStyle);
				}
			}
		}
	}
	
	return wb;
}

Example 19

Source File: DataSetExportServicesImpl.java From dashbuilder with Apache License 2.0

4 votes

private Map<String, CellStyle> createStyles(Workbook wb){
    Map<String, CellStyle> styles = new HashMap<>();
    CellStyle style;

    Font titleFont = wb.createFont();
    titleFont.setFontHeightInPoints((short)12);
    titleFont.setBold(true);
    style = wb.createCellStyle();
    style.setAlignment(HorizontalAlignment.CENTER);
    style.setVerticalAlignment(VerticalAlignment.CENTER);
    style.setFillForegroundColor( IndexedColors.GREY_25_PERCENT.getIndex());
    style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
    style.setFont(titleFont);
    style.setWrapText(false);
    style.setBorderBottom(BorderStyle.THIN);
    style.setBottomBorderColor(IndexedColors.GREY_80_PERCENT.getIndex());
    styles.put("header", style);

    Font cellFont = wb.createFont();
    cellFont.setFontHeightInPoints((short)10);
    cellFont.setBold(true);

    style = wb.createCellStyle();
    style.setAlignment(HorizontalAlignment.RIGHT);
    style.setVerticalAlignment(VerticalAlignment.BOTTOM);
    style.setFont(cellFont);
    style.setWrapText(false);
    style.setDataFormat(wb.createDataFormat().getFormat( BuiltinFormats.getBuiltinFormat( 3 )));
    styles.put("integer_number_cell", style);

    style = wb.createCellStyle();
    style.setAlignment(HorizontalAlignment.RIGHT);
    style.setVerticalAlignment(VerticalAlignment.BOTTOM);
    style.setFont(cellFont);
    style.setWrapText(false);
    style.setDataFormat(wb.createDataFormat().getFormat(BuiltinFormats.getBuiltinFormat(4)));
    styles.put("decimal_number_cell", style);

    style = wb.createCellStyle();
    style.setAlignment(HorizontalAlignment.LEFT);
    style.setVerticalAlignment(VerticalAlignment.BOTTOM);
    style.setFont(cellFont);
    style.setWrapText(false);
    style.setDataFormat( (short) BuiltinFormats.getBuiltinFormat("text") );
    styles.put(TEXT_CELL, style);

    style = wb.createCellStyle();
    style.setAlignment(HorizontalAlignment.CENTER);
    style.setVerticalAlignment(VerticalAlignment.BOTTOM);
    style.setFont(cellFont);
    style.setWrapText(false);
    style.setDataFormat(wb.createDataFormat().getFormat( DateFormatConverter.convert( Locale.getDefault(), dateFormatPattern )));
    styles.put("date_cell", style);
    return styles;
}

Example 20

Source File: SpreadsheetDataFileWriterXls.java From sakai with Educational Community License v2.0

4 votes

private Workbook getAsWorkbook(List<List<Object>> spreadsheetData) {
	Workbook wb = new HSSFWorkbook();
	Sheet sheet = wb.createSheet();
	CellStyle headerCs = wb.createCellStyle();
	Iterator<List<Object>> dataIter = spreadsheetData.iterator();
	
	// Set the header style
	headerCs.setBorderBottom(BorderStyle.THICK);
	headerCs.setFillBackgroundColor(IndexedColors.BLUE_GREY.getIndex());
	// Set the font
	CellStyle cellStyle = null;
	String fontName = ServerConfigurationService.getString("spreadsheet.font");
	if (fontName != null) {
		Font font = wb.createFont();
		font.setFontName(fontName);
		headerCs.setFont(font);
		cellStyle = wb.createCellStyle();
		cellStyle.setFont(font);
	}

	// By convention, the first list in the list contains column headers.
	Row headerRow = sheet.createRow((short)0);
	List<Object> headerList = dataIter.next();
	for (short i = 0; i < headerList.size(); i++) {
		Cell headerCell = createCell(headerRow, i);
		headerCell.setCellValue((String)headerList.get(i));
		headerCell.setCellStyle(headerCs);
		sheet.autoSizeColumn(i);
	}
	
	short rowPos = 1;
	while (dataIter.hasNext()) {
		List<Object> rowData = dataIter.next();
		Row row = sheet.createRow(rowPos++);
		for (short i = 0; i < rowData.size(); i++) {
			Cell cell = createCell(row, i);
			Object data = rowData.get(i);
			if (data != null) {
				if (data instanceof Double) {
					cell.setCellValue(((Double)data).doubleValue());
				} else {
					cell.setCellValue(data.toString());
				}
				if (cellStyle != null) {
					cell.setCellStyle(cellStyle);
				}
			}
		}
	}
	
	return wb;
}