0001: /* ====================================================================
0002: Copyright 2002-2004 Apache Software Foundation
0003:
0004: Licensed under the Apache License, Version 2.0 (the "License");
0005: you may not use this file except in compliance with the License.
0006: You may obtain a copy of the License at
0007:
0008: http://www.apache.org/licenses/LICENSE-2.0
0009:
0010: Unless required by applicable law or agreed to in writing, software
0011: distributed under the License is distributed on an "AS IS" BASIS,
0012: WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
0013: See the License for the specific language governing permissions and
0014: limitations under the License.
0015: ==================================================================== */
0016:
0017: package org.apache.poi.hssf.usermodel;
0018:
0019: import junit.framework.TestCase;
0020:
0021: import org.apache.poi.hssf.util.*;
0022:
0023: import java.io.*;
0024: import java.util.*;
0025: import java.text.SimpleDateFormat;
0026:
0027: /**
0028: * <p>Title: TestDataValidation</p>
0029: * <p>Description: Class for testing Excel's data validation mechanism
0030: * Second test :
0031: * -
0032: * </p>
0033: * @author Dragos Buleandra ( dragos.buleandra@trade2b.ro )
0034: */
0035: public class TestDataValidation extends TestCase {
0036: public TestDataValidation(String name) {
0037: super (name);
0038: }
0039:
0040: protected void setUp() {
0041: String filename = System.getProperty("HSSF.testdata.path");
0042: if (filename == null) {
0043: System.setProperty("HSSF.testdata.path",
0044: "src/testcases/org/apache/poi/hssf/data");
0045: }
0046: }
0047:
0048: public void testDataValidation() throws Exception {
0049: System.out
0050: .println("\nTest no. 2 - Test Excel's Data validation mechanism");
0051: String resultFile = System.getProperty("HSSF.testdata.path")
0052: + "/TestDataValidation.xls";
0053: HSSFWorkbook wb = new HSSFWorkbook();
0054:
0055: HSSFCellStyle style_1 = this .createStyle(wb,
0056: HSSFCellStyle.ALIGN_LEFT);
0057: HSSFCellStyle style_2 = this .createStyle(wb,
0058: HSSFCellStyle.ALIGN_CENTER);
0059: HSSFCellStyle style_3 = this .createStyle(wb,
0060: HSSFCellStyle.ALIGN_CENTER,
0061: HSSFColor.GREY_25_PERCENT.index, true);
0062: HSSFCellStyle style_4 = this .createHeaderStyle(wb);
0063: HSSFDataValidation data_validation = null;
0064:
0065: //data validation's number types
0066: System.out
0067: .print(" Create sheet for Data Validation's number types ... ");
0068: HSSFSheet fSheet = wb.createSheet("Number types");
0069:
0070: //"Whole number" validation type
0071: this .createDVTypeRow(wb, 0, style_3, "Whole number");
0072: this .createHeaderRow(wb, 0, style_4);
0073:
0074: short start_row = (short) fSheet.getPhysicalNumberOfRows();
0075: data_validation = new HSSFDataValidation((short) (start_row),
0076: (short) 0, (short) (start_row), (short) 0);
0077: data_validation
0078: .setDataValidationType(HSSFDataValidation.DATA_TYPE_INTEGER);
0079: data_validation
0080: .setOperator(HSSFDataValidation.OPERATOR_BETWEEN);
0081: data_validation.setFirstFormula("2");
0082: data_validation.setSecondFormula("6");
0083: data_validation.createErrorBox("Invalid input !",
0084: "Something is wrong ; check condition !");
0085: data_validation.createPromptBox("Hi , dear user !",
0086: "So , you just selected me ! Thanks !");
0087: fSheet.addValidationData(data_validation);
0088: this .writeDataValidationSettings(fSheet, style_1, style_2,
0089: "Between 2 and 6 ", true, true, true);
0090: this .writeOtherSettings(fSheet, style_1,
0091: "Error box type = STOP");
0092:
0093: data_validation.setFirstRow((short) (start_row + 1));
0094: data_validation.setLastRow((short) (start_row + 1));
0095: data_validation.setEmptyCellAllowed(false);
0096: data_validation
0097: .setOperator(HSSFDataValidation.OPERATOR_NOT_BETWEEN);
0098: data_validation
0099: .setErrorStyle(HSSFDataValidation.ERROR_STYLE_INFO);
0100: fSheet.addValidationData(data_validation);
0101: this .writeDataValidationSettings(fSheet, style_1, style_2,
0102: "Not between 2 and 6 ", false, true, true);
0103: this .writeOtherSettings(fSheet, style_1,
0104: "Error box type = INFO");
0105:
0106: data_validation.setFirstRow((short) (start_row + 2));
0107: data_validation.setLastRow((short) (start_row + 2));
0108: data_validation.setEmptyCellAllowed(false);
0109: data_validation.setShowPromptBox(false);
0110: data_validation.setFirstFormula("3");
0111: data_validation.setSecondFormula(null);
0112: data_validation.setOperator(HSSFDataValidation.OPERATOR_EQUAL);
0113: data_validation
0114: .setErrorStyle(HSSFDataValidation.ERROR_STYLE_WARNING);
0115: fSheet.addValidationData(data_validation);
0116: this .writeDataValidationSettings(fSheet, style_1, style_2,
0117: "Equal to 3", false, false, true);
0118: this .writeOtherSettings(fSheet, style_1,
0119: "Error box type = WARNING");
0120:
0121: data_validation.setFirstRow((short) (start_row + 3));
0122: data_validation.setLastRow((short) (start_row + 3));
0123: data_validation.setEmptyCellAllowed(false);
0124: data_validation.setShowPromptBox(false);
0125: data_validation.setShowErrorBox(false);
0126: data_validation.setFirstFormula("3");
0127: data_validation.setSecondFormula(null);
0128: data_validation
0129: .setOperator(HSSFDataValidation.OPERATOR_NOT_EQUAL);
0130: fSheet.addValidationData(data_validation);
0131: this .writeDataValidationSettings(fSheet, style_1, style_2,
0132: "Not equal to 3", false, false, false);
0133: this .writeOtherSettings(fSheet, style_1, "-");
0134:
0135: data_validation.setFirstRow((short) (start_row + 4));
0136: data_validation.setLastRow((short) (start_row + 4));
0137: data_validation.setEmptyCellAllowed(true);
0138: data_validation.setShowPromptBox(false);
0139: data_validation.setShowErrorBox(false);
0140: data_validation.setFirstFormula("3");
0141: data_validation.setSecondFormula(null);
0142: data_validation
0143: .setOperator(HSSFDataValidation.OPERATOR_GREATER_THAN);
0144: fSheet.addValidationData(data_validation);
0145: this .writeDataValidationSettings(fSheet, style_1, style_2,
0146: "Greater than 3", true, false, false);
0147: this .writeOtherSettings(fSheet, style_1, "-");
0148:
0149: data_validation.setFirstRow((short) (start_row + 5));
0150: data_validation.setLastRow((short) (start_row + 5));
0151: data_validation.setEmptyCellAllowed(true);
0152: data_validation.setShowPromptBox(true);
0153: data_validation.setShowErrorBox(false);
0154: data_validation.setFirstFormula("3");
0155: data_validation.setSecondFormula(null);
0156: data_validation
0157: .setOperator(HSSFDataValidation.OPERATOR_LESS_THAN);
0158: fSheet.addValidationData(data_validation);
0159: this .writeDataValidationSettings(fSheet, style_1, style_2,
0160: "Less than 3", true, true, false);
0161: this .writeOtherSettings(fSheet, style_1, "-");
0162:
0163: data_validation.setFirstRow((short) (start_row + 6));
0164: data_validation.setLastRow((short) (start_row + 6));
0165: data_validation.setEmptyCellAllowed(true);
0166: data_validation.setShowPromptBox(false);
0167: data_validation
0168: .setErrorStyle(HSSFDataValidation.ERROR_STYLE_STOP);
0169: data_validation.setShowErrorBox(true);
0170: data_validation.setFirstFormula("4");
0171: data_validation.setSecondFormula(null);
0172: data_validation
0173: .setOperator(HSSFDataValidation.OPERATOR_GREATER_OR_EQUAL);
0174: fSheet.addValidationData(data_validation);
0175: this .writeDataValidationSettings(fSheet, style_1, style_2,
0176: "Greater than or equal to 4", true, false, true);
0177: this .writeOtherSettings(fSheet, style_1,
0178: "Error box type = STOP");
0179:
0180: data_validation.setFirstRow((short) (start_row + 7));
0181: data_validation.setLastRow((short) (start_row + 7));
0182: data_validation.setEmptyCellAllowed(false);
0183: data_validation.setShowPromptBox(true);
0184: data_validation.setShowErrorBox(false);
0185: data_validation.setFirstFormula("4");
0186: data_validation.setSecondFormula(null);
0187: data_validation
0188: .setOperator(HSSFDataValidation.OPERATOR_LESS_OR_EQUAL);
0189: fSheet.addValidationData(data_validation);
0190: this .writeDataValidationSettings(fSheet, style_1, style_2,
0191: "Less than or equal to 4", false, true, false);
0192: this .writeOtherSettings(fSheet, style_1, "-");
0193:
0194: //"Decimal" validation type
0195: this .createDVTypeRow(wb, 0, style_3, "Decimal");
0196: this .createHeaderRow(wb, 0, style_4);
0197:
0198: start_row += (short) (8 + 4);
0199: data_validation = new HSSFDataValidation((short) (start_row),
0200: (short) 0, (short) (start_row), (short) 0);
0201: data_validation
0202: .setDataValidationType(HSSFDataValidation.DATA_TYPE_DECIMAL);
0203: data_validation
0204: .setOperator(HSSFDataValidation.OPERATOR_BETWEEN);
0205: data_validation.setFirstFormula("2");
0206: data_validation.setSecondFormula("6");
0207: data_validation.createErrorBox("Invalid input !",
0208: "Something is wrong ; check condition !");
0209: data_validation.createPromptBox("Hi , dear user !",
0210: "So , you just selected me ! Thanks !");
0211: fSheet.addValidationData(data_validation);
0212: this .writeDataValidationSettings(fSheet, style_1, style_2,
0213: "Between 2 and 6 ", true, true, true);
0214: this .writeOtherSettings(fSheet, style_1,
0215: "Error box type = STOP");
0216:
0217: data_validation.setFirstRow((short) (start_row + 1));
0218: data_validation.setLastRow((short) (start_row + 1));
0219: data_validation.setEmptyCellAllowed(false);
0220: data_validation
0221: .setOperator(HSSFDataValidation.OPERATOR_NOT_BETWEEN);
0222: data_validation
0223: .setErrorStyle(HSSFDataValidation.ERROR_STYLE_INFO);
0224: fSheet.addValidationData(data_validation);
0225: this .writeDataValidationSettings(fSheet, style_1, style_2,
0226: "Not between 2 and 6 ", false, true, true);
0227: this .writeOtherSettings(fSheet, style_1,
0228: "Error box type = INFO");
0229:
0230: data_validation.setFirstRow((short) (start_row + 2));
0231: data_validation.setLastRow((short) (start_row + 2));
0232: data_validation.setEmptyCellAllowed(false);
0233: data_validation.setShowPromptBox(false);
0234: data_validation.setFirstFormula("3");
0235: data_validation.setSecondFormula(null);
0236: data_validation.setOperator(HSSFDataValidation.OPERATOR_EQUAL);
0237: data_validation
0238: .setErrorStyle(HSSFDataValidation.ERROR_STYLE_WARNING);
0239: fSheet.addValidationData(data_validation);
0240: this .writeDataValidationSettings(fSheet, style_1, style_2,
0241: "Equal to 3", false, false, true);
0242: this .writeOtherSettings(fSheet, style_1,
0243: "Error box type = WARNING");
0244:
0245: data_validation.setFirstRow((short) (start_row + 3));
0246: data_validation.setLastRow((short) (start_row + 3));
0247: data_validation.setEmptyCellAllowed(false);
0248: data_validation.setShowPromptBox(false);
0249: data_validation.setShowErrorBox(false);
0250: data_validation.setFirstFormula("3");
0251: data_validation.setSecondFormula(null);
0252: data_validation
0253: .setOperator(HSSFDataValidation.OPERATOR_NOT_EQUAL);
0254: fSheet.addValidationData(data_validation);
0255: this .writeDataValidationSettings(fSheet, style_1, style_2,
0256: "Not equal to 3", false, false, false);
0257: this .writeOtherSettings(fSheet, style_1, "-");
0258:
0259: data_validation.setFirstRow((short) (start_row + 4));
0260: data_validation.setLastRow((short) (start_row + 4));
0261: data_validation.setEmptyCellAllowed(true);
0262: data_validation.setShowPromptBox(false);
0263: data_validation.setShowErrorBox(false);
0264: data_validation.setFirstFormula("3");
0265: data_validation.setSecondFormula(null);
0266: data_validation
0267: .setOperator(HSSFDataValidation.OPERATOR_GREATER_THAN);
0268: fSheet.addValidationData(data_validation);
0269: this .writeDataValidationSettings(fSheet, style_1, style_2,
0270: "Greater than 3", true, false, false);
0271: this .writeOtherSettings(fSheet, style_1, "-");
0272:
0273: data_validation.setFirstRow((short) (start_row + 5));
0274: data_validation.setLastRow((short) (start_row + 5));
0275: data_validation.setEmptyCellAllowed(true);
0276: data_validation.setShowPromptBox(true);
0277: data_validation.setShowErrorBox(false);
0278: data_validation.setFirstFormula("3");
0279: data_validation.setSecondFormula(null);
0280: data_validation
0281: .setOperator(HSSFDataValidation.OPERATOR_LESS_THAN);
0282: fSheet.addValidationData(data_validation);
0283: this .writeDataValidationSettings(fSheet, style_1, style_2,
0284: "Less than 3", true, true, false);
0285: this .writeOtherSettings(fSheet, style_1, "-");
0286:
0287: data_validation.setFirstRow((short) (start_row + 6));
0288: data_validation.setLastRow((short) (start_row + 6));
0289: data_validation.setEmptyCellAllowed(true);
0290: data_validation.setShowPromptBox(false);
0291: data_validation
0292: .setErrorStyle(HSSFDataValidation.ERROR_STYLE_STOP);
0293: data_validation.setShowErrorBox(true);
0294: data_validation.setFirstFormula("4");
0295: data_validation.setSecondFormula(null);
0296: data_validation
0297: .setOperator(HSSFDataValidation.OPERATOR_GREATER_OR_EQUAL);
0298: fSheet.addValidationData(data_validation);
0299: this .writeDataValidationSettings(fSheet, style_1, style_2,
0300: "Greater than or equal to 4", true, false, true);
0301: this .writeOtherSettings(fSheet, style_1,
0302: "Error box type = STOP");
0303:
0304: data_validation.setFirstRow((short) (start_row + 7));
0305: data_validation.setLastRow((short) (start_row + 7));
0306: data_validation.setEmptyCellAllowed(false);
0307: data_validation.setShowPromptBox(true);
0308: data_validation.setShowErrorBox(false);
0309: data_validation.setFirstFormula("4");
0310: data_validation.setSecondFormula(null);
0311: data_validation
0312: .setOperator(HSSFDataValidation.OPERATOR_LESS_OR_EQUAL);
0313: fSheet.addValidationData(data_validation);
0314: this .writeDataValidationSettings(fSheet, style_1, style_2,
0315: "Less than or equal to 4", false, true, false);
0316: this .writeOtherSettings(fSheet, style_1, "-");
0317:
0318: System.out.println("done !");
0319:
0320: //"List" Data Validation type
0321: /** @todo List*/
0322: System.out
0323: .print(" Create sheet for 'List' Data Validation type ... ");
0324: fSheet = wb.createSheet("Lists");
0325:
0326: this .createDVTypeRow(wb, 1, style_3,
0327: "Explicit lists - list items are explicitly provided");
0328: this
0329: .createDVDeescriptionRow(wb, 1, style_3,
0330: "Disadvantage - sum of item's length should be less than 255 characters");
0331: this .createHeaderRow(wb, 1, style_4);
0332:
0333: start_row = (short) fSheet.getPhysicalNumberOfRows();
0334: data_validation = new HSSFDataValidation((short) (start_row),
0335: (short) 0, (short) (start_row), (short) 0);
0336: data_validation
0337: .setDataValidationType(HSSFDataValidation.DATA_TYPE_LIST);
0338: data_validation.setFirstFormula("1+2+3");
0339: data_validation.setSecondFormula(null);
0340: data_validation.setSurppressDropDownArrow(false);
0341: data_validation.createErrorBox("Invalid input !",
0342: "Something is wrong ; check condition !");
0343: data_validation.createPromptBox("Hi , dear user !",
0344: "So , you just selected me ! Thanks !");
0345: fSheet.addValidationData(data_validation);
0346: this .writeDataValidationSettings(fSheet, style_1, style_2,
0347: "POIFS,HSSF,HWPF,HPSF", true, true, true);
0348: this .writeOtherSettings(fSheet, style_1,
0349: "Error box type=STOP ; In-cell dropdown=yes");
0350:
0351: data_validation = new HSSFDataValidation(
0352: (short) (start_row + 1), (short) 0,
0353: (short) (start_row + 1), (short) 0);
0354: data_validation
0355: .setDataValidationType(HSSFDataValidation.DATA_TYPE_LIST);
0356: data_validation.setFirstFormula("4+5+6+7");
0357: data_validation.setSecondFormula(null);
0358: data_validation.setSurppressDropDownArrow(false);
0359: data_validation.setEmptyCellAllowed(false);
0360: data_validation.setShowPromptBox(false);
0361: data_validation.createErrorBox("Invalid input !",
0362: "Something is wrong ; check condition !");
0363: fSheet.addValidationData(data_validation);
0364: this .writeDataValidationSettings(fSheet, style_1, style_2,
0365: "POIFS,HSSF,HWPF,HPSF", false, false, true);
0366: this .writeOtherSettings(fSheet, style_1,
0367: "Error box type=STOP ; In-cell dropdown=yes");
0368:
0369: data_validation = new HSSFDataValidation(
0370: (short) (start_row + 2), (short) 0,
0371: (short) (start_row + 2), (short) 0);
0372: data_validation
0373: .setDataValidationType(HSSFDataValidation.DATA_TYPE_LIST);
0374: data_validation.setFirstFormula("7+21");
0375: data_validation.setSecondFormula(null);
0376: data_validation.setSurppressDropDownArrow(true);
0377: data_validation.createErrorBox("Invalid input !",
0378: "Something is wrong ; check condition !");
0379: data_validation.createPromptBox("Hi , dear user !",
0380: "So , you just selected me ! Thanks !");
0381: fSheet.addValidationData(data_validation);
0382: this .writeDataValidationSettings(fSheet, style_1, style_2,
0383: "POIFS,HSSF,HWPF,HPSF", true, true, true);
0384: this .writeOtherSettings(fSheet, style_1,
0385: "Error box type=STOP ; In-cell dropdown=no");
0386:
0387: data_validation = new HSSFDataValidation(
0388: (short) (start_row + 3), (short) 0,
0389: (short) (start_row + 3), (short) 0);
0390: data_validation
0391: .setDataValidationType(HSSFDataValidation.DATA_TYPE_LIST);
0392: data_validation.setFirstFormula("8/2");
0393: data_validation.setSecondFormula(null);
0394: data_validation.setSurppressDropDownArrow(true);
0395: data_validation.createErrorBox("Invalid input !",
0396: "Something is wrong ; check condition !");
0397: data_validation.setEmptyCellAllowed(false);
0398: data_validation.setShowPromptBox(false);
0399: fSheet.addValidationData(data_validation);
0400: this .writeDataValidationSettings(fSheet, style_1, style_2,
0401: "POIFS,HSSF,HWPF,HPSF", false, false, true);
0402: this .writeOtherSettings(fSheet, style_1,
0403: "Error box type=STOP ; In-cell dropdown=no");
0404:
0405: this
0406: .createDVTypeRow(wb, 1, style_3,
0407: "Reference lists - list items are taken from others cells");
0408: this
0409: .createDVDeescriptionRow(wb, 1, style_3,
0410: "Advantage - no restriction regarding the sum of item's length");
0411: this .createHeaderRow(wb, 1, style_4);
0412:
0413: start_row += (short) (4 + 5);
0414: String cellStrValue = "a b c d e f g h i j k l m n o p r s t u v x y z w 0 1 2 3 4 "
0415: + "a b c d e f g h i j k l m n o p r s t u v x y z w 0 1 2 3 4 "
0416: + "a b c d e f g h i j k l m n o p r s t u v x y z w 0 1 2 3 4 "
0417: + "a b c d e f g h i j k l m n o p r s t u v x y z w 0 1 2 3 4 ";
0418:
0419: String strFormula = "$A$100:$A$120";
0420: data_validation = new HSSFDataValidation((short) (start_row),
0421: (short) 0, (short) (start_row), (short) 0);
0422: data_validation
0423: .setDataValidationType(HSSFDataValidation.DATA_TYPE_LIST);
0424: data_validation.setFirstFormula(strFormula);
0425: data_validation.setSecondFormula(null);
0426: data_validation.setSurppressDropDownArrow(false);
0427: data_validation.createErrorBox("Invalid input !",
0428: "Something is wrong ; check condition !");
0429: data_validation.createPromptBox("Hi , dear user !",
0430: "So , you just selected me ! Thanks !");
0431: fSheet.addValidationData(data_validation);
0432: this .writeDataValidationSettings(fSheet, style_1, style_2,
0433: strFormula, true, true, true);
0434: this .writeOtherSettings(fSheet, style_1,
0435: "Error box type=STOP ; In-cell dropdown=yes");
0436:
0437: data_validation = new HSSFDataValidation(
0438: (short) (start_row + 1), (short) 0,
0439: (short) (start_row + 1), (short) 0);
0440: data_validation
0441: .setDataValidationType(HSSFDataValidation.DATA_TYPE_LIST);
0442: data_validation.setFirstFormula(strFormula);
0443: data_validation.setSecondFormula(null);
0444: data_validation.setSurppressDropDownArrow(false);
0445: data_validation.setEmptyCellAllowed(false);
0446: data_validation.setShowPromptBox(false);
0447: data_validation.createErrorBox("Invalid input !",
0448: "Something is wrong ; check condition !");
0449: fSheet.addValidationData(data_validation);
0450: this .writeDataValidationSettings(fSheet, style_1, style_2,
0451: strFormula, false, false, true);
0452: this .writeOtherSettings(fSheet, style_1,
0453: "Error box type=STOP ; In-cell dropdown=yes");
0454:
0455: data_validation = new HSSFDataValidation(
0456: (short) (start_row + 2), (short) 0,
0457: (short) (start_row + 2), (short) 0);
0458: data_validation
0459: .setDataValidationType(HSSFDataValidation.DATA_TYPE_LIST);
0460: data_validation.setFirstFormula(strFormula);
0461: data_validation.setSecondFormula(null);
0462: data_validation.setSurppressDropDownArrow(true);
0463: data_validation.createErrorBox("Invalid input !",
0464: "Something is wrong ; check condition !");
0465: data_validation.createPromptBox("Hi , dear user !",
0466: "So , you just selected me ! Thanks !");
0467: fSheet.addValidationData(data_validation);
0468: this .writeDataValidationSettings(fSheet, style_1, style_2,
0469: strFormula, true, true, true);
0470: this .writeOtherSettings(fSheet, style_1,
0471: "Error box type=STOP ; In-cell dropdown=no");
0472:
0473: data_validation = new HSSFDataValidation(
0474: (short) (start_row + 3), (short) 0,
0475: (short) (start_row + 3), (short) 0);
0476: data_validation
0477: .setDataValidationType(HSSFDataValidation.DATA_TYPE_LIST);
0478: data_validation.setFirstFormula(strFormula);
0479: data_validation.setSecondFormula(null);
0480: data_validation.setSurppressDropDownArrow(true);
0481: data_validation.createErrorBox("Invalid input !",
0482: "Something is wrong ; check condition !");
0483: data_validation.setEmptyCellAllowed(false);
0484: data_validation.setShowPromptBox(false);
0485: fSheet.addValidationData(data_validation);
0486: this .writeDataValidationSettings(fSheet, style_1, style_2,
0487: strFormula, false, false, true);
0488: this .writeOtherSettings(fSheet, style_1,
0489: "Error box type=STOP ; In-cell dropdown=no");
0490:
0491: for (int i = 100; i <= 120; i++) {
0492: HSSFRow currRow = fSheet.createRow(i);
0493: currRow.createCell((short) 0).setCellValue(cellStrValue);
0494: // currRow.hide( true );
0495: }
0496:
0497: System.out.println("done !");
0498:
0499: //Date/Time Validation type
0500: System.out
0501: .print(" Create sheet for 'Date' and 'Time' Data Validation types ... ");
0502: fSheet = wb.createSheet("Date_Time");
0503: SimpleDateFormat df = new SimpleDateFormat("m/d/yyyy");
0504: HSSFDataFormat dataFormat = wb.createDataFormat();
0505: short fmtDate = dataFormat.getFormat("m/d/yyyy");
0506: short fmtTime = dataFormat.getFormat("h:mm");
0507: HSSFCellStyle cellStyle_data = wb.createCellStyle();
0508: cellStyle_data.setDataFormat(fmtDate);
0509: HSSFCellStyle cellStyle_time = wb.createCellStyle();
0510: cellStyle_time.setDataFormat(fmtTime);
0511:
0512: this
0513: .createDVTypeRow(wb, 2, style_3,
0514: "Date ( cells are already formated as date - m/d/yyyy)");
0515: this .createHeaderRow(wb, 2, style_4);
0516:
0517: start_row = (short) fSheet.getPhysicalNumberOfRows();
0518: data_validation = new HSSFDataValidation((short) (start_row),
0519: (short) 0, (short) (start_row), (short) 0);
0520: data_validation
0521: .setDataValidationType(HSSFDataValidation.DATA_TYPE_DATE);
0522: data_validation
0523: .setOperator(HSSFDataValidation.OPERATOR_BETWEEN);
0524:
0525: data_validation.setFirstFormula(String
0526: .valueOf((int) HSSFDateUtil.getExcelDate(df
0527: .parse("1/2/2004"))));
0528: data_validation.setSecondFormula(String
0529: .valueOf((int) HSSFDateUtil.getExcelDate(df
0530: .parse("1/6/2004"))));
0531:
0532: data_validation.createErrorBox("Invalid input !",
0533: "Something is wrong ; check condition !");
0534: data_validation.createPromptBox("Hi , dear user !",
0535: "So , you just selected me ! Thanks !");
0536: fSheet.addValidationData(data_validation);
0537: this .writeDataValidationSettings(fSheet, style_1, style_2,
0538: "Between 1/2/2004 and 1/6/2004 ", true, true, true);
0539: this .setCellFormat(fSheet, cellStyle_data);
0540: this .writeOtherSettings(fSheet, style_1,
0541: "Error box type = STOP");
0542:
0543: data_validation.setFirstRow((short) (start_row + 1));
0544: data_validation.setLastRow((short) (start_row + 1));
0545: data_validation.setEmptyCellAllowed(false);
0546: data_validation
0547: .setOperator(HSSFDataValidation.OPERATOR_NOT_BETWEEN);
0548: data_validation
0549: .setErrorStyle(HSSFDataValidation.ERROR_STYLE_INFO);
0550: fSheet.addValidationData(data_validation);
0551: this
0552: .writeDataValidationSettings(fSheet, style_1, style_2,
0553: "Not between 1/2/2004 and 1/6/2004 ", false,
0554: true, true);
0555: this .setCellFormat(fSheet, cellStyle_data);
0556: this .writeOtherSettings(fSheet, style_1,
0557: "Error box type = INFO");
0558:
0559: data_validation.setFirstRow((short) (start_row + 2));
0560: data_validation.setLastRow((short) (start_row + 2));
0561: data_validation.setEmptyCellAllowed(false);
0562: data_validation.setShowPromptBox(false);
0563: data_validation.setFirstFormula(String
0564: .valueOf((int) HSSFDateUtil.getExcelDate(df
0565: .parse("3/2/2004"))));
0566: data_validation.setSecondFormula(null);
0567: data_validation.setOperator(HSSFDataValidation.OPERATOR_EQUAL);
0568: data_validation
0569: .setErrorStyle(HSSFDataValidation.ERROR_STYLE_WARNING);
0570: fSheet.addValidationData(data_validation);
0571: this .writeDataValidationSettings(fSheet, style_1, style_2,
0572: "Equal to 3/2/2004", false, false, true);
0573: this .setCellFormat(fSheet, cellStyle_data);
0574: this .writeOtherSettings(fSheet, style_1,
0575: "Error box type = WARNING");
0576:
0577: data_validation.setFirstRow((short) (start_row + 3));
0578: data_validation.setLastRow((short) (start_row + 3));
0579: data_validation.setEmptyCellAllowed(false);
0580: data_validation.setShowPromptBox(false);
0581: data_validation.setShowErrorBox(false);
0582: data_validation.setFirstFormula(String
0583: .valueOf((int) HSSFDateUtil.getExcelDate(df
0584: .parse("3/2/2004"))));
0585: data_validation.setSecondFormula(null);
0586: data_validation
0587: .setOperator(HSSFDataValidation.OPERATOR_NOT_EQUAL);
0588: fSheet.addValidationData(data_validation);
0589: this .writeDataValidationSettings(fSheet, style_1, style_2,
0590: "Not equal to 3/2/2004", false, false, false);
0591: this .setCellFormat(fSheet, cellStyle_data);
0592: this .writeOtherSettings(fSheet, style_1, "-");
0593:
0594: data_validation.setFirstRow((short) (start_row + 4));
0595: data_validation.setLastRow((short) (start_row + 4));
0596: data_validation.setEmptyCellAllowed(true);
0597: data_validation.setShowPromptBox(false);
0598: data_validation.setShowErrorBox(false);
0599: data_validation.setFirstFormula(String
0600: .valueOf((int) HSSFDateUtil.getExcelDate(df
0601: .parse("3/2/2004"))));
0602: data_validation.setSecondFormula(null);
0603: data_validation
0604: .setOperator(HSSFDataValidation.OPERATOR_GREATER_THAN);
0605: fSheet.addValidationData(data_validation);
0606: this .writeDataValidationSettings(fSheet, style_1, style_2,
0607: "Greater than 3/2/2004", true, false, false);
0608: this .setCellFormat(fSheet, cellStyle_data);
0609: this .writeOtherSettings(fSheet, style_1, "-");
0610:
0611: data_validation.setFirstRow((short) (start_row + 5));
0612: data_validation.setLastRow((short) (start_row + 5));
0613: data_validation.setEmptyCellAllowed(true);
0614: data_validation.setShowPromptBox(true);
0615: data_validation.setShowErrorBox(false);
0616: data_validation.setFirstFormula(String
0617: .valueOf((int) HSSFDateUtil.getExcelDate(df
0618: .parse("3/2/2004"))));
0619: data_validation.setSecondFormula(null);
0620: data_validation
0621: .setOperator(HSSFDataValidation.OPERATOR_LESS_THAN);
0622: fSheet.addValidationData(data_validation);
0623: this .writeDataValidationSettings(fSheet, style_1, style_2,
0624: "Less than 3/2/2004", true, true, false);
0625: this .setCellFormat(fSheet, cellStyle_data);
0626: this .writeOtherSettings(fSheet, style_1, "-");
0627:
0628: data_validation.setFirstRow((short) (start_row + 6));
0629: data_validation.setLastRow((short) (start_row + 6));
0630: data_validation.setEmptyCellAllowed(true);
0631: data_validation.setShowPromptBox(false);
0632: data_validation
0633: .setErrorStyle(HSSFDataValidation.ERROR_STYLE_STOP);
0634: data_validation.setShowErrorBox(true);
0635: data_validation.setFirstFormula(String
0636: .valueOf((int) HSSFDateUtil.getExcelDate(df
0637: .parse("3/2/2004"))));
0638: data_validation.setSecondFormula(null);
0639: data_validation
0640: .setOperator(HSSFDataValidation.OPERATOR_GREATER_OR_EQUAL);
0641: fSheet.addValidationData(data_validation);
0642: this .writeDataValidationSettings(fSheet, style_1, style_2,
0643: "Greater than or equal to 3/2/2004", true, false, true);
0644: this .setCellFormat(fSheet, cellStyle_data);
0645: this .writeOtherSettings(fSheet, style_1,
0646: "Error box type = STOP");
0647:
0648: data_validation.setFirstRow((short) (start_row + 7));
0649: data_validation.setLastRow((short) (start_row + 7));
0650: data_validation.setEmptyCellAllowed(false);
0651: data_validation.setShowPromptBox(true);
0652: data_validation.setShowErrorBox(false);
0653: data_validation.setFirstFormula(String
0654: .valueOf((int) HSSFDateUtil.getExcelDate(df
0655: .parse("3/4/2004"))));
0656: data_validation.setSecondFormula(null);
0657: data_validation
0658: .setOperator(HSSFDataValidation.OPERATOR_LESS_OR_EQUAL);
0659: fSheet.addValidationData(data_validation);
0660: this .writeDataValidationSettings(fSheet, style_1, style_2,
0661: "Less than or equal to 3/4/2004", false, true, false);
0662: this .setCellFormat(fSheet, cellStyle_data);
0663: this .writeOtherSettings(fSheet, style_1, "-");
0664:
0665: //"Time" validation type
0666: this .createDVTypeRow(wb, 2, style_3,
0667: "Time ( cells are already formated as time - h:mm)");
0668: this .createHeaderRow(wb, 2, style_4);
0669:
0670: df = new SimpleDateFormat("hh:mm");
0671:
0672: start_row += (short) (8 + 4);
0673: data_validation = new HSSFDataValidation((short) (start_row),
0674: (short) 0, (short) (start_row), (short) 0);
0675: data_validation
0676: .setDataValidationType(HSSFDataValidation.DATA_TYPE_TIME);
0677: data_validation
0678: .setOperator(HSSFDataValidation.OPERATOR_BETWEEN);
0679: data_validation.setFirstFormula(String.valueOf(HSSFDateUtil
0680: .getExcelDate(df.parse("12:00"))));
0681: data_validation.setSecondFormula(String.valueOf(HSSFDateUtil
0682: .getExcelDate(df.parse("16:00"))));
0683: data_validation.createErrorBox("Invalid input !",
0684: "Something is wrong ; check condition !");
0685: data_validation.createPromptBox("Hi , dear user !",
0686: "So , you just selected me ! Thanks !");
0687: fSheet.addValidationData(data_validation);
0688: this .writeDataValidationSettings(fSheet, style_1, style_2,
0689: "Between 12:00 and 16:00 ", true, true, true);
0690: this .setCellFormat(fSheet, cellStyle_time);
0691: this .writeOtherSettings(fSheet, style_1,
0692: "Error box type = STOP");
0693:
0694: data_validation.setFirstRow((short) (start_row + 1));
0695: data_validation.setLastRow((short) (start_row + 1));
0696: data_validation.setEmptyCellAllowed(false);
0697: data_validation
0698: .setOperator(HSSFDataValidation.OPERATOR_NOT_BETWEEN);
0699: data_validation
0700: .setErrorStyle(HSSFDataValidation.ERROR_STYLE_INFO);
0701: fSheet.addValidationData(data_validation);
0702: this .writeDataValidationSettings(fSheet, style_1, style_2,
0703: "Not between 12:00 and 16:00 ", false, true, true);
0704: this .setCellFormat(fSheet, cellStyle_time);
0705: this .writeOtherSettings(fSheet, style_1,
0706: "Error box type = INFO");
0707:
0708: data_validation.setFirstRow((short) (start_row + 2));
0709: data_validation.setLastRow((short) (start_row + 2));
0710: data_validation.setEmptyCellAllowed(false);
0711: data_validation.setShowPromptBox(false);
0712: data_validation.setFirstFormula(String
0713: .valueOf((int) HSSFDateUtil.getExcelDate(df
0714: .parse("13:35"))));
0715: data_validation.setSecondFormula(null);
0716: data_validation.setOperator(HSSFDataValidation.OPERATOR_EQUAL);
0717: data_validation
0718: .setErrorStyle(HSSFDataValidation.ERROR_STYLE_WARNING);
0719: fSheet.addValidationData(data_validation);
0720: this .writeDataValidationSettings(fSheet, style_1, style_2,
0721: "Equal to 13:35", false, false, true);
0722: this .setCellFormat(fSheet, cellStyle_time);
0723: this .writeOtherSettings(fSheet, style_1,
0724: "Error box type = WARNING");
0725:
0726: data_validation.setFirstRow((short) (start_row + 3));
0727: data_validation.setLastRow((short) (start_row + 3));
0728: data_validation.setEmptyCellAllowed(false);
0729: data_validation.setShowPromptBox(false);
0730: data_validation.setShowErrorBox(false);
0731: data_validation.setFirstFormula(String.valueOf(HSSFDateUtil
0732: .getExcelDate(df.parse("13:35"))));
0733: data_validation.setSecondFormula(null);
0734: data_validation
0735: .setOperator(HSSFDataValidation.OPERATOR_NOT_EQUAL);
0736: fSheet.addValidationData(data_validation);
0737: this .writeDataValidationSettings(fSheet, style_1, style_2,
0738: "Not equal to 13:35", false, false, false);
0739: this .setCellFormat(fSheet, cellStyle_time);
0740: this .writeOtherSettings(fSheet, style_1, "-");
0741:
0742: data_validation.setFirstRow((short) (start_row + 4));
0743: data_validation.setLastRow((short) (start_row + 4));
0744: data_validation.setEmptyCellAllowed(true);
0745: data_validation.setShowPromptBox(false);
0746: data_validation.setShowErrorBox(false);
0747: data_validation.setFirstFormula(String.valueOf(HSSFDateUtil
0748: .getExcelDate(df.parse("12:00"))));
0749: data_validation.setSecondFormula(null);
0750: data_validation
0751: .setOperator(HSSFDataValidation.OPERATOR_GREATER_THAN);
0752: fSheet.addValidationData(data_validation);
0753: this .writeDataValidationSettings(fSheet, style_1, style_2,
0754: "Greater than 12:00", true, false, false);
0755: this .setCellFormat(fSheet, cellStyle_time);
0756: this .writeOtherSettings(fSheet, style_1, "-");
0757:
0758: data_validation.setFirstRow((short) (start_row + 5));
0759: data_validation.setLastRow((short) (start_row + 5));
0760: data_validation.setEmptyCellAllowed(true);
0761: data_validation.setShowPromptBox(true);
0762: data_validation.setShowErrorBox(false);
0763: data_validation.setFirstFormula(String.valueOf(HSSFDateUtil
0764: .getExcelDate(df.parse("12:00"))));
0765: data_validation.setSecondFormula(null);
0766: data_validation
0767: .setOperator(HSSFDataValidation.OPERATOR_LESS_THAN);
0768: fSheet.addValidationData(data_validation);
0769: this .writeDataValidationSettings(fSheet, style_1, style_2,
0770: "Less than 12:00", true, true, false);
0771: this .setCellFormat(fSheet, cellStyle_time);
0772: this .writeOtherSettings(fSheet, style_1, "-");
0773:
0774: data_validation.setFirstRow((short) (start_row + 6));
0775: data_validation.setLastRow((short) (start_row + 6));
0776: data_validation.setEmptyCellAllowed(true);
0777: data_validation.setShowPromptBox(false);
0778: data_validation
0779: .setErrorStyle(HSSFDataValidation.ERROR_STYLE_STOP);
0780: data_validation.setShowErrorBox(true);
0781: data_validation.setFirstFormula(String.valueOf(HSSFDateUtil
0782: .getExcelDate(df.parse("14:00"))));
0783: data_validation.setSecondFormula(null);
0784: data_validation
0785: .setOperator(HSSFDataValidation.OPERATOR_GREATER_OR_EQUAL);
0786: fSheet.addValidationData(data_validation);
0787: this .writeDataValidationSettings(fSheet, style_1, style_2,
0788: "Greater than or equal to 14:00", true, false, true);
0789: this .setCellFormat(fSheet, cellStyle_time);
0790: this .writeOtherSettings(fSheet, style_1,
0791: "Error box type = STOP");
0792:
0793: data_validation.setFirstRow((short) (start_row + 7));
0794: data_validation.setLastRow((short) (start_row + 7));
0795: data_validation.setEmptyCellAllowed(false);
0796: data_validation.setShowPromptBox(true);
0797: data_validation.setShowErrorBox(false);
0798: data_validation.setFirstFormula(String.valueOf(HSSFDateUtil
0799: .getExcelDate(df.parse("14:00"))));
0800: data_validation.setSecondFormula(null);
0801: data_validation
0802: .setOperator(HSSFDataValidation.OPERATOR_LESS_OR_EQUAL);
0803: fSheet.addValidationData(data_validation);
0804: this .writeDataValidationSettings(fSheet, style_1, style_2,
0805: "Less than or equal to 14:00", false, true, false);
0806: this .setCellFormat(fSheet, cellStyle_time);
0807: this .writeOtherSettings(fSheet, style_1, "-");
0808:
0809: System.out.println("done !");
0810:
0811: //"Text length" validation type
0812: System.out
0813: .print(" Create sheet for 'Text length' Data Validation type... ");
0814: fSheet = wb.createSheet("Text length");
0815: this .createHeaderRow(wb, 3, style_4);
0816:
0817: data_validation = new HSSFDataValidation((short) 1, (short) 0,
0818: (short) 1, (short) 0);
0819: data_validation
0820: .setDataValidationType(HSSFDataValidation.DATA_TYPE_TEXT_LENGTH);
0821: data_validation
0822: .setOperator(HSSFDataValidation.OPERATOR_BETWEEN);
0823: data_validation.setFirstFormula("2");
0824: data_validation.setSecondFormula("6");
0825: data_validation.createErrorBox("Invalid input !",
0826: "Something is wrong ; check condition !");
0827: data_validation.createPromptBox("Hi , dear user !",
0828: "So , you just selected me ! Thanks !");
0829: fSheet.addValidationData(data_validation);
0830: this .writeDataValidationSettings(fSheet, style_1, style_2,
0831: "Between 2 and 6 ", true, true, true);
0832: this .writeOtherSettings(fSheet, style_1,
0833: "Error box type = STOP");
0834:
0835: data_validation.setFirstRow((short) 2);
0836: data_validation.setLastRow((short) 2);
0837: data_validation.setEmptyCellAllowed(false);
0838: data_validation
0839: .setOperator(HSSFDataValidation.OPERATOR_NOT_BETWEEN);
0840: data_validation
0841: .setErrorStyle(HSSFDataValidation.ERROR_STYLE_INFO);
0842: fSheet.addValidationData(data_validation);
0843: this .writeDataValidationSettings(fSheet, style_1, style_2,
0844: "Not between 2 and 6 ", false, true, true);
0845: this .writeOtherSettings(fSheet, style_1,
0846: "Error box type = INFO");
0847:
0848: data_validation.setFirstRow((short) 3);
0849: data_validation.setLastRow((short) 3);
0850: data_validation.setEmptyCellAllowed(false);
0851: data_validation.setShowPromptBox(false);
0852: data_validation.setFirstFormula("3");
0853: data_validation.setSecondFormula(null);
0854: data_validation.setOperator(HSSFDataValidation.OPERATOR_EQUAL);
0855: data_validation
0856: .setErrorStyle(HSSFDataValidation.ERROR_STYLE_WARNING);
0857: fSheet.addValidationData(data_validation);
0858: this .writeDataValidationSettings(fSheet, style_1, style_2,
0859: "Equal to 3", false, false, true);
0860: this .writeOtherSettings(fSheet, style_1,
0861: "Error box type = WARNING");
0862:
0863: data_validation.setFirstRow((short) 4);
0864: data_validation.setLastRow((short) 4);
0865: data_validation.setEmptyCellAllowed(false);
0866: data_validation.setShowPromptBox(false);
0867: data_validation.setShowErrorBox(false);
0868: data_validation.setFirstFormula("3");
0869: data_validation.setSecondFormula(null);
0870: data_validation
0871: .setOperator(HSSFDataValidation.OPERATOR_NOT_EQUAL);
0872: fSheet.addValidationData(data_validation);
0873: this .writeDataValidationSettings(fSheet, style_1, style_2,
0874: "Not equal to 3", false, false, false);
0875: this .writeOtherSettings(fSheet, style_1, "-");
0876:
0877: data_validation.setFirstRow((short) 5);
0878: data_validation.setLastRow((short) 5);
0879: data_validation.setEmptyCellAllowed(true);
0880: data_validation.setShowPromptBox(false);
0881: data_validation.setShowErrorBox(false);
0882: data_validation.setFirstFormula("3");
0883: data_validation.setSecondFormula(null);
0884: data_validation
0885: .setOperator(HSSFDataValidation.OPERATOR_GREATER_THAN);
0886: fSheet.addValidationData(data_validation);
0887: this .writeDataValidationSettings(fSheet, style_1, style_2,
0888: "Greater than 3", true, false, false);
0889: this .writeOtherSettings(fSheet, style_1, "-");
0890:
0891: data_validation.setFirstRow((short) 6);
0892: data_validation.setLastRow((short) 6);
0893: data_validation.setEmptyCellAllowed(true);
0894: data_validation.setShowPromptBox(true);
0895: data_validation.setShowErrorBox(false);
0896: data_validation.setFirstFormula("3");
0897: data_validation.setSecondFormula(null);
0898: data_validation
0899: .setOperator(HSSFDataValidation.OPERATOR_LESS_THAN);
0900: fSheet.addValidationData(data_validation);
0901: this .writeDataValidationSettings(fSheet, style_1, style_2,
0902: "Less than 3", true, true, false);
0903: this .writeOtherSettings(fSheet, style_1, "-");
0904:
0905: data_validation.setFirstRow((short) 7);
0906: data_validation.setLastRow((short) 7);
0907: data_validation.setEmptyCellAllowed(true);
0908: data_validation.setShowPromptBox(false);
0909: data_validation
0910: .setErrorStyle(HSSFDataValidation.ERROR_STYLE_STOP);
0911: data_validation.setShowErrorBox(true);
0912: data_validation.setFirstFormula("4");
0913: data_validation.setSecondFormula(null);
0914: data_validation
0915: .setOperator(HSSFDataValidation.OPERATOR_GREATER_OR_EQUAL);
0916: fSheet.addValidationData(data_validation);
0917: this .writeDataValidationSettings(fSheet, style_1, style_2,
0918: "Greater than or equal to 4", true, false, true);
0919: this .writeOtherSettings(fSheet, style_1,
0920: "Error box type = STOP");
0921:
0922: data_validation.setFirstRow((short) 8);
0923: data_validation.setLastRow((short) 8);
0924: data_validation.setEmptyCellAllowed(false);
0925: data_validation.setShowPromptBox(true);
0926: data_validation.setShowErrorBox(false);
0927: data_validation.setFirstFormula("4");
0928: data_validation.setSecondFormula(null);
0929: data_validation
0930: .setOperator(HSSFDataValidation.OPERATOR_LESS_OR_EQUAL);
0931: fSheet.addValidationData(data_validation);
0932: this .writeDataValidationSettings(fSheet, style_1, style_2,
0933: "Less than or equal to 4", false, true, false);
0934: this .writeOtherSettings(fSheet, style_1, "-");
0935: System.out.println("done !");
0936:
0937: //Custom Validation type
0938: System.out
0939: .print(" Create sheet for 'Custom' Data Validation type ... ");
0940: fSheet = wb.createSheet("Custom");
0941: this .createHeaderRow(wb, 4, style_4);
0942:
0943: data_validation = new HSSFDataValidation((short) 1, (short) 0,
0944: (short) 1, (short) 0);
0945: data_validation
0946: .setDataValidationType(HSSFDataValidation.DATA_TYPE_FORMULA);
0947: data_validation.setFirstFormula("ISNUMBER($A2)");
0948: data_validation.setSecondFormula(null);
0949: data_validation.setShowPromptBox(true);
0950: data_validation.setShowErrorBox(true);
0951: data_validation.createErrorBox("Invalid input !",
0952: "Something is wrong ; check condition !");
0953: data_validation.createPromptBox("Hi , dear user !",
0954: "So , you just selected me ! Thanks !");
0955: fSheet.addValidationData(data_validation);
0956: this .writeDataValidationSettings(fSheet, style_1, style_2,
0957: "ISNUMBER(A2)", true, true, true);
0958: this .writeOtherSettings(fSheet, style_1,
0959: "Error box type = STOP");
0960:
0961: data_validation = new HSSFDataValidation((short) 2, (short) 0,
0962: (short) 2, (short) 0);
0963: data_validation
0964: .setDataValidationType(HSSFDataValidation.DATA_TYPE_FORMULA);
0965: data_validation.setFirstFormula("IF(SUM(A2:A3)=5,TRUE,FALSE)");
0966: data_validation.setSecondFormula(null);
0967: data_validation.setShowPromptBox(false);
0968: data_validation.setShowErrorBox(true);
0969: data_validation
0970: .setErrorStyle(HSSFDataValidation.ERROR_STYLE_WARNING);
0971: data_validation.createErrorBox("Invalid input !",
0972: "Something is wrong ; check condition !");
0973: data_validation.setEmptyCellAllowed(false);
0974: fSheet.addValidationData(data_validation);
0975: this .writeDataValidationSettings(fSheet, style_1, style_2,
0976: "IF(SUM(A2:A3)=5,TRUE,FALSE)", false, false, true);
0977: this .writeOtherSettings(fSheet, style_1,
0978: "Error box type = WARNING");
0979:
0980: System.out.println("done !");
0981:
0982: //so , everything it's ok for now ; it remains for you to open the file
0983: System.out
0984: .println("\n Everything it's ok since we've got so far -:) !\n"
0985: + " In order to complete the test , it remains for you to open the file \n"
0986: + " and see if there are four sheets , as described !");
0987: System.out.println(" File was saved in \"" + resultFile
0988: + "\"");
0989:
0990: FileOutputStream fileOut = new FileOutputStream(resultFile);
0991: wb.write(fileOut);
0992: fileOut.close();
0993: }
0994:
0995: private void createDVTypeRow(HSSFWorkbook wb, int sheetNo,
0996: HSSFCellStyle cellStyle, String strTypeDescription) {
0997: HSSFSheet sheet = wb.getSheetAt(sheetNo);
0998: HSSFRow row = sheet.createRow(sheet.getPhysicalNumberOfRows());
0999: row = sheet.createRow(sheet.getPhysicalNumberOfRows());
1000: sheet.addMergedRegion(new Region((short) (sheet
1001: .getPhysicalNumberOfRows() - 1), (short) 0,
1002: (short) (sheet.getPhysicalNumberOfRows() - 1),
1003: (short) 5));
1004: HSSFCell cell = row.createCell((short) 0);
1005: cell.setCellValue(strTypeDescription);
1006: cell.setCellStyle(cellStyle);
1007: row = sheet.createRow(sheet.getPhysicalNumberOfRows());
1008: }
1009:
1010: private void createDVDeescriptionRow(HSSFWorkbook wb, int sheetNo,
1011: HSSFCellStyle cellStyle, String strTypeDescription) {
1012: HSSFSheet sheet = wb.getSheetAt(sheetNo);
1013: HSSFRow row = sheet.getRow(sheet.getPhysicalNumberOfRows() - 1);
1014: sheet.addMergedRegion(new Region((short) (sheet
1015: .getPhysicalNumberOfRows() - 1), (short) 0,
1016: (short) (sheet.getPhysicalNumberOfRows() - 1),
1017: (short) 5));
1018: HSSFCell cell = row.createCell((short) 0);
1019: cell.setCellValue(strTypeDescription);
1020: cell.setCellStyle(cellStyle);
1021: row = sheet.createRow(sheet.getPhysicalNumberOfRows());
1022: }
1023:
1024: private void createHeaderRow(HSSFWorkbook wb, int sheetNo,
1025: HSSFCellStyle cellStyle) {
1026: HSSFSheet sheet = wb.getSheetAt(sheetNo);
1027: HSSFRow row = sheet.createRow(sheet.getPhysicalNumberOfRows());
1028: row.setHeight((short) 400);
1029: for (int i = 0; i < 6; i++) {
1030: row.createCell((short) i).setCellStyle(cellStyle);
1031: if (i == 2 || i == 3 || i == 4) {
1032: sheet.setColumnWidth((short) i, (short) 3500);
1033: } else if (i == 5) {
1034: sheet.setColumnWidth((short) i, (short) 10000);
1035: } else {
1036: sheet.setColumnWidth((short) i, (short) 8000);
1037: }
1038: }
1039: HSSFCell cell = row.getCell((short) 0);
1040: cell.setCellValue("Data validation cells");
1041: cell = row.getCell((short) 1);
1042: cell.setCellValue("Condition");
1043: cell = row.getCell((short) 2);
1044: cell.setCellValue("Allow blank");
1045: cell = row.getCell((short) 3);
1046: cell.setCellValue("Prompt box");
1047: cell = row.getCell((short) 4);
1048: cell.setCellValue("Error box");
1049: cell = row.getCell((short) 5);
1050: cell.setCellValue("Other settings");
1051: }
1052:
1053: private HSSFCellStyle createHeaderStyle(HSSFWorkbook wb) {
1054: HSSFFont font = wb.createFont();
1055: font.setColor(HSSFColor.WHITE.index);
1056: font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
1057:
1058: HSSFCellStyle cellStyle = wb.createCellStyle();
1059: cellStyle.setFillForegroundColor(HSSFColor.BLUE_GREY.index);
1060: cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
1061: cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
1062: cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
1063: cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
1064: cellStyle.setLeftBorderColor(HSSFColor.WHITE.index);
1065: cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
1066: cellStyle.setTopBorderColor(HSSFColor.WHITE.index);
1067: cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
1068: cellStyle.setRightBorderColor(HSSFColor.WHITE.index);
1069: cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
1070: cellStyle.setBottomBorderColor(HSSFColor.WHITE.index);
1071: cellStyle.setFont(font);
1072: return cellStyle;
1073: }
1074:
1075: private HSSFCellStyle createStyle(HSSFWorkbook wb, short h_align,
1076: short color, boolean bold) {
1077: HSSFFont font = wb.createFont();
1078: if (bold) {
1079: font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
1080: }
1081:
1082: HSSFCellStyle cellStyle = wb.createCellStyle();
1083: cellStyle.setFont(font);
1084: cellStyle.setFillForegroundColor(color);
1085: cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
1086: cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
1087: cellStyle.setAlignment(h_align);
1088: cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
1089: cellStyle.setLeftBorderColor(HSSFColor.BLACK.index);
1090: cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
1091: cellStyle.setTopBorderColor(HSSFColor.BLACK.index);
1092: cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
1093: cellStyle.setRightBorderColor(HSSFColor.BLACK.index);
1094: cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
1095: cellStyle.setBottomBorderColor(HSSFColor.BLACK.index);
1096:
1097: return cellStyle;
1098: }
1099:
1100: private HSSFCellStyle createStyle(HSSFWorkbook wb, short h_align) {
1101: return this .createStyle(wb, h_align, HSSFColor.WHITE.index,
1102: false);
1103: }
1104:
1105: private void writeDataValidationSettings(HSSFSheet sheet,
1106: HSSFCellStyle style_1, HSSFCellStyle style_2,
1107: String strCondition, boolean allowEmpty, boolean inputBox,
1108: boolean errorBox) {
1109: HSSFRow row = sheet.createRow(sheet.getPhysicalNumberOfRows());
1110: //condition's string
1111: HSSFCell cell = row.createCell((short) 1);
1112: cell.setCellStyle(style_1);
1113: cell.setCellValue(strCondition);
1114: //allow empty cells
1115: cell = row.createCell((short) 2);
1116: cell.setCellStyle(style_2);
1117: cell.setCellValue(((allowEmpty) ? "yes" : "no"));
1118: //show input box
1119: cell = row.createCell((short) 3);
1120: cell.setCellStyle(style_2);
1121: cell.setCellValue(((inputBox) ? "yes" : "no"));
1122: //show error box
1123: cell = row.createCell((short) 4);
1124: cell.setCellStyle(style_2);
1125: cell.setCellValue(((errorBox) ? "yes" : "no"));
1126: }
1127:
1128: private void setCellFormat(HSSFSheet sheet, HSSFCellStyle cell_style) {
1129: HSSFRow row = sheet.getRow(sheet.getPhysicalNumberOfRows() - 1);
1130: HSSFCell cell = row.createCell((short) 0);
1131: cell.setCellStyle(cell_style);
1132: }
1133:
1134: private void writeOtherSettings(HSSFSheet sheet,
1135: HSSFCellStyle style, String strStettings) {
1136: HSSFRow row = sheet.getRow(sheet.getPhysicalNumberOfRows() - 1);
1137: HSSFCell cell = row.createCell((short) 5);
1138: cell.setCellStyle(style);
1139: cell.setCellValue(strStettings);
1140: }
1141:
1142: public static void main(String[] args) {
1143: junit.textui.TestRunner.run(TestDataValidation.class);
1144: }
1145: }
|