Java Code Examples for org.apache.poi.xssf.usermodel.XSSFSheet#createRow()

public static List<String> writeExcelHeader(XSSFWorkbook wb, XSSFSheet sheet, List<String> extraFields) {
    try {
        List<String> columns = externalNames(extraFields);
        sheet.setDefaultColumnWidth(20);
        XSSFRow titleRow = sheet.createRow(0);
        XSSFCellStyle horizontalCenter = wb.createCellStyle();
        horizontalCenter.setAlignment(HorizontalAlignment.CENTER);
        for (int i = 0; i < columns.size(); i++) {
            XSSFCell cell = titleRow.createCell(i);
            cell.setCellValue(columns.get(i));
            cell.setCellStyle(horizontalCenter);
        }
        return columns;
    } catch (Exception e) {
        return null;
    }
}
 

private MetaFile writeExcel(Map<String, List<String[]>> data) throws IOException {

    XSSFWorkbook workBook = new XSSFWorkbook();

    for (String model : data.keySet()) {
      XSSFSheet sheet = workBook.createSheet(model);
      int count = 0;
      for (String[] record : data.get(model)) {
        XSSFRow row = sheet.createRow(count);
        int cellCount = 0;
        for (String val : record) {
          XSSFCell cell = row.createCell(cellCount);
          cell.setCellValue(val);
          cellCount++;
        }
        count++;
      }
    }

    File excelFile = MetaFiles.createTempFile("Data", ".xls").toFile();
    FileOutputStream out = new FileOutputStream(excelFile);
    workBook.write(out);
    out.close();

    return metaFiles.upload(excelFile);
  }
 

private static XSSFWorkbook writeSheetData(List<List<String>> data,String sheetName){
	XSSFWorkbook xssfWorkbook = new XSSFWorkbook();
	XSSFSheet xssfSheet = xssfWorkbook.createSheet(sheetName);
	XSSFRow[] rows = new XSSFRow[data.size()];
	for(int i=0;i<data.size();i++){
		List<String> columns = data.get(i);
		rows[i] = xssfSheet.createRow(i);
		//xssfSheet.setDefaultColumnWidth(columnWidth);//设置列的长度
		XSSFCell[] cells = new XSSFCell[columns.size()];
		for(int j=0;j<columns.size();j++){
			cells[j] = rows[i].createCell(j);
			//cells[j].setCellStyle(XSSFCellStyle);
			cells[j].setCellValue(columns.get(j));
		}
	}
	return xssfWorkbook;
}
 

public static void export2007(String filePath) {
	try {
		// 输出流
		OutputStream os = new FileOutputStream(filePath);
		// 工作区
		XSSFWorkbook wb = new XSSFWorkbook();
		XSSFSheet sheet = wb.createSheet(Globals.SHEETNAME);
		for (int i = 0; i < 1000; i++) {
			// 创建第一个sheet
			// 生成第一行
			XSSFRow row = sheet.createRow(i);
			// 给这一行的第一列赋值
			row.createCell(0).setCellValue("column" + i);
			// 给这一行的第一列赋值
			row.createCell(1).setCellValue("column" + i);
			// System.out.println(i);
		}
		// 写文件
		wb.write(os);
		// 关闭输出流
		os.close();
	} catch (Exception e) {
		e.printStackTrace();
	}
}
 

/**
 * @param destination
 *            the sheet to create from the copy.
 * @param the
 *            sheet to copy.
 * @param copyStyle
 *            true copy the style.
 */
private static void copySheet(List<CellStyle> list, HSSFSheet source, XSSFSheet destination, boolean copyStyle) {
	int maxColumnNum = 0;
	List<CellStyle> styleMap = null;
	if (copyStyle) {
		styleMap = list;
	}

	for (int i = source.getFirstRowNum(); i <= source.getLastRowNum(); i++) {
		HSSFRow srcRow = source.getRow(i);
		XSSFRow destRow = destination.createRow(i);

		if (srcRow != null) {

			copyRow(source, destination, srcRow, destRow, styleMap);
			if (srcRow.getLastCellNum() > maxColumnNum) {
				maxColumnNum = srcRow.getLastCellNum();
			}
		}
	}
	destination.shiftRows(destination.getFirstRowNum(), destination.getLastRowNum(), 3);
	for (int i = 0; i <= maxColumnNum; i++) {
		destination.autoSizeColumn(i);
	}
}
 

@Test
public void lastRowNumXSSF() throws IOException {
    String file = "D:\\test\\珠海.xlsx";
    XSSFWorkbook xssfWorkbook = new XSSFWorkbook(file);
    LOGGER.info("一共:{}个sheet", xssfWorkbook.getNumberOfSheets());
    XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(0);
    LOGGER.info("一共行数:{}", xssfSheet.getLastRowNum());
    XSSFRow row = xssfSheet.getRow(0);
    LOGGER.info("第一行数据:{}", row);
    xssfSheet.createRow(20);
    LOGGER.info("一共行数:{}", xssfSheet.getLastRowNum());
}
 

private static void insert(int rowNum, XSSFSheet sheet, XSSFRow row, XSSFCell cell, int arg0, int arg1, int arg2) {
	row = sheet.createRow(rowNum);
       cell = row.createCell(0);
	cell.setCellValue(arg0);
	cell = row.createCell(1);
	cell.setCellValue(arg1);
	cell = row.createCell(2);
	cell.setCellValue(arg2);
}
 

public static void writeXLSXFile() throws Exception {

		XSSFWorkbook xssfwb = new XSSFWorkbook();
		XSSFSheet sheet = xssfwb.createSheet(sheetName);
		for (int row = 0; row < tableData.size(); row++) {
			XSSFRow xddfrow = sheet.createRow(row);
			rowData = tableData.get(row);
			for (int col = 0; col < rowData.size(); col++) {
				XSSFCell cell = xddfrow.createCell(col);
				cell.setCellValue(rowData.get(col));
				logger.info("Writing " + row + " " + col + "  " + rowData.get(col));
			}
		}

		try (OutputStream fileOutputStream = new FileOutputStream(
				excelFileName)) {

			xssfwb.write(fileOutputStream);
			xssfwb.close();
			fileOutputStream.flush();
			fileOutputStream.close();

		} catch (IOException e) {
			String message = String.format("Exception saving XLSX file %s\n",
					excelFileName) + e.getMessage();
			logger.info(message);
			// NOTE: throw exceptions with user friendly messages to be rendered
			// by the master app
			throw new Exception(message);

		}
	}
 

@Before
public void testXLS1() throws IOException {
       try (XSSFWorkbook workbook = new XSSFWorkbook()) {
        XSSFSheet sheet = workbook.createSheet();
        XSSFRow row = null;
        XSSFCell cell = null;
        row = sheet.createRow(0);
        cell = row.createCell(0);
		cell.setCellValue("A");
		cell = row.createCell(1);
		cell.setCellValue("B");
		cell = row.createCell(2);
		cell.setCellValue("C");
		
		insert(1,sheet,row,cell,1,2,3);
		insert(2,sheet,row,cell,4,5,6);
		insert(3,sheet,row,cell,4,5,6);
		insert(4,sheet,row,cell,1,2,3);
		insert(5,sheet,row,cell,7,8,9);
		insert(6,sheet,row,cell,7,8,9);
		insert(7,sheet,row,cell,4,5,6);
        
		try (FileOutputStream out = new FileOutputStream(file)) {
		    workbook.write(out);
		} catch (IOException e) {
			e.printStackTrace();
		}
       }

	String src = 
			"BEGIN;\n" +
					"var myvar external xls \"" + file.getAbsolutePath() + "\" dup_count;" +
			"END;\n" +
			"true";
	testEquals("true", src);
}
 

@Before
public void testXLS1() throws IOException {
       try (XSSFWorkbook workbook = new XSSFWorkbook()) {
        XSSFSheet sheet = workbook.createSheet();
        XSSFRow row = null;
        XSSFCell cell = null;
        row = sheet.createRow(0);
        cell = row.createCell(0);
		cell.setCellValue("A");
		cell = row.createCell(1);
		cell.setCellValue("B");
		cell = row.createCell(2);
		cell.setCellValue("C");
		
		insert(1,sheet,row,cell,1,2,3);
		insert(2,sheet,row,cell,4,5,6);
		insert(3,sheet,row,cell,7,8,9);
        
		try (FileOutputStream out = new FileOutputStream(file)) {
		    workbook.write(out);
		} catch (IOException e) {
			e.printStackTrace();
		}
       }

	String src = 
			"BEGIN;\n" +
					"var myvar external xls \"" + file.getAbsolutePath() + "\" autokey;" +
			"END;\n" +
			"true";
	testEquals("true", src);
}
 

private int writeExcelHeader(List<SurveyQuestion> questions, XSSFSheet sheet) {
    Row headerRow = sheet.createRow(0);
    AtomicInteger colNum = new AtomicInteger();

    mkHeaderStrings(questions).forEach(hdr -> writeExcelHeaderCell(headerRow, colNum, hdr));

    return colNum.get();
}
 

@Test
public void lastRowNumXSSF() throws IOException {
    String file = TestFileUtil.getPath() + "demo" + File.separator + "demo.xlsx";
    XSSFWorkbook xssfWorkbook = new XSSFWorkbook(file);
    LOGGER.info("一共:{}个sheet", xssfWorkbook.getNumberOfSheets());
    XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(0);
    LOGGER.info("一共行数:{}", xssfSheet.getLastRowNum());
    XSSFRow row = xssfSheet.getRow(0);
    LOGGER.info("第一行数据:{}", row);
    xssfSheet.createRow(20);
    LOGGER.info("一共行数:{}", xssfSheet.getLastRowNum());
}
 

private static void insert(int rowNum, XSSFSheet sheet, XSSFRow row, XSSFCell cell, int arg0, int arg1, int arg2) {
	row = sheet.createRow(rowNum);
       cell = row.createCell(0);
	cell.setCellValue(arg0);
	cell = row.createCell(1);
	cell.setCellValue(arg1);
	cell = row.createCell(2);
	cell.setCellValue(arg2);
}
 

private static XSSFWorkbook writeSheetObject(List<Object> data,String sheetName) throws Exception {
	XSSFWorkbook xssfWorkbook = new XSSFWorkbook();
	XSSFSheet xssfSheet = xssfWorkbook.createSheet(sheetName);
	XSSFRow[] rows = new XSSFRow[data.size()];
	for(int i=0;i<data.size();i++){
		Object classes = data.get(i);
		Object target = classes.getClass().newInstance();
		Field[] fields = target.getClass().getDeclaredFields();
		rows[i] = xssfSheet.createRow(i);
		//xssfSheet.setDefaultColumnWidth(columnWidth);//设置列的长度
		XSSFCell[] cells = new XSSFCell[fields.length];
		for(int j=0;j<fields.length;j++){
			String fieldName = fields[j].getName();
			fieldName = fieldName.substring(0,1).toUpperCase()+fieldName.substring(1,fieldName.length());
			Method method = target.getClass().getDeclaredMethod("get"+fieldName,new Class[]{});
			cells[j] = rows[i].createCell(j);
			Object value = method.invoke(classes,new Object[]{});
			if(value instanceof Double){
				cells[j].setCellValue(new Double(value.toString()));
			}else if(value instanceof Integer){
				cells[j].setCellValue(new Double(value.toString()).intValue());
			}else if(value instanceof Float){
				cells[j].setCellValue(new Double(value.toString()));
			}else{
				cells[j].setCellValue(value.toString());
			}
			//cells[j].setCellStyle(XSSFCellStyle);
		}
	}
	return xssfWorkbook;
}
 

/**
 * Returns the cell of a given sheet at icol;irow. Former values are overwritten.
 *
 * @param sheet the sheet to write on
 * @param icol index of column
 * @param irow index of row
 * @return the given cell at icol;irow
 * @see
 */
public Cell getCell(XSSFSheet sheet, int icol, int irow) {
  // try to get row
  Row row = sheet.getRow(irow);
  // if not exist: create row
  if (row == null)
    row = sheet.createRow(irow);
  // get cell
  return row.createCell(icol);
}
 

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

protected boolean writeExcel(File file, String sql) {
    try ( Connection conn = DriverManager.getConnection(protocol + dbHome() + login);
             FileWriter writer = new FileWriter(file, Charset.forName("utf-8"))) {
        String filename = file.getAbsolutePath();
        conn.setReadOnly(true);
        XSSFWorkbook wb = new XSSFWorkbook();
        XSSFSheet sheet = wb.createSheet("sheet1");
        List<String> columns = columnNames();
        XSSFRow titleRow = sheet.createRow(0);
        XSSFCellStyle horizontalCenter = wb.createCellStyle();
        horizontalCenter.setAlignment(HorizontalAlignment.CENTER);
        for (int i = 0; i < columns.size(); i++) {
            XSSFCell cell = titleRow.createCell(i);
            cell.setCellValue(columns.get(i));
            cell.setCellStyle(horizontalCenter);
        }
        int count = 0;
        try ( ResultSet results = conn.createStatement().executeQuery(sql)) {
            while (results.next()) {
                if (cancelled) {
                    updateLogs(message("Cancelled") + " " + filename);
                    return false;
                }
                writeExcel(conn, sheet, results, count);
                count++;
                if (verboseCheck.isSelected() && (count % 50 == 0)) {
                    updateLogs(message("Exported") + " " + count + ": " + filename);
                }
            }
        }
        for (int i = 0; i < columns.size(); i++) {
            sheet.autoSizeColumn(i);
        }
        try ( OutputStream fileOut = new FileOutputStream(file)) {
            wb.write(fileOut);
        }
        return true;
    } catch (Exception e) {
        updateLogs(e.toString());
        return false;
    }
}
 

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

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