Example usage for org.apache.poi.ss.usermodel Name setRefersToFormula

List of usage examples for org.apache.poi.ss.usermodel Name setRefersToFormula

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. Name
  6. setRefersToFormula

Introduction

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

Prototype

void setRefersToFormula(String formulaText);

Source Link

Document

Sets the formula that the name is defined to refer to.

Usage

From source file:cn.org.vbn.util.LinkedDropDownLists.java

License:Apache License

/**
 * Called to populate the named areas/regions. The contents of the cells on
 * row one will be used to populate the first drop down list. The contents of
 * the cells on rows two, three and four will be used to populate the second
 * drop down list, just which row will be determined by the choice the user
 * makes in the first drop down list./*from   www .j a v  a2  s  . c o m*/
 *
 * In all cases, the approach is to create a row, create and populate cells
 * with data and then specify a name that identifies those cells. With the
 * exception of the first range, the names that are chosen for each range
 * of cells are quite important. In short, each of the options the user
 * could select in the first drop down list is used as the name for another
 * range of cells. Thus, in this example, the user can select either
 * 'Animal', 'Vegetable' or 'Mineral' in the first drop down and so the
 * sheet contains ranges named 'ANIMAL', 'VEGETABLE' and 'MINERAL'.
 *
 * @param dataSheet An instance of a class that implements the Sheet Sheet
 *        interface (HSSFSheet or XSSFSheet).
 */
private static final void buildDataSheet(Sheet dataSheet) {
    Row row = null;
    Cell cell = null;
    Name name = null;

    // The first row will hold the data for the first validation.
    row = dataSheet.createRow(10);
    cell = row.createCell(0);
    cell.setCellValue("Animal");
    cell = row.createCell(1);
    cell.setCellValue("Vegetable");
    cell = row.createCell(2);
    cell.setCellValue("Mineral");
    name = dataSheet.getWorkbook().createName();
    name.setRefersToFormula("$A$11:$C$11");
    name.setNameName("CHOICES");

    // The next three rows will hold the data that will be used to
    // populate the second, or linked, drop down list.
    row = dataSheet.createRow(11);
    cell = row.createCell(0);
    cell.setCellValue("Lion");
    cell = row.createCell(1);
    cell.setCellValue("Tiger");
    cell = row.createCell(2);
    cell.setCellValue("Leopard");
    cell = row.createCell(3);
    cell.setCellValue("Elephant");
    cell = row.createCell(4);
    cell.setCellValue("Eagle");
    cell = row.createCell(5);
    cell.setCellValue("Horse");
    cell = row.createCell(6);
    cell.setCellValue("Zebra");
    name = dataSheet.getWorkbook().createName();
    name.setRefersToFormula("$A$12:$G$12");
    name.setNameName("ANIMAL");

    row = dataSheet.createRow(12);
    cell = row.createCell(0);
    cell.setCellValue("Cabbage");
    cell = row.createCell(1);
    cell.setCellValue("Cauliflower");
    cell = row.createCell(2);
    cell.setCellValue("Potato");
    cell = row.createCell(3);
    cell.setCellValue("Onion");
    cell = row.createCell(4);
    cell.setCellValue("Beetroot");
    cell = row.createCell(5);
    cell.setCellValue("Asparagus");
    cell = row.createCell(6);
    cell.setCellValue("Spinach");
    cell = row.createCell(7);
    cell.setCellValue("Chard");
    name = dataSheet.getWorkbook().createName();
    name.setRefersToFormula("$A$13:$H$13");
    name.setNameName("VEGETABLE");

    row = dataSheet.createRow(13);
    cell = row.createCell(0);
    cell.setCellValue("Bauxite");
    cell = row.createCell(1);
    cell.setCellValue("Quartz");
    cell = row.createCell(2);
    cell.setCellValue("Feldspar");
    cell = row.createCell(3);
    cell.setCellValue("Shist");
    cell = row.createCell(4);
    cell.setCellValue("Shale");
    cell = row.createCell(5);
    cell.setCellValue("Mica");
    name = dataSheet.getWorkbook().createName();
    name.setRefersToFormula("$A$14:$F$14");
    name.setNameName("MINERAL");
}

From source file:com.b510.excel.client.LoanCalculator.java

License:Apache License

public static void createNames(Workbook wb) {
    Name name;

    name = wb.createName();//from   w  w w. ja  v a 2 s. c  om
    name.setNameName("Interest_Rate");
    name.setRefersToFormula("'Loan Calculator'!$E$5");

    name = wb.createName();
    name.setNameName("Loan_Amount");
    name.setRefersToFormula("'Loan Calculator'!$E$4");

    name = wb.createName();
    name.setNameName("Loan_Start");
    name.setRefersToFormula("'Loan Calculator'!$E$7");

    name = wb.createName();
    name.setNameName("Loan_Years");
    name.setRefersToFormula("'Loan Calculator'!$E$6");

    name = wb.createName();
    name.setNameName("Number_of_Payments");
    name.setRefersToFormula("'Loan Calculator'!$E$10");

    name = wb.createName();
    name.setNameName("Monthly_Payment");
    name.setRefersToFormula("-PMT(Interest_Rate/12,Number_of_Payments,Loan_Amount)");

    name = wb.createName();
    name.setNameName("Total_Cost");
    name.setRefersToFormula("'Loan Calculator'!$E$12");

    name = wb.createName();
    name.setNameName("Total_Interest");
    name.setRefersToFormula("'Loan Calculator'!$E$11");

    name = wb.createName();
    name.setNameName("Values_Entered");
    name.setRefersToFormula("IF(Loan_Amount*Interest_Rate*Loan_Years*Loan_Start>0,1,0)");
}

From source file:com.dataart.spreadsheetanalytics.engine.DataModelConverters.java

License:Apache License

/** Convertes plain {@link IDataModel} to new {@link XSSFWorkbook} with formatting provided. */
static Workbook toWorkbook(final IDataModel dataModel, final Workbook formatting) {
    Workbook result = formatting == null ? ConverterUtils.newWorkbook()
            : ConverterUtils.clearContent(formatting);

    Sheet wbSheet = result.getSheet(dataModel.getName());
    if (wbSheet == null) {
        wbSheet = result.createSheet(dataModel.getName());
    }//from   w  w  w  .  ja  va 2s .  c o m

    dataModel.getNamedAddresses().forEach((k, v) -> {
        Name name = result.createName();
        name.setNameName(k);

        name.setRefersToFormula(createPoiNameRef(v.address(), dataModel.getName()));
    });

    dataModel.getNamedValues().forEach((k, v) -> {
        Name name = result.createName();
        name.setNameName(k);

        String refString = v.get() == null ? "" : v.get().toString();
        if (refString.startsWith(FORMULA_PREFIX)) {
            refString = refString.substring(1);
        }

        name.setRefersToFormula(refString);
    });

    for (int rowIdx = dataModel.getFirstRowIndex(); rowIdx <= dataModel.getLastRowIndex(); rowIdx++) {
        IDmRow dmRow = dataModel.getRow(rowIdx);
        if (dmRow == null) {
            continue;
        }
        Row wbRow = wbSheet.getRow(rowIdx);
        if (wbRow == null) {
            wbRow = wbSheet.createRow(rowIdx);
        }

        for (int cellIdx = dmRow.getFirstColumnIndex(); cellIdx <= dmRow.getLastColumnIndex(); cellIdx++) {
            IDmCell dmCell = dmRow.getCell(cellIdx);
            if (dmCell == null) {
                continue;
            }

            Cell wbCell = wbRow.getCell(cellIdx);
            if (wbCell == null) {
                wbCell = wbRow.createCell(cellIdx);
            }

            ConverterUtils.populateCellValue(wbCell, dmCell.getContent());
        }
    }

    return result;
}

From source file:com.dituiba.excel.DefaultValidateAdapter.java

License:Apache License

/**
 * ??/*from  ww  w  .j av a 2s.c om*/
 * @param config
 * @param sheet
 * @param columnIndex
 * @param valueSet
 */
protected void createDicCodeSheet(DicValidateConfig config, Sheet sheet, int columnIndex,
        Set<String> valueSet) {
    Workbook workbook = sheet.getWorkbook();
    Sheet codeSheet = workbook.getSheet(DICCODE_SHEET_NAME);
    if (codeSheet == null) {
        log.debug("?Sheet?Sheet");
        codeSheet = workbook.createSheet(DICCODE_SHEET_NAME);
    }
    int codeIndex = config.columnName() - 'A';
    log.debug("codeIndex{}", codeIndex);
    if (codeSheet.getRow(0) == null || codeSheet.getRow(0).getCell(codeIndex) == null) {
        log.debug("????");
        int i = 0;
        for (String dic : valueSet) {
            Row row = codeSheet.getRow(i);
            if (row == null)
                row = codeSheet.createRow(i);
            Cell cell = row.createCell(codeIndex);
            cell.setCellValue(dic);
            i++;
        }
    } else {
        log.debug("????");
    }
    Name name = workbook.getName(config.columnName() + "");
    if (name == null || name.isDeleted()) {
        log.debug("?Name?Name");
        name = workbook.createName();
        name.setNameName(config.columnName() + "");
    }
    name.setRefersToFormula(DICCODE_SHEET_NAME + "!$" + config.columnName() + "$1:$" + config.columnName() + "$"
            + valueSet.size());
    DVConstraint constraint = DVConstraint.createFormulaListConstraint(name.getNameName());
    CellRangeAddressList addressList = new CellRangeAddressList(BaseExcelService.START_ROW, Short.MAX_VALUE,
            columnIndex, columnIndex);
    HSSFDataValidation validation = new HSSFDataValidation(addressList, constraint);
    workbook.setSheetHidden(workbook.getSheetIndex(DICCODE_SHEET_NAME), Workbook.SHEET_STATE_VERY_HIDDEN);
    setValidationTip(validation, config);
    sheet.addValidationData(validation);
    log.debug("??");
}

From source file:com.globalsight.everest.webapp.pagehandler.administration.reports.generator.CommentsAnalysisReportGenerator.java

License:Apache License

/**
 * Create workbook name areas for category failure drop down list, it is
 * from "AA8" to "AAn".//  w ww .j  a  v a 2  s  . c o m
 * <P>
 * Only write the data of drop down list into the first sheet as it can be
 * referenced from all sheets.
 * </P>
 * <P>
 * The formula is like
 * "[sheetName]!$AA$[startRow]:$AA$[endRow]",i.e."TER!$AA$8:$AA$32".
 * </P>
 */
private void createCategoryFailureNameArea(Workbook p_workbook) {
    try {
        Sheet firstSheet = getSheet(p_workbook, 0);
        List<String> categories = getFailureCategoriesList();
        // Set the categories in "AA" column, starts with row 8.
        int col = 26;
        for (int i = 0; i < categories.size(); i++) {
            Row row = getRow(firstSheet, SEGMENT_START_ROW + i);
            Cell cell = getCell(row, col);
            cell.setCellValue(categories.get(i));
        }

        String formula = firstSheet.getSheetName() + "!$AA$" + (SEGMENT_START_ROW + 1) + ":$AA$"
                + (SEGMENT_START_ROW + categories.size());
        Name name = p_workbook.createName();
        name.setRefersToFormula(formula);
        name.setNameName(CATEGORY_FAILURE_DROP_DOWN_LIST);

        // Hide "AA" column
        firstSheet.setColumnHidden(26, true);
    } catch (Exception e) {
        logger.error("Error when create hidden area for category failures.", e);
    }
}

From source file:com.globalsight.everest.webapp.pagehandler.administration.reports.generator.PostReviewQAReportGenerator.java

License:Apache License

/**
 * Create workbook name areas for category failure drop down list, it is
 * from "AA8" to "AAn"./*from   ww w .ja v a2s.  c o m*/
 * <P>
 * Only write the data of drop down list into the first sheet as it can be
 * referenced from all sheets.
 * </P>
 * <P>
 * The formula is like
 * "[sheetName]!$AA$[startRow]:$AA$[endRow]",i.e."TER!$AA$8:$AA$32".
 * </P>
 */
private void createCategoryFailureNameArea(Workbook p_workbook) {
    try {
        // Ensure the name area is written only one time,otherwise it has
        // problem when open generated excel file.
        if (p_workbook.getNumberOfSheets() == 1) {
            Sheet firstSheet = getSheet(p_workbook, 0);
            List<String> categories = getFailureCategoriesList();
            // Set the categories in "AA" column, starts with row 8.
            int col = 26;
            for (int i = 0; i < categories.size(); i++) {
                Row row = getRow(firstSheet, SEGMENT_START_ROW + i);
                Cell cell = getCell(row, col);
                cell.setCellValue(categories.get(i));
            }

            String formula = firstSheet.getSheetName() + "!$AA$" + (SEGMENT_START_ROW + 1) + ":$AA$"
                    + (SEGMENT_START_ROW + categories.size());
            Name name = p_workbook.createName();
            name.setRefersToFormula(formula);
            name.setNameName(CATEGORY_FAILURE_DROP_DOWN_LIST);

            // Hide "AA" column
            firstSheet.setColumnHidden(26, true);
        }
    } catch (Exception e) {
        logger.error("Error when create hidden area for category failures.", e);
    }
}

From source file:com.globalsight.everest.webapp.pagehandler.administration.reports.generator.PostReviewQAReportGenerator.java

License:Apache License

private void createQualityAssessmentNameArea(Workbook p_workbook) {
    try {/*from   ww  w.  j  a  va2 s.  c  o  m*/
        // Ensure the name area is written only one time,otherwise it has
        // problem when open generated excel file.
        if (p_workbook.getNumberOfSheets() == 1) {
            Sheet firstSheet = getSheet(p_workbook, 0);
            List<String> qualityCategories = getQualityAssessmentList();
            // Set the categories in "AA" column, starts with row 8.
            int col = 27;
            for (int i = 0; i < qualityCategories.size(); i++) {
                Row row = getRow(firstSheet, SEGMENT_START_ROW + i);
                Cell cell = getCell(row, col);
                cell.setCellValue(qualityCategories.get(i));
            }

            String formula = firstSheet.getSheetName() + "!$AB$" + (SEGMENT_START_ROW + 1) + ":$AB$"
                    + (SEGMENT_START_ROW + qualityCategories.size());
            Name name = p_workbook.createName();
            name.setRefersToFormula(formula);
            name.setNameName(QUALITY_ASSESSMENT_LIST);

            // Hide "AB" column
            firstSheet.setColumnHidden(27, true);
        }
    } catch (Exception e) {
        logger.error("Error when create hidden area for category failures.", e);
    }
}

From source file:com.globalsight.everest.webapp.pagehandler.administration.reports.generator.PostReviewQAReportGenerator.java

License:Apache License

private void createMarketSuitabilityNameArea(Workbook p_workbook) {
    try {/*from  w  w w.ja va  2s. c om*/
        // Ensure the name area is written only one time,otherwise it has
        // problem when open generated excel file.
        if (p_workbook.getNumberOfSheets() == 1) {
            Sheet firstSheet = getSheet(p_workbook, 0);
            List<String> marketCategories = getMarketSuitabilityList();
            // Set the categories in "AC" column, starts with row 11.
            int col = 28;
            for (int i = 0; i < marketCategories.size(); i++) {
                Row row = getRow(firstSheet, SEGMENT_START_ROW + i);
                Cell cell = getCell(row, col);
                cell.setCellValue(marketCategories.get(i));
            }

            String formula = firstSheet.getSheetName() + "!$AC$" + (SEGMENT_START_ROW + 1) + ":$AC$"
                    + (SEGMENT_START_ROW + marketCategories.size());
            Name name = p_workbook.createName();
            name.setRefersToFormula(formula);
            name.setNameName(MARKET_SUITABILITY_LIST);

            // Hide "AC" column
            firstSheet.setColumnHidden(28, true);
        }
    } catch (Exception e) {
        logger.error("Error when create hidden area for category failures.", e);
    }
}

From source file:com.globalsight.everest.webapp.pagehandler.administration.reports.ReviewerLisaQAXlsReportHelper.java

License:Apache License

/**
 * Create workbook name areas for category failure drop down list, it is
 * from "AA8" to "AAn".//from   w  w  w.  j  a  va 2  s  .  c o  m
 * <P>
 * Only write the data of drop down list into the first sheet as it can be
 * referenced from all sheets.
 * </P>
 * <P>
 * The formula is like
 * "[sheetName]!$AA$[startRow]:$AA$[endRow]",i.e."TER!$AA$8:$AA$32".
 * </P>
 */
private void createCategoryFailureNameArea(Workbook p_workbook) {
    Sheet firstSheet = getSheet(p_workbook, 0);
    List<String> categories = getFailureCategoriesList();
    // Set the categories in "AA" column, starts with row 8.
    int col = 26;
    for (int i = 0; i < categories.size(); i++) {
        Row row = getRow(firstSheet, SEGMENT_START_ROW + i);
        Cell cell = getCell(row, col);
        cell.setCellValue(categories.get(i));
    }

    String formula = firstSheet.getSheetName() + "!$AA$" + (SEGMENT_START_ROW + 1) + ":$AA$"
            + (SEGMENT_START_ROW + categories.size());
    Name name = p_workbook.createName();
    name.setRefersToFormula(formula);
    name.setNameName(CATEGORY_FAILURE_DROP_DOWN_LIST);

    // Hide "AA" column
    firstSheet.setColumnHidden(26, true);
}

From source file:com.miraisolutions.xlconnect.Workbook.java

License:Open Source License

public void createName(String name, String formula, boolean overwrite) {
    if (existsName(name)) {
        if (overwrite) {
            // Name already exists but we overwrite --> remove
            removeName(name);/*from ww w.j  av a  2  s .c o  m*/
        } else {
            // Name already exists but we don't want to overwrite --> error
            throw new IllegalArgumentException("Specified name '" + name + "' already exists!");
        }
    }

    Name cname = workbook.createName();
    try {
        cname.setNameName(name);
        cname.setRefersToFormula(formula);
    } catch (Exception e) {
        // --> Clean up (= remove) name
        // Need to set dummy name in order to be able to remove it ...
        String dummyNameName = "XLConnectDummyName";
        cname.setNameName(dummyNameName);
        removeName(dummyNameName);
        throw new IllegalArgumentException(e);
    }
}