Mamatha Kv

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.

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...
import java.io.File; import java.io.FileOutputStream; import java.util.ArrayList; import org.apache.poi.hssf.usermodel.DVConstraint; import org.apache.poi.hssf.usermodel.HSSFDataValidation; import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.hssf.util.CellRangeAddress; import org.apache.poi.hssf.util.CellRangeAddressList; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.Row; public class TestThis{ 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

I agree they are semantically equivalent.
But in our case, the input xml files given by the clients for translations. We translate the node content and write back the xml file using DOM. In this process the empty nodes are replaced as <tag/>.

So when the clients view the xmls they see the difference, the way these tags are written back.

Is there way we can override this property to expand the empty nodes/tag.

I wanted to output xml node like below

<tag></tag>

instead of

<tag/>

Currently Iam doing this with DOM parser using java

Transformer transformer = TransformerFactory.newInstance().newTransformer();
transformer.setOutputProperty(OutputKeys.INDENT, "yes");
transformer.setOutputProperty(OutputKeys.STANDALONE, "yes");
DOMSource domSource = new DOMSource((Node) doc);
StreamResult result = new StreamResult(file);

transformer.transform(domSource, result);

Please help... .

Please let me klnow which method is more efficient in terms of performance to generate an auto increment number in oracle.

Step1

CREATE TABLE test
(id NUMBER PRIMARY KEY,
name VARCHAR2(30));

Step2
CREATE SEQUENCE test_sequence
START WITH 1
INCREMENT BY 1;

Method1

Follow Step1 and Step2 and create a Trigger as below :

CREATE OR REPLACE TRIGGER test_trigger
BEFORE INSERT
ON test
REFERENCING NEW AS NEW
FOR EACH ROW
BEGIN
SELECT test_sequence.nextval INTO :NEW.ID FROM dual;
END;
/


Method 2

Follow Step1 and Step2 and directly have an insert statement as below:

INSERT INTO test (id, name) VALUES (test_sequence.nextval , 'Jon343');


Thanks
Mamatha

Hello,

Please help on the syntax of using Cost Based Optimizer that I am using.

SELECT count (REPOSITORY_KEY) from TIDLRREP --derby-properties index = TIILRREP where REPOSITORY_KEY like 'S%';

Column name- REPOSITORY_KEY
Table - TIDLRREP
Index name - TIILRREP


Also tried using - DERBY-PROPERTIES

When I through a file using java org.apache.derby.tools.ij C:\mysql.sql > C:\GLS\myoutput.txt

Derby Database Version : db-derby-10.5.3.0-bin

mysql.sql file contains

connect 'jdbc:derby:D:\test\PerformanceTest;user=admin;password=password';

MaximumDisplayWidth 9999;

CALL SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1);

CALL SYSCS_UTIL.SYSCS_SET_STATISTICS_TIMING(1);

SELECT count(REPOSITORY_KEY) from TIDLRREP --derby-properties index = TIILRREP where REPOSITORY_KEY like 'S%';

Error output in myoutput.txt

ij version 10.5

ij> connect
ij> 'jdbc:derby:D:\test\PerformanceTest;user=admin;password=password';

ij> MaximumDisplayWidth 9999;

ij> CALL SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1);

0 rows inserted/updated/deleted

ij> CALL SYSCS_UTIL.SYSCS_SET_STATISTICS_TIMING(1);

0 rows inserted/updated/deleted

ij> SELECT count(REPOSITORY_KEY) from TIDLRREP --derby-properties index
ij> = TIILRREP where REPOSITORY_KEY like 'S%';;

ERROR 42Y46: Invalid Properties list in FROM list. There is no index 'TIILRREP WHERE REPOSITORY_KEY LIKE 'S%';' on table 'TIDLRREP'.

ij>


Also I have added below entries in my derby.properties file

derby.language.logQueryPlan=true

derby.optimizer.noTimeout=true


When I directly the query on ij ... it just hangs!!


Any help is appreciated.


Thanks

Mamatha