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();
}
}
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 void TestCreateMultipleRegionsValidation()
{
XSSFWorkbook wb = new XSSFWorkbook();
try
{
XSSFSheet sheet = wb.CreateSheet() as XSSFSheet;
IDataValidationHelper dataValidationHelper = sheet.GetDataValidationHelper();
IDataValidationConstraint constraint = dataValidationHelper.CreateExplicitListConstraint(new string[] { "A" });
CellRangeAddressList cellRangeAddressList = new CellRangeAddressList();
cellRangeAddressList.AddCellRangeAddress(0, 0, 0, 0);
cellRangeAddressList.AddCellRangeAddress(0, 1, 0, 1);
cellRangeAddressList.AddCellRangeAddress(0, 2, 0, 2);
XSSFDataValidation dataValidation = dataValidationHelper.CreateValidation(constraint, cellRangeAddressList) as XSSFDataValidation;
sheet.AddValidationData(dataValidation);
Assert.AreEqual(new CellRangeAddress(0, 0, 0, 0), sheet.GetDataValidations()[0].Regions.CellRangeAddresses[0]);
Assert.AreEqual(new CellRangeAddress(0, 0, 1, 1), sheet.GetDataValidations()[0].Regions.CellRangeAddresses[1]);
Assert.AreEqual(new CellRangeAddress(0, 0, 2, 2), sheet.GetDataValidations()[0].Regions.CellRangeAddresses[2]);
Assert.AreEqual("A1 B1 C1", dataValidation.GetCTDataValidation().sqref);
}
finally
{
wb.Close();
}
}
/// <summary>
/// EXCEL2007下拉值
/// </summary>
/// <param name="sheet"></param>
/// <param name="data"></param>
/// <param name="StarCol"></param>
/// <param name="EndCol"></param>
private static void CreateDropDwonListForXLSX(XSSFSheet sheet, List <string> data, int StarCol, int EndCol)
{
XSSFDataValidationHelper Validation = new XSSFDataValidationHelper(sheet);
XSSFDataValidationConstraint Constraint = (XSSFDataValidationConstraint)Validation.CreateExplicitListConstraint(data.ToArray());
CellRangeAddressList AddressList = new CellRangeAddressList(1, 65535, StarCol, EndCol);
var XSSF = Validation.CreateValidation(Constraint, AddressList);
sheet.AddValidationData(XSSF);
}
public void CreateSheet <T>(IEnumerable <T> data, XSSFSheet sheet)
{
//CreateHeader
var header = sheet.CreateRow(0);
header.CreateCell(0);
PropertyDescriptorCollection props = TypeDescriptor.GetProperties(typeof(T));
int i = 0, j = 0;
foreach (PropertyDescriptor prop in props)
{
var cell = header.CreateCell(i);
cell.SetCellValue(prop.Name);
i++;
}
//CreateRows
i = 1;
foreach (T item in data)
{
var row = sheet.CreateRow(i);
j = 0;
foreach (PropertyDescriptor prop in props)
{
if (prop.Converter.ConvertToString(prop.GetValue(item)).Contains("$"))
{
XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper(sheet);
XSSFDataValidationConstraint constraint = (XSSFDataValidationConstraint)dvHelper.CreateExplicitListConstraint(DropDownList);
CellRangeAddressList addressList = new CellRangeAddressList(i, i, j, j);
XSSFDataValidation validation = (XSSFDataValidation)dvHelper.CreateValidation(constraint, addressList);
sheet.AddValidationData(validation);
// row.CreateCell(j).SetCellValue(prop.Converter.ConvertToString(prop.GetValue(item)));
}
else
{
row.CreateCell(j).SetCellValue(prop.Converter.ConvertToString(prop.GetValue(item)));
}
j++;
}
i++;
}
}
public void TestDefaultAllowBlank()
{
XSSFWorkbook wb = new XSSFWorkbook();
try
{
XSSFSheet sheet = wb.CreateSheet() as XSSFSheet;
XSSFDataValidation validation = CreateValidation(sheet);
sheet.AddValidationData(validation);
List <IDataValidation> dataValidations = sheet.GetDataValidations();
Assert.AreEqual(true, (dataValidations[0] as XSSFDataValidation).GetCTDataValidation().allowBlank);
}
finally
{
wb.Close();
}
}
public void AddValidationData(IDataValidation dataValidation)
{
_sh.AddValidationData(dataValidation);
}
/// <summary>
/// 导出期初模板
/// </summary>
/// <param name="dtSource"></param>
/// <param name="exportTemplateFilePath"></param>
/// <param name="fillRow"></param>
/// <param name="replaceCells"></param>
/// <returns></returns>
private static MemoryStream ExportOpeningTemplate(DataTable dtSource, string[] dropDowndtSource, string exportTemplateFilePath, int fillRow, int dropDownFillStartCell, int dropDownFillEndCell)
{
try
{
//打开Excle模板文件
IWorkbook workbook = null;
using (FileStream fileOne = new FileStream(exportTemplateFilePath, FileMode.Open, FileAccess.Read))
{
workbook = new XSSFWorkbook(fileOne); //获取第一个工作表
}
XSSFSheet sheet = (XSSFSheet)workbook.GetSheetAt(0); //获取第一个sheet
XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper(sheet);
XSSFDataValidationConstraint dvConstraint = (XSSFDataValidationConstraint)dvHelper
.CreateExplicitListConstraint(dropDowndtSource);
CellRangeAddressList addressList = new CellRangeAddressList(1, dtSource.Rows.Count, dropDownFillStartCell, dropDownFillEndCell);
XSSFDataValidation validation = (XSSFDataValidation)dvHelper.CreateValidation(dvConstraint, addressList);
sheet.AddValidationData(validation);
//格式日期
XSSFCellStyle dateStyle = workbook.CreateCellStyle() as XSSFCellStyle;
XSSFDataFormat format = workbook.CreateDataFormat() as XSSFDataFormat;
dateStyle.DataFormat = format.GetFormat("yyyy-MM-dd HH:mm:ss");
//格式数字
XSSFCellStyle decimelStyle = workbook.CreateCellStyle() as XSSFCellStyle;
XSSFDataFormat decimelformat = workbook.CreateDataFormat() as XSSFDataFormat;
decimelStyle.DataFormat = decimelformat.GetFormat("0.00####");
//单元格样式
ICellStyle style = workbook.CreateCellStyle();
//style.BorderBottom = BorderStyle.Thin;
//style.BorderLeft = BorderStyle.Thin;
//style.BorderRight = BorderStyle.Thin;
//style.BorderTop = BorderStyle.Thin;
int rowIndex = fillRow;
foreach (DataRow row in dtSource.Rows)
{
#region 填充内容
//sheet.ShiftRows(rowIndex, sheet.LastRowNum, 1, true, false);
XSSFRow dataRow = sheet.CreateRow(rowIndex) as XSSFRow;
foreach (DataColumn column in dtSource.Columns)
{
XSSFCell newCell = dataRow.CreateCell(column.Ordinal) as XSSFCell;
string drValue = row[column].ToString();
switch (column.DataType.ToString())
{
case "System.String": //字符串类型
newCell.SetCellValue(drValue);
break;
case "System.DateTime": //日期类型
if (drValue.Length > 0)
{
DateTime dateV;
DateTime.TryParse(drValue, out dateV);
newCell.SetCellValue(dateV);
newCell.CellStyle = dateStyle; //格式化显示
}
break;
case "System.Boolean": //布尔型
bool boolV = false;
bool.TryParse(drValue, out boolV);
newCell.SetCellValue(boolV);
break;
case "System.Int16": //整型
case "System.Int32":
case "System.Int64":
case "System.Byte":
int intV = 0;
int.TryParse(drValue, out intV);
newCell.SetCellValue(intV);
break;
case "System.Decimal": //浮点型
case "System.Double":
Double doubV = 0;
Double.TryParse(drValue, out doubV);
newCell.SetCellValue(doubV);
newCell.CellStyle = decimelStyle;
break;
case "System.DBNull": //空值处理
newCell.SetCellValue("");
break;
default:
newCell.SetCellValue("");
break;
}
newCell.CellStyle = style;
}
#endregion 填充内容
rowIndex++;
}
NpoiMemoryStream ms = new NpoiMemoryStream();
ms.AllowClose = false;
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
ms.Seek(0, SeekOrigin.Begin);
ms.AllowClose = true;
return(ms);
}
catch (Exception ex)
{
throw ex;
}
}