public void TestGetDataValidationsDecimal(){
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.CreateSheet() as HSSFSheet;
List<IDataValidation> list = sheet.GetDataValidations();
Assert.AreEqual(0, list.Count);
IDataValidationHelper dataValidationHelper = sheet.GetDataValidationHelper();
IDataValidationConstraint constraint = dataValidationHelper.CreateDecimalConstraint(OperatorType.BETWEEN, "=A2",
"200");
CellRangeAddressList AddressList = new CellRangeAddressList(0, 0, 0, 0);
IDataValidation validation = dataValidationHelper.CreateValidation(constraint, AddressList);
sheet.AddValidationData(validation);
list = sheet.GetDataValidations(); // <-- works
Assert.AreEqual(1, list.Count);
HSSFDataValidation dv = list[(0)] as HSSFDataValidation;
DVConstraint c = dv.Constraint;
Assert.AreEqual(ValidationType.DECIMAL, c.GetValidationType());
Assert.AreEqual(OperatorType.BETWEEN, c.Operator);
Assert.AreEqual("A2", c.Formula1);
Assert.AreEqual(null, c.Formula2);
Assert.AreEqual(double.NaN, c.Value1);
Assert.AreEqual(200, c.Value2);
}
public HSSFWorkbook CreateWorkbookTemplate()
{
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet(fMetaData.Table.TableDesc);
HSSFRow dataRow = (HSSFRow)sheet.CreateRow(0);
int index = 0;
foreach (Tk5FieldInfoEx fieldInfo in fMetaData.Table.TableList)
{
int colWith = ExcelUtil.GetColWidth(fieldInfo);
sheet.SetColumnWidth(index, colWith << 8);
ICellStyle styleContent = BorderAndFontSetting(workbook, fieldInfo, Model.Content);
HSSFDataValidation dataValidate = ExcelUtil.CreateDataValidation(index, fieldInfo, styleContent, workbook);
sheet.SetDefaultColumnStyle(index, styleContent);
if (dataValidate != null)
{
((HSSFSheet)sheet).AddValidationData(dataValidate);
}
ICell cell = dataRow.CreateCell(index);
ICellStyle styleHeader = BorderAndFontSetting(workbook, fieldInfo, Model.Header);
cell.SetCellValue(fieldInfo.DisplayName);
cell.CellStyle = styleHeader;
index++;
}
return(workbook);
}
private void setSheet2(HSSFWorkbook workBook, ISheet sheet)
{
//创建表
ISheet sheet2 = workBook.CreateSheet("岗位数据");
//隐藏
workBook.SetSheetHidden(1, true);
//取数据
using (var db = DbFactory.Open())
{
var builder = db.From <Model.Post.Post>().Where(w => w.PostType == ZZTXEnums.行政村防汛防台工作组.ToString());
var rlist = db.Select <PostViewModel>(builder);
for (int iRowIndex = 0; iRowIndex < rlist.Count; iRowIndex++)
{
sheet2.CreateRow(iRowIndex).CreateCell(0).SetCellValue(rlist[iRowIndex].PostName);
}
}
//设计表名称
IName range = workBook.CreateName();
range.RefersToFormula = "岗位数据!$A:$A";
range.NameName = "PostDataName";
//定义下拉框范围
CellRangeAddressList regions = new CellRangeAddressList(3, 65535, 1, 1);
//设置数据引用
DVConstraint constraint = DVConstraint.CreateFormulaListConstraint("PostDataName");
HSSFDataValidation dataValidate = new HSSFDataValidation(regions, constraint);
sheet.AddValidationData(dataValidate);
}
public static void SetDropDownList(this HSSFSheet sheet, string[] datas, HSSFWorkbook workbook,
CellRangeAddressList addressList, string formulaName)
{
var hiddenSheetName = "HiddenDataSource" + DateTime.Now.ToString("yyyyMMddHHmmss");
ISheet CourseSheet = workbook.CreateSheet(hiddenSheetName);
workbook.SetSheetHidden(workbook.GetSheetIndex(hiddenSheetName), true);
//CourseSheet.CreateRow(0).CreateCell(0).SetCellValue("");
IRow row = null;
ICell cell = null;
for (int i = 0; i < datas.Length; i++)
{
row = CourseSheet.CreateRow(i);
cell = row.CreateCell(0);
cell.SetCellValue(datas[i]);
}
IName range = workbook.CreateName();
range.RefersToFormula = string.Format("{0}!$A$1:$A${1}", hiddenSheetName, datas.Length);
range.NameName = formulaName;
DVConstraint constraint = DVConstraint.CreateFormulaListConstraint(formulaName);
HSSFDataValidation dataValidate = new HSSFDataValidation(addressList, constraint);
sheet.AddValidationData(dataValidate);
}
/// <summary>
/// 设置下拉选项
/// </summary>
/// <param name="workBook"></param>
/// <param name="sheet"></param>
/// <param name="cellName"></param>
/// <param name="cellNo"></param>
/// <param name="list"></param>
public void setSheet2(HSSFWorkbook workBook, ISheet sheet, string cellName, int cellNo, List <string> list)
{
//创建表
ISheet sheet2 = workBook.CreateSheet(cellName);
//隐藏
workBook.SetSheetHidden(1, true);
//取数据
for (int i = 0; i < list.Count; i++)
{
sheet2.CreateRow(i).CreateCell(0).SetCellValue(list[i]);
}
//设计表名称
IName range = workBook.CreateName();
range.RefersToFormula = cellName + "!$A:$A";
range.NameName = cellName;
//定义下拉框范围
CellRangeAddressList regions = new CellRangeAddressList(2, 65535, cellNo, cellNo);
//设置数据引用
DVConstraint constraint = DVConstraint.CreateFormulaListConstraint(cellName);
HSSFDataValidation dataValidate = new HSSFDataValidation(regions, constraint);
sheet.AddValidationData(dataValidate);
}
public void AddDropDownListToCell(ISheet sheet, ICell cell, string[] list)
{
CellRangeAddressList cellRange = new CellRangeAddressList(cell.RowIndex, cell.RowIndex, cell.ColumnIndex, cell.ColumnIndex);
DVConstraint constraint = null;
if (string.Join("", list).Length < 200)
{
constraint = DVConstraint.CreateExplicitListConstraint(list);
}
else
{
var workBook = sheet.Workbook;
var hiddenSheet = workBook.GetSheet("hidden") ?? workBook.CreateSheet("hidden");
workBook.SetSheetHidden(workBook.GetSheetIndex("hidden"), SheetState.Hidden);
var rowsCount = hiddenSheet.PhysicalNumberOfRows;
for (int i = 0; i < list.Length; i++)
{
hiddenSheet.CreateRow(rowsCount + i).CreateCell(0).SetCellValue(list[i]);
}
var formula = string.Format("hidden!$A{0}:$A{1}", rowsCount + 1, rowsCount + list.Length);
constraint = DVConstraint.CreateFormulaListConstraint(formula);
}
HSSFDataValidation validation = new HSSFDataValidation(cellRange, constraint);
((HSSFSheet)sheet).AddValidationData(validation);
}
public void TestGetDataValidationsDate()
{
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.CreateSheet() as HSSFSheet;
List<IDataValidation> list = sheet.GetDataValidations();
Assert.AreEqual(0, list.Count);
IDataValidationHelper dataValidationHelper = sheet.GetDataValidationHelper();
IDataValidationConstraint constraint = dataValidationHelper.CreateDateConstraint(OperatorType.EQUAL,
"2014/10/25", null, null);
CellRangeAddressList AddressList = new CellRangeAddressList(0, 0, 0, 0);
IDataValidation validation = dataValidationHelper.CreateValidation(constraint, AddressList);
sheet.AddValidationData(validation);
list = sheet.GetDataValidations(); // <-- works
Assert.AreEqual(1, list.Count);
HSSFDataValidation dv = list[(0)] as HSSFDataValidation;
DVConstraint c = dv.Constraint;
Assert.AreEqual(ValidationType.DATE, c.GetValidationType());
Assert.AreEqual(OperatorType.EQUAL, c.Operator);
Assert.AreEqual(null, c.Formula1);
Assert.AreEqual(null, c.Formula2);
Assert.AreEqual(DateUtil.GetExcelDate(DateUtil.ParseYYYYMMDDDate("2014/10/25")), c.Value1);
Assert.AreEqual(double.NaN, c.Value2);
}
public void TestGetDataValidationsListExplicit()
{
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.CreateSheet() as HSSFSheet;
List<IDataValidation> list = sheet.GetDataValidations();
Assert.AreEqual(0, list.Count);
IDataValidationHelper dataValidationHelper = sheet.GetDataValidationHelper();
IDataValidationConstraint constraint = dataValidationHelper.CreateExplicitListConstraint(new String[] { "aaa",
"bbb", "ccc" });
CellRangeAddressList AddressList = new CellRangeAddressList(0, 0, 0, 0);
IDataValidation validation = dataValidationHelper.CreateValidation(constraint, AddressList);
validation.SuppressDropDownArrow = (/*setter*/true);
sheet.AddValidationData(validation);
list = sheet.GetDataValidations(); // <-- works
Assert.AreEqual(1, list.Count);
HSSFDataValidation dv = list[(0)] as HSSFDataValidation;
Assert.AreEqual(true, dv.SuppressDropDownArrow);
DVConstraint c = dv.Constraint;
Assert.AreEqual(ValidationType.LIST, c.GetValidationType());
Assert.AreEqual(null, c.Formula1);
Assert.AreEqual(null, c.Formula2);
Assert.AreEqual(double.NaN, c.Value1);
Assert.AreEqual(double.NaN, c.Value2);
String[] values = c.ExplicitListValues;
Assert.AreEqual(3, values.Length);
Assert.AreEqual("aaa", values[0]);
Assert.AreEqual("bbb", values[1]);
Assert.AreEqual("ccc", values[2]);
}
public void TestGetDataValidationsFormula()
{
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.CreateSheet() as HSSFSheet;
List <IDataValidation> list = sheet.GetDataValidations();
Assert.AreEqual(0, list.Count);
IDataValidationHelper dataValidationHelper = sheet.GetDataValidationHelper();
IDataValidationConstraint constraint = dataValidationHelper.CreateCustomConstraint("A2:A3");
CellRangeAddressList AddressList = new CellRangeAddressList(0, 0, 0, 0);
IDataValidation validation = dataValidationHelper.CreateValidation(constraint, AddressList);
sheet.AddValidationData(validation);
list = sheet.GetDataValidations(); // <-- works
Assert.AreEqual(1, list.Count);
HSSFDataValidation dv = list[(0)] as HSSFDataValidation;
DVConstraint c = dv.Constraint;
Assert.AreEqual(ValidationType.FORMULA, c.GetValidationType());
Assert.AreEqual("A2:A3", c.Formula1);
Assert.AreEqual(null, c.Formula2);
Assert.AreEqual(double.NaN, c.Value1);
Assert.AreEqual(double.NaN, c.Value2);
}
private static HSSFDataValidation DisplayMsg(CellRangeAddressList region, DVConstraint constraint, string errorMsg)
{
HSSFDataValidation dataValidate = new HSSFDataValidation(region, constraint);
dataValidate.CreateErrorBox("error", errorMsg);
return(dataValidate);
}
public void TestGetDataValidationsListFormula()
{
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.CreateSheet() as HSSFSheet;
List<IDataValidation> list = sheet.GetDataValidations();
Assert.AreEqual(0, list.Count);
IDataValidationHelper dataValidationHelper = sheet.GetDataValidationHelper();
IDataValidationConstraint constraint = dataValidationHelper.CreateFormulaListConstraint("A2");
CellRangeAddressList AddressList = new CellRangeAddressList(0, 0, 0, 0);
IDataValidation validation = dataValidationHelper.CreateValidation(constraint, AddressList);
validation.SuppressDropDownArrow = (/*setter*/true);
sheet.AddValidationData(validation);
list = sheet.GetDataValidations(); // <-- works
Assert.AreEqual(1, list.Count);
HSSFDataValidation dv = list[(0)] as HSSFDataValidation;
Assert.AreEqual(true, dv.SuppressDropDownArrow);
DVConstraint c = dv.Constraint;
Assert.AreEqual(ValidationType.LIST, c.GetValidationType());
Assert.AreEqual("A2", c.Formula1);
Assert.AreEqual(null, c.Formula2);
Assert.AreEqual(double.NaN, c.Value1);
Assert.AreEqual(double.NaN, c.Value2);
}
static void Main(string[] args)
{
InitializeWorkbook();
Sheet sheet1 = hssfworkbook.CreateSheet("Sheet1");
Sheet sheet2 = hssfworkbook.CreateSheet("Sheet2");
//create three items in Sheet2
Row row0 = sheet2.CreateRow(0);
Cell cell0 = row0.CreateCell(4);
cell0.SetCellValue("Product1");
row0 = sheet2.CreateRow(1);
cell0 = row0.CreateCell(4);
cell0.SetCellValue("Product2");
row0 = sheet2.CreateRow(2);
cell0 = row0.CreateCell(4);
cell0.SetCellValue("Product3");
CellRangeAddressList rangeList = new CellRangeAddressList();
//add the data validation to the first column (1-100 rows)
rangeList.AddCellRangeAddress(new CellRangeAddress(1, 100, 0, 0));
DVConstraint dvconstraint = DVConstraint.CreateFormulaListConstraint("Sheet2!$E1:$E3");
HSSFDataValidation dataValidation = new
HSSFDataValidation(rangeList, dvconstraint);
//add the data validation to sheet1
((HSSFSheet)sheet1).AddValidationData(dataValidation);
WriteToFile();
}
private static void CreateDropDownListForExcel(ISheet sheet, string[] dropDownValues, int startRow, int lastRow, int column)
{
if (sheet == null)
{
return;
}
IWorkbook workbook = sheet.Workbook;
string dropDownName = sheet.SheetName + "DropDownValuesForColumn" + column;
ISheet hiddenSheet = workbook.CreateSheet(dropDownName);
for (int i = 0, length = dropDownValues.Length; i < length; i++)
{
string name = dropDownValues[i];
IRow row = hiddenSheet.CreateRow(i);
ICell cell = row.CreateCell(0);
cell.SetCellValue(name);
}
IName namedCell = workbook.CreateName();
namedCell.NameName = dropDownName;
namedCell.RefersToFormula = (dropDownName + "!$A$1:$A$" + dropDownValues.Length);
HSSFDataValidationHelper dvHelper = new HSSFDataValidationHelper(sheet as HSSFSheet);
IDataValidationConstraint dvConstraint = dvHelper.CreateFormulaListConstraint(dropDownName);
CellRangeAddressList addressList = new CellRangeAddressList(startRow, lastRow, column, column);
HSSFDataValidation validation = (HSSFDataValidation)dvHelper.CreateValidation(dvConstraint, addressList);
int hiddenSheetIndex = workbook.GetSheetIndex(hiddenSheet);
workbook.SetSheetHidden(hiddenSheetIndex, SheetState.Hidden);
sheet.AddValidationData(validation);
}
private void AddValidationInternal(int operatorType, String firstFormula,
String secondFormula, HSSFDataValidation.ERRORSTYLE errorStyle, String ruleDescr, String promptDescr,
bool allowEmpty, bool inputBox, bool errorBox, bool suppressDropDown,
String[] explicitListValues)
{
int rowNum = _currentRowIndex++;
DVConstraint dc = CreateConstraint(operatorType, firstFormula, secondFormula, explicitListValues);
HSSFDataValidation dv = new HSSFDataValidation(new CellRangeAddressList(rowNum, rowNum, 0, 0), dc);
dv.EmptyCellAllowed = (allowEmpty);
dv.ErrorStyle = (errorStyle);
dv.CreateErrorBox("Invalid Input", "Something is wrong - Check condition!");
dv.CreatePromptBox("Validated Cell", "Allowable values have been restricted");
dv.ShowPromptBox = (inputBox);
dv.ShowErrorBox = (errorBox);
dv.SuppressDropDownArrow = (suppressDropDown);
_sheet.AddValidationData(dv);
WriteDataValidationSettings(_sheet, _style_1, _style_2, ruleDescr, allowEmpty,
inputBox, errorBox);
if (_cellStyle != null)
{
Row row = _sheet.GetRow(_sheet.PhysicalNumberOfRows - 1);
Cell cell = row.CreateCell(0);
cell.CellStyle = (_cellStyle);
}
WriteOtherSettings(_sheet, _style_1, promptDescr);
}
public static void AddValidationData(ISheet sheet, string listFormula, int colIndex, int firstRow, int lastRow = 65535)
{
CellRangeAddressList regions = new CellRangeAddressList(firstRow, lastRow, colIndex, colIndex);
DVConstraint constraint = DVConstraint.CreateFormulaListConstraint(listFormula);
HSSFDataValidation dataValidation = new HSSFDataValidation(regions, constraint);
sheet.AddValidationData(dataValidation);
}
/// <summary>
/// 生成老师导入表格
/// </summary>
/// <param name="columnName"></param>
/// <param name="col"></param>
/// <returns></returns>
public static string BuildTchExcel(List <string> columnName, List <string> col)
{
MemoryStream ms = new MemoryStream();
IWorkbook workbook = new HSSFWorkbook();
ISheet sheet = workbook.CreateSheet();
IRow headerRow = sheet.CreateRow(0);
for (int i = 0; i < columnName.Count; i++)
{
headerRow.CreateCell(i).SetCellValue(columnName[i]);
}
//设置生成下拉框的行和列
var cellRegions = new CellRangeAddressList(1, 65535, 2, 2);
//设置科目 下拉框内容
DVConstraint constraint = DVConstraint.CreateExplicitListConstraint(col.ToArray());
//绑定下拉框和作用区域，并设置错误提示信息
HSSFDataValidation dataValidate = new HSSFDataValidation(cellRegions, constraint);
dataValidate.CreateErrorBox("输入不合法", "请输入或选择下拉列表中的值。");
dataValidate.ShowPromptBox = true;
sheet.AddValidationData(dataValidate);
//设置生成性别下拉框的行和列
var cellRegions1 = new CellRangeAddressList(1, 65535, 4, 4);
//设置 下拉框内容
DVConstraint constraint1 = DVConstraint.CreateExplicitListConstraint(new String[] { "男", "女" });
//绑定下拉框和作用区域，并设置错误提示信息
HSSFDataValidation dataValidate1 = new HSSFDataValidation(cellRegions1, constraint1);
dataValidate1.CreateErrorBox("输入不合法", "请输入或选择下拉列表中的值。");
dataValidate1.ShowPromptBox = true;
sheet.AddValidationData(dataValidate1);
//设置生成职称下拉框的行和列
var cellRegions2 = new CellRangeAddressList(1, 65535, 5, 5);
//设置 下拉框内容
DVConstraint constraint2 = DVConstraint.CreateExplicitListConstraint(new String[] { "正高级教师", "高级教师", "一级教师", "二级教师", "三级教师" });
//绑定下拉框和作用区域，并设置错误提示信息
HSSFDataValidation dataValidate2 = new HSSFDataValidation(cellRegions2, constraint2);
dataValidate2.CreateErrorBox("输入不合法", "请输入或选择下拉列表中的值。");
dataValidate2.ShowPromptBox = true;
sheet.AddValidationData(dataValidate2);
workbook.Write(ms);
string fileName = "Excel\\" + DateTime.Now.ToString("yyyy-MM-dd") + "\\" + "教师" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls";
if (!Directory.Exists(System.AppDomain.CurrentDomain.BaseDirectory + "Excel\\" + DateTime.Now.ToString("yyyy-MM-dd")))
{
Directory.CreateDirectory(System.AppDomain.CurrentDomain.BaseDirectory + "Excel\\" + DateTime.Now.ToString("yyyy-MM-dd"));
}
var f = File.Create(System.AppDomain.CurrentDomain.BaseDirectory + fileName);
ms.WriteTo(f);
ms.Close();
f.Close();
return(fileName);
}
//public static void test1()
//{
// HSSFWorkbook hssfworkbook = new HSSFWorkbook();
// HSSFSheet sheet1 = hssfworkbook.CreateSheet("Sheet1") as HSSFSheet;
// CellRangeAddressList regions = new CellRangeAddressList(0, 65535, 0, 0);
// DVConstraint constraint = DVConstraint.CreateExplicitListConstraint(new string[] { "itemA", "itemB", "itemC" });
// HSSFDataValidation dataValidate = new HSSFDataValidation(regions, constraint);
// sheet1.AddValidationData(dataValidate);
// MemoryStream ms = new MemoryStream();
// hssfworkbook.Write(ms);
// string workbookFile = @"D:\\wulei22.xls";
// hssfworkbook = null;
// FileStream fs = new FileStream(workbookFile, FileMode.Create, FileAccess.Write);
// byte[] data = ms.ToArray();
// fs.Write(data, 0, data.Length);
// fs.Flush();
// fs.Close();
//}
public static void setdownlist()
{
//创建工作簿
HSSFWorkbook ssfworkbook = new HSSFWorkbook();
//创建工作表(页)
HSSFSheet sheet1 = ssfworkbook.CreateSheet("Sheet1") as HSSFSheet;
//创建一行
HSSFRow headerRow = (HSSFRow)sheet1.CreateRow(0);
//设置表头
headerRow.CreateCell(0).SetCellValue("ID");
//设置表头的宽度
sheet1.SetColumnWidth(0, 15 * 256);
#region 添加显示下拉列表
HSSFSheet sheet2 = ssfworkbook.CreateSheet("ShtDictionary") as HSSFSheet;
ssfworkbook.SetSheetHidden(1, true); //隐藏
sheet2.CreateRow(0).CreateCell(0).SetCellValue("itemA"); //列数据
sheet2.CreateRow(1).CreateCell(0).SetCellValue("itemB");
sheet2.CreateRow(2).CreateCell(0).SetCellValue("itemC");
HSSFName range = ssfworkbook.CreateName() as HSSFName;//创建名称
// range.Reference = "ShtDictionary!$A$1:$A$3";//格式
range.NameName = "dicRange";
#endregion
headerRow.CreateCell(1).SetCellValue("Selected");
sheet1.SetColumnWidth(1, 15 * 256);
//将下拉列表添加
CellRangeAddressList regions = new CellRangeAddressList(1, 65535, 1, 1);
DVConstraint constraint = DVConstraint.CreateFormulaListConstraint("dicRange");
HSSFDataValidation dataValidate = new HSSFDataValidation(regions, constraint);
sheet1.AddValidationData(dataValidate);
headerRow.CreateCell(2).SetCellValue("VALUE");
sheet1.SetColumnWidth(2, 15 * 256);
//写入数据
//创建数据行
HSSFRow dataRow = (HSSFRow)sheet1.CreateRow(1);
//填充数据
dataRow.CreateCell(0).SetCellValue("1"); //id
dataRow.CreateCell(1).SetCellValue(""); //选择框
dataRow.CreateCell(2).SetCellValue("值"); //选择框
System.IO.MemoryStream ms = new System.IO.MemoryStream();
ssfworkbook.Write(ms);
string filename = "Sheet1" + DateTime.Now.Year.ToString() + DateTime.Now.Month.ToString() + DateTime.Now.Day.ToString() + DateTime.Now.Hour.ToString() + DateTime.Now.Minute.ToString() + DateTime.Now.Second.ToString() + ".xls";
object Response = null;
string workbookFile = @"D:\\wulei.xls";
FileStream fs = new FileStream(workbookFile, FileMode.Create, FileAccess.Write);
byte[] data = ms.ToArray();
fs.Write(data, 0, data.Length);
fs.Flush();
fs.Close();
//Response.AddHeader("Content-Disposition", string.Format("attachment; filename=" + filename + ""));
//Response.BinaryWrite(ms.ToArray());
ms.Close();
ms.Dispose();
}
/// <summary>
/// 设置只是输入数字
/// </summary>
/// <param name="sheet"></param>
/// <param name="firstRow"></param>
/// <param name="lastRow"></param>
/// <param name="firstCol"></param>
/// <param name="lastCol"></param>
public static void SetCellInputNumber(ISheet sheet, int firstRow, int lastRow, int firstCol, int lastCol)
{
var cellRegions = new CellRangeAddressList(firstRow, lastRow, firstCol, firstCol);
DVConstraint constraint = DVConstraint.CreateNumericConstraint(ValidationType.DECIMAL, OperatorType.BETWEEN, "0", "999999999");
HSSFDataValidation dataValidate = new HSSFDataValidation(cellRegions, constraint);
dataValidate.CreateErrorBox("", "经过语言");
sheet.AddValidationData(dataValidate);
}
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);
}
/// <summary>
/// 引用另一个工作表的形式 得到下拉
/// </summary>
/// <param name="book"></param>
/// <param name="columnIndex"></param>
/// <param name="values"></param>
/// <param name="sheetName"></param>
/// <returns></returns>
public static void CreateListConstaint(this HSSFWorkbook book, Int32 columnIndex, IEnumerable <String> values, string sheetName = "")
{
if (values == null)
{
return;
}
if (string.IsNullOrEmpty(sheetName))
{
sheetName = "_constraintSheet_";
}
//创建下拉数据到新表中
ISheet sheet = book.GetSheet(sheetName) ?? book.CreateSheet(sheetName);;
var firstRow = sheet.GetRow(0);
var conColumnIndex = firstRow == null ? 0 : firstRow.PhysicalNumberOfCells;
var rowIndex = 0;
var lastValue = string.Empty;
foreach (var value in values)
{
var row = sheet.GetRow(rowIndex) ?? sheet.CreateRow(rowIndex);
row.CreateCell(conColumnIndex).SetCellValue(value);
rowIndex++;
lastValue = value;
}
//如果无可选值的话，则增加一个空选项，防止用户填写内容
if (values.Count() == 0)
{
var row = sheet.GetRow(rowIndex) ?? sheet.CreateRow(rowIndex);
row.CreateCell(conColumnIndex).SetCellValue(" ");
rowIndex++;
}
//给该列所有单元格加上下拉选择
IName range = book.CreateName();
range.RefersToFormula = String.Format("{2}!${0}$1:${0}${1}",
(Char)('A' + conColumnIndex),
rowIndex.ToString(), sheetName);
string rangeName = "dicRange" + columnIndex;
range.NameName = rangeName;
var cellRegions = new CellRangeAddressList(1, 65535, columnIndex, columnIndex);
var constraint = DVConstraint.CreateFormulaListConstraint(rangeName);
book.SetSheetHidden(book.GetSheetIndex(sheet), SheetState.HIDDEN);
//创建验证
HSSFDataValidation valid = new HSSFDataValidation(cellRegions, constraint);
//关联验证
HSSFSheet v = book.GetSheetAt(0) as HSSFSheet;
v.AddValidationData(valid);
}
/// <summary>
/// 验证与提示
/// </summary>
/// <param name="validItem"></param>
/// <param name="colIndex"></param>
/// <param name="sheet"></param>
private void ValidAndMessage(ColumnValidItem validItem, int colIndex, ISheet sheet)
{
DVConstraint constraint = null;
HSSFDataValidation dataValidate = null;
switch (validItem.ValidType)//验证类型
{
case EnumValidType.Integer:
constraint = DVConstraint.CreateNumericConstraint(ValidationType.INTEGER, (int)validItem.ValidQualifier, validItem.ValidMin, validItem.ValidMax);
break;
case EnumValidType.Decimal:
constraint = DVConstraint.CreateNumericConstraint(ValidationType.DECIMAL, (int)validItem.ValidQualifier, validItem.ValidMin, validItem.ValidMax);
break;
case EnumValidType.DateTime:
constraint = DVConstraint.CreateDateConstraint((int)validItem.ValidQualifier, validItem.ValidMin, validItem.ValidMax, "yyyy-MM-dd");
break;
case EnumValidType.TextLength:
constraint = DVConstraint.CreateNumericConstraint(ValidationType.TEXT_LENGTH, (int)validItem.ValidQualifier, validItem.ValidMin ?? "1", validItem.ValidMax ?? "200");
break;
case EnumValidType.Customize:
constraint = DVConstraint.CreateCustomFormulaConstraint(string.Format(validItem.ValidFormula, Chr(colIndex)));
break;
}
if (constraint != null)
{
CellRangeAddressList regions = null;
if (validItem.ValidType == EnumValidType.Customize && validItem.ValidFormula == "COUNTIF({0}:{0},\"*@*.*\")=1") //邮件格式验证
{
int rowCount = 2000; //只支持前2000行邮件格式验证
for (int i = 0; i < rowCount; i++)
{
constraint = DVConstraint.CreateCustomFormulaConstraint(string.Format("COUNTIF({0}{1},\"*@*.*\")=1", Chr(colIndex), i + 2));
regions = new CellRangeAddressList(i + 1, i + 1, colIndex, colIndex);
dataValidate = new HSSFDataValidation(regions, constraint);
dataValidate.CreatePromptBox(validItem.Name, validItem.InputMessage);
dataValidate.CreateErrorBox(validItem.Name, validItem.ErrorMessage);
((HSSFSheet)sheet).AddValidationData(dataValidate);
}
}
else
{
regions = new CellRangeAddressList(1, 65535, colIndex, colIndex);
dataValidate = new HSSFDataValidation(regions, constraint);
dataValidate.CreatePromptBox(validItem.Name, validItem.InputMessage);
dataValidate.CreateErrorBox(validItem.Name, validItem.ErrorMessage);
((HSSFSheet)sheet).AddValidationData(dataValidate);
}
}
}
public static HSSFDataValidation CreateDateConstraint(this HSSFWorkbook book, int columnIndex)
{
ISheet sheet1 = book.GetSheetAt(0);
CellRangeAddressList cellRegions = new CellRangeAddressList(1, 65535, columnIndex, columnIndex);
DVConstraint constraint = DVConstraint.CreateDateConstraint(OperatorType.BETWEEN, "1900-01-01", "2999-12-31", "yyyy-MM-dd");
HSSFDataValidation dataValidate = new HSSFDataValidation(cellRegions, constraint);
dataValidate.CreateErrorBox("error", "You must input a date.");
sheet1.AddValidationData(dataValidate);
return(new HSSFDataValidation(cellRegions, constraint));
}
public static HSSFDataValidation CreateNumericConstraint(this HSSFWorkbook book, int columnIndex, string minvalue, string maxvalue, bool isInteger = false)
{
ISheet sheet1 = book.GetSheetAt(0);
CellRangeAddressList cellRegions = new CellRangeAddressList(1, 65535, columnIndex, columnIndex);
DVConstraint constraint = DVConstraint.CreateNumericConstraint(isInteger ? ValidationType.INTEGER : ValidationType.DECIMAL, OperatorType.BETWEEN, minvalue, maxvalue);
HSSFDataValidation dataValidate = new HSSFDataValidation(cellRegions, constraint);
dataValidate.CreateErrorBox("error", "You must input a numeric between " + minvalue + " and " + maxvalue + ".");
sheet1.AddValidationData(dataValidate);
return(new HSSFDataValidation(cellRegions, constraint));
}
private void WriteReportWithData()
{
var reportdata = _data.ReportData.ToList().AsParallel();
InitializeWorkbook();
var sheet = _hssfworkbook.CreateSheet("request");
_hssfworkbook.CreateSheet("hidden");
var style = _hssfworkbook.CreateCellStyle();
style.FillForegroundColor = HSSFColor.DarkBlue.Index;
style.FillPattern = FillPattern.SolidForeground;
var font = _hssfworkbook.CreateFont();
font.Color = HSSFColor.White.Index;
style.SetFont(font);
var rows = reportdata.Count() + 1;
for (var j = 0; j < rows; j++)
{
var row = sheet.CreateRow(j);
for (var i = 0; i < _data.Columns.Count; i++)
{
var header = _data.Columns.ElementAt(i);
if (j == 0)
{
var cell = row.CreateCell(i);
cell.SetCellValue(header);
cell.CellStyle = style;
}
else
{
var dictionary = reportdata.ElementAt(j - 1);
row.CreateCell(i).SetCellValue(dictionary[header]);
}
}
sheet.AutoSizeColumn(j);
}
sheet.SetColumnHidden(0, true);
var namedcell = _hssfworkbook.CreateName();
namedcell.NameName = "hidden";
var constraint = DVConstraint.CreateFormulaListConstraint("hidden");
var addressList = new CellRangeAddressList(1, reportdata.Count(), _data.Columns.Count,
_data.Columns.Count);
var validation = new HSSFDataValidation(addressList, constraint);
_hssfworkbook.SetSheetHidden(1, true);
}
// 模板
public static byte[] CreateExcelTemplate(Tk5ListMetaData metaData)
{
MemoryStream ms = new MemoryStream();
using (ms)
{
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet(metaData.Table.TableDesc);
HSSFRow dataRow = (HSSFRow)sheet.CreateRow(0);
int index = 0;
ExportExcelPageMaker headerConfigData =
new ExportExcelPageMaker(ContentFormat.DefaultHead, ContentFormat.DefaultContent)
{
UserBorder = true
};
ExportExcelPageMaker contentConfigData =
new ExportExcelPageMaker(ContentFormat.DefaultHead, ContentFormat.DefaultContent)
{
UserBorder = false
};
foreach (Tk5FieldInfoEx fieldInfo in metaData.Table.TableList)
{
int colWith = NPOIWrite.GetColWidth(fieldInfo);
sheet.SetColumnWidth(index, colWith << 8);
ICellStyle styleContent = NPOIWrite.BorderAndFontSetting(workbook,
contentConfigData, fieldInfo, NPOIWrite.Model.Content);
HSSFDataValidation dataValidate = CreateDataValidation(index, fieldInfo, styleContent, workbook);
sheet.SetDefaultColumnStyle(index, styleContent);
if (dataValidate != null)
{
((HSSFSheet)sheet).AddValidationData(dataValidate);
}
ICell cell = dataRow.CreateCell(index);
ICellStyle styleHeader = NPOIWrite.BorderAndFontSetting(workbook,
headerConfigData, fieldInfo, NPOIWrite.Model.Header);
cell.SetCellValue(fieldInfo.DisplayName);
cell.CellStyle = styleHeader;
index++;
}
//string strFileName = @"D:\EmportTemplateTest.xls";
//using (FileStream fs = new FileStream(strFileName, FileMode.Create, FileAccess.Write))
//{
// workbook.Write(fs);
//}
workbook.Write(ms);
ms.Flush();
return(ms.ToArray());
}
}
/// <summary>
/// 验证手机号格式
/// </summary>
/// <param name="sheet"></param>
/// <param name="index"></param>
/// <param name="formula"></param>
/// <param name="message"></param>
public static void SetCellFormula(this ISheet sheet, int index, string formula, string message = "输入格式不正确")
{
//设置生成下拉框的行和列
var cellRegions = new CellRangeAddressList(1, 65535, index, index);
var constraint = DVConstraint.
CreateCustomFormulaConstraint(formula);
var dataValidate = new HSSFDataValidation(cellRegions, constraint);
dataValidate.CreateErrorBox("输入不合法", message);
//dataValidate.PromptBoxTitle = "ErrorInput";
sheet.AddValidationData(dataValidate);
}
public static byte[] ModifyTemplete(string fileName, string[] deptList, string[] dutiesList)
{
IWorkbook workbook = null; //全局workbook
ISheet sheet; //sheet
try
{
FileInfo fileInfo = new FileInfo(fileName);//判断文件是否存在
if (fileInfo.Exists)
{
FileStream fileStream = fileInfo.OpenRead();//打开文件，得到文件流
switch (fileInfo.Extension)
{
//xls是03，用HSSFWorkbook打开，.xlsx是07或者10用XSSFWorkbook打开
case ".xls": workbook = new HSSFWorkbook(fileStream); break;
case ".xlsx": workbook = new XSSFWorkbook(fileStream); break;
default: break;
}
fileStream.Close();//关闭文件流
}
if (workbook == null)
{
return(null);
}
sheet = workbook.GetSheetAt(0);
if (deptList != null && deptList.Length > 0)
{
CellRangeAddressList regions = new CellRangeAddressList(0, 65535, 2, 2);
DVConstraint constraint = DVConstraint.CreateExplicitListConstraint(deptList);
HSSFDataValidation validate = new HSSFDataValidation(regions, constraint);
sheet.AddValidationData(validate);
}
if (dutiesList != null && dutiesList.Length > 0)
{
CellRangeAddressList regions = new CellRangeAddressList(0, 65535, 4, 4);
DVConstraint constraint = DVConstraint.CreateExplicitListConstraint(dutiesList);
HSSFDataValidation validate = new HSSFDataValidation(regions, constraint);
sheet.AddValidationData(validate);
}
MemoryStream ms = new MemoryStream();
workbook.Write(ms);
byte[] buf = ms.ToArray();
return(buf);
}
catch { }
return(null);
}
public void TestGetDataValidationsAny()
{
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.CreateSheet() as HSSFSheet;
List <IDataValidation> list = sheet.GetDataValidations();
Assert.AreEqual(0, list.Count);
IDataValidationHelper dataValidationHelper = sheet.GetDataValidationHelper();
IDataValidationConstraint constraint = dataValidationHelper.CreateNumericConstraint(ValidationType.ANY,
OperatorType.IGNORED, null, null);
CellRangeAddressList AddressList = new CellRangeAddressList(1, 2, 3, 4);
IDataValidation validation = dataValidationHelper.CreateValidation(constraint, AddressList);
validation.EmptyCellAllowed = (/*setter*/ true);
validation.CreateErrorBox("error-title", "error-text");
validation.CreatePromptBox("prompt-title", "prompt-text");
sheet.AddValidationData(validation);
list = sheet.GetDataValidations(); // <-- works
Assert.AreEqual(1, list.Count);
HSSFDataValidation dv = list[(0)] as HSSFDataValidation;
{
CellRangeAddressList regions = dv.Regions;
Assert.AreEqual(1, regions.CountRanges());
CellRangeAddress Address = regions.GetCellRangeAddress(0);
Assert.AreEqual(1, Address.FirstRow);
Assert.AreEqual(2, Address.LastRow);
Assert.AreEqual(3, Address.FirstColumn);
Assert.AreEqual(4, Address.LastColumn);
}
Assert.AreEqual(true, dv.EmptyCellAllowed);
Assert.AreEqual(false, dv.SuppressDropDownArrow);
Assert.AreEqual(true, dv.ShowErrorBox);
Assert.AreEqual("error-title", dv.ErrorBoxTitle);
Assert.AreEqual("error-text", dv.ErrorBoxText);
Assert.AreEqual(true, dv.ShowPromptBox);
Assert.AreEqual("prompt-title", dv.PromptBoxTitle);
Assert.AreEqual("prompt-text", dv.PromptBoxText);
IDataValidationConstraint c = dv.ValidationConstraint;
Assert.AreEqual(ValidationType.ANY, c.GetValidationType());
Assert.AreEqual(OperatorType.IGNORED, c.Operator);
}
/// <summary>
/// 设置单元格为日期
/// </summary>
/// <param name="sheet"></param>
/// <param name="index"></param>
public static void SetCellDate(this ISheet sheet, int index)
{
//设置生成下拉框的行和列
var cellRegions = new CellRangeAddressList(1, 65535, index, index);
//设置 下拉框内容
DVConstraint constraint = DVConstraint.CreateDateConstraint(OperatorType.BETWEEN, "1900-01-01", "2999-12-31", "yyyy-MM-dd");
//绑定下拉框和作用区域，并设置错误提示信息
HSSFDataValidation dataValidate = new HSSFDataValidation(cellRegions, constraint);
dataValidate.CreateErrorBox("输入不合法", "请输入正确的时间格式");
dataValidate.ShowPromptBox = true;
sheet.AddValidationData(dataValidate);
}
public static void SetCellDropdownList(ISheet sheet, int firstcol, int lastcol, string[] vals)
{
//设置生成下拉框的行和列
var cellRegions = new CellRangeAddressList(1, 65535, firstcol, lastcol);
//设置 下拉框内容
DVConstraint constraint = DVConstraint.CreateExplicitListConstraint(vals);
//绑定下拉框和作用区域，并设置错误提示信息
HSSFDataValidation dataValidate = new HSSFDataValidation(cellRegions, constraint);
dataValidate.CreateErrorBox("输入不合法", "请输入或选择下拉列表中的值。");
dataValidate.ShowPromptBox = true;
sheet.AddValidationData(dataValidate);
}
public void VisitRecord(Record r)
{
if (!(r is DVRecord))
{
return;
}
DVRecord dvRecord = (DVRecord)r;
CellRangeAddressList regions = dvRecord.CellRangeAddress.Copy();
DVConstraint constraint = DVConstraint.CreateDVConstraint(dvRecord, book);
HSSFDataValidation hssfDataValidation = new HSSFDataValidation(regions, constraint);
hssfDataValidation.ErrorStyle = (dvRecord.ErrorStyle);
hssfDataValidation.EmptyCellAllowed = (dvRecord.EmptyCellAllowed);
hssfDataValidation.SuppressDropDownArrow = (dvRecord.SuppressDropdownArrow);
hssfDataValidation.CreatePromptBox(dvRecord.PromptTitle, dvRecord.PromptText);
hssfDataValidation.ShowPromptBox = (dvRecord.ShowPromptOnCellSelected);
hssfDataValidation.CreateErrorBox(dvRecord.ErrorTitle, dvRecord.ErrorText);
hssfDataValidation.ShowErrorBox = (dvRecord.ShowErrorOnInvalidValue);
hssfValidations.Add(hssfDataValidation);
}
/// <summary>
/// Creates a data validation object
/// </summary>
/// <param name="dataValidation">The Data validation object settings</param>
public void AddValidationData(HSSFDataValidation dataValidation)
{
if (dataValidation == null)
{
throw new ArgumentException("objValidation must not be null");
}
DataValidityTable dvt = _sheet.GetOrCreateDataValidityTable();
DVRecord dvRecord = dataValidation.CreateDVRecord(_workbook);
dvt.AddDataValidation(dvRecord);
}
/// <summary>
/// 设置某些列的值只能输入预制的数据,显示下拉框
/// </summary>
/// <param name="sheet">要设置的sheet</param>
/// <param name="textlist">下拉框显示的内容</param>
/// <param name="firstRow">开始行</param>
/// <param name="endRow">结束行</param>
/// <param name="firstCol">开始列</param>
/// <param name="endCol">结束列</param>
/// <returns>设置好的sheet</returns>
public static ISheet SetHSSFValidation(ISheet sheet,
string[] textlist, int firstRow, int endRow, int firstCol,
int endCol)
{
IWorkbook workbook = sheet.Workbook;
if (endRow > sheet.LastRowNum)
{
endRow = sheet.LastRowNum;
}
ISheet hidden = null;
string hiddenSheetName = "hidden" + sheet.SheetName;
int hIndex = workbook.GetSheetIndex(hiddenSheetName);
if (hIndex < 0)
{
hidden = workbook.CreateSheet(hiddenSheetName);
workbook.SetSheetHidden(sheet.Workbook.NumberOfSheets - 1, SheetState.HIDDEN);
}
else
{
hidden = workbook.GetSheetAt(hIndex);
}
IRow row = null;
ICell cell = null;
for (int i = 0, length = textlist.Length; i < length; i++)
{
row = hidden.GetRow(i);
if (row == null)
{
row = hidden.CreateRow(i);
}
cell = row.GetCell(firstCol);
if (cell == null)
{
cell = row.CreateCell(firstCol);
}
cell.SetCellValue(textlist[i]);
}
// 加载下拉列表内容
string nameCellKey = hiddenSheetName + firstCol;
IName namedCell = workbook.GetName(nameCellKey);
if (namedCell == null)
{
namedCell = workbook.CreateName();
namedCell.NameName = nameCellKey;
namedCell.RefersToFormula = string.Format("{0}!${1}$1:${1}${2}", hiddenSheetName, NumberToChar(firstCol + 1), textlist.Length);
}
DVConstraint constraint = DVConstraint.CreateFormulaListConstraint(nameCellKey);
// 设置数据有效性加载在哪个单元格上,四个参数分别是：起始行、终止行、起始列、终止列
CellRangeAddressList regions = new CellRangeAddressList(firstRow, endRow, firstCol, endCol);
// 数据有效性对象
HSSFDataValidation validation = new HSSFDataValidation(regions, constraint);
//// 取消弹出错误框
//validation.ShowErrorBox = false;
sheet.AddValidationData(validation);
return sheet;
}