Example usage for org.apache.poi.ss.usermodel Workbook createName

Introduction

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

Prototype

Name createName();

Source Link

Document

Creates a new (uninitialised) defined name in this workbook

Usage

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

License:Apache License

public static void createNames(Workbook wb) {
    Name name;// ww w.j a  va2 s.  c  o  m

    name = wb.createName();
    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 . j  av a 2  s . 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

/**
 * ??/* www .  j  a  v a2s  .  c o m*/
 * @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  w w  .j  a v a2  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 .co 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 w  w  w  . j  av a 2 s  .com
        // 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 {//  w  ww.  j a  v  a  2 s.  co 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> 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".//w ww . j ava2 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:nz.ac.auckland.abi.formatting.poi.ModelJSONToExcel.java

License:LGPL

private void addWorkSheet(Workbook wb, String measure, List<FEMModelMeasure> measures, int maxTimePoints,
        Map<String, CellStyle> styles) {
    Sheet sheet = wb.createSheet(measure);
    PrintSetup printSetup = sheet.getPrintSetup();
    printSetup.setLandscape(true);/* ww  w.ja  va  2s  . c  o  m*/
    sheet.setHorizontallyCenter(true);

    final int leadingHeaders = 4;
    int rowCounter = 0;
    // Create header row
    Row headerRow = sheet.createRow(rowCounter++);
    headerRow.setHeightInPoints(40);
    Cell headerCell;
    int colCtr = 0;
    {
        headerCell = headerRow.createCell(colCtr++);
        headerCell.setCellValue("ModelName");
        headerCell.setCellStyle(styles.get("header"));
    }
    {
        headerCell = headerRow.createCell(colCtr++);
        headerCell.setCellValue("StartTime");
        headerCell.setCellStyle(styles.get("header"));
    }
    {
        headerCell = headerRow.createCell(colCtr++);
        headerCell.setCellValue("EndTime");
        headerCell.setCellStyle(styles.get("header"));
    }
    {
        headerCell = headerRow.createCell(colCtr++);
        headerCell.setCellValue("MetaData");
        headerCell.setCellStyle(styles.get("header"));
    }
    {
        headerCell = headerRow.createCell(colCtr++);
        headerCell.setCellValue("ID");
        headerCell.setCellStyle(styles.get("header"));
    }
    // Insert Time points
    for (int i = 0; i < maxTimePoints; i++) {
        headerCell = headerRow.createCell(colCtr++);
        headerCell.setCellValue("" + i);
        headerCell.setCellStyle(styles.get("header"));
    }
    // Insert composite variables
    {
        headerCell = headerRow.createCell(colCtr++);
        headerCell.setCellValue("MAX");
        headerCell.setCellStyle(styles.get("header"));
    }
    {
        headerCell = headerRow.createCell(colCtr++);
        headerCell.setCellValue("MIN");
        headerCell.setCellStyle(styles.get("header"));
    }
    {
        headerCell = headerRow.createCell(colCtr++);
        headerCell.setCellValue("MEAN");
        headerCell.setCellStyle(styles.get("header"));
    }
    {
        headerCell = headerRow.createCell(colCtr++);
        headerCell.setCellValue("SD");
        headerCell.setCellStyle(styles.get("header"));
    }
    // Output the values for each measure

    for (FEMModelMeasure mes : measures) {
        double[][] strains = mes.getMeasure(measure);
        if (strains == null)
            continue;
        int numRows = strains.length + 1; // 1 for Avg
        int rowStarts = rowCounter;
        int colCounter = 0;
        for (int rctr = 0; rctr < numRows - 1; rctr++) {
            colCounter = 0;
            int myRowID = rowCounter + 1;
            Row row = sheet.createRow(rowCounter++);
            for (int colc = 0; colc < leadingHeaders; colc++) { // Common
                // Elements
                row.createCell(colCounter++);
            }
            // Create ROW ID
            {
                Cell cell = row.createCell(colCounter++);
                cell.setCellValue("S" + (rctr + 1));
            }
            String strainStartXLColName = getColumnPrefix(colCounter);
            int strainLength = strains[rctr].length;
            for (int stc = 0; stc < strainLength; stc++) {
                Cell cell = row.createCell(colCounter++);
                cell.setCellValue(strains[rctr][stc]);
            }
            String strainEndXLColName = getColumnPrefix(colCounter - 1);
            while (strainLength < maxTimePoints) { // Create dummy cells to
                // fill up space
                row.createCell(colCounter++);
                strainLength++;
            }
            // Add formulas and create names
            {
                //String modelName = mes.getModelName();
                //String sname = "";
                // MAX
                Cell cell = row.createCell(colCounter++);
                String ref = strainStartXLColName + "" + myRowID + ":" + strainEndXLColName + "" + myRowID;
                cell.setCellFormula("MAX(" + ref + ")");
                cell.setCellStyle(styles.get("MAX"));
                /*
                 * sname =
                 * (modelName+"_"+measure+"_"+"MAX_S"+(rctr+1)..replaceAll
                 * (" ", "_").trim(); Name namedCel = wb.createName();
                 * namedCel.setNameName(sname); String reference =
                 * measure+"!"+getColumnPrefix(colCounter-1)+myRowID; //
                 * cell reference namedCel.setRefersToFormula(reference);
                 */
                // MIN
                cell = row.createCell(colCounter++);
                cell.setCellFormula("MIN(" + ref + ")");
                cell.setCellStyle(styles.get("MIN"));
                /*
                 * sname = modelName+"_"+measure+"_"+"MIN_S"+(rctr+1);
                 * namedCel = wb.createName(); namedCel.setNameName(sname);
                 * reference =
                 * measure+"!"+getColumnPrefix(colCounter-1)+myRowID; //
                 * cell reference namedCel.setRefersToFormula(reference);
                 */
                // MEAN
                cell = row.createCell(colCounter++);
                cell.setCellFormula("AVERAGE(" + ref + ")");
                cell.setCellStyle(styles.get("AVERAGE"));
                /*
                 * sname = modelName+"_"+measure+"_"+"AVERAGE_S"+(rctr+1);
                 * namedCel = wb.createName(); namedCel.setNameName(sname);
                 * reference =
                 * measure+"!"+getColumnPrefix(colCounter-1)+myRowID; //
                 * cell reference namedCel.setRefersToFormula(reference);
                 */
                // STANDARD DEVIATION
                cell = row.createCell(colCounter++);
                cell.setCellFormula("STDEV(" + ref + ")");
                cell.setCellStyle(styles.get("STDEV"));
                /*
                 * sname = modelName+"_"+measure+"_"+"STDEV_S"+(rctr+1);
                 * namedCel = wb.createName(); namedCel.setNameName(sname);
                 * reference =
                 * measure+"!"+getColumnPrefix(colCounter-1)+""+myRowID; //
                 * cell reference namedCel.setRefersToFormula(reference);
                 */
            }
        }

        // Add user defined series
        Hashtable<String, String> formulaMap = new Hashtable<String, String>();
        for (String exp : userSeries) {

            // Replace all S[0-9]*, and D[0-9]* with appropriate column
            // values
            String toks[] = exp.split("=");
            int myRowID = rowCounter;
            colCounter = 0;
            Row row = sheet.createRow(rowCounter++);
            for (int colc = 0; colc < leadingHeaders; colc++) { // Common
                // Elements
                row.createCell(colCounter++);
            }
            // Create ROW ID
            {
                Cell cell = row.createCell(colCounter++);
                cell.setCellValue(toks[0]);
            }
            String strainStartXLColName = getColumnPrefix(colCounter);
            int strainLength = strains[numRows - 2].length;
            for (int stc = 0; stc < strainLength; stc++) {
                Cell cell = row.createCell(colCounter++);
                // Get the expression
                String expression = toks[1].toLowerCase();// Regex is case
                // senstive,
                // since th
                // COLUMN
                // PREFIXs are
                // CAPS,
                // replaceAll
                // will work as
                // expected else
                // S17 will
                // mathc for S1
                // (but not s1)
                for (int sCtr = mes.numSegments; sCtr > 0; sCtr--) {
                    String XLColName = (char) ('A' + stc + leadingHeaders + 1) + "" + (rowStarts + sCtr); // Note
                    // excel
                    // formulas
                    // need
                    // base
                    // 1
                    expression = expression.replaceAll("s" + sCtr + "{1}", XLColName);
                }
                cell.setCellFormula(expression);
                cell.setCellStyle(styles.get("AVGSERIES"));
            }
            String strainEndXLColName = getColumnPrefix(colCounter - 1);
            while (strainLength < maxTimePoints) { // Create dummy cells to
                // fill up space
                row.createCell(colCounter++);
                strainLength++;
            }
            // Add formulas and create names
            {
                StringBuffer formulas = new StringBuffer();

                String modelName = mes.getModelName();
                char c = modelName.charAt(0);
                if (c >= '0' && c <= '9') {
                    modelName = "_" + modelName;
                }
                String measureName = measure.replaceAll("\\(", "_").replaceAll("\\)", "_").replaceAll(" ", "");
                String sname = "";
                // MAX
                Cell cell = row.createCell(colCounter++);
                String ref = strainStartXLColName + myRowID + ":" + strainEndXLColName + myRowID;
                cell.setCellFormula("MAX(" + ref + ")");
                cell.setCellStyle(styles.get("MAX"));
                sname = (modelName + "_" + measureName + "_" + "MAX_" + toks[0]).replaceAll(" ", "_").trim();
                Name namedCel = wb.createName();
                namedCel.setNameName(sname);
                String reference = measureName + "!" + getColumnPrefix(colCounter - 1) + (myRowID + 1); // cell
                // reference
                // in
                // xl
                // base
                try {
                    namedCel.setRefersToFormula(reference);
                    formulas.append(reference + "#");
                    // MIN
                    cell = row.createCell(colCounter++);
                    cell.setCellFormula("MIN(" + ref + ")");
                    cell.setCellStyle(styles.get("MIN"));
                    sname = (modelName + "_" + measureName + "_" + "MIN_" + toks[0]).replaceAll(" ", "_")
                            .trim();
                    namedCel = wb.createName();
                    namedCel.setNameName(sname);
                    reference = measureName + "!" + getColumnPrefix(colCounter - 1) + (myRowID + 1); // cell
                    // reference
                    // in
                    // xl
                    // base
                    namedCel.setRefersToFormula(reference);
                    formulas.append(reference + "#");
                    // MEAN
                    cell = row.createCell(colCounter++);
                    cell.setCellFormula("AVERAGE(" + ref + ")");
                    cell.setCellStyle(styles.get("AVERAGE"));
                    sname = (modelName + "_" + measureName + "_" + "AVERAGE_" + toks[0]).replaceAll(" ", "_")
                            .trim();
                    namedCel = wb.createName();
                    namedCel.setNameName(sname);
                    reference = measureName + "!" + getColumnPrefix(colCounter - 1) + (myRowID + 1); // cell
                    // reference
                    // in
                    // xl
                    // base
                    namedCel.setRefersToFormula(reference);
                    formulas.append(reference + "#");
                    // STANDARD DEVIATION
                    cell = row.createCell(colCounter++);
                    cell.setCellFormula("STDEV(" + ref + ")");
                    cell.setCellStyle(styles.get("STDEV"));
                    sname = (modelName + "_" + measureName + "_" + "STDEV_" + toks[0]).replaceAll(" ", "_")
                            .trim();
                    namedCel = wb.createName();
                    namedCel.setNameName(sname);
                    reference = measureName + "!" + getColumnPrefix(colCounter - 1) + (myRowID + 1); // cell
                    // reference
                    // in
                    // xl
                    // base
                    namedCel.setRefersToFormula(reference);
                    formulas.append(reference);
                    formulaMap.put(toks[0], formulas.toString());
                } catch (Exception exx) {
                    //exx.printStackTrace();
                    System.out.println(exx + " occured for formula " + reference);
                }
            }
        }
        mes.addToFormulaMap(measure, formulaMap);
        // Set the commom columns
        Row row;
        Cell cell;
        row = sheet.getRow(rowStarts);
        cell = row.getCell(0);
        cell.setCellValue(mes.getModelName());
        cell = row.getCell(1);
        cell.setCellValue(mes.getStartTime());
        cell = row.getCell(2);
        cell.setCellValue(mes.getEndTime());
        cell = row.getCell(3);
        cell.setCellValue(mes.getMetaData());
        sheet.addMergedRegion(CellRangeAddress.valueOf("$A$" + (rowStarts + 1) + ":$A$" + (rowCounter))); // Since
        // excel
        // number
        // starts
        // from
        // 1
        // but
        // api
        // is
        // 0
        sheet.addMergedRegion(CellRangeAddress.valueOf("$B$" + (rowStarts + 1) + ":$B$" + (rowCounter)));
        sheet.addMergedRegion(CellRangeAddress.valueOf("$C$" + (rowStarts + 1) + ":$C$" + (rowCounter)));
        sheet.addMergedRegion(CellRangeAddress.valueOf("$D$" + (rowStarts + 1) + ":$D$" + (rowCounter)));
        sheet.createRow(rowCounter++);// Create Empty row for model break
    }

}

---

From source file:org.cgiar.ccafs.marlo.action.center.capdev.ParticipantsAction.java

License:Open Source License

public void createDataValidator(Workbook wb, Sheet sheet, String[] data, String dataValidationName) {
    String reference = null;//from  w w  w.j  av  a  2  s .c o  m

    /*
     * se escriben las listas con los datos en cada sheet deseada
     */
    for (int i = 0; i < data.length; i++) {
        final Row fila = sheet.createRow(i);
        final Cell celda = fila.createCell(0);
        celda.setCellValue(data[i]);
    }

    // se protege el sheet para evitar que el usuario lo modifique
    sheet.protectSheet("marlo-ciat");

    /*
     * se crea referencia la lista de datos con el que se creara el data validator
     */
    final Name namedCountry = wb.createName();
    namedCountry.setNameName(dataValidationName);
    reference = sheet.getSheetName() + "!$A$1:$A$" + data.length; // area de referencia
    namedCountry.setRefersToFormula(reference);

}

---

• «
• 1
• 2
• »