Ngupta
Ngupta

Reputation: 349

How to get a cell's name in Excel in java using POI?

I am trying to learn POI and I have a sample code.

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.CellReference;
import org.apache.poi.ss.usermodel.CellValue;
import org.apache.poi.ss.usermodel.FormulaEvaluator;
import org.apache.poi.ss.usermodel.Row;

public class ExpressionExample {

    public static void main(String[] args) throws Exception {

        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet("Sample sheet");
        Row row = sheet.createRow(0);
        FormulaEvaluator evaluator = workbook.getCreationHelper()
                .createFormulaEvaluator();
        HSSFCell formulaCell = (HSSFCell) row.createCell(6);
        HSSFCell c1 = (HSSFCell) row.createCell(1);
        HSSFCell c2 = (HSSFCell) row.createCell(2);
        c1.setCellValue(5);
        c2.setCellValue(10);
        CellReference cr1 = new CellReference(0 , 1); 

        String line = "if(A1>A2, \"LAR\" , if(5>10, \"AB\" , \"CD\"))";
        formulaCell.setCellFormula(line);

        CellValue cellValue = evaluator.evaluate(formulaCell);
        System.out.println(cellValue.getStringValue());
    }

}

now in my formula I have applied values of cells A1 and A2, but I want to give this value based on my code and cells should be 'C1 and c2. any Idea how to do this ?

It would be great help.

Upvotes: 1

Views: 1771

Answers (1)

TheOriginal
TheOriginal

Reputation: 50

I'm not sure if I have picked up your question correctly but here goes...

If you are wanting to use the values to which you are setting into the cells c1 and c2 (in this case '5' & '10') in your formula, you can create 2 int objects to hold these values:

int c1Value = 5;
int c2Value = 10;

You can then use these objects to set the cell values and also include them in your formula:

c1.setCellValue(c1Value); 
c2.setCellValue(c2Value);

String line = "if(" + c1Value + ">" + c2Value + ", \"LAR\" , if(5>10, \"AB\" , \"CD\"))";

Also note, in your code c1 refers to the cell A2 in Excel, and c2 refers to cell A3. Both rows and columns start at 0 in Apache.

Is this what you are trying to do?

Upvotes: 1

Related Questions