public void TestGetDataValidationsListFormula()
{
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.CreateSheet() as HSSFSheet;
List<IDataValidation> list = sheet.GetDataValidations();
Assert.AreEqual(0, list.Count);
IDataValidationHelper dataValidationHelper = sheet.GetDataValidationHelper();
IDataValidationConstraint constraint = dataValidationHelper.CreateFormulaListConstraint("A2");
CellRangeAddressList AddressList = new CellRangeAddressList(0, 0, 0, 0);
IDataValidation validation = dataValidationHelper.CreateValidation(constraint, AddressList);
validation.SuppressDropDownArrow = (/*setter*/true);
sheet.AddValidationData(validation);
list = sheet.GetDataValidations(); // <-- works
Assert.AreEqual(1, list.Count);
HSSFDataValidation dv = list[(0)] as HSSFDataValidation;
Assert.AreEqual(true, dv.SuppressDropDownArrow);
DVConstraint c = dv.Constraint;
Assert.AreEqual(ValidationType.LIST, c.GetValidationType());
Assert.AreEqual("A2", c.Formula1);
Assert.AreEqual(null, c.Formula2);
Assert.AreEqual(double.NaN, c.Value1);
Assert.AreEqual(double.NaN, c.Value2);
}
private void AddValidationInternal(int operatorType, string firstFormula,
string secondFormula, int errorStyle, string ruleDescr, string promptDescr,
bool allowEmpty, bool inputBox, bool errorBox, bool suppressDropDown,
string[] explicitListValues)
{
int rowNum = _currentRowIndex++;
IDataValidationHelper dataValidationHelper = _sheet.GetDataValidationHelper();
IDataValidationConstraint dc = CreateConstraint(dataValidationHelper, operatorType, firstFormula, secondFormula, explicitListValues);
IDataValidation dv = dataValidationHelper.CreateValidation(dc, new CellRangeAddressList(rowNum, rowNum, 0, 0));
dv.EmptyCellAllowed = (/*setter*/ allowEmpty);
dv.ErrorStyle = (/*setter*/ errorStyle);
dv.CreateErrorBox("Invalid Input", "Something is wrong - check condition!");
dv.CreatePromptBox("Validated Cell", "Allowable values have been restricted");
dv.ShowPromptBox = (/*setter*/ inputBox);
dv.ShowErrorBox = (/*setter*/ errorBox);
dv.SuppressDropDownArrow = (/*setter*/ suppressDropDown);
_sheet.AddValidationData(dv);
WriteDataValidationSettings(_sheet, _style_1, _style_2, ruleDescr, allowEmpty,
inputBox, errorBox);
if (_cellStyle != null)
{
IRow row = _sheet.GetRow(_sheet.PhysicalNumberOfRows - 1);
ICell cell = row.CreateCell(0);
cell.CellStyle = (/*setter*/ _cellStyle);
}
WriteOtherSettings(_sheet, _style_1, promptDescr);
}
public void TestGetDataValidationsDecimal(){
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.CreateSheet() as HSSFSheet;
List<IDataValidation> list = sheet.GetDataValidations();
Assert.AreEqual(0, list.Count);
IDataValidationHelper dataValidationHelper = sheet.GetDataValidationHelper();
IDataValidationConstraint constraint = dataValidationHelper.CreateDecimalConstraint(OperatorType.BETWEEN, "=A2",
"200");
CellRangeAddressList AddressList = new CellRangeAddressList(0, 0, 0, 0);
IDataValidation validation = dataValidationHelper.CreateValidation(constraint, AddressList);
sheet.AddValidationData(validation);
list = sheet.GetDataValidations(); // <-- works
Assert.AreEqual(1, list.Count);
HSSFDataValidation dv = list[(0)] as HSSFDataValidation;
DVConstraint c = dv.Constraint;
Assert.AreEqual(ValidationType.DECIMAL, c.GetValidationType());
Assert.AreEqual(OperatorType.BETWEEN, c.Operator);
Assert.AreEqual("A2", c.Formula1);
Assert.AreEqual(null, c.Formula2);
Assert.AreEqual(double.NaN, c.Value1);
Assert.AreEqual(200, c.Value2);
}
public void TestGetDataValidationsFormula()
{
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.CreateSheet() as HSSFSheet;
List <IDataValidation> list = sheet.GetDataValidations();
Assert.AreEqual(0, list.Count);
IDataValidationHelper dataValidationHelper = sheet.GetDataValidationHelper();
IDataValidationConstraint constraint = dataValidationHelper.CreateCustomConstraint("A2:A3");
CellRangeAddressList AddressList = new CellRangeAddressList(0, 0, 0, 0);
IDataValidation validation = dataValidationHelper.CreateValidation(constraint, AddressList);
sheet.AddValidationData(validation);
list = sheet.GetDataValidations(); // <-- works
Assert.AreEqual(1, list.Count);
HSSFDataValidation dv = list[(0)] as HSSFDataValidation;
DVConstraint c = dv.Constraint;
Assert.AreEqual(ValidationType.FORMULA, c.GetValidationType());
Assert.AreEqual("A2:A3", c.Formula1);
Assert.AreEqual(null, c.Formula2);
Assert.AreEqual(double.NaN, c.Value1);
Assert.AreEqual(double.NaN, c.Value2);
}
/// <summary>
/// Used to fill the attendance details
/// </summary>
/// <param name="worksheet">worksheet used to get the range and fill attendance details</param>
private void FillAttendanceDetails(IWorksheet worksheet)
{
int rowIndex = 2;
foreach (EmployeeDetails empDetails in _employeeAttendanceList)
{
worksheet["A" + rowIndex].Text = empDetails.Name;
worksheet["B" + rowIndex].Text = empDetails.Supervisor;
for (int colIndex = 0; colIndex < empDetails.Attendances.Count; colIndex++)
{
worksheet[rowIndex, colIndex + 8].Text = empDetails.Attendances[colIndex];
}
rowIndex++;
}
//Data validation for list
IDataValidation validation = worksheet.Range["H2:AL31"].DataValidation;
validation.ListOfValues = new string[] { "P", "A", "L", "WE" };
worksheet["C2:C31"].Formula = "=CountIf('H2:AL2',\"P\")";
worksheet["D2:D31"].Formula = "=CountIf('H2:AL2',\"L\")";
worksheet["E2:E31"].Formula = "=CountIf('H2:AL2',\"A\")";
worksheet["F2:F31"].Formula = "=E2/(C2+D2+E2)";
worksheet["G2:G31"].Formula = "=D2/(C2+D2+E2)";
worksheet["F2:G31"].NumberFormat = ".00 %";
}
public void TestGetDataValidationsListExplicit()
{
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.CreateSheet() as HSSFSheet;
List<IDataValidation> list = sheet.GetDataValidations();
Assert.AreEqual(0, list.Count);
IDataValidationHelper dataValidationHelper = sheet.GetDataValidationHelper();
IDataValidationConstraint constraint = dataValidationHelper.CreateExplicitListConstraint(new String[] { "aaa",
"bbb", "ccc" });
CellRangeAddressList AddressList = new CellRangeAddressList(0, 0, 0, 0);
IDataValidation validation = dataValidationHelper.CreateValidation(constraint, AddressList);
validation.SuppressDropDownArrow = (/*setter*/true);
sheet.AddValidationData(validation);
list = sheet.GetDataValidations(); // <-- works
Assert.AreEqual(1, list.Count);
HSSFDataValidation dv = list[(0)] as HSSFDataValidation;
Assert.AreEqual(true, dv.SuppressDropDownArrow);
DVConstraint c = dv.Constraint;
Assert.AreEqual(ValidationType.LIST, c.GetValidationType());
Assert.AreEqual(null, c.Formula1);
Assert.AreEqual(null, c.Formula2);
Assert.AreEqual(double.NaN, c.Value1);
Assert.AreEqual(double.NaN, c.Value2);
String[] values = c.ExplicitListValues;
Assert.AreEqual(3, values.Length);
Assert.AreEqual("aaa", values[0]);
Assert.AreEqual("bbb", values[1]);
Assert.AreEqual("ccc", values[2]);
}
static void AddValidations(ISheet sheet, ExcelVersion version, params DataValidation[] validations)
{
IDataValidationHelper helper = sheet.GetDataValidationHelper();
foreach (DataValidation validation in validations)
{
if ((validation.List == null || validation.List.Count == 0) && validation.Name == null)
{
throw new InvalidOperationException("Validation is invalid");
}
IDataValidationConstraint constraint = validation.Name != null?
helper.CreateFormulaListConstraint(validation.Name) :
helper.CreateExplicitListConstraint(validation.List.ToArray());
var range = new CellRangeAddressList(
validation.Range.RowStart ?? 0,
validation.Range.RowEnd ?? ExcelHelper.GetRowMax(version) - 1,
validation.Range.ColumnStart ?? 0,
validation.Range.ColumnEnd ?? ExcelHelper.GetColumnMax(version) - 1);
IDataValidation dataValidation = helper.CreateValidation(constraint, range);
sheet.AddValidationData(dataValidation);
}
}
public void TestGetDataValidationsDate()
{
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.CreateSheet() as HSSFSheet;
List<IDataValidation> list = sheet.GetDataValidations();
Assert.AreEqual(0, list.Count);
IDataValidationHelper dataValidationHelper = sheet.GetDataValidationHelper();
IDataValidationConstraint constraint = dataValidationHelper.CreateDateConstraint(OperatorType.EQUAL,
"2014/10/25", null, null);
CellRangeAddressList AddressList = new CellRangeAddressList(0, 0, 0, 0);
IDataValidation validation = dataValidationHelper.CreateValidation(constraint, AddressList);
sheet.AddValidationData(validation);
list = sheet.GetDataValidations(); // <-- works
Assert.AreEqual(1, list.Count);
HSSFDataValidation dv = list[(0)] as HSSFDataValidation;
DVConstraint c = dv.Constraint;
Assert.AreEqual(ValidationType.DATE, c.GetValidationType());
Assert.AreEqual(OperatorType.EQUAL, c.Operator);
Assert.AreEqual(null, c.Formula1);
Assert.AreEqual(null, c.Formula2);
Assert.AreEqual(DateUtil.GetExcelDate(DateUtil.ParseYYYYMMDDDate("2014/10/25")), c.Value1);
Assert.AreEqual(double.NaN, c.Value2);
}
public void Test53965()
{
XSSFWorkbook wb = new XSSFWorkbook();
try
{
XSSFSheet sheet = wb.CreateSheet() as XSSFSheet;
List <IDataValidation> lst = sheet.GetDataValidations(); //<-- works
Assert.AreEqual(0, lst.Count);
//create the cell that will have the validation applied
sheet.CreateRow(0).CreateCell(0);
IDataValidationHelper dataValidationHelper = sheet.GetDataValidationHelper();
IDataValidationConstraint constraint = dataValidationHelper.CreateCustomConstraint("SUM($A$1:$A$1) <= 3500");
CellRangeAddressList addressList = new CellRangeAddressList(0, 0, 0, 0);
IDataValidation validation = dataValidationHelper.CreateValidation(constraint, addressList);
sheet.AddValidationData(validation);
// this line caused XmlValueOutOfRangeException , see Bugzilla 3965
lst = sheet.GetDataValidations();
Assert.AreEqual(1, lst.Count);
}
finally
{
wb.Close();
}
}
static void Main(string[] args)
{
string workbookName = "test.xlsx";
IWorkbook workbook = null;
ISheet sheet = null;
IDataValidationHelper dvHelper = null;
IDataValidationConstraint dvConstraint = null;
IDataValidation validation = null;
CellRangeAddressList addressList = null;
// Using the ss.usermodel allows this class to support both binary
// and xml based workbooks. The choice of which one to create is
// made by checking the file extension.
if (workbookName.EndsWith(".xlsx"))
{
workbook = new XSSFWorkbook();
}
else
{
workbook = new HSSFWorkbook();
}
// Build the sheet that will hold the data for the validations. This
// must be done first as it will create names that are referenced
// later.
sheet = workbook.CreateSheet("Linked Validations");
BuildDataSheet(sheet);
// Build the first data validation to occupy cell A1. Note
// that it retrieves it's data from the named area or region called
// CHOICES. Further information about this can be found in the
// static buildDataSheet() method below.
addressList = new CellRangeAddressList(0, 0, 0, 0);
dvHelper = sheet.GetDataValidationHelper();
dvConstraint = dvHelper.CreateFormulaListConstraint("CHOICES");
validation = dvHelper.CreateValidation(dvConstraint, addressList);
sheet.AddValidationData(validation);
// Now, build the linked or dependent drop down list that will
// occupy cell B1. The key to the whole process is the use of the
// INDIRECT() function. In the buildDataSheet(0 method, a series of
// named regions are created and the names of three of them mirror
// the options available to the user in the first drop down list
// (in cell A1). Using the INDIRECT() function makes it possible
// to convert the selection the user makes in that first drop down
// into the addresses of a named region of cells and then to use
// those cells to populate the second drop down list.
addressList = new CellRangeAddressList(0, 0, 1, 1);
dvConstraint = dvHelper.CreateFormulaListConstraint(
"INDIRECT(UPPER($A$1))");
validation = dvHelper.CreateValidation(dvConstraint, addressList);
sheet.AddValidationData(validation);
FileStream sw = File.OpenWrite(workbookName);
workbook.Write(sw);
sw.Close();
}
public App(IDBAccess dbAccess, IStager stager, IScriptRunner scriptRunner, IMessageBroker messageBroker, IDataValidation dataValidation, ILogger <App> log)
{
Stager = stager;
ScriptRunner = scriptRunner;
DBAccess = dbAccess;
MessageBroker = messageBroker;
DataValidation = dataValidation;
_log = log;
}
/// <summary>
/// 创建列表
/// </summary>
/// <param name="helper"></param>
/// <param name="ListData"></param>
/// <returns></returns>
internal static IDataValidation getValidationExplicitList(IDataValidationHelper helper, CellRangeAddressList range, string[] ListData)
{
IDataValidationConstraint constraint = helper.CreateExplicitListConstraint(ListData);
IDataValidation validation = helper.CreateValidation(constraint, range);
//validation.ShowPromptBox = true;
validation.ShowErrorBox = true;
return(validation);
}
public static void Test()
{
var filepath = "E:/test.xlsx";
if (File.Exists(filepath))
{
File.Delete(filepath);
}
using (var ms = new FileStream(filepath, FileMode.OpenOrCreate))
{
IWorkbook workbook = new XSSFWorkbook();
try
{
ISheet sheetRef = workbook.CreateSheet("ref");//名为ref的工作表
var items = new dynamic[] {
new { code = "1", name = "项目" },
new { code = "2", name = "标段" },
new { code = "3", name = "桥梁" },
new { code = "4", name = "隧道" }
};
for (int i = 0; i < items.Length; i++)//A1到A4格子里存放0001到0004，这是下拉框可以选择的4个选项
{
var r = sheetRef.CreateRow(i);
r.CreateCell(0).SetCellValue(items[i].code);
r.CreateCell(1).SetCellValue(items[i].name);
//sheetRef.GetRow(i);
}
IName range = workbook.CreateName(); //创建一个命名公式
range.RefersToFormula = "ref!$A$1:$A$" + items.Length; //公式内容，就是上面的区域
range.NameName = "sectionName"; //公式名称，可以在"公式"-->"名称管理器"中看到
ISheet sheet1 = workbook.CreateSheet("data"); //获得第一个工作表
IRow row = sheet1.CreateRow(0);
row.CreateCell(0).SetCellValue("项目名称");
row.CreateCell(1).SetCellValue("地图名称");
row.CreateCell(2).SetCellValue("地图类型-代码");
row.CreateCell(3).SetCellValue("地图类型-名称");
row.CreateCell(4).SetCellValue("经纬度");
//设定公式
row.GetCell(3).SetCellFormula("VLOOKUP(C2,ref!A:B,2,FALSE)");
CellRangeAddressList regions = new CellRangeAddressList(1, 65535, 2, 3); //约束范围：B1到B65535
XSSFDataValidationHelper helper = new XSSFDataValidationHelper((XSSFSheet)sheet1); //获得一个数据验证Helper
IDataValidation validation = helper.CreateValidation(helper.CreateFormulaListConstraint("sectionName"), regions); //创建一个特定约束范围内的公式列表约束（即第一节里说的"自定义"方式）
validation.CreateErrorBox("错误", "请按右侧下拉箭头选择!"); //不符合约束时的提示
validation.ShowErrorBox = true; //显示上面提示 = True
sheet1.AddValidationData(validation); //添加进去
sheet1.ForceFormulaRecalculation = true;
workbook.Write(ms);
}
finally
{
workbook.Close();
}
}
}
internal static IDataValidation getValidationYearMonth(IDataValidationHelper helper, CellRangeAddressList range)
{
IDataValidationConstraint constraint = helper.CreateintConstraint(
ST_DataValidationOperator.between.GetHashCode(), "200001", "202012");
IDataValidation validation = helper.CreateValidation(constraint, range);
validation.CreateErrorBox("error", "请输入YYYYMM样式的年月，如2012年8月为【201208】");
validation.ShowErrorBox = true;
return(validation);
}
public IActionResult ExportToExcel()
{
//创建EXCEL工作薄
IWorkbook workBook = new XSSFWorkbook();
//创建sheet文件表
ISheet sheet = workBook.CreateSheet("上下料点信息");
var expDir = string.Format("{0}Export\\{1}", System.AppDomain.CurrentDomain.BaseDirectory,
DateTime.Now.ToString("yyyyMM"));
if (!Directory.Exists(expDir))
{
Directory.CreateDirectory(expDir);
}
string filePath = string.Format("{0}\\CD{1}.xlsx", expDir, DateTime.Now.ToString("yyyyMMddHHmmss"));
#region 创建Excel表头
//创建表头
IRow header = sheet.CreateRow(0);
ICell cell = header.CreateCell(0);
cell.SetCellValue("料点编号");
cell = header.CreateCell(1);
cell.SetCellValue("料点名称");
cell = header.CreateCell(2);
cell.SetCellValue("仓库编号");
cell = header.CreateCell(3);
cell.SetCellValue("巷道编号");
cell = header.CreateCell(4);
cell.SetCellValue("料点类型");
ISheet sheet1 = workBook.GetSheetAt(0); //获得第一个工作表
CellRangeAddressList regions = new CellRangeAddressList(1, 65535, 4, 4); //设定位置 行起，行止,列起,列终
XSSFDataValidationHelper helper = new XSSFDataValidationHelper((XSSFSheet)sheet1); //获得一个数据验证Helper
IDataValidation validation = helper.CreateValidation(helper.CreateExplicitListConstraint(new string[] { "In", "Out", "InOut", "OutBack" }), regions); //创建一个特定约束范围内的公式列表约束（即第一节里说的"自定义"方式）
validation.CreateErrorBox("错误", "请按右侧下拉箭头选择!"); //不符合约束时的提示
validation.ShowErrorBox = true; //显示上面提示 = True
sheet1.AddValidationData(validation); //添加进去
sheet1.ForceFormulaRecalculation = true;
#endregion
//工作流写入，通过流的方式进行创建生成文件
using (MemoryStream stream = new MemoryStream())
{
workBook.Write(stream);
byte[] buffer = stream.ToArray();
return(File(buffer, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", string.Format("上下料点信息表_{0}.xlsx", DateTime.Now.ToString("yyyyMMddHHmmss"))));
}
}
/// <summary>
/// 设置单元格验证(数字)
/// 创建者：戚鹏
/// 创建日期：2013.5.20
/// </summary>
/// <param name="RowCount"></param>
/// <param name="ColNum"></param>
/// <param name="FirstValue"></param>
/// <param name="LastValue"></param>
/// <returns></returns>
internal static IDataValidation getValidationInt(IDataValidationHelper helper, CellRangeAddressList range,
int from = 0, int to = 99999999)
{
IDataValidationConstraint constraint = helper.CreateintConstraint(
ST_DataValidationOperator.between.GetHashCode(), from.ToString(), to.ToString());
IDataValidation validation = helper.CreateValidation(constraint, range);
//validation.CreateErrorBox("error", "You must input a numeric between 1 and 50.");
validation.ShowErrorBox = true;
return(validation);
}
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.
IWorkbook wb = HSSFTestDataSamples.OpenSampleWorkbook("dvEmpty.xls");
int dvRow = 0;
ISheet sheet = wb.GetSheetAt(0);
IDataValidationHelper dataValidationHelper = sheet.GetDataValidationHelper();
IDataValidationConstraint dc = dataValidationHelper.CreateintConstraint(OperatorType.EQUAL, "42", null);
IDataValidation dv = dataValidationHelper.CreateValidation(dc, new CellRangeAddressList(dvRow, dvRow, 0, 0));
dv.EmptyCellAllowed = (/*setter*/ false);
dv.ErrorStyle = (/*setter*/ ERRORSTYLE.STOP);
dv.ShowPromptBox = (/*setter*/ true);
dv.CreateErrorBox("Xxx", "Yyy");
dv.SuppressDropDownArrow = (/*setter*/ true);
sheet.AddValidationData(dv);
MemoryStream baos = new MemoryStream();
try
{
wb.Write(baos);
}
catch (IOException e)
{
throw new RuntimeException(e);
}
byte[] wbData = baos.ToArray();
byte[] dvHeaderRecStart = { (byte)0xB2, 0x01, 0x12, 0x00, };
int dvHeaderOffset = FindIndex(wbData, dvHeaderRecStart);
Assert.IsTrue(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 AssertionException("Identified bug 45519");
}
Assert.AreEqual(DVRecord.sid, nextSid);
}
protected void SetOtherValidationParameters(IDataValidation validation)
{
bool yesNo = true;
validation.EmptyCellAllowed = yesNo;
validation.ShowErrorBox = yesNo;
validation.ShowPromptBox = yesNo;
validation.CreateErrorBox("Error Message Title", "Error Message");
validation.CreatePromptBox("Prompt", "Enter some value");
validation.SuppressDropDownArrow = yesNo;
}
public void TestGetDataValidationsAny()
{
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.CreateSheet() as HSSFSheet;
List <IDataValidation> list = sheet.GetDataValidations();
Assert.AreEqual(0, list.Count);
IDataValidationHelper dataValidationHelper = sheet.GetDataValidationHelper();
IDataValidationConstraint constraint = dataValidationHelper.CreateNumericConstraint(ValidationType.ANY,
OperatorType.IGNORED, null, null);
CellRangeAddressList AddressList = new CellRangeAddressList(1, 2, 3, 4);
IDataValidation validation = dataValidationHelper.CreateValidation(constraint, AddressList);
validation.EmptyCellAllowed = (/*setter*/ true);
validation.CreateErrorBox("error-title", "error-text");
validation.CreatePromptBox("prompt-title", "prompt-text");
sheet.AddValidationData(validation);
list = sheet.GetDataValidations(); // <-- works
Assert.AreEqual(1, list.Count);
HSSFDataValidation dv = list[(0)] as HSSFDataValidation;
{
CellRangeAddressList regions = dv.Regions;
Assert.AreEqual(1, regions.CountRanges());
CellRangeAddress Address = regions.GetCellRangeAddress(0);
Assert.AreEqual(1, Address.FirstRow);
Assert.AreEqual(2, Address.LastRow);
Assert.AreEqual(3, Address.FirstColumn);
Assert.AreEqual(4, Address.LastColumn);
}
Assert.AreEqual(true, dv.EmptyCellAllowed);
Assert.AreEqual(false, dv.SuppressDropDownArrow);
Assert.AreEqual(true, dv.ShowErrorBox);
Assert.AreEqual("error-title", dv.ErrorBoxTitle);
Assert.AreEqual("error-text", dv.ErrorBoxText);
Assert.AreEqual(true, dv.ShowPromptBox);
Assert.AreEqual("prompt-title", dv.PromptBoxTitle);
Assert.AreEqual("prompt-text", dv.PromptBoxText);
IDataValidationConstraint c = dv.ValidationConstraint;
Assert.AreEqual(ValidationType.ANY, c.GetValidationType());
Assert.AreEqual(OperatorType.IGNORED, c.Operator);
}
/// <summary>
/// 设置excel下拉列表
/// </summary>
/// <param name="sheet">excel工作表</param>
/// <param name="sheetName">下拉列表使用数据的sheet的名字</param>
/// <param name="rangeName">下拉列表使用数据的域</param>
/// <param name="names">待处理的姓名字符串</param>
/// <param name="count">数据表中数据行数，ref引用格式</param>
/// <param name="Row">所要设置的下拉列表所在行</param>
/// <param name="Col">所要设置的下拉列表所在列</param>
public static void SetDropDownList(ISheet sheet, string sheetName, string rangeName, string names, ref int count, int Row, int Col)
{
ISheet dataSheet = null;
IRow row = null;
ICell cell = null;
IName name = null;
IDataValidationHelper dvHelper = null;
IDataValidationConstraint dvConstraint = null;
IDataValidation validation = null;
CellRangeAddressList addressList = null;
dataSheet = sheet.Workbook.GetSheet(sheetName); //获取存储下拉数据的sheet
if (dataSheet == null) //若不存在，则创建
{
dataSheet = sheet.Workbook.CreateSheet(sheetName);
}
//string[] list = new string[] { "123", "456", "789" };
string[] list = BuildDropData(names); //获取下拉数据
row = dataSheet.CreateRow(count++);
for (int i = 0; i < list.Count(); i++) //将数据写入sheet中
{
cell = row.CreateCell(i);
cell.SetCellValue(list[i]);
}
//生成一个列表引用区域,并唯一标识它
name = sheet.Workbook.CreateName();
name.RefersToFormula = string.Format("'{0}'!$A${1}:${2}${3}", sheetName, count, IndexToColumn(list.Count()), count);
name.NameName = rangeName.ToUpper() + Row.ToString();
addressList = new CellRangeAddressList(Row, Row, Col, Col); //设置生成下拉框的行和列
dvHelper = sheet.GetDataValidationHelper();
dvConstraint = dvHelper.CreateFormulaListConstraint(rangeName.ToUpper() + Row.ToString());
validation = dvHelper.CreateValidation(dvConstraint, addressList); //绑定下拉框和作用区域
sheet.AddValidationData(validation);
IWorkbook wb = sheet.Workbook;
wb.SetSheetHidden(wb.GetSheetIndex(dataSheet), 1); //隐藏数据sheet
}
/// <summary>
/// 设置字段为下拉框
/// </summary>
/// <param name="ruleName">规则名称</param>
/// <param name="fieldName">字段名称</param>
private void SetField2Select(string ruleName, string fieldName)
{
//查找字符索引
var field = _config.Row.Where(t => t.Field == fieldName).FirstOrDefault();
if (field == null)
{
return;
}
CellRangeAddressList regions = new CellRangeAddressList(_config.Prop.StartRow - 1, 65535, field.ColumnIndex, field.ColumnIndex); //约束范围：B1到B65535
XSSFDataValidationHelper helper = new XSSFDataValidationHelper((XSSFSheet)_sheet); //获得一个数据验证Helper
IDataValidation validation = helper.CreateValidation(helper.CreateFormulaListConstraint(ruleName), regions); //创建一个特定约束范围内的公式列表约束（即第一节里说的"自定义"方式）
validation.EmptyCellAllowed = true;
validation.CreateErrorBox("错误", "请按右侧下拉箭头选择!"); //不符合约束时的提示
validation.ShowErrorBox = true; //显示上面提示 = True
_sheet.AddValidationData(validation); //添加进去
}
/// <summary>
/// Sheet中引用校验引用区域
/// </summary>
/// <param name="workbook"></param>
/// <param name="sheet"></param>
/// <param name="item"></param>
private void SheetAddDataValidation(IWorkbook workbook, ISheet sheet, ColumnProperty item)
{
if (item == null || string.IsNullOrWhiteSpace(item.ConstraintReference)) //如果没有引用区域， 则退出
{
return;
}
CellRangeAddressList regions = new CellRangeAddressList(2, 65535, item.ColumnIndex, item.ColumnIndex);
IDataValidation dataValidate = null;
if (excelVersion == ExcelVersion.XLSX)
{
XSSFSheet xssfSheet = sheet as XSSFSheet;
XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper(xssfSheet);
XSSFDataValidationConstraint dvConstraint = (XSSFDataValidationConstraint)dvHelper.CreateFormulaListConstraint(item.ConstraintReference);
dataValidate = dvHelper.CreateValidation(dvConstraint, regions);
dataValidate.EmptyCellAllowed = true;
dataValidate.ShowErrorBox = true;
dataValidate.CreateErrorBox("系统提示", "请选择指定的" + item.ColumnHeader + "选项");
dataValidate.ShowPromptBox = true;
dataValidate.CreatePromptBox("", item.ColumnHeader);
}
else
{
IName range = workbook.CreateName();
range.RefersToFormula = item.ConstraintReference;
range.NameName = item.ConstraintName;
DVConstraint constraint = DVConstraint.CreateFormulaListConstraint(item.ConstraintName);
dataValidate = new HSSFDataValidation(regions, constraint);
dataValidate.EmptyCellAllowed = true;
dataValidate.ShowErrorBox = true;
dataValidate.CreateErrorBox("系统提示", "请选择指定的" + item.ColumnHeader + "选项");
dataValidate.ShowPromptBox = true;
dataValidate.CreatePromptBox("", item.ColumnHeader);
}
sheet.AddValidationData(dataValidate);
}
public void TestDvProtectionOrder_bug47363b()
{
IWorkbook workbook = new HSSFWorkbook();
ISheet sheet = workbook.CreateSheet("Sheet1");
sheet.ProtectSheet("secret");
IDataValidationHelper dataValidationHelper = sheet.GetDataValidationHelper();
IDataValidationConstraint dvc = dataValidationHelper.CreateintConstraint(OperatorType.BETWEEN, "10", "100");
CellRangeAddressList numericCellAddressList = new CellRangeAddressList(0, 0, 1, 1);
IDataValidation dv = dataValidationHelper.CreateValidation(dvc, numericCellAddressList);
try
{
sheet.AddValidationData(dv);
}
catch (InvalidOperationException e)
{
String expMsg = "Unexpected (NPOI.HSSF.Record.PasswordRecord) while looking for DV Table insert pos";
if (expMsg.Equals(e.Message))
{
throw new AssertionException("Identified bug 47363b");
}
throw e;
}
TestCases.HSSF.UserModel.RecordInspector.RecordCollector rc;
rc = new RecordInspector.RecordCollector();
((HSSFSheet)sheet).Sheet.VisitContainedRecords(rc, 0);
int nRecsWithProtection = rc.Records.Length;
sheet.ProtectSheet(null);
rc = new RecordInspector.RecordCollector();
((HSSFSheet)sheet).Sheet.VisitContainedRecords(rc, 0);
int nRecsWithoutProtection = rc.Records.Length;
Assert.AreEqual(4, nRecsWithProtection - nRecsWithoutProtection);
}
protected void SetOtherValidationParameters(IDataValidation validation)
{
bool yesNo = true;
validation.EmptyCellAllowed = yesNo;
validation.ShowErrorBox = yesNo;
validation.ShowPromptBox = yesNo;
validation.CreateErrorBox("Error Message Title", "Error Message");
validation.CreatePromptBox("Prompt", "Enter some value");
validation.SuppressDropDownArrow = yesNo;
}
private void WriteWorkSheet_DataValidations_formulas(IDataValidation item, XElement dataValidationNode)
{
if (item is DataValidationInt)
{
DataValidationInt intItem = item as DataValidationInt;
WriteWorkSheet_DataValidations_formulas_int(intItem, dataValidationNode);
}
else if (item is DataValidationDecimal)
{
DataValidationDecimal decimalItem = item as DataValidationDecimal;
WriteWorkSheet_DataValidations_formulas_decimal(decimalItem, dataValidationNode);
}
else if (item is DataValidationList)
{
DataValidationList listItem = item as DataValidationList;
WriteWorkSheet_DataValidations_formulas_list(listItem, dataValidationNode);
}
else if (item is DataValidationDateTime)
{
DataValidationDateTime dateTime = item as DataValidationDateTime;
WriteWorkSheet_DataValidations_formulas_dateTime(dateTime, dataValidationNode);
}
else if (item is DataValidationTime)
{
DataValidationTime timeItem = item as DataValidationTime;
WriteWorkSheet_DataValidations_formulas_Time(timeItem, dataValidationNode);
}
else if (item is DataValidationCustom)
{
DataValidationCustom customItem = item as DataValidationCustom;
WriteWorkSheet_DataValidations_formulas_Custom(customItem, dataValidationNode);
}
}
void ISheet.AddValidationData(IDataValidation dataValidation)
{
throw new System.NotImplementedException();
}
/// <summary>
/// Creates a data validation object
/// </summary>
/// <param name="dataValidation">The data validation object settings</param>
public void AddValidationData(IDataValidation dataValidation)
{
if (dataValidation == null)
{
throw new ArgumentException("objValidation must not be null");
}
HSSFDataValidation hssfDataValidation = (HSSFDataValidation)dataValidation;
DataValidityTable dvt = _sheet.GetOrCreateDataValidityTable();
DVRecord dvRecord = hssfDataValidation.CreateDVRecord(this);
dvt.AddDataValidation(dvRecord);
}
public void AddValidationData(IDataValidation dataValidation)
{
_sh.AddValidationData(dataValidation);
}
public IActionResult ExportToExcel()
{
//创建EXCEL工作薄
IWorkbook workBook = new XSSFWorkbook();
//创建sheet文件表
ISheet sheet = workBook.CreateSheet("客户信息");
var expDir = string.Format("{0}Export\\{1}", System.AppDomain.CurrentDomain.BaseDirectory,
DateTime.Now.ToString("yyyyMM"));
if (!Directory.Exists(expDir))
{
Directory.CreateDirectory(expDir);
}
string filePath = string.Format("{0}\\CD{1}.xlsx", expDir, DateTime.Now.ToString("yyyyMMddHHmmss"));
#region 创建Excel表头
//创建表头
IRow header = sheet.CreateRow(0);
ICell cell = header.CreateCell(0);
cell.SetCellValue("客户编号");
cell = header.CreateCell(1);
cell.SetCellValue("客户名称");
cell = header.CreateCell(2);
cell.SetCellValue("客户类型");
cell = header.CreateCell(3);
cell.SetCellValue("联系电话/Phone");
cell = header.CreateCell(4);
cell.SetCellValue("传真/Fax");
cell = header.CreateCell(5);
cell.SetCellValue("邮箱/Email");
//IName range = workBook.CreateName();//创建一个命名公式
//range.RefersToFormula = "客户信息!$A$1:$A$4";//公式内容，就是上面的区域
//range.NameName = "sectionName";//公式名称，可以在"公式"-->"名称管理器"中看到
ISheet sheet1 = workBook.GetSheetAt(0); //获得第一个工作表
CellRangeAddressList regions = new CellRangeAddressList(1, 65535, 2, 2); //设定位置 行起，行止,列起,列终
XSSFDataValidationHelper helper = new XSSFDataValidationHelper((XSSFSheet)sheet1); //获得一个数据验证Helper
IDataValidation validation = helper.CreateValidation(helper.CreateExplicitListConstraint(new string[] { "Company", "Personal", "Virtual", "Internal" }), regions); //创建一个特定约束范围内的公式列表约束（即第一节里说的"自定义"方式）
validation.CreateErrorBox("错误", "请按右侧下拉箭头选择!"); //不符合约束时的提示
validation.ShowErrorBox = true; //显示上面提示 = True
sheet1.AddValidationData(validation); //添加进去
sheet1.ForceFormulaRecalculation = true;
#endregion
//工作流写入，通过流的方式进行创建生成文件
using (MemoryStream stream = new MemoryStream())
{
workBook.Write(stream);
byte[] buffer = stream.ToArray();
return(File(buffer, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", string.Format("客户信息表_{0}.xlsx", DateTime.Now.ToString("yyyyMMddHHmmss"))));
}
}
private async void btnGenerateExcel_Click(object sender, RoutedEventArgs e)
{
StorageFile storageFile;
if (!(Windows.Foundation.Metadata.ApiInformation.IsTypePresent("Windows.Phone.UI.Input.HardwareButtons")))
{
FileSavePicker savePicker = new FileSavePicker();
savePicker.SuggestedStartLocation = PickerLocationId.Desktop;
savePicker.SuggestedFileName = "DataValidationSample";
if (rdBtn2003.IsChecked.Value)
{
savePicker.FileTypeChoices.Add("Excel Files", new List <string>()
{
".xls"
});
}
else
{
savePicker.FileTypeChoices.Add("Excel Files", new List <string>()
{
".xlsx",
});
}
storageFile = await savePicker.PickSaveFileAsync();
}
else
{
StorageFolder local = Windows.Storage.ApplicationData.Current.LocalFolder;
if (rdBtn2003.IsChecked.Value)
{
storageFile = await local.CreateFileAsync("DataValidationSample.xls", CreationCollisionOption.ReplaceExisting);
}
else
{
storageFile = await local.CreateFileAsync("DataValidationSample.xlsx", CreationCollisionOption.ReplaceExisting);
}
}
if (storageFile == null)
{
return;
}
//Instantiate excel Engine
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
if (rdBtn2003.IsChecked.Value)
{
application.DefaultVersion = ExcelVersion.Excel97to2003;
}
else
{
application.DefaultVersion = ExcelVersion.Excel2013;
}
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet sheet = workbook.Worksheets[0];
//Data validation to list the values in the first cell
IDataValidation validation = sheet.Range["C7"].DataValidation;
sheet.Range["B7"].Text = "Select an item from the validation list";
validation.ListOfValues = new string[] { "PDF", "XlsIO", "DocIO" };
validation.PromptBoxText = "Data Validation list";
validation.IsPromptBoxVisible = true;
validation.ShowPromptBox = true;
sheet.Range["C7"].CellStyle.Borders.LineStyle = ExcelLineStyle.Medium;
sheet.Range["C7"].CellStyle.Borders[ExcelBordersIndex.DiagonalDown].ShowDiagonalLine = false;
sheet.Range["C7"].CellStyle.Borders[ExcelBordersIndex.DiagonalUp].ShowDiagonalLine = false;
// Data Validation for Numbers
IDataValidation validation1 = sheet.Range["C9"].DataValidation;
sheet.Range["B9"].Text = "Enter a Number to validate";
validation1.AllowType = ExcelDataType.Integer;
validation1.CompareOperator = ExcelDataValidationComparisonOperator.Between;
validation1.FirstFormula = "0";
validation1.SecondFormula = "10";
validation1.ShowErrorBox = true;
validation1.ErrorBoxText = "Value must be between 0 and 10";
validation1.ErrorBoxTitle = "ERROR";
validation1.PromptBoxText = "Value must be between 0 and 10";
validation1.ShowPromptBox = true;
sheet.Range["C9"].CellStyle.Borders.LineStyle = ExcelLineStyle.Medium;
sheet.Range["C9"].CellStyle.Borders[ExcelBordersIndex.DiagonalDown].ShowDiagonalLine = false;
sheet.Range["C9"].CellStyle.Borders[ExcelBordersIndex.DiagonalUp].ShowDiagonalLine = false;
// Data Validation for Date
IDataValidation validation2 = sheet.Range["C11"].DataValidation;
sheet.Range["B11"].Text = "Enter the Date to validate";
validation2.AllowType = ExcelDataType.Date;
validation2.CompareOperator = ExcelDataValidationComparisonOperator.Between;
validation2.FirstDateTime = new DateTime(2012, 1, 1);
validation2.SecondDateTime = new DateTime(2012, 12, 31);
validation2.ShowErrorBox = true;
validation2.ErrorBoxText = "Value must be from 01/1/2012 to 31/12/2012";
validation2.ErrorBoxTitle = "ERROR";
validation2.PromptBoxText = "Value must be from 01/1/2012 to 31/12/2012";
validation2.ShowPromptBox = true;
sheet.Range["C11"].CellStyle.Borders.LineStyle = ExcelLineStyle.Medium;
sheet.Range["C11"].CellStyle.Borders[ExcelBordersIndex.DiagonalDown].ShowDiagonalLine = false;
sheet.Range["C11"].CellStyle.Borders[ExcelBordersIndex.DiagonalUp].ShowDiagonalLine = false;
// Data Validation for TextLength
IDataValidation validation3 = sheet.Range["C13"].DataValidation;
sheet.Range["B13"].Text = "Enter the Text to validate";
validation3.AllowType = ExcelDataType.TextLength;
validation3.CompareOperator = ExcelDataValidationComparisonOperator.Between;
validation3.FirstFormula = "1";
validation3.SecondFormula = "6";
validation3.ShowErrorBox = true;
validation3.ErrorBoxText = "Maximum 6 characters are allowed";
validation3.ErrorBoxTitle = "ERROR";
validation3.PromptBoxText = "Maximum 6 characters are allowed";
validation3.ShowPromptBox = true;
sheet.Range["C13"].CellStyle.Borders.LineStyle = ExcelLineStyle.Medium;
sheet.Range["C13"].CellStyle.Borders[ExcelBordersIndex.DiagonalDown].ShowDiagonalLine = false;
sheet.Range["C13"].CellStyle.Borders[ExcelBordersIndex.DiagonalUp].ShowDiagonalLine = false;
// Data Validation for Time
IDataValidation validation4 = sheet.Range["C15"].DataValidation;
sheet.Range["B15"].Text = "Enter the Time to validate";
validation4.AllowType = ExcelDataType.Time;
validation4.CompareOperator = ExcelDataValidationComparisonOperator.Between;
validation4.FirstFormula = "10";
validation4.SecondFormula = "12";
validation4.ShowErrorBox = true;
validation4.ErrorBoxText = "Time must be between 10 and 12";
validation4.ErrorBoxTitle = "ERROR";
validation4.PromptBoxText = "Time must be between 10 and 12";
validation4.ShowPromptBox = true;
sheet.Range["C15"].CellStyle.Borders.LineStyle = ExcelLineStyle.Medium;
sheet.Range["C15"].CellStyle.Borders[ExcelBordersIndex.DiagonalDown].ShowDiagonalLine = false;
sheet.Range["C15"].CellStyle.Borders[ExcelBordersIndex.DiagonalUp].ShowDiagonalLine = false;
sheet.Range["B2:C2"].Merge();
sheet.Range["B2"].Text = "Simple Data validation";
sheet.Range["B5"].Text = "Validation criteria";
sheet.Range["C5"].Text = "Validation";
sheet.Range["B5"].CellStyle.Font.Bold = true;
sheet.Range["C5"].CellStyle.Font.Bold = true;
sheet.Range["B2"].CellStyle.Font.Bold = true;
sheet.Range["B2"].CellStyle.Font.Size = 16;
sheet.Range["B2"].CellStyle.HorizontalAlignment = ExcelHAlign.HAlignCenter;
sheet.AutofitColumn(2);
sheet["B7"].ColumnWidth = 40.3;
sheet.UsedRange.AutofitRows();
await workbook.SaveAsAsync(storageFile);
workbook.Close();
excelEngine.Dispose();
MessageDialog msgDialog = new MessageDialog("Do you want to view the Document?", "File has been created successfully.");
UICommand yesCmd = new UICommand("Yes");
msgDialog.Commands.Add(yesCmd);
UICommand noCmd = new UICommand("No");
msgDialog.Commands.Add(noCmd);
IUICommand cmd = await msgDialog.ShowAsync();
if (cmd == yesCmd)
{
// Launch the saved file
bool success = await Windows.System.Launcher.LaunchFileAsync(storageFile);
}
}
private void WriteWorkSheet_DataValidations_Attributes(XElement dataValidationNode, IDataValidation item)
{
if (item.AllowBlank.ConvertBool())
{
dataValidationNode.Add(new XAttribute(XName.Get("allowBlank"), 1));
}
if (item.ShowInputMessage.ConvertBool())
{
dataValidationNode.Add(new XAttribute(XName.Get("showInputMessage"), 1));
}
if (item.ShowErrorMessage.ConvertBool())
{
dataValidationNode.Add(new XAttribute(XName.Get("showErrorMessage"), 1));
}
if (!string.IsNullOrEmpty(item.ErrorTitle))
{
dataValidationNode.Add(new XAttribute(XName.Get("errorTitle"), item.ErrorTitle));
}
if (!string.IsNullOrEmpty(item.Error))
{
dataValidationNode.Add(new XAttribute(XName.Get("error"), item.Error));
}
if (!string.IsNullOrEmpty(item.PromptTitle))
{
dataValidationNode.Add(new XAttribute(XName.Get("promptTitle"), item.PromptTitle));
}
if (!string.IsNullOrEmpty(item.Prompt))
{
dataValidationNode.Add(new XAttribute(XName.Get("prompt"), item.Prompt));
}
}
void ISheet.AddValidationData(IDataValidation dataValidation)
{
throw new System.NotImplementedException();
}
//
// GET: /Default/
public ActionResult Default(string SaveOption)
{
if (SaveOption == null)
{
return(View());
}
//New instance of XlsIO is created.[Equivalent to launching Microsoft Excel with no workbooks open].
//The instantiation process consists of two steps.
//Step 1 : Instantiate the spreadsheet creation engine.
ExcelEngine excelEngine = new ExcelEngine();
//Step 2 : Instantiate the excel application object.
IApplication application = excelEngine.Excel;
//A new workbook is created.[Equivalent to creating a new workbook in Microsoft Excel]
//The new workbook will have 12 worksheets
IWorkbook workbook = application.Workbooks.Open(DefaultResolveApplicationDataPath(@"BudgetPlanner.xls"));
IWorksheet sheet = workbook.Worksheets[1];
sheet.FirstVisibleRow = 3;
IFont font = workbook.CreateFont();
font.Bold = true;
#region TextBox
ITextBoxShape textbox = sheet.TextBoxes.AddTextBox(5, 2, 40, 140);
textbox.Text = "Quick Budget";
textbox.RichText.SetFont(0, 11, font);
textbox.VAlignment = ExcelCommentVAlign.Center;
textbox.HAlignment = ExcelCommentHAlign.Center;
textbox.Fill.FillType = ExcelFillType.Gradient;
textbox.Fill.GradientColorType = ExcelGradientColor.TwoColor;
textbox.Fill.TwoColorGradient(ExcelGradientStyle.Vertical, ExcelGradientVariants.ShadingVariants_2);
textbox.Fill.BackColor = Color.FromArgb(204, 204, 255);
textbox = sheet.TextBoxes.AddTextBox(7, 2, 25, 140);
textbox.Text = "Income";
textbox.RichText.SetFont(0, 5, font);
textbox.VAlignment = ExcelCommentVAlign.Center;
textbox.HAlignment = ExcelCommentHAlign.Center;
textbox.Fill.FillType = ExcelFillType.Gradient;
textbox.Fill.GradientColorType = ExcelGradientColor.TwoColor;
textbox.Fill.TwoColorGradient(ExcelGradientStyle.Vertical, ExcelGradientVariants.ShadingVariants_2);
textbox.Fill.BackColor = Color.FromArgb(0, 0, 128);
textbox = sheet.TextBoxes.AddTextBox(16, 2, 25, 140);
textbox.Text = "Spending";
textbox.RichText.SetFont(0, 7, font);
textbox.VAlignment = ExcelCommentVAlign.Center;
textbox.HAlignment = ExcelCommentHAlign.Center;
textbox.Fill.FillType = ExcelFillType.Gradient;
textbox.Fill.GradientColorType = ExcelGradientColor.TwoColor;
textbox.Fill.TwoColorGradient(ExcelGradientStyle.Vertical, ExcelGradientVariants.ShadingVariants_2);
textbox.Fill.BackColor = Color.FromArgb(0, 0, 128);
#endregion
#region Write Text and Numbers
sheet.Range["O6"].Text = "Weekly";
sheet.Range["E7"].Text = "Frequency";
sheet.Range["F7"].Text = "Amount";
sheet.Range["G7"].Text = "Monthly";
sheet.Range["H7"].Text = "Yearly";
sheet.Range["B8"].Text = "Total Income";
sheet.Range["C9"].Text = "Salary/Wages";
sheet.Range["C10"].Text = "Salary/Wages(Spouse)";
sheet.Range["C11"].Text = "Other";
sheet.Range["C12"].Text = "Other";
sheet.Range["C13"].Text = "Other";
sheet.Range["B17"].Text = "Transportation";
sheet.Range["F25"].Number = 3000;
sheet.Range["F9"].Number = 55000;
sheet.Range["F10"].Number = 35000;
sheet.Range["C18"].Text = "Auto Loan/Lease";
sheet.Range["C19"].Text = "Insurance";
sheet.Range["C20"].Text = "Gas ";
sheet.Range["C21"].Text = "Maintenance ";
sheet.Range["C22"].Text = "Registration/Inspection";
sheet.Range["C23"].Text = "Bill's train pass";
sheet.Range["C24"].Text = "Jane's bus pass";
sheet.Range["C25"].Text = "Other";
sheet.Range["E16"].Text = "Total";
sheet.Range["N6"].Text = "Chart";
sheet.Range["B27"].Text = "Home";
sheet.Range["F28"].Number = 20000;
sheet.Range["F29"].Number = 5000;
sheet.Range["F33"].Number = 5000;
sheet.Range["C28"].Text = "EMI";
sheet.Range["C29"].Text = "Rent";
sheet.Range["C30"].Text = "Maintanence";
sheet.Range["C31"].Text = "Insurance";
sheet.Range["C32"].Text = "Furniture";
sheet.Range["C33"].Text = "Household Supplies";
sheet.Range["C34"].Text = "Groceries";
sheet.Range["C35"].Text = "Real Estate Tax";
sheet.Range["C36"].Text = "Other";
sheet.Range["B39"].Text = "Utilities";
sheet.Range["F41"].Number = 1000;
sheet.Range["F42"].Number = 250;
sheet.Range["F43"].Number = 150;
sheet.Range["F45"].Number = 175;
sheet.Range["C40"].Text = "Phone - Home";
sheet.Range["C41"].Text = "Phone - Cell";
sheet.Range["C42"].Text = "Cable";
sheet.Range["C43"].Text = "Gas";
sheet.Range["C44"].Text = "Water";
sheet.Range["C45"].Text = "Electricity";
sheet.Range["C46"].Text = "Internet";
sheet.Range["C47"].Text = "Other";
sheet.Range["B50"].Text = "Health";
sheet.Range["F53"].Number = 500;
sheet.Range["C51"].Text = "Dental";
sheet.Range["C52"].Text = "Medical";
sheet.Range["C53"].Text = "Medication";
sheet.Range["C54"].Text = "Vision/contacts";
sheet.Range["C55"].Text = "Life Insurance";
sheet.Range["C56"].Text = "Electricity";
sheet.Range["C57"].Text = "Other";
#endregion
#region Cell styles
IStyle tableStyle = workbook.Styles.Add("TableStyle");
tableStyle.BeginUpdate();
tableStyle.Color = Color.White;
tableStyle.Borders[ExcelBordersIndex.EdgeBottom].ColorRGB = Color.FromArgb(150, 150, 150);
tableStyle.Borders[ExcelBordersIndex.EdgeBottom].LineStyle = ExcelLineStyle.Thin;
tableStyle.Borders[ExcelBordersIndex.EdgeLeft].ColorRGB = Color.FromArgb(150, 150, 150);
tableStyle.Borders[ExcelBordersIndex.EdgeLeft].LineStyle = ExcelLineStyle.Thin;
tableStyle.Borders[ExcelBordersIndex.EdgeRight].ColorRGB = Color.FromArgb(150, 150, 150);
tableStyle.Borders[ExcelBordersIndex.EdgeRight].LineStyle = ExcelLineStyle.Thin;
tableStyle.Borders[ExcelBordersIndex.EdgeTop].ColorRGB = Color.FromArgb(150, 150, 150);
tableStyle.Borders[ExcelBordersIndex.EdgeTop].LineStyle = ExcelLineStyle.Thin;
tableStyle.EndUpdate();
sheet.Range["E7:H7"].CellStyle.Font.Bold = true;
sheet.Range["B17"].CellStyle.Font.Bold = true;
sheet.Range["B27"].CellStyle.Font.Bold = true;
sheet.Range["B39"].CellStyle.Font.Bold = true;
sheet.Range["B50"].CellStyle.Font.Bold = true;
sheet.Range["E7:H7"].CellStyle.Font.Underline = ExcelUnderline.Single;
sheet.Range["B7:H14"].CellStyle.Color = Color.FromArgb(223, 223, 223);
sheet.Range["C9:C13"].CellStyle = tableStyle;
sheet.Range["E9:F13"].CellStyle = tableStyle;
sheet.Range["B16:H26"].CellStyle.Color = Color.FromArgb(223, 223, 223);
sheet.Range["B17:C17"].CellStyle.Color = Color.White;
sheet.Range["C18:C25"].CellStyle = tableStyle;
sheet.Range["O6"].CellStyle = tableStyle;
sheet.Range["E18:F25"].CellStyle = tableStyle;
sheet.Range["B27:H38"].CellStyle.Color = Color.FromArgb(223, 223, 223);
sheet.Range["C28:C36"].CellStyle = tableStyle;
sheet.Range["B27:C27"].CellStyle.Color = Color.White;
sheet.Range["E28:F36"].CellStyle = tableStyle;
sheet.Range["B39:H49"].CellStyle.Color = Color.FromArgb(223, 223, 223);
sheet.Range["C40:C47"].CellStyle = tableStyle;
sheet.Range["B39:C39"].CellStyle.Color = Color.White;
sheet.Range["E40:F47"].CellStyle = tableStyle;
sheet.Range["B50:H58"].CellStyle.Color = Color.FromArgb(223, 223, 223);
sheet.Range["C51:C57"].CellStyle = tableStyle;
sheet.Range["B50:C50"].CellStyle.Color = Color.White;
sheet.Range["E51:F57"].CellStyle = tableStyle;
#endregion
#region Data Validation
IDataValidation validation = sheet.Range["E9:E13"].DataValidation;
sheet.Range["E9:E13"].Text = "Monthly";
validation.ListOfValues = new string[] { "Daily", "Weekly", "Monthly", "Semi-Annually", "Quarterly", "Yearly" };
IDataValidation validation1 = sheet.Range["E18:E25"].DataValidation;
sheet.Range["E18:E25"].Text = "Monthly";
validation1.ListOfValues = new string[] { "Daily", "Weekly", "Monthly", "Semi-Annually", "Quarterly", "Yearly" };
IDataValidation validation2 = sheet.Range["O6"].DataValidation;
validation2.ListOfValues = new string[] { "Weekly", "Monthly", "Yearly" };
IDataValidation validation3 = sheet.Range["E28:E37"].DataValidation;
sheet.Range["E28:E36"].Text = "Monthly";
validation3.ListOfValues = new string[] { "Daily", "Weekly", "Monthly", "Semi-Annually", "Quarterly", "Yearly" };
IDataValidation validation4 = sheet.Range["E40:E47"].DataValidation;
sheet.Range["E40:E47"].Text = "Monthly";
validation4.ListOfValues = new string[] { "Daily", "Weekly", "Monthly", "Semi-Annually", "Quarterly", "Yearly" };
IDataValidation validation5 = sheet.Range["E51:E57"].DataValidation;
sheet.Range["E51:E57"].Text = "Monthly";
validation5.ListOfValues = new string[] { "Daily", "Weekly", "Monthly", "Semi-Annually", "Quarterly", "Yearly" };
#endregion
#region Formulas
sheet.Range["G8"].Formula = "SUM(G9:G13)";
sheet.Range["H8"].Formula = "SUM(H9:H13)";
sheet.Range["G17"].Formula = "SUM(G18:G25)";
sheet.Range["H17"].Formula = "SUM(H18:H25)";
sheet.Range["G16"].Formula = "G17+G27+G39+G50+G59+G71";
sheet.Range["h16"].Formula = "H17+H27+H39+H50+H59+H71";
for (int i = 9; i <= 13; i++)
{
sheet.Range["G" + i].Formula = "F" + i + "*A" + i;
sheet.Range["H" + i].Formula = "G" + i + "*12";
}
for (int i = 18; i <= 25; i++)
{
sheet.Range["G" + i].Formula = "F" + i + "*A" + i;
sheet.Range["H" + i].Formula = "G" + i + "*12";
}
sheet.Range["G27"].Formula = "SUM(G28:G36)";
sheet.Range["H27"].Formula = "SUM(H28:H37)";
for (int i = 28; i <= 36; i++)
{
sheet.Range["G" + i].Formula = "F" + i + "*A" + i;
sheet.Range["H" + i].Formula = "G" + i + "*12";
}
sheet.Range["G39"].Formula = "SUM(G40:G47)";
sheet.Range["H39"].Formula = "SUM(H40:H47)";
for (int i = 40; i <= 47; i++)
{
sheet.Range["G" + i].Formula = "F" + i + "*A" + i;
sheet.Range["H" + i].Formula = "G" + i + "*12";
}
sheet.Range["G50"].Formula = "SUM(G51:G57)";
sheet.Range["H50"].Formula = "SUM(H51:H57)";
for (int i = 51; i <= 57; i++)
{
sheet.Range["G" + i].Formula = "F" + i + "*A" + i;
sheet.Range["H" + i].Formula = "G" + i + "*12";
}
#endregion
#region SummaryChart
//Clustered Column Chart
IChartShape chart = sheet.Charts.Add();
//Set Chart Type
chart.ChartType = ExcelChartType.Bar_Clustered;
//Set DataRange.
chart.Series.Add("Expense");
chart.Series[0].Values = workbook.Worksheets["Sheet1"].Range["N10"];
chart.Series[0].DataPoints[0].DataLabels.IsValue = true;
chart.Series[0].DataPoints[0].DataLabels.Size = 7f;
chart.Series.Add("Income");
chart.Series[1].Values = workbook.Worksheets["Sheet1"].Range["N9"];
chart.Series[1].DataPoints[0].DataLabels.IsValue = true;
chart.Series[1].DataPoints[0].DataLabels.Size = 7f;
chart.Series.Add("Balance");
chart.Series[2].Values = workbook.Worksheets["Sheet1"].Range["N8"];
chart.Series[2].DataPoints[0].DataLabels.IsValue = true;
chart.Series[2].DataPoints[0].DataLabels.Size = 7f;
chart.PrimaryValueAxis.NumberFormat = "$#,##0";
chart.PrimaryCategoryAxis.Visible = false;
//Format Chart Area
IChartFrameFormat chartArea = chart.ChartArea;
//Style
chartArea.Border.LinePattern = ExcelChartLinePattern.Solid;
chartArea.Border.LineColor = Color.Gray;
chartArea.Border.LineWeight = ExcelChartLineWeight.Medium;
//Plot Area
IChartFrameFormat chartPlotArea = chart.PlotArea;
chartPlotArea.Border.LinePattern = ExcelChartLinePattern.Solid;
chart.PlotArea.Border.LineColor = Color.Gray;
chart.Legend.Position = ExcelLegendPosition.Bottom;
//Embedded chart position.
chart.TopRow = 7;
chart.BottomRow = 22;
chart.LeftColumn = 9;
chart.RightColumn = 16;
chart.ChartTitle = "Budget Summary";
chart.ChartTitleArea.Bold = true;
#endregion
#region SpendingChart
chart = sheet.Charts.Add();
chart.ChartTitle = "Spending Summary";
chart.ChartTitleArea.Bold = true;
//Set Chart Type
chart.ChartType = ExcelChartType.Pie_3D;
//Set DataRange.
chart.DataRange = workbook.Worksheets["Sheet1"].Range["J9:K12"];
chart.IsSeriesInRows = false;
chart.Series[0].Values = workbook.Worksheets["Sheet1"].Range["K9:K12"];
chart.Series[0].CategoryLabels = workbook.Worksheets["Sheet1"].Range["J9:J12"];
chart.Series[0].Name = "Spending summary";
chart.Series[0].DataPoints[0].DataLabels.IsValue = true;
chart.Series[0].DataPoints[0].DataLabels.Size = 7f;
chart.Series[0].DataPoints[1].DataLabels.IsValue = true;
chart.Series[0].DataPoints[1].DataLabels.Size = 7f;
chart.Series[0].DataPoints[2].DataLabels.IsValue = true;
chart.Series[0].DataPoints[2].DataLabels.Size = 7f;
chart.Series[0].DataPoints[3].DataLabels.IsValue = true;
chart.Series[0].DataPoints[3].DataLabels.Size = 7f;
chart.PrimaryValueAxis.NumberFormat = "$#,##0";
//Format Chart Area
chartArea = chart.ChartArea;
//Style
chartArea.Border.LinePattern = ExcelChartLinePattern.Solid;
chartArea.Border.LineColor = Color.Gray;
chartArea.Border.LineWeight = ExcelChartLineWeight.Medium;
//Plot Area
chartPlotArea = chart.PlotArea;
chartPlotArea.Border.LinePattern = ExcelChartLinePattern.Solid;
chart.PlotArea.Border.LineColor = Color.Gray;
chartPlotArea.Fill.ForeColor = Color.FromArgb(223, 223, 223);
chart.Legend.Position = ExcelLegendPosition.Bottom;
//Embedded chart position.
chart.TopRow = 25;
chart.BottomRow = 42;
chart.LeftColumn = 9;
chart.RightColumn = 16;
#endregion
#region Sheet View
workbook.Worksheets["Sheet1"].Visibility = WorksheetVisibility.Hidden;
workbook.Worksheets[0].Activate();
workbook.TabSheets[0].TabColor = ExcelKnownColors.Blue;
workbook.TabSheets[1].TabColor = ExcelKnownColors.Blue;
workbook.Worksheets[1].IsRowColumnHeadersVisible = false;
sheet.InsertColumn(9);
#endregion
try
{
//Saving the workbook to disk.
if (SaveOption == "Xls")
{
//Save as .xls format
return(excelEngine.SaveAsActionResult(workbook, "SpreadSheet.xls", HttpContext.ApplicationInstance.Response, ExcelDownloadType.PromptDialog, ExcelHttpContentType.Excel97));
}
//Save as .xlsx format
else
{
workbook.Version = ExcelVersion.Excel2016;
return(excelEngine.SaveAsActionResult(workbook, "SpreadSheet.xlsx", HttpContext.ApplicationInstance.Response, ExcelDownloadType.PromptDialog, ExcelHttpContentType.Excel2016));
}
}
catch (Exception)
{
}
//Close the workbook.
workbook.Close();
excelEngine.Dispose();
return(View());
}
//
// GET: /DataValidation/
public ActionResult DataValidation(string SaveOption)
{
if (SaveOption == null)
{
return(View());
}
//New instance of XlsIO is created.[Equivalent to launching Microsoft Excel with no workbooks open].
//The instantiation process consists of two steps.
//Step 1 : Instantiate the spreadsheet creation engine.
ExcelEngine excelEngine = new ExcelEngine();
//Step 2 : Instantiate the excel application object.
IApplication application = excelEngine.Excel;
if (SaveOption == "Xls")
{
application.DefaultVersion = ExcelVersion.Excel97to2003;
}
else
{
application.DefaultVersion = ExcelVersion.Excel2016;
}
//A new workbook is created.[Equivalent to creating a new workbook in Microsoft Excel]
//The new workbook will have 3 worksheets
IWorkbook workbook = application.Workbooks.Create(3);
//The first worksheet object in the worksheets collection is accessed.
IWorksheet sheet = workbook.Worksheets[0];
//Data validation to list the values in the first cell
IDataValidation validation = sheet.Range["C7"].DataValidation;
sheet.Range["B7"].Text = "Select an item from the validation list";
validation.ListOfValues = new string[] { "PDF", "XlsIO", "DocIO" };
validation.PromptBoxText = "Data Validation list";
validation.IsPromptBoxVisible = true;
validation.ShowPromptBox = true;
sheet.Range["C7"].CellStyle.Borders.LineStyle = ExcelLineStyle.Medium;
sheet.Range["C7"].CellStyle.Borders[ExcelBordersIndex.DiagonalDown].ShowDiagonalLine = false;
sheet.Range["C7"].CellStyle.Borders[ExcelBordersIndex.DiagonalUp].ShowDiagonalLine = false;
// Data Validation for Numbers
IDataValidation validation1 = sheet.Range["C9"].DataValidation;
sheet.Range["B9"].Text = "Enter a Number to validate";
validation1.AllowType = ExcelDataType.Integer;
validation1.CompareOperator = ExcelDataValidationComparisonOperator.Between;
validation1.FirstFormula = "0";
validation1.SecondFormula = "10";
validation1.ShowErrorBox = true;
validation1.ErrorBoxText = "Enter Value between 0 to 10";
validation1.ErrorBoxTitle = "ERROR";
validation1.PromptBoxText = "Data Validation using Condition for Numbers";
validation1.ShowPromptBox = true;
sheet.Range["C9"].CellStyle.Borders.LineStyle = ExcelLineStyle.Medium;
sheet.Range["C9"].CellStyle.Borders[ExcelBordersIndex.DiagonalDown].ShowDiagonalLine = false;
sheet.Range["C9"].CellStyle.Borders[ExcelBordersIndex.DiagonalUp].ShowDiagonalLine = false;
// Data Validation for Date
IDataValidation validation2 = sheet.Range["C11"].DataValidation;
sheet.Range["B11"].Text = "Enter the Date to validate";
validation2.AllowType = ExcelDataType.Date;
validation2.CompareOperator = ExcelDataValidationComparisonOperator.Between;
validation2.FirstDateTime = new DateTime(2003, 5, 10);
validation2.SecondDateTime = new DateTime(2004, 5, 10);
validation2.ShowErrorBox = true;
validation2.ErrorBoxText = "Enter Value between 5/10/2003 to 5/10/2004";
validation2.ErrorBoxTitle = "ERROR";
validation2.PromptBoxText = "Data Validation using Condition for Date";
validation2.ShowPromptBox = true;
sheet.Range["C11"].CellStyle.Borders.LineStyle = ExcelLineStyle.Medium;
sheet.Range["C11"].CellStyle.Borders[ExcelBordersIndex.DiagonalDown].ShowDiagonalLine = false;
sheet.Range["C11"].CellStyle.Borders[ExcelBordersIndex.DiagonalUp].ShowDiagonalLine = false;
// Data Validation for TextLength
IDataValidation validation3 = sheet.Range["C13"].DataValidation;
sheet.Range["B13"].Text = "Enter the Text to validate";
validation3.AllowType = ExcelDataType.TextLength;
validation3.CompareOperator = ExcelDataValidationComparisonOperator.Between;
validation3.FirstFormula = "1";
validation3.SecondFormula = "6";
validation3.ShowErrorBox = true;
validation3.ErrorBoxText = "Retype text length to 6 character";
validation3.ErrorBoxTitle = "ERROR";
validation3.PromptBoxText = "Data Validation using Condition for TextLength";
validation3.ShowPromptBox = true;
sheet.Range["C13"].CellStyle.Borders.LineStyle = ExcelLineStyle.Medium;
sheet.Range["C13"].CellStyle.Borders[ExcelBordersIndex.DiagonalDown].ShowDiagonalLine = false;
sheet.Range["C13"].CellStyle.Borders[ExcelBordersIndex.DiagonalUp].ShowDiagonalLine = false;
// Data Validation for Time
IDataValidation validation4 = sheet.Range["C15"].DataValidation;
sheet.Range["B15"].Text = "Enter the Time to validate";
validation4.AllowType = ExcelDataType.Time;
validation4.CompareOperator = ExcelDataValidationComparisonOperator.Between;
validation4.FirstFormula = "10";
validation4.SecondFormula = "12";
validation4.ShowErrorBox = true;
validation4.ErrorBoxText = "Enter the Correct time between 10 to 12 ";
validation4.ErrorBoxTitle = "ERROR";
validation4.PromptBoxText = "Data Validation using Condition for Time";
validation4.ShowPromptBox = true;
sheet.Range["C15"].CellStyle.Borders.LineStyle = ExcelLineStyle.Medium;
sheet.Range["C15"].CellStyle.Borders[ExcelBordersIndex.DiagonalDown].ShowDiagonalLine = false;
sheet.Range["C15"].CellStyle.Borders[ExcelBordersIndex.DiagonalUp].ShowDiagonalLine = false;
sheet.Range["B2:C2"].Merge();
sheet.Range["B2"].Text = "Simple Data validation";
sheet.Range["B5"].Text = "Validation criteria";
sheet.Range["C5"].Text = "Validation";
sheet.Range["B5"].CellStyle.Font.Bold = true;
sheet.Range["C5"].CellStyle.Font.Bold = true;
sheet.Range["B2"].CellStyle.Font.Bold = true;
sheet.Range["B2"].CellStyle.Font.Size = 16;
sheet.Range["B2"].CellStyle.HorizontalAlignment = ExcelHAlign.HAlignCenter;
sheet.UsedRange.AutofitColumns();
sheet.UsedRange.AutofitRows();
try
{
//Saving the workbook to disk.
if (SaveOption == "Xls")
{
return(excelEngine.SaveAsActionResult(workbook, "DataValidation.xls", HttpContext.ApplicationInstance.Response, ExcelDownloadType.PromptDialog, ExcelHttpContentType.Excel97));
}
else
{
return(excelEngine.SaveAsActionResult(workbook, "DataValidation.xlsx", HttpContext.ApplicationInstance.Response, ExcelDownloadType.PromptDialog, ExcelHttpContentType.Excel2016));
}
}
catch (Exception)
{
}
workbook.Close();
excelEngine.Dispose();
return(View());
}
public void TestAddValidations()
{
XSSFWorkbook workbook = XSSFTestDataSamples.OpenSampleWorkbook("DataValidations-49244.xlsx");
ISheet sheet = workbook.GetSheetAt(0);
List <IDataValidation> dataValidations = ((XSSFSheet)sheet).GetDataValidations();
/**
* For each validation type, there are two cells with the same validation. This Tests
* application of a single validation defInition to multiple cells.
*
* For list ( 3 validations for explicit and 3 for formula )
* - one validation that allows blank.
* - one that does not allow blank.
* - one that does not show the drop down arrow.
* = 2
*
* For number validations ( integer/decimal and text length ) with 8 different types of operators.
* = 50
*
* = 52 ( Total )
*/
Assert.AreEqual(52, dataValidations.Count);
IDataValidationHelper dataValidationHelper = sheet.GetDataValidationHelper();
int[] validationTypes = new int[] { ValidationType.INTEGER, ValidationType.DECIMAL, ValidationType.TEXT_LENGTH };
int[] SingleOperandOperatorTypes = new int[] {
OperatorType.LESS_THAN, OperatorType.LESS_OR_EQUAL,
OperatorType.GREATER_THAN, OperatorType.GREATER_OR_EQUAL,
OperatorType.EQUAL, OperatorType.NOT_EQUAL
};
int[] doubleOperandOperatorTypes = new int[] {
OperatorType.BETWEEN, OperatorType.NOT_BETWEEN
};
decimal value = (decimal)10, value2 = (decimal)20;
double dvalue = (double)10.001, dvalue2 = (double)19.999;
int lastRow = sheet.LastRowNum;
int offset = lastRow + 3;
int lastKnownNumValidations = dataValidations.Count;
IRow row = sheet.CreateRow(offset++);
ICell cell = row.CreateCell(0);
IDataValidationConstraint explicitListValidation = dataValidationHelper.CreateExplicitListConstraint(new String[] { "MA", "MI", "CA" });
CellRangeAddressList cellRangeAddressList = new CellRangeAddressList();
cellRangeAddressList.AddCellRangeAddress(cell.RowIndex, cell.ColumnIndex, cell.RowIndex, cell.ColumnIndex);
IDataValidation dataValidation = dataValidationHelper.CreateValidation(explicitListValidation, cellRangeAddressList);
SetOtherValidationParameters(dataValidation);
sheet.AddValidationData(dataValidation);
lastKnownNumValidations++;
row = sheet.CreateRow(offset++);
cell = row.CreateCell(0);
cellRangeAddressList = new CellRangeAddressList();
cellRangeAddressList.AddCellRangeAddress(cell.RowIndex, cell.ColumnIndex, cell.RowIndex, cell.ColumnIndex);
ICell firstCell = row.CreateCell(1); firstCell.SetCellValue("UT");
ICell secondCell = row.CreateCell(2); secondCell.SetCellValue("MN");
ICell thirdCell = row.CreateCell(3); thirdCell.SetCellValue("IL");
int rowNum = row.RowNum + 1;
String listFormula = new StringBuilder("$B$").Append(rowNum).Append(":").Append("$D$").Append(rowNum).ToString();
IDataValidationConstraint formulaListValidation = dataValidationHelper.CreateFormulaListConstraint(listFormula);
dataValidation = dataValidationHelper.CreateValidation(formulaListValidation, cellRangeAddressList);
SetOtherValidationParameters(dataValidation);
sheet.AddValidationData(dataValidation);
lastKnownNumValidations++;
offset++;
offset++;
for (int i = 0; i < validationTypes.Length; i++)
{
int validationType = validationTypes[i];
offset = offset + 2;
IRow row0 = sheet.CreateRow(offset++);
ICell cell_10 = row0.CreateCell(0);
cell_10.SetCellValue(validationType == ValidationType.DECIMAL ? "Decimal " : validationType == ValidationType.INTEGER ? "int" : "Text Length");
offset++;
for (int j = 0; j < SingleOperandOperatorTypes.Length; j++)
{
int operatorType = SingleOperandOperatorTypes[j];
IRow row1 = sheet.CreateRow(offset++);
//For int (> and >=) we add 1 extra cell for validations whose formulae reference other cells.
IRow row2 = i == 0 && j < 2 ? sheet.CreateRow(offset++) : null;
cell_10 = row1.CreateCell(0);
cell_10.SetCellValue(XSSFDataValidation.operatorTypeMappings[operatorType].ToString());
ICell cell_11 = row1.CreateCell(1);
ICell cell_21 = row1.CreateCell(2);
ICell cell_22 = i == 0 && j < 2 ? row2.CreateCell(2) : null;
ICell cell_13 = row1.CreateCell(3);
cell_13.SetCellType(CellType.Numeric);
cell_13.SetCellValue(validationType == ValidationType.DECIMAL ? dvalue : (double)value);
//First create value based validation;
IDataValidationConstraint constraint = dataValidationHelper.CreateNumericConstraint(validationType, operatorType, value.ToString(), null);
cellRangeAddressList = new CellRangeAddressList();
cellRangeAddressList.AddCellRangeAddress(new CellRangeAddress(cell_11.RowIndex, cell_11.RowIndex, cell_11.ColumnIndex, cell_11.ColumnIndex));
IDataValidation validation = dataValidationHelper.CreateValidation(constraint, cellRangeAddressList);
SetOtherValidationParameters(validation);
sheet.AddValidationData(validation);
Assert.AreEqual(++lastKnownNumValidations, ((XSSFSheet)sheet).GetDataValidations().Count);
//Now create real formula based validation.
String formula1 = new CellReference(cell_13.RowIndex, cell_13.ColumnIndex).FormatAsString();
constraint = dataValidationHelper.CreateNumericConstraint(validationType, operatorType, formula1, null);
if (i == 0 && j == 0)
{
cellRangeAddressList = new CellRangeAddressList();
cellRangeAddressList.AddCellRangeAddress(new CellRangeAddress(cell_21.RowIndex, cell_21.RowIndex, cell_21.ColumnIndex, cell_21.ColumnIndex));
validation = dataValidationHelper.CreateValidation(constraint, cellRangeAddressList);
SetOtherValidationParameters(validation);
sheet.AddValidationData(validation);
Assert.AreEqual(++lastKnownNumValidations, ((XSSFSheet)sheet).GetDataValidations().Count);
cellRangeAddressList = new CellRangeAddressList();
cellRangeAddressList.AddCellRangeAddress(new CellRangeAddress(cell_22.RowIndex, cell_22.RowIndex, cell_22.ColumnIndex, cell_22.ColumnIndex));
validation = dataValidationHelper.CreateValidation(constraint, cellRangeAddressList);
SetOtherValidationParameters(validation);
sheet.AddValidationData(validation);
Assert.AreEqual(++lastKnownNumValidations, ((XSSFSheet)sheet).GetDataValidations().Count);
}
else if (i == 0 && j == 1)
{
cellRangeAddressList = new CellRangeAddressList();
cellRangeAddressList.AddCellRangeAddress(new CellRangeAddress(cell_21.RowIndex, cell_21.RowIndex, cell_21.ColumnIndex, cell_21.ColumnIndex));
cellRangeAddressList.AddCellRangeAddress(new CellRangeAddress(cell_22.RowIndex, cell_22.RowIndex, cell_22.ColumnIndex, cell_22.ColumnIndex));
validation = dataValidationHelper.CreateValidation(constraint, cellRangeAddressList);
SetOtherValidationParameters(validation);
sheet.AddValidationData(validation);
Assert.AreEqual(++lastKnownNumValidations, ((XSSFSheet)sheet).GetDataValidations().Count);
}
else
{
cellRangeAddressList = new CellRangeAddressList();
cellRangeAddressList.AddCellRangeAddress(new CellRangeAddress(cell_21.RowIndex, cell_21.RowIndex, cell_21.ColumnIndex, cell_21.ColumnIndex));
validation = dataValidationHelper.CreateValidation(constraint, cellRangeAddressList);
SetOtherValidationParameters(validation);
sheet.AddValidationData(validation);
Assert.AreEqual(++lastKnownNumValidations, ((XSSFSheet)sheet).GetDataValidations().Count);
}
}
for (int j = 0; j < doubleOperandOperatorTypes.Length; j++)
{
int operatorType = doubleOperandOperatorTypes[j];
IRow row1 = sheet.CreateRow(offset++);
cell_10 = row1.CreateCell(0);
cell_10.SetCellValue(XSSFDataValidation.operatorTypeMappings[operatorType].ToString());
ICell cell_11 = row1.CreateCell(1);
ICell cell_21 = row1.CreateCell(2);
ICell cell_13 = row1.CreateCell(3);
ICell cell_14 = row1.CreateCell(4);
String value1String = validationType == ValidationType.DECIMAL ? dvalue.ToString() : value.ToString();
cell_13.SetCellType(CellType.Numeric);
cell_13.SetCellValue(validationType == ValidationType.DECIMAL ? dvalue : (int)value);
String value2String = validationType == ValidationType.DECIMAL ? dvalue2.ToString() : value2.ToString();
cell_14.SetCellType(CellType.Numeric);
cell_14.SetCellValue(validationType == ValidationType.DECIMAL ? dvalue2 : (int)value2);
//First create value based validation;
IDataValidationConstraint constraint = dataValidationHelper.CreateNumericConstraint(validationType, operatorType, value1String, value2String);
cellRangeAddressList = new CellRangeAddressList();
cellRangeAddressList.AddCellRangeAddress(new CellRangeAddress(cell_11.RowIndex, cell_11.RowIndex, cell_11.ColumnIndex, cell_11.ColumnIndex));
IDataValidation validation = dataValidationHelper.CreateValidation(constraint, cellRangeAddressList);
SetOtherValidationParameters(validation);
sheet.AddValidationData(validation);
Assert.AreEqual(++lastKnownNumValidations, ((XSSFSheet)sheet).GetDataValidations().Count);
//Now create real formula based validation.
String formula1 = new CellReference(cell_13.RowIndex, cell_13.ColumnIndex).FormatAsString();
String formula2 = new CellReference(cell_14.RowIndex, cell_14.ColumnIndex).FormatAsString();
constraint = dataValidationHelper.CreateNumericConstraint(validationType, operatorType, formula1, formula2);
cellRangeAddressList = new CellRangeAddressList();
cellRangeAddressList.AddCellRangeAddress(new CellRangeAddress(cell_21.RowIndex, cell_21.RowIndex, cell_21.ColumnIndex, cell_21.ColumnIndex));
validation = dataValidationHelper.CreateValidation(constraint, cellRangeAddressList);
SetOtherValidationParameters(validation);
sheet.AddValidationData(validation);
Assert.AreEqual(++lastKnownNumValidations, ((XSSFSheet)sheet).GetDataValidations().Count);
}
}
workbook = (XSSFWorkbook)XSSFTestDataSamples.WriteOutAndReadBack(workbook);
ISheet sheetAt = workbook.GetSheetAt(0);
Assert.AreEqual(lastKnownNumValidations, ((XSSFSheet)sheetAt).GetDataValidations().Count);
}
public void AddValidationData(IDataValidation dataValidation)
{
XSSFDataValidation xssfDataValidation = (XSSFDataValidation)dataValidation;
CT_DataValidations dataValidations = worksheet.dataValidations;
if (dataValidations == null)
{
dataValidations = worksheet.AddNewDataValidations();
}
int currentCount = dataValidations.sizeOfDataValidationArray();
CT_DataValidation newval = dataValidations.AddNewDataValidation();
newval.Set(xssfDataValidation.GetCTDataValidation());
dataValidations.count = (uint)currentCount + 1;
}
