在Apache POI中使用Excel内置函数的示例

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?

我对Apache POI很新,我想知道我是否想要使用excel VBA的内置函数,我该怎么做?

For example:

In VBA, I can write something like:

在VBA中,我可以写一些类似于:

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)?

如何在Apache POI中使用该功能(我不想将该公式提供给单元格并对其进行评估)?

Thanks,

2 个解决方案

#1

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

POI不提供VBA引擎,因此您无法使用POI执行VBA。

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.

但是有一种方法可以在Java中实现等效代码并将其与POI一起使用。下面的链接提供了如何执行此操作的示例。

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

#2

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.

Apache poi无法解释VBA,但当然它有一个公式评估器。因此,您当然可以直接在apache poi代码中评估Excel公式。

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.

但默认情况下不实现VBA函数MonthName。所以你要么从单元格中获取日期,使用apache poi显然是可能的。然后,使用Java代码获取该日期之外的月份和月份名称。这在以下示例中称为第一种方法。

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

或者您正在使用已实现的Excel功能。以TEXT为例。这在以下示例中称为第二种方法。

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();
 }  
}

#1