Example usage for org.apache.poi.ss.util CellRangeAddress CellRangeAddress

Introduction

In this page you can find the example usage for org.apache.poi.ss.util CellRangeAddress CellRangeAddress.

Prototype

public CellRangeAddress(int firstRow, int lastRow, int firstCol, int lastCol) 

Source Link

Document

Creates new cell range.

Usage

From source file:net.sourceforge.fenixedu.presentationTier.Action.administrativeOffice.scholarship.utl.report.ReportStudentsUTLCandidates.java

License:Open Source License

protected void addHeaders(HSSFSheet sheet) {
    sheet.createRow(0);/*from  ww w .  j  av a2 s.c  om*/
    sheet.createRow(1);

    addHeaderCell(sheet, getHeaderInBundle("institutionCode"), 0);
    addHeaderCell(sheet, getHeaderInBundle("institutionName"), 1);
    addHeaderCell(sheet, getHeaderInBundle("candidacyNumber"), 2);
    addHeaderCell(sheet, getHeaderInBundle("studentNumberForPrint"), 3);
    addHeaderCell(sheet, getHeaderInBundle("studentName"), 4);
    addHeaderCell(sheet, getHeaderInBundle("documentTypeName"), 5);
    addHeaderCell(sheet, getHeaderInBundle("documentNumber"), 6);
    addHeaderCell(sheet, getHeaderInBundle("degreeCode"), 7);
    addHeaderCell(sheet, getHeaderInBundle("degreeName"), 8);
    addHeaderCell(sheet, getHeaderInBundle("degreeTypeName"), 9);
    addHeaderCell(sheet, getHeaderInBundle("code"), 10);
    addHeaderCell(sheet, getHeaderInBundle("countNumberOfDegreeChanges"), 11);
    addHeaderCell(sheet, getHeaderInBundle("hasMadeDegreeChange"), 12);
    addHeaderCell(sheet, getHeaderInBundle("firstEnrolmentOnCurrentExecutionYear"), 13);
    addHeaderCell(sheet, getHeaderInBundle("regime"), 14);
    addHeaderCell(sheet, getHeaderInBundle("code"), 15);

    HSSFRow row = sheet.getRow(0);
    HSSFCell cell = row.createCell(16);
    cell.setCellValue(getHeaderInBundle("ingression.year.on.cycle.studies"));
    cell.setCellStyle(headerStyle);
    sheet.addMergedRegion(new CellRangeAddress(0, 0, 16, 18));

    cell = sheet.getRow(1).createCell(16);
    cell.setCellValue(getHeaderInBundle("ingression.year.on.cycle.studies.year"));
    cell.setCellStyle(headerStyle);

    cell = sheet.getRow(1).createCell(17);
    cell.setCellValue(getHeaderInBundle("ingression.year.on.cycle.studies.count"));
    cell.setCellStyle(headerStyle);

    cell = sheet.getRow(1).createCell(18);
    cell.setCellValue(getHeaderInBundle("ingression.year.on.cycle.studies.integral.count"));
    cell.setCellStyle(headerStyle);

    addHeaderCell(sheet, getHeaderInBundle("numberOfDoneECTS"), 19);
    addHeaderCell(sheet, getHeaderInBundle("numberOfDegreeCurricularYears"), 20);
    addHeaderCell(sheet, getHeaderInBundle("curricularYearOneYearAgo"), 21);
    addHeaderCell(sheet, getHeaderInBundle("numberOfEnrolledEctsOneYearAgo"), 22);
    addHeaderCell(sheet, getHeaderInBundle("numberOfApprovedEctsOneYearAgo"), 23);
    addHeaderCell(sheet, getHeaderInBundle("curricularYearInCurrentYear"), 24);
    addHeaderCell(sheet, getHeaderInBundle("numberOfEnrolledECTS"), 25);
    addHeaderCell(sheet, getHeaderInBundle("gratuityAmount"), 26);
    addHeaderCell(sheet, getHeaderInBundle("numberOfMonthsExecutionYear"), 27);
    addHeaderCell(sheet, getHeaderInBundle("firstMonthOfPayment"), 28);
    addHeaderCell(sheet, getHeaderInBundle("ownerOfCETQualification"), 29);
    addHeaderCell(sheet, getHeaderInBundle("degreeQualificationOwner"), 30);
    addHeaderCell(sheet, getHeaderInBundle("masterQualificationOwner"), 31);
    addHeaderCell(sheet, getHeaderInBundle("phdQualificationOwner"), 32);
    addHeaderCell(sheet, getHeaderInBundle("ownerOfCollegeQualification"), 33);
    addHeaderCell(sheet, getHeaderInBundle("observations"), 34);
    addHeaderCell(sheet, getHeaderInBundle("lastEnrolledExecutionYear"), 35);
    addHeaderCell(sheet, getHeaderInBundle("nif"), 36);
    addHeaderCell(sheet, getHeaderInBundle("last.conclusion.academic.facts"), 37);
}

---

From source file:net.sourceforge.fenixedu.presentationTier.Action.administrativeOffice.scholarship.utl.report.ReportStudentsUTLCandidates.java

License:Open Source License

protected void addHeaderCell(HSSFSheet sheet, String value, int columnNumber) {
    HSSFRow row = sheet.getRow(0);//from w  w w . j av  a2s  .co m
    HSSFCell cell = row.createCell(columnNumber);

    cell.setCellValue(value);
    cell.setCellStyle(headerStyle);

    sheet.addMergedRegion(new CellRangeAddress(0, 1, columnNumber, columnNumber));
}

---

From source file:nl.meine.scouting.solparser.writer.ExcelWriter.java

License:Open Source License

private void createHeading(Sheet sheet) {
    Row r = sheet.createRow(0);//from  w  ww . j  av  a  2  s. c  om
    r.createCell(0).setCellValue("Lidnummer");
    r.createCell(1).setCellValue("Achternaam");
    r.createCell(2).setCellValue("Tussenvoegsel");
    r.createCell(3).setCellValue("Voornaam");
    r.createCell(4).setCellValue("Initialen");
    r.createCell(5).setCellValue("Geslacht");
    r.createCell(6).setCellValue("Straat");
    r.createCell(7).setCellValue("Adres");
    r.createCell(8).setCellValue("Postcode");
    r.createCell(9).setCellValue("Plaats");
    r.createCell(10).setCellValue("Telefoonnummer");
    r.createCell(11).setCellValue("Mobiel");
    r.createCell(12).setCellValue("Mail lid");
    r.createCell(13).setCellValue("Naam ouder/verzorger 1");
    r.createCell(14).setCellValue("Mail ouder/verzorger 1");
    r.createCell(15).setCellValue("Telefoonnummer ouder/verzorger 1");
    r.createCell(16).setCellValue("Naam ouder/verzorger 2");
    r.createCell(17).setCellValue("Mail ouder/verzorger 2");
    r.createCell(18).setCellValue("Telefoonnummer ouder/verzorger 2");
    r.createCell(19).setCellValue("Speltak");
    r.createCell(20).setCellValue("Functie");
    r.createCell(21).setCellValue("Geboortedatum");
    r.createCell(22).setCellValue("Functie startdatum");
    r.createCell(23).setCellValue("Overige informatie");

    Iterator<Cell> it = r.cellIterator();
    while (it.hasNext()) {
        Cell c = it.next();
        c.setCellStyle(headingStyle);
    }

    sheet.setAutoFilter(new CellRangeAddress(0, 0, 0, 23));
}

---

From source file:nl.meine.scouting.solparser.writer.ExcelWriter.java

License:Open Source License

private void processQuitters() {
    if (hasPrevious()) {
        Sheet sheet = workbook.getSheet(SorterFactory.GROUP_NAME_ALL);
        List<Row> quitters = new ArrayList<Row>();
        FileInputStream previousStream = null;
        try {//from   ww w .jav a  2 s  .  co m
            previousStream = new FileInputStream(previous);
            //Get the workbook instance for XLS file
            HSSFWorkbook prevWorkbook = new HSSFWorkbook(previousStream);
            Sheet prevSheet = prevWorkbook.getSheet(SorterFactory.GROUP_NAME_ALL);
            if (prevSheet == null) {
                return;
            }
            // Check of er mensen vertrokken zijn
            for (Row row : prevSheet) {
                if (row.getRowNum() > 0) {
                    String lidnummer = row.getCell(NUM_LIDNUMMER_CELL).getStringCellValue();
                    Row currentRow = getLidFromSheet(lidnummer, sheet);
                    if (currentRow == null) {
                        quitters.add(row);
                    }
                }
            }
        } catch (IOException ex) {
            System.out.println("Error Reading the previous file: " + ex.getLocalizedMessage());
            return;
        } finally {
            try {
                if (previousStream != null) {
                    previousStream.close();
                }
            } catch (IOException ex) {
                System.out.println("Problems closing file: " + ex.getLocalizedMessage());
            }
        }
        if (quitters.isEmpty()) {
            return;
        }
        Sheet removedSheet = workbook.createSheet(SHEET_REMOVED_PERSONS);

        // Create header
        Row header = removedSheet.createRow(0);
        //Lidnummer   Achternaam   Tussenvoegsel   Voornaam   Geslacht    Telefoonnummer   Mobiel  Geboortedatum

        Cell lidnummer = header.createCell(0);
        lidnummer.setCellValue("Lidnummer");
        lidnummer.setCellStyle(headingStyle);
        Cell achternaam = header.createCell(1);
        achternaam.setCellValue("Achternaam");
        achternaam.setCellStyle(headingStyle);
        Cell tussenvoegsel = header.createCell(2);
        tussenvoegsel.setCellValue("Tussenvoegsel");
        tussenvoegsel.setCellStyle(headingStyle);
        Cell voornaam = header.createCell(3);
        voornaam.setCellValue("Voornaam");
        voornaam.setCellStyle(headingStyle);
        Cell geslacht = header.createCell(4);
        geslacht.setCellValue("Geslacht");
        geslacht.setCellStyle(headingStyle);
        Cell telefoonnummer = header.createCell(5);
        telefoonnummer.setCellValue("Telefoonnummer");
        telefoonnummer.setCellStyle(headingStyle);
        Cell geboortedatum = header.createCell(6);
        geboortedatum.setCellValue("Geboortedatum");
        geboortedatum.setCellStyle(headingStyle);

        // Iterate over quitters
        int index = 1;
        for (Row quitter : quitters) {
            Row r = removedSheet.createRow(index);
            r.createCell(0).setCellValue(quitter.getCell(0).getStringCellValue());
            r.createCell(1).setCellValue(quitter.getCell(1).getStringCellValue());
            r.createCell(2).setCellValue(quitter.getCell(2).getStringCellValue());
            r.createCell(3).setCellValue(quitter.getCell(3).getStringCellValue());
            r.createCell(4).setCellValue(quitter.getCell(5).getStringCellValue());
            r.createCell(5).setCellValue(quitter.getCell(10).getStringCellValue());
            r.createCell(6).setCellValue(quitter.getCell(21).getStringCellValue());
            index++;
        }

        removedSheet.setAutoFilter(new CellRangeAddress(0, 0, 0, 6));
        int numcells = removedSheet.getRow(0).getLastCellNum();
        for (int i = 0; i < numcells; i++) {
            removedSheet.autoSizeColumn(i);
        }
    }
}

---

From source file:opn.greenwebs.FXMLDocumentController.java

private File createStockFile(List<ItemDB> list) {
    int nSize = list.size();
    XSSFWorkbook wbs = createStockWorkbook();

    XSSFSheet sheetStock = wbs.getSheet("Digital Version");
    List<XSSFTable> lTables = sheetStock.getTables();
    // Create a FormulaEvaluator to use
    FormulaEvaluator mainWorkbookEvaluator = sheetStock.getWorkbook().getCreationHelper()
            .createFormulaEvaluator();//from  w  ww.  ja v a  2  s  .co  m
    File fStock = createFilename("STK", "");
    Instant instant = Instant.from(dteOrderDate.getValue().atStartOfDay(ZoneId.systemDefault()));
    Row rowed = sheetStock.getRow(6);
    Cell celled = rowed.getCell(10);
    CellStyle cellStyle = celled.getCellStyle();
    XSSFFont font = sheetStock.getWorkbook().createFont();
    font.setFontHeight(14);
    cellStyle.setFont(font);
    celled.setCellValue(Date.from(instant));
    celled.setCellStyle(cellStyle);
    rowed = sheetStock.getRow(10);
    celled = rowed.getCell(2);
    celled.setCellValue(fStock.getName().substring(0, fStock.getName().length() - 5));
    if (!lTables.isEmpty()) {
        XSSFTable table = lTables.get(0);
        table.getCTTable()
                .setRef(new CellRangeAddress(table.getStartCellReference().getRow(),
                        table.getEndCellReference().getRow() + nSize, table.getStartCellReference().getCol(),
                        table.getEndCellReference().getCol()).formatAsString());
        XSSFRow row;
        XSSFCell cell;
        font = sheetStock.getWorkbook().createFont();
        font.setFontHeight(14);
        int nCellRef = table.getStartCellReference().getRow() + 1;
        for (ItemDB itemdb : list) {
            row = sheetStock.createRow(nCellRef++);
            cell = row.createCell(0);
            cellStyle = cell.getCellStyle();
            cell.setCellValue(itemdb.getDblQty());
            cellStyle.setFont(font);
            cell.setCellStyle(cellStyle);
            cell = row.createCell(1);
            cell.setCellValue(itemdb.getStrMfr());
            cell.setCellStyle(cellStyle);
            cell = row.createCell(2);
            cell.setCellValue(itemdb.getStrSKU());
            cell.setCellStyle(cellStyle);
            cell = row.createCell(3);
            cell.setCellValue(itemdb.getStrDescrip());
            cell.setCellStyle(cellStyle);
            cell = row.createCell(4);
            cell.setCellValue(itemdb.getStrSupplier());
            cell.setCellStyle(cellStyle);
            cell = row.createCell(5);
            cell.setCellValue(itemdb.getStrSupPart());
            cell.setCellType(HSSFCell.CELL_TYPE_STRING);
            //cell.setCellStyle(cellStyle);
            cell = row.createCell(6);
            cell.setCellValue(itemdb.getDblSalePrice());
            cell.setCellStyle(cellStyle);
            cell = row.createCell(7);
            cell.setCellValue(itemdb.getDblCost());
            cell.setCellStyle(cellStyle);
            /*cell = row.createCell(8);
            cell.setCellType(HSSFCell.CELL_TYPE_FORMULA);
            cell.setCellFormula("IF(A" + nCellRef + ">0,IF(G" + nCellRef + ">0,IF(H" + nCellRef + ">0,A" + nCellRef + "*G" + nCellRef + "-A" + nCellRef + "*H" + nCellRef + ",\"\"),\"\"),\"\")");
            mainWorkbookEvaluator.evaluateFormulaCell(cell);
            cell.setCellStyle(cellStyle);
            cell = row.createCell(9);
            cell.setCellFormula("IF(I" + nCellRef + "<>\"\",I" + nCellRef + "/(A" + nCellRef + "*G" + nCellRef + "),\"\")");
            mainWorkbookEvaluator.evaluateFormulaCell(cell);
            CellStyle style = wbs.createCellStyle();
            style.setDataFormat(wbs.createDataFormat().getFormat("0%"));
            cell.setCellStyle(style);*/
            mainWorkbookEvaluator.evaluateAll();
        }

        try {
            try (FileOutputStream fileOut = new FileOutputStream(fStock)) {
                wbs.write(fileOut);
                return fStock;
            }
        } catch (FileNotFoundException ex) {
            logger.info(ex.getLocalizedMessage());
        } catch (IOException ex) {
            logger.info(ex.getLocalizedMessage());
        }
    }
    return null;
}

---

From source file:org.activityinfo.server.endpoint.export.SiteExporter.java

License:Open Source License

private void createHeaders(ActivityFormDTO activity, HSSFSheet sheet) {

    // / The HEADER rows

    Row headerRow1 = sheet.createRow(0);
    Row headerRow2 = sheet.createRow(1);
    headerRow2.setHeightInPoints(HEADER_CELL_HEIGHT);

    // Create a title cell with the complete database + activity name
    Cell titleCell = headerRow1.createCell(0);
    titleCell.setCellValue(/*  w ww. j  a v  a  2 s.  c o  m*/
            creationHelper.createRichTextString(activity.getDatabaseName() + " - " + activity.getName()));
    titleCell.setCellStyle(titleStyle);

    int column = 0;

    createHeaderCell(headerRow2, column++, "SiteId", CellStyle.ALIGN_LEFT);
    createHeaderCell(headerRow2, column++, "DateCreated", CellStyle.ALIGN_RIGHT);

    sheet.setColumnHidden(0, true);
    sheet.setColumnHidden(1, true);

    createHeaderCell(headerRow2, column++, "Date1", CellStyle.ALIGN_RIGHT);
    createHeaderCell(headerRow2, column++, "Date2", CellStyle.ALIGN_RIGHT);

    createHeaderCell(headerRow2, column, "Partner");
    sheet.setColumnWidth(column, characters(PARTNER_COLUMN_WIDTH));
    column++;

    createHeaderCell(headerRow2, column, activity.getLocationType().getName());
    sheet.setColumnWidth(column, characters(LOCATION_COLUMN_WIDTH));
    column++;

    createHeaderCell(headerRow2, column++, "Axe");

    indicators = new ArrayList<Integer>(activity.getIndicators().size());
    if (activity.getReportingFrequency() == ActivityFormDTO.REPORT_ONCE) {
        for (IndicatorGroup group : activity.groupIndicators()) {
            if (group.getName() != null) {
                // create a merged cell on the top row spanning all members
                // of the group
                createHeaderCell(headerRow1, column, group.getName());
                sheet.addMergedRegion(
                        new CellRangeAddress(0, 0, column, column + group.getIndicators().size() - 1));
            }
            for (IndicatorDTO indicator : group.getIndicators()) {
                indicators.add(indicator.getId());
                createHeaderCell(headerRow2, column, indicator.getName(), indicatorHeaderStyle);
                sheet.setColumnWidth(column, characters(INDICATOR_COLUMN_WIDTH));
                column++;
            }
        }
    }
    attributes = new ArrayList<>();
    for (AttributeGroupDTO group : activity.getAttributeGroups()) {
        if (group.getAttributes().size() != 0) {
            createHeaderCell(headerRow1, column, group.getName(), CellStyle.ALIGN_CENTER);
            sheet.addMergedRegion(
                    new CellRangeAddress(0, 0, column, column + group.getAttributes().size() - 1));

            for (AttributeDTO attrib : group.getAttributes()) {
                attributes.add(attrib.getId());
                createHeaderCell(headerRow2, column, attrib.getName(), attribHeaderStyle);
                sheet.setColumnWidth(column, characters(ATTRIBUTE_COLUMN_WIDTH));
                column++;
            }
        }
    }

    levels = new ArrayList<>();

    for (AdminLevelDTO level : activity.getAdminLevels()) {
        createHeaderCell(headerRow2, column++, "Code " + level.getName());
        createHeaderCell(headerRow2, column++, level.getName());
        levels.add(level.getId());
    }

    int latColumn = column++;
    int lngColumn = column++;

    createHeaderCell(headerRow2, latColumn, I18N.CONSTANTS.longitude(), CellStyle.ALIGN_RIGHT);
    createHeaderCell(headerRow2, lngColumn, I18N.CONSTANTS.latitude(), CellStyle.ALIGN_RIGHT);
    sheet.setColumnWidth(lngColumn, characters(COORD_COLUMN_WIDTH));
    sheet.setColumnWidth(latColumn, characters(COORD_COLUMN_WIDTH));

    createHeaderCell(headerRow2, column++, I18N.CONSTANTS.comments());

}

---

From source file:org.activityinfo.server.report.renderer.excel.BaseExcelTableRenderer.java

License:Open Source License

protected void generateColumnHeaders(int firstCol, ColumnT root) {

    /*//from w  ww  .j a  v a2  s  .c  om
     * Now try building the column headers
     */

    int depth = root.getDepth();
    colIndexMap = new HashMap<ColumnT, Integer>();

    int startLevel = depth == 0 ? 0 : 1;

    for (int level = startLevel; level <= depth; ++level) {

        Row row = sheet.createRow(rowIndex);

        int colIndex = firstCol;

        List<ColumnT> cols = root.getDescendantsAtDepth(level, true);

        for (ColumnT col : cols) {

            if (col == null) {
                colIndex++;
            } else {
                Cell cell = row.createCell(colIndex);
                cell.setCellValue(factory.createRichTextString(col.getLabel()));
                cell.setCellStyle(col.isLeaf() ? leafColHeaderStyle : colHeaderStyle);

                int span = col.getLeaves().size();

                if (span > 1) {
                    sheet.addMergedRegion(
                            new CellRangeAddress(rowIndex, rowIndex, colIndex, colIndex + span - 1));
                }
                if (col.isLeaf()) {
                    colIndexMap.put(col, colIndex);
                }

                colIndex += span;
            }
        }

        rowIndex++;
    }

}

---

From source file:org.agmip.ui.afsirs.util.SummaryReportExcelFormat.java

public SummaryReportExcelFormat(String fName) {
    this.fileName = fName;

    if (fName == null) {
        //throw new Exception ("File Name cant be null");
    }//from w  ww .  ja  v a  2  s.  co m

    //Create blank workbook
    workbook = new XSSFWorkbook();
    //Create a blank sheet
    spreadsheet = workbook.createSheet("Summary Info");

    spreadsheet.addMergedRegion(new CellRangeAddress(rowNum, rowNum, 1, 14));

    //This data needs to be written (Object[])
    summaryInfo = new TreeMap<Integer, Object[]>();

    row = spreadsheet.createRow(rowNum);

    try {
        //Write the workbook in file system
        out = new FileOutputStream(new File(fileName));
    } catch (FileNotFoundException e) {

    }

}

---

From source file:org.agmip.ui.afsirs.util.SummaryReportExcelFormat.java

public void mergeCells() {
    spreadsheet.addMergedRegion(new CellRangeAddress(rowNum, rowNum, colNum, 14));
    spreadsheet.autoSizeColumn(colNum, true);
}

---

From source file:org.alanwilliamson.openbd.plugin.spreadsheet.functions.SpreadsheetMergeCells.java

License:Open Source License

public cfData execute(cfSession _session, List<cfData> parameters) throws cfmRunTimeException {
    cfSpreadSheetData spreadsheet = null;

    /*/*from   w w  w .ja va 2 s  . co m*/
     * Collect up the parameters
     */
    spreadsheet = (cfSpreadSheetData) parameters.get(4);
    int startRow = parameters.get(3).getInt() - 1;
    int endRow = parameters.get(2).getInt() - 1;
    int startCol = parameters.get(1).getInt() - 1;
    int endCol = parameters.get(0).getInt() - 1;

    if (startRow < 0)
        throwException(_session, "startRow must be 1 or greater (" + startRow + ")");
    if (endRow < 0)
        throwException(_session, "endRow must be 1 or greater (" + endRow + ")");
    if (startCol < 0)
        throwException(_session, "column must be 1 or greater (" + startCol + ")");
    if (endCol < 0)
        throwException(_session, "row must be 1 or greater (" + endCol + ")");

    if (endRow < startRow)
        throwException(_session, "startrow must be smaller than endrow");
    if (endCol < startCol)
        throwException(_session, "startcolumn must be smaller than endcolumn");

    //Perform the merge operation
    spreadsheet.getActiveSheet().addMergedRegion(new CellRangeAddress(startRow, endRow, startCol, endCol));

    return cfBooleanData.TRUE;
}

---

• «
• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14
• 15
• 16
• 17
• 18
• 19
• 20
• 21
• 22
• 23
• 24
• 25
• 26
• 27
• 28
• 29
• 30
• 31
• 32
• 33
• 34
• 35
• 36
• 37
• 38
• 39
• 40
• 41
• 42
• 43
• 44
• 45
• 46
• 47
• 48
• 49
• 50
• »