java - 将Excel VBA代码集成到Java代码中，最终创建从Excel工作表单元格的下拉列表中选择多个值的功能

Question

我有一个 VBA 代码，用于从 Excel 工作表单元格的下拉列表中一次选择多个值。我想将此代码与我的 java 代码集成，这允许我们仅选择使用 Apache Poi 从 Excel 工作表单元格的下拉列表中一次选择多个值，这样在集成之后，我最终应该能够从 Excel 工作表单元格的下拉列表中一次选择多个值。

Private Sub Worksheet_Change(ByVal Target As Range)

Dim Oldvalue As String
Dim Newvalue As String
Application.EnableEvents = True
On Error GoTo Exitsub
If Target.Address = "$C$2" Then
  If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then
    GoTo Exitsub
  Else: If Target.Value = "" Then GoTo Exitsub Else
    Application.EnableEvents = False
    Newvalue = Target.Value
    Application.Undo
    Oldvalue = Target.Value
      If Oldvalue = "" Then
        Target.Value = Newvalue
      Else
        If InStr(1, Oldvalue, Newvalue) = 0 Then
            Target.Value = Oldvalue & ", " & Newvalue
      Else:
        Target.Value = Oldvalue
      End If
    End If
  End If
End If
Application.EnableEvents = True
Exitsub:
Application.EnableEvents = True
End Sub

使用 Apache Poi 从 Excel 工作表单元格的下拉列表中一次仅选择值的 Java 代码

XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper((XSSFSheet)sheet);

String[] arrayList = allowedValues.toArray(new String[allowedValues.size()]);

XSSFDataValidationConstraint dvConstraint = (XSSFDataValidationConstraint) dvHelper.createExplicitListConstraint(arrayList);

CellRangeAddressList addressList = new CellRangeAddressList(row.getRowNum() + 1, 10000, cell.getColumnIndex(), cell.getColumnIndex());

XSSFDataValidation validation = (XSSFDataValidation) dvHelper.createValidation(dvConstraint, addressList);

validation.setShowErrorBox(true);

validation.createErrorBox("ERROR MESSAGE:Invalid Data", "Please provide valid data in the drop down list.");

sheet.addValidationData(validation);

Answer 1

VBA 代码无法集成到创建数据验证的 Java 代码中。 VBA 代码在 Excel GUI 中运行，而 Java 代码仅创建 Excel > 文件，并且在任何情况下都不会与 Excel 的 GUI 交互。

您提供的VBA代码是Worksheet_Change事件的特殊代码。因此，每次当它所应用的工作表上发生某些变化时，它就会运行。因此，在有人从数据验证下拉列表中选择一个值后，它能够提供附加功能。它使用户可以为单元格一个接一个地选择多个值。

将其与 apache poi 代码结合起来可以使用一个 *.xlsm 模板，该模板将 Worksheet_Change 应用于工作表已经。然后，apache poi 可以打开该模板，添加数据验证并将其写入新的 *.xlsm 文件。

示例:

ExcelTemplate.xlsm 具有 Sub

Private Sub Worksheet_Change(ByVal Target As Range)
 Dim Oldvalue As String
 Dim Newvalue As String
 Application.EnableEvents = True
 On Error GoTo Exitsub
 If Target.Column = 1 Then
  If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then GoTo Exitsub
  If Target.Value = "" Then GoTo Exitsub
  Application.EnableEvents = False
  Newvalue = Target.Value
  Application.Undo
  Oldvalue = Target.Value
  If Oldvalue = "" Then
   Target.Value = Newvalue
  ElseIf InStr(1, Oldvalue, Newvalue) = 0 Then
   Target.Value = Oldvalue & ", " & Newvalue
  Else
   Target.Value = Oldvalue
  End If
 End If
 Application.EnableEvents = True
Exitsub:
 Application.EnableEvents = True
End Sub

应用于第一个工作表。

然后在下面的代码之后

import java.io.FileInputStream;
import java.io.FileOutputStream;
import org.apache.poi.ss.util.*;
import org.apache.poi.ss.usermodel.*;

class CreateDataValidation {

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

  String excelTemplatePath = "./ExcelTemplate.xlsm";
  String excelResultPath = "./ExcelResult.xlsm";

  Workbook workbook = WorkbookFactory.create(new FileInputStream(excelTemplatePath));

  Sheet sheet = workbook.getSheetAt(0);

  String[] optionsArray = new String[] {"Value 1", "Value 2", "Value 3"};

  int no_of_rows = 10000;

  DataValidationConstraint constraint = sheet.getDataValidationHelper().createExplicitListConstraint(optionsArray);
  CellRangeAddressList addressList = new CellRangeAddressList(0, no_of_rows, 0, 0);
  DataValidation dataValidation = sheet.getDataValidationHelper().createValidation(constraint, addressList);
  sheet.addValidationData(dataValidation);

  FileOutputStream out = new FileOutputStream(excelResultPath);
  workbook.write(out);
  out.close();
  workbook.close();
 }
}

ExcelResult.xlsm 在第一个工作表的 A1:A10001 范围内进行数据验证。由于它也将 Worksheet_Change Sub 应用于该工作表，因此只要允许运行宏代码，它就提供了附加功能。