public string Get(Guid entityId)
{
var dir = _webHelper.MapPath("~/excel/");
var entity = _entityFinder.FindById(entityId);
var attributes = _attributeFinder.Query(n => n.Where(f => f.EntityId == entityId &&
f.AttributeTypeName != AttributeTypeIds.PRIMARYKEY &&
f.Name.NotIn("createdon", "createdby", "modifiedon", "modifiedby", "versionnumber", "owneridtype", "owningbusinessunit", "organizationid", "workflowid", "processstate", "stageid")).Sort(s => s.SortAscending(a => a.CreatedOn)));
var filePath = dir + entity.Name + "_" + DateTime.Now.ToString("yyyyMMddhhmmss") + ".xls";
HSSFWorkbook book = new HSSFWorkbook();
ISheet sheet = book.CreateSheet(entity.LocalizedName);
IRow headerRow = sheet.CreateRow(0);
int columnIndex = 0;
foreach (var attr in attributes)
{
ICell cell;
ICellStyle style = book.CreateCellStyle();
if (attr.TypeIsPickList() || attr.TypeIsStatus())
{
var options = _optionSetDetailServiceFinder.Query(n => n.Where(f => f.OptionSetId == attr.OptionSetId).Sort(s => s.SortAscending(f => f.DisplayOrder)));
sheet.AddValidationData(book.CreateListConstraint(columnIndex, options.Select(f => f.Name)));
}
else if (attr.TypeIsBit() || attr.TypeIsState())
{
var options = _stringMapFinder.Query(n => n.Where(f => f.AttributeId == attr.AttributeId).Sort(s => s.SortAscending(f => f.DisplayOrder)));
sheet.AddValidationData(book.CreateListConstraint(columnIndex, options.Select(f => f.Name)));
}
else if (attr.TypeIsDateTime())
{
sheet.AddValidationData(book.CreateDateConstraint(columnIndex));
}
else if (attr.TypeIsDecimal() || attr.TypeIsMoney())
{
sheet.AddValidationData(book.CreateNumericConstraint(columnIndex, attr.MinValue.ToString(), attr.MaxValue.ToString()));
}
else if (attr.TypeIsInt())
{
sheet.AddValidationData(book.CreateNumericConstraint(columnIndex, attr.MinValue.ToString(), attr.MaxValue.ToString(), true));
}
cell = headerRow.CreateCell(columnIndex);
cell.SetCellValue(attr.LocalizedName);
cell.CellStyle = style;
columnIndex++;
}
using (MemoryStream ms = new MemoryStream())
{
book.Write(ms);
using (FileStream fs = new FileStream(filePath, FileMode.Create, FileAccess.Write))
{
byte[] data = ms.ToArray();
fs.Write(data, 0, data.Length);
fs.Flush();
}
book = null;
}
return(filePath);
}
/// <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);
}
static void Main(string[] args)
{
string workbookName = "test.xlsx";
IWorkbook workbook = null;
ISheet sheet = null;
IDataValidationHelper dvHelper = null;
IDataValidationConstraint dvConstraint = null;
IDataValidation validation = null;
CellRangeAddressList addressList = null;
// Using the ss.usermodel allows this class to support both binary
// and xml based workbooks. The choice of which one to create is
// made by checking the file extension.
if (workbookName.EndsWith(".xlsx"))
{
workbook = new XSSFWorkbook();
}
else
{
workbook = new HSSFWorkbook();
}
// Build the sheet that will hold the data for the validations. This
// must be done first as it will create names that are referenced
// later.
sheet = workbook.CreateSheet("Linked Validations");
BuildDataSheet(sheet);
// Build the first data validation to occupy cell A1. Note
// that it retrieves it's data from the named area or region called
// CHOICES. Further information about this can be found in the
// static buildDataSheet() method below.
addressList = new CellRangeAddressList(0, 0, 0, 0);
dvHelper = sheet.GetDataValidationHelper();
dvConstraint = dvHelper.CreateFormulaListConstraint("CHOICES");
validation = dvHelper.CreateValidation(dvConstraint, addressList);
sheet.AddValidationData(validation);
// Now, build the linked or dependent drop down list that will
// occupy cell B1. The key to the whole process is the use of the
// INDIRECT() function. In the buildDataSheet(0 method, a series of
// named regions are created and the names of three of them mirror
// the options available to the user in the first drop down list
// (in cell A1). Using the INDIRECT() function makes it possible
// to convert the selection the user makes in that first drop down
// into the addresses of a named region of cells and then to use
// those cells to populate the second drop down list.
addressList = new CellRangeAddressList(0, 0, 1, 1);
dvConstraint = dvHelper.CreateFormulaListConstraint(
"INDIRECT(UPPER($A$1))");
validation = dvHelper.CreateValidation(dvConstraint, addressList);
sheet.AddValidationData(validation);
FileStream sw = File.OpenWrite(workbookName);
workbook.Write(sw);
sw.Close();
}
private string d2007()
{
string fileName = "G:/" + DateTime.Now.Ticks.ToString() + ".xlsx";
XSSFWorkbook wk = new XSSFWorkbook();
ISheet sheet = wk.CreateSheet();
CellRangeAddressList regions = new CellRangeAddressList(0, 5, 2, 2);
CT_DataValidation ctDataValidation = new CT_DataValidation();
ctDataValidation.allowBlank = true;
ctDataValidation.type = ST_DataValidationType.list;
ctDataValidation.showInputMessage = true;
ctDataValidation.showErrorMessage = true;
ctDataValidation.sqref = "C1:C88";
ctDataValidation.formula1 = "\"itemA,itemB,itemC\"";
//ctDataValidation.showDropDown = true;
//XSSFDataValidationConstraint constraint = new XSSFDataValidationConstraint(new string[] { "itemA", "itemB", "itemC" });
XSSFDataValidation dataValidate = new XSSFDataValidation(regions, ctDataValidation);
//dataValidate= new XSSFDataValidation()
sheet.AddValidationData(dataValidate);
using (FileStream fs = File.OpenWrite(fileName))
{
wk.Write(fs);
}
return(fileName);
}
/// <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);
}
private void AddValidationInternal(int operatorType, string firstFormula,
string secondFormula, int errorStyle, string ruleDescr, string promptDescr,
bool allowEmpty, bool inputBox, bool errorBox, bool suppressDropDown,
string[] explicitListValues)
{
int rowNum = _currentRowIndex++;
IDataValidationHelper dataValidationHelper = _sheet.GetDataValidationHelper();
IDataValidationConstraint dc = CreateConstraint(dataValidationHelper, operatorType, firstFormula, secondFormula, explicitListValues);
IDataValidation dv = dataValidationHelper.CreateValidation(dc, new CellRangeAddressList(rowNum, rowNum, 0, 0));
dv.EmptyCellAllowed = (/*setter*/ allowEmpty);
dv.ErrorStyle = (/*setter*/ errorStyle);
dv.CreateErrorBox("Invalid Input", "Something is wrong - check condition!");
dv.CreatePromptBox("Validated Cell", "Allowable values have been restricted");
dv.ShowPromptBox = (/*setter*/ inputBox);
dv.ShowErrorBox = (/*setter*/ errorBox);
dv.SuppressDropDownArrow = (/*setter*/ suppressDropDown);
_sheet.AddValidationData(dv);
WriteDataValidationSettings(_sheet, _style_1, _style_2, ruleDescr, allowEmpty,
inputBox, errorBox);
if (_cellStyle != null)
{
IRow row = _sheet.GetRow(_sheet.PhysicalNumberOfRows - 1);
ICell cell = row.CreateCell(0);
cell.CellStyle = (/*setter*/ _cellStyle);
}
WriteOtherSettings(_sheet, _style_1, promptDescr);
}
static void AddValidations(ISheet sheet, ExcelVersion version, params DataValidation[] validations)
{
IDataValidationHelper helper = sheet.GetDataValidationHelper();
foreach (DataValidation validation in validations)
{
if ((validation.List == null || validation.List.Count == 0) && validation.Name == null)
{
throw new InvalidOperationException("Validation is invalid");
}
IDataValidationConstraint constraint = validation.Name != null?
helper.CreateFormulaListConstraint(validation.Name) :
helper.CreateExplicitListConstraint(validation.List.ToArray());
var range = new CellRangeAddressList(
validation.Range.RowStart ?? 0,
validation.Range.RowEnd ?? ExcelHelper.GetRowMax(version) - 1,
validation.Range.ColumnStart ?? 0,
validation.Range.ColumnEnd ?? ExcelHelper.GetColumnMax(version) - 1);
IDataValidation dataValidation = helper.CreateValidation(constraint, range);
sheet.AddValidationData(dataValidation);
}
}
private void 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);
}
// <summary>
/// The add validation.
/// </summary>
/// <param name="sheet">
/// 要加入列表的sheet
/// </param>
/// <param name="itemSheet">
/// 选项 sheet.
/// </param>
/// <param name="headerCell">
/// 标题单元格
/// </param>
/// <param name="items">
/// 列表项
/// </param>
private static void AddValidation(ISheet sheet, ISheet itemSheet, ICell headerCell, List <string> items)
{
// 新建行
var row = itemSheet.CreateRow(itemSheet.PhysicalNumberOfRows);
// 新行中写入选项
for (int i = 0; i < items.Count; i++)
{
var cell = row.CreateCell(i);
cell.SetCellValue(items[i]);
}
// 要加下拉列表的范围
var addressList = new CellRangeAddressList(
headerCell.RowIndex + 1,
65535,
headerCell.ColumnIndex,
headerCell.ColumnIndex);
var dvHelper = sheet.GetDataValidationHelper();
// 格式 Sheet2!$A$1:$E$1
var dvConstraint = dvHelper.CreateFormulaListConstraint(
$"{itemSheet.SheetName}!$A${row.RowNum + 1}:${(items.Count)}${row.RowNum + 1}");
var validation = dvHelper.CreateValidation(dvConstraint, addressList);
// 强制必须填下拉列表给出的值
// validation.ShowErrorBox = true;
sheet.AddValidationData(validation);
}
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 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);
}
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);
}
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();
}
}
}
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="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 TestAddToExistingSheet()
{
// dvEmpty.xls is a simple one sheet workbook. With a DataValidations header record but no
// DataValidations. It's important that the example has one SHEETPROTECTION record.
// Such a workbook can be Created in Excel (2007) by Adding datavalidation for one cell
// and then deleting the row that Contains the cell.
IWorkbook wb = HSSFTestDataSamples.OpenSampleWorkbook("dvEmpty.xls");
int dvRow = 0;
ISheet sheet = wb.GetSheetAt(0);
IDataValidationHelper dataValidationHelper = sheet.GetDataValidationHelper();
IDataValidationConstraint dc = dataValidationHelper.CreateintConstraint(OperatorType.EQUAL, "42", null);
IDataValidation dv = dataValidationHelper.CreateValidation(dc, new CellRangeAddressList(dvRow, dvRow, 0, 0));
dv.EmptyCellAllowed = (/*setter*/ false);
dv.ErrorStyle = (/*setter*/ ERRORSTYLE.STOP);
dv.ShowPromptBox = (/*setter*/ true);
dv.CreateErrorBox("Xxx", "Yyy");
dv.SuppressDropDownArrow = (/*setter*/ true);
sheet.AddValidationData(dv);
MemoryStream baos = new MemoryStream();
try
{
wb.Write(baos);
}
catch (IOException e)
{
throw new RuntimeException(e);
}
byte[] wbData = baos.ToArray();
byte[] dvHeaderRecStart = { (byte)0xB2, 0x01, 0x12, 0x00, };
int dvHeaderOffset = FindIndex(wbData, dvHeaderRecStart);
Assert.IsTrue(dvHeaderOffset > 0);
int nextRecIndex = dvHeaderOffset + 22;
int nextSid
= ((wbData[nextRecIndex + 0] << 0) & 0x00FF)
+ ((wbData[nextRecIndex + 1] << 8) & 0xFF00)
;
// nextSid should be for a DVRecord. If anything comes between the DV header record
// and the DV records, Excel will not be able to open the workbook without error.
if (nextSid == 0x0867)
{
throw new AssertionException("Identified bug 45519");
}
Assert.AreEqual(DVRecord.sid, nextSid);
}
/// <summary>设置列下拉列表</summary>
public static void SetColumnDropdownList(this ISheet sheet, string[] values, int startRow, int column)
{
var regions = new CellRangeAddressList(startRow, 65535, column, column);
var helper = sheet.GetDataValidationHelper();
var constraint = helper.CreateExplicitListConstraint(values);
var validation = helper.CreateValidation(constraint, regions);
validation.CreateErrorBox("输入不合法", "请输入下拉列表中的值。");
validation.ShowPromptBox = true;
sheet.AddValidationData(validation);
}
public IActionResult ExportToExcel()
{
//创建EXCEL工作薄
IWorkbook workBook = new XSSFWorkbook();
//创建sheet文件表
ISheet sheet = workBook.CreateSheet("上下料点信息");
var expDir = string.Format("{0}Export\\{1}", System.AppDomain.CurrentDomain.BaseDirectory,
DateTime.Now.ToString("yyyyMM"));
if (!Directory.Exists(expDir))
{
Directory.CreateDirectory(expDir);
}
string filePath = string.Format("{0}\\CD{1}.xlsx", expDir, DateTime.Now.ToString("yyyyMMddHHmmss"));
#region 创建Excel表头
//创建表头
IRow header = sheet.CreateRow(0);
ICell cell = header.CreateCell(0);
cell.SetCellValue("料点编号");
cell = header.CreateCell(1);
cell.SetCellValue("料点名称");
cell = header.CreateCell(2);
cell.SetCellValue("仓库编号");
cell = header.CreateCell(3);
cell.SetCellValue("巷道编号");
cell = header.CreateCell(4);
cell.SetCellValue("料点类型");
ISheet sheet1 = workBook.GetSheetAt(0); //获得第一个工作表
CellRangeAddressList regions = new CellRangeAddressList(1, 65535, 4, 4); //设定位置 行起，行止,列起,列终
XSSFDataValidationHelper helper = new XSSFDataValidationHelper((XSSFSheet)sheet1); //获得一个数据验证Helper
IDataValidation validation = helper.CreateValidation(helper.CreateExplicitListConstraint(new string[] { "In", "Out", "InOut", "OutBack" }), regions); //创建一个特定约束范围内的公式列表约束（即第一节里说的"自定义"方式）
validation.CreateErrorBox("错误", "请按右侧下拉箭头选择!"); //不符合约束时的提示
validation.ShowErrorBox = true; //显示上面提示 = True
sheet1.AddValidationData(validation); //添加进去
sheet1.ForceFormulaRecalculation = true;
#endregion
//工作流写入，通过流的方式进行创建生成文件
using (MemoryStream stream = new MemoryStream())
{
workBook.Write(stream);
byte[] buffer = stream.ToArray();
return(File(buffer, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", string.Format("上下料点信息表_{0}.xlsx", DateTime.Now.ToString("yyyyMMddHHmmss"))));
}
}
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));
}
/// <summary>
/// 添加下拉框
/// </summary>
/// <param name="exSheet">Excel表单对象</param>
/// <param name="field">字段规则</param>
/// <param name="rowIndex">起始行号</param>
/// <param name="rowSpan">结束行号</param>
public static void AddDropDownList(ISheet exSheet, string[] values, int colIndex, int rowIndex, int colSpan = 1, int rowSpan = 1)
{
if (values.Length > 0)
{
IDataValidationHelper helper = exSheet.GetDataValidationHelper();
IDataValidationConstraint dvconstraint = helper.CreateExplicitListConstraint(values);
CellRangeAddressList rangeList = new CellRangeAddressList(rowIndex, rowIndex + rowSpan - 1, colIndex, colIndex + colSpan - 1);
//DVConstraint constraint = DVConstraint.CreateExplicitListConstraint(new string[] { "itemA", "itemB", "itemC" });
//exSheet.AddValidationData(new HSSFDataValidation(rangeList, constraint));
exSheet.AddValidationData(helper.CreateValidation(dvconstraint, rangeList));
}
}
public 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));
}
/// <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 void Render(ICell cell)
{
if (ValueList.Length > 0)
{
ISheet sheet = cell.Sheet;
IDataValidationHelper helper = sheet.GetDataValidationHelper();
IDataValidationConstraint dvconstraint = helper.CreateExplicitListConstraint(ValueList);
CellRangeAddressList rangeList = new CellRangeAddressList(FillArea.RowIndex, FillArea.RowIndex + FillArea.RowCount - 1,
FillArea.ColIndex, FillArea.ColIndex + FillArea.ColCount - 1);
//DVConstraint constraint = DVConstraint.CreateExplicitListConstraint(new string[] { "itemA", "itemB", "itemC" });
//exSheet.AddValidationData(new HSSFDataValidation(rangeList, constraint));
sheet.AddValidationData(helper.CreateValidation(dvconstraint, rangeList));
}
}
/// <summary>
/// 建立级联关系
/// </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>
/// 设置单元格为日期
/// </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);
}
/// <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);
}
/// <summary>
/// 设置excel下拉列表
/// </summary>
/// <param name="sheet">excel工作表</param>
/// <param name="sheetName">下拉列表使用数据的sheet的名字</param>
/// <param name="rangeName">下拉列表使用数据的域</param>
/// <param name="names">待处理的姓名字符串</param>
/// <param name="count">数据表中数据行数，ref引用格式</param>
/// <param name="Row">所要设置的下拉列表所在行</param>
/// <param name="Col">所要设置的下拉列表所在列</param>
public static void SetDropDownList(ISheet sheet, string sheetName, string rangeName, string names, ref int count, int Row, int Col)
{
ISheet dataSheet = null;
IRow row = null;
ICell cell = null;
IName name = null;
IDataValidationHelper dvHelper = null;
IDataValidationConstraint dvConstraint = null;
IDataValidation validation = null;
CellRangeAddressList addressList = null;
dataSheet = sheet.Workbook.GetSheet(sheetName); //获取存储下拉数据的sheet
if (dataSheet == null) //若不存在，则创建
{
dataSheet = sheet.Workbook.CreateSheet(sheetName);
}
//string[] list = new string[] { "123", "456", "789" };
string[] list = BuildDropData(names); //获取下拉数据
row = dataSheet.CreateRow(count++);
for (int i = 0; i < list.Count(); i++) //将数据写入sheet中
{
cell = row.CreateCell(i);
cell.SetCellValue(list[i]);
}
//生成一个列表引用区域,并唯一标识它
name = sheet.Workbook.CreateName();
name.RefersToFormula = string.Format("'{0}'!$A${1}:${2}${3}", sheetName, count, IndexToColumn(list.Count()), count);
name.NameName = rangeName.ToUpper() + Row.ToString();
addressList = new CellRangeAddressList(Row, Row, Col, Col); //设置生成下拉框的行和列
dvHelper = sheet.GetDataValidationHelper();
dvConstraint = dvHelper.CreateFormulaListConstraint(rangeName.ToUpper() + Row.ToString());
validation = dvHelper.CreateValidation(dvConstraint, addressList); //绑定下拉框和作用区域
sheet.AddValidationData(validation);
IWorkbook wb = sheet.Workbook;
wb.SetSheetHidden(wb.GetSheetIndex(dataSheet), 1); //隐藏数据sheet
}
/// <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); //添加进去
}
private string d2003()
{
string fileName = "G:/" + DateTime.Now.Ticks.ToString() + ".xls";
HSSFWorkbook wk = new HSSFWorkbook();
ISheet sheet = wk.CreateSheet();
CellRangeAddressList regions = new CellRangeAddressList(0, 100, 2, 2);
DVConstraint constraint = DVConstraint.CreateExplicitListConstraint(new string[] { "itemA", "itemB", "itemC" });
HSSFDataValidation dataValidate = new HSSFDataValidation(regions, constraint);
sheet.AddValidationData(dataValidate);
using (FileStream fs = File.OpenWrite(fileName))
{
wk.Write(fs);
}
return(fileName);
}
private void CopyRowAreaApplyDetail(HSSFWorkbook workbook, ISheet sourceWorksheet, ISheet destinationWorksheet, int sourceRowNum, int destinationRowNum, int rowNum)
{
var getLastRowNum = destinationWorksheet.LastRowNum;
for (int i = 0; i < rowNum; i++)
{
CopyRow(workbook, sourceWorksheet, destinationWorksheet, sourceRowNum + i, destinationRowNum + i);
}
var markNumConstraint = DVConstraint.CreateNumericConstraint(0x02, 0x06, "0", null);
var markNumColumn_4 = new CellRangeAddressList(getLastRowNum, getLastRowNum + rowNum, 4, 4);
var markdvNum_4 = new HSSFDataValidation(markNumColumn_4, markNumConstraint);
destinationWorksheet.AddValidationData(markdvNum_4);
}
private void CopyRowAreaApply(HSSFWorkbook workbook, ISheet sourceWorksheet, ISheet destinationWorksheet, int sourceRowNum, int destinationRowNum, int rowNum)
{
var getLastRowNum = destinationWorksheet.LastRowNum;
for (int i = 0; i < rowNum; i++)
{
CopyRow(workbook, sourceWorksheet, destinationWorksheet, sourceRowNum + i, destinationRowNum + i);
}
var markListConstraint = DVConstraint.CreateExplicitListConstraint(new string[] { "氣體", "液體", "固體" });
var markNumConstraint = DVConstraint.CreateNumericConstraint(0x02, 0x06, "0", null);
var markUnitLongConstraint = DVConstraint.CreateExplicitListConstraint(new string[] { "kcal/m³", "kcal/L", "kcal/kg" });
var markUnitShortConstraint = DVConstraint.CreateExplicitListConstraint(new string[] { "m³", "kL", "kg" });
var markColumn = new CellRangeAddressList(getLastRowNum, getLastRowNum + rowNum, 3, 3);
var markdv = new HSSFDataValidation(markColumn, markListConstraint);
var markULColumn = new CellRangeAddressList(getLastRowNum, getLastRowNum + rowNum, 6, 6);
var markULdv = new HSSFDataValidation(markULColumn, markUnitLongConstraint);
var markSLColumn = new CellRangeAddressList(getLastRowNum, getLastRowNum + rowNum, 8, 8);
var markSLdv = new HSSFDataValidation(markSLColumn, markUnitShortConstraint);
var markNumColumn_5 = new CellRangeAddressList(getLastRowNum, getLastRowNum + rowNum, 5, 5);
var markdvNum_5 = new HSSFDataValidation(markNumColumn_5, markNumConstraint);
var markNumColumn_7 = new CellRangeAddressList(getLastRowNum, getLastRowNum + rowNum, 7, 7);
var markdvNum_7 = new HSSFDataValidation(markNumColumn_7, markNumConstraint);
var markNumColumn_9 = new CellRangeAddressList(getLastRowNum, getLastRowNum, 9, 9);
var markdvNum_9 = new HSSFDataValidation(markNumColumn_9, markNumConstraint);
var markNumColumn_10 = new CellRangeAddressList(getLastRowNum, getLastRowNum, 10, 10);
var markdvNum_10 = new HSSFDataValidation(markNumColumn_10, markNumConstraint);
destinationWorksheet.AddValidationData(markdv);
destinationWorksheet.AddValidationData(markULdv);
destinationWorksheet.AddValidationData(markSLdv);
destinationWorksheet.AddValidationData(markdvNum_5);
destinationWorksheet.AddValidationData(markdvNum_7);
destinationWorksheet.AddValidationData(markdvNum_9);
destinationWorksheet.AddValidationData(markdvNum_10);
}
private static void buildTemplateSheet(IWorkbook workbook, ISheet sheet, ISheet sheetDS, List<WFActivityField> fields, ref int dsStartIndex, int sheetIndex)
{
if (fields != null)
{
IRow rowColumn = sheet.CreateRow(0);
for (int i = 0; i < fields.Count; i++)
{
ICell cell = rowColumn.CreateCell(i);
string colName = String.IsNullOrWhiteSpace(fields[i].DisplayName) ? fields[i].FieldName : fields[i].DisplayName;
cell.SetCellValue(colName);
ICellStyle cellStyle = workbook.CreateCellStyle();
if (fields[i].IsRequired)
{
cellStyle.FillForegroundColor = IndexedColors.Yellow.Index;
}
else
{
cellStyle.FillForegroundColor = IndexedColors.Grey25Percent.Index;
}
cellStyle.FillPattern = FillPattern.SolidForeground;
cell.CellStyle = cellStyle;
sheet.AutoSizeColumn(i);
switch (fields[i].DataType)
{
case FieldInfo.DATATYPE_LIST:
case FieldInfo.DATATYPE_LIST_SQL:
string lstFormulaName = fields[i].FieldName + "fn";
int dsEndIndex = BuildDataSource(fields[i], sheetDS, dsStartIndex);
if (dsEndIndex > dsStartIndex)
{
IName name = sheet.Workbook.CreateName();
name.RefersToFormula = String.Format("'DataSource'!$A${0}:$A${1}", dsStartIndex + 1, dsEndIndex);
name.NameName = lstFormulaName;
name.SheetIndex = sheetIndex;
CellRangeAddressList addressList = new CellRangeAddressList(1, 1, i, i);
IDataValidationHelper dvHelper = sheet.GetDataValidationHelper();
IDataValidationConstraint dvConstraint = dvHelper.CreateFormulaListConstraint(lstFormulaName);
IDataValidation validation = dvHelper.CreateValidation(dvConstraint, addressList);
sheet.AddValidationData(validation);
dsStartIndex = dsEndIndex;
}
break;
}
}
}
}
private void PutNamesWithValidations(ISheet ws, ICellStyle cs1, ICellStyle cs2, ICellStyle cs3, params string[][] names)
{
var rn0 = ws.PhysicalNumberOfRows;
var rn = rn0;
for (var i = 0; i < names.Length; ++i)
{
var rnc = rn++;
var r = ws.CreateRow(rnc);
if (1 < names[i].Length)
{
r.RowStyle = cs3;
var vh = ws.GetDataValidationHelper();
var vl = names[i].Skip(1).ToList();
vl.Sort();
var vd = vh.CreateValidation(
vh.CreateExplicitListConstraint(vl.ToArray()),
new CellRangeAddressList(rnc, rnc, 1, _excelVer.LastColumnIndex)
);
vd.ShowErrorBox = false;
vd.ShowPromptBox = true;
ws.AddValidationData(vd);
}
var c = r.CreateCell(0);
c.SetCellValue(names[i][0]);
if (0 == i)
{
c.CellStyle = cs1;
r.RowStyle = cs1;
}
else
{
c.CellStyle = cs2;
}
}
if (1 < names.Length) ws.GroupRow(rn0 + 1, rn - 1);
//ws.SetRowGroupCollapsed(rn0 + 1, true);
ws.AddMergedRegion(new CellRangeAddress(rn0, rn0, 0, _excelVer.LastColumnIndex));
}
/// <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;
}
