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: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 .j  av a2 s  .c  om
    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.aio.handy.poi.TimesheetDemo.java

License:Apache License

public static void main(String[] args) throws Exception {
    Workbook wb;/*from   w  ww .  j a va2 s .c om*/

    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 = "e:/timesheet.xls";
    if (wb instanceof XSSFWorkbook)
        file += "x";
    FileOutputStream out = new FileOutputStream(file);
    wb.write(out);
    out.close();
}

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

License:Open Source License

public cfData execute(cfSession _session, List<cfData> parameters) throws cfmRunTimeException {
    cfSpreadSheetData spreadsheet = null;
    String formula;//from ww w .  j  a  v a2  s  .  com
    int rowNo, columnNo;

    /*
     * Collect up the parameters
     */
    spreadsheet = (cfSpreadSheetData) parameters.get(3);
    formula = parameters.get(2).getString();
    rowNo = parameters.get(1).getInt() - 1;
    columnNo = parameters.get(0).getInt() - 1;

    if (rowNo < 0)
        throwException(_session, "row must be 1 or greater (" + rowNo + ")");
    if (columnNo < 0)
        throwException(_session, "column must be 1 or greater (" + columnNo + ")");

    /*
     * Perform the insertion
     */
    Sheet sheet = spreadsheet.getActiveSheet();
    Row row = sheet.getRow(rowNo);
    if (row == null)
        row = sheet.createRow(rowNo);

    Cell cell = row.getCell(columnNo);
    if (cell == null)
        cell = row.createCell(columnNo);

    cell.setCellFormula(formula);

    return cfBooleanData.TRUE;
}

From source file:org.alanwilliamson.openbd.plugin.spreadsheet.SheetUtility.java

License:Open Source License

public static void cloneCell(Cell cNew, Cell cOld) {
    cNew.setCellComment(cOld.getCellComment());
    cNew.setCellStyle(cOld.getCellStyle());
    cNew.setCellType(cOld.getCellType());

    switch (cNew.getCellType()) {
    case Cell.CELL_TYPE_BOOLEAN: {
        cNew.setCellValue(cOld.getBooleanCellValue());
        break;//from   ww  w . j  av a2s  . com
    }
    case Cell.CELL_TYPE_NUMERIC: {
        cNew.setCellValue(cOld.getNumericCellValue());
        break;
    }
    case Cell.CELL_TYPE_STRING: {
        cNew.setCellValue(cOld.getStringCellValue());
        break;
    }
    case Cell.CELL_TYPE_ERROR: {
        cNew.setCellValue(cOld.getErrorCellValue());
        break;
    }
    case Cell.CELL_TYPE_FORMULA: {
        cNew.setCellFormula(cOld.getCellFormula());
        break;
    }
    case Cell.CELL_TYPE_BLANK: {
        cNew.setCellValue(cOld.getNumericCellValue());
        break;
    }

    }

}

From source file:org.bbreak.excella.core.util.PoiUtil.java

License:Open Source License

/**
 * ?// www.j  a v a  2s.  c o m
 * 
 * @param fromCell 
 * @param toCell 
 */
public static void copyCell(Cell fromCell, Cell toCell) {

    if (fromCell != null) {

        // 
        CellType cellType = fromCell.getCellTypeEnum();
        switch (cellType) {
        case BLANK:
            break;
        case FORMULA:
            String cellFormula = fromCell.getCellFormula();
            toCell.setCellFormula(cellFormula);
            break;
        case BOOLEAN:
            toCell.setCellValue(fromCell.getBooleanCellValue());
            break;
        case ERROR:
            toCell.setCellErrorValue(fromCell.getErrorCellValue());
            break;
        case NUMERIC:
            toCell.setCellValue(fromCell.getNumericCellValue());
            break;
        case STRING:
            toCell.setCellValue(fromCell.getRichStringCellValue());
            break;
        default:
        }

        // 
        if (fromCell.getCellStyle() != null
                && fromCell.getSheet().getWorkbook().equals(toCell.getSheet().getWorkbook())) {
            toCell.setCellStyle(fromCell.getCellStyle());
        }

        // 
        if (fromCell.getCellComment() != null) {
            toCell.setCellComment(fromCell.getCellComment());
        }
    }
}

From source file:org.eclipse.rcptt.ecl.data.apache.poi.impl.internal.commands.WriteExcelFileService.java

License:Open Source License

private void writeRow(Row row, org.eclipse.rcptt.ecl.data.objects.Row tableRow) {
    EList<String> cells = tableRow.getValues();
    for (int cellnum = 0; cellnum < cells.size(); cellnum++) {
        String value = cells.get(cellnum);
        if (value == null || value.equals("")) {
            continue;
        }//from  w  ww. java  2 s.c  om

        Cell cell = row.getCell(cellnum);
        if (cell == null) {
            cell = row.createCell(cellnum);
        }

        if (value.startsWith("=")) {
            try {
                cell.setCellFormula(value.replaceAll("^=", ""));
            } catch (FormulaParseException e) {
                cell.setCellValue(value);
            }
        } else {
            cell.setCellValue(value);
        }
    }
}

From source file:org.eclipse.titanium.markers.export.XlsProblemExporter.java

License:Open Source License

/**
 * Create the summary sheet in the exported document.
 * /*  w w w .  j a  v a2s  . c om*/
 * @param workbook the workbook to work in.
 * */
private void createTimeSheet(final HSSFWorkbook workbook) {
    final HSSFSheet timeSheet = workbook.createSheet("Repair times");
    workbook.setSheetOrder("Repair times", 1);

    final Row headerRow = timeSheet.createRow(0);
    headerRow.createCell(1).setCellValue("Minimal repair time");
    headerRow.createCell(2).setCellValue("Average repair time");
    headerRow.createCell(3).setCellValue("Maximal repair time");

    int summaryRow = 4;
    Cell label;
    for (final TaskType t : TaskType.values()) {
        final Row row2 = timeSheet.createRow(summaryRow);
        label = row2.createCell(0);
        label.setCellValue(t.getHumanReadableName());
        final Cell minTimeCell = row2.createCell(1);
        minTimeCell.setCellType(HSSFCell.CELL_TYPE_FORMULA);
        minTimeCell.setCellFormula(t.getMinRepairTime() + "*Summary!$B" + (summaryRow + 1));
        final Cell avgTimeCell = row2.createCell(2);
        avgTimeCell.setCellType(HSSFCell.CELL_TYPE_FORMULA);
        avgTimeCell.setCellFormula(t.getAvgRepairTime() + "*Summary!$B" + (summaryRow + 1));
        final Cell maxTimeCell = row2.createCell(3);
        maxTimeCell.setCellType(HSSFCell.CELL_TYPE_FORMULA);
        maxTimeCell.setCellFormula(t.getMaxRepairTime() + "*Summary!$B" + (++summaryRow));
    }

    for (final CodeSmellType t : CodeSmellType.values()) {
        final Row row2 = timeSheet.createRow(summaryRow);
        label = row2.createCell(0);
        label.setCellValue(t.getHumanReadableName());
        final Cell minTimeCell = row2.createCell(1);
        minTimeCell.setCellType(HSSFCell.CELL_TYPE_FORMULA);
        minTimeCell.setCellFormula(t.getMinRepairTime() + "*Summary!$B" + (summaryRow + 1));
        final Cell avgTimeCell = row2.createCell(2);
        avgTimeCell.setCellType(HSSFCell.CELL_TYPE_FORMULA);
        avgTimeCell.setCellFormula(t.getAvgRepairTime() + "*Summary!$B" + (summaryRow + 1));
        final Cell maxTimeCell = row2.createCell(3);
        maxTimeCell.setCellType(HSSFCell.CELL_TYPE_FORMULA);
        maxTimeCell.setCellFormula(t.getMaxRepairTime() + "*Summary!$B" + (++summaryRow));
    }

    final Row totalRow = timeSheet.createRow(1);
    totalRow.createCell(0).setCellValue("Total");

    final Cell cell1 = totalRow.createCell(1);
    cell1.setCellType(HSSFCell.CELL_TYPE_FORMULA);
    cell1.setCellFormula("SUM($B4:$B" + summaryRow + ")");

    final Cell cell2 = totalRow.createCell(2);
    cell2.setCellType(HSSFCell.CELL_TYPE_FORMULA);
    cell2.setCellFormula("SUM($C4:$C" + summaryRow + ")");

    final Cell cell3 = totalRow.createCell(3);
    cell3.setCellType(HSSFCell.CELL_TYPE_FORMULA);
    cell3.setCellFormula("SUM($D4:$D" + summaryRow + ")");

    timeSheet.autoSizeColumn(0);
    timeSheet.autoSizeColumn(1);
    timeSheet.autoSizeColumn(2);
    timeSheet.autoSizeColumn(3);
}

From source file:org.efaps.esjp.common.file.FileUtil_Base.java

License:Apache License

/**
 * Copy cell./*from   ww  w  .  ja v  a2 s.  c o  m*/
 *
 * @param _oldCell the old cell
 * @param _newCell the new cell
 * @param _styleMap the style map
 */
protected void copyCell(final Cell _oldCell, final Cell _newCell, final Map<Integer, CellStyle> _styleMap) {
    if (_styleMap != null) {
        if (_oldCell.getSheet().getWorkbook() == _newCell.getSheet().getWorkbook()) {
            _newCell.setCellStyle(_oldCell.getCellStyle());
        } else {
            final int stHashCode = _oldCell.getCellStyle().hashCode();
            CellStyle newCellStyle = _styleMap.get(stHashCode);
            if (newCellStyle == null) {
                newCellStyle = _newCell.getSheet().getWorkbook().createCellStyle();
                newCellStyle.cloneStyleFrom(_oldCell.getCellStyle());
                _styleMap.put(stHashCode, newCellStyle);
            }
            _newCell.setCellStyle(newCellStyle);
        }
    }
    switch (_oldCell.getCellType()) {
    case Cell.CELL_TYPE_STRING:
        _newCell.setCellValue(_oldCell.getStringCellValue());
        break;
    case Cell.CELL_TYPE_NUMERIC:
        _newCell.setCellValue(_oldCell.getNumericCellValue());
        break;
    case Cell.CELL_TYPE_BLANK:
        _newCell.setCellType(Cell.CELL_TYPE_BLANK);
        break;
    case Cell.CELL_TYPE_BOOLEAN:
        _newCell.setCellValue(_oldCell.getBooleanCellValue());
        break;
    case Cell.CELL_TYPE_ERROR:
        _newCell.setCellErrorValue(_oldCell.getErrorCellValue());
        break;
    case Cell.CELL_TYPE_FORMULA:
        _newCell.setCellFormula(_oldCell.getCellFormula());
        break;
    default:
        break;
    }

}

From source file:org.formulacompiler.spreadsheet.internal.excel.xls.saver.ExcelXLSSaver.java

License:Open Source License

private Cell createCell(CellInstance _c, int _colIdx, Row _row) throws SpreadsheetException {
    final Cell cell = _row.createCell(_colIdx);
    if (_c instanceof CellWithExpression) {
        final ExpressionNode expr = ((CellWithExpression) _c).getExpression();
        cell.setCellFormula(ExpressionFormatter.format(expr, _c.getCellIndex()));
    } else {//w w  w .j a v  a  2s  . c om
        final Object val = _c.getValue();
        if (val instanceof String) {
            cell.setCellValue((String) val);
        } else if (val instanceof Date) {
            final GregorianCalendar calendar = new GregorianCalendar(this.timeZone);
            calendar.setTime((Date) val);
            cell.setCellValue(calendar);
        } else if (val instanceof Boolean) {
            cell.setCellValue((Boolean) val);
        } else if (val instanceof Number) {
            final double value = ((Number) val).doubleValue();
            cell.setCellValue(value);
            if (val instanceof LocalDate) {
                if (value % 1 == 0) {
                    cell.setCellStyle(this.DATE_STYLE);
                } else {
                    cell.setCellStyle(this.DATE_TIME_STYLE);
                }
            } else if (val instanceof Duration) {
                cell.setCellStyle(this.TIME_STYLE);
            }
        }
    }
    return cell;
}

From source file:org.generationcp.middleware.util.PoiUtil.java

License:Open Source License

private static void cloneCell(final Cell cNew, final Cell cOld) {
    cNew.setCellComment(cOld.getCellComment());
    cNew.setCellStyle(cOld.getCellStyle());

    switch (cNew.getCellType()) {
    case Cell.CELL_TYPE_BOOLEAN: {
        cNew.setCellValue(cOld.getBooleanCellValue());
        break;//  www  .  ja  v  a2 s  .  c  o m
    }
    case Cell.CELL_TYPE_NUMERIC: {
        cNew.setCellValue(cOld.getNumericCellValue());
        break;
    }
    case Cell.CELL_TYPE_STRING: {
        cNew.setCellValue(cOld.getStringCellValue());
        break;
    }
    case Cell.CELL_TYPE_ERROR: {
        cNew.setCellValue(cOld.getErrorCellValue());
        break;
    }
    case Cell.CELL_TYPE_FORMULA: {
        cNew.setCellFormula(cOld.getCellFormula());
        break;
    }
    }

}