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

The following examples show how to use org.apache.poi.xssf.usermodel.XSSFColor. 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: ExcelCellStyleBuilderTest.java From pentaho-reporting with GNU Lesser General Public License v2.1

6 votes

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

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

  builder.withBackgroundStyle( bg, styleKey );

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

Example #2

Source File: ExcelCellStyleBuilder.java From pentaho-reporting with GNU Lesser General Public License v2.1

6 votes

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

Example #3

Source File: StyleManagerXUtils.java From birt with Eclipse Public License 1.0

6 votes

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

Example #4

Source File: StyleManagerXUtils.java From birt with Eclipse Public License 1.0

6 votes

@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 #5

Source File: StyleManagerXUtils.java From birt with Eclipse Public License 1.0

6 votes

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

Example #6

Source File: StylerHelper.java From autopoi with Apache License 2.0

5 votes

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

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

Example #7

Source File: HSSFCellStyleProducer.java From pentaho-reporting with GNU Lesser General Public License v2.1

5 votes

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

Example #8

Source File: Issue24.java From birt with Eclipse Public License 1.0

5 votes

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

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

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

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

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

Example #9

Source File: StyleManagerXUtils.java From birt with Eclipse Public License 1.0

5 votes

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

Example #10

Source File: StyleManagerXUtils.java From birt with Eclipse Public License 1.0

5 votes

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

Example #11

Source File: ExcelSheet.java From objectlabkit with Apache License 2.0

5 votes

public ExcelSheet tabColour(IndexedColors colour) {
    if (sheet() instanceof XSSFSheet) {
        ((XSSFSheet) sheet()).setTabColor(new XSSFColor(colour, new DefaultIndexedColorMap()));
    } else if (sheet() instanceof SXSSFSheet) {
        ((SXSSFSheet) sheet()).setTabColor(new XSSFColor(colour, new DefaultIndexedColorMap()));
    }
    return this;
}

Example #12

Source File: FileUtil.java From JavaWeb with Apache License 2.0

5 votes

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

Example #13

Source File: FileUtil.java From JavaWeb with Apache License 2.0

5 votes

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

Example #14

Source File: StylerHelper.java From easypoi with Apache License 2.0

5 votes

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

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

Example #15

Source File: TitleStyleBuilder.java From bdf3 with Apache License 2.0

5 votes

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 #16

Source File: ColorUtils.java From Octopus with MIT License

5 votes

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

Example #17

Source File: ExcelWriter.java From excel-boot with Artistic License 2.0

5 votes

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

Example #18

Source File: ExcelUtils.java From mySpringBoot with Apache License 2.0

5 votes

/**
 * 设置表头
 *
 * @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 #19

Source File: ExcelUtils.java From mySpringBoot with Apache License 2.0

5 votes

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

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

Example #20

Source File: ExcelUtils.java From mySpringBoot with Apache License 2.0

5 votes

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

Example #21

Source File: StylerHelper.java From jeasypoi with Apache License 2.0

5 votes

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

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

Example #22

Source File: Scenario.java From NoraUi with GNU Affero General Public License v3.0

5 votes

/**
 * @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 #23

Source File: ColorUtils.java From Octopus with MIT License

5 votes

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

Example #24

Source File: ColorUtils.java From Octopus with MIT License

5 votes

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

Example #25

Source File: PackageServletHandler.java From urule with Apache License 2.0

5 votes

@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 #26

Source File: FontStyle.java From myexcel with Apache License 2.0

5 votes

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

Example #27

Source File: BackgroundStyle.java From myexcel with Apache License 2.0

5 votes

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

Example #28

Source File: PersonalReportExcelCommand.java From bamboobsc with Apache License 2.0

4 votes

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

Example #29

Source File: GridStyleBuilder.java From bdf3 with Apache License 2.0

4 votes

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 #30

Source File: KpiPeriodTrendsExcelCommand.java From bamboobsc with Apache License 2.0

4 votes

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