org.apache.poi.ss.usermodel.IndexedColors Java Examples

@XlsPostSave
public void handlePostSave(final Sheet sheet) {

    if(!name.equals("平均")) {
        return;
    }

    final Workbook book = sheet.getWorkbook();

    for(Point address : positions.values()) {
        Cell cell = POIUtils.getCell(sheet, address);

        CellStyle style = book.createCellStyle();
        style.cloneStyleFrom(cell.getCellStyle());

        // 塗りつぶし
        style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);

        // 罫線の上部を変更
        style.setBorderTop(BorderStyle.DOUBLE);

        cell.setCellStyle(style);
    }

}
 

private int addLangCell(Row header, TmxSegement segment) {
	int CellNum = header.getLastCellNum();
	if (-1 == CellNum) {
		CellNum = 0;
	}
	Cell createCell = header.createCell(CellNum);
	CellStyle cellStyle = wb.createCellStyle();
	XSSFFont headerFont = (XSSFFont) wb.createFont();
	headerFont.setBold(true);
	cellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
	cellStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
	cellStyle.setFont(headerFont);
	createCell.setCellValue(segment.getLangCode());
	createCell.setCellStyle(cellStyle);
	sh.setColumnWidth(CellNum, (100 * 7 + 5) / 7 * 256);
	return CellNum;
}
 

@Deprecated
private WriteCellStyle buildWriteCellStyle(Font font, IndexedColors indexedColors) {
    WriteCellStyle writeCellStyle = new WriteCellStyle();
    if (indexedColors != null) {
        writeCellStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);
        writeCellStyle.setFillForegroundColor(indexedColors.getIndex());
    }
    if (font != null) {
        WriteFont writeFont = new WriteFont();
        writeFont.setFontName(font.getFontName());
        writeFont.setFontHeightInPoints(font.getFontHeightInPoints());
        writeFont.setBold(font.isBold());
        writeCellStyle.setWriteFont(writeFont);
    }
    return writeCellStyle;
}
 

SerialWriteWorkbook() {
  this.swb = new SXSSFWorkbook(100);
  this.sh = this.swb.createSheet();

  this.rowIndex = 0;

  this.headerStyle = (XSSFCellStyle)swb.createCellStyle();
  this.headerStyle.setFillBackgroundColor(IndexedColors.BLACK.getIndex());
  //solid fill
  this.headerStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
  Font headerFont = swb.createFont();
  headerFont.setFontHeightInPoints((short)14);
  headerFont.setBold(true);
  headerFont.setColor(IndexedColors.WHITE.getIndex());
  this.headerStyle.setFont(headerFont);
}
 

protected CellStyle getHeaderIntervalStyle(P p) {
	String styleId = "header-interval";
	CellStyle style = iStyles.get(styleId);
	if (style == null) {
		style = iWorkbook.createCellStyle();
        style.setBorderLeft(BorderStyle.THIN);
        style.setLeftBorderColor(IndexedColors.GREY_80_PERCENT.getIndex());
        style.setBorderTop(BorderStyle.THIN);
        style.setTopBorderColor(IndexedColors.GREY_80_PERCENT.getIndex());
        if (p.getWidth() == 1) {
        	style.setBorderRight(BorderStyle.THIN);
        	style.setRightBorderColor(IndexedColors.GREY_80_PERCENT.getIndex());
        }
        style.setFont(getFont(p));
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.TOP);
        style.setWrapText(true);
        iStyles.put(styleId, style);
	}
	return style;
}
 

protected CellStyle getGridNameStyle(P p) {
	String styleId = "grid-name";
	CellStyle style = iStyles.get(styleId);
	if (style == null) {
		style = iWorkbook.createCellStyle();
        style.setBorderLeft(BorderStyle.THIN);
        style.setLeftBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
        style.setBorderTop(BorderStyle.THIN);
        style.setTopBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
        style.setFont(getFont(p));
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.TOP);
        style.setWrapText(true);
        iStyles.put(styleId, style);
	}
	return style;
}
 

protected CellStyle getStyle(A f, boolean dashed, String format) {
	String styleId = (dashed ? "D" : "")
			+ (f.has(F.BOLD) ? "b" : "") + (f.has(F.ITALIC) ? "i" : "") + (f.has(F.UNDERLINE) ? "u" : "")
			+ (f.has(F.RIGHT) ? "R" : f.has(F.CENTER) ? "C" : "L")
			+ (f.hasColor() ? "#" + Integer.toHexString(f.getColor().getRGB()) : "")
			+ (format == null ? "" : "|" + format);
	CellStyle style = iStyles.get(styleId);
	if (style == null) {
		style = iWorkbook.createCellStyle();
		if (dashed) {
			style.setBorderTop(BorderStyle.DASHED);
	        style.setTopBorderColor(IndexedColors.BLACK.getIndex());
		}
		style.setAlignment(f.has(F.RIGHT) ? HorizontalAlignment.RIGHT : f.has(F.CENTER) ? HorizontalAlignment.CENTER : HorizontalAlignment.LEFT);
		style.setVerticalAlignment(VerticalAlignment.TOP);
		style.setFont(getFont(f.has(F.BOLD), f.has(F.ITALIC), f.has(F.UNDERLINE), f.getColor()));
       	style.setWrapText(true);
       	if (format != null)
       		style.setDataFormat(iWorkbook.createDataFormat().getFormat(format));
       	iStyles.put(styleId, style);
	}
	return style;
}
 

/**
 * XSSFRichTextString.utfDecode()<br/>
 * value.contains("_x")<br/>
 * Pattern.compile("_x([0-9A-Fa-f]{4})_");
 */
private static void export2007WithStyle(String filePath) {
	try {
		OutputStream os = new FileOutputStream(filePath);
		XSSFWorkbook wb = new XSSFWorkbook();
		XSSFSheet sheet = wb.createSheet(Globals.SHEETNAME);
		XSSFCell cell = sheet.createRow(0).createCell(0);
		cell.setCellValue(TestUtil.RICH_TEXT_STRINGS[0]
				+ escape(TestUtil.REGEX + TestUtil.RICH_TEXT_STRINGS[1]
						+ TestUtil.REGEX) + TestUtil.RICH_TEXT_STRINGS[2]);
		CellStyle style = sheet.getWorkbook().createCellStyle();
		XSSFFont font = wb.createFont();
		font.setColor(IndexedColors.BLUE.index);
		style.setFont(font);
		cell.setCellStyle(style);
		// richString.applyFont(font);
		wb.write(os);
		os.close();
	} catch (Exception e) {
		e.printStackTrace();
	}
}
 

@Test
public void exportExcelWithStyle() {
	try {
		String filePath = TestUtil.DOC_PATH + File.separator
				+ Globals.EXPORT_PRODUCT;
		OutputStream os = new FileOutputStream(filePath);
		HSSFWorkbook wb = new HSSFWorkbook();
		HSSFSheet sheet = wb.createSheet(Globals.SHEETNAME);
		HSSFRichTextString richString = new HSSFRichTextString(
				TestUtil.RICH_TEXT_STRING);
		HSSFFont font = wb.createFont();
		font.setColor(IndexedColors.BLUE.index);
		richString.applyFont(font);
		sheet.createRow(0).createCell(0).setCellValue(richString);
		wb.write(os);
		os.close();
	} catch (Exception e) {
		e.printStackTrace();
	}
}
 

private static Row createHeaders(Map<String, String> headerMap,
		Sheet sheet) {
	CellStyle style = sheet.getWorkbook().createCellStyle();
	style.setFillForegroundColor(IndexedColors.YELLOW.getIndex());
	style.setFillPattern(CellStyle.SOLID_FOREGROUND);
	style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
	style.setBorderTop(HSSFCellStyle.BORDER_THIN);
	style.setBorderRight(HSSFCellStyle.BORDER_THIN);
	style.setBorderLeft(HSSFCellStyle.BORDER_THIN);

	Row header = sheet.createRow(0);
	int columnIndex = 0;
	for (Map.Entry<String, String> entry : headerMap.entrySet()) {
		String headerText = StringUtils.defaultString(entry.getValue());
		Cell cell = createCell(header, columnIndex);
		cell.setCellValue(headerText);
		cell.setCellStyle(style);
		sheet.autoSizeColumn(columnIndex);
		columnIndex++;
	}

	return header;
}
 

/**
 * Create styled workbook.
 * @throws IOException If fails
 */
@Test
public void createsWorkbookWithStyles() throws IOException {
    final Workbook workbook = new XsWorkbook(
        new XsSheet(
            new XsRow(
                new TextCell("text")
            )
        )
    ).with(
        new XsStyle(
            new ForegroundColor(
                IndexedColors.GOLD.getIndex()
            )
        )
    ).asWorkbook();
    MatcherAssert.assertThat(
        workbook.getSheetAt(0).getRow(0)
            .getCell(0).getCellStyle().getFillForegroundColor(),
        Matchers.equalTo(IndexedColors.GOLD.getIndex())
    );
}
 

@SuppressWarnings("unused")
private TableStyle getTableStyle1() {
        TableStyle tableStyle = new TableStyle();
        Font headFont = new Font();
        headFont.setBold(true);
        headFont.setFontHeightInPoints((short)22);
        headFont.setFontName("楷体");
        tableStyle.setTableHeadFont(headFont);
        tableStyle.setTableHeadBackGroundColor(IndexedColors.LIGHT_BLUE);

        Font contentFont = new Font();
        contentFont.setBold(true);
        contentFont.setFontHeightInPoints((short)22);
        contentFont.setFontName("黑体");
        tableStyle.setTableContentFont(contentFont);
        tableStyle.setTableContentBackGroundColor(IndexedColors.LIGHT_GREEN);
        return tableStyle;
    }
 

public TableStyle style() {
    TableStyle tableStyle = new TableStyle();
    Font headFont = new Font();
    headFont.setBold(true);
    headFont.setFontHeightInPoints((short)22);
    headFont.setFontName("楷体");
    tableStyle.setTableHeadFont(headFont);
    tableStyle.setTableHeadBackGroundColor(IndexedColors.BLUE);

    Font contentFont = new Font();
    contentFont.setBold(true);
    contentFont.setFontHeightInPoints((short)22);
    contentFont.setFontName("黑体");
    tableStyle.setTableContentFont(contentFont);
    tableStyle.setTableContentBackGroundColor(IndexedColors.GREEN);
    return tableStyle;
}
 

private void readAndWrite(File file) {
    SimpleColumnWidthStyleStrategy simpleColumnWidthStyleStrategy = new SimpleColumnWidthStyleStrategy(50);
    SimpleRowHeightStyleStrategy simpleRowHeightStyleStrategy =
        new SimpleRowHeightStyleStrategy((short)40, (short)50);

    WriteCellStyle headWriteCellStyle = new WriteCellStyle();
    headWriteCellStyle.setFillForegroundColor(IndexedColors.RED.getIndex());
    WriteFont headWriteFont = new WriteFont();
    headWriteFont.setFontHeightInPoints((short)20);
    headWriteCellStyle.setWriteFont(headWriteFont);
    WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
    contentWriteCellStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);
    contentWriteCellStyle.setFillForegroundColor(IndexedColors.GREEN.getIndex());
    WriteFont contentWriteFont = new WriteFont();
    contentWriteFont.setFontHeightInPoints((short)20);
    headWriteCellStyle.setWriteFont(contentWriteFont);
    HorizontalCellStyleStrategy horizontalCellStyleStrategy =
        new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);

    OnceAbsoluteMergeStrategy onceAbsoluteMergeStrategy = new OnceAbsoluteMergeStrategy(2, 2, 0, 1);
    EasyExcel.write(file, StyleData.class).registerWriteHandler(simpleColumnWidthStyleStrategy)
        .registerWriteHandler(simpleRowHeightStyleStrategy).registerWriteHandler(horizontalCellStyleStrategy)
        .registerWriteHandler(onceAbsoluteMergeStrategy).sheet().doWrite(data());
    EasyExcel.read(file, StyleData.class, new StyleDataListener()).sheet().doRead();
}
 

public CellStyle build(ExcelCell cell) {
    Optional<CellStyle> cs = cell.findStyle(hashCode());
    if (cs.isPresent()) {
        return cs.get();
    }

    CellStyle cellStyle = cell.cloneStyle(hashCode());
    addFontFormat(cell, cellStyle);

    if (header) {
        cellStyle.setBorderBottom(BorderStyle.THIN);
        cellStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        cellStyle.setFillForegroundColor(IndexedColors.PALE_BLUE.getIndex());
        cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
    }

    addFormat(cell, cellStyle);

    if (backgroundColour != null) {
        cellStyle.setFillForegroundColor(backgroundColour.getIndex());
        cellStyle.setFillPattern(backgroundFillPatternType != null ? backgroundFillPatternType : FillPatternType.SOLID_FOREGROUND);
    }

    addAlignment(cellStyle);
    return cellStyle;
}
 

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

/**
 * 样式
 *
 * @return
 */
protected HorizontalCellStyleStrategy buildStyle() {
    WriteFont baseFont = new WriteFont();
    baseFont.setFontHeightInPoints((short) 12);
    baseFont.setColor(IndexedColors.BLACK.getIndex());

    // 头
    WriteCellStyle headStyle = new WriteCellStyle();
    headStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
    headStyle.setWriteFont(baseFont);
    // 内容
    WriteCellStyle contentStyle = new WriteCellStyle();
    contentStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
    // 这里需要指定 FillPatternType 为 FillPatternType.SOLID_FOREGROUND 不然无法显示背景颜色
    // 头默认了 FillPatternType 所以可以不指定
    contentStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);
    contentStyle.setWriteFont(baseFont);
    contentStyle.setBorderBottom(BorderStyle.THIN);
    contentStyle.setBorderRight(BorderStyle.THIN);

    return new HorizontalCellStyleStrategy(headStyle, contentStyle);
}
 

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

public CellStyle createBorderCellStyle(Workbook workbook, boolean showBorder) {
	CellStyle style = workbook.createCellStyle();
	if (showBorder) {
		style.setBorderRight(CellStyle.BORDER_THIN);
		style.setRightBorderColor(IndexedColors.BLACK.getIndex());
		style.setBorderBottom(CellStyle.BORDER_THIN);
		style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
		style.setBorderLeft(CellStyle.BORDER_THIN);
		style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
		style.setBorderTop(CellStyle.BORDER_THIN);
		style.setTopBorderColor(IndexedColors.BLACK.getIndex());
	}
	return style;
}
 

private CellStyle createHeaderStyle(){
    //TO-DO read style information from sakai.properties
    Font font = gradesWorkbook.createFont();
    font.setFontName(HSSFFont.FONT_ARIAL);
    font.setColor(IndexedColors.PLUM.getIndex());
    font.setBold(true);
    CellStyle cellStyle = gradesWorkbook.createCellStyle();
    cellStyle.setFont(font);
    return cellStyle;
}
 

public ExcelSheet tabColour(IndexedColors colour) {
    if (sheet() instanceof XSSFSheet) {
        ((XSSFSheet) sheet()).setTabColor(new XSSFColor(colour, new DefaultIndexedColorMap()));
    } else if (sheet() instanceof SXSSFSheet) {
        ((SXSSFSheet) sheet()).setTabColor(new XSSFColor(colour, new DefaultIndexedColorMap()));
    }
    return this;
}
 

private static <T> Row createRow(Map<String, String> headerMap,
								 T record, SepRecordType recordType, int recordIndex, Sheet sheet, int rowIndex,
								 String datumErrPlaceholder, List<DatumError> datumErrors) {
	Row row = sheet.createRow(rowIndex);
	int columnIndex = 0;

	for (Map.Entry<String, String> entry : headerMap.entrySet()) {
		boolean datumErr = false;
		String propName = entry.getKey();
		Object propValue = null;
		try {
			propValue = getProperty(record, recordType, propName);
		} catch (Exception e) {
			if (datumErrors != null) {
				DatumError de = new DatumError();
				de.setPropName(propName);
				de.setRecordIndex(recordIndex);
				de.setCause(e);
				datumErrors.add(de);
			}
			datumErr = true;
			propValue = datumErrPlaceholder;
		}
		String propValueText = (propValue == null ? null : propValue
				.toString());
		Cell cell = createCell(row, columnIndex);
		cell.setCellValue(StringUtils.defaultString(propValueText));

		if (datumErr) {
			CellStyle errStyle = sheet.getWorkbook().createCellStyle();
			errStyle.setFillForegroundColor(IndexedColors.RED.getIndex());
			errStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
			cell.setCellStyle(errStyle);
		}

		columnIndex++;
	}

	return row;
}
 

public void postProcessXLS(Object document) {  
    HSSFWorkbook wb = (HSSFWorkbook) document;  
    HSSFSheet sheet = wb.getSheetAt(0);  
    HSSFRow header = sheet.getRow(0);  
    HSSFCellStyle cellStyle = wb.createCellStyle();    
    cellStyle.setFillForegroundColor(HSSFColor.GREEN.index);  
    cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);  
      
    for(int i=0; i < header.getPhysicalNumberOfCells();i++) {  
        HSSFCell cell = header.getCell(i);  
          
        cell.setCellStyle(cellStyle);  
    }  
    Row row=sheet.createRow((short)sheet.getLastRowNum()+3);
    Cell cellDisclaimer = row.createCell(0);
    HSSFFont customFont= wb.createFont();
    customFont.setFontHeightInPoints((short)10);
    customFont.setFontName("Arial");
    customFont.setColor(IndexedColors.BLACK.getIndex());
    customFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    customFont.setItalic(true);
    
    cellDisclaimer.setCellValue("Disclaimer");
    HSSFCellStyle cellStyleDisclaimer = wb.createCellStyle();
    cellStyleDisclaimer.setFont(customFont);
    cellDisclaimer.setCellStyle(cellStyleDisclaimer);
    
    Row row1=sheet.createRow(sheet.getLastRowNum()+2);
    Cell cellDisclaimerContent1 = row1.createCell(0);
    cellDisclaimerContent1.setCellValue("The information contained in this website is for information purposes only, and does not constitute, nor is it intended to constitute, the provision of financial product advice.");	    
    Row row2=sheet.createRow(sheet.getLastRowNum()+1);
    Cell cellDisclaimerContent2 = row2.createCell(0);
    cellDisclaimerContent2.setCellValue("This website is intended to track the investor account summary information,investments and transaction in a partcular period of time. ");
    
}
 

public void postProcessXLS(Object document) {  
    HSSFWorkbook wb = (HSSFWorkbook) document;  
    HSSFSheet sheet = wb.getSheetAt(0);  
    HSSFRow header = sheet.getRow(0);  
      
    HSSFCellStyle cellStyle = wb.createCellStyle();    
    cellStyle.setFillForegroundColor(HSSFColor.GREEN.index);  
    cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);  
      
    for(int i=0; i < header.getPhysicalNumberOfCells();i++) {  
        HSSFCell cell = header.getCell(i);  
          
        cell.setCellStyle(cellStyle);  
    }  
    
    Row row=sheet.createRow((short)sheet.getLastRowNum()+3);
    Cell cellDisclaimer = row.createCell(0);
    HSSFFont customFont= wb.createFont();
    customFont.setFontHeightInPoints((short)10);
    customFont.setFontName("Arial");
    customFont.setColor(IndexedColors.BLACK.getIndex());
    customFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    customFont.setItalic(true);
    
    cellDisclaimer.setCellValue("Disclaimer");
    HSSFCellStyle cellStyleDisclaimer = wb.createCellStyle();
    cellStyleDisclaimer.setFont(customFont);
    cellDisclaimer.setCellStyle(cellStyleDisclaimer);
    
    Row row1=sheet.createRow(sheet.getLastRowNum()+2);
    Cell cellDisclaimerContent1 = row1.createCell(0);
    cellDisclaimerContent1.setCellValue("The information contained in this website is for information purposes only, and does not constitute, nor is it intended to constitute, the provision of financial product advice.");
    
    Row row2=sheet.createRow(sheet.getLastRowNum()+1);
    Cell cellDisclaimerContent2 = row2.createCell(0);
    cellDisclaimerContent2.setCellValue("This website is intended to track the investor account summary information,investments and transaction in a partcular period of time. ");
    
}
 

public void postProcessXLS(Object document) {
	HSSFWorkbook wb = (HSSFWorkbook) document;
	HSSFSheet sheet = wb.getSheetAt(0);
	HSSFRow header = sheet.getRow(0);

	HSSFCellStyle cellStyle = wb.createCellStyle();
	cellStyle.setFillForegroundColor(HSSFColor.GREEN.index);
	cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

	for (int i = 0; i < header.getPhysicalNumberOfCells(); i++) {
		HSSFCell cell = header.getCell(i);

		cell.setCellStyle(cellStyle);
	}

	Row row = sheet.createRow((short) sheet.getLastRowNum() + 3);
	Cell cellDisclaimer = row.createCell(0);
	HSSFFont customFont = wb.createFont();
	customFont.setFontHeightInPoints((short) 10);
	customFont.setFontName("Arial");
	customFont.setColor(IndexedColors.BLACK.getIndex());
	customFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
	customFont.setItalic(true);

	cellDisclaimer.setCellValue("Disclaimer");
	HSSFCellStyle cellStyleDisclaimer = wb.createCellStyle();
	cellStyleDisclaimer.setFont(customFont);
	cellDisclaimer.setCellStyle(cellStyleDisclaimer);

	Row row1 = sheet.createRow(sheet.getLastRowNum() + 2);
	Cell cellDisclaimerContent1 = row1.createCell(0);
	cellDisclaimerContent1
			.setCellValue("The information contained in this website is for information purposes only, and does not constitute, nor is it intended to constitute, the provision of financial product advice.");

	Row row2 = sheet.createRow(sheet.getLastRowNum() + 1);
	Cell cellDisclaimerContent2 = row2.createCell(0);
	cellDisclaimerContent2
			.setCellValue("This website is intended to track the investor account summary information,investments and transaction in a partcular period of time. ");

}
 

protected Font getFont(boolean bold, boolean italic, boolean underline, Color c) {
	Short color = null;
	if (c == null) c = Color.BLACK;
	if (c != null) {
		String colorId = Integer.toHexString(c.getRGB());
		color = iColors.get(colorId);
		if (color == null) {
			HSSFPalette palette = ((HSSFWorkbook)iWorkbook).getCustomPalette();
			HSSFColor clr = palette.findSimilarColor(c.getRed(), c.getGreen(), c.getBlue());
			color = (clr == null ? IndexedColors.BLACK.getIndex() : clr.getIndex());
			iColors.put(colorId, color);
		}
	}
	String fontId = (bold ? "b" : "") + (italic ? "i" : "") + (underline ? "u" : "") + (color == null ? "" : color);
	Font font = iFonts.get(fontId);
	if (font == null) {
		font = iWorkbook.createFont();
		font.setBold(bold);
		font.setItalic(italic);
		font.setUnderline(underline ? Font.U_SINGLE : Font.U_NONE);
		font.setColor(color);
		font.setFontHeightInPoints((short)10);
		font.setFontName("Arial");
		iFonts.put(fontId, font);
	}
	return font;
}
 

private static CellStyle createBorderedStyle(Workbook wb) {
    CellStyle style = wb.createCellStyle();
    style.setBorderRight(CellStyle.BORDER_THIN);
    style.setRightBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderBottom(CellStyle.BORDER_THIN);
    style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderLeft(CellStyle.BORDER_THIN);
    style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderTop(CellStyle.BORDER_THIN);
    style.setTopBorderColor(IndexedColors.BLACK.getIndex());
    return style;
}
 

/**
 * 对变量名称反射出来的值进行加工处理
 * 
 * @author      ZhengWei(HY)
 * @createDate  2017-06-29
 * @version     v1.0
 *
 * @param i_RTemplate     模板
 * @param i_TemplateCell  模板单元格对象
 * @param i_DataCell      数据单元格对象
 * @param i_DataWorkbook  数据工作薄对象
 * @param i_RSystemValue  系统变量信息
 * @param i_Datas         本行对应的数据
 * @param i_Value         反射出来的变量名称对应的值
 * @return 
 */
public String getValue(RTemplate i_RTemplate ,Cell i_TemplateCell ,Cell i_DataCell ,RWorkbook i_DataWorkbook ,RSystemValue i_RSystemValue ,Object i_Datas ,Object i_Value)
{
    CellStyle v_NewCellStyle = null;
    
    if ( i_DataCell.getRow().getRowNum() % 2 == 1 )
    {
        v_NewCellStyle = i_DataWorkbook.getCellStyleByCopy("单行" ,i_DataCell ,i_RTemplate);
        v_NewCellStyle.setFillForegroundColor(IndexedColors.ORANGE.index);
    }
    else
    {
        v_NewCellStyle = i_DataWorkbook.getCellStyleByCopy("双行" ,i_DataCell ,i_RTemplate);
        v_NewCellStyle.setFillForegroundColor(IndexedColors.RED.index);
    }
    
    v_NewCellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
    
    i_DataCell.setCellStyle(v_NewCellStyle);
    
    if ( i_Value == null )
    {
        return "";
    }
    
    return i_Value.toString();
}
 

/**
 * 创建表格样式
 * 
 * @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;
}
 

public CellStyle buildDimensionCellStyle(Sheet sheet) {
	CellStyle cellStyle = sheet.getWorkbook().createCellStyle();
	cellStyle.setAlignment(CellStyle.ALIGN_CENTER);
	cellStyle.setVerticalAlignment(CellStyle.ALIGN_CENTER);

	String headerBGColor = (String) this.getProperty(PROPERTY_DIMENSION_NAME_BACKGROUND_COLOR);
	logger.debug("Header background color : " + headerBGColor);
	short backgroundColorIndex = headerBGColor != null ? IndexedColors.valueOf(headerBGColor).getIndex() : IndexedColors.valueOf(
			DEFAULT_DIMENSION_NAME_BACKGROUND_COLOR).getIndex();
	cellStyle.setFillForegroundColor(backgroundColorIndex);

	cellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);

	cellStyle.setBorderBottom(CellStyle.BORDER_THIN);
	cellStyle.setBorderLeft(CellStyle.BORDER_THIN);
	cellStyle.setBorderRight(CellStyle.BORDER_THIN);
	cellStyle.setBorderTop(CellStyle.BORDER_THIN);

	String bordeBorderColor = (String) this.getProperty(PROPERTY_HEADER_BORDER_COLOR);
	logger.debug("Header border color : " + bordeBorderColor);
	short borderColorIndex = bordeBorderColor != null ? IndexedColors.valueOf(bordeBorderColor).getIndex() : IndexedColors.valueOf(
			DEFAULT_HEADER_BORDER_COLOR).getIndex();

	cellStyle.setLeftBorderColor(borderColorIndex);
	cellStyle.setRightBorderColor(borderColorIndex);
	cellStyle.setBottomBorderColor(borderColorIndex);
	cellStyle.setTopBorderColor(borderColorIndex);

	Font font = sheet.getWorkbook().createFont();

	Short headerFontSize = (Short) this.getProperty(PROPERTY_HEADER_FONT_SIZE);
	logger.debug("Header font size : " + headerFontSize);
	short headerFontSizeShort = headerFontSize != null ? headerFontSize.shortValue() : DEFAULT_HEADER_FONT_SIZE;
	font.setFontHeightInPoints(headerFontSizeShort);

	String fontName = (String) this.getProperty(PROPERTY_FONT_NAME);
	logger.debug("Font name : " + fontName);
	fontName = fontName != null ? fontName : DEFAULT_FONT_NAME;
	font.setFontName(fontName);

	String color = (String) this.getProperty(PROPERTY_DIMENSION_NAME_COLOR);
	logger.debug("Dimension color : " + color);
	short colorIndex = bordeBorderColor != null ? IndexedColors.valueOf(color).getIndex() : IndexedColors.valueOf(DEFAULT_DIMENSION_NAME_COLOR).getIndex();
	font.setColor(colorIndex);

	font.setBoldweight(Font.BOLDWEIGHT_BOLD);
	font.setItalic(true);
	cellStyle.setFont(font);
	return cellStyle;
}