path: root/src/testcases/org/apache/poi/hssf/usermodel/TestDataValidation.java

/* ====================================================================
   Licensed to the Apache Software Foundation (ASF) under one or more
   contributor license agreements.  See the NOTICE file distributed with
   this work for additional information regarding copyright ownership.
   The ASF licenses this file to You under the Apache License, Version 2.0
   (the "License"); you may not use this file except in compliance with
   the License.  You may obtain a copy of the License at

       http://www.apache.org/licenses/LICENSE-2.0

   Unless required by applicable law or agreed to in writing, software
   distributed under the License is distributed on an "AS IS" BASIS,
   WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
   See the License for the specific language governing permissions and
   limitations under the License.
==================================================================== */

package org.apache.poi.hssf.usermodel;

import static org.junit.Assert.assertEquals;
import static org.junit.Assert.assertTrue;
import static org.junit.Assert.fail;

import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.PrintStream;
import java.util.List;

import org.apache.poi.hssf.HSSFITestDataProvider;
import org.apache.poi.hssf.HSSFTestDataSamples;
import org.apache.poi.hssf.record.DVRecord;
import org.apache.poi.ss.usermodel.BaseTestDataValidation;
import org.apache.poi.ss.usermodel.DataValidation;
import org.apache.poi.ss.usermodel.DataValidation.ErrorStyle;
import org.apache.poi.ss.usermodel.DataValidationConstraint;
import org.apache.poi.ss.usermodel.DataValidationConstraint.OperatorType;
import org.apache.poi.ss.usermodel.DataValidationConstraint.ValidationType;
import org.apache.poi.ss.usermodel.DataValidationHelper;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.junit.Test;

/**
 * Class for testing Excel's data validation mechanism
 *
 * @author Dragos Buleandra ( dragos.buleandra@trade2b.ro )
 */
public final class TestDataValidation extends BaseTestDataValidation {

    public TestDataValidation(){
        super(HSSFITestDataProvider.instance);
    }


	public void assertDataValidation(Workbook wb) {

		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));
	  
    }
  
	@Test
    public void testAddToExistingSheet() throws Exception {

		// 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;
		Sheet sheet = wb.getSheetAt(0);
		DataValidationHelper dataValidationHelper = sheet.getDataValidationHelper();
		DataValidationConstraint dc = dataValidationHelper.createIntegerConstraint(OperatorType.EQUAL, "42", null);
		DataValidation dv = dataValidationHelper.createValidation(dc,new CellRangeAddressList(dvRow, dvRow, 0, 0));
		
		dv.setEmptyCellAllowed(false);
		dv.setErrorStyle(ErrorStyle.STOP);
		dv.setShowPromptBox(true);
		dv.createErrorBox("Xxx", "Yyy");
		dv.setSuppressDropDownArrow(true);

		sheet.addValidationData(dv);
		
		ByteArrayOutputStream baos = new ByteArrayOutputStream();
		wb.write(baos);
		
		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) {
			fail("Identified bug 45519");
		}
		assertEquals(DVRecord.sid, nextSid);
		
		wb.close();
	}
	
	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;
	}

	@Test
    public void testGetDataValidationsAny() throws Exception {
        HSSFWorkbook wb = new HSSFWorkbook();
        HSSFSheet sheet = wb.createSheet();
        List<HSSFDataValidation> list = sheet.getDataValidations();
        assertEquals(0, list.size());

        DataValidationHelper dataValidationHelper = sheet.getDataValidationHelper();
        DataValidationConstraint constraint = dataValidationHelper.createNumericConstraint(ValidationType.ANY,
                OperatorType.IGNORED, null, null);
        CellRangeAddressList addressList = new CellRangeAddressList(1, 2, 3, 4);
        DataValidation validation = dataValidationHelper.createValidation(constraint, addressList);
        validation.setEmptyCellAllowed(true);
        validation.createErrorBox("error-title", "error-text");
        validation.createPromptBox("prompt-title", "prompt-text");
        sheet.addValidationData(validation);

        list = sheet.getDataValidations(); // <-- works
        assertEquals(1, list.size());

        HSSFDataValidation dv = list.get(0);
        {
            CellRangeAddressList regions = dv.getRegions();
            assertEquals(1, regions.countRanges());

            CellRangeAddress address = regions.getCellRangeAddress(0);
            assertEquals(1, address.getFirstRow());
            assertEquals(2, address.getLastRow());
            assertEquals(3, address.getFirstColumn());
            assertEquals(4, address.getLastColumn());
        }
        assertEquals(true, dv.getEmptyCellAllowed());
        assertEquals(false, dv.getSuppressDropDownArrow());
        assertEquals(true, dv.getShowErrorBox());
        assertEquals("error-title", dv.getErrorBoxTitle());
        assertEquals("error-text", dv.getErrorBoxText());
        assertEquals(true, dv.getShowPromptBox());
        assertEquals("prompt-title", dv.getPromptBoxTitle());
        assertEquals("prompt-text", dv.getPromptBoxText());

        DataValidationConstraint c = dv.getValidationConstraint();
        assertEquals(ValidationType.ANY, c.getValidationType());
        assertEquals(OperatorType.IGNORED, c.getOperator());
        
        wb.close();
    }

	@Test
    public void testGetDataValidationsIntegerFormula() throws Exception {
        HSSFWorkbook wb = new HSSFWorkbook();
        HSSFSheet sheet = wb.createSheet();
        List<HSSFDataValidation> list = sheet.getDataValidations();
        assertEquals(0, list.size());

        DataValidationHelper dataValidationHelper = sheet.getDataValidationHelper();
        DataValidationConstraint constraint = dataValidationHelper.createIntegerConstraint(OperatorType.BETWEEN, "=A2",
                "=A3");
        CellRangeAddressList addressList = new CellRangeAddressList(0, 0, 0, 0);
        DataValidation validation = dataValidationHelper.createValidation(constraint, addressList);
        sheet.addValidationData(validation);

        list = sheet.getDataValidations(); // <-- works
        assertEquals(1, list.size());

        HSSFDataValidation dv = list.get(0);
        DVConstraint c = dv.getConstraint();
        assertEquals(ValidationType.INTEGER, c.getValidationType());
        assertEquals(OperatorType.BETWEEN, c.getOperator());
        assertEquals("A2", c.getFormula1());
        assertEquals("A3", c.getFormula2());
        assertEquals(null, c.getValue1());
        assertEquals(null, c.getValue2());
        
        wb.close();
    }

	@Test
    public void testGetDataValidationsIntegerValue() throws Exception {
        HSSFWorkbook wb = new HSSFWorkbook();
        HSSFSheet sheet = wb.createSheet();
        List<HSSFDataValidation> list = sheet.getDataValidations();
        assertEquals(0, list.size());

        DataValidationHelper dataValidationHelper = sheet.getDataValidationHelper();
        DataValidationConstraint constraint = dataValidationHelper.createIntegerConstraint(OperatorType.BETWEEN, "100",
                "200");
        CellRangeAddressList addressList = new CellRangeAddressList(0, 0, 0, 0);
        DataValidation validation = dataValidationHelper.createValidation(constraint, addressList);
        sheet.addValidationData(validation);

        list = sheet.getDataValidations(); // <-- works
        assertEquals(1, list.size());

        HSSFDataValidation dv = list.get(0);
        DVConstraint c = dv.getConstraint();
        assertEquals(ValidationType.INTEGER, c.getValidationType());
        assertEquals(OperatorType.BETWEEN, c.getOperator());
        assertEquals(null, c.getFormula1());
        assertEquals(null, c.getFormula2());
        assertEquals(new Double("100"), c.getValue1());
        assertEquals(new Double("200"), c.getValue2());
        
        wb.close();
    }

	@Test
    public void testGetDataValidationsDecimal() throws Exception {
        HSSFWorkbook wb = new HSSFWorkbook();
        HSSFSheet sheet = wb.createSheet();
        List<HSSFDataValidation> list = sheet.getDataValidations();
        assertEquals(0, list.size());

        DataValidationHelper dataValidationHelper = sheet.getDataValidationHelper();
        DataValidationConstraint constraint = dataValidationHelper.createDecimalConstraint(OperatorType.BETWEEN, "=A2",
                "200");
        CellRangeAddressList addressList = new CellRangeAddressList(0, 0, 0, 0);
        DataValidation validation = dataValidationHelper.createValidation(constraint, addressList);
        sheet.addValidationData(validation);

        list = sheet.getDataValidations(); // <-- works
        assertEquals(1, list.size());

        HSSFDataValidation dv = list.get(0);
        DVConstraint c = dv.getConstraint();
        assertEquals(ValidationType.DECIMAL, c.getValidationType());
        assertEquals(OperatorType.BETWEEN, c.getOperator());
        assertEquals("A2", c.getFormula1());
        assertEquals(null, c.getFormula2());
        assertEquals(null, c.getValue1());
        assertEquals(new Double("200"), c.getValue2());
        
        wb.close();
    }

	@Test
    public void testGetDataValidationsDate() throws Exception {
        HSSFWorkbook wb = new HSSFWorkbook();
        HSSFSheet sheet = wb.createSheet();
        List<HSSFDataValidation> list = sheet.getDataValidations();
        assertEquals(0, list.size());

        DataValidationHelper dataValidationHelper = sheet.getDataValidationHelper();
        DataValidationConstraint constraint = dataValidationHelper.createDateConstraint(OperatorType.EQUAL,
                "2014/10/25", null, null);
        CellRangeAddressList addressList = new CellRangeAddressList(0, 0, 0, 0);
        DataValidation validation = dataValidationHelper.createValidation(constraint, addressList);
        sheet.addValidationData(validation);

        list = sheet.getDataValidations(); // <-- works
        assertEquals(1, list.size());

        HSSFDataValidation dv = list.get(0);
        DVConstraint c = dv.getConstraint();
        assertEquals(ValidationType.DATE, c.getValidationType());
        assertEquals(OperatorType.EQUAL, c.getOperator());
        assertEquals(null, c.getFormula1());
        assertEquals(null, c.getFormula2());
        assertEquals(DateUtil.getExcelDate(DateUtil.parseYYYYMMDDDate("2014/10/25")), c.getValue1(), 0);
        assertEquals(null, c.getValue2());
        
        wb.close();
    }

	@Test
    public void testGetDataValidationsListExplicit() throws Exception {
        HSSFWorkbook wb = new HSSFWorkbook();
        HSSFSheet sheet = wb.createSheet();
        List<HSSFDataValidation> list = sheet.getDataValidations();
        assertEquals(0, list.size());

        DataValidationHelper dataValidationHelper = sheet.getDataValidationHelper();
        DataValidationConstraint constraint = dataValidationHelper.createExplicitListConstraint(new String[] { "aaa",
                "bbb", "ccc" });
        CellRangeAddressList addressList = new CellRangeAddressList(0, 0, 0, 0);
        DataValidation validation = dataValidationHelper.createValidation(constraint, addressList);
        validation.setSuppressDropDownArrow(true);
        sheet.addValidationData(validation);

        list = sheet.getDataValidations(); // <-- works
        assertEquals(1, list.size());

        HSSFDataValidation dv = list.get(0);
        assertEquals(true, dv.getSuppressDropDownArrow());

        DVConstraint c = dv.getConstraint();
        assertEquals(ValidationType.LIST, c.getValidationType());
        assertEquals(null, c.getFormula1());
        assertEquals(null, c.getFormula2());
        assertEquals(null, c.getValue1());
        assertEquals(null, c.getValue2());
        String[] values = c.getExplicitListValues();
        assertEquals(3, values.length);
        assertEquals("aaa", values[0]);
        assertEquals("bbb", values[1]);
        assertEquals("ccc", values[2]);
        
        wb.close();
    }

	@Test
    public void testGetDataValidationsListFormula() throws Exception {
        HSSFWorkbook wb = new HSSFWorkbook();
        HSSFSheet sheet = wb.createSheet();
        List<HSSFDataValidation> list = sheet.getDataValidations();
        assertEquals(0, list.size());

        DataValidationHelper dataValidationHelper = sheet.getDataValidationHelper();
        DataValidationConstraint constraint = dataValidationHelper.createFormulaListConstraint("A2");
        CellRangeAddressList addressList = new CellRangeAddressList(0, 0, 0, 0);
        DataValidation validation = dataValidationHelper.createValidation(constraint, addressList);
        validation.setSuppressDropDownArrow(true);
        sheet.addValidationData(validation);

        list = sheet.getDataValidations(); // <-- works
        assertEquals(1, list.size());

        HSSFDataValidation dv = list.get(0);
        assertEquals(true, dv.getSuppressDropDownArrow());

        DVConstraint c = dv.getConstraint();
        assertEquals(ValidationType.LIST, c.getValidationType());
        assertEquals("A2", c.getFormula1());
        assertEquals(null, c.getFormula2());
        assertEquals(null, c.getValue1());
        assertEquals(null, c.getValue2());
        
        wb.close();
    }

	@Test
    public void testGetDataValidationsFormula() throws Exception {
        HSSFWorkbook wb = new HSSFWorkbook();
        HSSFSheet sheet = wb.createSheet();
        List<HSSFDataValidation> list = sheet.getDataValidations();
        assertEquals(0, list.size());

        DataValidationHelper dataValidationHelper = sheet.getDataValidationHelper();
        DataValidationConstraint constraint = dataValidationHelper.createCustomConstraint("A2:A3");
        CellRangeAddressList addressList = new CellRangeAddressList(0, 0, 0, 0);
        DataValidation validation = dataValidationHelper.createValidation(constraint, addressList);
        sheet.addValidationData(validation);

        list = sheet.getDataValidations(); // <-- works
        assertEquals(1, list.size());

        HSSFDataValidation dv = list.get(0);
        DVConstraint c = dv.getConstraint();
        assertEquals(ValidationType.FORMULA, c.getValidationType());
        assertEquals("A2:A3", c.getFormula1());
        assertEquals(null, c.getFormula2());
        assertEquals(null, c.getValue1());
        assertEquals(null, c.getValue2());
        wb.close();
    }
}