APACHE POI - Dropdown and dependent dynamic dropdown lists for Excel from Java

UNPOPULAR OPINION! I LOVE Excel. Which is great because since I’ve started working as a software developer, I think every single project has involved at least some Excel manipulations. As I develop my backends with Java, I had to tame the APACHE POI library, a third-party API that allows to read from, create, modify and write in Excel files.

In this post, I will tell you about creating dropdown lists in Excel, and even dependent dropdown lists, which content’s dynamically changed by the choice made in another dropdown list, using Java and Apache POI.

Pour yourself a good cup of java and let’s go!

Understanding Excel

The first thing to do when you face a problem with Excel, even though you’re coding the solution in Java (or any programming language, may it be C#, Python, JavaScript, whatever…) is to understand how to solve it in Excel.

Basically, looking for tutorials on the how to in Excel, opening Excel and play with it. You first want to make sure that what you’re trying to achieve is actually possible!

Apache POI doesn’t offer methods to reproduce identically every single function that exists in Excel. But once you understand what Excel does when you press a button, you can use Apache POI to mimic its behavior and get the same result. That’s how I had to work my way to implement dependent dynamic dropdown lists.

Making a dropdown list in Excel using Java and Apache POI

First, let’s have a look at a simple dropdown list, and how to make it in Java using Apache POI.

As I said, I won’t go though the code that allows you to create or modify an Excel file in Java in this article, so let’s assume you have your basic code up and running.

Creating a sheet to hold the values for the dropdown list

First thing to do is get data to populate the dropdown list. You can normally get this data on the fly from a database, a request to an API… but it appeared to be troublesome.

When writing dropdown lists directly from large databases, the Excel file ended up being corrupted. The safest way around is to write the data you need for your dropdown list in a sheet.

For this example, I’ll call that sheet “dataSheet”. To make it easier, I stock the String in a constant variable.

final String DATA_SHEET_NAME = "my_data_sheet";

Be aware that Excel doesn’t like blank spaces in the names. To avoid any issue, use the underscore convention.

I had several dropdown lists to handle in my case and I stocked all the values in that sheet. If you don’t want the end user to be aware of it, you can simply hide it.

workbook.getSheet(DATA_SHEET_NAME).protectSheet("whatever");

• workbook is the name of a (XSSF)Workbook, previously created (code not shown in this tutorial).
• .getSheet(Str sheetName) gets a (XSSF)Sheet.
• .protectSheet(Str password) sets a password on the sheet, preventing any access to it.

Now actually hiding the sheet:

workbook.setSheetVisibility(workbook.getSheetIndex(DATA_SHEET_NAME), SheetVisibility.VERY_HIDDEN);

• .setSheetHidden takes the sheet index as first parameter, that’s why I use the .getSheetIndex method, passing the sheet’s name as parameter. The second parameter is a static value of type SheetVisibility.

Also, be aware that the sheet you want to hide cannot be the active sheet.

Reference the values area

When your data is written in your data sheet, you’ll want to use named areas in order to easily access it.

I created a String that allows to store an absolute reference to a cell area. POI gives you a method to get cells references, but somehow I found out the references were relative, therefore incorrect in my use case.

Making a cell’s reference absolute (fixed) is done in Excel by adding a “$” sign before its column and row references. You can also make only the row or column fixed. This is really useful when you make dynamic formulas in Excel! So let’s say a cell is in row B, column 52, its reference is B52, its absolute reference is $B$52, but you could only fix its column ($B52) or its row (B$52).

Now, we populated our list with several values, so we want a cell range. A cell range is referenced by firstCellReference:lastCellReference, where the first cell is in upper left corner and last cell lower right corner.

Of course, all your data could be on a single line, in which case the row won’t change in first and last cell, or in the same column, in which case the column letter won’t change. The layout will depend on your use case.

Then, if your data is on a different sheet than the one you will write you dropdown list in, you need the reference the sheet’s name. In that case, the name is followed by a “!”, then the range of cells.

In the end, it would look something like this: my_data_sheet!$B$4:$E$6

So, back to Java my friend. I created a String that holds that cell range reference like this :

String reference = dataSheet.getSheetName() 
        + "!$" + CellReference.convertNumToColString(firstCell.getColumnIndex()) 
        + "$" + (firstCell.getRowIndex() + 1) 
        + ":$" + CellReference.convertNumToColString(row.getCell(row.getLastCellNum() - 1).getColumnIndex())
        + "$" + (row.getCell(row.getLastCellNum() - 1).getRowIndex() + 1);

getSheetName gets the name of the sheet. I could have used my constant here, but I wanted to show that method too because it can be useful in many situations.

POI offers a static method to convert a cell number to the column letter, which is useful here because we need to get the reference as it would appear in Excel. Something I don’t like at all with POI is that sometimes they use the word “number” in their methods to talk about the index (starting with 0), something to talk about the actual “human” number (starting from 1). You will probably loose some hair on those indexes/numbers issues with this library, just be aware.

Anyway, this is useful because you can’t just use a conversion from integer to char to get the column letter, because as soon as Excel reaches the ‘Z’ column, it goes to AA, AB… and it can jump to 3 letters, and so on. You don’t want to hardcode that.

That method converts the column index in a letter, so for example column 0 becomes A. So far so great. Then you get the row number, but careful!, it still gives you the index, while you want the actual number (an Excel sheet first row is 1, not 0), so that’s why I add +1 to the value.

You also notice that there are very convenient methods to retrieve last cell of a row, or last row of a sheet. I’ll let you discover all that in the official Apache POI documentation if it feels new. This post is already so long I cannot cover more details. You might want to use a direct reference to the cell if you know it. In my case, the size of the rows/columns wasn’t fixed so I had to work with these.

Almost there, but not yet! Now you want to use that wonderful formula you created with your bear hands. Almost. We will actually create the named area now:

Name namedArea = workbook.createName();
namedArea.setNameName("myDataArea");
namedArea.setRefersToFormula(reference);

Yes, there’s really a method that’s called setNameName. You can create dynamic names (with counters, using variables in your code…) but it has to be accessible afterwards, it cannot be duplicated within the same workbook, and it cannot contain ” ” or “:”.

Finally creating that simple dropdown list!

Now you want to set the cell range where the dropdown list will appear. I use a single cell to place my dropdown list. So basically, my first two parameters are the same (the cell row index), as are the last two (the cell column index).

CellRangeAddressList addressList = new CellRangeAddressList(cellToWriteIn.getRowIndex(), cellToWriteIn.getRowIndex(), cellToWriteIn.getColumnIndex(), cellToWriteIn.getColumnIndex());

Now, we’re using some helper functions from POI that will mock the Data Validation logic in Excel. Go have a look at it in Excel to get how it works. Be super careful, we’re now working with the sheet were the dropdown list will appear!

DataValidationHelper dvHelper = sheetToWriteIn.getDataValidationHelper();
DataValidationConstraint dvConstraint = dvHelper.createFormulaListConstraint("myDataArea");
DataValidation validation = dvHelper.createValidation(dvConstraint, addressList);

So here, we’ve reused the name of the named area, exactly as it was created two steps before. The constraint is the values against which the input values would be tested. Here we will make a dropdown list, but you could use this to allow users to input values manually and check them. The data validation adds the constraint to the cell (range) we defined at the previous step.

Making the data validation a dropdown list takes this line of code:

validation.setSuppressDropDownArrow(true);

Finally, you add all this to the sheet, you press play, and voilà!

sheetToWriteIn.addValidationData(validation);

A dependent dropdown list which content changes dynamically according to choice made in another dropdown list

Now we have a dropdown list, which will influence the content of a second dropdown list. For better understanding, I will get a simple example, with fruit and vegetables. I take this opportunity to raise a debate on whether tomatoes and avocados are fruit or vegetables.

So let’s say we just created a dropdown list with two choices: fruit or vegetable. Now we want the user to make a choice in the list of fruit or veggies, according to their first choice.

First, we want to make named areas for each set of data. This means we will make a named area from tomato to strawberry, and another from lettuce to beans. The name of the area from tomato to strawberry has to be “Fruit”, and the other “Vegetable”, which are also the exact same value that will be used for the first dropdown list!

You need to make a CellRangeAddressList for the cell (range) you want to write your dependent dropdown list in, just has we did before for a simple dropdown list.

The only major difference is when you make you DataValidationConstraint dvConstraint: we will use the INDIRECT function from Excel, to refer to the first dropdown list. The INDIRECT function takes a cell reference to the main dropdown list. This is a formula in Excel for which POI doesn’t offer an existing method, so we basically write the formula ourselves:

DataValidationConstraint dvConstraint = dvHelper.createFormulaListConstraint(
"INDIRECT(" 
+ CellReference.convertNumToColString(mainDropdownCell.getColumnIndex() + i) 
+ (mainDropdownCell.getRowIndex()+1) + ")" 
);

The mainDropdownCell is the the (XSSF)Cell where the first dropdown was created (where the Fruit/Vegetable dropdown would appear). Here I use an iteration value (i) because my code is in a for-loop, because I create dynamic ranges referring to a correspondent dropdown list each (through INDIRECT function).

That’s it! Thanks to that formula and the named area we created right before, the dropdown list we just created will adjust according to the choice made in the first dropdown.

Debugging Apache POI dropdown lists in Java

First, be extra careful about the infamous NPE (nullPointerException, Java developers’ best friend). Because you see the grid of the Excel sheet when you open a new Excel document doesn’t mean all those cells exist! A cell that was not CREATED doesn’t exist, same goes for rows. And if it doesn’t exist, it’s NULL. So yeah, the first times, you will probably face a lot of null pointers. Learning by mistake…

Another error I encountered was “The Source currently evaluates to an error. Do you want to continue?“. It means you’re trying to make dependent dropdown lists but the values in the first dropdown don’t evaluate to the names of the areas of values. Names have to be exactly the same.

To check it, or to debug other dropdown lists issues, you’ll want to open the name manager in the Excel file (ctrl + F3). Here you can see all the named areas and cells, and the values they refer to. REALLY USEFUL because maybe you just have a problem of index in your code and there’s not need to send your laptop to the neighbor’s garden while swearing.

Anyway, the best way to debug such a case is to reproduce it in Excel to really understand what happens, checking the formula bar and menus like the name manager. That’s how I understood a lot how to code this feature.