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));
}
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>
/// 设置下拉选项
/// </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);
}
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 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 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);
}
//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="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);
}
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 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()");
}
}
/// <summary>
/// 设置有效性
/// </summary>
/// <param name="colName">列名</param>
/// <param name="startRowIndex">开始行索引 0起</param>
/// <param name="validSheetName">有效性 目标sheet</param>
/// <param name="validateColName">有效性列名</param>
/// <param name="validateRowStart">有效性开始行 0起</param>
/// <param name="validateRowEnd">有效性结束行 0起</param>
public void SetValidation(string colName, int startRowIndex,
string validSheetName, string validateColName, int validateRowStart, int validateRowEnd)
{
var columnIndex = ColumnNameToIndex(colName);
//设置数据有效性作用域
var regions = new CellRangeAddressList(startRowIndex, 65535, columnIndex, columnIndex);
//设置名称管理器管理数据源范围
var range = workbook.CreateName();
// 验证页, 验证列名 验证开始行
range.RefersToFormula = validSheetName + "!$" + validateColName + "$" + (validateRowStart + 1) +
//验证结束列 //验证结束行
":$" + validateColName + "$" + (validateRowStart + validateRowEnd);
range.NameName = "dicRange" + columnIndex;
//根据名称生成下拉框内容
DVConstraint constraint = DVConstraint.CreateFormulaListConstraint("dicRange" + columnIndex);
//绑定下拉框和作用区域
var dataValidate = new HSSFDataValidation(regions, constraint);
sheet.AddValidationData(dataValidate);
}
/// <summary>
/// Sheet中引用校验引用区域
/// </summary>
/// <param name="workbook"></param>
/// <param name="sheet"></param>
/// <param name="item"></param>
private void SheetAddDataValidation(IWorkbook workbook, ISheet sheet, ColumnProperty item)
{
if (item == null || string.IsNullOrWhiteSpace(item.ConstraintReference)) //如果没有引用区域， 则退出
{
return;
}
CellRangeAddressList regions = new CellRangeAddressList(2, 65535, item.ColumnIndex, item.ColumnIndex);
IDataValidation dataValidate = null;
if (excelVersion == ExcelVersion.XLSX)
{
XSSFSheet xssfSheet = sheet as XSSFSheet;
XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper(xssfSheet);
XSSFDataValidationConstraint dvConstraint = (XSSFDataValidationConstraint)dvHelper.CreateFormulaListConstraint(item.ConstraintReference);
dataValidate = dvHelper.CreateValidation(dvConstraint, regions);
dataValidate.EmptyCellAllowed = true;
dataValidate.ShowErrorBox = true;
dataValidate.CreateErrorBox("系统提示", "请选择指定的" + item.ColumnHeader + "选项");
dataValidate.ShowPromptBox = true;
dataValidate.CreatePromptBox("", item.ColumnHeader);
}
else
{
IName range = workbook.CreateName();
range.RefersToFormula = item.ConstraintReference;
range.NameName = item.ConstraintName;
DVConstraint constraint = DVConstraint.CreateFormulaListConstraint(item.ConstraintName);
dataValidate = new HSSFDataValidation(regions, constraint);
dataValidate.EmptyCellAllowed = true;
dataValidate.ShowErrorBox = true;
dataValidate.CreateErrorBox("系统提示", "请选择指定的" + item.ColumnHeader + "选项");
dataValidate.ShowPromptBox = true;
dataValidate.CreatePromptBox("", item.ColumnHeader);
}
sheet.AddValidationData(dataValidate);
}
public static HSSFDataValidation CreateListConstraint(this HSSFWorkbook book, int columnIndex, IEnumerable <string> values)
{
var 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 = "";
foreach (var value in values)
{
var row = sheet.GetRow(rowIndex) ?? sheet.CreateRow(rowIndex);
row.CreateCell(conColumnIndex).SetCellValue(value);
rowIndex++;
lastValue = value;
}
//如果只有一个可选值，则增加一个相同的选项
if (values.Count() == 1)
{
var row = sheet.GetRow(rowIndex) ?? sheet.CreateRow(rowIndex);
row.CreateCell(conColumnIndex).SetCellValue(lastValue);
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), true);
return(new HSSFDataValidation(cellRegions, constraint));
}
public static void test1()
{
HSSFWorkbook hssfworkbook = new HSSFWorkbook();
HSSFSheet sheet2 = hssfworkbook.CreateSheet("ShtDictionary") as HSSFSheet;
sheet2.CreateRow(0).CreateCell(0).SetCellValue("itemA");
sheet2.CreateRow(1).CreateCell(0).SetCellValue("itemB");
sheet2.CreateRow(2).CreateCell(0).SetCellValue("itemC");
HSSFName range = hssfworkbook.CreateName() as HSSFName;
range.RefersToFormula = "ShtDictionary!$A1:$A3";
range.NameName = "dicRange";
HSSFSheet sheet1 = hssfworkbook.CreateSheet("Sheet1") as HSSFSheet;
CellRangeAddressList regions = new CellRangeAddressList(0, 65535, 0, 0);
DVConstraint constraint = DVConstraint.CreateFormulaListConstraint("dicRange");
HSSFDataValidation dataValidate = new HSSFDataValidation(regions, constraint);
sheet1.AddValidationData(dataValidate);
MemoryStream ms = new MemoryStream();
hssfworkbook.Write(ms);
string workbookFile = @"D:\\wulei1.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 CreateDropDownList(List <Category> lists, string fatherName, string fileName)
{
if (lists.Count > 0)
{
HSSFWorkbook hssfworkbook = new HSSFWorkbook();
HSSFSheet sheet1 = hssfworkbook.CreateSheet("Sheet1") as HSSFSheet;
CellRangeAddressList regions = new CellRangeAddressList(0, 0, 0, 0);
HSSFSheet sheet2 = hssfworkbook.CreateSheet("ShtDictionary") as HSSFSheet;
List <Category> BigCategory = lists.Where(f => f.FatherName == fatherName).ToList();
int row = 0;
int column = 1;
HSSFRow dataRow = sheet2.CreateRow(row++) as HSSFRow;
IName range1 = hssfworkbook.CreateName(); //创建名称
range1.NameName = fatherName; //设置名称
var colName = GetExcelColumnName(BigCategory.Count + 1); //根据序号获取列名，具体代码见下文
range1.RefersToFormula = string.Format("ShtDictionary!$B1:{0}1", colName);
foreach (var Category in BigCategory)
{
dataRow.CreateCell(0).SetCellValue(fatherName);
dataRow.CreateCell(column++).SetCellValue(Category.Name);
HSSFRow childrenrow = sheet2.CreateRow(row++) as HSSFRow;
childrenrow.CreateCell(0).SetCellValue(Category.Name);
List <Category> childrenCategory = lists.Where(f => f.FatherName == Category.Name).ToList();
int childcolumn = 1;
if (childrenCategory.Count > 0)
{
foreach (var ca in childrenCategory)
{
childrenrow.CreateCell(childcolumn++).SetCellValue(ca.Name);
}
range1 = hssfworkbook.CreateName(); //创建名称
range1.NameName = Category.Name; //设置名称
colName = GetExcelColumnName(childrenCategory.Count + 1); //根据序号获取列名，具体代码见下文
range1.RefersToFormula = string.Format("ShtDictionary!$B{1}:{0}{1}", colName, row);
}
}
DVConstraint constraint = DVConstraint.CreateFormulaListConstraint(fatherName);
HSSFDataValidation dataValidate = new HSSFDataValidation(regions, constraint);
sheet1.AddValidationData(dataValidate);
regions = new CellRangeAddressList(0, 0, 1, 1);
DVConstraint constraint1 = DVConstraint.CreateFormulaListConstraint(string.Format("INDIRECT(${0}${1})", "A", 1));
// constraint = DVConstraint.CreateFormulaListConstraint("dicRange");
dataValidate = new HSSFDataValidation(regions, constraint1);
sheet1.AddValidationData(dataValidate);
MemoryStream ms = new MemoryStream();
hssfworkbook.Write(ms);
ms.Flush();
ms.Position = 0;
string workbookFile = fileName;
sheet2 = null;
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();
}
}
/// <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);
}
if (textlist == null || textlist.Length == 0)
{
textlist = new string[] { "" };
}
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);
}
public HSSFWorkbook ExportToExcelInOneSheet <T>(List <T> list)
{
if (SheetNames.Count == 0)
{
SheetNames.Add("CN");
}
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet(SheetNames.FirstOrDefault());
if (defaultRowHeight > 0)
{
sheet.DefaultRowHeight = Convert.ToInt16(defaultRowHeight * 20);
}
if (columnWidth != null && columnWidth.Length > 0)
{
for (int i = 0; i < columnWidth.Length; i++)
{
sheet.SetColumnWidth(i, columnWidth[i] * 256);
}
}
if (list.Count > 0)
{
//填充表头
Type t = list.FirstOrDefault().GetType();
System.Reflection.PropertyInfo[] ps = t.GetProperties();
HSSFRow dataRow = (HSSFRow)sheet.CreateRow(0);
//dataRow.Height = 30 * 20;
ICellStyle notesStyle = workbook.CreateCellStyle();
if (cellStyle != null)
{
notesStyle.CloneStyleFrom(cellStyle);
}
notesStyle.WrapText = true;//设置换行这个要先设置
int headIndex = 0;
//表头命名字典不为空，替换中文表头
if (headDictionary != null && headDictionary.Count > 0)
{
foreach (var dict in headDictionary)
{
sheet.SetDefaultColumnStyle(headIndex, notesStyle);
var cell = dataRow.CreateCell(headIndex);
cell.SetCellValue(dict.Value);
ICellStyle style = workbook.CreateCellStyle();
style.CloneStyleFrom(notesStyle);
style.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.LightBlue.Index;
//style.FillPattern = FillPattern.SolidForeground;
cell.CellStyle = style;
#region 222
var dropDownList = dropDownLists.FirstOrDefault(m => m.Key == dict.Key);
if (!string.IsNullOrWhiteSpace(dropDownList.Key))
{
System.Reflection.PropertyInfo p = ps.FirstOrDefault(m => m.Name == dropDownList.Key);
//创建新标签，并插入下拉框数据集
ISheet dSheet = workbook.CreateSheet(p.Name);
dSheet.CreateRow(0).CreateCell(0).SetCellValue(p.Name + "(请勿修改)");
//var dropdownlist = dropDownLists.First(m => m.Key == p.Name).Value;
for (var i = 0; i < dropDownList.Value.Length; i++)
{
dSheet.CreateRow(i + 1).CreateCell(0).SetCellValue(dropDownList.Value[i].ToString() + "");
}
//将下拉框数据集映射到主表中的单元格
IName range = workbook.CreateName();
range.RefersToFormula = string.Format("{0}!$A$2:$A${1}", p.Name + "", (dropDownList.Value.Length + 1).ToString());
range.NameName = p.Name;
//CellRangeAddressList(首行，尾行，首列，尾列)
CellRangeAddressList regions = new CellRangeAddressList(1, 65535, headIndex, headIndex);
DVConstraint constraint = DVConstraint.CreateFormulaListConstraint(range.NameName);
HSSFDataValidation dataValidate = new HSSFDataValidation(regions, constraint);
sheet.AddValidationData(dataValidate);
}
#endregion
headIndex++;
}
}
else
{
foreach (System.Reflection.PropertyInfo p in ps)
{
sheet.SetDefaultColumnStyle(headIndex, notesStyle);
dataRow.CreateCell(headIndex).SetCellValue(p.Name);
headIndex++;
}
}
#region 待优化的重复代码
//如果下拉框字典不为空，执行插入下拉框的方法
if (dropDownLists != null)
{
//foreach (var dropDownList in dropDownLists)
//{
//System.Reflection.PropertyInfo p = ps.FirstOrDefault(m => m.Name == dropDownList.Key);
////创建新标签，并插入下拉框数据集
//ISheet dSheet = workbook.CreateSheet(p.Name);
//dSheet.CreateRow(0).CreateCell(0).SetCellValue(p.Name + "(请勿修改)");
////var dropdownlist = dropDownLists.First(m => m.Key == p.Name).Value;
//for (var i = 0; i < dropDownList.Value.Length; i++)
//{
// dSheet.CreateRow(i + 1).CreateCell(0).SetCellValue(dropDownList.Value[i]);
//}
////将下拉框数据集映射到主表中的单元格
//IName range = workbook.CreateName();
//range.RefersToFormula = string.Format("{0}!$A$2:$A${1}", p.Name, (dropDownList.Value.Length).ToString());
//range.NameName = "N" + Guid.NewGuid().ToString("N");
////CellRangeAddressList(首行，尾行，首列，尾列)
//CellRangeAddressList regions = new CellRangeAddressList(1, 65535, headIndex, headIndex);
//DVConstraint constraint = DVConstraint.CreateFormulaListConstraint(range.NameName);
//HSSFDataValidation dataValidate = new HSSFDataValidation(regions, constraint);
//sheet.AddValidationData(dataValidate);
//}
}
#endregion
int rowIndex = 1;
foreach (var item in list)
{
dataRow = (HSSFRow)sheet.CreateRow(rowIndex);
int colIndex = 0;
//根据字典中有的key导出数据
if (headDictionary != null && headDictionary.Count > 0)
{
foreach (var dict in headDictionary)
{
var cell = dataRow.CreateCell(colIndex);
cell.SetCellValue((ps.First(m => m.Name == dict.Key).GetValue(item, null) + "").Replace(@"\n", Environment.NewLine));
//ICellStyle style = workbook.CreateCellStyle();
//style.CloneStyleFrom(cellStyle);
//cell.CellStyle = style;
colIndex++;
}
}
else
{
foreach (System.Reflection.PropertyInfo p in ps)
{
try
{
var cell = dataRow.CreateCell(colIndex);
cell.SetCellValue((p.GetValue(item, null) + "").Replace(@"\n", Environment.NewLine));
//ICellStyle style = workbook.CreateCellStyle();
//style.CloneStyleFrom(cellStyle);
//cell.CellStyle = style;
//dataRow.CreateCell(colIndex).SetCellValue();
}
catch
{
}
}
colIndex++;
}
rowIndex++;
}
}
return(workbook);
//保存
//SaveAndResponseFile(workbook, "数据关系视图");
//workbook.Dispose();
}
/// <summary>
/// 设置Excel单元格样式（标题），数据格式
/// </summary>
/// <param name="dateType">数据类型</param>
/// <param name="porpetyIndex">单元格索引</param>
/// <param name="sheet">Sheet页</param>
/// <param name="dataSheet">数据Sheet页</param>
/// <param name="dataStyle">样式</param>
/// <param name="dataFormat">格式</param>
public void SetColumnFormat(ColumnDataType dateType, int porpetyIndex, HSSFSheet sheet, HSSFSheet dataSheet, ICellStyle dataStyle, IDataFormat dataFormat)
{
HSSFDataValidation dataValidation = null;
switch (dateType)
{
case ColumnDataType.Date:
this.MinValueOrLength = DateTime.Parse("1950/01/01").ToString("yyyy/MM/dd");
this.MaxValuseOrLength = string.IsNullOrEmpty(this.MaxValuseOrLength) ? DateTime.MaxValue.ToString("yyyy/MM/dd") : this.MaxValuseOrLength;
dataValidation = new HSSFDataValidation(new CellRangeAddressList(1, 65535, porpetyIndex, porpetyIndex),
DVConstraint.CreateDateConstraint(OperatorType.BETWEEN, this.MinValueOrLength, this.MaxValuseOrLength, "yyyy/MM/dd"));
dataValidation.CreateErrorBox("错误", "请输入日期");
dataValidation.CreatePromptBox("请输入日期格式 yyyy/mm/dd", "在" + MinValueOrLength + " 到 " + MaxValuseOrLength + "之间");
//dataStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("yyyy/MM/dd");
dataStyle.DataFormat = dataFormat.GetFormat("yyyy/mm/dd");
break;
case ColumnDataType.Number:
this.MinValueOrLength = string.IsNullOrEmpty(this.MinValueOrLength) ? long.MinValue.ToString() : this.MinValueOrLength;
this.MaxValuseOrLength = string.IsNullOrEmpty(this.MaxValuseOrLength) ? long.MaxValue.ToString() : this.MaxValuseOrLength;
dataValidation = new HSSFDataValidation(new CellRangeAddressList(1, 65535, porpetyIndex, porpetyIndex),
DVConstraint.CreateNumericConstraint(ValidationType.INTEGER, OperatorType.BETWEEN, this.MinValueOrLength, this.MaxValuseOrLength));
dataValidation.CreateErrorBox("错误", "请输入数字");
dataStyle.DataFormat = dataFormat.GetFormat("0");
dataValidation.CreatePromptBox("请输入数字格式", "在" + MinValueOrLength + " 到 " + MaxValuseOrLength + "之间");
break;
case ColumnDataType.Float:
this.MinValueOrLength = string.IsNullOrEmpty(this.MinValueOrLength) ? decimal.MinValue.ToString() : this.MinValueOrLength;
this.MaxValuseOrLength = string.IsNullOrEmpty(this.MaxValuseOrLength) ? decimal.MaxValue.ToString() : this.MaxValuseOrLength;
dataValidation = new HSSFDataValidation(new CellRangeAddressList(1, 65535, porpetyIndex, porpetyIndex),
DVConstraint.CreateNumericConstraint(ValidationType.DECIMAL, OperatorType.BETWEEN, this.MinValueOrLength, this.MaxValuseOrLength));
dataValidation.CreateErrorBox("错误", "请输入小数");
dataStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00");
dataValidation.CreatePromptBox("请输入小数", "在" + MinValueOrLength + " 到 " + MaxValuseOrLength + "之间");
break;
case ColumnDataType.Bool:
dataValidation = new HSSFDataValidation(new CellRangeAddressList(1, 65535, porpetyIndex, porpetyIndex),
DVConstraint.CreateFormulaListConstraint("Sheet1!$A$1:$B$1"));
dataValidation.CreateErrorBox("错误", "请输入下拉菜单中存在的数据");
sheet.AddValidationData(dataValidation);
dataValidation.CreatePromptBox("下拉菜单", "请输入下拉菜单中存在的数据");
break;
case ColumnDataType.Text:
this.MinValueOrLength = string.IsNullOrEmpty(this.MinValueOrLength) ? "0" : this.MinValueOrLength;
this.MaxValuseOrLength = string.IsNullOrEmpty(this.MaxValuseOrLength) ? "2000" : this.MaxValuseOrLength;
dataValidation = new HSSFDataValidation(new CellRangeAddressList(1, 65535, porpetyIndex, porpetyIndex),
DVConstraint.CreateNumericConstraint(ValidationType.TEXT_LENGTH, OperatorType.BETWEEN, MinValueOrLength, MaxValuseOrLength));
dataValidation.CreateErrorBox("错误", "文本长度不符合要求");
dataStyle.DataFormat = dataFormat.GetFormat("@");
dataValidation.CreatePromptBox("请输入文本", "在" + MinValueOrLength + " 到 " + MaxValuseOrLength + "之间");
break;
case ColumnDataType.ComboBox:
case ColumnDataType.Enum:
int count = this.ListItems.Count() == 0 ? 1 : this.ListItems.Count();
string cloIndex = "";
if (porpetyIndex > 25)
{
cloIndex += Convert.ToChar((int)(Math.Floor(porpetyIndex / 26d)) - 1 + 65);
}
cloIndex += Convert.ToChar(65 + porpetyIndex % 26).ToString();
//定义名称
IName range = sheet.Workbook.CreateName();
range.RefersToFormula = "Sheet2!$" + cloIndex + "$1:$" + cloIndex + "$" + count;
range.NameName = "dicRange" + porpetyIndex;
//dataValidation = new HSSFDataValidation(new CellRangeAddressList(1, 65535, porpetyIndex, porpetyIndex),
// DVConstraint.CreateFormulaListConstraint("Sheet2!$" + cloIndex + "$1:$" + cloIndex + "$" + count));
dataValidation = new HSSFDataValidation(new CellRangeAddressList(1, 65535, porpetyIndex, porpetyIndex),
DVConstraint.CreateFormulaListConstraint("dicRange" + porpetyIndex));
dataValidation.CreateErrorBox("错误", "请输入下拉菜单中存在的数据");
var listItemsTemp = this.ListItems.ToList();
for (int rowIndex = 0; rowIndex < this.ListItems.Count(); rowIndex++)
{
//HSSFRow dataSheetRow = (HSSFRow)dataSheet.CreateRow(rowIndex);
HSSFRow dataSheetRow = (HSSFRow)dataSheet.GetRow(rowIndex);
if (dataSheetRow == null)
{
dataSheetRow = (HSSFRow)dataSheet.CreateRow(rowIndex);
}
//dataSheetRow.CreateCell(porpetyIndex).SetCellValue(this.ListItems.ToList()[rowIndex].Text);
dataSheetRow.CreateCell(porpetyIndex).SetCellValue(listItemsTemp[rowIndex].Text);
dataStyle.DataFormat = dataFormat.GetFormat("@");
dataSheetRow.Cells.Where(x => x.ColumnIndex == porpetyIndex).FirstOrDefault().CellStyle = dataStyle;
}
sheet.AddValidationData(dataValidation);
dataValidation.CreatePromptBox("下拉菜单", "请输入下拉菜单中存在的数据");
break;
default:
dataValidation = new HSSFDataValidation(new CellRangeAddressList(1, 65535, porpetyIndex, porpetyIndex),
DVConstraint.CreateNumericConstraint(ValidationType.TEXT_LENGTH, OperatorType.BETWEEN, this.MinValueOrLength, this.MaxValuseOrLength));
dataValidation.CreateErrorBox("错误", "文本长度不符合要求");
dataStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("@");
break;
}
if (!this.IsNullAble)
{
dataValidation.EmptyCellAllowed = false;
}
sheet.SetDefaultColumnStyle(porpetyIndex, dataStyle);
sheet.AddValidationData(dataValidation);
}
/// <summary>
/// 创建标题行
/// </summary>
/// <param name="sheet"></param>
/// <param name="rowTitle"></param>
private void CreateTitleRow(ISheet sheet, List <ColumnValidItem> rowTitle)
{
var ShtDictionary = hssfworkbook.GetSheet("ShtDictionary") ?? hssfworkbook.CreateSheet("ShtDictionary");
IRow hsTitleRow = sheet.CreateRow(0);
hsTitleRow.HeightInPoints = 20;
//单元格格式是文本与居中
var formatCellStyle1 = hssfworkbook.CreateCellStyle();
formatCellStyle1.Alignment = HorizontalAlignment.Center;
formatCellStyle1.DataFormat = HSSFDataFormat.GetBuiltinFormat("@");
//单元格格式是常规与居中
var formatCellStyle2 = hssfworkbook.CreateCellStyle();
formatCellStyle2.Alignment = HorizontalAlignment.Center;
//日期
var formatCellStyle3 = hssfworkbook.CreateCellStyle();
formatCellStyle3.Alignment = HorizontalAlignment.Center;
var format = hssfworkbook.CreateDataFormat();
formatCellStyle3.DataFormat = format.GetFormat("yyyy-m-d");
var cellStyle1 = hssfworkbook.CreateCellStyle();
cellStyle1.Alignment = HorizontalAlignment.Center;
var font1 = hssfworkbook.CreateFont();
font1.Color = HSSFColor.Red.Index;
font1.FontHeightInPoints = 12;
font1.FontName = "宋体";
cellStyle1.SetFont(font1);
var cellStyle2 = hssfworkbook.CreateCellStyle();
cellStyle2.Alignment = HorizontalAlignment.Center;
var font2 = hssfworkbook.CreateFont();
font2.FontHeightInPoints = 12;
font2.FontName = "宋体";
cellStyle2.SetFont(font2);
ICell cell = null;
ColumnValidItem validItem = null;
for (int i = 0; i < rowTitle.Count; i++)
{
validItem = rowTitle[i];
if (validItem != null)
{
cell = hsTitleRow.CreateCell(i);
cell.SetCellValue(validItem.Name);
//列宽度
sheet.SetColumnWidth(i, validItem.Width == 0 ? 80 * 50 : validItem.Width * 50);
#region 项类型
switch (validItem.ItemType)//是否以红色标记
{
case EnumItemType.MustHave:
case EnumItemType.MustFill:
cell.CellStyle = cellStyle1;
break;
case EnumItemType.SelectFill:
cell.CellStyle = cellStyle2;
break;
}
#endregion
#region 值类型
switch (validItem.ValueType)//值类型
{
case EnumValueType.String:
sheet.SetDefaultColumnStyle(i, formatCellStyle1);
break;
case EnumValueType.Number:
sheet.SetDefaultColumnStyle(i, formatCellStyle2);
break;
case EnumValueType.DateTime:
sheet.SetDefaultColumnStyle(i, formatCellStyle3);
break;
case EnumValueType.List:
int count = this.CreateColumnList(validItem, i, ShtDictionary);
var regions = new CellRangeAddressList(1, 65535, i, i);
var constraint = DVConstraint.CreateFormulaListConstraint(string.Format("ShtDictionary!${0}${1}:${0}${2}", Chr(i), 1, count));
var dataValidate = new HSSFDataValidation(regions, constraint);
((HSSFSheet)sheet).AddValidationData(dataValidate);
break;
}
#endregion
this.ValidAndMessage(validItem, i, sheet);//验证类型
}
}
}
/// <summary>
/// 生成备课资源表
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void button5_Click(object sender, EventArgs e)
{
IWorkbook wb = new HSSFWorkbook();
#region 创建基础数据表
ISheet sh = wb.CreateSheet("基础数据");
List <string> headtitles = new List <string>();
headtitles.Add("学科"); headtitles.Add("年级");
headtitles.Add("学段"); headtitles.Add("册别");
headtitles.Add("教学环节"); headtitles.Add("适用对象");
headtitles.Add("新教学模块"); headtitles.Add("英语教学模块");
headtitles.Add("数学教学模块"); headtitles.Add("语文教学模块");
headtitles.Add("资源权限"); headtitles.Add("资源评级");
headtitles.Add("资源来源"); headtitles.Add("资源推荐");
headtitles.Add("资源类型"); headtitles.Add("版本");
headtitles.Add("教学形式"); headtitles.Add("题型");
headtitles.Add("词库"); headtitles.Add("版权信息");
headtitles.Add("课时"); headtitles.Add("课例类型");//
//-----新增资源大类-------
headtitles.Add("资源大类");
headtitles.Add("语文_001"); headtitles.Add("数学_002"); headtitles.Add("英语_003");
for (int i = 0; i < 500; i++)
{
sh.CreateRow(i);
}
for (int i = 0; i < metadataLists.Count - 1; i++)//前0—21列表
{
List <String> subList = (List <String>)metadataLists[i];
sh.GetRow(0).CreateCell(i).SetCellValue(headtitles[i]);
for (int j = 1; j <= subList.Count; j++)
{
sh.GetRow(j).CreateCell(i).SetCellValue(subList[j - 1]);
}
}
sh.GetRow(0).GetCell(19).SetCellValue("");
sh.GetRow(1).GetCell(19).SetCellValue("");
sh.GetRow(2).GetCell(19).SetCellValue("");
sh.GetRow(3).GetCell(19).SetCellValue("");
sh.GetRow(4).GetCell(19).SetCellValue("");
//List<List<String>> knowledgeLists = (List<List<String>>)metadataLists[22];
List <List <String> > knowledgeLists = (List <List <String> >)metadataLists[23];
for (int i = 0; i < knowledgeLists.Count; i++)
{
List <String> subknowledgeList = (List <String>)knowledgeLists[i];
//sh.GetRow(0).CreateCell(22 + i).SetCellValue(headtitles[22 + i]);
sh.GetRow(0).CreateCell(23 + i).SetCellValue(headtitles[23 + i]);
for (int j = 1; j <= subknowledgeList.Count; j++)
{
//sh.GetRow(j).CreateCell(22 + i).SetCellValue(subknowledgeList[j - 1]);
sh.GetRow(j).CreateCell(23 + i).SetCellValue(subknowledgeList[j - 1]);
}
}
#endregion
#region 创建教材目录表
ISheet sh1 = wb.CreateSheet("教材目录");
for (int i = 0; i < 500; i++)
{
sh1.CreateRow(i);
}
for (int i = 0; i < catologList.Count; i++)
{
sh1.GetRow(i).CreateCell(0).SetCellValue(catologList[i]);
}
//创建通用设置表（适用对象，资源评级，资源评级，资源来源，资源推荐，教学形式）
ISheet sh2 = wb.CreateSheet("通用设置");
IRow r0 = sh2.CreateRow(0);//标题
r0.CreateCell(0).SetCellValue("适用对象"); r0.CreateCell(1).SetCellValue("资源评级");
r0.CreateCell(2).SetCellValue("资源权限"); r0.CreateCell(3).SetCellValue("资源来源");
r0.CreateCell(4).SetCellValue("资源推荐"); r0.CreateCell(5).SetCellValue("教学形式");
IRow r1 = sh2.CreateRow(1); //内容
r1.CreateCell(0).SetCellValue(((List <String>)metadataLists[5])[0]); //适用对象固定内容
r1.CreateCell(1).SetCellValue(((List <String>)metadataLists[11])[0]); //资源评级固定内容
r1.CreateCell(2).SetCellValue(((List <String>)metadataLists[10])[0]); //资源评级固定内容
r1.CreateCell(3).SetCellValue(((List <String>)metadataLists[12])[1]); //资源来源固定内容
r1.CreateCell(4).SetCellValue(((List <String>)metadataLists[13])[0]); //资源推荐固定内容
r1.CreateCell(5).SetCellValue(((List <String>)metadataLists[16])[1]); //教学形式固定内容
CellRangeAddressList region00 = new CellRangeAddressList(1, 1, 0, 0);
CellRangeAddressList region11 = new CellRangeAddressList(1, 1, 1, 1);
CellRangeAddressList region22 = new CellRangeAddressList(1, 1, 2, 2);
CellRangeAddressList region33 = new CellRangeAddressList(1, 1, 3, 3);
CellRangeAddressList region44 = new CellRangeAddressList(1, 1, 4, 4);
CellRangeAddressList region55 = new CellRangeAddressList(1, 1, 5, 5);
DVConstraint constraint00 = DVConstraint.CreateExplicitListConstraint((string[])((List <string>)metadataLists[5]).ToArray());
DVConstraint constraint11 = DVConstraint.CreateExplicitListConstraint((string[])((List <string>)metadataLists[11]).ToArray());
DVConstraint constraint22 = DVConstraint.CreateExplicitListConstraint((string[])((List <string>)metadataLists[10]).ToArray());
DVConstraint constraint33 = DVConstraint.CreateExplicitListConstraint((string[])((List <string>)metadataLists[12]).ToArray());
DVConstraint constraint44 = DVConstraint.CreateExplicitListConstraint((string[])((List <string>)metadataLists[13]).ToArray());
DVConstraint constraint55 = DVConstraint.CreateExplicitListConstraint((string[])((List <string>)metadataLists[16]).ToArray());
HSSFDataValidation dataValidate00 = new HSSFDataValidation(region00, constraint00);
HSSFDataValidation dataValidate11 = new HSSFDataValidation(region11, constraint11);
HSSFDataValidation dataValidate22 = new HSSFDataValidation(region22, constraint22);
HSSFDataValidation dataValidate33 = new HSSFDataValidation(region33, constraint33);
HSSFDataValidation dataValidate44 = new HSSFDataValidation(region44, constraint44);
HSSFDataValidation dataValidate55 = new HSSFDataValidation(region55, constraint55);
sh2.AddValidationData(dataValidate00);
sh2.AddValidationData(dataValidate11);
sh2.AddValidationData(dataValidate22);
sh2.AddValidationData(dataValidate33);
sh2.AddValidationData(dataValidate44);
sh2.AddValidationData(dataValidate55);
#endregion
#region 创建U1表
ISheet sheet = wb.CreateSheet("U1");
IRow row0 = sheet.CreateRow(0);
row0.CreateCell(0).SetCellValue("序号"); row0.CreateCell(1).SetCellValue("显示名称");
row0.CreateCell(2).SetCellValue("内容说明");
row0.CreateCell(3).SetCellValue("资源类型"); //二级关联 14
row0.CreateCell(4).SetCellValue("教材名称"); //bookName
sheet.CreateRow(1).CreateCell(4).SetCellValue(bookName.Substring(0, bookName.LastIndexOf("_"))); //固化教材名称，一行
row0.CreateCell(5).SetCellValue("教材目录"); //cataloglist
row0.CreateCell(6).SetCellValue("教学环节"); //4
row0.CreateCell(7).SetCellValue("教学模块"); //语数英
row0.CreateCell(8).SetCellValue("知识点"); //语数英
row0.CreateCell(9).SetCellValue("关键字"); row0.CreateCell(10).SetCellValue("资源描述");
row0.CreateCell(11).SetCellValue("制作者"); row0.CreateCell(12).SetCellValue("检测者");
row0.CreateCell(13).SetCellValue("上传者"); row0.CreateCell(14).SetCellValue("文件路径");
row0.CreateCell(15).SetCellValue("缩略图路径");
row0.CreateCell(16).SetCellValue("版权信息");//19
//-----------新增“资源大类”20170213
row0.CreateCell(17).SetCellValue("资源大类");
CellRangeAddressList region3 = new CellRangeAddressList(0, 65535, 3, 3);
CellRangeAddressList region5 = new CellRangeAddressList(0, 65535, 5, 5);
CellRangeAddressList region6 = new CellRangeAddressList(0, 65535, 6, 6);
CellRangeAddressList region7 = new CellRangeAddressList(0, 65535, 7, 7);
CellRangeAddressList region8 = new CellRangeAddressList(0, 65535, 8, 8);
CellRangeAddressList region16 = new CellRangeAddressList(0, 65535, 16, 16);
//-----------新增“资源大类”20170213
CellRangeAddressList region17 = new CellRangeAddressList(0, 65535, 17, 17);
IName range3 = wb.CreateName();
int num3 = ((List <string>)metadataLists[14]).Count + 1;
range3.RefersToFormula = "基础数据!$O$2:$O$" + num3.ToString();
range3.NameName = "dicRange3";
DVConstraint constraint3 = DVConstraint.CreateFormulaListConstraint("dicRange3");
IName range5 = wb.CreateName();
if (catologList.Count == 0)
{
MessageBox.Show("请导入教材目录！");
return;
}
else
{
int num5 = catologList.Count;
range5.RefersToFormula = "教材目录!$A$1:$A$" + num5;
range5.NameName = "dicRange5";
DVConstraint constraint5 = DVConstraint.CreateFormulaListConstraint("dicRange5");
DVConstraint constraint6 = DVConstraint.CreateExplicitListConstraint((string[])((List <string>)metadataLists[4]).ToArray());
DVConstraint constraint7;
DVConstraint constraint8;
String[] test = bookName.Substring(bookName.LastIndexOf("_")).Split('-');
if (test[1] == "1")
{
IName range8 = wb.CreateName();
int num8 = ((string[])((List <List <String> >)metadataLists[23])[0].ToArray()).Length + 1;
range8.RefersToFormula = "基础数据!$X$2:$X$" + num8.ToString();
range8.NameName = "dicRange8";
constraint7 = DVConstraint.CreateExplicitListConstraint((string[])((List <string>)metadataLists[9]).ToArray());
constraint8 = DVConstraint.CreateFormulaListConstraint("dicRange8");
}
else if (test[1] == "2")
{
IName range8 = wb.CreateName();
int num8 = ((string[])((List <List <String> >)metadataLists[23])[1].ToArray()).Length + 1;
range8.RefersToFormula = "基础数据!$Y$2:$Y$" + num8.ToString();
range8.NameName = "dicRange8";
constraint7 = DVConstraint.CreateExplicitListConstraint(((List <string>)metadataLists[8]).ToArray());
constraint8 = DVConstraint.CreateFormulaListConstraint("dicRange8");
}
else
{
IName range8 = wb.CreateName();
//int num8 = ((string[])((List<List<String>>)metadataLists[22])[2].ToArray()).Length + 1;
int num8 = ((string[])((List <List <String> >)metadataLists[23])[2].ToArray()).Length + 1;
range8.RefersToFormula = "基础数据!$Z$2:$Z$" + num8.ToString();
range8.NameName = "dicRange8";
constraint7 = DVConstraint.CreateExplicitListConstraint((string[])((List <string>)metadataLists[7]).ToArray());
constraint8 = DVConstraint.CreateFormulaListConstraint("dicRange8");
}
//DVConstraint constraint16 = DVConstraint.CreateExplicitListConstraint((string[])((List<string>)metadataLists[19]).ToArray());
DVConstraint constraint16 = DVConstraint.CreateExplicitListConstraint(new string[] { "A", "B", "C", "D" });
DVConstraint constraint17 = DVConstraint.CreateExplicitListConstraint(new string[] { "同步资源", "拓展资源" });
HSSFDataValidation dataValidate3 = new HSSFDataValidation(region3, constraint3);
HSSFDataValidation dataValidate5 = new HSSFDataValidation(region5, constraint5);
HSSFDataValidation dataValidate6 = new HSSFDataValidation(region6, constraint6);
HSSFDataValidation dataValidate7 = new HSSFDataValidation(region7, constraint7);
HSSFDataValidation dataValidate8 = new HSSFDataValidation(region8, constraint8);
HSSFDataValidation dataValidate16 = new HSSFDataValidation(region16, constraint16);
//----------新增“资源大类”20170213
HSSFDataValidation dataValidate17 = new HSSFDataValidation(region17, constraint17);
sheet.AddValidationData(dataValidate3);
sheet.AddValidationData(dataValidate5);
sheet.AddValidationData(dataValidate6);
sheet.AddValidationData(dataValidate7);
sheet.AddValidationData(dataValidate8);
sheet.AddValidationData(dataValidate16);
sheet.AddValidationData(dataValidate17);
#endregion
//表格样式
#region
sh2.SetColumnWidth(0, 10 * 256);
sh2.SetColumnWidth(1, 10 * 256);
sh2.SetColumnWidth(2, 20 * 256);
sh2.SetColumnWidth(3, 20 * 256);
sh2.SetColumnWidth(4, 10 * 256);
sh2.SetColumnWidth(5, 15 * 256);
sheet.GetRow(0).Height = 30 * 20;
sheet.SetColumnWidth(0, 10 * 256);
sheet.SetColumnWidth(1, 20 * 256);
sheet.SetColumnWidth(2, 40 * 256);
sheet.SetColumnWidth(3, 20 * 256);
sheet.SetColumnWidth(4, 40 * 256);
sheet.SetColumnWidth(5, 40 * 256);
sheet.SetColumnWidth(6, 10 * 256);
sheet.SetColumnWidth(7, 15 * 256);
sheet.SetColumnWidth(8, 40 * 256);
sheet.SetColumnWidth(9, 40 * 256);
sheet.SetColumnWidth(10, 20 * 256);
sheet.SetColumnWidth(11, 20 * 256);
sheet.SetColumnWidth(12, 10 * 256);
sheet.SetColumnWidth(13, 10 * 256);
sheet.SetColumnWidth(14, 40 * 256);
sheet.SetColumnWidth(15, 40 * 256);
sheet.SetColumnWidth(16, 10 * 256);
sheet.SetColumnWidth(17, 10 * 256);
#endregion
String fileName = bookName.Substring(0, bookName.IndexOf("_")) + "_资源集模板.xls";
using (FileStream fs = new FileStream(fileName, FileMode.OpenOrCreate, FileAccess.ReadWrite))
{
wb.Write(fs);
fs.Close();
MessageBox.Show("创建成功！");
}
}
}
public static void test4()
{
HSSFWorkbook workbook = new HSSFWorkbook();
ISheet sheet = workbook.CreateSheet("sheet1");
IRow row = sheet.CreateRow(0);
row.CreateCell(0).SetCellValue("姓名");
row.CreateCell(1).SetCellValue("身份证号");
row.CreateCell(2).SetCellValue("年级");
row.CreateCell(3).SetCellValue("班级");
row.CreateCell(4).SetCellValue("课程");
row.CreateCell(5).SetCellValue("角色（班主任、单科老师）");
IRow row1 = sheet.CreateRow(1);
row1.CreateCell(0).SetCellValue("张峰");
row1.CreateCell(1).SetCellValue("1111111111111");
row1.CreateCell(2).SetCellValue("小学六年级");
row1.CreateCell(3).SetCellValue("4班");
row1.CreateCell(4).SetCellValue("语文");
row1.CreateCell(5).SetCellValue("单科老师");
var ic = workbook.CreateCellStyle();
ic.DataFormat = HSSFDataFormat.GetBuiltinFormat("@");
sheet.SetDefaultColumnStyle(1, ic);
sheet.SetColumnWidth(1, 5000);
sheet.SetColumnWidth(2, 4000);
sheet.SetColumnWidth(3, 4000);
sheet.SetColumnWidth(5, 24000);
List <CourseCodeInfo> list = new List <CourseCodeInfo>();
list.Add(new CourseCodeInfo()
{
Name = "语文"
});
list.Add(new CourseCodeInfo()
{
Name = "数学"
});
list.Add(new CourseCodeInfo()
{
Name = "英议事"
});
var CourseSheetName = "Course";
var RangeName = "dicRange";
ISheet CourseSheet = workbook.CreateSheet(CourseSheetName);
CourseSheet.CreateRow(0).CreateCell(0).SetCellValue("课程列表（用于生成课程下拉框，请勿修改）");
for (var i = 1; i < list.Count; i++)
{
CourseSheet.CreateRow(i).CreateCell(0).SetCellValue(list[i - 1].Name);
}
IName range = workbook.CreateName();
range.RefersToFormula = string.Format("{0}!$A$2:$A${1}", CourseSheetName, list.Count.ToString());
range.NameName = RangeName;
//
CellRangeAddressList regions = new CellRangeAddressList(1, 65535, 4, 4);
DVConstraint constraint = DVConstraint.CreateFormulaListConstraint(RangeName);
HSSFDataValidation dataValidate = new HSSFDataValidation(regions, constraint);
sheet.AddValidationData(dataValidate);
MemoryStream ms = new MemoryStream();
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
string workbookFile = @"D:\\777.xls";
// sheet2 = null;
workbook = 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 test3()
{
HSSFWorkbook hssfworkbook = new HSSFWorkbook();
HSSFSheet sheet2 = hssfworkbook.CreateSheet("ShtDictionary") as HSSFSheet;
HSSFRow dataRow = sheet2.CreateRow(0) as HSSFRow;
dataRow.CreateCell(0).SetCellValue("省份");
dataRow.CreateCell(1).SetCellValue("湖北");
dataRow.CreateCell(2).SetCellValue("湖南");
dataRow.CreateCell(3).SetCellValue("广东");
dataRow = sheet2.CreateRow(1) as HSSFRow;
dataRow.CreateCell(0).SetCellValue("湖北");
dataRow.CreateCell(1).SetCellValue("汉口");
dataRow.CreateCell(2).SetCellValue("汉阳");
dataRow.CreateCell(3).SetCellValue("武昌");
dataRow = sheet2.CreateRow(2) as HSSFRow;
dataRow.CreateCell(0).SetCellValue("湖南");
dataRow.CreateCell(1).SetCellValue("长沙");
dataRow.CreateCell(2).SetCellValue("岳阳");
dataRow.CreateCell(3).SetCellValue("长沙南");
dataRow = sheet2.CreateRow(3) as HSSFRow;
dataRow.CreateCell(0).SetCellValue("广东");
dataRow.CreateCell(1).SetCellValue("深圳");
dataRow.CreateCell(2).SetCellValue("广州");
dataRow.CreateCell(3).SetCellValue("广州东");
// sheet2.IsRightToLeft = false;
IName range1 = hssfworkbook.CreateName(); //创建名称
range1.NameName = "省份"; //设置名称
// var colName = GetExcelColumnName(colIndex);//根据序号获取列名，具体代码见下文
range1.RefersToFormula = "ShtDictionary!$B1:D1";
range1 = hssfworkbook.CreateName(); //创建名称
range1.NameName = "湖北"; //设置名称
// var colName = GetExcelColumnName(colIndex);//根据序号获取列名，具体代码见下文
range1.RefersToFormula = "ShtDictionary!$B2:D2";
range1 = hssfworkbook.CreateName(); //创建名称
range1.NameName = "湖南"; //设置名称
// var colName = GetExcelColumnName(colIndex);//根据序号获取列名，具体代码见下文
range1.RefersToFormula = "ShtDictionary!$B3:D3";
range1 = hssfworkbook.CreateName(); //创建名称
range1.NameName = "广东"; //设置名称
// var colName = GetExcelColumnName(colIndex);//根据序号获取列名，具体代码见下文
range1.RefersToFormula = "ShtDictionary!$B4:D4";
//range1.RefersToFormula = string.Format("{0}!${3}${2}:${3}${1}",
// "ShtDictionary",
// "4",
// 2,
// "A");
// var colName = GetExcelColumnName(1);
HSSFName range = hssfworkbook.CreateName() as HSSFName;
// range.RefersToFormula = "ShtDictionary!$B1:D1";
range.RefersToFormula = "ShtDictionary!$A1:A4";
range.NameName = "dicRange";
HSSFSheet sheet1 = hssfworkbook.CreateSheet("Sheet1") as HSSFSheet;
CellRangeAddressList regions = new CellRangeAddressList(0, 0, 0, 0);
DVConstraint constraint = DVConstraint.CreateFormulaListConstraint("dicRange");
HSSFDataValidation dataValidate = new HSSFDataValidation(regions, constraint);
sheet1.AddValidationData(dataValidate);
regions = new CellRangeAddressList(0, 0, 1, 1);
DVConstraint constraint1 = DVConstraint.CreateFormulaListConstraint(string.Format("INDIRECT(${0}${1})", "A", 1));
// constraint = DVConstraint.CreateFormulaListConstraint("dicRange");
dataValidate = new HSSFDataValidation(regions, constraint1);
sheet1.AddValidationData(dataValidate);
//regions = new CellRangeAddressList(2, 2, 0, 0);
// constraint1 = DVConstraint.CreateFormulaListConstraint(string.Format("INDIRECT(${0}${1})", "C", 2));
//// constraint = DVConstraint.CreateFormulaListConstraint("dicRange");
//dataValidate = new HSSFDataValidation(regions, constraint1);
//sheet1.AddValidationData(dataValidate);
MemoryStream ms = new MemoryStream();
hssfworkbook.Write(ms);
ms.Flush();
ms.Position = 0;
string workbookFile = @"D:\\8888.xls";
sheet2 = null;
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();
}
protected override void SetExcelColumns(Sheet worksheet, DataFeedTemplate data)
{
ushort colindex = 0;
ushort colindex2 = 0;
HSSFSheet sheet = ((HSSFSheet)worksheet);
CellStyle style = sheet.Workbook.CreateCellStyle();
style.DataFormat = HSSFDataFormat.GetBuiltinFormat("@");
for (int i = 0; i < data.BasicColumns.Count; i++)
{
var item = data.BasicColumns[i];
if (item.Type == DataType.LIST)
{
if (item.Type == DataType.LIST && item.List != null && item.List.Count > 0)
{
CellRangeAddressList regions = new CellRangeAddressList(HeaderNameRowIndex + 1, 65535, colindex2, colindex2);
Name range = WorkBook.CreateName();
HSSFSheet sheetDetails = (HSSFSheet)WorkBook.GetSheet(DETAILSHEET);
string celName = GetExcelColumnIndex(colindex);
range.RefersToFormula = DETAILSHEET + "!$" + celName + "$2" + ":$" + celName + "$" + (item.List.Count + 1);
range.NameName = string.Format("sheet{0}ranges{1}",
worksheet.Workbook.GetSheetIndex(worksheet),
item.Number);
DVConstraint constraint = DVConstraint.CreateFormulaListConstraint(range.NameName);
HSSFDataValidation dataValidate = new HSSFDataValidation(regions, constraint);
sheet.AddValidationData(dataValidate);
sheet.SetDefaultColumnStyle(colindex2, style);
}
colindex++;
}
this.SetExcelColumns(worksheet, item.Width * 600, colindex2);
colindex2++;
}
if (data.Properties != null && data.Properties.Count > 0)
{
for (int i = 0; i < data.Properties.Count; i++)
{
var item = data.Properties[i];
if (item.Type == DataType.LIST && item.List != null && item.List.Count > 0)
{
CellRangeAddressList regions = new CellRangeAddressList(HeaderNameRowIndex + 1, 65535, colindex2, colindex2);
Name range = WorkBook.CreateName();
HSSFSheet sheetDetails = (HSSFSheet)WorkBook.GetSheet(DETAILSHEET);
string celName = GetExcelColumnIndex(colindex);
range.RefersToFormula = DETAILSHEET + "!$" + celName + "$2" + ":$" + celName + "$" + (item.List.Count + 1);
range.NameName = BuildRangeName(
item.Number,
worksheet.Workbook.GetSheetIndex(worksheet)
);
DVConstraint constraint = DVConstraint.CreateFormulaListConstraint(range.NameName);
HSSFDataValidation dataValidate = new HSSFDataValidation(regions, constraint);
sheet.AddValidationData(dataValidate);
sheet.SetDefaultColumnStyle(colindex2, style);
}
this.SetExcelColumns(worksheet, item.Width * 600, colindex2);
colindex++;
colindex2++;
}
}
}
