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);
}
private DVConstraint CreateConstraint(int operatorType, String firstFormula,
String secondFormula, String[] explicitListValues)
{
if (_validationType == DVConstraint.ValidationType.LIST)
{
if (explicitListValues != null)
{
return(DVConstraint.CreateExplicitListConstraint(explicitListValues));
}
return(DVConstraint.CreateFormulaListConstraint(firstFormula));
}
if (_validationType == DVConstraint.ValidationType.TIME)
{
return(DVConstraint.CreateTimeConstraint(operatorType, firstFormula, secondFormula));
}
if (_validationType == DVConstraint.ValidationType.DATE)
{
return(DVConstraint.CreateDateConstraint(operatorType, firstFormula, secondFormula, null));
}
if (_validationType == DVConstraint.ValidationType.FORMULA)
{
return(DVConstraint.CreateCustomFormulaConstraint(firstFormula));
}
return(DVConstraint.CreateNumericConstraint(_validationType, operatorType, firstFormula, secondFormula));
}
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 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]);
}
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 void TestGetDataValidationsFormula()
{
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.CreateSheet() as HSSFSheet;
List <IDataValidation> list = sheet.GetDataValidations();
Assert.AreEqual(0, list.Count);
IDataValidationHelper dataValidationHelper = sheet.GetDataValidationHelper();
IDataValidationConstraint constraint = dataValidationHelper.CreateCustomConstraint("A2:A3");
CellRangeAddressList AddressList = new CellRangeAddressList(0, 0, 0, 0);
IDataValidation validation = dataValidationHelper.CreateValidation(constraint, AddressList);
sheet.AddValidationData(validation);
list = sheet.GetDataValidations(); // <-- works
Assert.AreEqual(1, list.Count);
HSSFDataValidation dv = list[(0)] as HSSFDataValidation;
DVConstraint c = dv.Constraint;
Assert.AreEqual(ValidationType.FORMULA, c.GetValidationType());
Assert.AreEqual("A2:A3", c.Formula1);
Assert.AreEqual(null, c.Formula2);
Assert.AreEqual(double.NaN, c.Value1);
Assert.AreEqual(double.NaN, c.Value2);
}
/// <summary>
/// 设置下拉选项
/// </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 TestGetDataValidationsDecimal(){
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.CreateSheet() as HSSFSheet;
List<IDataValidation> list = sheet.GetDataValidations();
Assert.AreEqual(0, list.Count);
IDataValidationHelper dataValidationHelper = sheet.GetDataValidationHelper();
IDataValidationConstraint constraint = dataValidationHelper.CreateDecimalConstraint(OperatorType.BETWEEN, "=A2",
"200");
CellRangeAddressList AddressList = new CellRangeAddressList(0, 0, 0, 0);
IDataValidation validation = dataValidationHelper.CreateValidation(constraint, AddressList);
sheet.AddValidationData(validation);
list = sheet.GetDataValidations(); // <-- works
Assert.AreEqual(1, list.Count);
HSSFDataValidation dv = list[(0)] as HSSFDataValidation;
DVConstraint c = dv.Constraint;
Assert.AreEqual(ValidationType.DECIMAL, c.GetValidationType());
Assert.AreEqual(OperatorType.BETWEEN, c.Operator);
Assert.AreEqual("A2", c.Formula1);
Assert.AreEqual(null, c.Formula2);
Assert.AreEqual(double.NaN, c.Value1);
Assert.AreEqual(200, c.Value2);
}
public void 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);
}
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);
}
private static HSSFDataValidation DisplayMsg(CellRangeAddressList region, DVConstraint constraint, string errorMsg)
{
HSSFDataValidation dataValidate = new HSSFDataValidation(region, constraint);
dataValidate.CreateErrorBox("error", errorMsg);
return(dataValidate);
}
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();
}
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);
}
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>
/// EXCEL2003下拉值
/// </summary>
/// <param name="sheet"></param>
/// <param name="data"></param>
/// <param name="StarCol"></param>
/// <param name="EndCol"></param>
private static void CreateDropDwonListForXLS(HSSFSheet sheet, List <string> data, int StarCol, int EndCol)
{
HSSFDataValidationHelper Validation = new HSSFDataValidationHelper(sheet);
DVConstraint Constraint = (DVConstraint)Validation.CreateExplicitListConstraint(data.ToArray());
CellRangeAddressList AddressList = new CellRangeAddressList(1, 65535, StarCol, EndCol);
var HSSF = Validation.CreateValidation(Constraint, AddressList);
sheet.AddValidationData(HSSF);
}
/// <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);
}
/// <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);
}
/// <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);
}
/// <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);
}
private void CellDataWriter1(int row, int col, string FilePath, string SheetName)
{
var fs = new FileStream(FilePath, FileMode.Open, FileAccess.ReadWrite);
var templateWorkbook = new HSSFWorkbook(fs);
var sheet = (HSSFSheet)templateWorkbook.GetSheet(SheetName);
int r = row;
string result1 = TempData["res1"].ToString();
string result2 = TempData["res2"].ToString();
string result = TempData["res"].ToString();
int c = col;
CellRangeAddressList addressList1 = new CellRangeAddressList(1, 50, 1, 1);
DVConstraint dvConstraint1 = DVConstraint.CreateExplicitListConstraint(
new String[] { result1 });
HSSFDataValidation dataValidation1 = new HSSFDataValidation(addressList1, dvConstraint1);
dataValidation1.SuppressDropDownArrow = false;
((HSSFSheet)sheet).AddValidationData(dataValidation1);
CellRangeAddressList addressList2 = new CellRangeAddressList(1, 50, 2, 2);
DVConstraint dvConstraint2 = DVConstraint.CreateExplicitListConstraint(
new String[] { result2 });
HSSFDataValidation dataValidation2 = new HSSFDataValidation(addressList2, dvConstraint2);
dataValidation2.SuppressDropDownArrow = false;
((HSSFSheet)sheet).AddValidationData(dataValidation2);
CellRangeAddressList addressList = new CellRangeAddressList(1, 50, 4, 4);
DVConstraint dvConstraint = DVConstraint.CreateExplicitListConstraint(
new String[] { result });
HSSFDataValidation dataValidation = new HSSFDataValidation(addressList, dvConstraint);
dataValidation.SuppressDropDownArrow = false;
((HSSFSheet)sheet).AddValidationData(dataValidation);
fs = new FileStream(FilePath, FileMode.Open, FileAccess.ReadWrite);
templateWorkbook.Write(fs);
fs.Close();
}
/// <summary>
/// 设置单元格只能输入数字
/// </summary>
/// <param name="sheet"></param>
public static void SetCellInputNumber(this ISheet sheet, int index, string min, string max, string msg)
{
//设置生成下拉框的行和列
var cellRegions = new CellRangeAddressList(1, 65535, index, index);
//第二个参数int comparisonOperator 参考源码获取
//https://github.com/tonyqus/npoi
//NPOITest项目
DVConstraint constraint = DVConstraint.CreateNumericConstraint(
ValidationType.INTEGER, OperatorType.BETWEEN, min, max);
HSSFDataValidation dataValidate = new HSSFDataValidation(cellRegions, constraint);
dataValidate.CreateErrorBox("输入不合法", msg);
//dataValidate.PromptBoxTitle = "ErrorInput";
sheet.AddValidationData(dataValidate);
}
public void TestPLVRecord1()
{
Stream is1 = HSSFTestDataSamples.OpenSampleFileStream(XLS_FILENAME);
HSSFWorkbook workbook = new HSSFWorkbook(is1);
CellRangeAddressList cellRange = new CellRangeAddressList(0, 0, 1, 1);
IDataValidationConstraint constraint = DVConstraint.CreateFormulaListConstraint(DV_DEFINITION);
HSSFDataValidation dataValidation = new HSSFDataValidation(cellRange, constraint);
// This used to throw an error before
try
{
workbook.GetSheet(SHEET_NAME).AddValidationData(dataValidation);
}
catch (InvalidOperationException)
{
Assert.Fail("Identified bug 53972, PLV record breaks addDataValidation()");
}
}
