CODEWITHSUNDEEP
excelapache-poi
Tony Eastwood
Tony Eastwood

Reputation: 853

Setting up range validation between worksheets in POI

Our program exports some database data as Excel. The users chooses either: 1) One workbook and many tabs, one for each original table. 2) Multiple workbooks (files) each with only one tab.

I add validation code for foreign key/primary key references exactly as explained in standard POI documentation (https://poi.apache.org/spreadsheet/quick-guide.html#Validation). The magic formula is similar to 'pkTable'!$A$3:$A$6.

I am trying to reproduce this behavior across many workbooks - that is validate one cell against a range of values in another workbook. We use both HSSF and XSSF but XSSF is preferred.

If I pass the string *file:/t:/Excel/pkTable.xlsx#'pkTable'!$A$3:$A$*6 to XSSFDataValidationHelper.createCustomConstraint() POI throws no exceptions. But on opening in Excel we see the message 'We found a problem with some content...'

I've also tried solving the problem indirectly by creating named ranges. Then all we need to do is indicate that a named range is in a different workbook. I've tried two approaches:

Attempt to qualify the named range directly (i.e. in the call namedRange.setRefersToFormula(). However, If I pass in file:/t:/Excel/pkTable.xlsx#'pkTable'!$A$3:$A$6 rather than 'pkTable'!$A$3:$A$6 we get a POI expection 'org.apache.poi.ss.formula.FormulaParseException: Specified named range 'file' does not exist in the current workbook.'

A second approach is to use namedRange.setRefersToFormula("pkTable'!$A$3:$A$6") and attempting to qualify the call to the named range - that is call XSSFDataValidationHelper.createCustomConstraint("file:/t:/Excel/pkTable.xlsx#Rg_pkTable" ) (where 'Rg_pkTable' is the name of my range ) POI is happy - but Excel tells me 'We found a problem with some content'

This is frustrating because my experiments show that: theCell.setHyperlink("file:/t:/Excel/pkTable.xlsx#'pkTable'!A1") will create a good working cross-workbook hyper link. (Note: that same formula as it appears in Excel Visual Basic will have expanded the # character supplied via Java.)

For a working hyperlink one must create the hyperlink using CreationHelper.createHyperlink(Hyperlink.LINK_URL) in place of CreationHelper.createHyperlink(Hyperlink.LINK_DOCUMENT); This suggests that for the range check case internally POI assumes LINK_DOCUMENT syntax even if the passed string is a LINK_URL. Does anyone know how to defeat this?

In general all these clever techniques (hyper links/range checks) appear to be finally inserted as Excel formulas. On that basis every technique available in native Excel Visual Basic should be available via POI

Upvotes: 1

Views: 2584

Answers (2)

Tony Eastwood
Tony Eastwood

Reputation: 853

Thank you for your hard work in creating this example. Much appreciated - it is quite amazing what one can do.

I actually did it by 'cheating' and creating copies of the data in each workbook. If the length of the comma separated list of all the possible pk values is less than 256 then I create an explicit string list. Otherwise, I make reference to column on a hidden tab containing the copied values. Because the header of my fk column contains a working hyperlink to the other workbook the user-experience is good. The users finds that values are constrained on modification – but they can also click and instantly navigate to a list of values in their 'expected place' on another workbook.

See Is there a max number items while generating drop down list in Excel using Apache POI?

The big drawback in my solution is that my user cannot add or modify primary data (For example: add a new colour or country code). As it happens that is not a use case I need to support. (I'm just lucky) In addition, for me, the big plus in using hidden or explicit reference data is that I can support fk enforcement of compound natural keys – in some cases the foreign key reference is actually a string concatenation of a number of pk values ( I didn't design this database!!)

Upvotes: 0

Axel Richter
Axel Richter

Reputation: 61945

To make Excel data validation drop down list work from different workbook, multiple things must be noted. All the used list ranges must be named ranges. But the most annoying thing is Users will have to open both the workbooks -- the one with the drop down lists, and the workbook with the original source list.

So the data validation constraint cannot be a reference to a file path. It must be a reference to an object in random access memory. And if it is a reference to a named range, then this named range also cannot be a reference to a file path. It also must must be a reference to an object in random access memory.

But it is possible also using apache poi:

import java.io.*;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.*;

import org.apache.poi.ss.util.CellRangeAddressList;

class DataValidationExternalWorkbook {

 public static void main(String[] args) {
  try {

   Workbook workbook = new XSSFWorkbook();  
   Sheet sheet = workbook.createSheet("Sheet1");
   for (int i = 0; i < 5; i++) {
    sheet.createRow(i).createCell(0).setCellValue("ListItem " + i);
   }

   Name namedCell = workbook.createName();
   namedCell.setNameName("SourceList");
   String reference = "Sheet1!$A$1:$A$5";
   namedCell.setRefersToFormula(reference);

   Cell cell = sheet.getRow(0).createCell(1);
   Hyperlink fileLink = workbook.getCreationHelper().createHyperlink(Hyperlink.LINK_FILE);
   fileLink.setAddress("Datavalidation.xlsx#Sheet1!B2");
   cell.setCellValue("Click here to Datavalidation.xlsx, Sheet1, B2");
   cell.setHyperlink(fileLink);

   FileOutputStream fileOut = new FileOutputStream("/home/axel/Dokumente/DatavalidationSource.xlsx");
   workbook.write(fileOut);
   fileOut.close();

   workbook = new XSSFWorkbook();  
   sheet = workbook.createSheet("Sheet1");

   namedCell = workbook.createName();
   namedCell.setNameName("DVList");
   reference = "DatavalidationSource.xlsx!SourceList";
   namedCell.setRefersToFormula(reference);

   DataValidationHelper dvHelper = sheet.getDataValidationHelper();
   DataValidationConstraint dvConstraint = dvHelper.createFormulaListConstraint("DVList");
   CellRangeAddressList addressList = new CellRangeAddressList(1, 1, 1, 1);            
   DataValidation validation = dvHelper.createValidation(dvConstraint, addressList);

   sheet.addValidationData(validation);

   cell = sheet.createRow(0).createCell(1);
   fileLink = workbook.getCreationHelper().createHyperlink(Hyperlink.LINK_FILE);
   fileLink.setAddress("DatavalidationSource.xlsx#Sheet1!B1");
   cell.setCellValue("Open DatavalidationSource.xlsx to make the list in B2 work.");
   cell.setHyperlink(fileLink);

   cell = sheet.getRow(0).createCell(10);
   fileLink = workbook.getCreationHelper().createHyperlink(Hyperlink.LINK_DOCUMENT);
   fileLink.setAddress("Sheet2!B2:D4");
   cell.setCellValue("goto Sheet2!B2:D4");
   cell.setHyperlink(fileLink);

   sheet = workbook.createSheet("Sheet2");

   fileOut = new FileOutputStream("/home/axel/Dokumente/Datavalidation.xlsx");
   workbook.write(fileOut);
   fileOut.close();

  } catch (FileNotFoundException fnfex) {
  } catch (IOException ioex) {
  }
 }
}

The paths of all external references are relative. So in this example both workbooks needs to be created in the same directory.

You should open DatavalidationSource.xlsx first. There is the list named SourceList in A1:A5 and there is a link to Datavalidation.xlsx in B1. In Datavalidation.xlsx the dropdown is in Sheet1!B2. The data validation list refers to the name DVList which refers to DatavalidationSource.xlsx!SourceList.

In Datavalidation.xlsx#Sheet1!K1 is a link using Hyperlink.LINK_DOCUMENT.

Upvotes: 4

Related Questions