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 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);
}
}
