org.apache.poi.hssf.usermodel.HSSFPalette Java Examples

public short getNearestColor( final Color awtColor ) {
  if ( lastUsedColor > 64 ) {
    // we ran out of palette... try to get nearest color then
    return StaticExcelColorSupport.getNearestColor( awtColor, usedTripplets );
  }

  final HSSFPalette palette = workbook.getCustomPalette();
  final HSSFColor hssfColor =
      palette.findColor( (byte) awtColor.getRed(), (byte) awtColor.getGreen(), (byte) awtColor.getBlue() );

  if ( hssfColor != null && hssfColor.getIndex() < lastUsedColor ) {
    return hssfColor.getIndex();
  } else {
    palette.setColorAtIndex( lastUsedColor, (byte) awtColor.getRed(), (byte) awtColor.getGreen(), (byte) awtColor
        .getBlue() );
    final HSSFColor color = palette.getColor( lastUsedColor );
    usedTripplets.put( color.getHexString(), color );
    return lastUsedColor++;
  }
}
 

/**
 * 初始化默认单元格样式
 *
 * @param workbook workbook
 */
protected void initCellStyle(Workbook workbook) {
    if (useDefaultStyle) {
        defaultCellStyleMap = new EnumMap<>(HtmlTableParser.HtmlTag.class);
        defaultCellStyleMap.put(HtmlTableParser.HtmlTag.th, new ThDefaultCellStyle().supply(workbook));
        defaultCellStyleMap.put(HtmlTableParser.HtmlTag.td, new TdDefaultCellStyle().supply(workbook));
        defaultCellStyleMap.put(HtmlTableParser.HtmlTag.link, new LinkDefaultCellStyle().supply(workbook));
    } else {
        if (workbook instanceof HSSFWorkbook) {
            HSSFPalette palette = ((HSSFWorkbook) workbook).getCustomPalette();
            customColor = new CustomColor(true, palette);
        } else {
            customColor = new CustomColor();
        }
    }
}
 

/**
 * Get an HSSFPalette index for a workbook that closely approximates the passed in colour.
 * @param workbook
 * The workbook for which the colour is being sought.
 * @param colour
 * The colour, in the form "rgb(<i>r</i>, <i>g</i>, <i>b</i>)".
 * @return
 * The index into the HSSFPallete for the workbook for a colour that approximates the passed in colour.
 */
private short getHColour( HSSFWorkbook workbook, String colour ) {
	int[] rgbInt = ColorUtil.getRGBs(colour);
	if( rgbInt == null ) {
		return 0;
	}
	
	byte[] rgbByte = new byte[] { (byte)rgbInt[0], (byte)rgbInt[1], (byte)rgbInt[2] };
	HSSFPalette palette = workbook.getCustomPalette();
	
	HSSFColor result = palette.findColor(rgbByte[0], rgbByte[1], rgbByte[2]);
	if( result == null) {
		if( paletteIndex > minPaletteIndex ) {
			--paletteIndex;
			palette.setColorAtIndex(paletteIndex, rgbByte[0], rgbByte[1], rgbByte[2]);
			return paletteIndex;
		} else {
			result = palette.findSimilarColor(rgbByte[0], rgbByte[1], rgbByte[2]);
		}
	}
	return result.getIndex();
}
 

/**
 * 创建颜色。
 * 
 * 写本方法的原因是：从2003版本的模板中复制单元格颜色时，会出现颜色失真的问题。
 * 
 * 但最终也没有解决。因为：当单元格的颜色设置为非标准颜色时，就会失真，但设置为标准颜色时，是正常的。
 * 
 * 也因为此，本方法与 i_ToCellStyle.setFillBackgroundColor(i_FromCellStyle.getFillBackgroundColor()); 的效果是相同的。
 * 
 * 本方法作为研究使用而保留下来，但不没有使用价值。
 * 
 * @author      ZhengWei(HY)
 * @createDate  2017-03-21
 * @version     v1.0
 *
 * @param i_FromColor
 * @param i_DataWorkbook
 * @return
 */
@Deprecated
public final static HSSFColor createColor(HSSFColor i_FromColor ,HSSFWorkbook i_DataWorkbook)
{
    short [] v_RGBHex    = i_FromColor.getTriplet();
    byte     v_ByteRed   = (byte)v_RGBHex[0];
    byte     v_ByteGreen = (byte)v_RGBHex[1];
    byte     v_ByteBlue  = (byte)v_RGBHex[2];
    
    HSSFPalette v_Palette   = i_DataWorkbook.getCustomPalette();
    HSSFColor   v_DataColor = v_Palette.findColor(v_ByteRed ,v_ByteGreen ,v_ByteBlue);
    
    if ( v_DataColor == null )
    {
        v_Palette.setColorAtIndex(i_FromColor.getIndex() ,v_ByteRed ,v_ByteGreen ,v_ByteBlue);
        
        return v_Palette.getColor(i_FromColor.getIndex());
    }
    
    return  v_DataColor;
}
 

private HSSFCellStyle createHSSFCellStyle(Workbook wb, int[] bgColor, int[] fontColor, int fontSize) {
	HSSFWorkbook workbook = (HSSFWorkbook) wb;
	HSSFPalette palette = workbook.getCustomPalette();
	
	palette.setColorAtIndex((short) 9, (byte) fontColor[0], (byte) fontColor[1], (byte) fontColor[2]);
	palette.setColorAtIndex((short) 10, (byte) bgColor[0], (byte) bgColor[1], (byte) bgColor[2]);

	HSSFFont titleFont = workbook.createFont();
	titleFont.setCharSet(HSSFFont.DEFAULT_CHARSET);
	titleFont.setFontName("宋体");
	titleFont.setColor((short) 9);
	titleFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
	titleFont.setFontHeightInPoints((short) fontSize);

	HSSFCellStyle titleStyle = (HSSFCellStyle) createBorderCellStyle(workbook, true);
	titleStyle.setFont(titleFont);
	titleStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
	titleStyle.setFillForegroundColor((short) 10);
	titleStyle.setAlignment(CellStyle.ALIGN_CENTER);
	titleStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);

	return titleStyle;
}
 

@Override
public Font correctFontColorIfBackground(FontManager fm, Workbook wb, BirtStyle birtStyle, Font font) {
	HSSFPalette palette = ((HSSFWorkbook)wb).getCustomPalette();		
	
	CSSValue bgColour = birtStyle.getProperty( StyleConstants.STYLE_BACKGROUND_COLOR );
	int bgRgb[] = parseColour( bgColour == null ? null : bgColour.getCssText(), "white" );

	short fgRgb[] = HSSFColor.BLACK.triplet;
	if( ( font != null ) && ( font.getColor() != Short.MAX_VALUE ) ) {
		fgRgb = palette.getColor(font.getColor()).getTriplet();
	}
	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;
	}
}
 

protected Font getFont(boolean bold, boolean italic, boolean underline, Color c) {
	Short color = null;
	if (c == null) c = Color.BLACK;
	if (c != null) {
		String colorId = Integer.toHexString(c.getRGB());
		color = iColors.get(colorId);
		if (color == null) {
			HSSFPalette palette = ((HSSFWorkbook)iWorkbook).getCustomPalette();
			HSSFColor clr = palette.findSimilarColor(c.getRed(), c.getGreen(), c.getBlue());
			color = (clr == null ? IndexedColors.BLACK.getIndex() : clr.getIndex());
			iColors.put(colorId, color);
		}
	}
	String fontId = (bold ? "b" : "") + (italic ? "i" : "") + (underline ? "u" : "") + (color == null ? "" : color);
	Font font = iFonts.get(fontId);
	if (font == null) {
		font = iWorkbook.createFont();
		font.setBold(bold);
		font.setItalic(italic);
		font.setUnderline(underline ? Font.U_SINGLE : Font.U_NONE);
		font.setColor(color);
		font.setFontHeightInPoints((short)10);
		font.setFontName("Arial");
		iFonts.put(fontId, font);
	}
	return font;
}
 

private HSSFColor buildHSSFColor(HSSFWorkbook wb,String colorStr){
	String[] color=colorStr.split(",");
	HSSFPalette palette=wb.getCustomPalette();
	byte r=BigInteger.valueOf(Integer.valueOf(color[0])).byteValue();
	byte g=BigInteger.valueOf(Integer.valueOf(color[1])).byteValue();
	byte b=BigInteger.valueOf(Integer.valueOf(color[2])).byteValue();
	HSSFColor targetColor=palette.findColor(r,g,b);
	if(targetColor==null){
		palette.setColorAtIndex(HSSFColorPredefined.LAVENDER.getIndex(), r, g,b);
		targetColor=palette.getColor(HSSFColorPredefined.LAVENDER.getIndex());
	}
	return targetColor;
}
 

protected CellStyle getMeetingFooterStyle(P p, P parent, Color bgColor) {
	if (bgColor == null) bgColor = Color.WHITE;
	String styleId = "meeting-footer-" + Integer.toHexString(bgColor.getRGB());
	CellStyle style = iStyles.get(styleId);
	if (style == null) {
		style = iWorkbook.createCellStyle();
        style.setBorderBottom(BorderStyle.THICK);
        style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderLeft(BorderStyle.THICK);
        style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderRight(BorderStyle.THICK);
        style.setRightBorderColor(IndexedColors.BLACK.getIndex());
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.TOP);
        style.setFont(getFont(parent));
        String colorId = Integer.toHexString(bgColor.getRGB());
		Short color = iColors.get(colorId);
		if (color == null) {
			HSSFPalette palette = ((HSSFWorkbook)iWorkbook).getCustomPalette();
			HSSFColor clr = palette.findSimilarColor(bgColor.getRed(), bgColor.getGreen(), bgColor.getBlue());
			color = (clr == null ? IndexedColors.WHITE.getIndex() : clr.getIndex());
			iColors.put(colorId, color);
		}
        style.setFillForegroundColor(color);
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        style.setWrapText(true);
        iStyles.put(styleId, style);
	}
	return style;
}
 

protected Font getFont(boolean bold, boolean italic, boolean underline, Color c) {
	Short color = null;
	if (c == null) c = Color.BLACK;
	if (c != null) {
		String colorId = Integer.toHexString(c.getRGB());
		color = iColors.get(colorId);
		if (color == null) {
			HSSFPalette palette = ((HSSFWorkbook)iWorkbook).getCustomPalette();
			HSSFColor clr = palette.findSimilarColor(c.getRed(), c.getGreen(), c.getBlue());
			color = (clr == null ? IndexedColors.BLACK.getIndex() : clr.getIndex());
			iColors.put(colorId, color);
		}
	}
	String fontId = (bold ? "b" : "") + (italic ? "i" : "") + (underline ? "u" : "") + (color == null ? "" : color);
	Font font = iFonts.get(fontId);
	if (font == null) {
		font = iWorkbook.createFont();
		font.setBold(bold);
		font.setItalic(italic);
		font.setUnderline(underline ? Font.U_SINGLE : Font.U_NONE);
		font.setColor(color);
		font.setFontHeightInPoints((short)iFontSize);
		font.setFontName(iFontName);
		iFonts.put(fontId, font);
	}
	return font;
}
 

protected CellStyle getMeetingHeaderStyle(P p, P parent, Color bgColor) {
	if (bgColor == null) bgColor = Color.WHITE;
	String styleId = "meeting-header-" + Integer.toHexString(bgColor.getRGB()) + (p.iItalics ? "-italics" : "");
	CellStyle style = iStyles.get(styleId);
	if (style == null) {
		style = iWorkbook.createCellStyle();
        style.setBorderTop(BorderStyle.THICK);
        style.setTopBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderLeft(BorderStyle.THICK);
        style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderRight(BorderStyle.THICK);
        style.setRightBorderColor(IndexedColors.BLACK.getIndex());
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.TOP);
        style.setFont(getFont(parent));
        String colorId = Integer.toHexString(bgColor.getRGB());
		Short color = iColors.get(colorId);
		if (color == null) {
			HSSFPalette palette = ((HSSFWorkbook)iWorkbook).getCustomPalette();
			HSSFColor clr = palette.findSimilarColor(bgColor.getRed(), bgColor.getGreen(), bgColor.getBlue());
			color = (clr == null ? IndexedColors.BLACK.getIndex() : clr.getIndex());
			iColors.put(colorId, color);
		}
        style.setFillForegroundColor(color);
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        style.setWrapText(true);
        iStyles.put(styleId, style);
	}
	return style;
}
 

private Map<String, CellStyle> createHSSFCellStyles(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>();

	HSSFWorkbook workbook = (HSSFWorkbook) wb;
	HSSFPalette palette = workbook.getCustomPalette();
	palette.setColorAtIndex((short) 11, (byte) contextBgColor[0], (byte) contextBgColor[1], (byte) contextBgColor[2]);
	palette.setColorAtIndex((short) 12, (byte) contextFontColor[0], (byte) contextFontColor[1], (byte) contextFontColor[2]);
	palette.setColorAtIndex((short) 13, (byte) headerBgColor[0], (byte) headerBgColor[1], (byte) headerBgColor[2]);
	palette.setColorAtIndex((short) 14, (byte) headerFontColor[0], (byte) headerFontColor[1], (byte) headerFontColor[2]);

	HSSFFont headerFont = workbook.createFont();
	headerFont.setCharSet(HSSFFont.DEFAULT_CHARSET);
	headerFont.setFontName("宋体");
	headerFont.setColor((short) 14);
	headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
	headerFont.setFontHeightInPoints((short) headerFontSize);
	CellStyle headerStyle = this.createBorderCellStyle(workbook, true);

	headerStyle.setFont(headerFont);
	headerStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
	headerStyle.setFillForegroundColor((short) 13);
	this.setCellStyleAligment(headerStyle, headerAlign);
	headerStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
	styles.put(GridStyleType.headerStyle.name(), headerStyle);

	HSSFFont dataFont = workbook.createFont();
	dataFont.setColor((short) 12);
	dataFont.setFontHeightInPoints((short) contextFontSize);
	dataFont.setCharSet(HSSFFont.DEFAULT_CHARSET);
	dataFont.setFontName("宋体");

	CellStyle dataAlignLeftStyle = this.createBorderCellStyle(workbook, true);
	dataAlignLeftStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
	dataAlignLeftStyle.setFillForegroundColor((short) 11);
	dataAlignLeftStyle.setFont(dataFont);
	dataAlignLeftStyle.setVerticalAlignment(CellStyle.ALIGN_CENTER);
	dataAlignLeftStyle.setWrapText(true);
	dataAlignLeftStyle.setAlignment(CellStyle.ALIGN_LEFT);
	styles.put(GridStyleType.dataAlignLeftStyle.name(), dataAlignLeftStyle);

	CellStyle dataAlignCenterStyle = this.createBorderCellStyle(workbook, true);
	dataAlignCenterStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
	dataAlignCenterStyle.setFillForegroundColor((short) 11);
	dataAlignCenterStyle.setFont(dataFont);
	dataAlignCenterStyle.setVerticalAlignment(CellStyle.ALIGN_CENTER);
	dataAlignCenterStyle.setWrapText(true);
	dataAlignCenterStyle.setAlignment(CellStyle.ALIGN_CENTER);
	styles.put(GridStyleType.dataAlignCenterStyle.name(), dataAlignCenterStyle);

	CellStyle dataAlignRightStyle = this.createBorderCellStyle(workbook, true);
	dataAlignRightStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
	dataAlignRightStyle.setFillForegroundColor((short) 11);
	dataAlignRightStyle.setFont(dataFont);
	dataAlignRightStyle.setVerticalAlignment(CellStyle.ALIGN_CENTER);
	dataAlignRightStyle.setWrapText(true);
	dataAlignRightStyle.setAlignment(CellStyle.ALIGN_RIGHT);
	styles.put(GridStyleType.dataAlignRightStyle.name(), dataAlignRightStyle);

	CellStyle dateStyle = this.createBorderCellStyle(workbook, true);
	CreationHelper helper = workbook.getCreationHelper();
	dateStyle.setDataFormat(helper.createDataFormat().getFormat("m/d/yy h:mm"));
	dateStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
	dateStyle.setFillForegroundColor((short) 11);
	dateStyle.setFont(dataFont);
	dateStyle.setVerticalAlignment(CellStyle.ALIGN_CENTER);
	this.setCellStyleAligment(dateStyle, contextFontAlign);
	styles.put(GridStyleType.dateStyle.name(), dateStyle);

	return styles;
}
 

public HSSFColor getColor( final short index ) {
  final HSSFPalette palette = workbook.getCustomPalette();
  return palette.getColor( index );
}
 

private static HSSFColor getSimilarColor(HSSFWorkbook workbook, Color color) {
    HSSFPalette palette = workbook.getCustomPalette();
    HSSFColor result = palette.findSimilarColor(color.getRed(), color.getGreen(), color.getBlue());
    return result == null ? HSSFColor.HSSFColorPredefined.AUTOMATIC.getColor() : result;
}
 

public void setPalette(HSSFPalette palette) {
    this.palette = palette;
}
 

public HSSFPalette getPalette() {
    return this.palette;
}
 

public CustomColor(boolean isXls, HSSFPalette palette) {
    this.isXls = isXls;
    this.palette = palette;
}
 

public static Short getCustomColorIndex(@NonNull CustomColor customColor, @NonNull int[] rgb) {
    HSSFPalette palette = customColor.getPalette();
    short index = (short) customColor.getColorIndex().getAndIncrement();
    palette.setColorAtIndex(index, (byte) rgb[0], (byte) rgb[1], (byte) rgb[2]);
    return index;
}
 

private void xlsToHtml() throws Throwable {
	FileOutputStream output = new FileOutputStream(new File(htmlPath));
	StringBuffer htmlHeaderSB = new StringBuffer();
	htmlHeaderSB.append("<html xmlns:o='urn:schemas-microsoft-com:office:office' xmlns:x='urn:schemas-microsoft-com:office:excel' "
			+ "xmlns='http://www.w3.org/TR/REC-html40'>");
	htmlHeaderSB.append("<head><meta http-equiv=Content-Type content='text/html; charset=utf-8'><meta name=ProgId content=Excel.Sheet>"
			+ "</head><body>");
	output.write(htmlHeaderSB.toString().getBytes());
	HSSFSheet sheet;
	HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(filePath)); // 获整个Excel
	for (int sheetIndex = 0; sheetIndex < workbook.getNumberOfSheets(); sheetIndex++) {
		if (workbook.getSheetAt(sheetIndex) != null) {
			sheet = workbook.getSheetAt(sheetIndex);// 获得不为空的这个sheet
			if (sheet != null) {
				int firstRowNum = sheet.getFirstRowNum(); // 第一行
				int lastRowNum = sheet.getLastRowNum(); // 最后一行
				// 构造Table
				output.write(("<table width=\"100%\" style=\"border:1px solid #000;border-width:1px 0 0 1px;margin:2px 0 2px 0;"
						+ "border-collapse:collapse;\">").getBytes());
				for (int rowNum = firstRowNum; rowNum <= lastRowNum; rowNum++) {
					if (sheet.getRow(rowNum) != null) {// 如果行不为空，
						HSSFRow row = sheet.getRow(rowNum);
						short firstCellNum = row.getFirstCellNum(); // 该行的第一个单元格
						short lastCellNum = row.getLastCellNum(); // 该行的最后一个单元格
						int height = (int) (row.getHeight() / 15.625); // 行的高度
						output.write(("<tr height=\"" + height + "\" style=\"border:1px solid #000;border-width:0 1px 1px 0;"
								+ "margin:2px 0 2px 0;\">").getBytes());
						for (short cellNum = firstCellNum; cellNum <= lastCellNum; cellNum++) { // 循环该行的每一个单元格
							HSSFCell cell = row.getCell(cellNum);
							if (cell != null) {
								if (cell.getCellType() != HSSFCell.CELL_TYPE_BLANK) {
									StringBuffer tdStyle = new StringBuffer("<td style=\"border:1px solid #000; border-width:0 1px 1px 0;"
											+ "margin:2px 0 2px 0; ");
									HSSFCellStyle cellStyle = cell.getCellStyle();
									HSSFPalette palette = workbook.getCustomPalette(); // 类HSSFPalette用于求颜色的国际标准形式
									HSSFColor hColor = palette.getColor(cellStyle.getFillForegroundColor());
									HSSFColor hColor2 = palette.getColor(cellStyle.getFont(workbook).getColor());
									String bgColor = convertToStardColor(hColor);// 背景颜色
									short boldWeight = cellStyle.getFont(workbook).getBoldweight(); // 字体粗细
									short fontHeight = (short) (cellStyle.getFont(workbook).getFontHeight() / 2); // 字体大小
									String fontColor = convertToStardColor(hColor2); // 字体颜色
									if (bgColor != null && !"".equals(bgColor.trim())) {
										tdStyle.append(" background-color:");
										tdStyle.append(bgColor);
										tdStyle.append("; ");
									}
									if (fontColor != null && !"".equals(fontColor.trim())) {
										tdStyle.append(" color:");
										tdStyle.append(fontColor);
										tdStyle.append("; ");
									}
									tdStyle.append(" font-weight:");
									tdStyle.append(boldWeight);
									tdStyle.append("; ");
									tdStyle.append(" font-size: ");
									tdStyle.append(fontHeight);
									tdStyle.append("%;");
									output.write((tdStyle + "\"").getBytes());

									int width = (int) (sheet.getColumnWidth(cellNum) / 35.7); //
									int cellRegionCol = getMergerCellRegionCol(sheet, rowNum, cellNum); // 合并的列（solspan）
									int cellRegionRow = getMergerCellRegionRow(sheet, rowNum, cellNum);// 合并的行（rowspan）
									String align = convertAlignToHtml(cellStyle.getAlignment()); //
									String vAlign = convertVerticalAlignToHtml(cellStyle.getVerticalAlignment());

									output.write((" align=\"" + align + "\" valign=\"" + vAlign + "\" width=\"" + width + "\" ").getBytes());
									output.write((" colspan=\"" + cellRegionCol + "\" rowspan=\"" + cellRegionRow + "\"").getBytes());
									output.write((">" + getCellValue(cell) + "</td>").getBytes());
								}
							}
						}
						output.write("</tr>".getBytes());
					}
				}
				output.write(("</table>").getBytes());
			}
		}
	}
	output.write(("</body></html>").getBytes());
	output.close();
}