Skip to main content

POI Spreadsheet Manager - Part 3

By
Between the holidays and some deadline pressure, it's taken longer to get to this than I'd like. But at last, here it is. This is my CellStyleManager and CellStyles enum.

public class CellStyleManager {
    public static final int BORDERLOC_NONE = 0;
    public static final int BORDERLOC_TOP = 1;
    public static final int BORDERLOC_BOTTOM = 2;
    public static final int BORDERLOC_LEFT = 4;
    public static final int BORDERLOC_RIGHT = 8;
    public static final int BORDERLOC_ALL = BORDERLOC_BOTTOM | BORDERLOC_TOP | BORDERLOC_LEFT | BORDERLOC_RIGHT;
    public static final int BORDERLOC_SIDES = BORDERLOC_LEFT | BORDERLOC_RIGHT;
    public static final int BORDERLOC_TB = BORDERLOC_TOP | BORDERLOC_BOTTOM;

    private final Workbook wb;
    private final Map<cellstyles cellstyle=""> styleMap;
    private final FontManager fontManager;

    public CellStyleManager(Workbook wb) {
        this.wb = wb;
        this.fontManager = new FontManager(wb);
        this.styleMap = new HashMap<cellstyles cellstyle="">();
    }

    public CellStyle get(CellStyles cs) {
        if (!styleMap.containsKey(cs)) {
            styleMap.put(cs, createCellStyle(cs));
        }
        return styleMap.get(cs);
    }

    protected CellStyle createCellStyle(CellStyles cs) {
        XSSFCellStyle style = (XSSFCellStyle) wb.createCellStyle();
        style.setAlignment(cs.getHalign());
        // Default borders
        style.setBorderTop(CellStyle.BORDER_THIN);
        style.setBorderColor(BorderSide.TOP, new XSSFColor(Color.LIGHT_GRAY));
        style.setBorderLeft(CellStyle.BORDER_THIN);
        style.setBorderColor(BorderSide.LEFT, new XSSFColor(Color.LIGHT_GRAY));
        style.setBorderRight(CellStyle.BORDER_THIN);
        style.setBorderColor(BorderSide.RIGHT, new XSSFColor(Color.LIGHT_GRAY));
        style.setBorderBottom(CellStyle.BORDER_THIN);
        style.setBorderColor(BorderSide.BOTTOM, new XSSFColor(Color.LIGHT_GRAY));
        if ((cs.getBorders() & BORDERLOC_TOP) > 0) {
            style.setBorderTop(CellStyle.BORDER_MEDIUM);
            if (cs.getBorderColor() != null) {
                style.setBorderColor(BorderSide.TOP, cs.getBorderColor());
            } else {
                style.setTopBorderColor(new XSSFColor(Color.BLACK));
            }
        }
        if ((cs.getBorders() & BORDERLOC_BOTTOM) > 0) {
            style.setBorderBottom(CellStyle.BORDER_MEDIUM);
            if (cs.getBorderColor() != null) {
                style.setBottomBorderColor(cs.getBorderColor());
            } else {
                style.setBottomBorderColor(new XSSFColor(Color.BLACK));
            }
        }
        if ((cs.getBorders() & BORDERLOC_LEFT) > 0) {
            style.setBorderLeft(CellStyle.BORDER_MEDIUM);
            if (cs.getBorderColor() != null) {
                style.setLeftBorderColor(cs.getBorderColor());
            } else {
                style.setLeftBorderColor(new XSSFColor(Color.BLACK));
            }
        }
        if ((cs.getBorders() & BORDERLOC_RIGHT) > 0) {
            style.setBorderRight(CellStyle.BORDER_MEDIUM);
            if (cs.getBorderColor() != null) {
                style.setRightBorderColor(cs.getBorderColor());
            } else {
                style.setRightBorderColor(new XSSFColor(Color.BLACK));
            }
        }
        style.setFont(fontManager.get(cs.getFont()));
        style.setWrapText(cs.isWordWrap());
        style.setVerticalAlignment(cs.getValign());
        if (cs.getBgColor() != null) {
            style.setFillForegroundColor(cs.getBgColor());
            style.setFillPattern(CellStyle.SOLID_FOREGROUND);

        }
        if (cs.getFormat() != null &amp;&amp; !cs.getFormat().isEmpty()) {
            style.setDataFormat(wb.createDataFormat().getFormat(cs.getFormat()));
        }
        return style;
    }

My CellStyleManager also manages the FontManager. So I can really ignore it once this piece is written. Nothing really interesting here except using some bitwise logic to handle borders. And here is my CellStyles enum.

 public enum CellStyles {
    REPORT_HEADER(Cell.CELL_TYPE_STRING, String.class, null, CellStyleManager.BORDERLOC_NONE, null, CellStyle.ALIGN_CENTER, CellStyle.VERTICAL_BOTTOM, Color.WHITE, Fonts.REPORT_HEADER, true),

    REPORT_SUBHEADER(Cell.CELL_TYPE_STRING, String.class, null, CellStyleManager.BORDERLOC_NONE, null, CellStyle.ALIGN_CENTER, CellStyle.VERTICAL_BOTTOM, Color.WHITE, Fonts.REPORT_SUBHEADER, true),

    COLUMN_HEADERLEFT(Cell.CELL_TYPE_STRING, String.class, null, CellStyleManager.BORDERLOC_BOTTOM, Color.BLACK, CellStyle.ALIGN_LEFT, CellStyle.VERTICAL_BOTTOM, Color.WHITE, Fonts.COLUMN_HEADER, true),

    COLUMN_HEADERRIGHT(Cell.CELL_TYPE_STRING, String.class, null, CellStyleManager.BORDERLOC_BOTTOM, Color.BLACK, CellStyle.ALIGN_RIGHT, CellStyle.VERTICAL_BOTTOM, Color.WHITE, Fonts.COLUMN_HEADER, true),

    CATEGORY(Cell.CELL_TYPE_STRING, String.class, null, CellStyleManager.BORDERLOC_NONE, null, CellStyle.ALIGN_LEFT, CellStyle.VERTICAL_BOTTOM, Color.WHITE, Fonts.CATEGORY, false),

    DATA_TEXT(Cell.CELL_TYPE_STRING, String.class, null, CellStyleManager.BORDERLOC_NONE, null, CellStyle.ALIGN_LEFT, CellStyle.VERTICAL_BOTTOM, Color.WHITE, Fonts.DATA, false),

    DATA_DATE(Cell.CELL_TYPE_NUMERIC, Date.class, "dd MMM yyyy", CellStyleManager.BORDERLOC_NONE, null, CellStyle.ALIGN_RIGHT, CellStyle.VERTICAL_BOTTOM, Color.WHITE, Fonts.DATA, false),

    DATA_INTEGER(Cell.CELL_TYPE_NUMERIC, Integer.class, "###,###,##0", CellStyleManager.BORDERLOC_NONE, null, CellStyle.ALIGN_RIGHT, CellStyle.VERTICAL_BOTTOM, Color.WHITE, Fonts.DATA, false),

    DATA_CURRENCY(Cell.CELL_TYPE_NUMERIC, Double.class, "$###,###,##0.00", CellStyleManager.BORDERLOC_NONE, null, CellStyle.ALIGN_RIGHT, CellStyle.VERTICAL_BOTTOM, Color.WHITE, Fonts.DATA, false),

    DATA_DOUBLE(Cell.CELL_TYPE_NUMERIC, Double.class, "###,###,##0.00", CellStyleManager.BORDERLOC_NONE, null, CellStyle.ALIGN_RIGHT, CellStyle.VERTICAL_BOTTOM, Color.WHITE, Fonts.DATA, false),

    TOTAL_BLANK(Cell.CELL_TYPE_BLANK, String.class, null, CellStyleManager.BORDERLOC_TOP, null, CellStyle.ALIGN_LEFT, CellStyle.VERTICAL_BOTTOM, Color.WHITE, Fonts.TOTAL, false),

    TOTAL_INTEGER(Cell.CELL_TYPE_NUMERIC, Integer.class, "###,###,##0", CellStyleManager.BORDERLOC_TOP, null, CellStyle.ALIGN_RIGHT, CellStyle.VERTICAL_BOTTOM, Color.WHITE, Fonts.TOTAL, false),

    TOTAL_CURRENCY(Cell.CELL_TYPE_NUMERIC, Double.class, "$###,###,##0.00", CellStyleManager.BORDERLOC_TOP, null, CellStyle.ALIGN_RIGHT, CellStyle.VERTICAL_BOTTOM, Color.WHITE, Fonts.TOTAL, false),

    ERROR(Cell.CELL_TYPE_ERROR, String.class, null, CellStyleManager.BORDERLOC_NONE, null, CellStyle.ALIGN_CENTER, CellStyle.VERTICAL_BOTTOM, Color.WHITE, Fonts.ERROR, false);

    private int dataType;
    private Class<?> expected;
    private String format;
    private Color borderColor;
    private int borders;
    private int halign;
    private int valign;
    private Color bgColor;
    private Fonts font;
    private boolean wrap;

    private CellStyles(int dataType, Class<?> expected, String format, int borders, Color borderColor, int halign, int valign,
            Color bgColor, Fonts font, boolean wrap) {
        this.dataType = dataType;
        this.expected = expected;
        this.format = format;
        this.borders = borders;
        this.borderColor = borderColor;
        this.halign = halign;
        this.valign = valign;
        this.bgColor = bgColor;
        this.font = font;
        this.wrap = wrap;
    }

    public int getDataType() {
        return dataType;
    }

    public Class<?> getExpected() {
        return expected;
    }

    public String getFormat() {
        return format;
    }

    public XSSFColor getBorderColor() {
        if (borderColor == null) {
            return new XSSFColor(Color.LIGHT_GRAY);
        } else {
            return new XSSFColor(borderColor);
        }
    }

    public int getBorders() {
        return borders;
    }

    public short getHalign() {
        return (short) halign;
    }

    public short getValign() {
        return (short) valign;
    }

    public XSSFColor getBgColor() {
        if (bgColor == null) {
            return new XSSFColor(Color.WHITE);
        } else {
            return new XSSFColor(bgColor);
        }
    }

    public Fonts getFont() {
        return font;
    }

    public boolean isWordWrap() {
        return wrap;
    }

    public int getRowHeight() {
        //        return (int) Math.round(font.getFontHeight() * 1.5);
        return -1;
    }

    public boolean validate(Object o) {
        if (o == null || o.getClass() == expected) {
            return true;
        } else {
            return false;
        }
    }

 I think I have everything I might need covered here, but if not, I can always add a new cell style definition.

Next up: combining all of this into a report (or at least the abstract base class of a report).
Labels:

Comments

Post a Comment

Popular posts from this blog

Pass data between XPages and existing LS scripts

By
I'm working on modernizing a fairly hefty application with a lot of existing script libraries which we want to leverage within the XPages environment. Here is a technique that works very well. First, create an in-memory document in SSJS. We can set any input values needed for the back end. Then we pass that document to a LS Agent which can work it's magic using the values set in SSJS and use the same document to return values back to the XPage. Here is how it works in detail:
6 comments
Read more

Quick tip: Convert a number to String in EL

By
I just had a need to do this and a Google search didn't immediately turn up a solution. So I thought for a couple of minutes and came up with this: value="0#{numberVar}" This takes advantage of the way Java auto-converts objects to strings when doing a concatenation. So if your number is 13, Java EL turns this into new String("0"+13), which becomes "013". You can then strip off the leading zero or just parse the string back into a number.
Post a Comment
Read more

Project in Review - Part 3: What didn't work

By
Of course, not everything was an unmitigated success. I tried many things that didn't work out. Much of which I've removed and forgotten about, but a few things remain - either scarred into my psyche or woven too deeply to fix. What didn't work Storing my entire configuration in application.properties Using properties files is great. It let me get configuration out of a profile document and into something much easier to edit - particularly configuration that users will never see or maintain (and thus there is no need for an interface for). But I took it too far. The paths to the other databases are there, and that's good. But view aliases are also there, and that was a mistake. I already have a ViewDefinition enum that describes each view and all the information I need to know about it. I could have set view names there, but instead I'm reading them from the application config. I can change where a view is pointing without having to go into my code. Except of co
Post a Comment
Read more