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 back-ends with Java, I had to meet and 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.

I’m not going through the basics of Apache POI because they pretty much sticked to the usual Java WOW and I think their documentation is sufficient (but hey if you beg me to, I might make the effort). When I had to dive into the dropdown lists though, it was pretty painful for me. Especially the dependent dropdown lists! As usual, I found it hard to find clear explanations and I spent too much time testing and trying, half blindly. So here’s the human readable, understandable, guide to master the Excel dropdown lists with POI. Pour yourself a good cup of java and let’s go!

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. So, basically, looking for tutorials on the how to in Excel, opening Excel and play with it. Apache POI, as any other Microsoft Office helper API I guess, doesn’t offer methods to reproduce identically every single function (c’mon, look at Excel’s toolbar, every suboption, and so on). 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.

Content:

• Creating a sheet to hold the values for the dropdown list(s)
• Reference the values area
• Finally creating that simple dropdown list!
• Taking it to the next level: a dependent dropdown list which content changes according to choice made in another dropdown list, dynamically
• Debugging Apache POI dropdown list in Java

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(s)

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, while working on the first project I encountered this request, I faced an issue with it, which is apparently known as I quickly found other fellas on Stack Overflow having the same trouble. While writing dropdown lists directly from large databases, the Excel file would end up being corrupted. So the safest way around is to write the data you need for your dropdown list(s) 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
Here we want to retrieve that sheet were we stocked the raw data, so I use the constant I created in previous code line.
.protectSheet(Str password) sets a password on the sheet, preventing any access to it
As you probably created that sheet only for data manipulation from your Java code, you won’t need to use it afterwards. You can probably forget the password.

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 cannot be the active sheet in order to be hidden. Usually, you won’t have to think about it, because you’ll probably create the data sheet after other sheets, which means the focus will not be on it. But if you’re playing around with only one sheet, change its activeness with the setActiveSheet method.

According to you very problem, you maybe don’t have to create that sheet, as you might read already existing data from the file to make your dropdown list.

Reference the values area

When your data is written in your data sheet (make it clean, using a logic organisation), 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. Yes, while playing with Excel, you might think a lot about matrices. Good old memories right?

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

Yeah, I think Apache POI API is not the simplest library to use, I hope you like method chaining…

So let’s split it out, because your use case might be different from mine: 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.

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 natural number (starting from 1). You will probably loose some hair on those indexes/numbers issues with this particular library, just be prepared. 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 +1 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. Be careful with the name you choose because we will use it afterwards. 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);

English is not my mother tongue, I would really appreciate if someone can explain to me why we have to put “true” to show a dropdown list when the method is called setSUPPRESSDropDownArrow. But that’s just it, even though it’s a bit funny…

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

sheetToWriteIn.addValidationData(validation);

Taking it to the next level: a dependent dropdown list which content changes according to choice made in another dropdown list, dynamically

That is a long section title, right? So, now let’s roll up our sleeves for the serious stuff (unless you just wanted a simple dropdown, in which case you might still consider keeping this page in your fav bar, you know, for later).

So we already 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 have to 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, so were 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 (through INDIRECT function) to a correspondent first dropdown list each. I you don’t iterate, you don’t need that ‘+ i’ part. You might not need this information now, but just be aware it’s possible to automate things, always try not to hardcode information for reusability and maintenance.

That’s it! Thanks to that formula and the named area we created right before (that’s why it’s important to understand all the steps, because they’re tightly linked!), the dropdown list we just created will adjust according to the choice made in the first dropdown.

Of course, don’t forget to write the rest of the code. I didn’t want to give you a full code snippet because you would just copy paste without trying to understand (I mean, most of you would). If you did read carefully, you’re able to put the pieces together to create your own code.

Debugging Apache POI dropdown lists in Java

First, be extra careful about el famoso 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, in the first times, you will probably face a lot of null pointers. Learning by mistake…

Use a try/catch block to allow your code to write as much information as possible in the Excel file, because it will help you find the problem. Yes, we will search the Excel file for answers, don’t be shy, it won’t hurt. Maybe your file opens just fine but doesn’t really do what you expected. Here are some steps to consider.

First, it you get an error message “The Source currently evaluates to an error. Do you want to continue?“, it means you’re probably trying to make dependent dropdown lists but the values in the first dropdown don’t evaluate to the names of the areas of values. Which mean you didn’t read my tutorial carefully, shame on you! 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.

I hope this tutorial was useful! Let me know how I could improve or what you want to learn next!