testdatavalidation.java

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

/* ====================================================================
   Copyright 2002-2004   Apache Software Foundation

   Licensed 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 java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.PrintStream;

import junit.framework.AssertionFailedError;
import junit.framework.TestCase;

import org.apache.poi.hssf.HSSFTestDataSamples;
import org.apache.poi.hssf.eventmodel.ERFListener;
import org.apache.poi.hssf.eventmodel.EventRecordFactory;
import org.apache.poi.hssf.record.DVRecord;
import org.apache.poi.hssf.record.RecordFormatException;
import org.apache.poi.hssf.util.CellRangeAddress;
import org.apache.poi.hssf.util.CellRangeAddressList;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;

/**
 * Class for testing Excel's data validation mechanism
 *
 * @author Dragos Buleandra ( [e-mail] )
 */
public final class TestDataValidation extends TestCase {

	/** Convenient access to ERROR_STYLE constants */
	/*package*/ static final HSSFDataValidation.ErrorStyle ES = null;
	/** Convenient access to OPERATOR constants */
	/*package*/ static final DVConstraint.ValidationType VT = null;
	/** Convenient access to OPERATOR constants */
	/*package*/ static final DVConstraint.OperatorType OP = null;

	private static void log(String msg) {
		if (false) { // successful tests should be silent
			System.out.println(msg);
		}      
	}
  
	private static final class ValidationAdder {
	  
		private final HSSFCellStyle _style_1;
		private final HSSFCellStyle _style_2;
		private  final int _validationType;
		private final HSSFSheet _sheet;
		private int _currentRowIndex;
		private final HSSFCellStyle _cellStyle;

		public ValidationAdder(HSSFSheet fSheet, HSSFCellStyle style_1, HSSFCellStyle style_2,
				HSSFCellStyle cellStyle, int validationType) {
			_sheet = fSheet;
			_style_1 = style_1;
			_style_2 = style_2;
			_cellStyle = cellStyle;
			_validationType = validationType;
			_currentRowIndex = fSheet.getPhysicalNumberOfRows();
		}
		public void addValidation(int operatorType, String firstFormula, String secondFormula,
				int errorStyle, String ruleDescr, String promptDescr, 
				boolean allowEmpty, boolean inputBox, boolean errorBox) {
			String[] explicitListValues = null;
			
			addValidationInternal(operatorType, firstFormula, secondFormula, errorStyle, ruleDescr,
					promptDescr, allowEmpty, inputBox, errorBox, true,
					explicitListValues);
		}

		private void addValidationInternal(int operatorType, String firstFormula,
				String secondFormula, int errorStyle, String ruleDescr, String promptDescr,
				boolean allowEmpty, boolean inputBox, boolean errorBox, boolean suppressDropDown,
				String[] explicitListValues) {
			int rowNum = _currentRowIndex++;

			DVConstraint dc = createConstraint(operatorType, firstFormula, secondFormula, explicitListValues);

			HSSFDataValidation dv = new HSSFDataValidation(new CellRangeAddressList(rowNum, rowNum, 0, 0), dc);
			
			dv.setEmptyCellAllowed(allowEmpty);
			dv.setErrorStyle(errorStyle);
			dv.createErrorBox("Invalid Input", "Something is wrong - check condition!");
			dv.createPromptBox("Validated Cell", "Allowable values have been restricted");

			dv.setShowPromptBox(inputBox);
			dv.setShowErrorBox(errorBox);
			dv.setSuppressDropDownArrow(suppressDropDown);
			
			
			_sheet.addValidationData(dv);
			writeDataValidationSettings(_sheet, _style_1, _style_2, ruleDescr, allowEmpty,
					inputBox, errorBox);
			if (_cellStyle != null) {
				HSSFRow row = _sheet.getRow(_sheet.getPhysicalNumberOfRows() - 1);
				HSSFCell cell = row.createCell(0);
				cell.setCellStyle(_cellStyle);
			}
			writeOtherSettings(_sheet, _style_1, promptDescr);
		}
		private DVConstraint createConstraint(int operatorType, String firstFormula,
				String secondFormula, String[] explicitListValues) {
			if (_validationType == VT.LIST) {
				if (explicitListValues != null) {
					return DVConstraint.createExplicitListConstraint(explicitListValues);
				}
				return DVConstraint.createFormulaListConstraint(firstFormula);
			}
			if (_validationType == VT.TIME) {
				return DVConstraint.createTimeConstraint(operatorType, firstFormula, secondFormula);
			}
			if (_validationType == VT.DATE) {
				return DVConstraint.createDateConstraint(operatorType, firstFormula, secondFormula, null);
			}
			if (_validationType == VT.FORMULA) {
				return DVConstraint.createCustomFormulaConstraint(firstFormula);
			}
			return DVConstraint.createNumericConstraint(_validationType, operatorType, firstFormula, secondFormula);
		}
		/**
		 * writes plain text values into cells in a tabular format to form comments readable from within 
		 * the spreadsheet.
		 */
		private static void writeDataValidationSettings(HSSFSheet sheet, HSSFCellStyle style_1,
				HSSFCellStyle style_2, String strCondition, boolean allowEmpty, boolean inputBox,
				boolean errorBox) {
			HSSFRow row = sheet.createRow(sheet.getPhysicalNumberOfRows());
			// condition's string
			HSSFCell cell = row.createCell(1);
			cell.setCellStyle(style_1);
			setCellValue(cell, strCondition);
			// allow empty cells
			cell = row.createCell(2);
			cell.setCellStyle(style_2);
			setCellValue(cell, ((allowEmpty) ? "yes" : "no"));
			// show input box
			cell = row.createCell(3);
			cell.setCellStyle(style_2);
			setCellValue(cell, ((inputBox) ? "yes" : "no"));
			// show error box
			cell = row.createCell(4);
			cell.setCellStyle(style_2);
			setCellValue(cell, ((errorBox) ? "yes" : "no"));
		}
		private static void writeOtherSettings(HSSFSheet sheet, HSSFCellStyle style,
				String strStettings) {
			HSSFRow row = sheet.getRow(sheet.getPhysicalNumberOfRows() - 1);
			HSSFCell cell = row.createCell(5);
			cell.setCellStyle(style);
			setCellValue(cell, strStettings);
		}
		public void addListValidation(String[] explicitListValues, String listFormula, String listValsDescr,
				boolean allowEmpty, boolean suppressDropDown) {
			String promptDescr = (allowEmpty ? "empty ok" : "not empty") 
					+ ", " + (suppressDropDown ? "no drop-down" : "drop-down"); 
			addValidationInternal(VT.LIST, listFormula, null, ES.STOP, listValsDescr, promptDescr, 
					allowEmpty, false, true, suppressDropDown, explicitListValues);
		}
	}

	/**
	 * Manages the cell styles used for formatting the output spreadsheet
	 */
	private static final class WorkbookFormatter {

		private final HSSFWorkbook _wb;
		private final HSSFCellStyle _style_1;
		private final HSSFCellStyle _style_2;
		private final HSSFCellStyle _style_3;
		private final HSSFCellStyle _style_4;
		private HSSFSheet _currentSheet;

		public WorkbookFormatter(HSSFWorkbook wb) {
			_wb = wb;
			_style_1 = createStyle( wb, HSSFCellStyle.ALIGN_LEFT );
			_style_2 = createStyle( wb, HSSFCellStyle.ALIGN_CENTER );
			_style_3 = createStyle( wb, HSSFCellStyle.ALIGN_CENTER, HSSFColor.GREY_25_PERCENT.index, true );
			_style_4 = createHeaderStyle(wb);
		}
		
		private static HSSFCellStyle createStyle(HSSFWorkbook wb, short h_align, short color,
				boolean bold) {
			HSSFFont font = wb.createFont();
			if (bold) {
				font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
			}

			HSSFCellStyle cellStyle = wb.createCellStyle();
			cellStyle.setFont(font);
			cellStyle.setFillForegroundColor(color);
			cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
			cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
			cellStyle.setAlignment(h_align);
			cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
			cellStyle.setLeftBorderColor(HSSFColor.BLACK.index);
			cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
			cellStyle.setTopBorderColor(HSSFColor.BLACK.index);
			cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
			cellStyle.setRightBorderColor(HSSFColor.BLACK.index);
			cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
			cellStyle.setBottomBorderColor(HSSFColor.BLACK.index);

			return cellStyle;
		}

		private static HSSFCellStyle createStyle(HSSFWorkbook wb, short h_align) {
			return createStyle(wb, h_align, HSSFColor.WHITE.index, false);
		}
		private static HSSFCellStyle createHeaderStyle(HSSFWorkbook wb) {
			HSSFFont font = wb.createFont();
			font.setColor( HSSFColor.WHITE.index );
			font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
			
			HSSFCellStyle cellStyle = wb.createCellStyle();
			cellStyle.setFillForegroundColor(HSSFColor.BLUE_GREY.index);
			cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
			cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
			cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
			cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
			cellStyle.setLeftBorderColor(HSSFColor.WHITE.index);
			cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
			cellStyle.setTopBorderColor(HSSFColor.WHITE.index);
			cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
			cellStyle.setRightBorderColor(HSSFColor.WHITE.index);
			cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
			cellStyle.setBottomBorderColor(HSSFColor.WHITE.index);
			cellStyle.setFont(font);
			return cellStyle;
		}
		

		public HSSFSheet createSheet(String sheetName) {
			_currentSheet = _wb.createSheet(sheetName);
			return _currentSheet;
		}
		public void createDVTypeRow(String strTypeDescription) {
			HSSFSheet sheet = _currentSheet;
			HSSFRow row = sheet.createRow(sheet.getPhysicalNumberOfRows());
			row = sheet.createRow(sheet.getPhysicalNumberOfRows());
			sheet.addMergedRegion(new CellRangeAddress(sheet.getPhysicalNumberOfRows()-1, sheet.getPhysicalNumberOfRows()-1, 0, 5));
			HSSFCell cell = row.createCell(0);
			setCellValue(cell, strTypeDescription);
			cell.setCellStyle(_style_3);
			row = sheet.createRow(sheet.getPhysicalNumberOfRows());
		}
		
		public void createHeaderRow() {
			HSSFSheet sheet = _currentSheet;
			HSSFRow row = sheet.createRow(sheet.getPhysicalNumberOfRows());
			row.setHeight((short) 400);
			for (int i = 0; i < 6; i++) {
				row.createCell(i).setCellStyle(_style_4);
				if (i == 2 || i == 3 || i == 4) {
					sheet.setColumnWidth(i, 3500);
				} else if (i == 5) {
					sheet.setColumnWidth(i, 10000);
				} else {
					sheet.setColumnWidth(i, 8000);
				}
			}
			HSSFCell cell = row.getCell(0);
			setCellValue(cell, "Data validation cells");
			cell = row.getCell(1);
			setCellValue(cell, "Condition");
			cell = row.getCell(2);
			setCellValue(cell, "Allow blank");
			cell = row.getCell(3);
			setCellValue(cell, "Prompt box");
			cell = row.getCell(4);
			setCellValue(cell, "Error box");
			cell = row.getCell(5);
			setCellValue(cell, "Other settings");
		}

		public ValidationAdder createValidationAdder(HSSFCellStyle cellStyle, int dataValidationType) {
			return new ValidationAdder(_currentSheet, _style_1, _style_2, cellStyle, dataValidationType);
		}

		public void createDVDescriptionRow(String strTypeDescription) {
			HSSFSheet sheet = _currentSheet;
			HSSFRow row = sheet.getRow(sheet.getPhysicalNumberOfRows()-1);
			sheet.addMergedRegion(new CellRangeAddress(sheet.getPhysicalNumberOfRows()-1, sheet.getPhysicalNumberOfRows()-1, 0, 5));
			HSSFCell cell = row.createCell(0);
			setCellValue(cell, strTypeDescription);
			cell.setCellStyle(_style_3);
			row = sheet.createRow(sheet.getPhysicalNumberOfRows());
		}
	}
	
  
	private void addCustomValidations(WorkbookFormatter wf) {
		wf.createSheet("Custom");
		wf.createHeaderRow();

		ValidationAdder va = wf.createValidationAdder(null, VT.FORMULA);
		va.addValidation(OP.BETWEEN, "ISNUMBER($A2)", null, ES.STOP, "ISNUMBER(A2)", "Error box type = STOP", true, true, true);
		va.addValidation(OP.BETWEEN, "IF(SUM(A2:A3)=5,TRUE,FALSE)", null, ES.WARNING, "IF(SUM(A2:A3)=5,TRUE,FALSE)", "Error box type = WARNING", false, false, true);
	}

	private static void addSimpleNumericValidations(WorkbookFormatter wf) {
		// data validation's number types
		wf.createSheet("Numbers");

		// "Whole number" validation type
		wf.createDVTypeRow("Whole number");
		wf.createHeaderRow();