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

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

private int addLangCell(Row header, TmxSegement segment) {
	int CellNum = header.getLastCellNum();
	if (-1 == CellNum) {
		CellNum = 0;
	}
	Cell createCell = header.createCell(CellNum);
	CellStyle cellStyle = wb.createCellStyle();
	XSSFFont headerFont = (XSSFFont) wb.createFont();
	headerFont.setBold(true);
	cellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
	cellStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
	cellStyle.setFont(headerFont);
	createCell.setCellValue(segment.getLangCode());
	createCell.setCellStyle(cellStyle);
	sh.setColumnWidth(CellNum, (100 * 7 + 5) / 7 * 256);
	return CellNum;
}
 

/**
 * XSSFRichTextString.utfDecode()<br/>
 * value.contains("_x")<br/>
 * Pattern.compile("_x([0-9A-Fa-f]{4})_");
 */
private static void export2007WithStyle(String filePath) {
	try {
		OutputStream os = new FileOutputStream(filePath);
		XSSFWorkbook wb = new XSSFWorkbook();
		XSSFSheet sheet = wb.createSheet(Globals.SHEETNAME);
		XSSFCell cell = sheet.createRow(0).createCell(0);
		cell.setCellValue(TestUtil.RICH_TEXT_STRINGS[0]
				+ escape(TestUtil.REGEX + TestUtil.RICH_TEXT_STRINGS[1]
						+ TestUtil.REGEX) + TestUtil.RICH_TEXT_STRINGS[2]);
		CellStyle style = sheet.getWorkbook().createCellStyle();
		XSSFFont font = wb.createFont();
		font.setColor(IndexedColors.BLUE.index);
		style.setFont(font);
		cell.setCellStyle(style);
		// richString.applyFont(font);
		wb.write(os);
		os.close();
	} catch (Exception e) {
		e.printStackTrace();
	}
}
 

@Test
public void testExcel() throws IOException {
	String filepath = "E:\\ConvertTester\\excel\\abcd.xlsx";
	File originFile = new File(filepath);
	InputStream in = new FileInputStream(originFile);
	XSSFWorkbook workbook = new XSSFWorkbook(in);
	XSSFSheet sheet = workbook.createSheet("testSheet");

	XSSFDrawing drawing = sheet.createDrawingPatriarch();
	
	XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 1023, 255, (short) 2, 4, (short) 13, 26);

	XSSFTextBox textbox = drawing.createTextbox(anchor);
	XSSFRichTextString rtxt = new XSSFRichTextString("ekozhan");
	XSSFFont font = workbook.createFont();
	font.setColor((short) 27);
	font.setBold(true);
	font.setFontHeightInPoints((short) 192);
	font.setFontName("Verdana");
	rtxt.applyFont(font);
	textbox.setText(rtxt);
	textbox.setLineStyle(XSSFShape.EMU_PER_POINT);
	textbox.setNoFill(true);
	workbook.write(new FileOutputStream(filepath));
	workbook.close();
}
 

/**
 * Gets the {@link MStyle} of the given {@link XSSFCell}.
 * 
 * @param cellthe
 *            {@link XSSFCell}
 * @return the {@link MStyle} of the given {@link XSSFCell}
 */
private MStyle getStyle(XSSFCell cell) {
    final XSSFCellStyle style = cell.getCellStyle();
    final XSSFFont font = style.getFont();
    int modifiers = 0;
    if (font.getBold()) {
        modifiers |= MStyle.FONT_BOLD;
    }
    if (font.getItalic()) {
        modifiers |= MStyle.FONT_ITALIC;
    }
    if (font.getStrikeout()) {
        modifiers |= MStyle.FONT_STRIKE_THROUGH;
    }
    if (font.getUnderline() != 0) {
        modifiers |= MStyle.FONT_UNDERLINE;
    }
    return new MStyleImpl(font.getFontName(), font.getFontHeightInPoints(), getColor(font.getXSSFColor()), null,
            modifiers);
}
 

private Color getColor(Font font) {
    if (helper instanceof HSSFHtmlHelper) {
        return ((HSSFWorkbook) sheet.getWorkbook()).getCustomPalette()
            .getColor(font.getColor());
    } else {
        return ((XSSFFont) font).getXSSFColor();
    }
}
 

@Test
public void getFontSizeInPoints_ReturnsTheDefaultFontSizeIfStyleOrFontIsNull() {

    SpreadsheetCell cell = getCell();
    
    assertThat(cell.getStyle())
            .isNull();
    assertThat(cell.getFontSizeInPoints())
            .isEqualTo((int) XSSFFont.DEFAULT_FONT_SIZE);

    cell.setStyle(new SpreadsheetCellStyle());

    assertThat(cell.getStyle())
            .isNotNull();
    assertThat(cell.getStyle().getFont())
            .isNull();

    assertThat(cell.getFontSizeInPoints())
            .isEqualTo((int) XSSFFont.DEFAULT_FONT_SIZE);

    cell.setStyle(new SpreadsheetCellStyle().withFontSizeInPoints(17));

    assertThat(cell.getStyle())
            .isNotNull();

    assertThat(cell.getStyle().getFont())
            .isNotNull();

    assertThat(cell.getFontSizeInPoints())
            .isNotEqualTo((int) XSSFFont.DEFAULT_FONT_SIZE);

    assertThat(cell.getFontSizeInPoints())
            .isEqualTo(17);
}
 

public int getFontSizeInPoints() {
    if (style != null) {
        SpreadsheetFont font = style.getFont();
        if (font != null) {
            Integer size = font.getFontSizeInPoints();
            if (size != null) {
                return size;
            }
        }
    }
    return XSSFFont.DEFAULT_FONT_SIZE;
}
 

@Override
protected void startSerialize( RootNode rootNode, OutputStream outputStream ) throws Exception
{
    workbook = new XSSFWorkbook();
    sheet = workbook.createSheet( "Sheet1" );

    XSSFFont boldFont = workbook.createFont();
    boldFont.setBold( true );

    XSSFCellStyle boldCellStyle = workbook.createCellStyle();
    boldCellStyle.setFont( boldFont );

    // build schema
    for ( Node child : rootNode.getChildren() )
    {
        if ( child.isCollection() )
        {
            if ( !child.getChildren().isEmpty() )
            {
                Node node = child.getChildren().get( 0 );

                XSSFRow row = sheet.createRow( 0 );

                int cellIdx = 0;

                for ( Node property : node.getChildren() )
                {
                    if ( property.isSimple() )
                    {
                        XSSFCell cell = row.createCell( cellIdx++ );
                        cell.setCellValue( property.getName() );
                        cell.setCellStyle( boldCellStyle );
                    }
                }
            }
        }
    }
}
 

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

/**
 * 设置表体的单元格样式
 * 
 * @return
 */
public XSSFCellStyle getBodyStyle() {
	// 创建单元格样式
	XSSFCellStyle cellStyle = wb.createCellStyle();
	// 创建单元格内容显示不下时自动换行
	//cellStyle.setWrapText(true);
	// 设置单元格字体样式
	XSSFFont font = wb.createFont();
	// 设置字体加粗
	font.setFontName("宋体");
	font.setFontHeight((short) 200);
	cellStyle.setFont(font);
	return cellStyle;
}
 

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

/**
 * 查找一模一样的字体对象
 * 
 * @author      ZhengWei(HY)
 * @createDate  2017-11-16
 * @version     v1.0
 *
 * @param i_Workbook
 * @param i_Font
 * @return
 */
public final static XSSFFont findFont(XSSFWorkbook i_Workbook ,XSSFFont i_Font)
{
    return i_Workbook.getStylesSource().findFont(i_Font.getBold() 
                                                ,i_Font.getColor() 
                                                ,i_Font.getFontHeight() 
                                                ,i_Font.getFontName() 
                                                ,i_Font.getItalic()
                                                ,i_Font.getStrikeout()
                                                ,i_Font.getTypeOffset()
                                                ,i_Font.getUnderline());
}
 

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

/**
 * 大标题样式
 *
 * @param wb
 * @param cell
 * @param sxssfRow
 */
public static void setLabelStyles(SXSSFWorkbook wb, Cell cell, SXSSFRow sxssfRow) {
    CellStyle cellStyle = wb.createCellStyle();
    cellStyle.setAlignment(HorizontalAlignment.CENTER);
    cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
    sxssfRow.setHeight((short) (399 * 2));
    XSSFFont font = (XSSFFont) wb.createFont();
    font.setFontName("宋体");
    font.setFontHeight(16);
    cellStyle.setFont(font);
    cell.setCellStyle(cellStyle);
}
 

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

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

/**
 * 默认样式
 *
 * @param cellStyle
 * @param font
 * @return
 * @Parm
 */
public static void setStyle(CellStyle cellStyle, XSSFFont font,Integer fontSize) {
    cellStyle.setAlignment(HorizontalAlignment.CENTER);
    cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
    font.setFontName("宋体");
    cellStyle.setFont(font);
    font.setFontHeight(fontSize);
    setBorder(cellStyle, true);
}
 

private Color getColor(Font font) {
	if (helper instanceof HSSFHtmlHelper) {
		return ((HSSFWorkbook) sheet.getWorkbook()).getCustomPalette().getColor(font.getColor());
	} else {
		return ((XSSFFont) font).getXSSFColor();
	}
}
 

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

private Color getColor(Font font) {
	if (helper instanceof HSSFHtmlHelper) {
		return ((HSSFWorkbook) sheet.getWorkbook()).getCustomPalette().getColor(font.getColor());
	} else {
		return ((XSSFFont) font).getXSSFColor();
	}
}
 

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

/**
 * 设置数据：无样式（行、列、单元格样式）
 *
 * @param wb
 * @param sxssfRow
 * @param dataLists
 * @param regionMap
 * @param columnMap
 * @param paneMap
 * @param sheetName
 * @param labelName
 * @param dropDownMap
 * @throws Exception
 */
public static void setDataListNoStyle(SXSSFWorkbook wb, SXSSFRow sxssfRow, List<List<String[]>> dataLists, HashMap regionMap,
                                      HashMap columnMap, HashMap paneMap, String[] sheetName, String[] labelName, HashMap dropDownMap,Integer defaultColumnWidth,Integer fontSize) throws Exception {
    if (dataLists == null) {
        log.debug("=== ===  === :Andyczy ExcelUtils Exception Message：Export data(type:List<List<String[]>>) cannot be empty!");
    }
    if (sheetName == null) {
        log.debug("=== ===  === :Andyczy ExcelUtils Exception Message：Export sheet(type:String[]) name cannot be empty!");
    }
    int k = 0;
    for (List<String[]> listRow : dataLists) {
        SXSSFSheet sxssfSheet = wb.createSheet();
        sxssfSheet.setDefaultColumnWidth(defaultColumnWidth);
        wb.setSheetName(k, sheetName[k]);
        CellStyle cellStyle = wb.createCellStyle();
        XSSFFont font = (XSSFFont) wb.createFont();

        int jRow = 0;
        //  自定义：大标题（看该方法说明）。
        jRow = setLabelName(jRow, k, wb, labelName, sxssfRow, sxssfSheet, listRow);

        //  自定义：每个表格固定表头（看该方法说明）。
        Integer pane = 1;
        if (paneMap != null && paneMap.get(k + 1) != null) {
            pane = (Integer) paneMap.get(k + 1) + (labelName != null ? 1 : 0);
            createFreezePane(sxssfSheet, pane);
        }
        //  自定义：每个单元格自定义合并单元格：对每个单元格自定义合并单元格（看该方法说明）。
        if (regionMap != null) {
            setMergedRegion(sxssfSheet, (ArrayList<Integer[]>) regionMap.get(k + 1));
        }
        //  自定义：每个单元格自定义下拉列表：对每个单元格自定义下拉列表（看该方法说明）。
        if (dropDownMap != null) {
            setDataValidation(sxssfSheet, (List<String[]>) dropDownMap.get(k + 1), listRow.size());
        }
        //  自定义：每个表格自定义列宽：对每个单元格自定义列宽（看该方法说明）。
        if (columnMap != null) {
            setColumnWidth(sxssfSheet, (HashMap) columnMap.get(k + 1));
        }
        //  默认样式。
        setStyle(cellStyle, font,fontSize);

        //  写入小标题与数据。
        Integer SIZE = listRow.size() < MAX_ROWSUM ? listRow.size() : MAX_ROWSUM;
        for (int i = 0; i < SIZE; i++) {
            sxssfRow = sxssfSheet.createRow(jRow);
            for (int j = 0; j < listRow.get(i).length; j++) {
                Cell cell = createCell(sxssfRow, j, listRow.get(i)[j]);
                cell.setCellStyle(cellStyle);
            }
            jRow++;
        }
        k++;
    }
}
 

public void writeExcel() throws IOException {
    Workbook workbook = new XSSFWorkbook();

    try {
        Sheet sheet = workbook.createSheet("Persons");
        sheet.setColumnWidth(0, 6000);
        sheet.setColumnWidth(1, 4000);

        Row header = sheet.createRow(0);

        CellStyle headerStyle = workbook.createCellStyle();

        headerStyle.setFillForegroundColor(IndexedColors.LIGHT_BLUE.getIndex());
        headerStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);

        XSSFFont font = ((XSSFWorkbook) workbook).createFont();
        font.setFontName("Arial");
        font.setFontHeightInPoints((short) 16);
        font.setBold(true);
        headerStyle.setFont(font);

        Cell headerCell = header.createCell(0);
        headerCell.setCellValue("Name");
        headerCell.setCellStyle(headerStyle);

        headerCell = header.createCell(1);
        headerCell.setCellValue("Age");
        headerCell.setCellStyle(headerStyle);

        CellStyle style = workbook.createCellStyle();
        style.setWrapText(true);

        Row row = sheet.createRow(2);
        Cell cell = row.createCell(0);
        cell.setCellValue("John Smith");
        cell.setCellStyle(style);

        cell = row.createCell(1);
        cell.setCellValue(20);
        cell.setCellStyle(style);

        File currDir = new File(".");
        String path = currDir.getAbsolutePath();
        String fileLocation = path.substring(0, path.length() - 1) + "temp.xlsx";

        FileOutputStream outputStream = new FileOutputStream(fileLocation);
        workbook.write(outputStream);
    } finally {
        if (workbook != null) {
           
                workbook.close();
           
        }
    }
}
 

private String getFontIndex(XSSFFont font) {
	return fontCache.get(font.getBoldweight() + "_" + font.getItalic() + "_" + font.getFontName() + "_" + font.getFontHeightInPoints() + "_" + font.getColor());
}
 

/**
 * {@inheritDoc}
 */
@Override
public void generateReport(final ReportDescriptor descriptor,
                           final Map<String, Object> parameters,
                           final Object data,
                           final String lang,
                           final OutputStream outputStream) {

    if (data == null || data instanceof Collection && ((Collection) data).isEmpty()) {
        LOG.debug("No data, no report will be generated");
        return;

    }

    try {

        final XSSFWorkbook workbook = new XSSFWorkbook();
        final XSSFSheet sheet = workbook.createSheet(descriptor.getReportId());

        final XSSFCellStyle headerStyle = workbook.createCellStyle();
        final XSSFFont font = workbook.createFont();
        font.setBold(true);
        headerStyle.setFont(font);

        int rowNum = 0;

        for (Object dataLine : (Collection) data) {

            final Object[] line = (Object[]) dataLine;

            final Row row = sheet.createRow(rowNum++);
            int colNum = 0;
            for (Object field : line) {
                final Cell cell = row.createCell(colNum++);
                if (rowNum == 1) {
                    cell.setCellStyle(headerStyle);
                }
                if (field instanceof String) {
                    cell.setCellValue((String) field);
                } else if (field instanceof Number) {
                    cell.setCellValue(((Number) field).doubleValue());
                } else {
                    cell.setCellValue("");
                }
            }
        }

        workbook.write(outputStream);
        workbook.close();

    } catch (Exception exp) {
        LOG.error("Unable to generate report for " + descriptor + " in " + lang, exp);
    }

}
 

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

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