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

XPages Application Framework (Part 1?)

By
Note: I changed projects and priorities after my last POI article, resulting in a long hiatus. I anticipate using that framework on my current project and will likely refine and complete my related article series. For the past several months, I've been fortunate enough to lead a project overhaul from traditional Domino Webapp to XPages. I had a few goals in mind, but the top three were integrating Bootstrap, embracing MVC principles, and eliminating all SSJS . I imagine the motivation for Bootstrap integration is self-explanatory. There is an OpenNTF project out there called Bootstrap4XPages. I didn't use that for a number of reasons - the main being a policy in the current environment. But you may ask yourself (if you didn't follow that link), why so much hatred for SSJS? I have a litany of reasons. It impacts readability of the XPage source; it hurts maintainability when logic is scattered across dozens of XPages, custom controls, and script libraries; it mixes log...
Post a Comment
Read more

Rows per page selection: Part 1

By
I was asked to create a control that would allow users to select the number of rows per page in a view/repeat control (the application uses both). It seemed simple at first, but I ran into a few issues that I thought I'd share the solutions to. First, lets start at the beginning. I went through the relevant design elements and set row="#{viewScope.tableRows}" , and I created an xp:comboBox with value="#{viewScope.tableRows}" and added items for 20, 30, 50, and 100, and I assigned it an onChange event handler that did a partial execution and partial refresh of a div containing the combo box, pager and the table. Then I started fixing all the problems. Problem 1: The combobox value was a string, but the rows parameter requires an integer. This was causing IllegalArgumentException / java.lang.String incompatible with java.lang.Integer. I added a NumberConverter, but this only slightly changed the exception message to java.lang.Long incompatible with java.lang....
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