Example usage for org.apache.poi.ss.usermodel Sheet createFreezePane

List of usage examples for org.apache.poi.ss.usermodel Sheet createFreezePane

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. Sheet
  6. createFreezePane

Introduction

In this page you can find the example usage for org.apache.poi.ss.usermodel Sheet createFreezePane.

Prototype

void createFreezePane(int colSplit, int rowSplit);

Source Link

Document

Creates a split (freezepane).

Usage

From source file:hrytsenko.gscripts.io.XlsFiles.java

License:Apache License

private static Row createHeader(Sheet sheet) {
    sheet.createFreezePane(0, 1);
    return sheet.createRow(0);
}

From source file:itpreneurs.itp.report.archive.BusinessPlan.java

License:Apache License

public static void main(String[] args) throws Exception {
    Workbook wb;//from   w w w .jav  a2 s  . co m

    // if(args.length > 0 && args[0].equals("-xls")) wb = new
    // HSSFWorkbook();
    // else wb = new XSSFWorkbook();

    wb = new XSSFWorkbook();

    Map<String, CellStyle> styles = createStyles(wb);

    Sheet sheet = wb.createSheet("Business Plan");

    // turn off gridlines
    sheet.setDisplayGridlines(false);
    sheet.setPrintGridlines(false);
    sheet.setFitToPage(true);
    sheet.setHorizontallyCenter(true);
    PrintSetup printSetup = sheet.getPrintSetup();
    printSetup.setLandscape(true);

    // the following three statements are required only for HSSF
    sheet.setAutobreaks(true);
    printSetup.setFitHeight((short) 1);
    printSetup.setFitWidth((short) 1);

    // the header row: centered text in 48pt font
    Row headerRow = sheet.createRow(0);
    headerRow.setHeightInPoints(12.75f);
    for (int i = 0; i < titles.length; i++) {
        Cell cell = headerRow.createCell(i);
        cell.setCellValue(titles[i]);
        cell.setCellStyle(styles.get("header"));
    }
    // columns for 11 weeks starting from 9-Jul
    Calendar calendar = Calendar.getInstance();
    int year = calendar.get(Calendar.YEAR);

    calendar.setTime(fmt.parse("9-Jul"));
    calendar.set(Calendar.YEAR, year);
    for (int i = 0; i < 11; i++) {
        Cell cell = headerRow.createCell(titles.length + i);
        cell.setCellValue(calendar);
        cell.setCellStyle(styles.get("header_date"));
        calendar.roll(Calendar.WEEK_OF_YEAR, true);
    }
    // freeze the first row
    sheet.createFreezePane(0, 1);

    Row row;
    Cell cell;
    int rownum = 1;
    for (int i = 0; i < data.length; i++, rownum++) {
        row = sheet.createRow(rownum);
        if (data[i] == null)
            continue;

        for (int j = 0; j < data[i].length; j++) {
            cell = row.createCell(j);
            String styleName;
            boolean isHeader = i == 0 || data[i - 1] == null;
            switch (j) {
            case 0:
                if (isHeader) {
                    styleName = "cell_b";
                    cell.setCellValue(Double.parseDouble(data[i][j]));
                } else {
                    styleName = "cell_normal";
                    cell.setCellValue(data[i][j]);
                }
                break;
            case 1:
                if (isHeader) {
                    styleName = i == 0 ? "cell_h" : "cell_bb";
                } else {
                    styleName = "cell_indented";
                }
                cell.setCellValue(data[i][j]);
                break;
            case 2:
                styleName = isHeader ? "cell_b" : "cell_normal";
                cell.setCellValue(data[i][j]);
                break;
            case 3:
                styleName = isHeader ? "cell_b_centered" : "cell_normal_centered";
                cell.setCellValue(Integer.parseInt(data[i][j]));
                break;
            case 4: {
                calendar.setTime(fmt.parse(data[i][j]));
                calendar.set(Calendar.YEAR, year);
                cell.setCellValue(calendar);
                styleName = isHeader ? "cell_b_date" : "cell_normal_date";
                break;
            }
            case 5: {
                int r = rownum + 1;
                String fmla = "IF(AND(D" + r + ",E" + r + "),E" + r + "+D" + r + ",\"\")";
                cell.setCellFormula(fmla);
                styleName = isHeader ? "cell_bg" : "cell_g";
                break;
            }
            default:
                styleName = data[i][j] != null ? "cell_blue" : "cell_normal";
            }

            cell.setCellStyle(styles.get(styleName));
        }
    }

    // group rows for each phase, row numbers are 0-based
    sheet.groupRow(4, 6);
    sheet.groupRow(9, 13);
    sheet.groupRow(16, 18);

    // set column widths, the width is measured in units of 1/256th of a
    // character width
    sheet.setColumnWidth(0, 256 * 6);
    sheet.setColumnWidth(1, 256 * 33);
    sheet.setColumnWidth(2, 256 * 20);
    sheet.setZoom(3, 4);

    // Write the output to a file
    String file = "/Users/vincentgong/Documents/workspaces/Resource/itpreneurs/report/businessplan.xls";
    if (wb instanceof XSSFWorkbook)
        file += "x";
    FileOutputStream out = new FileOutputStream(file);
    wb.write(out);
    out.close();
}

From source file:org.alfresco.repo.web.scripts.DeclarativeSpreadsheetWebScript.java

License:Open Source License

/**
 * Generates the spreadsheet, based on the properties in the header
 *  and a callback for the body.//from   ww  w.  j  a  v  a  2  s  .c  o  m
 */
public void generateSpreadsheet(Object resource, String format, WebScriptRequest req, Status status,
        Map<String, Object> model) throws IOException {
    Pattern qnameMunger = Pattern.compile("([A-Z][a-z]+)([A-Z].*)");
    String delimiterParam = req.getParameter(PARAM_REQ_DELIMITER);
    CSVStrategy reqCSVstrategy = null;
    if (delimiterParam != null && !delimiterParam.isEmpty()) {
        reqCSVstrategy = new CSVStrategy(delimiterParam.charAt(0), '"', CSVStrategy.COMMENTS_DISABLED);
    }
    // Build up the details of the header
    List<Pair<QName, Boolean>> propertyDetails = buildPropertiesForHeader(resource, format, req);
    String[] headings = new String[propertyDetails.size()];
    String[] descriptions = new String[propertyDetails.size()];
    boolean[] required = new boolean[propertyDetails.size()];
    for (int i = 0; i < headings.length; i++) {
        Pair<QName, Boolean> property = propertyDetails.get(i);
        if (property == null || property.getFirst() == null) {
            headings[i] = "";
            required[i] = false;
        } else {
            QName column = property.getFirst();
            required[i] = property.getSecond();

            // Ask the dictionary service nicely for the details
            PropertyDefinition pd = dictionaryService.getProperty(column);
            if (pd != null && pd.getTitle(dictionaryService) != null) {
                // Use the friendly titles, which may even be localised!
                headings[i] = pd.getTitle(dictionaryService);
                descriptions[i] = pd.getDescription(dictionaryService);
            } else {
                // Nothing friendly found, try to munge the raw qname into
                //  something we can show to a user...
                String raw = column.getLocalName();
                raw = raw.substring(0, 1).toUpperCase() + raw.substring(1);

                Matcher m = qnameMunger.matcher(raw);
                if (m.matches()) {
                    headings[i] = m.group(1) + " " + m.group(2);
                } else {
                    headings[i] = raw;
                }
            }
        }
    }

    // Build a list of just the properties
    List<QName> properties = new ArrayList<QName>(propertyDetails.size());
    for (Pair<QName, Boolean> p : propertyDetails) {
        QName qn = null;
        if (p != null) {
            qn = p.getFirst();
        }
        properties.add(qn);
    }

    // Output
    if ("csv".equals(format)) {
        StringWriter sw = new StringWriter();
        CSVPrinter csv = new CSVPrinter(sw, reqCSVstrategy != null ? reqCSVstrategy : getCsvStrategy());
        csv.println(headings);

        populateBody(resource, csv, properties);

        model.put(MODEL_CSV, sw.toString());
    } else {
        Workbook wb;
        if ("xlsx".equals(format)) {
            wb = new XSSFWorkbook();
            // TODO Properties
        } else {
            wb = new HSSFWorkbook();
            // TODO Properties
        }

        // Add our header row
        Sheet sheet = wb.createSheet("Export");
        Row hr = sheet.createRow(0);
        sheet.createFreezePane(0, 1);

        Font fb = wb.createFont();
        fb.setBoldweight(Font.BOLDWEIGHT_BOLD);
        Font fi = wb.createFont();
        fi.setBoldweight(Font.BOLDWEIGHT_BOLD);
        fi.setItalic(true);

        CellStyle csReq = wb.createCellStyle();
        csReq.setFont(fb);
        CellStyle csOpt = wb.createCellStyle();
        csOpt.setFont(fi);

        // Populate the header
        Drawing draw = null;
        for (int i = 0; i < headings.length; i++) {
            Cell c = hr.createCell(i);
            c.setCellValue(headings[i]);

            if (required[i]) {
                c.setCellStyle(csReq);
            } else {
                c.setCellStyle(csOpt);
            }

            if (headings[i].length() == 0) {
                sheet.setColumnWidth(i, 3 * 250);
            } else {
                sheet.setColumnWidth(i, 18 * 250);
            }

            if (descriptions[i] != null && descriptions[i].length() > 0) {
                // Add a description for it too
                if (draw == null) {
                    draw = sheet.createDrawingPatriarch();
                }
                ClientAnchor ca = wb.getCreationHelper().createClientAnchor();
                ca.setCol1(c.getColumnIndex());
                ca.setCol2(c.getColumnIndex() + 1);
                ca.setRow1(hr.getRowNum());
                ca.setRow2(hr.getRowNum() + 2);

                Comment cmt = draw.createCellComment(ca);
                cmt.setAuthor("");
                cmt.setString(wb.getCreationHelper().createRichTextString(descriptions[i]));
                cmt.setVisible(false);
                c.setCellComment(cmt);
            }
        }

        // Have the contents populated
        populateBody(resource, wb, sheet, properties);

        // Save it for the template
        ByteArrayOutputStream baos = new ByteArrayOutputStream();
        wb.write(baos);
        model.put(MODEL_EXCEL, baos.toByteArray());
    }
}

From source file:org.eclipse.emfforms.internal.spreadsheet.core.renderer.EMFFormsSpreadsheetControlRenderer.java

License:Open Source License

private static void setupSheetFormat(final Sheet sheet) {
    sheet.setDefaultColumnWidth(30);/*from w  ww.j av a  2s. c o m*/
    // do not scroll the first column (id) and the three top rows (label+info)
    sheet.createFreezePane(1, 3);
}

From source file:org.forzaframework.util.ExcelUtils.java

License:Apache License

static public void modelToExcelSheet(Workbook wb, String sheetName, List<Map<String, Object>> headers,
        List<Map<String, Object>> data, List<Map<String, Object>> footers, Integer freezePane,
        Boolean defaultFormat, Boolean createNewSheet, Integer indexSheet, Integer startInRow,
        Boolean printHeader, Boolean autoSizeColumns) {
    Sheet sheet = getSheet(wb, sheetName, createNewSheet, indexSheet);
    CellStyle headerCellStyle = getDefaultHeaderCellStyle(wb, defaultFormat);
    CellStyle titlesCellStyle = null;/*w  w  w. j a v a2s .  c  om*/
    if (defaultFormat != null && defaultFormat) {
        titlesCellStyle = wb.createCellStyle();
        //Creamos el tipo de fuente
        Font titleFont = wb.createFont();
        //            headerFont.setFontName(HSSFFont.FONT_ARIAL);
        titleFont.setBold(Boolean.TRUE);
        titleFont.setColor(Font.COLOR_NORMAL);
        titleFont.setFontHeightInPoints((short) 8);
        titlesCellStyle.setFont(titleFont);
    }

    Integer col = 0;
    Integer row = 0;
    if (startInRow != null) {
        row = startInRow;
    }
    Map<Integer, Integer> columnWidthMap = new HashMap<Integer, Integer>();
    //Indice de la fila donde empieza los encabezados de titulo de cada columna
    Integer principalHeaderIndex = headers.size() - 1;
    if (printHeader != null && printHeader) {
        //Armamos el encabezado
        for (Map<String, Object> header : headers) {
            for (Map.Entry<String, Object> entry : header.entrySet()) {
                Cell cell = getCell(sheet, row, col);
                if (defaultFormat != null && defaultFormat) {
                    if (principalHeaderIndex.equals(row)) {
                        //Colocamos el formato de la celda
                        cell.setCellStyle(headerCellStyle);
                    } else {
                        cell.setCellStyle(titlesCellStyle);
                    }
                }
                setValue(cell, entry.getValue());
                //Especificamos el ancho que tendra la columna
                if (autoSizeColumns != null && autoSizeColumns) {
                    columnWidthMap.put(col, entry.getValue().toString().length());
                }
                col++;
            }
            row++;
            col = 0;
        }
        //Ponemos la altura del encabezado
        setRowHeight(sheet, row - 1, (short) 420);
    }

    CellStyle detailCellStyle = getDefaultDetailCellStyle(wb, defaultFormat);

    Map<String, Object> principalHeader = headers.get(principalHeaderIndex);
    // datos
    for (Map<String, Object> map : data) {
        for (Map.Entry<String, Object> entry : principalHeader.entrySet()) {
            Object value = map.get(entry.getKey());
            buildCellAndCalculateColumnWidth(sheet, value, col, row, detailCellStyle, columnWidthMap,
                    autoSizeColumns);
            col++;
        }
        col = 0;
        row++;
    }
    CellStyle totalCellStyle = null;
    if (defaultFormat != null && defaultFormat) {
        //Armamos el formato los totales
        totalCellStyle = wb.createCellStyle();
        Font totalFont = wb.createFont();
        totalFont.setBold(Boolean.TRUE);
        totalFont.setColor(Font.COLOR_NORMAL);
        totalFont.setFontHeightInPoints((short) 8);
        totalCellStyle.setFont(totalFont);
    }

    if (footers != null) {
        for (Map<String, Object> footer : footers) {
            for (Map.Entry<String, Object> entry : principalHeader.entrySet()) {
                Cell cell = getCell(sheet, row, col++);
                if (totalCellStyle != null) {
                    //Colocamos el formato de la celda
                    cell.setCellStyle(totalCellStyle);
                }

                Object object = footer.get(entry.getKey());
                if (object != null) {
                    setValue(cell, object);
                } else {
                    setText(cell, "");
                }
            }
        }
    }

    if (autoSizeColumns != null && autoSizeColumns) {
        setColumnsWidth(sheet, columnWidthMap, principalHeader.size());
    }

    if (freezePane != null && freezePane > 0) {
        //Colocamos la columna estatica y las filas del encabezado estaticas
        sheet.createFreezePane(freezePane, headers.size());
    }
}

From source file:org.forzaframework.util.ExcelUtils.java

License:Apache License

static public void jsonToExcelSheet(Workbook wb, List<JSONObject> jsonHeaderList,
        List<JSONObject> jsonRecordList, String sheetName, Integer freezePane) {
    Sheet sheet = getSheet(wb, sheetName, true, 0);
    CellStyle headerCellStyle = getDefaultHeaderCellStyle(wb, true);

    Integer col = 0;/*from   w  w  w.  ja v  a 2  s .  c  om*/
    Integer row = 0;
    Map<Integer, Integer> columnWidthMap = new HashMap<Integer, Integer>();

    JSONObject principalHeader = null;
    if (jsonHeaderList != null && !jsonHeaderList.isEmpty()) {
        LinkedList<JSONObject> headerList = new LinkedList<JSONObject>(jsonHeaderList);
        principalHeader = headerList.getLast();
        headerList.removeLast();
        for (JSONObject header : headerList) {
            Collection headerValues = header.values();
            for (Object o : headerValues) {
                Cell cell = getCell(sheet, row, col);
                setValue(cell, o);
                col++;
            }
            col = 0;
            row++;
        }

    }

    List<JSONObject> datas;
    if (principalHeader == null) {
        principalHeader = jsonRecordList.get(0);
        datas = jsonRecordList.subList(1, jsonRecordList.size());
    } else {
        datas = jsonRecordList;
    }
    Collection headerValues = principalHeader.values();
    for (Object o : headerValues) {
        Cell cell = getCell(sheet, row, col);
        cell.setCellStyle(headerCellStyle);
        setValue(cell, o);
        columnWidthMap.put(col, o.toString().length());
        col++;
    }
    col = 0;
    row++;

    setRowHeight(sheet, row - 1, (short) 420);

    CellStyle detailCellStyle = getDefaultDetailCellStyle(wb, true);
    for (JSONObject jsonObject : datas) {
        for (Object value : jsonObject.values()) {
            buildCellAndCalculateColumnWidth(sheet, value, col, row, detailCellStyle, columnWidthMap, true);
            col++;
        }
        row++;
        col = 0;
    }

    setColumnsWidth(sheet, columnWidthMap, headerValues.size());
    if (freezePane != null && freezePane > 0) {
        //Colocamos la columna estatica y las filas del encabezado estaticas
        if (jsonHeaderList != null) {
            sheet.createFreezePane(freezePane, jsonHeaderList.size());
        } else {
            sheet.createFreezePane(freezePane, 1);
        }

    }

}

From source file:org.isisaddons.module.excel.dom.ExcelConverter.java

License:Apache License

<T> File toFile(final Class<T> cls, final List<T> domainObjects) throws IOException {

    final ObjectSpecification objectSpec = specificationLoader.loadSpecification(cls);

    final List<ObjectAdapter> adapters = Lists.transform(domainObjects,
            ObjectAdapter.Functions.adapterForUsing(adapterManager));

    @SuppressWarnings("deprecation")
    final List<? extends ObjectAssociation> propertyList = objectSpec.getAssociations(VISIBLE_PROPERTIES);

    final Workbook wb = new XSSFWorkbook();
    final String sheetName = cls.getSimpleName();
    final File tempFile = File.createTempFile(ExcelConverter.class.getName(), sheetName + XLSX_SUFFIX);

    final FileOutputStream fos = new FileOutputStream(tempFile);
    final Sheet sheet = wb.createSheet(sheetName);

    final ExcelConverter.RowFactory rowFactory = new RowFactory(sheet);
    final Row headerRow = rowFactory.newRow();

    // header row
    int i = 0;//from  w  ww.  ja  v a  2  s  . c om
    for (final ObjectAssociation property : propertyList) {
        final Cell cell = headerRow.createCell((short) i++);
        cell.setCellValue(property.getName());
    }

    final CellMarshaller cellMarshaller = newCellMarshaller(wb);

    // detail rows
    for (final ObjectAdapter objectAdapter : adapters) {
        final Row detailRow = rowFactory.newRow();
        i = 0;
        for (final ObjectAssociation oa : propertyList) {
            final Cell cell = detailRow.createCell((short) i++);
            final OneToOneAssociation otoa = (OneToOneAssociation) oa;
            cellMarshaller.setCellValue(objectAdapter, otoa, cell);
        }
    }

    // freeze panes
    sheet.createFreezePane(0, 1);

    wb.write(fos);
    fos.close();
    return tempFile;
}

From source file:org.isisaddons.wicket.excel.cpt.ui.ExcelFileModel.java

License:Apache License

private File createFile() throws IOException, FileNotFoundException {
    final Workbook wb = new XSSFWorkbook();
    String sheetName = model.getName();
    final File tempFile = File.createTempFile(ExcelFileModel.class.getCanonicalName(), sheetName + ".xlsx");
    final FileOutputStream fos = new FileOutputStream(tempFile);
    final Sheet sheet = wb.createSheet(sheetName);

    final ObjectSpecification typeOfSpec = model.getTypeOfSpecification();
    @SuppressWarnings("unchecked")
    final Filter<ObjectAssociation> filter = Filters.and(ObjectAssociationFilters.PROPERTIES,
            ObjectAssociationFilters// ww w  .j  a v  a 2s  . c  o m
                    .staticallyVisible(model.isParented() ? Where.PARENTED_TABLES : Where.STANDALONE_TABLES));
    final List<? extends ObjectAssociation> propertyList = typeOfSpec.getAssociations(filter);

    final ExcelFileModel.RowFactory rowFactory = new RowFactory(sheet);
    Row row = rowFactory.newRow();

    // header row
    int i = 0;
    for (ObjectAssociation property : propertyList) {
        final Cell cell = row.createCell((short) i++);
        cell.setCellValue(property.getName());
    }

    final CellStyle dateCellStyle = createDateFormatCellStyle(wb);

    // detail rows
    final List<ObjectAdapter> adapters = model.getObject();
    for (final ObjectAdapter objectAdapter : adapters) {
        row = rowFactory.newRow();
        i = 0;
        for (final ObjectAssociation property : propertyList) {
            final Cell cell = row.createCell((short) i++);
            setCellValue(objectAdapter, property, cell, dateCellStyle);
        }
    }

    // freeze panes
    sheet.createFreezePane(0, 1);

    wb.write(fos);
    fos.close();
    return tempFile;
}

From source file:org.pentaho.reporting.engine.classic.core.modules.output.table.xls.helper.ExcelPrinterBase.java

License:Open Source License

protected void configureSheetProperties(final Sheet sheet,
        final SheetPropertySource excelTableContentProducer) {
    final String pageHeaderCenter = excelTableContentProducer.getPageHeaderCenter();
    final String pageFooterCenter = excelTableContentProducer.getPageFooterCenter();
    final String pageHeaderLeft = excelTableContentProducer.getPageHeaderLeft();
    final String pageFooterLeft = excelTableContentProducer.getPageFooterLeft();
    final String pageHeaderRight = excelTableContentProducer.getPageHeaderRight();
    final String pageFooterRight = excelTableContentProducer.getPageFooterRight();

    if (isHeaderFooterValid(pageHeaderLeft, pageHeaderCenter, pageHeaderRight)) {
        if (pageHeaderLeft != null) {
            sheet.getHeader().setLeft(pageHeaderLeft);
        }//from ww w .j a  v a  2  s  .c  o  m
        if (pageHeaderCenter != null) {
            sheet.getHeader().setCenter(pageHeaderCenter);
        }
        if (pageHeaderRight != null) {
            sheet.getHeader().setRight(pageHeaderRight);
        }
    } else {
        logger.warn(
                "Page-Header exceeds the maximum length of 255 characters. No page-header will be added to the sheet.");
    }
    if (isHeaderFooterValid(pageFooterLeft, pageFooterCenter, pageFooterRight)) {
        if (pageFooterCenter != null) {
            sheet.getFooter().setCenter(pageFooterCenter);
        }
        if (pageFooterLeft != null) {
            sheet.getFooter().setLeft(pageFooterLeft);
        }
        if (pageFooterRight != null) {
            sheet.getFooter().setRight(pageFooterRight);
        }
    } else {
        logger.warn(
                "Page-Footer exceeds the maximum length of 255 characters. No page-footer will be added to the sheet.");
    }

    int sheetFreezeTop = excelTableContentProducer.getFreezeTop();
    int sheetFreezeLeft = excelTableContentProducer.getFreezeLeft();
    if (sheetFreezeTop > 0 || sheetFreezeLeft > 0) {
        sheet.createFreezePane(sheetFreezeLeft, sheetFreezeTop);
    }
}

From source file:org.phenotips.export.internal.SpreadsheetExporter.java

License:Open Source License

protected void freezeHeader(Short height, Sheet sheet) {
    sheet.createFreezePane(0, height);
}