Hello,
I am able to get the formula working through code only for first cell as per
below code... I want it to dynamically apply for the entire column.
Please suggest...
public static void main1(String[] args) {
// New Workbook.
File outputFile = new File("C:/mamatha.xls");
try {
FileOutputStream fos = new FileOutputStream(outputFile);
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.createSheet("Validation");
Row row = sheet.createRow(0);
Cell cell = row.createCell(0);
cell.setCellValue(5);
cell = row.createCell(1);
cell.setCellValue("good");
row = sheet.createRow(1);
cell = row.createCell(0);
cell.setCellValue(7);
cell = row.createCell(1);
cell.setCellValue("now");
//String formula = "IF(LEN($B$1) > $A$1, FALSE, $B$1)";
String formula = "IF(LEN($B$1:$B10) > $A$1:$A10, FALSE, $B$1:$B10)";
CellRangeAddressList addressList = new CellRangeAddressList();
addressList.addCellRangeAddress(0, 1, 3, 1);
DVConstraint constraing =
DVConstraint.createFormulaListConstraint(formula);
HSSFDataValidation dataValidation = new
HSSFDataValidation(addressList, constraing);
dataValidation.setEmptyCellAllowed(true);
dataValidation.setShowPromptBox(true);
dataValidation.setSuppressDropDownArrow(false);
dataValidation.createErrorBox("Invalid input !!", " Length of Col B
> colA ");
sheet.addValidationData(dataValidation);
workbook.write(fos);
} catch (Exception e) {
System.out.println(e);
}
}
Thanks
Mamatha
-----Original Message-----
From: Mamatha Kodigehalli Venkatesh [mailto:mamatha.venkat...@ness.com]
Sent: Tuesday, March 15, 2011 1:41 PM
To: POI Users List
Subject: Any Quick help..... Formula for Entire Column using DataValidation
-----Original Message-----
From: Mamatha Kodigehalli Venkatesh [mailto:mamatha.venkat...@ness.com]
Sent: Monday, March 14, 2011 6:12 PM
To: POI Users List
Subject: Formula for Entire Column using DataValidation
Hello,
I have been looking HSSFDataValidation and was able to run the sample below.
My requirement is I need to apply this formula for Entire column to achieve the
need like
When user enters data in col2 (B) of greater than the number specified in col1
(A) and then show the ErrorBox.
My code sample below
public static void main(String[] args) {
// New Workbook.
File outputFile = new File("C:/mamatha.xls");
try {
FileOutputStream fos = new FileOutputStream(outputFile);
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.createSheet("Validation");
String[] strFormula = new String[] { "100", "200", "300", "400",
"500" };
CellRangeAddressList addressList = new CellRangeAddressList();
addressList.addCellRangeAddress(0, 0,0, 0);
DVConstraint constraing =
DVConstraint.createExplicitListConstraint(strFormula);
HSSFDataValidation dataValidation = new
HSSFDataValidation(addressList, constraing);
dataValidation.setEmptyCellAllowed(false);
dataValidation.setShowPromptBox(true);
dataValidation.setSuppressDropDownArrow(false);
dataValidation.createErrorBox("Invalid input !", " Col B <= ColA
");
sheet.addValidationData(dataValidation);
workbook.write(fos);
} catch (Exception e) {
System.out.println(e);
}
}
Thanks
Mamatha
-----Original Message-----
From: Mark Beardsley [mailto:markbrd...@tiscali.co.uk]
Sent: Tuesday, February 08, 2011 1:49 PM
To: user@poi.apache.org
Subject: Re: Formula Support in Apache POI
The place to begin with all questions like this one is to ask how can it be
done using Excel? When you know the answer to that question, you can look to
emulate the same process using POI. Last night, I tried to do this with
Excel itself and do not believe that it is possible using just the forumla
support.
The first part is very easy as Excel contains a LEN() function which returns
the length of either the text placed between the paraentheses or the
contents of a cell. Therefore LEN(B1) where cell B1 contains the string
'Test', would return 4. The first bit of the second part is also very easy
becasue Excel efines an IF() function that looks like this - IF(Logical
Expression, Do If True, Do If False). Combining the LEN() and IF() functions
gets us so far - IF(LEN(B1) <= A1, LEN(B1, ......) - which would be entered
into cell C1 and says, simply, if the length of the contents of cell B1 is
less than or equal to the value of cell A1 then set the value of C1 to the
legth of B1 else......, and that is the hard bit. As far as I can see, there
is no way to create a Message Box using any of the available
funtions/forumlae.
I think that you will need to find an Excel forum and post a similar
question there. Leave out any mention of POI and ask, simply, how to achieve
what you are after using Excel. Macros will be the answer I suspect and, if
this is the case, then you will find macros cannot be created or edited
using POI.
Yours
Mark B
--
View this message in context:
http://apache-poi.1045710.n5.nabble.com/Formula-Support-in-Apache-POI-tp3374589p3375526.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscr...@poi.apache.org
For additional commands, e-mail: user-h...@poi.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscr...@poi.apache.org
For additional commands, e-mail: user-h...@poi.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscr...@poi.apache.org
For additional commands, e-mail: user-h...@poi.apache.org
