public static void AddConstraint(this ISheet sheet, IWorkbook workbook, string name, string mula, int columnIndex, bool isCustom = false)
{
IName namedRange = workbook.CreateName();
namedRange.NameName = name;
XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper((XSSFSheet)sheet);
XSSFDataValidationConstraint dvConstraint;
// XSSFDataValidation validation;
if (!isCustom)
{
namedRange.RefersToFormula = mula;//公式
dvConstraint = (XSSFDataValidationConstraint)dvHelper.CreateFormulaListConstraint(namedRange.NameName);
}
else
{
//自定义
dvConstraint = (XSSFDataValidationConstraint)dvHelper.CreateExplicitListConstraint(name.Split(','));
}
CellRangeAddressList addressList = new CellRangeAddressList(1, 10000, columnIndex, columnIndex);
XSSFDataValidation validation = (XSSFDataValidation)dvHelper.CreateValidation(dvConstraint, addressList);
validation.SuppressDropDownArrow = true;
validation.ShowErrorBox = true;
sheet.AddValidationData(validation);
}
private static void SetCodeColumn(XSSFSheet sheet, string columnLetter, int colindex, int elements, string sheetName, bool createCodeColumns)
{
if (createCodeColumns)
{
var row0 = sheet.GetRow(0);
var lastColIndex = row0.LastCellNum;
var headerCell = row0.CreateCell(lastColIndex, CellType.String);
headerCell.SetCellValue(sheetName + " Code");
row0.Cells.Add(headerCell);
headerCell.CellStyle = sheet.Workbook?.GetSheetAt(0)?.GetRow(0)?.GetCell(0)?.CellStyle;
for (var i = 1; i <= elements; i++)
{
var row = sheet.GetRow(i) ?? sheet.CreateRow(i);
var cell = row.CreateCell(lastColIndex, CellType.Formula);
var formula = $"INDEX('{sheetName}'!A1:D{elements},MATCH(${columnLetter}{i + 1},'{sheetName}'!D1:D{elements},0),1)";
cell.SetCellFormula(formula);
}
sheet.SetColumnHidden(row0.LastCellNum - 1, true);
}
var validationHelper = new XSSFDataValidationHelper(sheet);
var addressList = new CellRangeAddressList(0, elements - 1, colindex - 1, colindex - 1);
var constraint = validationHelper.CreateFormulaListConstraint($"'{sheetName}'!$D$2:$D$" + elements);
var dataValidation = validationHelper.CreateValidation(constraint, addressList);
sheet.AddValidationData(dataValidation);
}
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();
}
}
}
/// <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>
/// 设置Excel单元格样式（标题），数据格式
/// </summary>
/// <param name="dateType">数据类型</param>
/// <param name="porpetyIndex">单元格索引</param>
/// <param name="sheet">Sheet页</param>
/// <param name="dataSheet">数据Sheet页</param>
/// <param name="dataStyle">样式</param>
/// <param name="dataFormat">格式</param>
public void SetColumnFormat(ColumnDataType dateType, int porpetyIndex, ISheet sheet, ISheet dataSheet, ICellStyle dataStyle, IDataFormat dataFormat)
{
XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper((XSSFSheet)sheet);
CellRangeAddressList CellRangeList = new CellRangeAddressList(1, 1048576 - 1, porpetyIndex, porpetyIndex); //超过1048576最大行数，打开Excel会报错
XSSFDataValidationConstraint dvConstraint = null;
XSSFDataValidation dataValidation = null;
switch (dateType)
{
case ColumnDataType.Date:
case ColumnDataType.DateTime:
//因为DateTime类型，添加Validation报错，所以去掉
dataStyle.DataFormat = dataFormat.GetFormat("yyyy-MM-dd HH:mm:ss");
break;
case ColumnDataType.Number:
this.MinValueOrLength = string.IsNullOrEmpty(this.MinValueOrLength) ? long.MinValue.ToString() : this.MinValueOrLength;
this.MaxValuseOrLength = string.IsNullOrEmpty(this.MaxValuseOrLength) ? long.MaxValue.ToString() : this.MaxValuseOrLength;
dvConstraint = (XSSFDataValidationConstraint)dvHelper.CreateNumericConstraint(ValidationType.INTEGER, OperatorType.BETWEEN, this.MinValueOrLength, this.MaxValuseOrLength);
dataValidation = (XSSFDataValidation)dvHelper.CreateValidation(dvConstraint, CellRangeList);
dataValidation.CreateErrorBox(CoreProgram.Callerlocalizer?["Error"], CoreProgram.Callerlocalizer?["PleaseInputNumber"]);
dataStyle.DataFormat = dataFormat.GetFormat("0");
dataValidation.CreatePromptBox(CoreProgram.Callerlocalizer?["PleaseInputNumberFormat"], CoreProgram.Callerlocalizer?["DataRange", MinValueOrLength, MaxValuseOrLength]);
break;
case ColumnDataType.Float:
this.MinValueOrLength = string.IsNullOrEmpty(this.MinValueOrLength) ? decimal.MinValue.ToString() : this.MinValueOrLength;
this.MaxValuseOrLength = string.IsNullOrEmpty(this.MaxValuseOrLength) ? decimal.MaxValue.ToString() : this.MaxValuseOrLength;
dvConstraint = (XSSFDataValidationConstraint)dvHelper.CreateNumericConstraint(ValidationType.DECIMAL, OperatorType.BETWEEN, this.MinValueOrLength, this.MaxValuseOrLength);
dataValidation = (XSSFDataValidation)dvHelper.CreateValidation(dvConstraint, CellRangeList);
dataValidation.CreateErrorBox(CoreProgram.Callerlocalizer?["Error"], CoreProgram.Callerlocalizer?["PleaseInputDecimal"]);
dataStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00");
dataValidation.CreatePromptBox(CoreProgram.Callerlocalizer?["PleaseInputDecimalFormat"], CoreProgram.Callerlocalizer?["DataRange", MinValueOrLength, MaxValuseOrLength]);
break;
case ColumnDataType.Bool:
dvConstraint = (XSSFDataValidationConstraint)dvHelper.CreateFormulaListConstraint("Sheet1!$A$1:$B$1");
dataValidation = (XSSFDataValidation)dvHelper.CreateValidation(dvConstraint, CellRangeList);
dataValidation.CreateErrorBox(CoreProgram.Callerlocalizer?["Error"], CoreProgram.Callerlocalizer?["PleaseInputExistData"]);
dataValidation.CreatePromptBox(CoreProgram.Callerlocalizer?["ComboBox"], CoreProgram.Callerlocalizer?["PleaseInputExistData"]);
break;
case ColumnDataType.Text:
this.MinValueOrLength = string.IsNullOrEmpty(this.MinValueOrLength) ? "0" : this.MinValueOrLength;
this.MaxValuseOrLength = string.IsNullOrEmpty(this.MaxValuseOrLength) ? "2000" : this.MaxValuseOrLength;
dvConstraint = (XSSFDataValidationConstraint)dvHelper.CreateNumericConstraint(ValidationType.TEXT_LENGTH, OperatorType.BETWEEN, this.MinValueOrLength, this.MaxValuseOrLength);
dataValidation = (XSSFDataValidation)dvHelper.CreateValidation(dvConstraint, CellRangeList);
dataValidation.CreateErrorBox(CoreProgram.Callerlocalizer?["Error"], CoreProgram.Callerlocalizer?["WrongTextLength"]);
dataStyle.DataFormat = dataFormat.GetFormat("@");
dataValidation.CreatePromptBox(CoreProgram.Callerlocalizer?["PleaseInputText"], CoreProgram.Callerlocalizer?["DataRange", MinValueOrLength, MaxValuseOrLength]);
break;
case ColumnDataType.ComboBox:
case ColumnDataType.Enum:
int count = this.ListItems.Count() == 0 ? 1 : this.ListItems.Count();
string cloIndex = "";
if (porpetyIndex > 25)
{
cloIndex += Convert.ToChar((int)(Math.Floor(porpetyIndex / 26d)) - 1 + 65);
}
cloIndex += Convert.ToChar(65 + porpetyIndex % 26).ToString();
IName range = sheet.Workbook.CreateName();
range.RefersToFormula = "Sheet2!$" + cloIndex + "$1:$" + cloIndex + "$" + count;
range.NameName = "dicRange" + porpetyIndex;
dvConstraint = (XSSFDataValidationConstraint)dvHelper.CreateFormulaListConstraint("dicRange" + porpetyIndex);
dataValidation = (XSSFDataValidation)dvHelper.CreateValidation(dvConstraint, CellRangeList);
dataValidation.CreateErrorBox(CoreProgram.Callerlocalizer?["Error"], CoreProgram.Callerlocalizer?["PleaseInputExistData"]);
var listItemsTemp = this.ListItems.ToList();
for (int rowIndex = 0; rowIndex < this.ListItems.Count(); rowIndex++)
{
IRow dataSheetRow = dataSheet.GetRow(rowIndex);
if (dataSheetRow == null)
{
dataSheetRow = dataSheet.CreateRow(rowIndex);
}
dataSheetRow.CreateCell(porpetyIndex).SetCellValue(listItemsTemp[rowIndex].Text);
dataStyle.DataFormat = dataFormat.GetFormat("@");
dataSheetRow.Cells.Where(x => x.ColumnIndex == porpetyIndex).FirstOrDefault().CellStyle = dataStyle;
}
dataValidation.CreatePromptBox(CoreProgram.Callerlocalizer?["ComboBox"], CoreProgram.Callerlocalizer?["PleaseInputExistData"]);
break;
default:
dvConstraint = (XSSFDataValidationConstraint)dvHelper.CreateNumericConstraint(ValidationType.TEXT_LENGTH, OperatorType.BETWEEN, this.MinValueOrLength, this.MaxValuseOrLength);
dataValidation = (XSSFDataValidation)dvHelper.CreateValidation(dvConstraint, CellRangeList);
dataValidation.CreateErrorBox(CoreProgram.Callerlocalizer?["Error"], CoreProgram.Callerlocalizer?["WrongTextLength"]);
dataStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("@");
break;
}
if (dataValidation == null)
{
return;
}
if (!this.IsNullAble)
{
dataValidation.EmptyCellAllowed = false;
}
sheet.SetDefaultColumnStyle(porpetyIndex, dataStyle);
dataValidation.ShowErrorBox = true;
sheet.AddValidationData(dataValidation);
}
//public NpoiMemoryStream GetFileTemplate(string fileName,string[] heads,params CellValidation[] cellValidations)
//{
// var memory = new NpoiMemoryStream();
// var workbook = new XSSFWorkbook();
// var sheet = workbook.CreateSheet(fileName);
// var row = sheet.CreateRow(0);
// for (var i = 0; i < heads.Length; i++)
// {
// row.CreateCell(i).SetCellValue(heads[i]);
// }
// var sheet1 = workbook.GetSheetAt(0);
// cellValidations.ForEach(item =>
// {
// var regions = new CellRangeAddressList(item.FirstRow,item.LastRow,item.FirstCol, item.LastCol);//约束范围：c2到c65535
// var helper = new XSSFDataValidationHelper((XSSFSheet)sheet1);//获得一个数据验证Helper
// var validation =
// helper.CreateValidation(helper.CreateExplicitListConstraint(item.ListOfValues),
// regions);//创建约束
// validation.CreateErrorBox("错误", "请按右侧下拉箭头选择!");
// validation.ShowErrorBox = true;//显示上面提示=Ture
// sheet1.AddValidationData(validation);//添加进去
// });
// sheet1.ForceFormulaRecalculation = true;
// memory.AllowClose = false;
// workbook.Write(memory);
// memory.Flush();
// memory.Position = 0; // 指定内存流起始值
// return memory;
//}
public NpoiMemoryStream GetFileTemplate(string fileName, string[] heads, params CellValidation[] cellValidations)
{
var workbook = new XSSFWorkbook();
var sheet = workbook.CreateSheet(fileName);
var style = workbook.CreateCellStyle();
style.DataFormat = HSSFDataFormat.GetBuiltinFormat("@");
#region set heads
var newRow = sheet.CreateRow(0);
for (var i = 0; i < heads.Length; i++)
{
var cell = newRow.CreateCell(i);
cell.CellStyle = style;
cell.SetCellValue(heads[i]);
for (int j = 1; j <= 65535; j++)
{
var row = sheet.GetRow(j) ?? sheet.CreateRow(j);
cell = row.CreateCell(i);
cell.CellStyle = style;
}
}
#endregion set heads
#region set datasource
var dataNum = 0;
cellValidations?.ToList().ForEach(item =>
{
if (item.FirstList == null)
{
return;
}
var dataSourceSheetName = $"dataSource{dataNum}";
dataNum++;
var dataSourceSheet = workbook.CreateSheet(dataSourceSheetName);//创建sheet
for (var i = 0; i < item.FirstList.Count; i++)
{
var row = dataSourceSheet.GetRow(i) ?? dataSourceSheet.CreateRow(i); //添加行
row.CreateCell(0).SetCellValue(item.FirstList[i].Value); //单元格写值
if (i > 0)
{
if (item.SecondList != null && item.SecondList.Any())
{
dataSourceSheet.GetRow(0).CreateCell(i).SetCellValue(item.FirstList[i].Value);//一级列头
var datas = item.SecondList.Where(c => c.Key == item.FirstList[i].Key).ToList();
for (var j = 0; j < datas.Count; j++)
{
var secondRow = dataSourceSheet.GetRow(j + 1) ?? dataSourceSheet.CreateRow(j + 1);
secondRow.CreateCell(i).SetCellValue(datas[j].Value);//单元格写值
}
}
}
}
#region Range
for (var i = 0; i < item.FirstList.Count; i++)
{
if (i == 0)
{
var range = workbook.CreateName();
range.NameName = item.FirstList[i].Value;
range.RefersToFormula = string.Format("{0}!${3}${2}:${3}${1}",
dataSourceSheetName,
item.FirstList.Count,
2,
Index2ColName(i));
}
else
{
if (item.SecondList != null && item.SecondList.Any())
{
var cellDatas = item.SecondList.Where(c => c.Key == item.FirstList[i].Key).ToList();
if (cellDatas.Any())
{
var range = workbook.CreateName();
range.NameName = item.FirstList[i].Value;
range.RefersToFormula = string.Format("{0}!${3}${2}:${3}${1}",
dataSourceSheetName,
cellDatas.Count + 1,
2,
Index2ColName(i));
}
}
}
}
#endregion Range
});
#endregion set datasource
cellValidations?.ToList().ForEach(item =>
{
if (item.FirstList != null && item.FirstList.Any())
{
var regions = new CellRangeAddressList(item.FirstRow, item.LastRow, item.FirstCol, item.LastCol); //约束范围：c2到c65535
var helper = new XSSFDataValidationHelper((XSSFSheet)sheet); //获得一个数据验证Helper
var validation =
helper.CreateValidation(
helper.CreateFormulaListConstraint(item.FirstList[0].Value),
regions); //创建约束
validation.CreateErrorBox("错误", "请按右侧下拉箭头选择!");
validation.ShowErrorBox = true; //显示上面提示
sheet.AddValidationData(validation); //添加进去
}
else
{
if (item.ListOfValues != null && item.ListOfValues.Any())
{
var regions = new CellRangeAddressList(item.FirstRow, item.LastRow, item.FirstCol, item.LastCol); //约束范围：c2到c65535
var helper = new XSSFDataValidationHelper((XSSFSheet)sheet); //获得一个数据验证Helper
var validation =
helper.CreateValidation(helper.CreateExplicitListConstraint(item.ListOfValues),
regions); //创建约束
validation.CreateErrorBox("错误", "请按右侧下拉箭头选择!");
validation.ShowErrorBox = true; //显示上面提示
sheet.AddValidationData(validation); //添加进去
}
}
});
cellValidations?.ToList().ForEach(item =>
{
if (item.SecondList != null && item.SecondList.Any())
{
var regions = new CellRangeAddressList(item.FirstRow, item.LastRow, item.FirstCol + 1, item.LastCol + 1); //约束范围：c2到c65535
var helper = new XSSFDataValidationHelper((XSSFSheet)sheet); //获得一个数据验证Helper
var validation =
helper.CreateValidation(
helper.CreateFormulaListConstraint($"INDIRECT(${Index2ColName(item.FirstCol)}2)"),
regions); //创建约束
validation.CreateErrorBox("错误", "请按右侧下拉箭头选择!");
validation.ShowErrorBox = true; //显示上面提示=Ture
sheet.AddValidationData(validation); //添加进去
}
});
sheet.ForceFormulaRecalculation = true;
var memory = new NpoiMemoryStream();
memory.AllowClose = false;
workbook.Write(memory);
memory.Flush();
memory.Position = 0; // 指定内存流起始值
return(memory);
}
public static MemoryStream CreateExcelHeader(MDataTable header, Dictionary <string, string[]> validateData)
{
MemoryStream ms = new MemoryStream();
if (header != null && header.Rows.Count > 0)
{
MDataTable importHeader = header.FindAll("Import=1");
try
{
XSSFWorkbook export = new XSSFWorkbook();
ICellStyle style = GetStyle(export, HSSFColor.LightOrange.Index);
ISheet sheet = export.CreateSheet("Sheet1");//创建内存Excel
#region 创建引用
int rowStartIndex = 1;
CreateValidationSheet(export, validateData, rowStartIndex);
#endregion
importHeader.Rows.Sort("ORDER BY MergeIndexed DESC");//Hidden=0 AND (Export=1 OR Field LIKE 'mg_%')
MDataTable headTable = importHeader.Clone();
int ColTitleRowCount = 0;
Dictionary <string, int> formatdic = new Dictionary <string, int>();
for (int i = importHeader.Rows.Count - 1; i >= 0; i--)//MDataTable 不支持 NOT LIKE
{
if (importHeader.Rows[i]["Field"].Value.ToString().IndexOf("mg") > -1)
{
importHeader.Rows.RemoveAt(i);//非字段列移除
}
}
int colSum = importHeader.Rows.Count;//实际列数
importHeader.Rows.Sort("ORDER BY OrderNum ASC");
if (!ExportMulHeader(header, true))
{
IRow row = sheet.CreateRow(0);
ICell cell;
for (int i = 0; i < colSum; i++)
{
string title = importHeader.Rows[i]["Title"].Value.ToString();
cell = row.CreateCell(i);
cell.SetCellValue(title);//设置列头
sheet.SetColumnWidth(i, 3000);
cell.CellStyle = style;
}
}
else
{
CreateMulHeadExcel(export, headTable, out ColTitleRowCount, colSum);
ColTitleRowCount -= 1;
}
for (int i = 0; i < importHeader.Rows.Count; i++)
{
string formater = importHeader.Rows[i].Get <string>("Formatter");
if (!string.IsNullOrEmpty(formater) && formater.Length > 1 && !formatdic.ContainsKey(formater))
{
formatdic.Add(formater, i);//存储列索引
}
}
int maxRow = 50000;//限制最大行数(07之前版本的excel最大行数为65536，但NOPI似乎没有支持到最大行数,这里设置为50000行,到60000行数据有效性失效)
XSSFSheet xssfSheet = (XSSFSheet)sheet;
XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper(xssfSheet);
for (int i = 0; i < importHeader.Rows.Count; i++)
{
MDataRow dtRow = importHeader.Rows[i];
string formatter = dtRow.Get <string>("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;
//int maxRow = 65535;
if (validateData.ContainsKey(formatter))
{
regions = new CellRangeAddressList(ColTitleRowCount + 1, maxRow, i, i);
string key = formatter.Split('=')[0].Replace("#", "");// "V" + (char)formatter.Length;// formatter.Replace("#", "V");
/*03版本api
* constraint = DVConstraint.CreateFormulaListConstraint(key);//);//validateData[formatter]
* dataValidate = new HSSFDataValidation(regions, constraint);
*/
constraint = dvHelper.CreateFormulaListConstraint(key);
dataValidate = dvHelper.CreateValidation(constraint, regions);
sheet.AddValidationData(dataValidate);
//regions = new CellRangeAddressList(ColTitleRowCount, maxRow, i, i);
//string key = formatter.Split('=')[0].Replace("#", "");// "V" + (char)formatter.Length;// formatter.Replace("#", "V");
//constraint = DVConstraint.CreateFormulaListConstraint(key);//);//validateData[formatter]
//dataValidate = new HSSFDataValidation(regions, constraint);
//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;
//int parentindex = formatdic[hereformatter];
string t = IntToMoreChar(parentindex);
for (int im = ColTitleRowCount; 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);
}
//for (int im = ColTitleRowCount; im < maxRow; im++)//1000应为maxRow
//{
// string func = "INDIRECT(" + t + (im + 1) + ")";//excel2013不能级联，03可以，其他没测过
// regions = new CellRangeAddressList(ColTitleRowCount, im, selfindex, selfindex);
// constraint = DVConstraint.CreateFormulaListConstraint(func);
// dataValidate = new HSSFDataValidation(regions, constraint);
// sheet.AddValidationData(dataValidate);
//}
Parentformatter = item.Key;
break;
}
}
point += 1;
}
if (point.Equals(formatdic.Count))
{
Parentformatter = string.Empty;
}
}
}
}
}
export.Write(ms);
ms.Flush();
ms.Close();
}
catch (Exception err)
{
Log.WriteLogToTxt(err);
}
}
return(ms);
}
/// <summary>
/// 建立级联关系
/// </summary>
/// <param name="sheet">表</param>
/// <param name="source">数据源(EXCEL表)</param>
/// <param name="minRow">起始行</param>
/// <param name="maxRow">终止行</param>
/// <param name="minCell">起始列</param>
/// <param name="maxCell">终止列</param>
public void ExcelLevelRelation(ISheet sheet, string source, int minRow, int maxRow, int minCell, int maxCell)
{
// 第一层绑定下拉的时候，可以一次性选择多个单元格进行绑定
// 要是从第二层开始，就只能一对一的绑定，如果目标单元格要与哪一个一级单元格进行关联
XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper(sheet as XSSFSheet);
XSSFDataValidationConstraint dvConstraint = (XSSFDataValidationConstraint)dvHelper.CreateFormulaListConstraint(source);
CellRangeAddressList cellRegions = new CellRangeAddressList(minRow, maxRow, minCell, maxCell);
XSSFDataValidation validation = (XSSFDataValidation)dvHelper.CreateValidation(dvConstraint, cellRegions);
validation.SuppressDropDownArrow = true;
validation.CreateErrorBox("输入不合法", "请选择下拉列表中的值。");
validation.ShowErrorBox = 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 static byte[] GenerateTemplate(List <Business.Entities.company> listCompany, List <Business.Entities.contractor> listContractor, List <Business.Entities.project> listProject)
{
//culture
Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US"); //supaya file tidak corrupt
int parseRecordNumber = 100; // number of rows that has style or validation
int startRowIndex = 3;
XSSFCellStyle styleCurrency;
XSSFCellStyle styleDate;
XSSFCellStyle styleNumeric;
XSSFCellStyle styleDecimal;
//kamus
XSSFWorkbook workbook = new XSSFWorkbook();
XSSFSheet sheet; XSSFRow row; XSSFCell cell;
XSSFCellStyle style; XSSFFont font;
CellRangeAddressList addressList; XSSFDataValidationHelper dvHelper; XSSFDataValidationConstraint dvConstraint; XSSFDataValidation validation;
List <string> listCompanyString = new List <string>();
foreach (var data in listCompany)
{
listCompanyString.Add(data.name);
}
List <string> listContractorString = new List <string>();
foreach (var data in listContractor)
{
listContractorString.Add(data.name);
}
List <string> listProjectString = new List <string>();
foreach (var data in listProject)
{
listProjectString.Add(data.name);
}
styleCurrency = (XSSFCellStyle)workbook.CreateCellStyle();
styleCurrency.DataFormat = workbook.CreateDataFormat().GetFormat("$#,##0.00_);($#,##0.00)");
styleNumeric = (XSSFCellStyle)workbook.CreateCellStyle();
styleNumeric.DataFormat = workbook.CreateDataFormat().GetFormat("#,##0");
styleDate = (XSSFCellStyle)workbook.CreateCellStyle();
styleDate.DataFormat = workbook.CreateDataFormat().GetFormat("mm/dd/yyyy");
styleDecimal = (XSSFCellStyle)workbook.CreateCellStyle();
styleDecimal.DataFormat = workbook.CreateDataFormat().GetFormat("0.00");
List <string> columnList = new List <string>();
columnList.Add("Name");
int ContractorStringLocation = 1;
columnList.Add("Contractor");
columnList.Add("Photo");
columnList.Add("Description");
columnList.Add("Start Date");
columnList.Add("Finish Date");
columnList.Add("Highlight");
columnList.Add("Project Stage");
columnList.Add("Status");
columnList.Add("Budget");
columnList.Add("Currency");
columnList.Add("Num");
int PmcStringLocation = 12;
columnList.Add("Pmc");
columnList.Add("Summary");
int CompanyStringLocation = 14;
columnList.Add("Company");
columnList.Add("Status Non Technical");
columnList.Add("Is Completed");
columnList.Add("Completed Date");
int ProjectStringLocation = 18;
columnList.Add("Project");
columnList.Add("Submit For Approval Time");
columnList.Add("Approval Status");
columnList.Add("Approval Time");
columnList.Add("Deleted");
columnList.Add("Approval Message");
columnList.Add("Status Technical");
columnList.Add("Scurve Data");
sheet = (XSSFSheet)workbook.CreateSheet("Data");
int col = 0;
int rowNumber = 0;
//create row (header)
row = (XSSFRow)sheet.CreateRow((short)rowNumber);
dvHelper = new XSSFDataValidationHelper(sheet);
//header data
style = (XSSFCellStyle)workbook.CreateCellStyle();
cell = (XSSFCell)row.CreateCell(col);
cell.SetCellValue("M Project");
font = (XSSFFont)workbook.CreateFont();
font.FontHeight = 24;
style.SetFont(font);
cell.CellStyle = style;
rowNumber++;
row = (XSSFRow)sheet.CreateRow((short)rowNumber);
style = (XSSFCellStyle)workbook.CreateCellStyle();
font = (XSSFFont)workbook.CreateFont();
font.Boldweight = (short)FontBoldWeight.Bold;
style.SetFont(font);
rowNumber++;
row = (XSSFRow)sheet.CreateRow((short)rowNumber);
//header data
foreach (string data in columnList)
{
cell = (XSSFCell)row.CreateCell(col);
cell.SetCellValue(data);
cell.CellStyle = style;
//cell.CellStyle.IsLocked = true;
//column width
sheet.SetColumnWidth(col, (30 * 256));
++col;
}
//sheet.CreateFreezePane(0, 4);
//dropdownlist Company
if (listCompanyString.Count > 0)
{
XSSFSheet hidden = (XSSFSheet)workbook.CreateSheet("MasterCompany");
int i = 0;
foreach (string a in listCompanyString)
{
row = (XSSFRow)hidden.CreateRow(i);
cell = (XSSFCell)row.CreateCell(0);
cell.SetCellValue(a);
i++;
}
validation = null;
dvConstraint = null;
dvHelper = null;
dvHelper = new XSSFDataValidationHelper(sheet);
addressList = new CellRangeAddressList(startRowIndex, parseRecordNumber, CompanyStringLocation, CompanyStringLocation);
dvConstraint = (XSSFDataValidationConstraint)dvHelper.CreateFormulaListConstraint("MasterCompany!$A$1:$A$" + listCompanyString.Count);
validation = (XSSFDataValidation)dvHelper.CreateValidation(dvConstraint, addressList);
validation.SuppressDropDownArrow = true;
validation.ShowErrorBox = true;
workbook.SetSheetHidden(1, true);
sheet.AddValidationData(validation);
}
//dropdownlist Contractor
if (listContractorString.Count > 0)
{
XSSFSheet hidden = (XSSFSheet)workbook.CreateSheet("MasterContractor");
int i = 0;
foreach (string a in listContractorString)
{
row = (XSSFRow)hidden.CreateRow(i);
cell = (XSSFCell)row.CreateCell(0);
cell.SetCellValue(a);
i++;
}
validation = null;
dvConstraint = null;
dvHelper = null;
dvHelper = new XSSFDataValidationHelper(sheet);
addressList = new CellRangeAddressList(startRowIndex, parseRecordNumber, ContractorStringLocation, ContractorStringLocation);
dvConstraint = (XSSFDataValidationConstraint)dvHelper.CreateFormulaListConstraint("MasterContractor!$A$1:$A$" + listContractorString.Count);
validation = (XSSFDataValidation)dvHelper.CreateValidation(dvConstraint, addressList);
validation.SuppressDropDownArrow = true;
validation.ShowErrorBox = true;
workbook.SetSheetHidden(2, true);
sheet.AddValidationData(validation);
}
//dropdownlist Project
if (listProjectString.Count > 0)
{
XSSFSheet hidden = (XSSFSheet)workbook.CreateSheet("MasterProject");
int i = 0;
foreach (string a in listProjectString)
{
row = (XSSFRow)hidden.CreateRow(i);
cell = (XSSFCell)row.CreateCell(0);
cell.SetCellValue(a);
i++;
}
validation = null;
dvConstraint = null;
dvHelper = null;
dvHelper = new XSSFDataValidationHelper(sheet);
addressList = new CellRangeAddressList(startRowIndex, parseRecordNumber, ProjectStringLocation, ProjectStringLocation);
dvConstraint = (XSSFDataValidationConstraint)dvHelper.CreateFormulaListConstraint("MasterProject!$A$1:$A$" + listProjectString.Count);
validation = (XSSFDataValidation)dvHelper.CreateValidation(dvConstraint, addressList);
validation.SuppressDropDownArrow = true;
validation.ShowErrorBox = true;
workbook.SetSheetHidden(3, true);
sheet.AddValidationData(validation);
}
/*Cell formatting*/
for (int i = startRowIndex; i <= parseRecordNumber; i++)
{
rowNumber++;
row = (XSSFRow)sheet.CreateRow((short)rowNumber);
//start_date
col = 4;
cell = (XSSFCell)row.CreateCell((short)col);
cell.CellStyle = styleDate;
if (i == startRowIndex)
{
addressList = new CellRangeAddressList(startRowIndex, parseRecordNumber, 4, 4);
dvConstraint = (XSSFDataValidationConstraint)dvHelper.CreateDateConstraint(OperatorType.GREATER_THAN, "01/01/1900", "", "mm/dd/yyyy");
validation = (XSSFDataValidation)dvHelper.CreateValidation(dvConstraint, addressList);
validation.ShowErrorBox = true;
validation.CreateErrorBox("Input Error", "Value must be a date, example 12/30/2000");
sheet.AddValidationData(validation);
}
//finish_date
col = 5;
cell = (XSSFCell)row.CreateCell((short)col);
cell.CellStyle = styleDate;
if (i == startRowIndex)
{
addressList = new CellRangeAddressList(startRowIndex, parseRecordNumber, 5, 5);
dvConstraint = (XSSFDataValidationConstraint)dvHelper.CreateDateConstraint(OperatorType.GREATER_THAN, "01/01/1900", "", "mm/dd/yyyy");
validation = (XSSFDataValidation)dvHelper.CreateValidation(dvConstraint, addressList);
validation.ShowErrorBox = true;
validation.CreateErrorBox("Input Error", "Value must be a date, example 12/30/2000");
sheet.AddValidationData(validation);
}
//budget
col = 9;
cell = (XSSFCell)row.CreateCell((short)col);
cell.CellStyle = styleDecimal;
if (i == startRowIndex)
{
}
//num
col = 11;
cell = (XSSFCell)row.CreateCell((short)col);
cell.CellStyle = styleNumeric;
if (i == startRowIndex)
{
addressList = new CellRangeAddressList(startRowIndex, parseRecordNumber, 11, 11);
dvHelper = new XSSFDataValidationHelper(sheet);
dvConstraint = (XSSFDataValidationConstraint)dvHelper.CreateNumericConstraint(ValidationType.INTEGER, OperatorType.BETWEEN, "0", "1000000000000000000");
validation = (XSSFDataValidation)dvHelper.CreateValidation(dvConstraint, addressList);
validation.ShowErrorBox = true;
validation.CreateErrorBox("Input Error", "Value must be a number, maximum 1.000.000.000.000.000.000");
sheet.AddValidationData(validation);
}
//completed_date
col = 17;
cell = (XSSFCell)row.CreateCell((short)col);
cell.CellStyle = styleDate;
if (i == startRowIndex)
{
addressList = new CellRangeAddressList(startRowIndex, parseRecordNumber, 17, 17);
dvConstraint = (XSSFDataValidationConstraint)dvHelper.CreateDateConstraint(OperatorType.GREATER_THAN, "01/01/1900", "", "mm/dd/yyyy");
validation = (XSSFDataValidation)dvHelper.CreateValidation(dvConstraint, addressList);
validation.ShowErrorBox = true;
validation.CreateErrorBox("Input Error", "Value must be a date, example 12/30/2000");
sheet.AddValidationData(validation);
}
//submit_for_approval_time
col = 19;
cell = (XSSFCell)row.CreateCell((short)col);
cell.CellStyle = styleDate;
if (i == startRowIndex)
{
addressList = new CellRangeAddressList(startRowIndex, parseRecordNumber, 19, 19);
dvConstraint = (XSSFDataValidationConstraint)dvHelper.CreateDateConstraint(OperatorType.GREATER_THAN, "01/01/1900", "", "mm/dd/yyyy");
validation = (XSSFDataValidation)dvHelper.CreateValidation(dvConstraint, addressList);
validation.ShowErrorBox = true;
validation.CreateErrorBox("Input Error", "Value must be a date, example 12/30/2000");
sheet.AddValidationData(validation);
}
//approval_time
col = 21;
cell = (XSSFCell)row.CreateCell((short)col);
cell.CellStyle = styleDate;
if (i == startRowIndex)
{
addressList = new CellRangeAddressList(startRowIndex, parseRecordNumber, 21, 21);
dvConstraint = (XSSFDataValidationConstraint)dvHelper.CreateDateConstraint(OperatorType.GREATER_THAN, "01/01/1900", "", "mm/dd/yyyy");
validation = (XSSFDataValidation)dvHelper.CreateValidation(dvConstraint, addressList);
validation.ShowErrorBox = true;
validation.CreateErrorBox("Input Error", "Value must be a date, example 12/30/2000");
sheet.AddValidationData(validation);
}
}
//write to byte[]
MemoryStream ms = new MemoryStream();
workbook.Write(ms);
return(ms.ToArray());
}
/// <summary>
/// 下载excel模板
/// </summary>
/// <param name="fileName">文件名称</param>
/// <param name="heads">表头</param>
/// <param name="contentInfo">导出excel数据源</param>
/// <param name="cellContents"></param>
/// <param name="templteRowCount">模板或者导出excel样式设置行数</param>
/// <param name="cellValidations">验证数据源</param>
/// <returns></returns>
public static NpoiMemoryStream GetFileTemplate(string fileName
, List <HeadInfo> heads
, List <List <ContentInfo> > contentInfo = null
, List <CellContent> cellContents = null
, int templteRowCount = 65535
, params CellValidation[] cellValidations)
{
#region 内容特殊字符过滤 待处理
for (var i = 0; i < cellValidations?.Length; i++)
{
if (cellValidations[i].FirstList == null || !cellValidations[i].FirstList.Any())
{
continue;
}
cellValidations[i].FirstList[0].Key = ReplaceSpecialCharacter(cellValidations[i].FirstList[0].Key);
cellValidations[i].FirstList[0].Value = ReplaceSpecialCharacter(cellValidations[i].FirstList[0].Value);
//foreach (var item in cellValidations[i].FirstList)
//{
// item.Key =ReplaceSpecialCharacter(item.Key);
// item.Value =ReplaceSpecialCharacter(item.Value);
//}
}
#endregion
var workbook = new XSSFWorkbook();
var sheet = workbook.CreateSheet(fileName);
#region set heads
var rowCount = contentInfo?.Count ?? templteRowCount;
var newRow = sheet.CreateRow(0);
for (var i = 0; i < heads.Count; i++)
{
sheet.SetColumnWidth(i, heads[i].ColumnWidth);
var style = GetCellStyle(workbook, heads[i].CellStyle);
var cell = newRow.CreateCell(i);
cell.CellStyle = style;
cell.SetCellValue(heads[i].Name);
for (var j = 1; j <= rowCount; j++)
{
var row = sheet.GetRow(j) ?? sheet.CreateRow(j);
cell = row.CreateCell(i);
cell.CellStyle = style;
}
}
#endregion
#region 初始化导出excel数据源
for (var i = 0; i < contentInfo?.Count; i++)
{
var contentRow = sheet.CreateRow(i + 1);
for (var j = 0; j < contentInfo[i]?.Count; j++)
{
var contentCell = contentRow.CreateCell(j);
contentCell.SetCellValue(contentInfo[i][j].Name);
}
}
#endregion
#region set datasource
cellValidations?.ToList().ForEach(item =>
{
if (item.FirstList == null)
{
return;
}
var dataSourceSheetName = $"{item.FirstList?[0]?.Value.Replace(" ", "")}";
var dataSourceSheet = workbook.CreateSheet(dataSourceSheetName);//创建sheet
/*
* FirstList
* 1 2 3
* 2
* 3
*/
#region init datasource
for (var i = 0; i < item.FirstList.Count; i++)
{
var row = dataSourceSheet.GetRow(i) ?? dataSourceSheet.CreateRow(i);//添加行
row.CreateCell(0).SetCellValue(i == 0
? item.FirstList[i].Value.Replace(" ", "")
: item.FirstList[i].Value);//单元格写值
if (i <= 0)
{
continue;
}
if (item.SecondList != null && item.SecondList.Any())
{
dataSourceSheet.GetRow(0).CreateCell(i).SetCellValue(item.FirstList[i].Value);//一级列头
var cellDatas = item.SecondList.Where(c => c.Key == item.FirstList[i].Key).ToList();
for (var j = 0; j < cellDatas.Count; j++)
{
var secondRow = dataSourceSheet.GetRow(j + 1) ?? dataSourceSheet.CreateRow(j + 1);
secondRow.CreateCell(i).SetCellValue(cellDatas[j].Value);//单元格写值
}
}
}
#endregion
#region Range
for (var i = 0; i < item.FirstList.Count; i++)
{
if (i == 0)
{
var range = workbook.CreateName();
range.NameName = item.FirstList[i].Value.Replace(" ", "");
range.RefersToFormula = string.Format("{0}!${3}${2}:${3}${1}",
dataSourceSheetName,
item.FirstList.Count,
2,
Index2ColName(i));
}
else
{
if (item.SecondList != null && item.SecondList.Any())
{
var cellDatas = item.SecondList.Where(c => c.Key == item.FirstList[i].Key).ToList();
if (cellDatas.Any())
{
var range = workbook.CreateName();
range.NameName = item.FirstList[i].Value.Replace(" ", "");
range.RefersToFormula = string.Format("{0}!${3}${2}:${3}${1}",
dataSourceSheetName,
cellDatas.Count + 1,
2,
Index2ColName(i));
}
}
}
}
#endregion
});
#endregion
#region set cellContents
cellContents?.ForEach(item =>
{
var writeSheet = workbook.GetSheetAt(item.SheetIndex) ??
workbook.CreateSheet(item.SheetIndex.ToString());
var i = 0;
item.ListOfValues?.ForEach(info =>
{
var writeRow = writeSheet.GetRow(item.FirstRow + i) ?? writeSheet.CreateRow(item.FirstRow + i);
var writeCell = writeRow.GetCell(item.FirstCol) ?? writeRow.CreateCell(item.FirstCol);
writeCell.SetCellValue(info);
i++;
});
});
#endregion
#region 设置验证
cellValidations?.ToList().ForEach(item =>
{
if (item.FirstList != null && item.FirstList.Any())
{
if (item.FirstCellLocation != null)
{
var regions = new CellRangeAddressList(item.FirstCellLocation.FirstRow, item.FirstCellLocation.LastRow, item.FirstCellLocation.FirstCol, item.FirstCellLocation.LastCol); //约束范围：c2到c65535
var helper = new XSSFDataValidationHelper((XSSFSheet)sheet); //获得一个数据验证Helper
var validation =
helper.CreateValidation(
helper.CreateFormulaListConstraint(item.FirstList[0].Value),
regions); //创建约束
validation.CreateErrorBox("错误", "请按右侧下拉箭头选择!");
validation.ShowErrorBox = true; //显示上面提示
sheet.AddValidationData(validation); //添加进去
}
}
else
{
if (item.ListOfValues != null && item.ListOfValues.Any())
{
if (item.FirstCellLocation != null)
{
var regions = new CellRangeAddressList(item.FirstCellLocation.FirstRow, item.FirstCellLocation.LastRow, item.FirstCellLocation.FirstCol, item.FirstCellLocation.LastCol); //约束范围：c2到c65535
var helper = new XSSFDataValidationHelper((XSSFSheet)sheet); //获得一个数据验证Helper
var validation =
helper.CreateValidation(helper.CreateExplicitListConstraint(item.ListOfValues),
regions); //创建约束
validation.CreateErrorBox("错误", "请按右侧下拉箭头选择!");
validation.ShowErrorBox = true; //显示上面提示
sheet.AddValidationData(validation); //添加进去
}
}
}
});
#endregion
#region 设置验证
cellValidations?.ToList().ForEach(item =>
{
if (item.SecondList != null && item.SecondList.Any())
{
if (item.SecondCellLocation != null)
{
var regions = new CellRangeAddressList(item.SecondCellLocation.FirstRow, item.SecondCellLocation.LastRow, item.SecondCellLocation.FirstCol, item.SecondCellLocation.LastCol); //约束范围：c2到c65535
var helper = new XSSFDataValidationHelper((XSSFSheet)sheet); //获得一个数据验证Helper
var validation =
helper.CreateValidation(
helper.CreateFormulaListConstraint($"INDIRECT(${Index2ColName(item.FirstCellLocation.FirstCol)}2)"),
regions); //创建约束
validation.CreateErrorBox("错误", "请按右侧下拉箭头选择!");
validation.ShowErrorBox = true; //显示上面提示=Ture
sheet.AddValidationData(validation); //添加进去
}
else if (item.FirstCellLocation != null)
{
var regions = new CellRangeAddressList(item.FirstCellLocation.FirstRow, item.FirstCellLocation.LastRow, item.FirstCellLocation.FirstCol + 1, item.FirstCellLocation.LastCol + 1); //约束范围：c2到c65535
var helper = new XSSFDataValidationHelper((XSSFSheet)sheet); //获得一个数据验证Helper
var validation =
helper.CreateValidation(
helper.CreateFormulaListConstraint($"INDIRECT(${Index2ColName(item.FirstCellLocation.FirstCol)}2)"),
regions); //创建约束
validation.CreateErrorBox("错误", "请按右侧下拉箭头选择!");
validation.ShowErrorBox = true; //显示上面提示=Ture
sheet.AddValidationData(validation); //添加进去
}
}
});
#endregion
sheet.ForceFormulaRecalculation = true;
var memory = new NpoiMemoryStream {
AllowClose = false
};
workbook.Write(memory);
memory.Flush();
memory.Position = 0; // 指定内存流起始值
return(memory);
}
