org.apache.poi.hssf.util.HSSFColor Java Examples

/**
 * exce表头单元格样式处理
 * @param workbook
 * @return
 */
public static HSSFCellStyle getTitleStyle(HSSFWorkbook workbook) {
	// 产生Excel表头
	HSSFCellStyle titleStyle = workbook.createCellStyle();
	titleStyle.setBorderBottom(HSSFCellStyle.BORDER_DOUBLE); // 设置边框样式
	titleStyle.setBorderLeft((short) 2); // 左边框
	titleStyle.setBorderRight((short) 2); // 右边框
	titleStyle.setBorderTop((short) 2); // 左边框
	titleStyle.setBorderBottom((short) 2); // 右边框
	titleStyle.setBorderTop(HSSFCellStyle.BORDER_DOUBLE); // 顶边框
	titleStyle.setFillForegroundColor(HSSFColor.SKY_BLUE.index); // 填充的背景颜色
	titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);

	titleStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); // 填充图案

	return titleStyle;
}
 

@Test
public void testFastExport() throws ResourceException, ReportProcessingException, IOException {
  // This establishes a baseline for the second test using the slow export.

  final MasterReport report = DebugReportRunner.parseLocalReport( "Prd-5391.prpt", Prd5391IT.class );
  final ByteArrayOutputStream bout = new ByteArrayOutputStream();
  FastExcelReportUtil.processXls( report, bout );

  final HSSFWorkbook wb = new HSSFWorkbook( new ByteArrayInputStream( bout.toByteArray() ) );
  final HSSFSheet sheetAt = wb.getSheetAt( 0 );
  final HSSFRow row = sheetAt.getRow( 0 );
  final HSSFCell cell0 = row.getCell( 0 );

  // assert that we are in the correct export type ..
  final HSSFCellStyle cellStyle = cell0.getCellStyle();
  final HSSFColor fillBackgroundColorColor = cellStyle.getFillBackgroundColorColor();
  final HSSFColor fillForegroundColorColor = cellStyle.getFillForegroundColorColor();
  Assert.assertEquals( "0:0:0", fillBackgroundColorColor.getHexString() );
  Assert.assertEquals( "FFFF:8080:8080", fillForegroundColorColor.getHexString() );

  HSSFFont font = cellStyle.getFont( wb );
  Assert.assertEquals( "Times New Roman", font.getFontName() );
}
 

public static ExtendedFormatRecord createExtendedFormat() {
    // CAUTION copied from org.apache.poi.hssf.model.InternalWorkbook#createExtendedFormat

    ExtendedFormatRecord retval = new ExtendedFormatRecord();

    retval.setFontIndex((short) 0);
    retval.setFormatIndex((short) 0x0);
    retval.setCellOptions((short) 0x1);
    retval.setAlignmentOptions((short) 0x20);
    retval.setIndentionOptions((short) 0);
    retval.setBorderOptions((short) 0);
    retval.setPaletteOptions((short) 0);
    retval.setAdtlPaletteOptions((short) 0);
    retval.setFillPaletteOptions((short) 0x20c0);
    retval.setTopBorderPaletteIdx(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
    retval.setBottomBorderPaletteIdx(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
    retval.setLeftBorderPaletteIdx(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
    retval.setRightBorderPaletteIdx(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
    return retval;
}
 

@Override
public MyContentProvider updateRowStyle(final ExportRow row)
{
  for (final ExportCell cell : row.getCells()) {
    final CellFormat format = cell.ensureAndGetCellFormat();
    format.setFillForegroundColor(HSSFColor.WHITE.index);
    switch (row.getRowNum()) {
      case 0:
        format.setFont(FONT_HEADER);
        break;
      case 1:
        format.setFont(FONT_NORMAL_BOLD);
        // alignment = CellStyle.ALIGN_CENTER;
        break;
      default:
        format.setFont(FONT_NORMAL);
        if (row.getRowNum() % 2 == 0) {
          format.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
        }
        break;
    }
  }
  return this;
}
 

/**
 * @throws TechnicalException
 *             is thrown if you have a technical error (format, configuration, datas, ...) in NoraUi.
 */
void openOutputData() throws TechnicalException {
    this.dataOutExtension = validExtension(dataOutPath);
    try (FileInputStream fileOut = new FileInputStream(dataOutPath + scenarioName + "." + dataOutExtension);) {
        initWorkbook(fileOut, dataOutExtension);
    } catch (final IOException e) {
        throw new TechnicalException(Messages.getMessage(TechnicalException.TECHNICAL_ERROR_MESSAGE_DATA_IOEXCEPTION), e);
    }

    styleSuccess = workbook.createCellStyle();
    final Font fontSuccess = workbook.createFont();
    fontSuccess.setColor(HSSFColor.HSSFColorPredefined.GREEN.getIndex());
    styleSuccess.setFont(fontSuccess);

    styleFailed = workbook.createCellStyle();
    final Font fontFailed = workbook.createFont();
    fontFailed.setColor(HSSFColor.HSSFColorPredefined.RED.getIndex());
    styleFailed.setFont(fontFailed);

    styleWarning = workbook.createCellStyle();
    final Font fontWarning = workbook.createFont();
    fontWarning.setColor(HSSFColor.HSSFColorPredefined.ORANGE.getIndex());
    styleWarning.setFont(fontWarning);
}
 

@Test
public void testSlowExport() throws ResourceException, ReportProcessingException, IOException {
  // This establishes a baseline for the second test using the slow export.

  final MasterReport report = DebugReportRunner.parseLocalReport( "Prd-5391.prpt", Prd5391IT.class );
  final ByteArrayOutputStream bout = new ByteArrayOutputStream();
  ExcelReportUtil.createXLS( report, bout );

  final HSSFWorkbook wb = new HSSFWorkbook( new ByteArrayInputStream( bout.toByteArray() ) );
  final HSSFSheet sheetAt = wb.getSheetAt( 0 );
  final HSSFRow row = sheetAt.getRow( 0 );
  final HSSFCell cell0 = row.getCell( 0 );

  // assert that we are in the correct export type ..
  final HSSFCellStyle cellStyle = cell0.getCellStyle();
  final HSSFColor fillBackgroundColorColor = cellStyle.getFillBackgroundColorColor();
  final HSSFColor fillForegroundColorColor = cellStyle.getFillForegroundColorColor();
  Assert.assertEquals( "0:0:0", fillBackgroundColorColor.getHexString() );
  Assert.assertEquals( "FFFF:8080:8080", fillForegroundColorColor.getHexString() );

  HSSFFont font = cellStyle.getFont( wb );
  Assert.assertEquals( "Times New Roman", font.getFontName() );
}
 

/**
 * 获取Excel标题单元格样式
 *
 * @param wb
 * @return
 */
public static CellStyle getHeadStyle(Workbook wb) {
    CellStyle style = wb.createCellStyle();
    style.setFillForegroundColor(HSSFColor.PALE_BLUE.index);
    style.setFillPattern(CellStyle.SOLID_FOREGROUND);
    style.setBorderTop(HSSFCellStyle.BORDER_THIN);
    style.setBorderRight(HSSFCellStyle.BORDER_THIN);
    style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
    style.setBorderLeft(HSSFCellStyle.BORDER_THIN);

    Font font = wb.createFont();
    font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); // 粗体
    style.setFont(font);
    style.setLocked(true);
    style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
    return style;
}
 

/**
 * exce表头单元格样式处理
 * @param workbook
 * @return
 */
public static HSSFCellStyle getTitleStyle(HSSFWorkbook workbook) {
	// 产生Excel表头
	HSSFCellStyle titleStyle = workbook.createCellStyle();
	titleStyle.setBorderBottom(HSSFCellStyle.BORDER_DOUBLE); // 设置边框样式
	titleStyle.setBorderLeft((short) 2); // 左边框
	titleStyle.setBorderRight((short) 2); // 右边框
	titleStyle.setBorderTop((short) 2); // 左边框
	titleStyle.setBorderBottom((short) 2); // 右边框
	titleStyle.setBorderTop(HSSFCellStyle.BORDER_DOUBLE); // 顶边框
	titleStyle.setFillForegroundColor(HSSFColor.SKY_BLUE.index); // 填充的背景颜色
	titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);

	titleStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); // 填充图案

	return titleStyle;
}
 

@Override
public MyContentProvider updateRowStyle(final ExportRow row)
{
  for (final ExportCell cell : row.getCells()) {
    final CellFormat format = cell.ensureAndGetCellFormat();
    format.setFillForegroundColor(HSSFColor.WHITE.index);
    switch (row.getRowNum()) {
      case 0:
        format.setFont(FONT_NORMAL_BOLD);
        // alignment = CellStyle.ALIGN_CENTER;
        break;
      default:
        format.setFont(FONT_NORMAL);
        if (row.getRowNum() % 2 == 0) {
          format.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
        }
        break;
    }
  }
  return this;
}
 

/**
 * Finds the closest matching color in the custom palette.  The
 * method for finding the distance between the colors is fairly
 * primative.
 *
 * @param red   The red component of the color to match.
 * @param green The green component of the color to match.
 * @param blue  The blue component of the color to match.
 * @return  The closest color or null if there are no custom
 *          colors currently defined.
 */
public HSSFColor findSimilarColor(int red, int green, int blue) {
    HSSFColor result = null;
    int minColorDistance = Integer.MAX_VALUE;
    byte[] b = _palette.getColor(PaletteRecord.FIRST_COLOR_INDEX);
    for (short i = PaletteRecord.FIRST_COLOR_INDEX; b != null;
        b = _palette.getColor(++i))
    {
        int colorDistance = Math.abs(red - unsignedInt(b[0])) +
        	Math.abs(green - unsignedInt(b[1])) +
        	Math.abs(blue - unsignedInt(b[2]));
        if (colorDistance < minColorDistance)
        {
            minColorDistance = colorDistance;
            result = getColor(i);
        }
    }
    return result;
}
 

/**
 *
 */
protected HSSFColor getNearestColor(Color awtColor)
{
	HSSFColor color = hssfColorsCache.get(awtColor);		
	if (color == null)
	{
		int minDiff = Integer.MAX_VALUE;
		for (Map.Entry<HSSFColor, short[]> hssfColorEntry : hssfColorsRgbs.entrySet())
		{
			HSSFColor crtColor = hssfColorEntry.getKey();
			short[] rgb = hssfColorEntry.getValue();
			
			int diff = Math.abs(rgb[0] - awtColor.getRed()) + Math.abs(rgb[1] - awtColor.getGreen()) + Math.abs(rgb[2] - awtColor.getBlue());

			if (diff < minDiff)
			{
				minDiff = diff;
				color = crtColor;
			}
		}

		hssfColorsCache.put(awtColor, color);
	}
	return color;
}
 

/**
 * Get an HSSFPalette index for a workbook that closely approximates the passed in colour.
 * @param workbook
 * The workbook for which the colour is being sought.
 * @param colour
 * The colour, in the form "rgb(<i>r</i>, <i>g</i>, <i>b</i>)".
 * @return
 * The index into the HSSFPallete for the workbook for a colour that approximates the passed in colour.
 */
private short getHColour( HSSFWorkbook workbook, String colour ) {
	int[] rgbInt = ColorUtil.getRGBs(colour);
	if( rgbInt == null ) {
		return 0;
	}
	
	byte[] rgbByte = new byte[] { (byte)rgbInt[0], (byte)rgbInt[1], (byte)rgbInt[2] };
	HSSFPalette palette = workbook.getCustomPalette();
	
	HSSFColor result = palette.findColor(rgbByte[0], rgbByte[1], rgbByte[2]);
	if( result == null) {
		if( paletteIndex > minPaletteIndex ) {
			--paletteIndex;
			palette.setColorAtIndex(paletteIndex, rgbByte[0], rgbByte[1], rgbByte[2]);
			return paletteIndex;
		} else {
			result = palette.findSimilarColor(rgbByte[0], rgbByte[1], rgbByte[2]);
		}
	}
	return result.getIndex();
}
 

public FieldInfo(String name, int order, String format, int width, String defaultValue, Method method, String
        mergeTo, String separator, String string, int[] tags, Class<HSSFColor> color, String expression, String filedName) {
    this.name = name;
    this.order = order;
    this.format = format;
    this.width = width;
    this.method = method;
    this.defaultValue = defaultValue;
    this.mergeTo = mergeTo;
    this.separator = separator;
    this.string = string;
    this.tags = tags;
    this.color = getInstance(color);
    this.expression = expression;
    this.filedName = filedName;
}
 

/**
 * 根据格式，创建返回样式对象
 *
 * @param format 格式
 * @return 样式对象
 */
public CellStyle getCustomFormatStyle(String format,HSSFColor color) {

    //存在对应格式直接返回
    if (customFormatStyleMap.containsKey(format) && color== null) {
        return customFormatStyleMap.get(format);
    }
    CellStyle customDateStyle = workbook.createCellStyle();
    if (!buildInFormatMap.containsKey(DATA_FORMAT_KEY)) {
        DataFormat dataFormat = workbook.createDataFormat();
        buildInFormatMap.put(DATA_FORMAT_KEY, dataFormat);
    }
    customDateStyle.setDataFormat(buildInFormatMap.get(DATA_FORMAT_KEY).getFormat(format));
    if (color == null){
        //放入map缓存
        customFormatStyleMap.put(format, customDateStyle);
    }else {
        customDateStyle.setFillForegroundColor(color.getIndex());
        customDateStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
    }
    this.setCommonStyle(customDateStyle);

    return customDateStyle;
}
 

@Override
public MyContentProvider updateRowStyle(final ExportRow row)
{
  for (final ExportCell cell : row.getCells()) {
    final CellFormat format = cell.ensureAndGetCellFormat();
    format.setFillForegroundColor(HSSFColor.WHITE.index);
    switch (row.getRowNum()) {
      case 0:
        format.setFont(FONT_HEADER);
        break;
      case 1:
        format.setFont(FONT_NORMAL_BOLD);
        // alignment = CellStyle.ALIGN_CENTER;
        break;
      default:
        format.setFont(FONT_NORMAL);
        if (row.getRowNum() % 2 == 0) {
          format.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
        }
        break;
    }
  }
  return this;
}
 

/**
 * 创建颜色。
 * 
 * 写本方法的原因是：从2003版本的模板中复制单元格颜色时，会出现颜色失真的问题。
 * 
 * 但最终也没有解决。因为：当单元格的颜色设置为非标准颜色时，就会失真，但设置为标准颜色时，是正常的。
 * 
 * 也因为此，本方法与 i_ToCellStyle.setFillBackgroundColor(i_FromCellStyle.getFillBackgroundColor()); 的效果是相同的。
 * 
 * 本方法作为研究使用而保留下来，但不没有使用价值。
 * 
 * @author      ZhengWei(HY)
 * @createDate  2017-03-21
 * @version     v1.0
 *
 * @param i_FromColor
 * @param i_DataWorkbook
 * @return
 */
@Deprecated
public final static HSSFColor createColor(HSSFColor i_FromColor ,HSSFWorkbook i_DataWorkbook)
{
    short [] v_RGBHex    = i_FromColor.getTriplet();
    byte     v_ByteRed   = (byte)v_RGBHex[0];
    byte     v_ByteGreen = (byte)v_RGBHex[1];
    byte     v_ByteBlue  = (byte)v_RGBHex[2];
    
    HSSFPalette v_Palette   = i_DataWorkbook.getCustomPalette();
    HSSFColor   v_DataColor = v_Palette.findColor(v_ByteRed ,v_ByteGreen ,v_ByteBlue);
    
    if ( v_DataColor == null )
    {
        v_Palette.setColorAtIndex(i_FromColor.getIndex() ,v_ByteRed ,v_ByteGreen ,v_ByteBlue);
        
        return v_Palette.getColor(i_FromColor.getIndex());
    }
    
    return  v_DataColor;
}
 

public void testBug() throws ResourceException, IOException, ReportProcessingException {
  final MasterReport report = DebugReportRunner.parseGoldenSampleReport( "Prd-3889.prpt" );
  final ByteArrayOutputStream bout = new ByteArrayOutputStream();
  ExcelReportUtil.createXLS( report, bout );

  final HSSFWorkbook wb = new HSSFWorkbook( new ByteArrayInputStream( bout.toByteArray() ) );
  final HSSFSheet sheetAt = wb.getSheetAt( 0 );
  final HSSFRow row = sheetAt.getRow( 0 );
  final HSSFCell cell0 = row.getCell( 0 );

  // assert that we are in the correct export type ..
  final HSSFCellStyle cellStyle = cell0.getCellStyle();
  final HSSFColor fillBackgroundColorColor = cellStyle.getFillBackgroundColorColor();
  final HSSFColor fillForegroundColorColor = cellStyle.getFillForegroundColorColor();
  assertEquals( "0:0:0", fillBackgroundColorColor.getHexString() );
  assertEquals( "FFFF:FFFF:9999", fillForegroundColorColor.getHexString() );

  // assert that there are no extra columns ..
  final HSSFRow row8 = sheetAt.getRow( 7 );
  assertNull( row8 );

}
 

public static HSSFCellStyle getTwoStyle(HSSFWorkbook workbook, boolean isWarp) {
	HSSFCellStyle style = workbook.createCellStyle();
	style.setBorderLeft((short) 1); // 左边框
	style.setBorderRight((short) 1); // 右边框
	style.setBorderBottom((short) 1);
	style.setBorderTop((short) 1);
	style.setFillForegroundColor(HSSFColor.LIGHT_TURQUOISE.index); // 填充的背景颜色
	style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); // 填充图案
	style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
	style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
	if(isWarp){style.setWrapText(true);}
	return style;
}
 

/**
 * exce表头单元格样式处理
 * @param workbook
 * @return
 */
public static HSSFCellStyle getTitleStyle(HSSFWorkbook workbook) {
	// 产生Excel表头
	HSSFCellStyle titleStyle = workbook.createCellStyle();
	titleStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); // 左边框
	titleStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); // 右边框
	titleStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 底边框
	titleStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); // 顶边框
	titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
	titleStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); // 填充的背景颜色
	titleStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); // 填充图案

	return titleStyle;
}
 

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

/**
 * create the styles in the workbook
 */
private void createStyles(Workbook wb) {
	// create the styles
	this.checkboxStyle = wb.createCellStyle();
	this.checkboxStyle.setAlignment(HorizontalAlignment.CENTER);
	this.checkboxStyle.setVerticalAlignment(VerticalAlignment.CENTER);
	this.checkboxStyle.setBorderBottom(BorderStyle.THIN);
	this.checkboxStyle.setBorderLeft(BorderStyle.THIN);
	this.checkboxStyle.setBorderRight(BorderStyle.THIN);
	this.checkboxStyle.setBorderTop(BorderStyle.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"));
	
	this.greenWrapped = createLeftWrapStyle(wb);
	this.greenWrapped.setFillForegroundColor(HSSFColor.LIGHT_GREEN.index);
	this.greenWrapped.setFillPattern(FillPatternType.SOLID_FOREGROUND);
	this.greenWrapped.setFillPattern(FillPatternType.SOLID_FOREGROUND);
	
	this.yellowWrapped = createLeftWrapStyle(wb);
	this.yellowWrapped.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index);
	this.yellowWrapped.setFillPattern(FillPatternType.SOLID_FOREGROUND);
	
	this.redWrapped = createLeftWrapStyle(wb);
	this.redWrapped.setFillForegroundColor(HSSFColor.RED.index);
	this.redWrapped.setFillPattern(FillPatternType.SOLID_FOREGROUND);
}
 

public DynamicExcelColorProducer( final HSSFWorkbook workbook ) {
  if ( workbook == null ) {
    throw new NullPointerException();
  }
  this.workbook = workbook;
  this.usedTripplets = new HashMap<String, HSSFColor>();
}
 

/**
 * header样式
 *
 * @return CellStyle
 */
public CellStyle getHeaderStyle() {

    if (buildInStyleMap.containsKey(HEADER_STYLE_KEY)) {
        return buildInStyleMap.get(HEADER_STYLE_KEY);
    }

    CellStyle headerStyle = workbook.createCellStyle();//头的样式
    // 设置单元格的背景颜色为淡蓝色
    headerStyle.setFillForegroundColor(HSSFColor.PALE_BLUE.index);
    headerStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
    this.setCommonStyle(headerStyle);
    buildInStyleMap.put(HEADER_STYLE_KEY, headerStyle);
    return headerStyle;
}
 

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 static Short getPredefinedColorIndex(String color) {
    HSSFColor.HSSFColorPredefined colorPredefined = COLOR_PREDEFINED_MAP.get(color);
    if (Objects.isNull(colorPredefined)) {
        return null;
    }
    return colorPredefined.getIndex();
}
 

/**
 * 设置内容样式，占用过大，并没有开启这个功能.
 *
 * @param workbook
 * @param contentStyle
 * @return org.apache.poi.ss.usermodel.CellStyle
 */
private static CellStyle getContentStyle(Workbook workbook, ExcelStyle contentStyle) {
    contentStyle = contentStyle == null ? new ExcelStyle() : contentStyle;
    CellStyle cellStyle = workbook.createCellStyle();

    // 对齐方式
    cellStyle.setAlignment(contentStyle.getAlign() == null ? HorizontalAlignment.LEFT : contentStyle.getAlign());

    // 设置字体
    Font font = workbook.createFont();
    String fontName = contentStyle.getFontName();
    font.setFontName(null == fontName ? "黑体" : fontName);
    // 字体大小
    font.setFontHeightInPoints(contentStyle.getSize() <= 0 ? 12 : contentStyle.getSize());
    font.setBold(contentStyle.isBold());
    font.setColor(contentStyle.getFontColor() <= 0 ? HSSFColor.HSSFColorPredefined.BLACK.getIndex() : contentStyle.getFontColor());
    cellStyle.setFont(font);

    // 背景
    cellStyle.setFillForegroundColor(contentStyle.getBackColor() <= 0
            ? HSSFColor.HSSFColorPredefined.WHITE.getIndex()
            : contentStyle.getBackColor());
    cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);

    BorderStyle border = contentStyle.getBorderStyle() == null ? BorderStyle.THIN : contentStyle.getBorderStyle();
    // 边框
    cellStyle.setBorderLeft(border);
    cellStyle.setBorderTop(border);
    cellStyle.setBorderRight(border);
    cellStyle.setBorderBottom(border);

    // 自动换行
    cellStyle.setWrapText(true);
    return cellStyle;
}
 

/**
 * 设置头样式.
 *
 * @param workbook
 * @param headStyle
 * @return org.apache.poi.ss.usermodel.CellStyle
 */
private static CellStyle getTitleStyle(Workbook workbook, ExcelStyle headStyle) {
    CellStyle cellStyle = workbook.createCellStyle();
    headStyle = headStyle == null ? new ExcelStyle() : headStyle;

    // 对齐方式
    cellStyle.setAlignment(headStyle.getAlign() == null ? HorizontalAlignment.CENTER : headStyle.getAlign());

    // 设置字体
    Font font = workbook.createFont();
    String fontName = headStyle.getFontName();
    font.setFontName(null == fontName ? "黑体" : fontName);
    // 字体大小
    font.setFontHeightInPoints(headStyle.getSize() <= 0 ? 14 : headStyle.getSize());
    font.setBold(true);
    font.setColor(headStyle.getFontColor() <= 0 ? HSSFColor.HSSFColorPredefined.BLACK.getIndex() : headStyle.getFontColor());
    cellStyle.setFont(font);

    // 背景
    cellStyle.setFillForegroundColor(headStyle.getBackColor() <= 0
            ? HSSFColor.HSSFColorPredefined.SEA_GREEN.getIndex()
            : headStyle.getBackColor());
    cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);

    BorderStyle border = headStyle.getBorderStyle() == null ? BorderStyle.MEDIUM : headStyle.getBorderStyle();
    // 边框
    cellStyle.setBorderLeft(border);
    cellStyle.setBorderTop(border);
    cellStyle.setBorderRight(border);
    cellStyle.setBorderBottom(border);

    // 设置自动换行
    cellStyle.setWrapText(true);
    return cellStyle;
}
 

/**
 * 首列样式
 * @return
 */
@SuppressWarnings("deprecation")
private CellStyle createFirstCellStyle(){
	CellStyle cellStyle = baseCellStyle();
	Font font = wb.createFont();
	font.setFontHeight((short) 180);
	cellStyle.setFont(font);
	
	cellStyle.setFillForegroundColor(HSSFColor.LIGHT_GREEN.index);
	cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
	

	return cellStyle;
}
 

private static HSSFCellStyle createTilteStyle(HSSFWorkbook wb) {
    HSSFFont bold = wb.createFont();
    bold.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);

    HSSFCellStyle style = wb.createCellStyle();
    style.setFont(bold);
    style.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
    style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
    style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
    style.setWrapText(true);

    return style;
}
 

/**
 * 设置表头的单元格样式
 * 
 * @return
 */
public XSSFCellStyle getHeadStyle() {
	// 创建单元格样式
	XSSFCellStyle cellStyle = wb.createCellStyle();
	// 设置单元格的背景颜色为淡蓝色
	cellStyle.setFillForegroundColor(HSSFColor.PALE_BLUE.index);
	// 创建单元格内容显示不下时自动换行
	//cellStyle.setWrapText(true);
	// 设置单元格字体样式
	XSSFFont font = wb.createFont();
	// 设置字体加粗
	font.setFontName("宋体");
	font.setFontHeight((short) 200);
	cellStyle.setFont(font);
	return cellStyle;
}