下面列出了org.apache.poi.ss.usermodel.Color#org.apache.poi.xssf.usermodel.XSSFFont 实例代码，或者点击链接到github查看源代码，也可以在右侧发表评论。
@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();
}
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;
}
/**
* 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);
}
/**
* 设置表头的单元格样式
*
* @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;
}
/**
* 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();
}
}
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;
}
@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 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;
}
}
/**
* 大标题样式
*
* @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);
}
/**
* 默认样式
*
* @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 Color getColor(Font font) {
if (helper instanceof HSSFHtmlHelper) {
return ((HSSFWorkbook) sheet.getWorkbook()).getCustomPalette().getColor(font.getColor());
} else {
return ((XSSFFont) font).getXSSFColor();
}
}
/**
* @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");
}
}
/**
* 查找一模一样的字体对象
*
* @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());
}
/**
* 设置表体的单元格样式
*
* @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;
}
private Color getColor(Font font) {
if (helper instanceof HSSFHtmlHelper) {
return ((HSSFWorkbook) sheet.getWorkbook()).getCustomPalette()
.getColor(font.getColor());
} else {
return ((XSSFFont) font).getXSSFColor();
}
}
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;
}
@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 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;
}
@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);
}
/**
* @param cell Cell对象。
* @param wb SXSSFWorkbook对象。
* @param fontSize 字体大小。
* @param bold 是否加粗。
* @param center 是否左右上下居中。
* @param isBorder 是否忽略边框
* @param leftBoolean 左对齐
* @param rightBoolean 右对齐
* @param height 行高
*/
public static void setExcelStyles(Cell cell, SXSSFWorkbook wb, SXSSFRow sxssfRow, Integer fontSize, Boolean bold, Boolean center, Boolean isBorder, Boolean leftBoolean,
Boolean rightBoolean, Integer fontColor, Integer height) {
CellStyle cellStyle = cell.getRow().getSheet().getWorkbook().createCellStyle();
//保证了既可以新建一个CellStyle，又可以不丢失原来的CellStyle 的样式
cellStyle.cloneStyleFrom(cell.getCellStyle());
//左右居中、上下居中
if (center != null && center) {
cellStyle.setAlignment(HorizontalAlignment.CENTER);
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
}
//右对齐
if (rightBoolean != null && rightBoolean) {
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
cellStyle.setAlignment(HorizontalAlignment.RIGHT);
}
//左对齐
if (leftBoolean != null && leftBoolean) {
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
cellStyle.setAlignment(HorizontalAlignment.LEFT);
}
//是否忽略边框
if (isBorder != null && isBorder) {
setBorderColor(cellStyle, isBorder);
}
//设置单元格字体样式
XSSFFont font = (XSSFFont) wb.createFont();
if (bold != null && bold) {
font.setBold(bold);
}
//行高
if (height != null) {
sxssfRow.setHeight((short) (height * 2));
}
font.setFontName("宋体");
font.setFontHeight(fontSize == null ? 12 : fontSize);
cellStyle.setFont(font);
// 点击可查看颜色对应的值： BLACK(8), WHITE(9), RED(10),
font.setColor(IndexedColors.fromInt(fontColor == null ? 8 : fontColor).index);
cell.setCellStyle(cellStyle);
}
/**
* 设置数据：无样式（行、列、单元格样式）
*
* @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++;
}
}
private String getFontIndex(XSSFFont font) {
return fontCache.get(font.getBoldweight() + "_" + font.getItalic() + "_" + font.getFontName() + "_" + font.getFontHeightInPoints() + "_" + font.getColor());
}
private String getFontIndex(XSSFFont font) {
return fontCache.get(font.getBoldweight() + "_" + font.getItalic() + "_" + font.getFontName() + "_" + font.getFontHeightInPoints() + "_" + font.getColor());
}
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;
}
private String getFontIndex(XSSFFont font) {
return fontCache.get(font.getBoldweight() + "_" + font.getItalic() + "_"
+ font.getFontName() + "_" + font.getFontHeightInPoints() + "_"
+ font.getColor());
}
@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;
}