Excel formula on the current value/cell POI

Question

I'm working on POI application in order to manipulate excel file.

In fact the user is giving a formula and files and I am applying the formula on a output file.The formula have to modify the value of the column on the cell.

For example on the columns B, I want to apply on all my column a formula. The user is giving to me LEFT(x,2), and I have to apply this to all the columns. (x is defining all the columns)

But when I am applying the formula I got the formula as a String. I try to pass the cell value at the formula but of course it is not working...

I think I should copy all my data into a another excel file, work on it and copy paste them in the output file or their is another way ?

Regards,

Code:

for (int i = 0; i < cell[0].length; i++){ //Checking the header
    for (int j = 0; j < ruleArray.length; j++){  //Checking the Header of the array with the rule to apply
        if (cell[0][i].toString().equals(ruleArray[j][0])){  //Comparing 
        String testF = ruleArray[j][1];
            if (testF.contains("X") || testF.contains("x")){ //Replacing X with value for the formula
                for (int k = 0; k < cell.length; k++){
                indexT = cell[0][i].getColumnIndex(); 
                indexC = cell[k][i].getRowIndex()+1;
                String colLetter = CellReference.convertNumToColString(indexT);

                formula = testF.replace("x", colLetter+indexC);
                cell[k][i].setCellType(CellType.FORMULA);      
                cell[k][i].setCellFormula(formula);
            }
        }
    }
}

}

Can you share what code you are trying? It is possible but need to see what you are trying now. — flopcoder

flopcoder flopcoder · Accepted Answer · 2018-11-26T17:22:04

I am not rewrite your code but you can take a help from this. Create a excel file with column City and Formula and then run this code. I have attached some snapshot of excel file. I thin kit will help you. LEFT(X,2) only parse first two char from a string

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellReference;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;

public class TestProblem

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


        InputStream inp = null;
        inp = new FileInputStream("E:\\Projects\\PoiAdvanceExample\\stackProblem.xlsx");

        Workbook wb = WorkbookFactory.create(inp);
        Sheet sheet = wb.getSheetAt(0);

        int rowsCount = sheet.getLastRowNum();
        int columnCount = sheet.getRow(0).getLastCellNum();
        String[][] inputData = new String[rowsCount+1][columnCount];

        for (int i = 0; i <= rowsCount; i++) {
            Row row = sheet.getRow(i);
            int colCounts = row.getLastCellNum();
            for (int j = 0; j < colCounts; j++) {
                Cell cell = row.getCell(j);
                if(cell.getCellType() == CellType.NUMERIC) {
                    inputData[i][j] = Double.toString(cell.getNumericCellValue());
                }
                if(cell.getCellType() == CellType.FORMULA) {
                    inputData[i][j] = cell.getCellFormula();
                }
                if(cell.getCellType() == CellType.STRING) {
                    inputData[i][j] = cell.getStringCellValue();
                }
            }
        }

       writeData(inputData);

    }

    private static void writeData(String[][] inputData) throws IOException {

        Workbook workbook = new XSSFWorkbook();
        Sheet sheet = workbook.createSheet();

        int r = 0;
        for (String[] dataRow : inputData) {
            Row row = sheet.createRow(r++);
            int column = 0;
            for (String dataCell : dataRow) {
                Cell cell = row.createCell(column++);
                if (r == 1 || column == 1) cell.setCellValue(dataCell);

                else if (column == 2) {
                    CellReference cellReference = new CellReference(cell);
                    String thisR = cellReference.getCellRefParts()[1];
                    cell.setCellFormula("LEFT(A" + thisR + ",2)");
                }
            }
        }


        FileOutputStream fileOut = new FileOutputStream("stackProblem.xlsx");
        workbook.write(fileOut);
        workbook.close();
    }
}

Excel file before run will be like this.

Excel file after run this code will be like this.

Excel formula on the current value/cell POI

1 Answers