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

The following examples show how to use org.apache.poi.xssf.usermodel.XSSFCellStyle#setFillPattern() . You can vote up the ones you like or vote down the ones you don't like, and go to the original project or source file by following the links above each example. You may check out the related API usage on the sidebar.
Example 1
Source File: StyleManagerXUtils.java    From birt with Eclipse Public License 1.0 6 votes vote down vote up
@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);
		}
	}
}
 
Example 2
Source File: ExcelUtils.java    From mySpringBoot with Apache License 2.0 5 votes vote down vote up
/**
 * 设置表头
 *
 * @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;
}
 
Example 3
Source File: Scenario.java    From NoraUi with GNU Affero General Public License v3.0 5 votes vote down vote up
/**
 * @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);
    }
}
 
Example 4
Source File: PackageServletHandler.java    From urule with Apache License 2.0 5 votes vote down vote up
@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();
}
 
Example 5
Source File: TitleStyleBuilder.java    From bdf3 with Apache License 2.0 5 votes vote down vote up
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;
}
 
Example 6
Source File: GridStyleBuilder.java    From bdf3 with Apache License 2.0 4 votes vote down vote up
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;
}
 
Example 7
Source File: ObjectivesDashboardExcelCommand.java    From bamboobsc with Apache License 2.0 4 votes vote down vote up
@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;
}
 
Example 8
Source File: PerspectivesDashboardExcelCommand.java    From bamboobsc with Apache License 2.0 4 votes vote down vote up
@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;
}
 
Example 9
Source File: KpisDashboardExcelCommand.java    From bamboobsc with Apache License 2.0 4 votes vote down vote up
@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;
}
 
Example 10
Source File: PdcaReportExcelCommand.java    From bamboobsc with Apache License 2.0 4 votes vote down vote up
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;
}
 
Example 11
Source File: KpiPeriodTrendsExcelCommand.java    From bamboobsc with Apache License 2.0 4 votes vote down vote up
@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") );				
	
}
 
Example 12
Source File: PersonalReportExcelCommand.java    From bamboobsc with Apache License 2.0 4 votes vote down vote up
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);				
	
}