I am setting the formula but when i evaluate it .Some garbage value is displayed. for example Date is "20170801" then it should display "8/1/2017" but it is not instead it is displaying a number "42948" . When i am using the dragFormula methods to drag a certain formula from a certain cell address upto a certain point then date is evaluating correctly in sheet. I need help in applying the date formula.
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.LinkedList;
import java.util.List;
import org.apache.poi.hssf.usermodel.HSSFEvaluationWorkbook;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.formula.FormulaParser;
import org.apache.poi.ss.formula.FormulaRenderer;
import org.apache.poi.ss.formula.FormulaType;
import org.apache.poi.ss.formula.ptg.AreaPtg;
import org.apache.poi.ss.formula.ptg.Ptg;
import org.apache.poi.ss.formula.ptg.RefPtgBase;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.FormulaEvaluator;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellReference;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
/**
*
* @author Agent-47
*/
public class Formula {
Workbook workbook ;
final static int VERTICAL_UP = -1;
final static int VERTICAL_DOWN = 1;
final static int HORIZONTALLY_RIGHT = 2;
final static int HORIZONTALLY_LEFT = -2;
public static void main(String[] arguments) throws IOException{
String filePath = "C:\\Users\\Agent-47\\Desktop\\Book1.xls";
FileInputStream fis = new FileInputStream(filePath);
Formula obj1 = new Formula();
Workbook book = obj1.createWorkbook(fis, filePath);
Sheet sheet = book.getSheetAt(0);
//obj1.dragFormulaVeritacally(sheet, "B1", 6,Formula.VERTICAL_DOWN);
String formula = "DATE(LEFT(A1,4),LEFT(RIGHT(A1,4),2),RIGHT(A1,2))";
obj1.applyFormula(sheet, formula, "A1");
book.write(new FileOutputStream(new File(filePath)));
}
public Workbook createWorkbook(FileInputStream fis,String filePath) throws IOException{
Workbook workbook = null;
if(filePath.endsWith(".xls")){
workbook = new HSSFWorkbook(fis);
}
else if(filePath.endsWith(".xlsx")){
workbook = new XSSFWorkbook(fis);
}
return workbook;
}
public void dragFormulaVeritacally(Sheet sheet,String cellAddress,int uptoRow,int direction){
if(sheet==null){
System.out.println("Sheet is null");
return;
}
CellReference cellRefernce = new CellReference(cellAddress);
int formulaRow = cellRefernce.getRow();
int formulaColumn = cellRefernce.getCol();
FormulaEvaluator eval = sheet.getWorkbook().getCreationHelper().createFormulaEvaluator();
Cell oldCell = sheet.getRow(formulaRow).getCell(formulaColumn);
if(oldCell!=null){
String formula ;
if(oldCell.getCellTypeEnum()== CellType.FORMULA){
formula = oldCell.getCellFormula();
try{
eval.evaluateInCell(oldCell);
}catch(Exception e){
System.out.println("This cell has issues at row : "+oldCell.getRowIndex()+" column index : "+oldCell.getColumnIndex());
}
if(oldCell.isPartOfArrayFormulaGroup()){
System.out.println("Formula is a part of array Group");
return;
}
}
else{
System.out.println("Cant copy cuz it is not a formula : ");
return;
}
eval.evaluateInCell(oldCell);
//boolean forCheck = true;
for(int i=formulaRow;;){
if(formulaRow+1<uptoRow){
if(i>=uptoRow-1)
break;
else
i++;
}
else{
if(i<=uptoRow-1)
break;
else
i--;
}
System.out.println(" i : "+i);
Row newRow = sheet.getRow(i);
if(newRow==null)
newRow = sheet.createRow(i);
Cell newCell = newRow.getCell(formulaColumn);
if(newCell==null)
newCell = newRow.createCell(formulaColumn);
try{
if(direction==Formula.VERTICAL_DOWN)
formula=copyFormula(sheet,formula,newCell,1,0);
else if(direction==Formula.VERTICAL_UP)
formula=copyFormula(sheet,formula,newCell,-1,0);
eval.evaluateInCell(newCell);
}catch(Exception e){
System.out.println("This cell has issues at row : "+newCell.getRowIndex()+" column index : "+newCell.getColumnIndex());
return;
}
}
}
}
public void dragFormulaHorizontally(Sheet sheet,String cellAddress,String uptoColumn,int direction){
if(sheet==null){
System.out.println("Sheet is null");
return;
}
CellReference cellRefernce = new CellReference(cellAddress);
CellReference uptoColumnReference = new CellReference(uptoColumn);
int uptoColumnIndex = uptoColumnReference.getCol();
int formulaRow = cellRefernce.getRow();
int formulaColumn = cellRefernce.getCol();
FormulaEvaluator eval = sheet.getWorkbook().getCreationHelper().createFormulaEvaluator();
Cell oldCell = sheet.getRow(formulaRow).getCell(formulaColumn);
if(oldCell!=null){
String formula;
if(oldCell.getCellTypeEnum()== CellType.FORMULA){
formula = oldCell.getCellFormula();
try{
eval.evaluateInCell(oldCell);
}catch(Exception e){
System.out.println("This cell has issues at row : "+oldCell.getRowIndex()+" column index : "+oldCell.getColumnIndex());
}
if(oldCell.isPartOfArrayFormulaGroup()){
System.out.println("Formula is a part of array Group");
return;
}
}
else{
System.out.println("Cant copy cuz it is not a formula : ");
return;
}
//boolean forCheck = true;
for(int i=formulaColumn;;){
if(formulaRow+1<uptoColumnIndex){
if(i>=uptoColumnIndex)
break;
else
i++;
}
else{
if(i<=uptoColumnIndex)
break;
else
i--;
}
Row newRow = sheet.getRow(formulaRow);
if(newRow==null)
newRow = sheet.createRow(formulaRow);
Cell newCell = newRow.getCell(i);
if(newCell==null)
newCell = newRow.createCell(i);
try{
if(direction==Formula.HORIZONTALLY_RIGHT)
formula=copyFormula(sheet,formula,newCell,0,1);
else if(direction==Formula.HORIZONTALLY_LEFT)
formula=copyFormula(sheet,formula,newCell,0,-1);
eval.evaluateInCell(newCell);
}catch(Exception e){
System.out.println("This cell has issues at row : "+newCell.getRowIndex()+" at column : "+newCell.getColumnIndex());
continue;
}
}
}
}
public String copyFormula(Sheet sheet,String formula,Cell newCell,int incRow,int incColumn){
HSSFEvaluationWorkbook workbookEval = HSSFEvaluationWorkbook.create((HSSFWorkbook)sheet.getWorkbook());
Ptg[] ptgs = FormulaParser.parse(formula, workbookEval, FormulaType.CELL, sheet.getWorkbook().getSheetIndex(sheet));
for(int i=0;i<ptgs.length;i++){
if(ptgs[i] instanceof RefPtgBase){
RefPtgBase ref = (RefPtgBase)ptgs[i];
System.out.println("Ref Ptg");
if(ref.isRowRelative())
ref.setRow(ref.getRow()+incRow);
if(ref.isColRelative())
ref.setColumn(ref.getColumn()+incColumn);
}
else if(ptgs[i] instanceof AreaPtg){
AreaPtg area = (AreaPtg)ptgs[i];
System.out.println("Area Ptg");
if(area.isFirstColRelative())
area.setFirstColumn(area.getFirstColumn()+incColumn);
if(area.isFirstRowRelative())
area.setFirstRow(area.getFirstRow()+incRow);
if(area.isLastColRelative())
area.setLastColumn(area.getLastColumn()+incColumn);
if(area.isLastRowRelative())
area.setLastRow(area.getLastRow()+incRow);
}
formula = FormulaRenderer.toFormulaString(workbookEval, ptgs);
newCell.setCellFormula(formula);
}
return formula;
}
// apply formula at single cell
public void applyFormula(Sheet sheet,String formula,String atColumnAddress){
//List<Cell> cellValues = new LinkedList<>();
FormulaEvaluator eval = sheet.getWorkbook().getCreationHelper().createFormulaEvaluator();
CellReference atColumnReference = new CellReference(atColumnAddress);
int atColumnIndex = atColumnReference.getCol();
int atRowIndex = atColumnReference.getRow();
Cell oldCell = sheet.getRow(atRowIndex).getCell(atColumnIndex);
Cell cell = sheet.createRow(sheet.getLastRowNum()+1).createCell(0);
cell.setCellType(CellType.FORMULA);
cell.setCellFormula(formula);
//copyFormula(sheet, formula, cell, 0, 0); ///---------calling the copy formula method
System.out.println("Date is : "+eval.evaluateInCell(cell));
}
}
