private static void AddSimpleNumericValidations(WorkbookFormatter wf)
{
// data validation's number types
wf.CreateSheet("Numbers");
// "Whole number" validation type
wf.CreateDVTypeRow("Whole number");
wf.CreateHeaderRow();
ValidationAdder va = wf.CreateValidationAdder(null, ValidationType.INTEGER);
va.AddValidation(OperatorType.BETWEEN, "2", "6", ERRORSTYLE.STOP, "Between 2 and 6 ", "Error box type = STOP", true, true, true);
va.AddValidation(OperatorType.NOT_BETWEEN, "2", "6", ERRORSTYLE.INFO, "Not between 2 and 6 ", "Error box type = INFO", false, true, true);
va.AddValidation(OperatorType.EQUAL, "=3+2", null, ERRORSTYLE.WARNING, "Equal to (3+2)", "Error box type = WARNING", false, false, true);
va.AddValidation(OperatorType.NOT_EQUAL, "3", null, ERRORSTYLE.WARNING, "Not equal to 3", "-", false, false, false);
va.AddValidation(OperatorType.GREATER_THAN, "3", null, ERRORSTYLE.WARNING, "Greater than 3", "-", true, false, false);
va.AddValidation(OperatorType.LESS_THAN, "3", null, ERRORSTYLE.WARNING, "Less than 3", "-", true, true, false);
va.AddValidation(OperatorType.GREATER_OR_EQUAL, "4", null, ERRORSTYLE.STOP, "Greater than or equal to 4", "Error box type = STOP", true, false, true);
va.AddValidation(OperatorType.LESS_OR_EQUAL, "4", null, ERRORSTYLE.STOP, "Less than or equal to 4", "-", false, true, false);
// "Decimal" validation type
wf.CreateDVTypeRow("Decimal");
wf.CreateHeaderRow();
va = wf.CreateValidationAdder(null, ValidationType.DECIMAL);
va.AddValidation(OperatorType.BETWEEN, "2", "6", ERRORSTYLE.STOP, "Between 2 and 6 ", "Error box type = STOP", true, true, true);
va.AddValidation(OperatorType.NOT_BETWEEN, "2", "6", ERRORSTYLE.INFO, "Not between 2 and 6 ", "Error box type = INFO", false, true, true);
va.AddValidation(OperatorType.EQUAL, "3", null, ERRORSTYLE.WARNING, "Equal to 3", "Error box type = WARNING", false, false, true);
va.AddValidation(OperatorType.NOT_EQUAL, "3", null, ERRORSTYLE.WARNING, "Not equal to 3", "-", false, false, false);
va.AddValidation(OperatorType.GREATER_THAN, "=12/6", null, ERRORSTYLE.WARNING, "Greater than (12/6)", "-", true, false, false);
va.AddValidation(OperatorType.LESS_THAN, "3", null, ERRORSTYLE.WARNING, "Less than 3", "-", true, true, false);
va.AddValidation(OperatorType.GREATER_OR_EQUAL, "4", null, ERRORSTYLE.STOP, "Greater than or equal to 4", "Error box type = STOP", true, false, true);
va.AddValidation(OperatorType.LESS_OR_EQUAL, "4", null, ERRORSTYLE.STOP, "Less than or equal to 4", "-", false, true, false);
}
private static void AddDateTimeValidations(WorkbookFormatter wf, IWorkbook wb)
{
wf.CreateSheet("Dates and Times");
IDataFormat dataFormat = wb.CreateDataFormat();
short fmtDate = dataFormat.GetFormat("m/d/yyyy");
short fmtTime = dataFormat.GetFormat("h:mm");
ICellStyle cellStyle_date = wb.CreateCellStyle();
cellStyle_date.DataFormat = (/*setter*/ fmtDate);
ICellStyle cellStyle_time = wb.CreateCellStyle();
cellStyle_time.DataFormat = (/*setter*/ fmtTime);
wf.CreateDVTypeRow("Date ( cells are already formated as date - m/d/yyyy)");
wf.CreateHeaderRow();
ValidationAdder va = wf.CreateValidationAdder(cellStyle_date, ValidationType.DATE);
va.AddValidation(OperatorType.BETWEEN, "2004/01/02", "2004/01/06", ERRORSTYLE.STOP, "Between 1/2/2004 and 1/6/2004 ", "Error box type = STOP", true, true, true);
va.AddValidation(OperatorType.NOT_BETWEEN, "2004/01/01", "2004/01/06", ERRORSTYLE.INFO, "Not between 1/2/2004 and 1/6/2004 ", "Error box type = INFO", false, true, true);
va.AddValidation(OperatorType.EQUAL, "2004/03/02", null, ERRORSTYLE.WARNING, "Equal to 3/2/2004", "Error box type = WARNING", false, false, true);
va.AddValidation(OperatorType.NOT_EQUAL, "2004/03/02", null, ERRORSTYLE.WARNING, "Not equal to 3/2/2004", "-", false, false, false);
va.AddValidation(OperatorType.GREATER_THAN, "=DATEVALUE(\"4-Jul-2001\")", null, ERRORSTYLE.WARNING, "Greater than DATEVALUE('4-Jul-2001')", "-", true, false, false);
va.AddValidation(OperatorType.LESS_THAN, "2004/03/02", null, ERRORSTYLE.WARNING, "Less than 3/2/2004", "-", true, true, false);
va.AddValidation(OperatorType.GREATER_OR_EQUAL, "2004/03/02", null, ERRORSTYLE.STOP, "Greater than or equal to 3/2/2004", "Error box type = STOP", true, false, true);
va.AddValidation(OperatorType.LESS_OR_EQUAL, "2004/03/04", null, ERRORSTYLE.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, ValidationType.TIME);
va.AddValidation(OperatorType.BETWEEN, "12:00", "16:00", ERRORSTYLE.STOP, "Between 12:00 and 16:00 ", "Error box type = STOP", true, true, true);
va.AddValidation(OperatorType.NOT_BETWEEN, "12:00", "16:00", ERRORSTYLE.INFO, "Not between 12:00 and 16:00 ", "Error box type = INFO", false, true, true);
va.AddValidation(OperatorType.EQUAL, "13:35", null, ERRORSTYLE.WARNING, "Equal to 13:35", "Error box type = WARNING", false, false, true);
va.AddValidation(OperatorType.NOT_EQUAL, "13:35", null, ERRORSTYLE.WARNING, "Not equal to 13:35", "-", false, false, false);
va.AddValidation(OperatorType.GREATER_THAN, "12:00", null, ERRORSTYLE.WARNING, "Greater than 12:00", "-", true, false, false);
va.AddValidation(OperatorType.LESS_THAN, "=1/2", null, ERRORSTYLE.WARNING, "Less than (1/2) -> 12:00", "-", true, true, false);
va.AddValidation(OperatorType.GREATER_OR_EQUAL, "14:00", null, ERRORSTYLE.STOP, "Greater than or equal to 14:00", "Error box type = STOP", true, false, true);
va.AddValidation(OperatorType.LESS_OR_EQUAL, "14:00", null, ERRORSTYLE.STOP, "Less than or equal to 14:00", "-", false, true, false);
}
private static void AddListValidations(WorkbookFormatter wf, IWorkbook wb)
{
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 ";
string dataSheetName = "list_data";
// "List" Data Validation type
ISheet fSheet = wf.CreateSheet("Lists");
ISheet 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, ValidationType.LIST);
string listValsDescr = "POIFS,HSSF,HWPF,HPSF";
string[] listVals = listValsDescr.Split(",".ToCharArray());
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, ValidationType.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);
IName namedRange = wb.CreateName();
namedRange.NameName = (/*setter*/ "myName");
namedRange.RefersToFormula = (/*setter*/ 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++)
{
IRow currRow = fSheet.CreateRow(i + 29);
SetCellValue(currRow.CreateCell(0), cellStrValue);
}
// add list data on another sheet
for (int i = 0; i < 10; i++)
{
IRow 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, IWorkbook wb)
{
wf.CreateSheet("Dates and Times");
IDataFormat dataFormat = wb.CreateDataFormat();
short fmtDate = dataFormat.GetFormat("m/d/yyyy");
short fmtTime = dataFormat.GetFormat("h:mm");
ICellStyle cellStyle_date = wb.CreateCellStyle();
cellStyle_date.DataFormat = (/*setter*/fmtDate);
ICellStyle cellStyle_time = wb.CreateCellStyle();
cellStyle_time.DataFormat = (/*setter*/fmtTime);
wf.CreateDVTypeRow("Date ( cells are already formated as date - m/d/yyyy)");
wf.CreateHeaderRow();
ValidationAdder va = wf.CreateValidationAdder(cellStyle_date, ValidationType.DATE);
va.AddValidation(OperatorType.BETWEEN, "2004/01/02", "2004/01/06", ERRORSTYLE.STOP, "Between 1/2/2004 and 1/6/2004 ", "Error box type = STOP", true, true, true);
va.AddValidation(OperatorType.NOT_BETWEEN, "2004/01/01", "2004/01/06", ERRORSTYLE.INFO, "Not between 1/2/2004 and 1/6/2004 ", "Error box type = INFO", false, true, true);
va.AddValidation(OperatorType.EQUAL, "2004/03/02", null, ERRORSTYLE.WARNING, "Equal to 3/2/2004", "Error box type = WARNING", false, false, true);
va.AddValidation(OperatorType.NOT_EQUAL, "2004/03/02", null, ERRORSTYLE.WARNING, "Not equal to 3/2/2004", "-", false, false, false);
va.AddValidation(OperatorType.GREATER_THAN, "=DATEVALUE(\"4-Jul-2001\")", null, ERRORSTYLE.WARNING, "Greater than DATEVALUE('4-Jul-2001')", "-", true, false, false);
va.AddValidation(OperatorType.LESS_THAN, "2004/03/02", null, ERRORSTYLE.WARNING, "Less than 3/2/2004", "-", true, true, false);
va.AddValidation(OperatorType.GREATER_OR_EQUAL, "2004/03/02", null, ERRORSTYLE.STOP, "Greater than or equal to 3/2/2004", "Error box type = STOP", true, false, true);
va.AddValidation(OperatorType.LESS_OR_EQUAL, "2004/03/04", null, ERRORSTYLE.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, ValidationType.TIME);
va.AddValidation(OperatorType.BETWEEN, "12:00", "16:00", ERRORSTYLE.STOP, "Between 12:00 and 16:00 ", "Error box type = STOP", true, true, true);
va.AddValidation(OperatorType.NOT_BETWEEN, "12:00", "16:00", ERRORSTYLE.INFO, "Not between 12:00 and 16:00 ", "Error box type = INFO", false, true, true);
va.AddValidation(OperatorType.EQUAL, "13:35", null, ERRORSTYLE.WARNING, "Equal to 13:35", "Error box type = WARNING", false, false, true);
va.AddValidation(OperatorType.NOT_EQUAL, "13:35", null, ERRORSTYLE.WARNING, "Not equal to 13:35", "-", false, false, false);
va.AddValidation(OperatorType.GREATER_THAN, "12:00", null, ERRORSTYLE.WARNING, "Greater than 12:00", "-", true, false, false);
va.AddValidation(OperatorType.LESS_THAN, "=1/2", null, ERRORSTYLE.WARNING, "Less than (1/2) -> 12:00", "-", true, true, false);
va.AddValidation(OperatorType.GREATER_OR_EQUAL, "14:00", null, ERRORSTYLE.STOP, "Greater than or equal to 14:00", "Error box type = STOP", true, false, true);
va.AddValidation(OperatorType.LESS_OR_EQUAL, "14:00", null, ERRORSTYLE.STOP, "Less than or equal to 14:00", "-", false, true, false);
}
private static void AddListValidations(WorkbookFormatter wf, IWorkbook wb)
{
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 ";
String dataSheetName = "list_data";
// "List" Data Validation type
ISheet fSheet = wf.CreateSheet("Lists");
ISheet 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, ValidationType.LIST);
String listValsDescr = "POIFS,HSSF,HWPF,HPSF";
String[] listVals = listValsDescr.Split(",".ToCharArray());
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, ValidationType.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);
IName namedRange = wb.CreateName();
namedRange.NameName = (/*setter*/"myName");
namedRange.RefersToFormula = (/*setter*/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++)
{
IRow currRow = fSheet.CreateRow(i + 29);
SetCellValue(currRow.CreateCell(0), cellStrValue);
}
// add list data on another sheet
for (int i = 0; i < 10; i++)
{
IRow 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 AddSimpleNumericValidations(WorkbookFormatter wf)
{
// data validation's number types
wf.CreateSheet("Numbers");
// "Whole number" validation type
wf.CreateDVTypeRow("Whole number");
wf.CreateHeaderRow();
ValidationAdder va = wf.CreateValidationAdder(null, ValidationType.INTEGER);
va.AddValidation(OperatorType.BETWEEN, "2", "6", ERRORSTYLE.STOP, "Between 2 and 6 ", "Error box type = STOP", true, true, true);
va.AddValidation(OperatorType.NOT_BETWEEN, "2", "6", ERRORSTYLE.INFO, "Not between 2 and 6 ", "Error box type = INFO", false, true, true);
va.AddValidation(OperatorType.EQUAL, "=3+2", null, ERRORSTYLE.WARNING, "Equal to (3+2)", "Error box type = WARNING", false, false, true);
va.AddValidation(OperatorType.NOT_EQUAL, "3", null, ERRORSTYLE.WARNING, "Not equal to 3", "-", false, false, false);
va.AddValidation(OperatorType.GREATER_THAN, "3", null, ERRORSTYLE.WARNING, "Greater than 3", "-", true, false, false);
va.AddValidation(OperatorType.LESS_THAN, "3", null, ERRORSTYLE.WARNING, "Less than 3", "-", true, true, false);
va.AddValidation(OperatorType.GREATER_OR_EQUAL, "4", null, ERRORSTYLE.STOP, "Greater than or equal to 4", "Error box type = STOP", true, false, true);
va.AddValidation(OperatorType.LESS_OR_EQUAL, "4", null, ERRORSTYLE.STOP, "Less than or equal to 4", "-", false, true, false);
// "Decimal" validation type
wf.CreateDVTypeRow("Decimal");
wf.CreateHeaderRow();
va = wf.CreateValidationAdder(null, ValidationType.DECIMAL);
va.AddValidation(OperatorType.BETWEEN, "2", "6", ERRORSTYLE.STOP, "Between 2 and 6 ", "Error box type = STOP", true, true, true);
va.AddValidation(OperatorType.NOT_BETWEEN, "2", "6", ERRORSTYLE.INFO, "Not between 2 and 6 ", "Error box type = INFO", false, true, true);
va.AddValidation(OperatorType.EQUAL, "3", null, ERRORSTYLE.WARNING, "Equal to 3", "Error box type = WARNING", false, false, true);
va.AddValidation(OperatorType.NOT_EQUAL, "3", null, ERRORSTYLE.WARNING, "Not equal to 3", "-", false, false, false);
va.AddValidation(OperatorType.GREATER_THAN, "=12/6", null, ERRORSTYLE.WARNING, "Greater than (12/6)", "-", true, false, false);
va.AddValidation(OperatorType.LESS_THAN, "3", null, ERRORSTYLE.WARNING, "Less than 3", "-", true, true, false);
va.AddValidation(OperatorType.GREATER_OR_EQUAL, "4", null, ERRORSTYLE.STOP, "Greater than or equal to 4", "Error box type = STOP", true, false, true);
va.AddValidation(OperatorType.LESS_OR_EQUAL, "4", null, ERRORSTYLE.STOP, "Less than or equal to 4", "-", false, true, false);
}
