Integrate Excel VBA code to Java code to finally create the feature of selecting multiple values from the drop down list in a cell of an excel sheet

Question

I have a VBA code for selecting multiple values at once from the drop-down list in a cell of an excel sheet. I want to integrate this code with my java code which allows us to select only value at a time from the drop-down list in a cell of an excel sheet using Apache Poi, so that after integration I should finally be able to select multiple values at once from the drop-down list in a cell of an excel sheet.


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

Java code for selecting only value at a time from the drop-down list in a cell of an excel sheet using Apache Poi

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

Axel Richter Axel Richter · Accepted Answer · 2019-11-27T07:58:48

The VBA code cannot be integrated to the Javacode which creates the data validation. The VBA code runs within the Excel GUI while the Java code only creates the Excel file and does not interact with Excel's GUI in any case.

Your provided VBA code is special code for Worksheet_Change event. So it runs every time when something changes on the worksheet it is applied to. So it is able providing additional functionality after someone has selected a value from datavalidation dropdown. It gives the user the possibility to select multiple values, one after the other, for the cell.

What one could do to combinate that with apache poi code is having a *.xlsm template which has the Worksheet_Change applied to a worksheet already. Then apache poi can open that template, add data validation and write that to a new *.xlsm file.

Example:

ExcelTemplate.xlsm has the 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

applied to first worksheet.

Then after following code

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

the ExcelResult.xlsm has the datavalidation in first sheet in range A1:A10001. And since it has the Worksheet_Change Sub applied to that sheet too, it provides the additional functionality as long as running macro code will be allowed.

Integrate Excel VBA code to Java code to finally create the feature of selecting multiple values from the drop down list in a cell of an excel sheet

1 Answers