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

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

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