// <summary>
/// The add validation.
/// </summary>
/// <param name="sheet">
/// 要加入列表的sheet
/// </param>
/// <param name="itemSheet">
/// 选项 sheet.
/// </param>
/// <param name="headerCell">
/// 标题单元格
/// </param>
/// <param name="items">
/// 列表项
/// </param>
private static void AddValidation(ISheet sheet, ISheet itemSheet, ICell headerCell, List <string> items)
{
// 新建行
var row = itemSheet.CreateRow(itemSheet.PhysicalNumberOfRows);
// 新行中写入选项
for (int i = 0; i < items.Count; i++)
{
var cell = row.CreateCell(i);
cell.SetCellValue(items[i]);
}
// 要加下拉列表的范围
var addressList = new CellRangeAddressList(
headerCell.RowIndex + 1,
65535,
headerCell.ColumnIndex,
headerCell.ColumnIndex);
var dvHelper = sheet.GetDataValidationHelper();
// 格式 Sheet2!$A$1:$E$1
var dvConstraint = dvHelper.CreateFormulaListConstraint(
$"{itemSheet.SheetName}!$A${row.RowNum + 1}:${(items.Count)}${row.RowNum + 1}");
var validation = dvHelper.CreateValidation(dvConstraint, addressList);
// 强制必须填下拉列表给出的值
// validation.ShowErrorBox = true;
sheet.AddValidationData(validation);
}
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);
}
}
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);
}
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 void SetSheetValidationForListConstraint(string sheetName, string rangeName, MergeCellRange cellRange)
{
CellRangeAddressList rangeList = new CellRangeAddressList(cellRange.FirstRowIndex, cellRange.LastRowIndex, cellRange.FirstColumnIndex, cellRange.LastColumnIndex);
ISheet sheet = _workbook.GetSheet(sheetName);
IDataValidationHelper dataValidationHelper = sheet.GetDataValidationHelper();
IDataValidationConstraint constraint = dataValidationHelper.CreateFormulaListConstraint(rangeName);
HSSFDataValidation validation = (HSSFDataValidation)dataValidationHelper.CreateValidation(constraint, rangeList);
sheet.AddValidationData(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);
}
/// <summary>设置列下拉列表</summary>
public static void SetColumnDropdownList(this ISheet sheet, string[] values, int startRow, int column)
{
var regions = new CellRangeAddressList(startRow, 65535, column, column);
var helper = sheet.GetDataValidationHelper();
var constraint = helper.CreateExplicitListConstraint(values);
var validation = helper.CreateValidation(constraint, regions);
validation.CreateErrorBox("输入不合法", "请输入下拉列表中的值。");
validation.ShowPromptBox = true;
sheet.AddValidationData(validation);
}
/// <summary>
/// 添加下拉框
/// </summary>
/// <param name="exSheet">Excel表单对象</param>
/// <param name="field">字段规则</param>
/// <param name="rowIndex">起始行号</param>
/// <param name="rowSpan">结束行号</param>
public static void AddDropDownList(ISheet exSheet, string[] values, int colIndex, int rowIndex, int colSpan = 1, int rowSpan = 1)
{
if (values.Length > 0)
{
IDataValidationHelper helper = exSheet.GetDataValidationHelper();
IDataValidationConstraint dvconstraint = helper.CreateExplicitListConstraint(values);
CellRangeAddressList rangeList = new CellRangeAddressList(rowIndex, rowIndex + rowSpan - 1, colIndex, colIndex + colSpan - 1);
//DVConstraint constraint = DVConstraint.CreateExplicitListConstraint(new string[] { "itemA", "itemB", "itemC" });
//exSheet.AddValidationData(new HSSFDataValidation(rangeList, constraint));
exSheet.AddValidationData(helper.CreateValidation(dvconstraint, rangeList));
}
}
public void Render(ICell cell)
{
if (ValueList.Length > 0)
{
ISheet sheet = cell.Sheet;
IDataValidationHelper helper = sheet.GetDataValidationHelper();
IDataValidationConstraint dvconstraint = helper.CreateExplicitListConstraint(ValueList);
CellRangeAddressList rangeList = new CellRangeAddressList(FillArea.RowIndex, FillArea.RowIndex + FillArea.RowCount - 1,
FillArea.ColIndex, FillArea.ColIndex + FillArea.ColCount - 1);
//DVConstraint constraint = DVConstraint.CreateExplicitListConstraint(new string[] { "itemA", "itemB", "itemC" });
//exSheet.AddValidationData(new HSSFDataValidation(rangeList, constraint));
sheet.AddValidationData(helper.CreateValidation(dvconstraint, rangeList));
}
}
/// <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
}
protected void AddOneFromManyValidation(int columnIndex, string sheetWithValuesName, string[] values, ISheet sheet)
{
var workbook = sheet.Workbook;
var valuesSheet = workbook.CreateSheet(sheetWithValuesName);
var valuesSheetIndex = workbook.GetSheetIndex(valuesSheet);
workbook.SetSheetHidden(valuesSheetIndex, SheetState.VeryHidden);
for (int i = 0; i < values.Length; i++)
{
valuesSheet.CreateRow(i)
.CreateCell(0)
.SetCellValue(values[i]);
}
var addressList = new CellRangeAddressList(1, MaxRowsPerSheet - 1, columnIndex, columnIndex);
var validationHelper = sheet.GetDataValidationHelper();
var validationConstraint = validationHelper.CreateFormulaListConstraint($"{sheetWithValuesName}!$A$1:$A$" + values.Length);
var validation = validationHelper.CreateValidation(validationConstraint, addressList);
validation.ShowErrorBox = true;
validation.CreateErrorBox("Validation failed", "Please choose a valid value.");
sheet.AddValidationData(validation);
}
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);
}
private void PutNamesWithValidations(ISheet ws, ICellStyle cs1, ICellStyle cs2, ICellStyle cs3, params string[][] names)
{
var rn0 = ws.PhysicalNumberOfRows;
var rn = rn0;
for (var i = 0; i < names.Length; ++i)
{
var rnc = rn++;
var r = ws.CreateRow(rnc);
if (1 < names[i].Length)
{
r.RowStyle = cs3;
var vh = ws.GetDataValidationHelper();
var vl = names[i].Skip(1).ToList();
vl.Sort();
var vd = vh.CreateValidation(
vh.CreateExplicitListConstraint(vl.ToArray()),
new CellRangeAddressList(rnc, rnc, 1, _excelVer.LastColumnIndex)
);
vd.ShowErrorBox = false;
vd.ShowPromptBox = true;
ws.AddValidationData(vd);
}
var c = r.CreateCell(0);
c.SetCellValue(names[i][0]);
if (0 == i)
{
c.CellStyle = cs1;
r.RowStyle = cs1;
}
else
{
c.CellStyle = cs2;
}
}
if (1 < names.Length) ws.GroupRow(rn0 + 1, rn - 1);
//ws.SetRowGroupCollapsed(rn0 + 1, true);
ws.AddMergedRegion(new CellRangeAddress(rn0, rn0, 0, _excelVer.LastColumnIndex));
}
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 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();
#if !HIDE_UNREACHABLE_CODE
if (false)
{ // TODO (Jul 2008) fix EventRecordFactory to process unknown records, (and DV records for that matter)
ERFListener erfListener = null; // new MyERFListener();
EventRecordFactory erf = new EventRecordFactory(erfListener, null);
try
{
POIFSFileSystem fs = new POIFSFileSystem(new MemoryStream(baos.ToArray()));
throw new NotImplementedException("The method CreateDocumentInputStream of POIFSFileSystem is not implemented.");
//erf.ProcessRecords(fs.CreateDocumentInputStream("Workbook"));
}
catch (RecordFormatException e)
{
throw new RuntimeException(e);
}
catch (IOException e)
{
throw new RuntimeException(e);
}
}
// else verify record ordering by navigating the raw bytes
#endif
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);
}
/// <summary>
/// 将数据生成为excel文件
/// 创建者：戚鹏
/// 创建日期：2013.5.20
/// <param name="info">配置信息</param>
/// <param name="tempFileFullPath">文件名</param>
/// </summary>
public bool WriteWorkbookAuto(string tempFileFullPath, SheetInfo info, DataTable dt,
LAF.Common.ExcelOperation.ExcelOperationHelper.DelegateCheck dCheck, params string[] Edition)
{
try
{
XSSFWorkbook workbook;
workbook = new XSSFWorkbook();
ISheet sheet = workbook.CreateSheet(info.SheetName);
//冻结列
sheet.CreateFreezePane(0, 2, 0, 2);
//工作簿加密
//TODO:暂时不加密 为了标题能够过滤
if (info.Protect)
{
sheet.ProtectSheet(EXCELPWD);
}
#region 主体内容
#region Title样式
IRow rows = sheet.CreateRow(1);
rows.HeightInPoints = 30;
#endregion
#region 隐藏行
IRow rowHidden = sheet.CreateRow(0);
rowHidden.Height = 1;
#endregion
for (int i = 0; i < info.ColInfos.Count; i++)
{
int ColumnWidth = info.ColInfos[i].ColumnWidth == 0 ? 20 : info.ColInfos[i].ColumnWidth;
if (info.ColInfos[i].ColumnHidden)
{
sheet.SetColumnWidth(i, 0);
}
else
{
sheet.SetColumnWidth(i, ColumnWidth * 256);
}
#region Title样式
ICell cell1 = rows.CreateCell(i);
cell1.CellStyle = GetCellStyle(workbook, stylexls.Title);
string titleStr = info.ColInfos[i].ColumnTitle;
//标题*号进行样式设定
IRichTextString rt = workbook.GetCreationHelper().CreateRichTextString(titleStr);
if (titleStr.EndsWith("*"))
{
IFont fontStar = workbook.CreateFont();
fontStar.Boldweight = 600;
fontStar.Color = HSSFColor.OliveGreen.Red.Index;
rt.ApplyFont(titleStr.Length - 1, titleStr.Length, fontStar);
}
cell1.SetCellValue(rt);
#endregion
#region 写入数据KEY
ICell cell = rowHidden.CreateCell(i);
cell.SetCellValue(info.ColInfos[i].ColumnName);
cell.CellStyle.IsLocked = true;
#endregion
#region 添加验证
IDataValidationHelper helper = sheet.GetDataValidationHelper();
CellRangeAddressList range = getCellRangeAddressList(info.DataArray.Count, i);
if (info.ColInfos[i].ColumnRangeValues != null)
{
sheet.AddValidationData(ExcelOperationValidationHelper.getValidationExplicitList(helper, range, info.ColInfos[i].ColumnRangeValues));
}
if (info.ColInfos[i].ColValMaxLength.HasValue)
{
sheet.AddValidationData(ExcelOperationValidationHelper.getValidationTextLength(helper, range,
1, info.ColInfos[i].ColValMaxLength.GetValueOrDefault()));
}
switch (info.ColInfos[i].ColumnValidation)
{
case EmuExcelCellType.Integer:
sheet.AddValidationData(ExcelOperationValidationHelper.getValidationInt(helper, range));
break;
case EmuExcelCellType.Decimal:
sheet.AddValidationData(ExcelOperationValidationHelper.getValidationInt(helper, range));
break;
case EmuExcelCellType.YearMonth:
sheet.AddValidationData(ExcelOperationValidationHelper.getValidationYearMonth(helper, range));
break;
default:
break;
}
#endregion
#region 筛选
CellRangeAddress CellRange = new CellRangeAddressList(1, 1, 0, i).CellRangeAddresses[0];
sheet.SetAutoFilter(CellRange);
#endregion
}
ICellStyle style = SetCellBorder(workbook, false);
ICellStyle styleLock = SetCellBorder(workbook, true);
foreach (List <CellInfo> items in info.DataArray)
{
foreach (CellInfo item in items)
{
IRow row = sheet.GetRow(int.Parse(item.YPosition));
if (row == null)
{
row = sheet.CreateRow(int.Parse(item.YPosition));
}
ICell cell = row.GetCell(int.Parse(item.XPosition));
if (cell == null)
{
cell = row.CreateCell(int.Parse(item.XPosition));
}
double cellValue = 0;
//如果是数值 把单元格格式设置为数值
if (double.TryParse(item.Value, out cellValue))
{
cell.SetCellType(CellType.Numeric);
cell.SetCellValue(cellValue);
//XSSFCellStyle cellStyle = (XSSFCellStyle)workbook.CreateCellStyle();
//IDataFormat dataFormat = workbook.CreateDataFormat();
//cellStyle.DataFormat = dataFormat;
//cellStyle.
}
else
{
cell.SetCellType(CellType.String);
cell.SetCellValue(item.Value);
}
cell.CellStyle = item.ColumnLock ? styleLock : style;
}
}
#endregion
#region 数据校验
bool hasErr = false;
if (dCheck != null)
{
//复制dt结构（不复制数据）
DataTable dtTemp = dt.Clone();
DataRow[] rules = dt.Select();
foreach (var dr in rules)
{
//int RowCount = sheet.GetRow(dt.Rows.IndexOf(dr) + 2).LastCellNum;
//for (int i = 2; i < RowCount; i++)
//{
// ICell Cell = sheet.GetRow(dt.Rows.IndexOf(dr) + 2).Cells[i];
// Cell.CellComment = null;
// Cell.CellStyle = style;
//}
dtTemp.Rows.Add(dr.ItemArray);
Tuple <List <string[]> > tu = dCheck.Invoke(dtTemp);
if (tu.Item1.Count > 0)
{
hasErr = true;
for (int i = 0; i < tu.Item1.Count; i++)
{
int iConNum = int.Parse(tu.Item1[i][0]);
string strMsg = tu.Item1[i][1];
ICell Cell = sheet.GetRow(dt.Rows.IndexOf(dr) + 2).GetCell(iConNum);
Cell.CellStyle = GetCellBorderException(workbook);
Cell.CellComment = null;
Cell.CellComment = GetCellComment(sheet, strMsg);
}
}
else
{
//隐藏正确行
sheet.GetRow(dt.Rows.IndexOf(dr) + 2).Height = 1;
}
dtTemp.Rows.Clear();
}
}
#endregion
#region 版本号
ISheet sheetEdition = null;
sheetEdition = workbook.CreateSheet("Property");
for (int r = 0; r < Edition.Length; r++)
{
sheetEdition.CreateRow(r).CreateCell(0).SetCellValue(Edition[r]);
}
workbook.SetSheetHidden(1, SheetState.VeryHidden);
#endregion
//写入文件
using (FileStream fs = new FileStream(tempFileFullPath, FileMode.OpenOrCreate, FileAccess.ReadWrite))
{
workbook.Write(fs);
fs.Close();
}
return(hasErr);
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// 设置列类型
/// </summary>
/// <param name="excelGlobalDTO"></param>
public void SetSheetColumnType(ExcelGlobalDTO <TEntity> excelGlobalDTO)
{
//遍历Sheet实体集合
foreach (var item in excelGlobalDTO.Sheets)
{
ISheet sheet = excelGlobalDTO.Workbook.GetSheetAt(item.SheetIndex);
//判断是否为空
if (item.SheetEntityList == null)
{
continue;
}
//遍历行
foreach (var entity in item.SheetEntityList)
{
IRow row = sheet.GetRow(entity.RowNumber);
foreach (var head in item.SheetHeadList)
{
//获取列
ICell cell = row.GetCell(head.ColumnIndex);
//设置生成下拉框的行和列
//var cellRegions = new NPOI.SS.Util.CellRangeAddressList((item.StartRowIndex.Value) + 1, sheet.LastRowNum, head.ColumnIndex, head.ColumnIndex);
var cellRegions = new NPOI.SS.Util.CellRangeAddressList(entity.RowNumber, entity.RowNumber, head.ColumnIndex, head.ColumnIndex);
IDataValidationHelper dvHelper = sheet.GetDataValidationHelper();
IDataValidationConstraint dvConstraint = null;
IDataValidation dataValidate = null;
switch (head.ColumnType)
{
//列类型为文本
case Attribute.Enum.ColumnTypeEnum.Text:
break;
//列类型为日期
case Attribute.Enum.ColumnTypeEnum.Date:
head.Format = string.IsNullOrEmpty(head.Format) ? "yyyy-MM-dd" : head.Format;
dvConstraint = dvHelper.CreateDateConstraint(OperatorType.BETWEEN, DateTime.MinValue.ToString(head.Format), DateTime.MaxValue.ToString(head.Format), head.Format);
dataValidate = dvHelper.CreateValidation(dvConstraint, cellRegions);
dataValidate.CreateErrorBox("输入不合法", "必须为日期");
dataValidate.ShowPromptBox = true;
break;
//列类型为浮点
case Attribute.Enum.ColumnTypeEnum.Decimal:
dvConstraint = dvHelper.CreateDecimalConstraint(OperatorType.BETWEEN, "0", "9999999999");
dataValidate = dvHelper.CreateValidation(dvConstraint, cellRegions);
dataValidate.CreateErrorBox("输入不合法", "必须在0~9999999999之间。");
dataValidate.ShowPromptBox = true;
break;
//列类型为选项
case Attribute.Enum.ColumnTypeEnum.Option:
List <string> options = null;
#region 全局列设置
if (item.ColumnOptions != null && item.ColumnOptions.Count > 0)
{
string key = null;
//如果头部名称存在则取头部名称（以头部名称设置选项）
if (item.ColumnOptions.Keys.Contains(head.HeadName) == true)
{
key = head.HeadName;
}
//如果属性存在则取头部名称（以属性名称设置选项）
if (item.ColumnOptions.Keys.Contains(head.PropertyName) == true)
{
key = head.PropertyName;
}
//不为空说明存在，则设置选项
if (key != null)
{
options = item.ColumnOptions[key];
}
}
#endregion
#region 单个列设置
//行的优先级高于Sheet的优先级
if (entity.ColumnOptions != null && entity.ColumnOptions.Count > 0)
{
string key = null;
//如果头部名称存在则取头部名称（以头部名称设置选项）
if (entity.ColumnOptions.Keys.Contains(head.HeadName) == true)
{
key = head.HeadName;
}
//如果属性存在则取头部名称（以属性名称设置选项）
if (entity.ColumnOptions.Keys.Contains(head.PropertyName) == true)
{
key = head.PropertyName;
}
//不为空说明存在，则设置选项
if (key != null)
{
options = entity.ColumnOptions[key];
}
}
#endregion
//不符合条件则跳出
if (options == null)
{
continue;
}
if (options.Count() > 0)
{
dvConstraint = dvHelper.CreateExplicitListConstraint(options.ToArray());
dataValidate = dvHelper.CreateValidation(dvConstraint, cellRegions);
dataValidate.CreateErrorBox("输入不合法", "请选择下拉列表中的值。");
dataValidate.ShowPromptBox = true;
}
break;
}
//类型在指定的范围内是才设置校验
if (dataValidate != null)
{
sheet.AddValidationData(dataValidate);
}
}
}
}
}
/// <summary>
/// 生成Sheet的级联指定
/// </summary>
private static void CreateCascadeSheet(ISheet sheet, MDataTable header, Dictionary <string, string[]> validateData, int maxLevel)
{
Dictionary <string, int> formatdic = new Dictionary <string, int>();
MDataTable[] dt2 = header.Split(Config_Grid.Field + " like 'mg_%'");
header = dt2[1];//去掉多级表头的数据。
for (int i = 0; i < header.Rows.Count; i++)
{
string formater = header.Rows[i].Get <string>(Config_Grid.Formatter);
if (!string.IsNullOrEmpty(formater) && formater.Length > 1 && !formatdic.ContainsKey(formater))
{
formatdic.Add(formater, i);//存储列索引
}
}
int maxRow = 1000;//限制最大行数(07之前版本的excel最大行数为65536，但NOPI似乎没有支持到最大行数,这里设置为50000行,到60000行数据有效性失效)
IDataValidationHelper dvHelper = sheet.GetDataValidationHelper();
for (int i = 0; i < header.Rows.Count; i++)
{
MDataRow dtRow = header.Rows[i];
string formatter = dtRow.Get <string>(Config_Grid.Formatter);
if (formatter == "boolFormatter")
{
formatter = "#是否"; //对bool型特殊处理。
}
if (!string.IsNullOrEmpty(formatter) && formatter.StartsWith("#") && validateData != null && formatter.Length > 1) //&& validateData.ContainsKey(formatter)
{
//处理数据的有效性
CellRangeAddressList regions = null;
IDataValidationConstraint constraint = null;
IDataValidation dataValidate = null;
if (validateData.ContainsKey(formatter))
{
regions = new CellRangeAddressList(maxLevel, maxRow, i, i);
string key = formatter.Split('=')[0].Replace("#", "").Replace(" ", "");// "V" + (char)formatter.Length;// formatter.Replace("#", "V");
constraint = dvHelper.CreateFormulaListConstraint(key);
dataValidate = dvHelper.CreateValidation(constraint, regions);
sheet.AddValidationData(dataValidate);
}
if (formatter.StartsWith("#C"))//级联要接着父级后加数据有效性才行
{
string parentFormatter = formatter;
while (formatdic.ContainsKey(parentFormatter))
{
int point = 0;
int parentindex = formatdic[parentFormatter];
formatdic.Remove(parentFormatter);
foreach (var item in formatdic)
{
if (item.Key.IndexOf('=') > -1)
{
string parent = item.Key.Split('=')[1];
parent = parent.Replace(">", "#");
if (parent.Equals(parentFormatter.Split('=')[0]))
{
int selfindex = item.Value;
string t = ConvertIndexToChar(parentindex);
for (int im = maxLevel; im < maxRow; im++)
{
string func = string.Format("INDIRECT(${0}${1})", t, im + 1);
regions = new CellRangeAddressList(im, im, selfindex, selfindex);
constraint = dvHelper.CreateFormulaListConstraint(func);
dataValidate = dvHelper.CreateValidation(constraint, regions);
sheet.AddValidationData(dataValidate);
}
parentFormatter = item.Key;
break;
}
}
point += 1;
}
if (point.Equals(formatdic.Count))
{
parentFormatter = string.Empty;
}
}
}
}
}
}
void ResetData(IWorkbook workbook, ISheet sheet)
{
var sheetIndex = workbook.GetSheetIndex(sheet);
var sheetName = sheet.SheetName;
var copySheet = workbook.CreateSheet("copySheet");
var columNumber = 0;
for (int i = sheet.FirstRowNum; i <= sheet.LastRowNum; ++i)
{
var row = sheet.GetRow(i);
if (row == null)
{
continue;
}
var copyRow = copySheet.CreateRow(i);
columNumber = System.Math.Max(columNumber, row.LastCellNum);
for (int j = 0; j < row.LastCellNum; ++j)
{
var cell = row.GetCell(j);
if (cell == null)
{
continue;
}
var copyCell = copyRow.CreateCell(j);
if (cell.CellStyle != null)
{
copyCell.CellStyle = cell.CellStyle;
}
if (cell.CellComment != null)
{
copyCell.CellComment = cell.CellComment;
}
if (cell.Hyperlink != null)
{
copyCell.Hyperlink = cell.Hyperlink;
}
copyCell.SetCellType(cell.CellType);
switch (cell.CellType)
{
case CellType.Numeric: copyCell.SetCellValue(cell.NumericCellValue); break;
case CellType.String: copyCell.SetCellValue(cell.RichStringCellValue); break;
case CellType.Formula: copyCell.SetCellFormula(cell.CellFormula); break;
case CellType.Blank: copyCell.SetCellValue(cell.StringCellValue); break;
case CellType.Boolean: copyCell.SetCellValue(cell.BooleanCellValue); break;
case CellType.Error: copyCell.SetCellErrorValue(cell.ErrorCellValue); break;
}
}
}
for (int i = 0; i < columNumber; ++i)
{
copySheet.SetColumnWidth(i, sheet.GetColumnWidth(i));
}
sheet.GetDataValidations().ForEach((data) => { if (data.ErrorBoxTitle != EnumCheckError)
{
copySheet.AddValidationData(data);
}
});
for (int i = 0; i < sheet.SheetConditionalFormatting.NumConditionalFormattings; ++i)
{
var formatting = sheet.SheetConditionalFormatting.GetConditionalFormattingAt(i);
var had = false;
for (var j = 0; j < formatting.NumberOfRules; ++j)
{
var rule = formatting.GetRule(j);
if (rule.Formula1.EndsWith(EnumConditional))
{
had = true;
break;
}
}
if (!had)
{
copySheet.SheetConditionalFormatting.AddConditionalFormatting(formatting);
}
}
var start = -1;
for (var i = copySheet.FirstRowNum; i <= copySheet.LastRowNum; ++i)
{
var row = copySheet.GetRow(i);
if (row == null)
{
continue;
}
var keyCell = row.GetCellString(0);
if (keyCell == KEYWORD_BEGIN)
{
start = i;
break;
}
}
if (start == -1)
{
return;
}
for (var i = 0; i < mFields.Count; ++i)
{
var field = mFields[i];
if (field.IsValid && !field.IsArray && field.IsEnum)
{
var cellRange = new CellRangeAddressList(start, 65535, i + 1, i + 1);
//添加下拉框
{
var helper = sheet.GetDataValidationHelper();
var constraint = helper.CreateExplicitListConstraint(mParser.GetEnumList(field.Type));
var dataValidation = helper.CreateValidation(constraint, cellRange);
dataValidation.SuppressDropDownArrow = true;
dataValidation.CreateErrorBox(EnumCheckError, "");
copySheet.AddValidationData(dataValidation);
}
//添加值验证
{
var lineName = (i + 1).GetLineName();
var ands = new List <string>();
Array.ForEach(mParser.GetEnumList(field.Type), (value) => { ands.Add($"{lineName}{start + 1}<>\"{value}\""); });
var conditional = copySheet.SheetConditionalFormatting.CreateConditionalFormattingRule(string.Format("=AND({0})+{1}", string.Join(",", ands.ToArray()), EnumConditional));
var pattern = conditional.CreatePatternFormatting();
pattern.FillBackgroundColor = IndexedColors.Red.Index;
pattern.FillPattern = FillPattern.SolidForeground;
copySheet.SheetConditionalFormatting.AddConditionalFormatting(cellRange.CellRangeAddresses, conditional);
}
}
}
workbook.RemoveSheetAt(sheetIndex);
workbook.SetSheetOrder(copySheet.SheetName, sheetIndex);
workbook.SetSheetName(sheetIndex, sheetName);
}
private static void buildTemplateSheet(IWorkbook workbook, ISheet sheet, ISheet sheetDS, List<WFActivityField> fields, ref int dsStartIndex, int sheetIndex)
{
if (fields != null)
{
IRow rowColumn = sheet.CreateRow(0);
for (int i = 0; i < fields.Count; i++)
{
ICell cell = rowColumn.CreateCell(i);
string colName = String.IsNullOrWhiteSpace(fields[i].DisplayName) ? fields[i].FieldName : fields[i].DisplayName;
cell.SetCellValue(colName);
ICellStyle cellStyle = workbook.CreateCellStyle();
if (fields[i].IsRequired)
{
cellStyle.FillForegroundColor = IndexedColors.Yellow.Index;
}
else
{
cellStyle.FillForegroundColor = IndexedColors.Grey25Percent.Index;
}
cellStyle.FillPattern = FillPattern.SolidForeground;
cell.CellStyle = cellStyle;
sheet.AutoSizeColumn(i);
switch (fields[i].DataType)
{
case FieldInfo.DATATYPE_LIST:
case FieldInfo.DATATYPE_LIST_SQL:
string lstFormulaName = fields[i].FieldName + "fn";
int dsEndIndex = BuildDataSource(fields[i], sheetDS, dsStartIndex);
if (dsEndIndex > dsStartIndex)
{
IName name = sheet.Workbook.CreateName();
name.RefersToFormula = String.Format("'DataSource'!$A${0}:$A${1}", dsStartIndex + 1, dsEndIndex);
name.NameName = lstFormulaName;
name.SheetIndex = sheetIndex;
CellRangeAddressList addressList = new CellRangeAddressList(1, 1, i, i);
IDataValidationHelper dvHelper = sheet.GetDataValidationHelper();
IDataValidationConstraint dvConstraint = dvHelper.CreateFormulaListConstraint(lstFormulaName);
IDataValidation validation = dvHelper.CreateValidation(dvConstraint, addressList);
sheet.AddValidationData(validation);
dsStartIndex = dsEndIndex;
}
break;
}
}
}
}
//刷新表注释
private void RefreshNote(string fileFullName)
{
bool change = false;
for (int i = 0; i < mFields.Count; ++i)
{
var filed = mFields[i];
if (filed.Enum)
{
change = true; break;
}
}
if (!change)
{
return;
}
IWorkbook workbook = new HSSFWorkbook(new FileStream(fileFullName, FileMode.Open, FileAccess.ReadWrite));
ISheet sheet = workbook.GetSheetAt(0);
string sheetName = sheet.SheetName;
ISheet sheetBack = workbook.CreateSheet("__sheetBackup");
sheetBack.CreateFreezePane(2, START_ROW);
int columnNum = 0;
for (int i = sheet.FirstRowNum; i <= sheet.LastRowNum; ++i)
{
IRow row = sheet.GetRow(i);
if (row == null)
{
continue;
}
if (row.LastCellNum > columnNum)
{
columnNum = row.LastCellNum;
}
IRow rowBack = sheetBack.CreateRow(i);
for (int j = 0; j < (mMaxColumn == -1 ? row.LastCellNum : mMaxColumn); ++j)
{
string val = Util.GetCellString(row.GetCell(j, MissingCellPolicy.CREATE_NULL_AS_BLANK));
var cell = rowBack.GetCell(j, MissingCellPolicy.CREATE_NULL_AS_BLANK);
Util.SetCellString(cell, val);
}
}
for (int i = 0; i < columnNum; ++i)
{
sheetBack.SetColumnWidth(i, sheet.GetColumnWidth(i));
}
for (int i = 0; i < mFields.Count; ++i)
{
var filed = mFields[i];
if (filed.Enum)
{
IDataValidationHelper helper = sheet.GetDataValidationHelper();
CellRangeAddressList cellRange = new CellRangeAddressList(START_ROW, 65535, i, i);
IDataValidationConstraint constraint = helper.CreateExplicitListConstraint(GetEnumList(filed.Type));
IDataValidation dataValidation = helper.CreateValidation(constraint, cellRange);
dataValidation.SuppressDropDownArrow = false;
dataValidation.CreatePromptBox(filed.Type, GetEnumComment(filed.Type));
sheetBack.AddValidationData(dataValidation);
}
}
workbook.RemoveSheetAt(0);
workbook.SetSheetOrder(sheetBack.SheetName, 0);
workbook.SetSheetName(0, sheetName);
using (FileStream stream = new FileStream(fileFullName, FileMode.Create)) {
workbook.Write(stream);
}
}
public IDataValidationHelper GetDataValidationHelper()
{
return(_sheet.GetDataValidationHelper());
}
