Index: src/documentation/content/xdocs/spreadsheet/quick-guide.xml =================================================================== --- src/documentation/content/xdocs/spreadsheet/quick-guide.xml (revision 1724913) +++ src/documentation/content/xdocs/spreadsheet/quick-guide.xml (working copy) @@ -74,6 +74,7 @@
  • Autofilters
  • Conditional Formatting
  • Hiding and Un-Hiding Rows
    • +
  • Setting Cell Properties
    • Features @@ -2107,34 +2108,34 @@

    - -
    Hiding and Un-Hiding Rows -

    - Using Excel, it is possible to hide a row on a worksheet by selecting that row (or rows), - right clicking once on the right hand mouse button and selecting 'Hide' from the pop=up menu that appears. -

    -

    - To emulate this using POI, simply call the setZeroHeight() method on an instance of either - XSSFRow or HSSFRow (the method is defined on the ss.usermodel.Row interface that both classes implement), like this: -

    - + +
    Hiding and Un-Hiding Rows +

    + Using Excel, it is possible to hide a row on a worksheet by selecting that row (or rows), + right clicking once on the right hand mouse button and selecting 'Hide' from the pop=up menu that appears. +

    +

    + To emulate this using POI, simply call the setZeroHeight() method on an instance of either + XSSFRow or HSSFRow (the method is defined on the ss.usermodel.Row interface that both classes implement), like this: +

    + Workbook workbook = new XSSFWorkbook(); // OR new HSSFWorkbook() Sheet sheet = workbook.createSheet(0); Row row = workbook.createRow(0); row.setZeroHeight(); - -

    - If the file were saved away to disc now, then the first row on the first sheet would not be visible. -

    -

    - Using Excel, it is possible to unhide previously hidden rows by selecting the row above and the row below - the one that is hidden and then pressing and holding down the Ctrl key, the Shift and the pressing - the number 9 before releasing them all. -

    -

    - To emulate this behaviour using POI do something like this: -

    - + +

    + If the file were saved away to disc now, then the first row on the first sheet would not be visible. +

    +

    + Using Excel, it is possible to unhide previously hidden rows by selecting the row above and the row below + the one that is hidden and then pressing and holding down the Ctrl key, the Shift and the pressing + the number 9 before releasing them all. +

    +

    + To emulate this behaviour using POI do something like this: +

    + Workbook workbook = WorkbookFactory.create(new File(.......)); Sheet = workbook.getSheetAt(0); Iterator<Row> row Iter = sheet.iterator(); @@ -2144,15 +2145,63 @@ row.setZeroHeight(false); } } - -

    - If the file were saved away to disc now, any previously hidden rows on the first sheet of the workbook would now be visible. -

    -

    - The example illustrates two features. Firstly, that it is possible to unhide a row simply by calling the setZeroHeight() - method and passing the boolean value 'false'. Secondly, it ilustrates how to test whther a row is hidden or not. - Simply call the getZeroHeight() method and it will return 'true' if the row is hidden, 'false' otherwise. -

    -
    + +

    + If the file were saved away to disc now, any previously hidden rows on the first sheet of the workbook would now be visible. +

    +

    + The example illustrates two features. Firstly, that it is possible to unhide a row simply by calling the setZeroHeight() + method and passing the boolean value 'false'. Secondly, it ilustrates how to test whther a row is hidden or not. + Simply call the getZeroHeight() method and it will return 'true' if the row is hidden, 'false' otherwise. +

    +
    + +
    Setting Cell Properties +

    + Sometimes it is easier or more efficient to create a spreadsheet with basic styles and then apply special styles to certain cells + such as drawing borders around a range of cells or setting fills for a region. CellUtil.setCellProperties lets you do that without creating + a bunch of unnecessary intermediate styles in your spreadsheet. +

    +

    + Properties are created as a HashMap and applied to a cell in the following manner. +

    + + Workbook workbook = new XSSFWorkbook(); // OR new HSSFWorkbook() + Sheet sheet = workbook.createSheet("Sheet1"); + HashMap<String, Object> properties = new HashMap<String, Object>(); + + // border around a cell + properties.put(CellUtil.BORDER_TOP, CellStyle.BORDER_MEDIUM); + properties.put(CellUtil.BORDER_BOTTOM, CellStyle.BORDER_MEDIUM); + properties.put(CellUtil.BORDER_LEFT, CellStyle.BORDER_MEDIUM); + properties.put(CellUtil.BORDER_RIGHT, CellStyle.BORDER_MEDIUM); + + // Give it a color (RED) + properties.put(CellUtil.TOP_BORDER_COLOR, IndexedColors.RED.getIndex()); + properties.put(CellUtil.BOTTOM_BORDER_COLOR, IndexedColors.RED.getIndex()); + properties.put(CellUtil.LEFT_BORDER_COLOR, IndexedColors.RED.getIndex()); + properties.put(CellUtil.RIGHT_BORDER_COLOR, IndexedColors.RED.getIndex()); + + // Apply the borders to the cell at B2 + Row row = sheet.createRow(1); + Cell cell = row.createCell(1); + CellUtil.setCellStyleProperties(cell, properties); + + // Apply the borders to a 3x3 region starting at D4 + for (int ix=3; ix <= 5; ix++) { + row = sheet.createRow(ix); + for (int iy = 3; iy <= 5; iy++) { + cell = row.createCell(iy); + CellUtil.setCellStyleProperties(cell, properties); + } + } + + +

    + NOTE: This does not replace the properties of the cell, it merges the properties you have put into the HashMap with the + cell's existing style properties. If a property already exists, it is replaced with the new property. If a property does not + exist, it is added. This method will not remove CellStyle properties. +

    +