Mailing-List: contact user-help@poi.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "POI Users List" <user@poi.apache.org>
Received-SPF: unknown (nike.apache.org: error in processing during lookup of
 markbrdsly@tiscali.co.uk)
Date: Fri, 23 Mar 2012 07:13:41 -0700 (PDT)
From: Mark Beardsley <markbrdsly@tiscali.co.uk>
To: user@poi.apache.org
Message-ID: <1332512021281-5589572.post@n5.nabble.com>
In-Reply-To: <1332508303511-5589416.post@n5.nabble.com>
References: <1331190707992-5546613.post@n5.nabble.com>
 <alpine.DEB.2.00.1203081115440.26022@urchin.earth.li>
 <1331215165780-5547557.post@n5.nabble.com>
 <1331297469631-5550481.post@n5.nabble.com>
 <1331309609375-5551040.post@n5.nabble.com>
 <1332338019618-5582958.post@n5.nabble.com>
 <1332342546728-5583192.post@n5.nabble.com>
 <1332415325992-5585555.post@n5.nabble.com>
 <1332419767252-5585719.post@n5.nabble.com>
 <1332508303511-5589416.post@n5.nabble.com>
Subject: Re: to obtain value of cellx.setCellFormula("SUM(I3:I20)");
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

OK. Take a look at this code;

import java.io.*;
import java.util.Random;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

/**
 *
 * @author Mark Beardsley
 */
public class EvaluatorTest {
    
    public EvaluatorTest(String filename) {
        File file = null;
        FileOutputStream fos = null;
        Workbook workbook = null;
        Sheet sheet = null;
        FormulaEvaluator evaluator = null;
        Cell formulaCell = null;
        CellValue cellValue = null;
        try {
            //
            // Does the uwser want to create a binary or OpenXML based
workbook?
            //
            if(filename.endsWith("xlsx")) {
                workbook = new XSSFWorkbook();
            }
            else {
                workbook = new HSSFWorkbook();
            }
            
            // Insert a new sheet into the workbook
            sheet = workbook.createSheet("Evaluator Test");
            
            // Call a method to populate the sheet with data. The method
returns
            // a reference to the cell that holds a formula.
            formulaCell = EvaluatorTest.buildSheet(sheet);
            
            // Get a FormulaEvaluator object for the workbook
            evaluator =
workbook.getCreationHelper().createFormulaEvaluator();
            
            // Call the evaluate() method to calculate teh cells formula and
            // return the resul encapsulated in a CellValue object
            cellValue = evaluator.evaluate(formulaCell);
            
            // Call another statit method to print out the value calculated
            // by the formula.
            EvaluatorTest.showCellsValue(cellValue);
            
            // Now change the value in one of the cells. I have simply
chosen to
            // change the value of cell A6 to 110.98
            sheet.getRow(5).getCell(0).setCellValue(110.98);

            // Get a new FormulaEvaluator for the sheet and use that to 
            // evaluate the cell which contains the formula - note that we 
            // do not have to get a new reference to the formula cell.
            evaluator =
workbook.getCreationHelper().createFormulaEvaluator();
            cellValue = evaluator.evaluate(formulaCell);
            
            // Call a static method to display the cells contents.
            EvaluatorTest.showCellsValue(cellValue);
            
            // Save the file away to disc.
            file = new File(filename);
            fos = new FileOutputStream(file);
            workbook.write(fos);
        }
        catch(IOException ioEx) {
            EvaluatorTest.printException(ioEx);
        }
        finally {
            if(fos != null) {
                try {
                    fos.close();
                    fos = null;
                }
                catch(IOException ioEx) {
                    EvaluatorTest.printException(ioEx);
                }
            }
        }
    }
    
    /**
     * Populates a sheet with data. This method stuffs random double values 
     * into cells A1 to A10 and a formula - SUM($A$1:$A$10) - into cell A12.
     * 
     * @param sheet A reference to a sheet from a workbook.
     * @return An instance of either HSSFCell or XSSFCell encapsulating a
     *         reference to the formula cell, cell A12 in this case.
     */
    private static Cell buildSheet(Sheet sheet) {
        Row row = null;
        Cell cell = null;
        double nextValue = 0.0D;
        Random random = new Random();
        
        // Stuff random double values into cells A1 to A10
        for(int i = 0; i < 10; i++) {
            row = sheet.createRow(i);
            cell = row.createCell(0);
            nextValue = random.nextDouble() * 10;
            cell.setCellValue(nextValue);
        }
        
        // Write a simple formula - SUM($A$1:$A$10) - into cell A12
        row = sheet.createRow(11);
        cell = row.createCell(0);
        cell.setCellFormula("SUM($A$1:$A$10)");
        return(cell);
    }
    
    /**
     * Display the cells value.
     * @param cellValue An instance of the CellValue class encapsulating
     *        information about a formula cells value.
     */
    private static void showCellsValue(CellValue cellValue) {
        switch(cellValue.getCellType()) {
            case Cell.CELL_TYPE_BOOLEAN:
                System.out.println("The cells boolean value is " + 
                    cellValue.getBooleanValue());
            case Cell.CELL_TYPE_ERROR:
                System.out.println("The cells error code is " +
                    cellValue.getErrorValue());
            case Cell.CELL_TYPE_NUMERIC:
                System.out.println("The numeric cells contents are " +
                    cellValue.getNumberValue());
                    break;
            case Cell.CELL_TYPE_STRING:
                System.out.println("The string cells contents are " +
                    cellValue.getStringValue());
        }
    }
    
    /**
     * Convenience method to simply print out details of an Exception if and
     * when it is thrown.
     * @param th An instance of a sub-class of Throwable that encapsulates
     *          information about the problem which caused the Exception to
     *          be thrown.
     */
    private static void printException(Throwable th) {
        System.out.println("Thrown: " + th.getClass().getName());
        System.out.println("Message: " + th.getMessage());
        System.out.println("Stacktrace follows:.....");
        th.printStackTrace(System.out);
    }
}

It is a stand alone example that you can compile and run. Put simply, it
will create a new workbook with a single sheet in it. Cells A1 to A10 are
populated with data - randomly generated double values - and A12 has a
formula written into it. Next, I use the FormulaEvaluator to calculate and
display what the user would see in cell A12 if they opened the workbook
using Excel (obviously, I am ignoring formatting at this stage). Next, I
change the value of one of the cells and re-calculate the formula. In both
cases, the values are displayed to System.out and you can always review the
results by opening the files it generates.

It should be possible to run the code a little like this;

new EvaluatorTest("C:/temp/Book1.xls");
and/or
new EvaluatorTest("C:/temp/Book1.xlsx");

as it works for both different file types.

Compile it and run the code and, if you have any questions, just let me
know.

--
View this message in context: http://apache-poi.1045710.n5.nabble.com/to-obtain-value-of-cellx-setCellFormula-SUM-I3-I20-tp5546613p5589572.html
Sent from the POI - User mailing list archive at Nabble.com.

---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org