3

we have a function in our java based web application where user can download an excel sheet template from the web application. Fill their data in this template and then upload the same excel sheet.

The system then reads this excel file and save this data in the database.

Below is snapshot of template file with some sample data in it.

What I want is when users download template file (template file usually just has the headers, so users know which data goes in which column), excel sheet should have drop downs for Division, Product, secondary product , Region and country. So that users do not enter any invalid values in those columns.

As well, products varies according to divisions and secondary product varies according to products. Its more like dependent drop downs.

Basically I will need to create the excel sheet using Apache POI in which users will chose values from the drop dowsn instead of typing it themselevs.

Even though we do have server side validation where we check if the values entered by users are valid or not.

The reason we wnat to do this is that e.g. some users might enter country as US, some as USA and some as United states.

The same thing goes for products etc. user may enter product as GFFX or GFFX Structuring or gffx etc.

Is it possible to do this in excel sheet using POI? If not what are the other possible solutions or wasy to make sure users know what they have to enter in each columns?

EDIT 1 :

I could created the drop downs but is it possible to created the dependent drop downs?

enter image description here

ajm
  • 12,863
  • 58
  • 163
  • 234
  • create a template of excel file before populating it with POI, in the template itself apply the restrictions you need via excel or VBA programming. – AurA Jun 20 '12 at 06:33
  • @AurA. The Division, products etc are dynamically coming from the database depending on the access user has. So drop downs will vary from user to user. – ajm Jun 20 '12 at 06:44

2 Answers2

7

I was about to suggest AurA's solution but it looks like you'll have to build the validation list at run-time indeed.

You should have a look at the POI Quick Guide, it seems they have exactly what you need:

hssf.usermodel (binary .xls format)

HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.createSheet("Data Validation");
CellRangeAddressList addressList = new CellRangeAddressList(0, 0, 0, 0);
DVConstraint dvConstraint = DVConstraint.createExplicitListConstraint(new String[]{"10", "20", "30"});
DataValidation dataValidation = new HSSFDataValidation(addressList, dvConstraint);
dataValidation.setSuppressDropDownArrow(false);
sheet.addValidationData(dataValidation);

xssf.usermodel (.xlsx format)

XSSFWorkbook workbook = new XSSFWorkbook();
XSSFSheet sheet = workbook.createSheet("Data Validation");
XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper(sheet);
XSSFDataValidationConstraint dvConstraint = (XSSFDataValidationConstraint)
dvHelper.createExplicitListConstraint(new String[]{"11", "21", "31"});
CellRangeAddressList addressList = new CellRangeAddressList(0, 0, 0, 0);
XSSFDataValidation validation = (XSSFDataValidation)dvHelper.createValidation(
dvConstraint, addressList);
validation.setShowErrorBox(true);
sheet.addValidationData(validation);
Olivier Coilland
  • 3,088
  • 16
  • 20
  • Thanks for pointing it out. But is it possible to do the dependent drop downs like we normally have on the HTML forms? – ajm Jun 20 '12 at 08:41
  • Yes you can, have a look at http://www.contextures.com/xlDataVal02.html or http://excelsemipro.com/2011/05/a-dynamic-dependent-drop-down-list-in-excel/ for example :) – Olivier Coilland Jun 20 '12 at 08:46
  • I had a look at the links you gave. BUt I will need to do the same using java and Apache POI programmatically. – ajm Jun 20 '12 at 08:57
  • Indeed, and it's possible I guess. I would recommand doing it in a template with a small test case and reverse engineer it in POI. I didn't say it wouldn't be painful ;-) – Olivier Coilland Jun 20 '12 at 09:12
0

You can get drop-down list (after clicking right mouse button) in case you've added (using POI) suggestions to the rows upper the first row that is visible to the user and should be filled (thus the rows beneath the header contain suggestions and are hgidden).

You won't get (AFAIK) category dependancy with POI or even pure excel (without VBA) drop-down list (that holds suggestions on the basis of values entered earlier).

What you can do, is to use POI to fill helper sheet with appropriate raw data and use VBA to dynamically generate drop-downs that would allow to pick a value from a list.

wilu
  • 549
  • 1
  • 12
  • 26