|
Hi; {this and the next couple of emails are because I was told
that the POI developers aren’t on the poi-user email list. If you are I
am sorry for the duplication.) Attached is some code for POI that I think should be added
to it. One warning, I am ex-Microsoft (senior developer in the Windows group,
not Office) so you have all the concerns about my not being able to make use of
internal knowledge. But the attached code makes no use of the Excel file spec. Thanks - dave |
package net.windward.format.xls; import org.apache.poi.hssf.usermodel.HSSFFont; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.hssf.usermodel.HSSFPalette; import org.apache.poi.hssf.usermodel.HSSFCellStyle; import org.apache.poi.hssf.util.HSSFColor;
import java.util.ArrayList;
import java.util.Iterator;
/**
* @author David Thielen
* @version 3.1 Dec 8, 2004
*/
public class PoiUtilities {
private static ArrayList styles = new ArrayList();
// can't instantiate this class.
private PoiUtilities() {
}
/**
* Get a font. If we already have this font, return it. If we don't, create it.
* bugbug - what units for typeOffset?
*
* @param wb The workbook the font will be used in.
* @param boldWeight The font weight. Should be HSSFFont.BOLDWEIGHT_*
* @param color The index of the font color in the color table. 0 for default color.
* @param fontHeight The height of the font in twips.
* @param fontName The font name.
* @param italic true if the font is italic.
* @param strikeout true if the font is striked out.
* @param typeOffset the offset of the font up/down (like the st is 1st).
* @param underline The font underline. Should be HSSFFont.U_*
*
* @return The font.
*/
public static HSSFFont findOrCreateFont(HSSFWorkbook wb, short boldWeight, short color, short fontHeight,
String fontName, boolean italic, boolean strikeout, short typeOffset, byte underline) {
// if it exists, return it
HSSFFont font = wb.findFont(boldWeight, color, fontHeight, fontName, italic, strikeout, typeOffset, underline);
if (font != null)
return font;
// if not, create it
font = wb.createFont();
font.setBoldweight(boldWeight);
font.setColor(color);
font.setFontHeight(fontHeight);
font.setFontName(fontName);
font.setItalic(italic);
font.setStrikeout(strikeout);
font.setTypeOffset(typeOffset);
font.setUnderline(underline);
return font;
}
/**
* Get a color. If we already have this color, return it. If we don't, create it.
*
* @param wb The workbook the font will be used in.
* @param red The red part of the color.
* @param green The green part of the color.
* @param blue The blue part of the color.
*
* @return The index in the workbook colortable. This is a 1-based index.
*/
public static short findOrCreateColor(HSSFWorkbook wb, byte red, byte green, byte blue) {
HSSFPalette palette = wb.getCustomPalette();
HSSFColor ssClr = palette.findColor(red, green, blue);
if (ssClr != null)
return ssClr.getIndex();
return palette.addColor(red, green, blue).getIndex();
}
/**
* Get a style. If we already have this style, return it. If we don't, create it.
* bugbug - should list allowed psfi's for dataFormat, fillPat
* bugbug - should list the units for indent, rotation.
*
* @param wb The workbook the font will be used in.
* @param align set the type of horizontal alignment for the cell. Use ALIGN_*
* @param brdrLeft set the type of border to use for the left border of the cell. Use BORDER_*
* @param brdrTop set the type of border to use for the top border of the cell. Use BORDER_*
* @param brdrRight set the type of border to use for the right border of the cell. Use BORDER_*
* @param brdrBottom set the type of border to use for the bottom border of the cell. Use BORDER_*
* @param brdrLeftClr set the color to use for the left border.
* @param brdrTopClr set the color to use for the top border.
* @param brdrRightClr set the color to use for the right border.
* @param brdrBottomClr set the color to use for the bottom border.
* @param dataFormat set the data format (must be a valid format).
* @param fillForeClr set the background fill color.
* @param fillBackClr set the foreground fill color.
* @param fillPat setting to one fills the cell with the foreground color.
* @param font set the font for this style.
* @param hidden set the cell's using this style to be hidden.
* @param indent set the number of spaces to indent the text in the cell.
* @param locked set the cell's using this style to be locked.
* @param rotation set the degree of rotation for the text in the cell.
* @param vertAlign set the type of vertical alignment for the cell. Use VERTICAL_*
* @param wrapped set whether the text should be wrapped.
*
* @return The requested style.
*/
public static HSSFCellStyle findOrCreateStyle(HSSFWorkbook wb, short align, short brdrLeft, short brdrTop, short brdrRight,
short brdrBottom, short brdrLeftClr, short brdrTopClr, short brdrRightClr, short brdrBottomClr,
short dataFormat, short fillForeClr, short fillBackClr, short fillPat, HSSFFont font, boolean hidden,
short indent, boolean locked, short rotation, short vertAlign, boolean wrapped) {
// this approach assumes a small number of styles. For a large number of styles it would be better
// to generate a hashcode and have a list for each hashcode value.
for (Iterator it=styles.iterator(); it.hasNext(); ) {
HSSFCellStyle style = (HSSFCellStyle) it.next();
if (style.getAlignment() != align)
continue;
if (style.getBorderLeft() != brdrLeft)
continue;
if (style.getBorderTop() != brdrTop)
continue;
if (style.getBorderRight() != brdrRight)
continue;
if (style.getBorderBottom() != brdrBottom)
continue;
if (style.getLeftBorderColor() != brdrLeftClr)
continue;
if (style.getTopBorderColor() != brdrTopClr)
continue;
if (style.getRightBorderColor() != brdrRightClr)
continue;
if (style.getBottomBorderColor() != brdrBottomClr)
continue;
if (style.getDataFormat() != dataFormat)
continue;
if (style.getFillForegroundColor() != fillForeClr)
continue;
if (style.getFillBackgroundColor() != fillBackClr)
continue;
if (style.getFillPattern() != fillPat)
continue;
if (style.getFontIndex() != font.getIndex())
continue;
if (style.getHidden() != hidden)
continue;
if (style.getIndention() != indent)
continue;
if (style.getLocked() != locked)
continue;
if (style.getRotation() != rotation)
continue;
if (style.getVerticalAlignment() != vertAlign)
continue;
if (style.getWrapText() != wrapped)
continue;
// we have a match!
return style;
}
// create a new one.
HSSFCellStyle style = wb.createCellStyle();
style.setAlignment(align);
style.setBorderLeft(brdrLeft);
style.setBorderTop(brdrTop);
style.setBorderRight(brdrRight);
style.setBorderBottom(brdrBottom);
style.setLeftBorderColor(brdrLeftClr);
style.setTopBorderColor(brdrTopClr);
style.setRightBorderColor(brdrRightClr);
style.setBottomBorderColor(brdrBottomClr);
style.setDataFormat(dataFormat);
style.setFillForegroundColor(fillForeClr);
style.setFillBackgroundColor(fillBackClr);
style.setFillPattern(fillPat);
style.setFont(font);
style.setHidden(hidden);
style.setIndention(indent);
style.setLocked(locked);
style.setRotation(rotation);
style.setVerticalAlignment(vertAlign);
style.setWrapText(wrapped);
styles.add(style);
return style;
}
}
--------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
