testdatavalidation.java

来自「EXCEL read and write」· Java 代码 · 共 646 行 · 第 1/2 页

		ValidationAdder va = wf.createValidationAdder(null, VT.INTEGER);
		va.addValidation(OP.BETWEEN, "2", "6", ES.STOP, "Between 2 and 6 ", "Error box type = STOP", true, true, true);
		va.addValidation(OP.NOT_BETWEEN, "2", "6", ES.INFO, "Not between 2 and 6 ", "Error box type = INFO", false, true, true);
		va.addValidation(OP.EQUAL, "=3+2", null, ES.WARNING, "Equal to (3+2)", "Error box type = WARNING", false, false, true);
		va.addValidation(OP.NOT_EQUAL, "3", null, ES.WARNING, "Not equal to 3", "-", false, false, false);
		va.addValidation(OP.GREATER_THAN, "3", null, ES.WARNING, "Greater than 3", "-", true, false, false);
		va.addValidation(OP.LESS_THAN, "3", null, ES.WARNING, "Less than 3", "-", true, true, false);
		va.addValidation(OP.GREATER_OR_EQUAL, "4", null, ES.STOP, "Greater than or equal to 4", "Error box type = STOP", true, false, true);
		va.addValidation(OP.LESS_OR_EQUAL, "4", null, ES.STOP, "Less than or equal to 4", "-", false, true, false);

		// "Decimal" validation type
		wf.createDVTypeRow("Decimal");
		wf.createHeaderRow();

		va = wf.createValidationAdder(null, VT.DECIMAL);
		va.addValidation(OP.BETWEEN, "2", "6", ES.STOP, "Between 2 and 6 ", "Error box type = STOP", true, true, true);
		va.addValidation(OP.NOT_BETWEEN, "2", "6", ES.INFO, "Not between 2 and 6 ", "Error box type = INFO", false, true, true);
		va.addValidation(OP.EQUAL, "3", null, ES.WARNING, "Equal to 3", "Error box type = WARNING", false, false, true);
		va.addValidation(OP.NOT_EQUAL, "3", null, ES.WARNING, "Not equal to 3", "-", false, false, false);
		va.addValidation(OP.GREATER_THAN, "=12/6", null, ES.WARNING, "Greater than (12/6)", "-", true, false, false);
		va.addValidation(OP.LESS_THAN, "3", null, ES.WARNING, "Less than 3", "-", true, true, false);
		va.addValidation(OP.GREATER_OR_EQUAL, "4", null, ES.STOP, "Greater than or equal to 4", "Error box type = STOP", true, false, true);
		va.addValidation(OP.LESS_OR_EQUAL, "4", null, ES.STOP, "Less than or equal to 4", "-", false, true, false);
	}
	
	private static void addListValidations(WorkbookFormatter wf, HSSFWorkbook wb) {
		final 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 "
		+ "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 "
		+ "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 "
		+ "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 ";
		final String dataSheetName = "list_data";
		// "List" Data Validation type
		HSSFSheet fSheet = wf.createSheet("Lists");
		HSSFSheet dataSheet = wb.createSheet(dataSheetName);


		wf.createDVTypeRow("Explicit lists - list items are explicitly provided");
		wf.createDVDescriptionRow("Disadvantage - sum of item's length should be less than 255 characters");
		wf.createHeaderRow();

		ValidationAdder va = wf.createValidationAdder(null, VT.LIST);
		String listValsDescr = "POIFS,HSSF,HWPF,HPSF";
		String[] listVals = listValsDescr.split(",");
		va.addListValidation(listVals, null, listValsDescr, false, false);
		va.addListValidation(listVals, null, listValsDescr, false, true);
		va.addListValidation(listVals, null, listValsDescr, true, false);
		va.addListValidation(listVals, null, listValsDescr, true, true);
		
		
		
		wf.createDVTypeRow("Reference lists - list items are taken from others cells");
		wf.createDVDescriptionRow("Advantage - no restriction regarding the sum of item's length");
		wf.createHeaderRow();
		va = wf.createValidationAdder(null, VT.LIST);
		String strFormula = "$A$30:$A$39";
		va.addListValidation(null, strFormula, strFormula, false, false);
		
		strFormula = dataSheetName + "!$A$1:$A$10";
		va.addListValidation(null, strFormula, strFormula, false, false);
		HSSFName namedRange = wb.createName();
		namedRange.setNameName("myName");
		namedRange.setReference(dataSheetName + "!$A$2:$A$7");
		strFormula = "myName";
		va.addListValidation(null, strFormula, strFormula, false, false);
		strFormula = "offset(myName, 2, 1, 4, 2)"; // Note about last param '2': 
		// - Excel expects single row or single column when entered in UI, but process this OK otherwise
		va.addListValidation(null, strFormula, strFormula, false, false);
		
		// add list data on same sheet
		for (int i = 0; i < 10; i++) {
			HSSFRow currRow = fSheet.createRow(i + 29);
			setCellValue(currRow.createCell(0), cellStrValue);
		}
		// add list data on another sheet
		for (int i = 0; i < 10; i++) {
			HSSFRow currRow = dataSheet.createRow(i + 0);
			setCellValue(currRow.createCell(0), "Data a" + i);
			setCellValue(currRow.createCell(1), "Data b" + i);
			setCellValue(currRow.createCell(2), "Data c" + i);
		}
	}

	private static void addDateTimeValidations(WorkbookFormatter wf, HSSFWorkbook wb) {
		wf.createSheet("Dates and Times");

		HSSFDataFormat dataFormat = wb.createDataFormat();
		short fmtDate = dataFormat.getFormat("m/d/yyyy");
		short fmtTime = dataFormat.getFormat("h:mm");
		HSSFCellStyle cellStyle_date = wb.createCellStyle();
		cellStyle_date.setDataFormat(fmtDate);
		HSSFCellStyle cellStyle_time = wb.createCellStyle();
		cellStyle_time.setDataFormat(fmtTime);

		wf.createDVTypeRow("Date ( cells are already formated as date - m/d/yyyy)");
		wf.createHeaderRow();

		ValidationAdder va = wf.createValidationAdder(cellStyle_date, VT.DATE);
		va.addValidation(OP.BETWEEN,     "2004/01/02", "2004/01/06", ES.STOP, "Between 1/2/2004 and 1/6/2004 ", "Error box type = STOP", true, true, true);
		va.addValidation(OP.NOT_BETWEEN, "2004/01/01", "2004/01/06", ES.INFO, "Not between 1/2/2004 and 1/6/2004 ", "Error box type = INFO", false, true, true);
		va.addValidation(OP.EQUAL,       "2004/03/02", null,       ES.WARNING, "Equal to 3/2/2004", "Error box type = WARNING", false, false, true);
		va.addValidation(OP.NOT_EQUAL,   "2004/03/02", null,       ES.WARNING, "Not equal to 3/2/2004", "-", false, false, false);
		va.addValidation(OP.GREATER_THAN,"=DATEVALUE(\"4-Jul-2001\")", null,       ES.WARNING, "Greater than DATEVALUE('4-Jul-2001')", "-", true, false, false);
		va.addValidation(OP.LESS_THAN,   "2004/03/02", null,       ES.WARNING, "Less than 3/2/2004", "-", true, true, false);
		va.addValidation(OP.GREATER_OR_EQUAL, "2004/03/02", null,       ES.STOP, "Greater than or equal to 3/2/2004", "Error box type = STOP", true, false, true);
		va.addValidation(OP.LESS_OR_EQUAL, "2004/03/04", null,       ES.STOP, "Less than or equal to 3/4/2004", "-", false, true, false);

		// "Time" validation type
		wf.createDVTypeRow("Time ( cells are already formated as time - h:mm)");
		wf.createHeaderRow();

		va = wf.createValidationAdder(cellStyle_time, VT.TIME);
		va.addValidation(OP.BETWEEN,     "12:00", "16:00", ES.STOP, "Between 12:00 and 16:00 ", "Error box type = STOP", true, true, true);
		va.addValidation(OP.NOT_BETWEEN, "12:00", "16:00", ES.INFO, "Not between 12:00 and 16:00 ", "Error box type = INFO", false, true, true);
		va.addValidation(OP.EQUAL,       "13:35", null,    ES.WARNING, "Equal to 13:35", "Error box type = WARNING", false, false, true);
		va.addValidation(OP.NOT_EQUAL,   "13:35", null,    ES.WARNING, "Not equal to 13:35", "-", false, false, false);
		va.addValidation(OP.GREATER_THAN,"12:00", null,    ES.WARNING, "Greater than 12:00", "-", true, false, false);
		va.addValidation(OP.LESS_THAN,   "=1/2", null,    ES.WARNING, "Less than (1/2) -> 12:00", "-", true, true, false);
		va.addValidation(OP.GREATER_OR_EQUAL, "14:00", null,    ES.STOP, "Greater than or equal to 14:00", "Error box type = STOP", true, false, true);
		va.addValidation(OP.LESS_OR_EQUAL,"14:00", null,    ES.STOP, "Less than or equal to 14:00", "-", false, true, false);
	}

	private static void addTextLengthValidations(WorkbookFormatter wf) {
		wf.createSheet("Text lengths");
		wf.createHeaderRow();

		ValidationAdder va = wf.createValidationAdder(null, VT.TEXT_LENGTH);
		va.addValidation(OP.BETWEEN, "2", "6", ES.STOP, "Between 2 and 6 ", "Error box type = STOP", true, true, true);
		va.addValidation(OP.NOT_BETWEEN, "2", "6", ES.INFO, "Not between 2 and 6 ", "Error box type = INFO", false, true, true);
		va.addValidation(OP.EQUAL, "3", null, ES.WARNING, "Equal to 3", "Error box type = WARNING", false, false, true);
		va.addValidation(OP.NOT_EQUAL, "3", null, ES.WARNING, "Not equal to 3", "-", false, false, false);
		va.addValidation(OP.GREATER_THAN, "3", null, ES.WARNING, "Greater than 3", "-", true, false, false);
		va.addValidation(OP.LESS_THAN, "3", null, ES.WARNING, "Less than 3", "-", true, true, false);
		va.addValidation(OP.GREATER_OR_EQUAL, "4", null, ES.STOP, "Greater than or equal to 4", "Error box type = STOP", true, false, true);
		va.addValidation(OP.LESS_OR_EQUAL, "4", null, ES.STOP, "Less than or equal to 4", "-", false, true, false);
	}
	
	public void testDataValidation() {
		log("\nTest no. 2 - Test Excel's Data validation mechanism");
		HSSFWorkbook wb = new HSSFWorkbook();
		WorkbookFormatter wf = new WorkbookFormatter(wb);

		log("    Create sheet for Data Validation's number types ... ");
		addSimpleNumericValidations(wf);
		log("done !");

		log("    Create sheet for 'List' Data Validation type ... ");
		addListValidations(wf, wb);
		log("done !");
		
		log("    Create sheet for 'Date' and 'Time' Data Validation types ... ");
		addDateTimeValidations(wf, wb);
		log("done !");

		log("    Create sheet for 'Text length' Data Validation type... ");
		addTextLengthValidations(wf);
		log("done !");

		// Custom Validation type
		log("    Create sheet for 'Custom' Data Validation type ... ");
		addCustomValidations(wf);
		log("done !");

		ByteArrayOutputStream baos = new ByteArrayOutputStream(22000);
		try {
			wb.write(baos);
			baos.close();
		} catch (IOException e) {
			throw new RuntimeException(e);
		}
		byte[] generatedContent = baos.toByteArray();
		boolean isSame;
		if (false) {
			// TODO - add proof spreadsheet and compare
			InputStream proofStream = HSSFTestDataSamples.openSampleFileStream("TestDataValidation.xls");
			isSame = compareStreams(proofStream, generatedContent);
		}
		isSame = true;
		
		if (isSame) {
			return;
		}
		File tempDir = new File(System.getProperty("java.io.tmpdir"));
		File generatedFile = new File(tempDir, "GeneratedTestDataValidation.xls");
		try {
			FileOutputStream fileOut = new FileOutputStream(generatedFile);
			fileOut.write(generatedContent);
			fileOut.close();
		} catch (IOException e) {
			throw new RuntimeException(e);
		}
	
		PrintStream ps = System.out;
	
		ps.println("This test case has failed because the generated file differs from proof copy '" 
				); // TODO+ proofFile.getAbsolutePath() + "'.");
		ps.println("The cause is usually a change to this test, or some common spreadsheet generation code.  "
				+ "The developer has to decide whether the changes were wanted or unwanted.");
		ps.println("If the changes to the generated version were unwanted, "
				+ "make the fix elsewhere (do not modify this test or the proof spreadsheet to get the test working).");
		ps.println("If the changes were wanted, make sure to open the newly generated file in Excel "
				+ "and verify it manually.  The new proof file should be submitted after it is verified to be correct.");
		ps.println("");
		ps.println("One other possible (but less likely) cause of a failed test is a problem in the "
				+ "comparison logic used here. Perhaps some extra file regions need to be ignored.");
		ps.println("The generated file has been saved to '" + generatedFile.getAbsolutePath() + "' for manual inspection.");
	
		fail("Generated file differs from proof copy.  See sysout comments for details on how to fix.");
		
	}
	
	private static boolean compareStreams(InputStream isA, byte[] generatedContent) {

		InputStream isB = new ByteArrayInputStream(generatedContent);

		// The allowable regions where the generated file can differ from the 
		// proof should be small (i.e. much less than 1K)
		int[] allowableDifferenceRegions = { 
				0x0228, 16,  // a region of the file containing the OS username
				0x506C, 8,   // See RootProperty (super fields _seconds_2 and _days_2)
		};
		int[] diffs = StreamUtility.diffStreams(isA, isB, allowableDifferenceRegions);
		if (diffs == null) {
			return true;
		}
		System.err.println("Diff from proof: ");
		for (int i = 0; i < diffs.length; i++) {
			System.err.println("diff at offset: 0x" + Integer.toHexString(diffs[i]));
		}
		return false;
	}
  




  /* package */ static void setCellValue(HSSFCell cell, String text) {
	  cell.setCellValue(new HSSFRichTextString(text));
	  
  }
  
	public void testAddToExistingSheet() {

		// dvEmpty.xls is a simple one sheet workbook.  With a DataValidations header record but no 
		// DataValidations.  It's important that the example has one SHEETPROTECTION record.
		// Such a workbook can be created in Excel (2007) by adding datavalidation for one cell
		// and then deleting the row that contains the cell.
		HSSFWorkbook wb = HSSFTestDataSamples.openSampleWorkbook("dvEmpty.xls");  
		int dvRow = 0;
		HSSFSheet sheet = wb.getSheetAt(0);
		DVConstraint dc = DVConstraint.createNumericConstraint(VT.INTEGER, OP.EQUAL, "42", null);
		HSSFDataValidation dv = new HSSFDataValidation(new CellRangeAddressList(dvRow, dvRow, 0, 0), dc);
		
		dv.setEmptyCellAllowed(false);
		dv.setErrorStyle(ES.STOP);
		dv.setShowPromptBox(true);
		dv.createErrorBox("Xxx", "Yyy");
		dv.setSuppressDropDownArrow(true);

		sheet.addValidationData(dv);
		
		ByteArrayOutputStream baos = new ByteArrayOutputStream();
		try {
			wb.write(baos);
		} catch (IOException e) {
			throw new RuntimeException(e);
		}
		
		byte[] wbData = baos.toByteArray();
		
		if (false) { // TODO (Jul 2008) fix EventRecordFactory to process unknown records, (and DV records for that matter)

			ERFListener erfListener = null; // new MyERFListener();
			EventRecordFactory erf = new EventRecordFactory(erfListener, null);
			try {
				POIFSFileSystem fs = new POIFSFileSystem(new ByteArrayInputStream(baos.toByteArray()));
				erf.processRecords(fs.createDocumentInputStream("Workbook"));
			} catch (RecordFormatException e) {
				throw new RuntimeException(e);
			} catch (IOException e) {
				throw new RuntimeException(e);
			}
		}
		// else verify record ordering by navigating the raw bytes
		
		byte[] dvHeaderRecStart= { (byte)0xB2, 0x01, 0x12, 0x00, };
		int dvHeaderOffset = findIndex(wbData, dvHeaderRecStart);
		assertTrue(dvHeaderOffset > 0);
		int nextRecIndex = dvHeaderOffset + 22;
		int nextSid 
			= ((wbData[nextRecIndex + 0] << 0) & 0x00FF) 
			+ ((wbData[nextRecIndex + 1] << 8) & 0xFF00)
			;
		// nextSid should be for a DVRecord.  If anything comes between the DV header record 
		// and the DV records, Excel will not be able to open the workbook without error.
		
		if (nextSid == 0x0867) {
			throw new AssertionFailedError("Identified bug 45519");
		}
		assertEquals(DVRecord.sid, nextSid);
	}
	private int findIndex(byte[] largeData, byte[] searchPattern) {
		byte firstByte = searchPattern[0];
		for (int i = 0; i < largeData.length; i++) {
			if(largeData[i] != firstByte) {
				continue;
			}
			boolean match = true;
			for (int j = 1; j < searchPattern.length; j++) {
				if(searchPattern[j] != largeData[i+j]) {
					match = false;
					break;
				}
			}
			if (match) {
				return i;
			}
		}
		return -1;
	}
}