Example usage for org.apache.poi.ss.usermodel Cell setCellFormula

List of usage examples for org.apache.poi.ss.usermodel Cell setCellFormula

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. Cell
  6. setCellFormula

Introduction

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

Prototype

void setCellFormula(String formula) throws FormulaParseException, IllegalStateException;

Source Link

Document

Sets formula for this cell.

Usage

From source file:metrics.sink.MetricsTable.java

License:Open Source License

private void printFooters() {
    int columns = columnMapping.size();
    Row firstRow = currentSheet.getRow(1);
    Row lastRow = currentSheet.getRow(rowCount - 1);

    Row sumFooterRow = currentSheet.createRow(rowCount++);
    Cell sumFooterLabelCell = sumFooterRow.createCell(0);
    sumFooterLabelCell.setCellValue("SUM");

    Row averageFooterRow = currentSheet.createRow(rowCount++);
    Cell averageFooterLabelCell = averageFooterRow.createCell(0);
    averageFooterLabelCell.setCellValue("AVERAGE");

    for (int index = 1; index <= columns; index++) {
        Cell cell = firstRow.getCell(index);
        if (cell == null) {
            cell = firstRow.createCell(index);
        }//w w  w.  j av a 2s . com
        Cell sumFooterCell = sumFooterRow.createCell(index);
        Cell averageFooterCell = averageFooterRow.createCell(index);

        CellReference firstCell = new CellReference(firstRow.getCell(index));
        Cell lastRowCell = lastRow.getCell(index);
        if (lastRowCell == null) {
            lastRowCell = lastRow.createCell(index);
        }
        CellReference lastCell = new CellReference(lastRowCell);

        sumFooterCell
                .setCellFormula("SUM(" + firstCell.formatAsString() + ":" + lastCell.formatAsString() + ")");
        averageFooterCell.setCellFormula(
                "AVERAGE(" + firstCell.formatAsString() + ":" + lastCell.formatAsString() + ")");
    }
}

From source file:mn.tsagaangeruud.TimesheetDemo.java

License:Apache License

public static void main(String[] args) throws Exception {
    Workbook wb;//from   www .  ja  v  a 2 s . co m

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

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

    Sheet sheet = wb.createSheet("Timesheet");
    PrintSetup printSetup = sheet.getPrintSetup();
    printSetup.setLandscape(true);
    sheet.setFitToPage(true);
    sheet.setHorizontallyCenter(true);

    //title row
    Row titleRow = sheet.createRow(0);
    titleRow.setHeightInPoints(45);
    Cell titleCell = titleRow.createCell(0);
    titleCell.setCellValue("Weekly Timesheet");
    titleCell.setCellStyle(styles.get("title"));
    sheet.addMergedRegion(CellRangeAddress.valueOf("$A$1:$L$1"));

    //header row
    Row headerRow = sheet.createRow(1);
    headerRow.setHeightInPoints(40);
    Cell headerCell;
    for (int i = 0; i < titles.length; i++) {
        headerCell = headerRow.createCell(i);
        headerCell.setCellValue(titles[i]);
        headerCell.setCellStyle(styles.get("header"));
    }

    int rownum = 2;
    for (int i = 0; i < 10; i++) {
        Row row = sheet.createRow(rownum++);
        for (int j = 0; j < titles.length; j++) {
            Cell cell = row.createCell(j);
            if (j == 9) {
                //the 10th cell contains sum over week days, e.g. SUM(C3:I3)
                String ref = "C" + rownum + ":I" + rownum;
                cell.setCellFormula("SUM(" + ref + ")");
                cell.setCellStyle(styles.get("formula"));
            } else if (j == 11) {
                cell.setCellFormula("J" + rownum + "-K" + rownum);
                cell.setCellStyle(styles.get("formula"));
            } else {
                cell.setCellStyle(styles.get("cell"));
            }
        }
    }

    //row with totals below
    Row sumRow = sheet.createRow(rownum++);
    sumRow.setHeightInPoints(35);
    Cell cell;
    cell = sumRow.createCell(0);
    cell.setCellStyle(styles.get("formula"));
    cell = sumRow.createCell(1);
    cell.setCellValue("Total Hrs:");
    cell.setCellStyle(styles.get("formula"));

    for (int j = 2; j < 12; j++) {
        cell = sumRow.createCell(j);
        String ref = (char) ('A' + j) + "3:" + (char) ('A' + j) + "12";
        cell.setCellFormula("SUM(" + ref + ")");
        if (j >= 9)
            cell.setCellStyle(styles.get("formula_2"));
        else
            cell.setCellStyle(styles.get("formula"));
    }
    rownum++;
    sumRow = sheet.createRow(rownum++);
    sumRow.setHeightInPoints(25);
    cell = sumRow.createCell(0);
    cell.setCellValue("Total Regular Hours");
    cell.setCellStyle(styles.get("formula"));
    cell = sumRow.createCell(1);
    cell.setCellFormula("L13");
    cell.setCellStyle(styles.get("formula_2"));
    sumRow = sheet.createRow(rownum++);
    sumRow.setHeightInPoints(25);
    cell = sumRow.createCell(0);
    cell.setCellValue("Total Overtime Hours");
    cell.setCellStyle(styles.get("formula"));
    cell = sumRow.createCell(1);
    cell.setCellFormula("K13");
    cell.setCellStyle(styles.get("formula_2"));

    //set sample data
    for (int i = 0; i < sample_data.length; i++) {
        Row row = sheet.getRow(2 + i);
        for (int j = 0; j < sample_data[i].length; j++) {
            if (sample_data[i][j] == null)
                continue;

            if (sample_data[i][j] instanceof String) {
                row.getCell(j).setCellValue((String) sample_data[i][j]);
            } else {
                row.getCell(j).setCellValue((Double) sample_data[i][j]);
            }
        }
    }

    //finally set column widths, the width is measured in units of 1/256th of a character width
    sheet.setColumnWidth(0, 30 * 256); //30 characters wide
    for (int i = 2; i < 9; i++) {
        sheet.setColumnWidth(i, 6 * 256); //6 characters wide
    }
    sheet.setColumnWidth(10, 10 * 256); //10 characters wide

    // Write the output to a file
    String file = "timesheet.xls";
    if (wb instanceof XSSFWorkbook)
        file += "x";
    FileOutputStream out = new FileOutputStream(file);
    wb.write(out);
    out.close();
}

From source file:nc.noumea.mairie.appock.services.impl.ExportExcelServiceImpl.java

License:Open Source License

private void remplitLigneArticle(Workbook wb, List<ArticleDemande> listeArticleDemande, Sheet sheet) {
    int numRow = 10;
    Map<ArticleCatalogue, Integer> mapArticleQuantite = new HashMap<>();

    for (ArticleDemande articleDemande : listeArticleDemande) {
        ArticleCatalogue articleCatalogue = articleDemande.getArticleCatalogue();
        Integer quantite = mapArticleQuantite.get(articleCatalogue);
        if (quantite != null) {
            mapArticleQuantite.replace(articleCatalogue, quantite + articleDemande.getQuantiteCommande());
        } else {//from   ww w. ja v  a  2s . c  o  m
            mapArticleQuantite.put(articleCatalogue, articleDemande.getQuantiteCommande());
        }
    }

    List<ArticleCatalogue> listeArticleCatalogue = new ArrayList(mapArticleQuantite.keySet());
    Collections.sort(listeArticleCatalogue, new ArticleCatalogueComparator());

    for (ArticleCatalogue articleCatalogue : listeArticleCatalogue) {
        Row row = createRowGeneric(sheet, numRow, 500);
        row.createCell(0).setCellValue(articleCatalogue.getReference());
        row.createCell(1).setCellValue(articleCatalogue.getLibelle());
        row.createCell(2).setCellValue(articleCatalogue.getPrix());
        row.createCell(3).setCellValue(articleCatalogue.getLibelleColisage());
        row.createCell(4).setCellValue(mapArticleQuantite.get(articleCatalogue));

        String strFormula = "C" + (numRow + 1) + "*E" + (numRow + 1) + "";
        Cell cell = row.createCell(5);
        cell.setCellType(CellType.FORMULA);
        cell.setCellFormula(strFormula);

        CellStyle style = createCellWithBorderAndColor(wb, BorderStyle.THIN, IndexedColors.LIGHT_YELLOW, false);
        CellStyle stylePrix = createCellWithBorderAndColor(wb, BorderStyle.THIN, IndexedColors.LIGHT_YELLOW,
                false);
        stylePrix.setDataFormat(HSSFDataFormat.getBuiltinFormat("#,##0"));

        row.getCell(0).setCellStyle(style);
        row.getCell(1).setCellStyle(style);
        row.getCell(2).setCellStyle(stylePrix);
        row.getCell(3).setCellStyle(style);
        row.getCell(4).setCellStyle(style);
        row.getCell(5).setCellStyle(stylePrix);
        numRow++;
    }
}

From source file:net.ceos.project.poi.annotated.core.CellFormulaHandler.java

License:Apache License

/**
 * Apply a formula value at the Cell.//from   w  w w .j ava  2  s .co  m
 * 
 * @param configCriteria
 *            the {@link XConfigCriteria} object
 * @param cell
 *            the {@link Cell} to use
 * @throws ElementException
 */
private static boolean toFormula(final XConfigCriteria configCriteria, final Cell cell)
        throws ElementException {
    boolean isFormulaApplied = false;

    if (StringUtils.isNotBlank(configCriteria.getElement().formula())) {
        // calculate position according the propagation type
        int position = PredicateFactory.isPropagationHorizontal.test(configCriteria.getPropagation())
                ? cell.getRowIndex() + 1
                : cell.getColumnIndex();

        // calculate and apply formula
        cell.setCellFormula(CellFormulaConverter.calculateSimpleOrDynamicFormula(configCriteria, position));
        isFormulaApplied = true;
    }

    return isFormulaApplied;
}

From source file:net.illustrato.ctrl.CtrlCore.java

private Row copyRow(Workbook workbook, Sheet worksheet, int sourceRowNum, int destinationRowNum) {
    // Get the source / new row
    Row newRow = worksheet.getRow(destinationRowNum);
    Row sourceRow = worksheet.getRow(sourceRowNum);

    // If the row exist in destination, push down all rows by 1 else create a new row
    if (newRow != null) {
        worksheet.shiftRows(destinationRowNum, worksheet.getLastRowNum(), 1);
    } else {//from  w w  w .  j a  v  a  2  s  .co m
        newRow = worksheet.createRow(destinationRowNum);
    }

    // Loop through source columns to add to new row
    for (int i = 0; i < sourceRow.getLastCellNum(); i++) {
        // Grab a copy of the old/new cell
        Cell oldCell = sourceRow.getCell(i);
        Cell newCell = newRow.createCell(i);

        // If the old cell is null jump to next cell
        if (oldCell == null) {
            newCell = null;
            continue;
        }

        // Copy style from old cell and apply to new cell
        CellStyle newCellStyle = workbook.createCellStyle();
        newCellStyle.cloneStyleFrom(oldCell.getCellStyle());
        newCell.setCellStyle(newCellStyle);

        // Set the cell data type
        newCell.setCellType(oldCell.getCellType());

        // Set the cell data value
        switch (oldCell.getCellType()) {
        case HSSFCell.CELL_TYPE_BLANK:
            newCell.setCellValue(oldCell.getStringCellValue());
            break;
        case HSSFCell.CELL_TYPE_FORMULA:
            newCell.setCellFormula(oldCell.getCellFormula());
            //Si tenemos que modificar la formulario lo podemos hacer como string
            //oldCell.getCellFormula().replace("A"+sourceRowNum, "A"+destinationRowNum)
            break;
        case HSSFCell.CELL_TYPE_NUMERIC:
            newCell.setCellValue(oldCell.getNumericCellValue());
            break;
        case HSSFCell.CELL_TYPE_STRING:
            newCell.setCellValue(oldCell.getRichStringCellValue());
            break;
        }
    }
    return newRow;
}

From source file:net.sf.excelutils.tags.FormulaTag.java

License:Apache License

public int[] parseTag(Object context, Workbook wb, Sheet sheet, Row curRow, Cell curCell)
        throws ExcelException {

    String cellstr = curCell.getStringCellValue();
    if (null == cellstr || "".equals(cellstr)) {
        return new int[] { 0, 0, 0 };
    }//from   w w  w.j a  v  a  2 s .com
    LOG.debug("FormulaTag:" + cellstr);
    cellstr = cellstr.substring(KEY_FORMULA.length()).trim();

    Object formula = ExcelParser.parseStr(context, cellstr);

    if (null != formula) {
        curCell.setCellFormula(formula.toString());
    }

    return new int[] { 0, 0, 0 };
}

From source file:net.sf.excelutils.WorkbookUtils.java

License:Apache License

/**
 * copy row//from ww w .  j  a va2 s. c om
 *
 * @param sheet
 * @param from begin of the row
 * @param to destination fo the row
 * @param count count of copy
 */
public static void copyRow(Sheet sheet, int from, int to, int count) {

    for (int rownum = from; rownum < from + count; rownum++) {
        Row fromRow = sheet.getRow(rownum);
        Row toRow = getRow(to + rownum - from, sheet);
        if (null == fromRow)
            return;
        toRow.setHeight(fromRow.getHeight());
        toRow.setHeightInPoints(fromRow.getHeightInPoints());
        int lastCellNum = fromRow.getLastCellNum();
        lastCellNum = lastCellNum > 255 ? 255 : lastCellNum;
        for (int i = fromRow.getFirstCellNum(); i <= lastCellNum && i >= 0; i++) {
            Cell fromCell = getCell(fromRow, i);
            Cell toCell = getCell(toRow, i);
            // toCell.setEncoding(fromCell.getEncoding());
            toCell.setCellStyle(fromCell.getCellStyle());
            toCell.setCellType(fromCell.getCellType());
            switch (fromCell.getCellType()) {
            case Cell.CELL_TYPE_BOOLEAN:
                toCell.setCellValue(fromCell.getBooleanCellValue());
                break;
            case Cell.CELL_TYPE_FORMULA:
                toCell.setCellFormula(fromCell.getCellFormula());
                break;
            case Cell.CELL_TYPE_NUMERIC:
                toCell.setCellValue(fromCell.getNumericCellValue());
                break;
            case Cell.CELL_TYPE_STRING:
                toCell.setCellValue(fromCell.getStringCellValue());
                break;
            default:
            }
        }
    }

    // copy merged region
    List shiftedRegions = new ArrayList();
    for (int i = 0; i < sheet.getNumMergedRegions(); i++) {
        CellRangeAddress r = sheet.getMergedRegion(i);
        if (r.getFirstRow() >= from && r.getLastRow() < from + count) {
            CellRangeAddress n_r = new CellRangeAddress(r.getFirstRow() + to - from, r.getLastRow() + to - from,
                    r.getFirstColumn(), r.getLastColumn());
            shiftedRegions.add(n_r);
        }
    }

    // readd so it doesn't get shifted again
    Iterator iterator = shiftedRegions.iterator();
    while (iterator.hasNext()) {
        CellRangeAddress region = (CellRangeAddress) iterator.next();
        sheet.addMergedRegion(region);
    }
}

From source file:net.sf.excelutils.WorkbookUtils.java

License:Apache License

public static void shiftCell(Sheet sheet, Row row, Cell beginCell, int shift, int rowCount) {

    if (shift == 0)
        return;//from   w  ww  .  j  ava 2s  .co  m

    // get the from & to row
    int fromRow = row.getRowNum();
    int toRow = row.getRowNum() + rowCount - 1;
    for (int i = 0; i < sheet.getNumMergedRegions(); i++) {
        CellRangeAddress r = sheet.getMergedRegion(i);
        if (r.getFirstRow() == row.getRowNum()) {
            if (r.getLastRow() > toRow) {
                toRow = r.getLastRow();
            }
            if (r.getFirstRow() < fromRow) {
                fromRow = r.getFirstRow();
            }
        }
    }

    for (int rownum = fromRow; rownum <= toRow; rownum++) {
        Row curRow = WorkbookUtils.getRow(rownum, sheet);
        int lastCellNum = curRow.getLastCellNum();
        for (int cellpos = lastCellNum; cellpos >= beginCell.getColumnIndex(); cellpos--) {
            Cell fromCell = WorkbookUtils.getCell(curRow, cellpos);
            Cell toCell = WorkbookUtils.getCell(curRow, cellpos + shift);
            toCell.setCellType(fromCell.getCellType());
            toCell.setCellStyle(fromCell.getCellStyle());
            switch (fromCell.getCellType()) {
            case Cell.CELL_TYPE_BOOLEAN:
                toCell.setCellValue(fromCell.getBooleanCellValue());
                break;
            case Cell.CELL_TYPE_FORMULA:
                toCell.setCellFormula(fromCell.getCellFormula());
                break;
            case Cell.CELL_TYPE_NUMERIC:
                toCell.setCellValue(fromCell.getNumericCellValue());
                break;
            case Cell.CELL_TYPE_STRING:
                toCell.setCellValue(fromCell.getStringCellValue());
                break;
            case Cell.CELL_TYPE_ERROR:
                toCell.setCellErrorValue(fromCell.getErrorCellValue());
                break;
            }
            fromCell.setCellValue("");
            fromCell.setCellType(Cell.CELL_TYPE_BLANK);
            // Workbook wb = new Workbook();
            // CellStyle style = wb.createCellStyle();
            // fromCell.setCellStyle(style);
        }

        // process merged region
        for (int cellpos = lastCellNum; cellpos >= beginCell.getColumnIndex(); cellpos--) {
            Cell fromCell = WorkbookUtils.getCell(curRow, cellpos);

            List shiftedRegions = new ArrayList();
            for (int i = 0; i < sheet.getNumMergedRegions(); i++) {
                CellRangeAddress r = sheet.getMergedRegion(i);
                if (r.getFirstRow() == curRow.getRowNum() && r.getFirstColumn() == fromCell.getColumnIndex()) {
                    r.setFirstColumn((short) (r.getFirstColumn() + shift));
                    r.setLastColumn((short) (r.getLastColumn() + shift));
                    // have to remove/add it back
                    shiftedRegions.add(r);
                    sheet.removeMergedRegion(i);
                    // we have to back up now since we removed one
                    i = i - 1;
                }
            }

            // readd so it doesn't get shifted again
            Iterator iterator = shiftedRegions.iterator();
            while (iterator.hasNext()) {
                CellRangeAddress region = (CellRangeAddress) iterator.next();
                sheet.addMergedRegion(region);
            }
        }
    }
}

From source file:nl.detoren.ijsco.io.ExcelExport.java

License:Open Source License

private void updateCell(XSSFSheet sheet, int row, int col, String value, boolean formula) {
    Cell cell = getCell(sheet, row, col);
    cell.setCellFormula(value);
}

From source file:nz.ac.auckland.abi.formatting.poi.ModelJSONToExcel.java

License:LGPL

private void createDashBoard(Sheet sheet, String[] measureNames, List<FEMModelMeasure> measures,
        Map<String, CellStyle> styles) {
    // Create header - metaData is expected to be csv
    // Each user variable has 4 additional columns
    int maxMetaDataColumns = getMaxMetaDataColumns(measures);
    int rowCounter = 0;
    // Create header row
    Row headerRow1 = sheet.createRow(rowCounter++);
    Row headerRow2 = sheet.createRow(rowCounter++);
    Row headerRow3 = sheet.createRow(rowCounter++);
    // Create column headers
    Cell headerCell;/*from w  ww  .j a  va2  s.  c  o  m*/
    int colCtr = 0;
    {
        int colID = colCtr++;
        headerRow1.createCell(colID);
        headerRow2.createCell(colID);
        headerCell = headerRow3.createCell(colID);
        /*
         * headerCell.setCellValue("Sno");
         * headerCell.setCellStyle(styles.get("header"));
         */
        sheet.addMergedRegion(CellRangeAddress.valueOf("$A$1:$A$3"));
        headerRow1.getCell(0).setCellValue("Sno");
        headerRow1.getCell(0).setCellStyle(styles.get("header"));
    }
    for (int i = 0; i < maxMetaDataColumns; i++) {
        int colID = colCtr++;
        String xcolID = getColumnPrefix(colID);
        headerRow3.createCell(colID);
        headerRow2.createCell(colID);
        headerCell = headerRow1.createCell(colID);
        sheet.addMergedRegion(CellRangeAddress.valueOf("$" + xcolID + "$1:$" + xcolID + "$3"));
        headerCell.setCellValue("Model Identifier " + i);
        headerCell.setCellStyle(styles.get("header"));
    }

    String[] compHeader = { "MAX", "MIN", "AVERAGE", "STDEV" };
    for (int i = 0; i < userSeries.size(); i++) {
        int startColID = colCtr;
        // For each measure
        for (int j = 0; j < measureNames.length; j++) {
            int mstartColID = colCtr;
            // The four possible composites
            for (int k = 0; k < compHeader.length; k++) {
                int colID = colCtr++;
                headerRow1.createCell(colID);
                headerRow2.createCell(colID);
                headerCell = headerRow3.createCell(colID);
                headerCell.setCellValue(compHeader[k]);
                headerCell.setCellStyle(styles.get("header"));
            }
            headerRow2.getCell(mstartColID).setCellValue(measureNames[j]);
            headerRow2.getCell(mstartColID).setCellStyle(styles.get("formula"));
            String startColPrefix = getColumnPrefix(mstartColID);
            String endColPrefix = getColumnPrefix(colCtr - 1);
            sheet.addMergedRegion(
                    CellRangeAddress.valueOf("$" + startColPrefix + "$2:$" + endColPrefix + "$2"));
        }
        String[] exp = userSeries.get(i).split("=");
        headerRow1.getCell(startColID).setCellValue(exp[0]);
        headerRow1.getCell(startColID).setCellStyle(styles.get("title"));
        String startColPrefix = getColumnPrefix(startColID);
        String endColPrefix = getColumnPrefix(colCtr - 1);
        sheet.addMergedRegion(CellRangeAddress.valueOf("$" + startColPrefix + "$1:$" + endColPrefix + "$1"));
    }
    // For each model output the data
    int ctr = 1;
    for (FEMModelMeasure model : measures) {
        Row row = sheet.createRow(rowCounter++);
        colCtr = 0;
        Cell cell = row.createCell(colCtr++);
        cell.setCellValue(ctr++);
        String[] metaData = model.getMetaData().split("\t");
        int mLength = metaData.length;
        for (int i = 0; i < mLength; i++) {
            cell = row.createCell(colCtr++);
            cell.setCellValue(metaData[i]);
        }
        while (mLength < maxMetaDataColumns) {
            cell = row.createCell(colCtr++);
            mLength++;
        }
        // String modelName = model.getModelName().replaceAll(" ",
        // "_").trim();;
        for (int i = 0; i < userSeries.size(); i++) {
            String[] exp = userSeries.get(i).split("=");
            for (int j = 0; j < measureNames.length; j++) {
                double[][] strains = model.getMeasure(measureNames[j]);
                if (strains == null)
                    continue;
                // Measure//userSeries
                Hashtable<String, String> map = model.getFormulaMap(measureNames[j]);
                try {
                    String[] formulas = map.get(exp[0]).split("#");
                    for (int k = 0; k < compHeader.length; k++) {
                        // String sname =
                        // modelName+"_"+measureNames[j]+"_"+compHeader[k]+"_"+exp[0];
                        cell = row.createCell(colCtr++);
                        cell.setCellFormula(formulas[k]);
                    }
                } catch (Exception exx) {
                    //exx.printStackTrace();
                    System.out.println(exx + " occured for expresion " + exp[0]);
                }
            }
        }
    }

}