You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by Mamatha Kodigehalli Venkatesh <Ma...@ness.com> on 2011/02/07 18:11:50 UTC
Formula Support in Apache POI
Hello,
I need to provide Validation and enable formula support for few columns in the excel sheet.
Sample data in a excel
col1 col2 col3
6 apple 5
1> When user types in say col2, I need to automatically update the col3 with the length of the data that will be entered in col2.
eg: col2= apple then col4= 5
2> Also I need to restrict the users to enter data in col2 of length less than or equal to that of number specified in col1 and therby prompt the user that he cannot enter more data in a comment box.
Please guide me to achieve this.
Thanks
Mamatha
Workaround so far ... Formula for Entire Column using DataValidation
Posted by Mamatha Kodigehalli Venkatesh <Ma...@ness.com>.
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.Venkatesh@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.Venkatesh@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:markbrdsly@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-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Any Quick help..... Formula for Entire Column using DataValidation
Posted by Mamatha Kodigehalli Venkatesh <Ma...@ness.com>.
-----Original Message-----
From: Mamatha Kodigehalli Venkatesh [mailto:Mamatha.Venkatesh@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:markbrdsly@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-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Formula for Entire Column using DataValidation
Posted by Mamatha Kodigehalli Venkatesh <Ma...@ness.com>.
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:markbrdsly@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-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Formula Support in Apache POI
Posted by Mark Beardsley <ma...@tiscali.co.uk>.
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-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Formula Support in Apache POI
Posted by Mark Beardsley <ma...@tiscali.co.uk>.
Been habing a chat with a very experienced Excel user today and the consensus
is that you have two options, both of which use Macros and VBA sadly.
First, as it is not possible to call either a macro or create a message box
directly from an Excel function/formula, you can create your own UDF (user
defined function) and this can be included into a formula that you create.
The UDF's capabilities are limited but would include the ability to display
a message box. The problem is that you may well fall foul of POI's forumla
parser when creating the formulae to add to the worksheet; simply put, this
is because POI does not recognise the name of the formula and so might throw
an exception. I do not know this for a fact and you would have to try it by
creating a UDF and then using it in a formula. Also, as POI is unable to
create or edit macros, you would need to use Excel to create a template file
that contained the UDF and which could be opened and populated using POI.
Secondly, there are certain events that are always triggered when the user
interacts with the workbook. It would be possible to create code in one of
these events to check the change the user had made and ensure it met with
your criteria. There are three possible problems with this technique
however. Firstly, as POI cannot be used to create or edit a macro, you will
have to create a template contaiing the macro. Secondly, when the user opens
the completed workbook, they will see a message telling them the workbook
contains macros and offering them the opportunity to disable them. Itis
possible to circumvent this by signing the macro but even this option would
not be available to you if you were creating a template for the older binary
format file (.xls). Finally, you do not want the macro to check verey cell
interation and so it is typical to create a list of cells that should be
listened to. You will need to ensure this list is complete and accurate.
Yours
Mark B
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Formula-Support-in-Apache-POI-tp3374589p3376004.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org