下面列出了org.apache.poi.ss.usermodel.Color#org.apache.poi.xssf.usermodel.XSSFColor 实例代码，或者点击链接到github查看源代码，也可以在右侧发表评论。
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;
}
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");
}
}
@SuppressWarnings("unchecked")
public void exportExcelTemplate(HttpServletRequest req, HttpServletResponse resp) throws Exception {
List<VariableCategory> variableCategories=(List<VariableCategory>)httpSessionKnowledgeCache.get(req, VCS_KEY);
if(variableCategories==null){
KnowledgeBase knowledgeBase=buildKnowledgeBase(req);
variableCategories=knowledgeBase.getResourceLibrary().getVariableCategories();
}
SXSSFWorkbook wb = new SXSSFWorkbook();
XSSFCellStyle style=(XSSFCellStyle)wb.createCellStyle();
Color c=new Color(147,208,15);
XSSFColor xssfColor=new XSSFColor(c);
style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
style.setFillForegroundColor(xssfColor);
for(VariableCategory vc:variableCategories){
buildSheet(wb, vc,style);
}
resp.setContentType("application/x-xls");
resp.setHeader("Content-Disposition","attachment; filename=urule-batch-test-template.xlsx");
OutputStream outputStream=resp.getOutputStream();
wb.write(outputStream);;
outputStream.flush();
outputStream.close();
}
private static 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;
}
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;
}
@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);
}
}
}
@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);
}
}
}
@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;
}
}
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 );
}
}
@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 ) );
}
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]);
}
/**
* 设置表头
*
* @param wb
* @param sheet
* @param titles
* @return
*/
private static int writeTitlesToExcel(XSSFWorkbook wb, Sheet sheet, List<String> titles) {
int rowIndex = 0;
int colIndex = 0;
Font titleFont = wb.createFont();
//设置字体
titleFont.setFontName("simsun");
//设置粗体
titleFont.setBoldweight(Short.MAX_VALUE);
//设置字号
titleFont.setFontHeightInPoints((short) 14);
//设置颜色
titleFont.setColor(IndexedColors.BLACK.index);
XSSFCellStyle titleStyle = wb.createCellStyle();
//水平居中
titleStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);
//垂直居中
titleStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
//设置图案颜色
titleStyle.setFillForegroundColor(new XSSFColor(new Color(182, 184, 192)));
//设置图案样式
titleStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
titleStyle.setFont(titleFont);
setBorder(titleStyle, BorderStyle.THIN, new XSSFColor(new Color(0, 0, 0)));
Row titleRow = sheet.createRow(rowIndex);
titleRow.setHeightInPoints(25);
colIndex = 0;
for (String field : titles) {
Cell cell = titleRow.createCell(colIndex);
cell.setCellValue(field);
cell.setCellStyle(titleStyle);
colIndex++;
}
rowIndex++;
return rowIndex;
}
/**
* 设置内容
*
* @param 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;
}
/**
* 设置边框
*
* @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);
}
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]);
}
/**
* @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);
}
}
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");
}
}
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");
}
}
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);
}
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]);
}
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;
}
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;
}
@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;
}
}
}
}
}
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;
}
@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();
}
}
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] );
}
private XSSFColor buildXSSFColor(String colorStr){
String[] color=colorStr.split(",");
Color c=new Color(Integer.valueOf(color[0]),Integer.valueOf(color[1]),Integer.valueOf(color[2]));
XSSFColor xssfColor=new XSSFColor(c);
return xssfColor;
}
private Map<String, CellStyle> createXSSFCellStyles(Workbook wb, int[] contextBgColor, int[] contextFontColor, int contextFontSize, int contextFontAlign, int[] headerBgColor,
int[] headerFontColor, int headerFontSize, int headerAlign) {
Map<String, CellStyle> styles = new HashMap<String, CellStyle>();
SXSSFWorkbook workbook = (SXSSFWorkbook) wb;
XSSFColor xssfContextBgColor = new XSSFColor(new java.awt.Color(contextBgColor[0], contextBgColor[1], contextBgColor[2]));
XSSFColor xssfContextFontColor = new XSSFColor(new java.awt.Color(contextFontColor[0], contextFontColor[1], contextFontColor[2]));
XSSFColor xssfHeaderBgColor = new XSSFColor(new java.awt.Color(headerBgColor[0], headerBgColor[1], headerBgColor[2]));
XSSFColor xssfHeaderFontColor = new XSSFColor(new java.awt.Color(headerFontColor[0], headerFontColor[1], headerFontColor[2]));
XSSFFont headerFont = (XSSFFont) workbook.createFont();
headerFont.setCharSet(HSSFFont.DEFAULT_CHARSET);
headerFont.setFontName("宋体");
if (!(headerFontColor[0] == 0 && headerFontColor[1] == 0 && headerFontColor[2] == 0)) {
headerFont.setColor(xssfHeaderFontColor);
}
headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
headerFont.setFontHeightInPoints((short) headerFontSize);
XSSFCellStyle headerStyle = (XSSFCellStyle) this.createBorderCellStyle(workbook, true);
headerStyle.setFont(headerFont);
headerStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
headerStyle.setFillForegroundColor(xssfHeaderBgColor);
this.setCellStyleAligment(headerStyle, headerAlign);
headerStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
styles.put(GridStyleType.headerStyle.name(), headerStyle);
XSSFFont dataFont = (XSSFFont) workbook.createFont();
if (!(contextFontColor[0] == 0 && contextFontColor[1] == 0 && contextFontColor[2] == 0)) {
dataFont.setColor(xssfContextFontColor);
}
dataFont.setFontHeightInPoints((short) contextFontSize);
dataFont.setCharSet(HSSFFont.DEFAULT_CHARSET);
dataFont.setFontName("宋体");
XSSFCellStyle dataAlignLeftStyle = (XSSFCellStyle) this.createBorderCellStyle(workbook, true);
dataAlignLeftStyle.setFont(dataFont);
dataAlignLeftStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
dataAlignLeftStyle.setFillForegroundColor(xssfContextBgColor);
dataAlignLeftStyle.setVerticalAlignment(CellStyle.ALIGN_CENTER);
dataAlignLeftStyle.setWrapText(true);
dataAlignLeftStyle.setAlignment(CellStyle.ALIGN_LEFT);
styles.put(GridStyleType.dataAlignLeftStyle.name(), dataAlignLeftStyle);
XSSFCellStyle dataAlignCenterStyle = (XSSFCellStyle) this.createBorderCellStyle(workbook, true);
dataAlignCenterStyle.setFont(dataFont);
dataAlignCenterStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
dataAlignCenterStyle.setFillForegroundColor(xssfContextBgColor);
dataAlignCenterStyle.setVerticalAlignment(CellStyle.ALIGN_CENTER);
dataAlignCenterStyle.setWrapText(true);
dataAlignCenterStyle.setAlignment(CellStyle.ALIGN_CENTER);
styles.put(GridStyleType.dataAlignCenterStyle.name(), dataAlignCenterStyle);
XSSFCellStyle dataAlignRightStyle = (XSSFCellStyle) this.createBorderCellStyle(workbook, true);
dataAlignRightStyle.setFont(dataFont);
dataAlignRightStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
dataAlignRightStyle.setFillForegroundColor(xssfContextBgColor);
dataAlignRightStyle.setVerticalAlignment(CellStyle.ALIGN_CENTER);
dataAlignRightStyle.setWrapText(true);
dataAlignRightStyle.setAlignment(CellStyle.ALIGN_RIGHT);
styles.put(GridStyleType.dataAlignRightStyle.name(), dataAlignRightStyle);
XSSFCellStyle dateStyle = (XSSFCellStyle) this.createBorderCellStyle(workbook, true);
CreationHelper helper = workbook.getCreationHelper();
dateStyle.setDataFormat(helper.createDataFormat().getFormat("m/d/yy h:mm"));
dateStyle.setFont(dataFont);
dateStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
dateStyle.setFillForegroundColor(xssfContextBgColor);
dateStyle.setVerticalAlignment(CellStyle.ALIGN_CENTER);
this.setCellStyleAligment(dateStyle, contextFontAlign);
styles.put(GridStyleType.dateStyle.name(), dateStyle);
return styles;
}
@SuppressWarnings("unchecked")
private int putCharts(XSSFWorkbook wb, XSSFSheet sh, Context context) throws Exception {
String barBase64Content = SimpleUtils.getPNGBase64Content( (String)context.get("barChartsData") );
BufferedImage barImage = SimpleUtils.decodeToImage( barBase64Content );
ByteArrayOutputStream barBos = new ByteArrayOutputStream();
ImageIO.write( barImage, "png", barBos );
barBos.flush();
SimpleUtils.setCellPicture(wb, sh, barBos.toByteArray(), 0, 0);
int row = 28;
List< Map<String, Object> > chartDatas = (List< Map<String, Object> >)context.get("chartDatas");
String year = (String)context.get("year");
XSSFCellStyle cellHeadStyle = wb.createCellStyle();
cellHeadStyle.setFillForegroundColor( new XSSFColor( SimpleUtils.getColorRGB4POIColor( "#f5f5f5" ) ) );
cellHeadStyle.setFillPattern( FillPatternType.SOLID_FOREGROUND );
XSSFFont cellHeadFont = wb.createFont();
cellHeadFont.setBold(true);
cellHeadStyle.setFont( cellHeadFont );
int titleCellSize = 14;
Row headRow = sh.createRow( row );
for (int i=0; i<titleCellSize; i++) {
Cell headCell = headRow.createCell( i );
headCell.setCellStyle(cellHeadStyle);
headCell.setCellValue( "Objectives metrics gauge ( " + year + " )" );
}
sh.addMergedRegion( new CellRangeAddress(row, row, 0, titleCellSize-1) );
row = row+1;
int cellLeft = 10;
int rowSpace = 17;
for (Map<String, Object> data : chartDatas) {
Map<String, Object> nodeData = (Map<String, Object>) ( (List<Object>)data.get("datas") ).get(0);
String pngImageData = SimpleUtils.getPNGBase64Content( (String)nodeData.get("outerHTML") );
BufferedImage imageData = SimpleUtils.decodeToImage( pngImageData );
ByteArrayOutputStream imgBos = new ByteArrayOutputStream();
ImageIO.write( imageData, "png", imgBos );
imgBos.flush();
SimpleUtils.setCellPicture(wb, sh, imgBos.toByteArray(), row, 0);
XSSFColor bgColor = new XSSFColor( SimpleUtils.getColorRGB4POIColor( (String)nodeData.get("bgColor") ) );
XSSFColor fnColor = new XSSFColor( SimpleUtils.getColorRGB4POIColor( (String)nodeData.get("fontColor") ) );
XSSFCellStyle cellStyle = wb.createCellStyle();
cellStyle.setFillForegroundColor( bgColor );
cellStyle.setFillPattern( FillPatternType.SOLID_FOREGROUND );
XSSFFont cellFont = wb.createFont();
cellFont.setBold(true);
cellFont.setColor(fnColor);
cellStyle.setFont(cellFont);
int perTitleCellSize = 4;
Row nowRow = sh.createRow(row);
for (int i=0; i<perTitleCellSize; i++) {
Cell cell1 = nowRow.createCell(cellLeft);
cell1.setCellStyle(cellStyle);
cell1.setCellValue( (String)nodeData.get("name") );
}
sh.addMergedRegion( new CellRangeAddress(row, row, cellLeft, cellLeft+perTitleCellSize-1) );
nowRow = sh.createRow(row+1);
Cell cell2 = nowRow.createCell(cellLeft);
cell2.setCellValue( "Target: " + String.valueOf( nodeData.get("target") ) );
nowRow = sh.createRow(row+2);
Cell cell3 = nowRow.createCell(cellLeft);
cell3.setCellValue( "Min: " + String.valueOf( nodeData.get("min") ) );
nowRow = sh.createRow(row+3);
Cell cell4 = nowRow.createCell(cellLeft);
cell4.setCellValue( "Score: " + String.valueOf( nodeData.get("score") ) );
row += rowSpace;
}
return row;
}