org.apache.poi.xssf.usermodel.XSSFColor Java Examples

@Test
public void testXlsx_BackgroundStyle() {
  when( workbook.createCellStyle() ).thenReturn( xlsxStyle );
  ExcelCellStyleBuilder builder = new ExcelCellStyleBuilder( workbook );

  CellBackground bg = getBackground();
  HSSFCellStyleProducer.HSSFCellStyleKey styleKey = getXlsxKey();

  builder.withBackgroundStyle( bg, styleKey );

  verify( xlsxStyle, times( 1 ) ).setBorderBottom( eq( BorderStyle.DASH_DOT ) );
  verify( xlsxStyle, times( 1 ) )
    .setBorderColor( eq( XSSFCellBorder.BorderSide.BOTTOM ), notNull( XSSFColor.class ) );
  verify( xlsxStyle, times( 1 ) ).setBorderTop( eq( BorderStyle.DOTTED ) );
  verify( xlsxStyle, times( 1 ) ).setBorderColor( eq( XSSFCellBorder.BorderSide.TOP ), notNull( XSSFColor.class ) );
  verify( xlsxStyle, times( 1 ) ).setBorderLeft( eq( BorderStyle.DASH_DOT_DOT ) );
  verify( xlsxStyle, times( 1 ) ).setBorderColor( eq( XSSFCellBorder.BorderSide.LEFT ), notNull( XSSFColor.class ) );
  verify( xlsxStyle, times( 1 ) ).setBorderRight( eq( BorderStyle.DASHED ) );
  verify( xlsxStyle, times( 1 ) ).setBorderColor( eq( XSSFCellBorder.BorderSide.RIGHT ), notNull( XSSFColor.class ) );
  verify( xlsxStyle, times( 1 ) ).setFillForegroundColor( notNull( XSSFColor.class ) );
  verify( xlsxStyle, times( 1 ) ).setFillPattern( eq( FillPatternType.SOLID_FOREGROUND ) );
}
 

void xlsx_backgroundStyle( final CellBackground bg, final HSSFCellStyleProducer.HSSFCellStyleKey styleKey ) {
  final XSSFCellStyle xssfCellStyle = (XSSFCellStyle) hssfCellStyle;
  if ( BorderStyle.NONE.equals( bg.getBottom().getBorderStyle() ) == false ) {
    hssfCellStyle.setBorderBottom( styleKey.getBorderStrokeBottom() );
    xssfCellStyle.setBorderColor( XSSFCellBorder.BorderSide.BOTTOM, new XSSFColor( styleKey.getExtendedColorBottom() ) );
  }
  if ( BorderStyle.NONE.equals( bg.getTop().getBorderStyle() ) == false ) {
    hssfCellStyle.setBorderTop( styleKey.getBorderStrokeTop() );
    xssfCellStyle.setBorderColor( XSSFCellBorder.BorderSide.TOP, new XSSFColor( styleKey.getExtendedColorTop() ) );
  }
  if ( BorderStyle.NONE.equals( bg.getLeft().getBorderStyle() ) == false ) {
    hssfCellStyle.setBorderLeft( styleKey.getBorderStrokeLeft() );
    xssfCellStyle.setBorderColor( XSSFCellBorder.BorderSide.LEFT, new XSSFColor( styleKey.getExtendedColorLeft() ) );
  }
  if ( BorderStyle.NONE.equals( bg.getRight().getBorderStyle() ) == false ) {
    hssfCellStyle.setBorderRight( styleKey.getBorderStrokeRight() );
    xssfCellStyle.setBorderColor( XSSFCellBorder.BorderSide.RIGHT, new XSSFColor( styleKey.getExtendedColorRight() ) );
  }
  if ( bg.getBackgroundColor() != null ) {
    xssfCellStyle.setFillForegroundColor( new XSSFColor( styleKey.getExtendedColor() ) );
    hssfCellStyle.setFillPattern( FillPatternType.SOLID_FOREGROUND );
  }
}
 

@Override
public Font correctFontColorIfBackground( FontManager fm, Workbook wb, BirtStyle birtStyle, Font font ) {
	CSSValue bgColour = birtStyle.getProperty( StyleConstants.STYLE_BACKGROUND_COLOR );
	int bgRgb[] = parseColour( bgColour == null ? null : bgColour.getCssText(), "white" );

	XSSFColor colour = ((XSSFFont)font).getXSSFColor();
	int fgRgb[] = rgbOnly( colour.getARgb() );
	if( ( fgRgb[0] == 255 ) && ( fgRgb[1] == 255 ) && ( fgRgb[2] == 255 ) ) {
		fgRgb[0]=fgRgb[1]=fgRgb[2]=0;
	} else if( ( fgRgb[0] == 0 ) && ( fgRgb[1] == 0 ) && ( fgRgb[2] == 0 ) ) {
		fgRgb[0]=fgRgb[1]=fgRgb[2]=255;
	}

	if( ( bgRgb[ 0 ] == fgRgb[ 0 ] ) && ( bgRgb[ 1 ] == fgRgb[ 1 ] ) && ( bgRgb[ 2 ] == fgRgb[ 2 ] ) ) {
		
		IStyle addedStyle = new AreaStyle( fm.getCssEngine() );
		addedStyle.setColor( contrastColour( bgRgb ) );
		
		return fm.getFontWithExtraStyle( font, addedStyle );
	} else {
		return font;
	}
}
 

@Override
public void addBackgroundColourToStyle(Workbook workbook, CellStyle style, String colour) {
	if(colour == null) {
		return ;
	}
	if(IStyle.TRANSPARENT_VALUE.equals(colour)) {
		return ;
	}
	if(style instanceof XSSFCellStyle) {
		XSSFCellStyle cellStyle = (XSSFCellStyle)style;
		XSSFColor xColour = getXColour(colour);
		if(xColour != null) {
			cellStyle.setFillForegroundColor(xColour);
			cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
		}
	}
}
 

@Override
public void addColourToFont(Workbook workbook, Font font, String colour) {
	if(colour == null) {
		return ;
	}
	if(IStyle.TRANSPARENT_VALUE.equals(colour)) {
		return ;
	}
	if(font instanceof XSSFFont) {
		XSSFFont xFont = (XSSFFont)font;
		XSSFColor xColour = getXColour(colour);
		
		if(xColour != null) {
			xFont.setColor(xColour);
		}
	}
}
 

public void styleColor(Formatter out, String attr, Color color) {
	XSSFColor xSSFColor = (XSSFColor) color;
	if (color == null || xSSFColor.isAuto())
		return;

	byte[] rgb = xSSFColor.getRgb();
	if (rgb == null) {
		return;
	}
	out.format("  %s: #%02x%02x%02x;%n", attr, rgb[0], rgb[1], rgb[2]);
}
 

private static Color createColor( final XSSFColor color ) {
  if ( color == null ) {
    return null;
  }
  final byte[] rgb = color.getRGB();
  return new Color( 0xFF & rgb[0], 0xFF & rgb[1], 0xFF & rgb[2] );
}
 

@Test
public void testExternalCss() throws BirtException, IOException {

	debug = false;
	InputStream inputStream = runAndRenderReport("Issue24.rptdesign", "xlsx");
	assertNotNull(inputStream);
	try {
		XSSFWorkbook workbook = new XSSFWorkbook(inputStream);
		assertNotNull(workbook);
		
		assertEquals( 1, workbook.getNumberOfSheets() );

		Sheet sheet = workbook.getSheetAt(0);
		assertEquals( 3, this.firstNullRow(sheet));

		assertEquals( "FF206090",              ((XSSFColor)sheet.getRow(1).getCell(0).getCellStyle().getFillForegroundColorColor()).getARGBHex());
		assertEquals( "FF206090",              ((XSSFColor)sheet.getRow(1).getCell(1).getCellStyle().getFillForegroundColorColor()).getARGBHex());
		assertEquals( "FF206090",              ((XSSFColor)sheet.getRow(1).getCell(2).getCellStyle().getFillForegroundColorColor()).getARGBHex());
		assertEquals( "FF206090",              ((XSSFColor)sheet.getRow(1).getCell(3).getCellStyle().getFillForegroundColorColor()).getARGBHex());
		
		assertEquals( "FF6495ED",              ((XSSFColor)sheet.getRow(2).getCell(0).getCellStyle().getFillForegroundColorColor()).getARGBHex());
		assertEquals( "FF6495ED",              ((XSSFColor)sheet.getRow(2).getCell(1).getCellStyle().getFillForegroundColorColor()).getARGBHex());
		assertEquals( "FF6495ED",              ((XSSFColor)sheet.getRow(2).getCell(2).getCellStyle().getFillForegroundColorColor()).getARGBHex());
		assertEquals( "FF6495ED",              ((XSSFColor)sheet.getRow(2).getCell(3).getCellStyle().getFillForegroundColorColor()).getARGBHex());

		assertEquals( "Customer",              sheet.getRow(1).getCell(0).getStringCellValue());
		assertEquals( "Given Name",            sheet.getRow(1).getCell(1).getStringCellValue());
		assertEquals( "Family Name",           sheet.getRow(1).getCell(2).getStringCellValue());
		assertEquals( "Phone",                 sheet.getRow(1).getCell(3).getStringCellValue());
	
	} finally {
		inputStream.close();
	}
}
 

private XSSFColor getXColour(String colour) {
	int[] rgbInt = ColorUtil.getRGBs(colour);
	if( rgbInt == null ) {
		return null;
	}
	byte[] rgbByte = { (byte)-1, (byte)rgbInt[0], (byte)rgbInt[1], (byte)rgbInt[2] };
	// System.out.println( "The X colour for " + colour + " is [ " + rgbByte[0] + "," + rgbByte[1] + "," + rgbByte[2] + "," + rgbByte[3] + "]" );
	XSSFColor result = new XSSFColor( rgbByte );
	return result;		
}
 

@Override
public void applyBorderStyle(Workbook workbook, CellStyle style, BorderSide side, CSSValue colour, CSSValue borderStyle, CSSValue width) {
	if( ( colour != null ) || ( borderStyle != null ) || ( width != null ) ) {
		String colourString = colour == null ? "rgb(0,0,0)" : colour.getCssText();
		String borderStyleString = borderStyle == null ? "solid" : borderStyle.getCssText();
		String widthString = width == null ? "medium" : width.getCssText();
		
		if( style instanceof XSSFCellStyle ) {
			XSSFCellStyle xStyle = (XSSFCellStyle)style;
			
			BorderStyle xBorderStyle = poiBorderStyleFromBirt(borderStyleString, widthString);
			XSSFColor xBorderColour = getXColour(colourString);
			if(xBorderStyle != BorderStyle.NONE) {
				switch( side ) {
				case TOP:
					xStyle.setBorderTop(xBorderStyle);
					xStyle.setTopBorderColor(xBorderColour);
					// log.debug( "Top border: " + xStyle.getBorderTop() + " / " + xStyle.getTopBorderXSSFColor().getARGBHex() );
					break;
				case LEFT:
					xStyle.setBorderLeft(xBorderStyle);
					xStyle.setLeftBorderColor(xBorderColour);
					// log.debug( "Left border: " + xStyle.getBorderLeft() + " / " + xStyle.getLeftBorderXSSFColor().getARGBHex() );
					break;
				case RIGHT:
					xStyle.setBorderRight(xBorderStyle);
					xStyle.setRightBorderColor(xBorderColour);
					// log.debug( "Right border: " + xStyle.getBorderRight() + " / " + xStyle.getRightBorderXSSFColor().getARGBHex() );
					break;
				case BOTTOM:
					xStyle.setBorderBottom(xBorderStyle);
					xStyle.setBottomBorderColor(xBorderColour);
					// log.debug( "Bottom border: " + xStyle.getBorderBottom() + " / " + xStyle.getBottomBorderXSSFColor().getARGBHex() );
					break;
				}
			}
		}
	}
}
 

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

public static XSSFCellStyle setStyle(XSSFWorkbook workbook) {  
    //设置字体;  
    XSSFFont font = workbook.createFont();  
    //设置字体大小;  
    font.setFontHeightInPoints((short) 20);  
    //设置字体名字;  
    font.setFontName("Courier New");  
    //font.setItalic(true);  
    //font.setStrikeout(true);  
    //设置样式;  
    XSSFCellStyle style = workbook.createCellStyle();  
    //设置底边框;  
    style.setBorderBottom(XSSFCellStyle.BORDER_THIN);  
    //设置底边框颜色;  
    style.setBottomBorderColor(new XSSFColor(Color.BLACK));  
    //设置左边框;  
    style.setBorderLeft(XSSFCellStyle.BORDER_THIN);  
    //设置左边框颜色;  
    style.setLeftBorderColor(new XSSFColor(Color.BLACK));  
    //设置右边框;  
    style.setBorderRight(XSSFCellStyle.BORDER_THIN);  
    //设置右边框颜色;  
    style.setRightBorderColor(new XSSFColor(Color.BLACK));  
    //设置顶边框;  
    style.setBorderTop(XSSFCellStyle.BORDER_THIN);  
    //设置顶边框颜色;  
    style.setTopBorderColor(new XSSFColor(Color.BLACK));  
    //在样式用应用设置的字体;  
    style.setFont(font);  
    //设置自动换行;  
    style.setWrapText(false);  
    //设置水平对齐的样式为居中对齐;  
    style.setAlignment(XSSFCellStyle.ALIGN_CENTER);  
    //设置垂直对齐的样式为居中对齐;  
    style.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);  
    return style;  
}
 

public static XSSFCellStyle setStyle(XSSFWorkbook workbook) {  
    //设置字体;  
    XSSFFont font = workbook.createFont();  
    //设置字体大小;  
    font.setFontHeightInPoints((short) 20);  
    //设置字体名字;  
    font.setFontName("Courier New");  
    //font.setItalic(true);  
    //font.setStrikeout(true);  
    //设置样式;  
    XSSFCellStyle style = workbook.createCellStyle();  
    //设置底边框;  
    style.setBorderBottom(XSSFCellStyle.BORDER_THIN);  
    //设置底边框颜色;  
    style.setBottomBorderColor(new XSSFColor(Color.BLACK));  
    //设置左边框;  
    style.setBorderLeft(XSSFCellStyle.BORDER_THIN);  
    //设置左边框颜色;  
    style.setLeftBorderColor(new XSSFColor(Color.BLACK));  
    //设置右边框;  
    style.setBorderRight(XSSFCellStyle.BORDER_THIN);  
    //设置右边框颜色;  
    style.setRightBorderColor(new XSSFColor(Color.BLACK));  
    //设置顶边框;  
    style.setBorderTop(XSSFCellStyle.BORDER_THIN);  
    //设置顶边框颜色;  
    style.setTopBorderColor(new XSSFColor(Color.BLACK));  
    //在样式用应用设置的字体;  
    style.setFont(font);  
    //设置自动换行;  
    style.setWrapText(false);  
    //设置水平对齐的样式为居中对齐;  
    style.setAlignment(XSSFCellStyle.ALIGN_CENTER);  
    //设置垂直对齐的样式为居中对齐;  
    style.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);  
    return style;  
}
 

public void styleColor(Formatter out, String attr, Color color) {
    XSSFColor xSSFColor = (XSSFColor) color;
    if (color == null || xSSFColor.isAuto())
        return;

    byte[] rgb = xSSFColor.getRgb();
    if (rgb == null) {
        return;
    }
    out.format("  %s: #%02x%02x%02x;%n", attr, rgb[0], rgb[1], rgb[2]);
}
 

private XSSFCellStyle createXSSFCellStyle(Workbook wb, int[] bgColor, int[] fontColor, int fontSize) {
	SXSSFWorkbook workbook = (SXSSFWorkbook) wb;
	XSSFFont titleFont = (XSSFFont) workbook.createFont();
	titleFont.setCharSet(HSSFFont.DEFAULT_CHARSET);
	titleFont.setFontName("宋体");

	XSSFColor color9 = new XSSFColor(new java.awt.Color(fontColor[0], fontColor[1], fontColor[2]));
	XSSFColor color10 = new XSSFColor(new java.awt.Color(bgColor[0], bgColor[1], bgColor[2]));
	
	if (!(fontColor[0] == 0 && fontColor[1] == 0 && fontColor[2] == 0)) {
		titleFont.setColor(color9);
	}
	titleFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
	titleFont.setFontHeightInPoints((short) fontSize);

	XSSFCellStyle titleStyle = (XSSFCellStyle) createBorderCellStyle(workbook, true);
	titleStyle.setFont(titleFont);
	titleStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
	titleStyle.setFillForegroundColor(color10);
	titleStyle.setAlignment(CellStyle.ALIGN_CENTER);
	titleStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);

	return titleStyle;
}
 

public static void setBorderColor(Workbook workbook, CellStyle cellStyle, Color[] color) {
    if (color == null) {
        return;
    }
    if (cellStyle instanceof XSSFCellStyle) {
        ((XSSFCellStyle) cellStyle).setTopBorderColor(new XSSFColor(color[0]));
        ((XSSFCellStyle) cellStyle).setRightBorderColor(new XSSFColor(color[1]));
        ((XSSFCellStyle) cellStyle).setBottomBorderColor(new XSSFColor(color[2]));
        ((XSSFCellStyle) cellStyle).setLeftBorderColor(new XSSFColor(color[3]));
    } else if (cellStyle instanceof HSSFCellStyle && workbook instanceof HSSFWorkbook) {
        cellStyle.setTopBorderColor(getSimilarColor((HSSFWorkbook) workbook, color[0]).getIndex());
        cellStyle.setRightBorderColor(getSimilarColor((HSSFWorkbook) workbook, color[1]).getIndex());
        cellStyle.setBottomBorderColor(getSimilarColor((HSSFWorkbook) workbook, color[2]).getIndex());
        cellStyle.setLeftBorderColor(getSimilarColor((HSSFWorkbook) workbook, color[3]).getIndex());
    } else {
        log.error("unknown font type");
    }
}
 

public CellStyle getHeaderCellStyle(SXSSFWorkbook workbook) {
    if (headCellStyle == null) {
        headCellStyle = workbook.getXSSFWorkbook().createCellStyle();
        headCellStyle.setBorderTop(BorderStyle.NONE);
        headCellStyle.setBorderRight(BorderStyle.NONE);
        headCellStyle.setBorderBottom(BorderStyle.NONE);
        headCellStyle.setBorderLeft(BorderStyle.NONE);
        headCellStyle.setAlignment(HorizontalAlignment.CENTER);
        headCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        XSSFColor color = new XSSFColor(new java.awt.Color(217, 217, 217));
        headCellStyle.setFillForegroundColor(color);
        headCellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        Font font = workbook.createFont();
        font.setFontName("微软雅黑");
        font.setColor(IndexedColors.ROYAL_BLUE.index);
        font.setBold(true);
        headCellStyle.setFont(font);
        headCellStyle.setDataFormat(workbook.createDataFormat().getFormat("@"));
    }
    return headCellStyle;
}
 

/**
 * 设置表头
 *
 * @param wb
 * @param sheet
 * @param titles
 * @return
 */
private static int writeTitlesToExcel(XSSFWorkbook wb, Sheet sheet, List<String> titles) {
    int rowIndex = 0;
    int colIndex = 0;
    Font titleFont = wb.createFont();
    //设置字体
    titleFont.setFontName("simsun");
    //设置粗体
    titleFont.setBoldweight(Short.MAX_VALUE);
    //设置字号
    titleFont.setFontHeightInPoints((short) 14);
    //设置颜色
    titleFont.setColor(IndexedColors.BLACK.index);
    XSSFCellStyle titleStyle = wb.createCellStyle();
    //水平居中
    titleStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);
    //垂直居中
    titleStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
    //设置图案颜色
    titleStyle.setFillForegroundColor(new XSSFColor(new Color(182, 184, 192)));
    //设置图案样式
    titleStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
    titleStyle.setFont(titleFont);
    setBorder(titleStyle, BorderStyle.THIN, new XSSFColor(new Color(0, 0, 0)));
    Row titleRow = sheet.createRow(rowIndex);
    titleRow.setHeightInPoints(25);
    colIndex = 0;
    for (String field : titles) {
        Cell cell = titleRow.createCell(colIndex);
        cell.setCellValue(field);
        cell.setCellStyle(titleStyle);
        colIndex++;
    }
    rowIndex++;
    return rowIndex;
}
 

/**
 * 设置内容
 *
 * @param wb
 * @param sheet
 * @param rows
 * @param rowIndex
 * @return
 */
private static int writeRowsToExcel(XSSFWorkbook wb, Sheet sheet, List<List<Object>> rows, int rowIndex) {
    int colIndex;
    Font dataFont = wb.createFont();
    dataFont.setFontName("simsun");
    dataFont.setFontHeightInPoints((short) 14);
    dataFont.setColor(IndexedColors.BLACK.index);

    XSSFCellStyle dataStyle = wb.createCellStyle();
    dataStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);
    dataStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
    dataStyle.setFont(dataFont);
    setBorder(dataStyle, BorderStyle.THIN, new XSSFColor(new Color(0, 0, 0)));
    for (List<Object> rowData : rows) {
        Row dataRow = sheet.createRow(rowIndex);
        dataRow.setHeightInPoints(25);
        colIndex = 0;
        for (Object cellData : rowData) {
            Cell cell = dataRow.createCell(colIndex);
            if (cellData != null) {
                cell.setCellValue(cellData.toString());
            } else {
                cell.setCellValue("");
            }
            cell.setCellStyle(dataStyle);
            colIndex++;
        }
        rowIndex++;
    }
    return rowIndex;
}
 

/**
 * 设置边框
 *
 * @param style
 * @param border
 * @param color
 */
private static void setBorder(XSSFCellStyle style, BorderStyle border, XSSFColor color) {
    style.setBorderTop(border);
    style.setBorderLeft(border);
    style.setBorderRight(border);
    style.setBorderBottom(border);
    style.setBorderColor(BorderSide.TOP, color);
    style.setBorderColor(BorderSide.LEFT, color);
    style.setBorderColor(BorderSide.RIGHT, color);
    style.setBorderColor(BorderSide.BOTTOM, color);
}
 

public void styleColor(Formatter out, String attr, Color color) {
	XSSFColor xSSFColor = (XSSFColor) color;
	if (color == null || xSSFColor.isAuto())
		return;

	byte[] rgb = xSSFColor.getRgb();
	if (rgb == null) {
		return;
	}
	out.format("  %s: #%02x%02x%02x;%n", attr, rgb[0], rgb[1], rgb[2]);
}
 

/**
 * @param scenarioName
 *            name of scenario.
 * @param excelPath
 */
private void addXlsxFile(String scenarioName, String excelPath) {
    try (FileOutputStream outputStream = new FileOutputStream(excelPath); XSSFWorkbook workbook = new XSSFWorkbook()) {
        XSSFCellStyle noraUiColumnStyle = workbook.createCellStyle();
        XSSFFont noraUiColumnFont = workbook.createFont();
        noraUiColumnFont.setColor(IndexedColors.BLACK.getIndex());
        noraUiColumnFont.setBold(true);
        noraUiColumnStyle.setFont(noraUiColumnFont);
        noraUiColumnStyle.setFillForegroundColor(new XSSFColor(new java.awt.Color(0, 96, 88)));
        noraUiColumnStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);

        XSSFCellStyle noraUiResultColumnStyle = workbook.createCellStyle();
        XSSFFont noraUiResultColumnFont = workbook.createFont();
        noraUiResultColumnFont.setColor(IndexedColors.WHITE.getIndex());
        noraUiResultColumnFont.setBold(false);
        noraUiResultColumnStyle.setFont(noraUiResultColumnFont);
        noraUiResultColumnStyle.setFillForegroundColor(new XSSFColor(new java.awt.Color(128, 128, 128)));
        noraUiResultColumnStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);

        XSSFSheet sheet = workbook.createSheet("NoraUi-" + scenarioName);
        Object[][] datas = { { "user", "password", "Result" }, { "user1", "password1" }, { "user2", "password2" } };
        int rowNum = 0;
        for (int i = 0; i < datas.length; i++) {
            Object[] data = datas[i];
            Row row = sheet.createRow(rowNum++);
            int colNum = 0;
            for (Object field : data) {
                Cell cell = row.createCell(colNum++);
                if (i == 0) {
                    setHeaderStyleInXlsxFile(noraUiColumnStyle, noraUiResultColumnStyle, field, cell);
                }
                setRowValueInXlsxFile(field, cell);
            }
        }
        workbook.write(outputStream);
    } catch (IOException e) {
        log.error("IOException {}", e.getMessage(), e);
    }
}
 

public static void setColor(Workbook workbook, Font font, Color color) {
    if (color == null) {
        return;
    }
    if (font instanceof XSSFFont) {
        ((XSSFFont) font).setColor(new XSSFColor(color));
    } else if (font instanceof HSSFFont && workbook instanceof HSSFWorkbook) {
        font.setColor(getSimilarColor((HSSFWorkbook) workbook, color).getIndex());
    } else {
        log.error("unknown font type");
    }
}
 

public static void setForegroundColor(Workbook workbook, CellStyle cellStyle, Color color) {
    if (color == null) {
        return;
    }
    cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
    if (cellStyle instanceof XSSFCellStyle) {
        ((XSSFCellStyle) cellStyle).setFillForegroundColor(new XSSFColor(color));
    } else if (cellStyle instanceof HSSFCellStyle && workbook instanceof HSSFWorkbook) {
        cellStyle.setFillForegroundColor(getSimilarColor((HSSFWorkbook) workbook, color).getIndex());
    } else {
        log.error("unknown font type");
    }
}
 

@SuppressWarnings("unchecked")
public void exportExcelTemplate(HttpServletRequest req, HttpServletResponse resp) throws Exception {
	List<VariableCategory> variableCategories=(List<VariableCategory>)httpSessionKnowledgeCache.get(req, VCS_KEY);
	if(variableCategories==null){
		KnowledgeBase knowledgeBase=buildKnowledgeBase(req);
		variableCategories=knowledgeBase.getResourceLibrary().getVariableCategories();
	}
	SXSSFWorkbook wb = new SXSSFWorkbook();
	XSSFCellStyle style=(XSSFCellStyle)wb.createCellStyle();
	Color c=new Color(147,208,15);
	XSSFColor xssfColor=new XSSFColor(c);
	style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
	style.setFillForegroundColor(xssfColor);
	for(VariableCategory vc:variableCategories){
		buildSheet(wb, vc,style);
	}
	resp.setContentType("application/x-xls");
	resp.setHeader("Content-Disposition","attachment; filename=urule-batch-test-template.xlsx");
	OutputStream outputStream=resp.getOutputStream();
	wb.write(outputStream);;
	outputStream.flush();
	outputStream.close();
}
 

private static Font setFontColor(Font font, CustomColor customColor, String fontColor) {
    Short colorPredefined = ColorUtil.getPredefinedColorIndex(fontColor);
    if (colorPredefined != null) {
        font.setColor(colorPredefined);
        return font;
    }
    int[] rgb = ColorUtil.getRGBByColor(fontColor);
    if (rgb == null) {
        return null;
    }
    if (customColor.isXls()) {
        short index = ColorUtil.getCustomColorIndex(customColor, rgb);
        font.setColor(index);
    } else {
        ((XSSFFont) font).setColor(new XSSFColor(new Color(rgb[0], rgb[1], rgb[2]), customColor.getDefaultIndexedColorMap()));
    }
    return font;
}
 

private static void setCustomColor(CellStyle style, int[] rgb, CustomColor customColor) {
    if (rgb == null) {
        return;
    }
    XSSFCellStyle xssfCellStyle = (XSSFCellStyle) style;
    xssfCellStyle.setFillForegroundColor(new XSSFColor(new Color(rgb[0], rgb[1], rgb[2]), customColor.getDefaultIndexedColorMap()));
    style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
}
 

private void createFoot(XSSFWorkbook wb, XSSFSheet sh, int row, VisionVO vision, Context context) throws Exception {
	
	Row footRow=sh.createRow(row);
	Row footRowB=sh.createRow(row+1);
	XSSFCellStyle cellStyle=wb.createCellStyle();
	
	cellStyle.setFillForegroundColor( new XSSFColor(SimpleUtils.getColorRGB4POIColor("#FFFFFF"), null) );
	cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);		
	cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
	cellStyle.setBorderBottom(BorderStyle.THIN);
	cellStyle.setBorderTop(BorderStyle.THIN);
	cellStyle.setBorderRight(BorderStyle.THIN);
	cellStyle.setBorderLeft(BorderStyle.THIN);					
	XSSFFont cellFont=wb.createFont();
	cellFont.setBold(true);
	cellStyle.setFont(cellFont);
	cellStyle.setWrapText(true);		
	
	Cell footCell1 = footRow.createCell(0);
	footCell1.setCellValue("assess:");
	footCell1.setCellStyle(cellStyle);			
	Cell footCell1B = footRowB.createCell(0);
	footCell1B.setCellValue("assess:");
	footCell1B.setCellStyle(cellStyle);		
	sh.addMergedRegion(new CellRangeAddress(row, row+1, 0, 0));					
	
	Cell footCell2 = footRow.createCell(1);
	footCell2.setCellValue( BscReportPropertyUtils.getPersonalReportClassLevel() );
	footCell2.setCellStyle(cellStyle);					
	Cell footCell3 = footRow.createCell(2);
	footCell3.setCellValue( BscReportPropertyUtils.getPersonalReportClassLevel() );
	footCell3.setCellStyle(cellStyle);			
	Cell footCell4 = footRow.createCell(3);
	footCell4.setCellValue( BscReportPropertyUtils.getPersonalReportClassLevel() );
	footCell4.setCellStyle(cellStyle);			
	Cell footCell2B = footRowB.createCell(1);
	footCell2B.setCellValue( BscReportPropertyUtils.getPersonalReportClassLevel() );
	footCell2B.setCellStyle(cellStyle);					
	Cell footCell3B = footRowB.createCell(2);
	footCell3B.setCellValue( BscReportPropertyUtils.getPersonalReportClassLevel() );
	footCell3B.setCellStyle(cellStyle);			
	Cell footCell4B = footRowB.createCell(3);
	footCell4B.setCellValue( BscReportPropertyUtils.getPersonalReportClassLevel() );
	footCell4B.setCellStyle(cellStyle);					
	sh.addMergedRegion(new CellRangeAddress(row, row+1, 1, 3));	
	
	Cell footCell5 = footRow.createCell(4);
	footCell5.setCellValue("Total");
	footCell5.setCellStyle(cellStyle);	
	
	float total = 0.0f;
	if ( context.get("total")!=null && context.get("total") instanceof Float ) {
		total = (Float)context.get("total");
	}
	
	Cell footCell6 = footRow.createCell(5);
	footCell6.setCellValue( BscReportSupportUtils.parse2(total) );
	footCell6.setCellStyle(cellStyle);			
	
	Cell footCell5b = footRowB.createCell(4);
	footCell5b.setCellValue("Class");
	footCell5b.setCellStyle(cellStyle);			
	
	Cell footCell6b = footRowB.createCell(5);
	footCell6b.setCellValue( "" );
	footCell6b.setCellStyle(cellStyle);				
	
}
 

private Map<String, CellStyle> createXSSFCellStyles(Workbook wb, int[] contextBgColor, int[] contextFontColor, int contextFontSize, int contextFontAlign, int[] headerBgColor,
		int[] headerFontColor, int headerFontSize, int headerAlign) {
	Map<String, CellStyle> styles = new HashMap<String, CellStyle>();

	SXSSFWorkbook workbook = (SXSSFWorkbook) wb;
	XSSFColor xssfContextBgColor = new XSSFColor(new java.awt.Color(contextBgColor[0], contextBgColor[1], contextBgColor[2]));
	XSSFColor xssfContextFontColor = new XSSFColor(new java.awt.Color(contextFontColor[0], contextFontColor[1], contextFontColor[2]));
	XSSFColor xssfHeaderBgColor = new XSSFColor(new java.awt.Color(headerBgColor[0], headerBgColor[1], headerBgColor[2]));
	XSSFColor xssfHeaderFontColor = new XSSFColor(new java.awt.Color(headerFontColor[0], headerFontColor[1], headerFontColor[2]));

	XSSFFont headerFont = (XSSFFont) workbook.createFont();
	headerFont.setCharSet(HSSFFont.DEFAULT_CHARSET);
	headerFont.setFontName("宋体");
	if (!(headerFontColor[0] == 0 && headerFontColor[1] == 0 && headerFontColor[2] == 0)) {
		headerFont.setColor(xssfHeaderFontColor);
	}
	headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
	headerFont.setFontHeightInPoints((short) headerFontSize);
	XSSFCellStyle headerStyle = (XSSFCellStyle) this.createBorderCellStyle(workbook, true);
	headerStyle.setFont(headerFont);
	headerStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
	headerStyle.setFillForegroundColor(xssfHeaderBgColor);
	this.setCellStyleAligment(headerStyle, headerAlign);
	headerStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
	styles.put(GridStyleType.headerStyle.name(), headerStyle);

	XSSFFont dataFont = (XSSFFont) workbook.createFont();
	if (!(contextFontColor[0] == 0 && contextFontColor[1] == 0 && contextFontColor[2] == 0)) {
		dataFont.setColor(xssfContextFontColor);
	}
	dataFont.setFontHeightInPoints((short) contextFontSize);
	dataFont.setCharSet(HSSFFont.DEFAULT_CHARSET);
	dataFont.setFontName("宋体");

	XSSFCellStyle dataAlignLeftStyle = (XSSFCellStyle) this.createBorderCellStyle(workbook, true);
	dataAlignLeftStyle.setFont(dataFont);
	dataAlignLeftStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
	dataAlignLeftStyle.setFillForegroundColor(xssfContextBgColor);
	dataAlignLeftStyle.setVerticalAlignment(CellStyle.ALIGN_CENTER);
	dataAlignLeftStyle.setWrapText(true);
	dataAlignLeftStyle.setAlignment(CellStyle.ALIGN_LEFT);
	styles.put(GridStyleType.dataAlignLeftStyle.name(), dataAlignLeftStyle);

	XSSFCellStyle dataAlignCenterStyle = (XSSFCellStyle) this.createBorderCellStyle(workbook, true);
	dataAlignCenterStyle.setFont(dataFont);
	dataAlignCenterStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
	dataAlignCenterStyle.setFillForegroundColor(xssfContextBgColor);
	dataAlignCenterStyle.setVerticalAlignment(CellStyle.ALIGN_CENTER);
	dataAlignCenterStyle.setWrapText(true);
	dataAlignCenterStyle.setAlignment(CellStyle.ALIGN_CENTER);
	styles.put(GridStyleType.dataAlignCenterStyle.name(), dataAlignCenterStyle);

	XSSFCellStyle dataAlignRightStyle = (XSSFCellStyle) this.createBorderCellStyle(workbook, true);
	dataAlignRightStyle.setFont(dataFont);
	dataAlignRightStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
	dataAlignRightStyle.setFillForegroundColor(xssfContextBgColor);
	dataAlignRightStyle.setVerticalAlignment(CellStyle.ALIGN_CENTER);
	dataAlignRightStyle.setWrapText(true);
	dataAlignRightStyle.setAlignment(CellStyle.ALIGN_RIGHT);
	styles.put(GridStyleType.dataAlignRightStyle.name(), dataAlignRightStyle);

	XSSFCellStyle dateStyle = (XSSFCellStyle) this.createBorderCellStyle(workbook, true);
	CreationHelper helper = workbook.getCreationHelper();
	dateStyle.setDataFormat(helper.createDataFormat().getFormat("m/d/yy h:mm"));
	dateStyle.setFont(dataFont);
	dateStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
	dateStyle.setFillForegroundColor(xssfContextBgColor);
	dateStyle.setVerticalAlignment(CellStyle.ALIGN_CENTER);
	this.setCellStyleAligment(dateStyle, contextFontAlign);
	styles.put(GridStyleType.dateStyle.name(), dateStyle);

	return styles;
}
 

@SuppressWarnings("unchecked")
private void putTables(XSSFWorkbook wb, XSSFSheet sh, Context context) throws Exception {
	
	XSSFCellStyle cellHeadStyle = wb.createCellStyle();
	cellHeadStyle.setFillForegroundColor( new XSSFColor( SimpleUtils.getColorRGB4POIColor( "#f5f5f5" ), null ) );
	cellHeadStyle.setFillPattern( FillPatternType.SOLID_FOREGROUND );	
	cellHeadStyle.setBorderBottom( BorderStyle.THIN );
	cellHeadStyle.setBorderTop( BorderStyle.THIN );
	cellHeadStyle.setBorderRight( BorderStyle.THIN );
	cellHeadStyle.setBorderLeft( BorderStyle.THIN );		
	XSSFFont cellHeadFont = wb.createFont();
	cellHeadFont.setBold(true);		
	cellHeadStyle.setFont( cellHeadFont );
	
	sh.setColumnWidth(0, 12000);
	
	int row = 0;
	
	Row nowRow = sh.createRow(row);
	Cell cell1 = nowRow.createCell(0);
	cell1.setCellStyle(cellHeadStyle);
	cell1.setCellValue( "KPI" );				
	Cell cell2 = nowRow.createCell(1);
	cell2.setCellStyle(cellHeadStyle);
	cell2.setCellValue( "Maximum" );									
	Cell cell3 = nowRow.createCell(2);
	cell3.setCellStyle(cellHeadStyle);
	cell3.setCellValue( "Target" );	
	Cell cell4 = nowRow.createCell(3);
	cell4.setCellStyle(cellHeadStyle);
	cell4.setCellValue( "Minimum" );								
	Cell cell5 = nowRow.createCell(4);
	cell5.setCellStyle(cellHeadStyle);
	cell5.setCellValue( "Current score" );			
	Cell cell6 = nowRow.createCell(5);
	cell6.setCellStyle(cellHeadStyle);
	cell6.setCellValue( "Previous score" );	
	Cell cell7 = nowRow.createCell(6);
	cell7.setCellStyle(cellHeadStyle);
	cell7.setCellValue( "Change(%)" );	
	
	row++;
	
	List<PeriodTrendsData<KpiVO>> periodDatas = (List<PeriodTrendsData<KpiVO>>)context.get("periodDatas");
	for (PeriodTrendsData<KpiVO> periodData : periodDatas) {
		nowRow = sh.createRow(row);

		cell1 = nowRow.createCell(0);
		cell1.setCellValue( periodData.getCurrent().getName() );				
		cell2 = nowRow.createCell(1);
		cell2.setCellValue( periodData.getCurrent().getMax() );									
		cell3 = nowRow.createCell(2);
		cell3.setCellValue( periodData.getCurrent().getTarget() );	
		cell4 = nowRow.createCell(3);
		cell4.setCellValue( periodData.getCurrent().getMin() );								
		cell5 = nowRow.createCell(4);
		cell5.setCellValue( BscReportSupportUtils.parse2( periodData.getCurrent().getScore() ) );			
		cell6 = nowRow.createCell(5);
		cell6.setCellValue( BscReportSupportUtils.parse2( periodData.getPrevious().getScore() ) );	
		cell7 = nowRow.createCell(6);
		cell7.setCellValue( BscReportSupportUtils.parse2( periodData.getChange() ) );			
		
		row++;
	}
	
	nowRow = sh.createRow(row);
	
	cell1 = nowRow.createCell(0);
	cell1.setCellValue( "Current period: " + (String)context.get("currentPeriodDateRange") + " , Previous period: " + (String)context.get("previousPeriodDateRange") );				
	
}