Java Code Examples for org.apache.poi.xssf.usermodel.XSSFCellStyle#setFillForegroundColor()

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

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

/**
 * 设置表头
 *
 * @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 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);
    }
}
 

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

/**
 * 设置表头的单元格样式
 * 
 * @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;
}
 

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 int putCharts(XSSFWorkbook wb, XSSFSheet sh, Context context) throws Exception {
	
	String barBase64Content = SimpleUtils.getPNGBase64Content( (String)context.get("barChartsData") );
	BufferedImage barImage = SimpleUtils.decodeToImage( barBase64Content );
	ByteArrayOutputStream barBos = new ByteArrayOutputStream();
	ImageIO.write( barImage, "png", barBos );
	barBos.flush();	
	SimpleUtils.setCellPicture(wb, sh, barBos.toByteArray(), 0, 0);	
	
	int row = 28;
	
	List< Map<String, Object> > chartDatas = (List< Map<String, Object> >)context.get("chartDatas");
	String year = (String)context.get("year");
	
	
	XSSFCellStyle cellHeadStyle = wb.createCellStyle();
	cellHeadStyle.setFillForegroundColor( new XSSFColor( SimpleUtils.getColorRGB4POIColor( "#f5f5f5" ) ) );
	cellHeadStyle.setFillPattern( FillPatternType.SOLID_FOREGROUND  );				
	
	XSSFFont cellHeadFont = wb.createFont();
	cellHeadFont.setBold(true);		
	cellHeadStyle.setFont( cellHeadFont );
	
	int titleCellSize = 14;
	Row headRow = sh.createRow( row );
	for (int i=0; i<titleCellSize; i++) {
		Cell headCell = headRow.createCell( i );
		headCell.setCellStyle(cellHeadStyle);
		headCell.setCellValue( "Objectives metrics gauge ( " + year + " )" );					
	}
	sh.addMergedRegion( new CellRangeAddress(row, row, 0, titleCellSize-1) );
	
	row = row+1;
	int cellLeft = 10;
	int rowSpace = 17;
	for (Map<String, Object> data : chartDatas) {							
		Map<String, Object> nodeData = (Map<String, Object>) ( (List<Object>)data.get("datas") ).get(0); 
		String pngImageData = SimpleUtils.getPNGBase64Content( (String)nodeData.get("outerHTML") );			
		BufferedImage imageData = SimpleUtils.decodeToImage( pngImageData );
		ByteArrayOutputStream imgBos = new ByteArrayOutputStream();
		ImageIO.write( imageData, "png", imgBos );
		imgBos.flush();		
		SimpleUtils.setCellPicture(wb, sh, imgBos.toByteArray(), row, 0);
		
		XSSFColor bgColor = new XSSFColor( SimpleUtils.getColorRGB4POIColor( (String)nodeData.get("bgColor") ) );
		XSSFColor fnColor = new XSSFColor( SimpleUtils.getColorRGB4POIColor( (String)nodeData.get("fontColor") ) );			
		
		XSSFCellStyle cellStyle = wb.createCellStyle();
		cellStyle.setFillForegroundColor( bgColor );
		cellStyle.setFillPattern( FillPatternType.SOLID_FOREGROUND  );				
		
		XSSFFont cellFont = wb.createFont();
		cellFont.setBold(true);
		cellFont.setColor(fnColor);			
		
		cellStyle.setFont(cellFont);
		
		int perTitleCellSize = 4;
		Row nowRow = sh.createRow(row);
		for (int i=0; i<perTitleCellSize; i++) {
			Cell cell1 = nowRow.createCell(cellLeft);
			cell1.setCellStyle(cellStyle);
			cell1.setCellValue( (String)nodeData.get("name") );				
		}
		sh.addMergedRegion( new CellRangeAddress(row, row, cellLeft, cellLeft+perTitleCellSize-1) );
		
		nowRow = sh.createRow(row+1);
		Cell cell2 = nowRow.createCell(cellLeft);
		cell2.setCellValue( "Target: " + String.valueOf( nodeData.get("target") ) );			
		
		nowRow = sh.createRow(row+2);
		Cell cell3 = nowRow.createCell(cellLeft);
		cell3.setCellValue( "Min: " + String.valueOf( nodeData.get("min") ) );				
		
		nowRow = sh.createRow(row+3);
		Cell cell4 = nowRow.createCell(cellLeft);
		cell4.setCellValue( "Score: " + String.valueOf( nodeData.get("score") ) );				
		
		row += rowSpace;			
	}
	
	return row;
}
 

@SuppressWarnings("unchecked")
private int putCharts(XSSFWorkbook wb, XSSFSheet sh, Context context) throws Exception {
	String pieBase64Content = SimpleUtils.getPNGBase64Content( (String)context.get("pieCanvasToData") );
	String barBase64Content = SimpleUtils.getPNGBase64Content( (String)context.get("barCanvasToData") );
	BufferedImage pieImage = SimpleUtils.decodeToImage( pieBase64Content );
	BufferedImage barImage = SimpleUtils.decodeToImage( barBase64Content );
	ByteArrayOutputStream pieBos = new ByteArrayOutputStream();
	ImageIO.write( pieImage, "png", pieBos );
	pieBos.flush();
	ByteArrayOutputStream barBos = new ByteArrayOutputStream();
	ImageIO.write( barImage, "png", barBos );
	barBos.flush();		
	SimpleUtils.setCellPicture(wb, sh, pieBos.toByteArray(), 0, 0);		
	SimpleUtils.setCellPicture(wb, sh, barBos.toByteArray(), 0, 9);		
	int row = 21;
	
	List< Map<String, Object> > chartDatas = (List< Map<String, Object> >)context.get("chartDatas");
	String year = (String)context.get("year");
	
	
	XSSFCellStyle cellHeadStyle = wb.createCellStyle();
	cellHeadStyle.setFillForegroundColor( new XSSFColor( SimpleUtils.getColorRGB4POIColor( "#f5f5f5" ) ) );
	cellHeadStyle.setFillPattern( FillPatternType.SOLID_FOREGROUND  );				
	
	XSSFFont cellHeadFont = wb.createFont();
	cellHeadFont.setBold(true);
	//cellHeadFont.setColor( new XSSFColor( SimpleUtils.getColorRGB4POIColor( "#000000" ) ) );		
	cellHeadStyle.setFont( cellHeadFont );
	
	int titleRow = row - 1;
	int titleCellSize = 14;
	Row headRow = sh.createRow( titleRow );
	for (int i=0; i<titleCellSize; i++) {
		Cell headCell = headRow.createCell( i );
		headCell.setCellStyle(cellHeadStyle);
		headCell.setCellValue( "Perspectives metrics gauge ( " + year + " )" );					
	}
	sh.addMergedRegion( new CellRangeAddress(titleRow, titleRow, 0, titleCellSize-1) );
	
	int cellLeft = 10;
	int rowSpace = 17;
	for (Map<String, Object> data : chartDatas) {							
		Map<String, Object> nodeData = (Map<String, Object>) ( (List<Object>)data.get("datas") ).get(0); 
		String pngImageData = SimpleUtils.getPNGBase64Content( (String)nodeData.get("outerHTML") );			
		BufferedImage imageData = SimpleUtils.decodeToImage( pngImageData );
		ByteArrayOutputStream imgBos = new ByteArrayOutputStream();
		ImageIO.write( imageData, "png", imgBos );
		imgBos.flush();		
		SimpleUtils.setCellPicture(wb, sh, imgBos.toByteArray(), row, 0);
		
		XSSFColor bgColor = new XSSFColor( SimpleUtils.getColorRGB4POIColor( (String)nodeData.get("bgColor") ) );
		XSSFColor fnColor = new XSSFColor( SimpleUtils.getColorRGB4POIColor( (String)nodeData.get("fontColor") ) );			
		
		XSSFCellStyle cellStyle = wb.createCellStyle();
		cellStyle.setFillForegroundColor( bgColor );
		cellStyle.setFillPattern( FillPatternType.SOLID_FOREGROUND  );				
		
		XSSFFont cellFont = wb.createFont();
		cellFont.setBold(true);
		cellFont.setColor(fnColor);			
		
		cellStyle.setFont(cellFont);
		
		int perTitleCellSize = 4;
		Row nowRow = sh.createRow(row);
		for (int i=0; i<perTitleCellSize; i++) {
			Cell cell1 = nowRow.createCell(cellLeft);
			cell1.setCellStyle(cellStyle);
			cell1.setCellValue( (String)nodeData.get("name") );				
		}
		sh.addMergedRegion( new CellRangeAddress(row, row, cellLeft, cellLeft+perTitleCellSize-1) );
		
		nowRow = sh.createRow(row+1);
		Cell cell2 = nowRow.createCell(cellLeft);
		cell2.setCellValue( "Target: " + String.valueOf( nodeData.get("target") ) );			
		
		nowRow = sh.createRow(row+2);
		Cell cell3 = nowRow.createCell(cellLeft);
		cell3.setCellValue( "Min: " + String.valueOf( nodeData.get("min") ) );				
		
		nowRow = sh.createRow(row+3);
		Cell cell4 = nowRow.createCell(cellLeft);
		cell4.setCellValue( "Score: " + String.valueOf( nodeData.get("score") ) );				
		
		row += rowSpace;			
	}
	
	return row;
}
 

@SuppressWarnings("unchecked")
private int putCharts(XSSFWorkbook wb, XSSFSheet sh, Context context, int row) throws Exception {
	
	String barBase64Content = SimpleUtils.getPNGBase64Content( (String)context.get("barChartsData") );
	BufferedImage barImage = SimpleUtils.decodeToImage( barBase64Content );
	ByteArrayOutputStream barBos = new ByteArrayOutputStream();
	ImageIO.write( barImage, "png", barBos );
	barBos.flush();	
	SimpleUtils.setCellPicture(wb, sh, barBos.toByteArray(), row, 0);	
	
	//int row = 28;
	row = row + 32;
	
	List< Map<String, Object> > chartDatas = (List< Map<String, Object> >)context.get("chartDatas");
	String year = (String)context.get("dateRangeLabel");
	
	
	XSSFCellStyle cellHeadStyle = wb.createCellStyle();
	cellHeadStyle.setFillForegroundColor( new XSSFColor( SimpleUtils.getColorRGB4POIColor( "#f5f5f5" ) ) );
	cellHeadStyle.setFillPattern( FillPatternType.SOLID_FOREGROUND  );				
	
	XSSFFont cellHeadFont = wb.createFont();
	cellHeadFont.setBold(true);		
	cellHeadStyle.setFont( cellHeadFont );
	
	int titleCellSize = 9;
	Row headRow = sh.createRow( row );
	for (int i=0; i<titleCellSize; i++) {
		Cell headCell = headRow.createCell( i );
		headCell.setCellStyle(cellHeadStyle);
		headCell.setCellValue( "KPIs metrics gauge ( " + year + " )" );					
	}
	sh.addMergedRegion( new CellRangeAddress(row, row, 0, titleCellSize-1) );
	
	row = row+1;
	int cellLeft = 5;
	int rowSpace = 17;
	for (Map<String, Object> data : chartDatas) {							
		Map<String, Object> nodeData = (Map<String, Object>) ( (List<Object>)data.get("datas") ).get(0); 
		String pngImageData = SimpleUtils.getPNGBase64Content( (String)nodeData.get("outerHTML") );			
		BufferedImage imageData = SimpleUtils.decodeToImage( pngImageData );
		ByteArrayOutputStream imgBos = new ByteArrayOutputStream();
		ImageIO.write( imageData, "png", imgBos );
		imgBos.flush();		
		SimpleUtils.setCellPicture(wb, sh, imgBos.toByteArray(), row, 0);
		
		XSSFColor bgColor = new XSSFColor( SimpleUtils.getColorRGB4POIColor( (String)nodeData.get("bgColor") ) );
		XSSFColor fnColor = new XSSFColor( SimpleUtils.getColorRGB4POIColor( (String)nodeData.get("fontColor") ) );			
		
		XSSFCellStyle cellStyle = wb.createCellStyle();
		cellStyle.setFillForegroundColor( bgColor );
		cellStyle.setFillPattern( FillPatternType.SOLID_FOREGROUND  );				
		
		XSSFFont cellFont = wb.createFont();
		cellFont.setBold(true);
		cellFont.setColor(fnColor);			
		
		cellStyle.setFont(cellFont);
		
		int perTitleCellSize = 4;
		Row nowRow = sh.createRow(row);
		for (int i=0; i<perTitleCellSize; i++) {
			Cell cell1 = nowRow.createCell(cellLeft);
			cell1.setCellStyle(cellStyle);
			cell1.setCellValue( (String)nodeData.get("name") );				
		}
		sh.addMergedRegion( new CellRangeAddress(row, row, cellLeft, cellLeft+perTitleCellSize-1) );
		
		nowRow = sh.createRow(row+1);
		Cell cell2 = nowRow.createCell(cellLeft);
		cell2.setCellValue( "Maximum: " + String.valueOf( nodeData.get("max") ) );				
		
		nowRow = sh.createRow(row+2);
		Cell cell3 = nowRow.createCell(cellLeft);
		cell3.setCellValue( "Target: " + String.valueOf( nodeData.get("target") ) );			
		
		nowRow = sh.createRow(row+3);
		Cell cell4 = nowRow.createCell(cellLeft);
		cell4.setCellValue( "Min: " + String.valueOf( nodeData.get("min") ) );				
		
		nowRow = sh.createRow(row+4);
		Cell cell5 = nowRow.createCell(cellLeft);
		cell5.setCellValue( "Score: " + String.valueOf( nodeData.get("score") ) );				
		
		row += rowSpace;			
	}
	
	return row;
}
 

private int createPdcaItem(XSSFWorkbook wb, XSSFSheet sh, int row, XSSFCellStyle cellNormalStyle, List<PdcaItemVO> items, PdcaAuditVO audit) throws Exception {
	
	XSSFColor fnColor = new XSSFColor( SimpleUtils.getColorRGB4POIColor("#000000"), null );		
	XSSFColor bgLabelColor = new XSSFColor( SimpleUtils.getColorRGB4POIColor("#F2F2F2"), null );
	
	XSSFCellStyle cellLabelStyle = wb.createCellStyle();
	cellLabelStyle.setFillForegroundColor( bgLabelColor );
	cellLabelStyle.setFillPattern( FillPatternType.SOLID_FOREGROUND );				
	
	XSSFFont cellLabelFont = wb.createFont();
	cellLabelFont.setBold(true);
	cellLabelFont.setColor(fnColor);
	cellLabelStyle.setFont(cellLabelFont);		
	cellLabelStyle.setBorderBottom(BorderStyle.THIN);
	cellLabelStyle.setBorderTop(BorderStyle.THIN);
	cellLabelStyle.setBorderRight(BorderStyle.THIN);
	cellLabelStyle.setBorderLeft(BorderStyle.THIN);
	cellLabelStyle.setVerticalAlignment(VerticalAlignment.CENTER);
	cellLabelStyle.setAlignment(HorizontalAlignment.CENTER);
	cellLabelStyle.setWrapText(true);			
	
	Map<String, String> pdcaTypeMap = PdcaType.getDataMap(false);
	
	for (PdcaItemVO item : items) {
		
		Row labelRow = sh.createRow(row);
		Cell labelCell_6_1 = labelRow.createCell(0);	
		labelCell_6_1.setCellValue( pdcaTypeMap.get(item.getType()) );
		labelCell_6_1.setCellStyle(cellLabelStyle);				
		
		Cell labelCell_6_2 = labelRow.createCell(1);	
		labelCell_6_2.setCellValue( item.getTitle() + ( !StringUtils.isBlank(item.getDescription()) ? "\n\n" + item.getDescription() : "" ) );
		labelCell_6_2.setCellStyle(cellNormalStyle);	
		
		Cell labelCell_6_3 = labelRow.createCell(2);	
		labelCell_6_3.setCellValue( item.getEmployeeAppendNames() );
		labelCell_6_3.setCellStyle(cellNormalStyle);
		
		Cell labelCell_6_4 = labelRow.createCell(3);	
		labelCell_6_4.setCellValue( item.getStartDateDisplayValue() + " ~ " + item.getEndDateDisplayValue() );
		labelCell_6_4.setCellStyle(cellNormalStyle);	
		
		Cell labelCell_6_5 = labelRow.createCell(4);	
		labelCell_6_5.setCellValue( (audit != null ? audit.getEmpId() : " ") );
		labelCell_6_5.setCellStyle(cellNormalStyle);	
		
		Cell labelCell_6_6 = labelRow.createCell(5);	
		labelCell_6_6.setCellValue( (audit != null ? audit.getConfirmDateDisplayValue() : " ") );
		labelCell_6_6.setCellStyle(cellNormalStyle);
		
		
		row++;
		
	}
	
	return row;
}
 

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

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