Data validation setting in excel file down load

[sajith_p]

Hi all

I created excel workbook using the below code by adding jxl.jar :

File out=new File("Personal_Details"System.currentTimeMillis()".xls");

WorkbookSettings wb=new WorkbookSettings();

// wb.setCellValidationDisabled(false);

wb.setSuppressWarnings(true);

WritableWorkbook copy=Workbook.createWorkbook(out,wb);

WritableSheet sheet2 = copy.createSheet("P_Det",0);

Anybody can tell me how we can set the Data Validation property of Excel using the available class in jxl.jar ??

Thanks and regards

Sajith

[Former Member · Accepted solution]

Hi Sajith,

you can use the cell features facility. In this example I'm assuming you are writing a Label, but it is the same with any other writable cell, like Number, etc..

Label label = new Label(0, 0, "Value A"); 

ArrayList validValues = new ArrayList();
validValues.add("Value A");
validValues.add("Value B");
validValues.add("Value C");
validValues.add("Value D");

WritableCellFeatures feat = new WritableCellFeatures();
feat.setDataValidationList( validValues );
label.setCellFeatures(feat);

sheet2.add(label);

There are other kind of data validation, like cell range, numeric range and numeric conditions.

Look here for more details: [http://jexcelapi.sourceforge.net/resources/javadocs/current/docs/|http://jexcelapi.sourceforge.net/resources/javadocs/current/docs/]

Bests

[sajith_p · Accepted solution]

Dear Marco

I already tried to use WritableCellFeatures, but there is no setDataValidationList method available. I think jxl.jar may be old version. Do you have any link to download the latest jxl.jar file download?.

Thanks n regards

Sajith

[Former Member · Accepted solution]

Hi Sajith,

I tested the above code with the last version, and it is working.

Download an up to date .jar here

http://sourceforge.net/project/showfiles.php?group_id=79926

Bests

[sajith_p · Accepted solution]

Thanks Marco

The new Jar file solved my problems.

[sajith_p · Accepted solution]

Dear Marco

Thank you very much for the solution given. Data validation is working fine now.

I have one more issue came regarding with adding the formula in Excel sheet.

I tried to add the following formula "C2D2Sheet1!A2" which will refer a value from the cell of different sheet from the same excel file.

I tried with the following code

f = new Formula(1,15,"C2D2Sheet1!A2");

s.addCell(f);

but this is not giving the output and throwing the warning as

"Warning: Could not find named cell Sheet1 when parsing formula C2D2Sheet1!A2

in cell Sheet1!B16"

Marco, Do you have any idea about this?

Or else if anybody knows about this please give a reply.

Thanks and Regards

Sajith