Example of using Excel built in function in Apache POI

Question

All:

I am pretty new to Apache POI, I wonder if I want to use the builtin function from excel VBA, how can I do that?

For example:

In VBA, I can write something like:

Dim month as String
month = MonthName(  Month(  Sheets1.Range("C12")  )  )

How can I use that function in Apache POI(I do not wanna give that formula to a cell and evaluate it)?

Thanks,

aventurin · Accepted Answer · 2018-03-20 21:33:32Z

3

POI does not provide a VBA engine, so you cannot execute VBA with POI.

But there's a way to implement equivalent code in Java and use it with POI. The link below gives an example of how to do that.

https://poi.apache.org/spreadsheet/user-defined-functions.html

answered Mar 20, 2018 at 21:33

aventurin

2,2134 gold badges29 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Kuan Over a year ago

Thanks, but this is so sad.

Axel Richter · Accepted Answer · 2018-03-22 18:30:53Z

Apache poi cannot interpret VBA but of course it has a formula evaluator. So of course you can evaluate Excel formulas directly in apache poi code.

But the VBA function MonthName is not implemented by default. So either you do getting the date from the cell, what is clearly possible using apache poi. And then, you get the month and the month name out of that date using Java code. This is called first approach in following example.

Or you are using a implemented Excel function. TEXT for example. This is called second approach in following example.

import java.io.FileInputStream;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.*;
import org.apache.poi.ss.formula.WorkbookEvaluator;
import org.apache.poi.ss.formula.eval.*;

public class ExcelEvaluateMonthFunctions{

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

  Workbook workbook = WorkbookFactory.create(new FileInputStream("workbook.xlsx"));
  Sheet sheet = workbook.getSheet("Sheet2");

  java.util.Locale.setDefault(java.util.Locale.US);

  //first approach: 
  String monthname = null;
  java.util.Date date = sheet.getRow(11).getCell(2).getDateCellValue(); //Sheet2!C12 must contain a date

  java.time.LocalDate localDate = date.toInstant().atZone(java.time.ZoneId.systemDefault()).toLocalDate();
  monthname = localDate.getMonth().getDisplayName(java.time.format.TextStyle.FULL, java.util.Locale.getDefault());
  System.out.println(monthname);

  //second approach:
  monthname = null;
  CreationHelper helper = workbook.getCreationHelper();

  XSSFFormulaEvaluator formulaevaluator = (XSSFFormulaEvaluator)helper.createFormulaEvaluator();
  WorkbookEvaluator workbookevaluator = formulaevaluator._getWorkbookEvaluator();

  ValueEval valueeval = null;
  valueeval = workbookevaluator.evaluate("TEXT(" + sheet.getSheetName() +"!C12, \"MMMM\")", null); //Sheet2!C12 must contain a date
  if (valueeval instanceof StringValueEval) {
   monthname = ((StringValueEval)valueeval).getStringValue();
  }
  System.out.println(monthname);

  workbook.close();
 }  
}

Collectives™ on Stack Overflow

Example of using Excel built in function in Apache POI

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related