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 void TestGetDataValidationsFormula()
{
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.CreateSheet() as HSSFSheet;
List <IDataValidation> list = sheet.GetDataValidations();
Assert.AreEqual(0, list.Count);
IDataValidationHelper dataValidationHelper = sheet.GetDataValidationHelper();
IDataValidationConstraint constraint = dataValidationHelper.CreateCustomConstraint("A2:A3");
CellRangeAddressList AddressList = new CellRangeAddressList(0, 0, 0, 0);
IDataValidation validation = dataValidationHelper.CreateValidation(constraint, AddressList);
sheet.AddValidationData(validation);
list = sheet.GetDataValidations(); // <-- works
Assert.AreEqual(1, list.Count);
HSSFDataValidation dv = list[(0)] as HSSFDataValidation;
DVConstraint c = dv.Constraint;
Assert.AreEqual(ValidationType.FORMULA, c.GetValidationType());
Assert.AreEqual("A2:A3", c.Formula1);
Assert.AreEqual(null, c.Formula2);
Assert.AreEqual(double.NaN, c.Value1);
Assert.AreEqual(double.NaN, c.Value2);
}
public void TestGetDataValidationsDate()
{
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.CreateSheet() as HSSFSheet;
List<IDataValidation> list = sheet.GetDataValidations();
Assert.AreEqual(0, list.Count);
IDataValidationHelper dataValidationHelper = sheet.GetDataValidationHelper();
IDataValidationConstraint constraint = dataValidationHelper.CreateDateConstraint(OperatorType.EQUAL,
"2014/10/25", null, null);
CellRangeAddressList AddressList = new CellRangeAddressList(0, 0, 0, 0);
IDataValidation validation = dataValidationHelper.CreateValidation(constraint, AddressList);
sheet.AddValidationData(validation);
list = sheet.GetDataValidations(); // <-- works
Assert.AreEqual(1, list.Count);
HSSFDataValidation dv = list[(0)] as HSSFDataValidation;
DVConstraint c = dv.Constraint;
Assert.AreEqual(ValidationType.DATE, c.GetValidationType());
Assert.AreEqual(OperatorType.EQUAL, c.Operator);
Assert.AreEqual(null, c.Formula1);
Assert.AreEqual(null, c.Formula2);
Assert.AreEqual(DateUtil.GetExcelDate(DateUtil.ParseYYYYMMDDDate("2014/10/25")), c.Value1);
Assert.AreEqual(double.NaN, c.Value2);
}
public void TestGetDataValidationsDecimal(){
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.CreateSheet() as HSSFSheet;
List<IDataValidation> list = sheet.GetDataValidations();
Assert.AreEqual(0, list.Count);
IDataValidationHelper dataValidationHelper = sheet.GetDataValidationHelper();
IDataValidationConstraint constraint = dataValidationHelper.CreateDecimalConstraint(OperatorType.BETWEEN, "=A2",
"200");
CellRangeAddressList AddressList = new CellRangeAddressList(0, 0, 0, 0);
IDataValidation validation = dataValidationHelper.CreateValidation(constraint, AddressList);
sheet.AddValidationData(validation);
list = sheet.GetDataValidations(); // <-- works
Assert.AreEqual(1, list.Count);
HSSFDataValidation dv = list[(0)] as HSSFDataValidation;
DVConstraint c = dv.Constraint;
Assert.AreEqual(ValidationType.DECIMAL, c.GetValidationType());
Assert.AreEqual(OperatorType.BETWEEN, c.Operator);
Assert.AreEqual("A2", c.Formula1);
Assert.AreEqual(null, c.Formula2);
Assert.AreEqual(double.NaN, c.Value1);
Assert.AreEqual(200, c.Value2);
}
public void TestGetDataValidationsListExplicit()
{
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.CreateSheet() as HSSFSheet;
List<IDataValidation> list = sheet.GetDataValidations();
Assert.AreEqual(0, list.Count);
IDataValidationHelper dataValidationHelper = sheet.GetDataValidationHelper();
IDataValidationConstraint constraint = dataValidationHelper.CreateExplicitListConstraint(new String[] { "aaa",
"bbb", "ccc" });
CellRangeAddressList AddressList = new CellRangeAddressList(0, 0, 0, 0);
IDataValidation validation = dataValidationHelper.CreateValidation(constraint, AddressList);
validation.SuppressDropDownArrow = (/*setter*/true);
sheet.AddValidationData(validation);
list = sheet.GetDataValidations(); // <-- works
Assert.AreEqual(1, list.Count);
HSSFDataValidation dv = list[(0)] as HSSFDataValidation;
Assert.AreEqual(true, dv.SuppressDropDownArrow);
DVConstraint c = dv.Constraint;
Assert.AreEqual(ValidationType.LIST, c.GetValidationType());
Assert.AreEqual(null, c.Formula1);
Assert.AreEqual(null, c.Formula2);
Assert.AreEqual(double.NaN, c.Value1);
Assert.AreEqual(double.NaN, c.Value2);
String[] values = c.ExplicitListValues;
Assert.AreEqual(3, values.Length);
Assert.AreEqual("aaa", values[0]);
Assert.AreEqual("bbb", values[1]);
Assert.AreEqual("ccc", values[2]);
}
private void AddValidationInternal(int operatorType, String firstFormula,
String secondFormula, HSSFDataValidation.ERRORSTYLE errorStyle, String ruleDescr, String promptDescr,
bool allowEmpty, bool inputBox, bool errorBox, bool suppressDropDown,
String[] explicitListValues)
{
int rowNum = _currentRowIndex++;
DVConstraint dc = CreateConstraint(operatorType, firstFormula, secondFormula, explicitListValues);
HSSFDataValidation dv = new HSSFDataValidation(new CellRangeAddressList(rowNum, rowNum, 0, 0), dc);
dv.EmptyCellAllowed = (allowEmpty);
dv.ErrorStyle = (errorStyle);
dv.CreateErrorBox("Invalid Input", "Something is wrong - Check condition!");
dv.CreatePromptBox("Validated Cell", "Allowable values have been restricted");
dv.ShowPromptBox = (inputBox);
dv.ShowErrorBox = (errorBox);
dv.SuppressDropDownArrow = (suppressDropDown);
_sheet.AddValidationData(dv);
WriteDataValidationSettings(_sheet, _style_1, _style_2, ruleDescr, allowEmpty,
inputBox, errorBox);
if (_cellStyle != null)
{
Row row = _sheet.GetRow(_sheet.PhysicalNumberOfRows - 1);
Cell cell = row.CreateCell(0);
cell.CellStyle = (_cellStyle);
}
WriteOtherSettings(_sheet, _style_1, promptDescr);
}
public void TestGetDataValidationsListFormula()
{
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.CreateSheet() as HSSFSheet;
List<IDataValidation> list = sheet.GetDataValidations();
Assert.AreEqual(0, list.Count);
IDataValidationHelper dataValidationHelper = sheet.GetDataValidationHelper();
IDataValidationConstraint constraint = dataValidationHelper.CreateFormulaListConstraint("A2");
CellRangeAddressList AddressList = new CellRangeAddressList(0, 0, 0, 0);
IDataValidation validation = dataValidationHelper.CreateValidation(constraint, AddressList);
validation.SuppressDropDownArrow = (/*setter*/true);
sheet.AddValidationData(validation);
list = sheet.GetDataValidations(); // <-- works
Assert.AreEqual(1, list.Count);
HSSFDataValidation dv = list[(0)] as HSSFDataValidation;
Assert.AreEqual(true, dv.SuppressDropDownArrow);
DVConstraint c = dv.Constraint;
Assert.AreEqual(ValidationType.LIST, c.GetValidationType());
Assert.AreEqual("A2", c.Formula1);
Assert.AreEqual(null, c.Formula2);
Assert.AreEqual(double.NaN, c.Value1);
Assert.AreEqual(double.NaN, c.Value2);
}
//public static void test1()
//{
// HSSFWorkbook hssfworkbook = new HSSFWorkbook();
// HSSFSheet sheet1 = hssfworkbook.CreateSheet("Sheet1") as HSSFSheet;
// CellRangeAddressList regions = new CellRangeAddressList(0, 65535, 0, 0);
// DVConstraint constraint = DVConstraint.CreateExplicitListConstraint(new string[] { "itemA", "itemB", "itemC" });
// HSSFDataValidation dataValidate = new HSSFDataValidation(regions, constraint);
// sheet1.AddValidationData(dataValidate);
// MemoryStream ms = new MemoryStream();
// hssfworkbook.Write(ms);
// string workbookFile = @"D:\\wulei22.xls";
// hssfworkbook = null;
// FileStream fs = new FileStream(workbookFile, FileMode.Create, FileAccess.Write);
// byte[] data = ms.ToArray();
// fs.Write(data, 0, data.Length);
// fs.Flush();
// fs.Close();
//}
public static void setdownlist()
{
//创建工作簿
HSSFWorkbook ssfworkbook = new HSSFWorkbook();
//创建工作表(页)
HSSFSheet sheet1 = ssfworkbook.CreateSheet("Sheet1") as HSSFSheet;
//创建一行
HSSFRow headerRow = (HSSFRow)sheet1.CreateRow(0);
//设置表头
headerRow.CreateCell(0).SetCellValue("ID");
//设置表头的宽度
sheet1.SetColumnWidth(0, 15 * 256);
#region 添加显示下拉列表
HSSFSheet sheet2 = ssfworkbook.CreateSheet("ShtDictionary") as HSSFSheet;
ssfworkbook.SetSheetHidden(1, true); //隐藏
sheet2.CreateRow(0).CreateCell(0).SetCellValue("itemA"); //列数据
sheet2.CreateRow(1).CreateCell(0).SetCellValue("itemB");
sheet2.CreateRow(2).CreateCell(0).SetCellValue("itemC");
HSSFName range = ssfworkbook.CreateName() as HSSFName;//创建名称
// range.Reference = "ShtDictionary!$A$1:$A$3";//格式
range.NameName = "dicRange";
#endregion
headerRow.CreateCell(1).SetCellValue("Selected");
sheet1.SetColumnWidth(1, 15 * 256);
//将下拉列表添加
CellRangeAddressList regions = new CellRangeAddressList(1, 65535, 1, 1);
DVConstraint constraint = DVConstraint.CreateFormulaListConstraint("dicRange");
HSSFDataValidation dataValidate = new HSSFDataValidation(regions, constraint);
sheet1.AddValidationData(dataValidate);
headerRow.CreateCell(2).SetCellValue("VALUE");
sheet1.SetColumnWidth(2, 15 * 256);
//写入数据
//创建数据行
HSSFRow dataRow = (HSSFRow)sheet1.CreateRow(1);
//填充数据
dataRow.CreateCell(0).SetCellValue("1"); //id
dataRow.CreateCell(1).SetCellValue(""); //选择框
dataRow.CreateCell(2).SetCellValue("值"); //选择框
System.IO.MemoryStream ms = new System.IO.MemoryStream();
ssfworkbook.Write(ms);
string filename = "Sheet1" + DateTime.Now.Year.ToString() + DateTime.Now.Month.ToString() + DateTime.Now.Day.ToString() + DateTime.Now.Hour.ToString() + DateTime.Now.Minute.ToString() + DateTime.Now.Second.ToString() + ".xls";
object Response = null;
string workbookFile = @"D:\\wulei.xls";
FileStream fs = new FileStream(workbookFile, FileMode.Create, FileAccess.Write);
byte[] data = ms.ToArray();
fs.Write(data, 0, data.Length);
fs.Flush();
fs.Close();
//Response.AddHeader("Content-Disposition", string.Format("attachment; filename=" + filename + ""));
//Response.BinaryWrite(ms.ToArray());
ms.Close();
ms.Dispose();
}
/// <summary>
/// EXCEL2003下拉值
/// </summary>
/// <param name="sheet"></param>
/// <param name="data"></param>
/// <param name="StarCol"></param>
/// <param name="EndCol"></param>
private static void CreateDropDwonListForXLS(HSSFSheet sheet, List <string> data, int StarCol, int EndCol)
{
HSSFDataValidationHelper Validation = new HSSFDataValidationHelper(sheet);
DVConstraint Constraint = (DVConstraint)Validation.CreateExplicitListConstraint(data.ToArray());
CellRangeAddressList AddressList = new CellRangeAddressList(1, 65535, StarCol, EndCol);
var HSSF = Validation.CreateValidation(Constraint, AddressList);
sheet.AddValidationData(HSSF);
}
/// <summary>
/// 引用另一个工作表的形式 得到下拉
/// </summary>
/// <param name="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);
}
public void TestGetDataValidationsAny()
{
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.CreateSheet() as HSSFSheet;
List <IDataValidation> list = sheet.GetDataValidations();
Assert.AreEqual(0, list.Count);
IDataValidationHelper dataValidationHelper = sheet.GetDataValidationHelper();
IDataValidationConstraint constraint = dataValidationHelper.CreateNumericConstraint(ValidationType.ANY,
OperatorType.IGNORED, null, null);
CellRangeAddressList AddressList = new CellRangeAddressList(1, 2, 3, 4);
IDataValidation validation = dataValidationHelper.CreateValidation(constraint, AddressList);
validation.EmptyCellAllowed = (/*setter*/ true);
validation.CreateErrorBox("error-title", "error-text");
validation.CreatePromptBox("prompt-title", "prompt-text");
sheet.AddValidationData(validation);
list = sheet.GetDataValidations(); // <-- works
Assert.AreEqual(1, list.Count);
HSSFDataValidation dv = list[(0)] as HSSFDataValidation;
{
CellRangeAddressList regions = dv.Regions;
Assert.AreEqual(1, regions.CountRanges());
CellRangeAddress Address = regions.GetCellRangeAddress(0);
Assert.AreEqual(1, Address.FirstRow);
Assert.AreEqual(2, Address.LastRow);
Assert.AreEqual(3, Address.FirstColumn);
Assert.AreEqual(4, Address.LastColumn);
}
Assert.AreEqual(true, dv.EmptyCellAllowed);
Assert.AreEqual(false, dv.SuppressDropDownArrow);
Assert.AreEqual(true, dv.ShowErrorBox);
Assert.AreEqual("error-title", dv.ErrorBoxTitle);
Assert.AreEqual("error-text", dv.ErrorBoxText);
Assert.AreEqual(true, dv.ShowPromptBox);
Assert.AreEqual("prompt-title", dv.PromptBoxTitle);
Assert.AreEqual("prompt-text", dv.PromptBoxText);
IDataValidationConstraint c = dv.ValidationConstraint;
Assert.AreEqual(ValidationType.ANY, c.GetValidationType());
Assert.AreEqual(OperatorType.IGNORED, c.Operator);
}
public void CreateDataValidation(string filePath, int sheetIndex, decimal fromNumber, decimal toNumber, int fromRow, int fromColumn, int toRow, int toColumn)
{
FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.ReadWrite);
HSSFWorkbook hssfworkbook = new HSSFWorkbook(file);
if (hssfworkbook != null)
{
CellRangeAddressList rangeList = new CellRangeAddressList(fromRow - 1, toRow - 1, fromColumn - 1, toColumn - 1); // Vì NPOI sử dụng row và column bắt đầu từ 0
DVConstraint dvconstraint = DVConstraint.CreateNumericConstraint(2, OperatorType.BETWEEN, fromNumber.ToString(), toNumber.ToString());
HSSFDataValidation dataValidation = new HSSFDataValidation(rangeList, dvconstraint);
HSSFSheet sheet = (HSSFSheet)hssfworkbook.GetSheetAt(sheetIndex - 1);
if (sheet != null)
{
sheet.AddValidationData(dataValidation);
file = new FileStream(filePath, FileMode.Create);
hssfworkbook.Write(file);
file.Close();
}
}
}
public void CreateDataValidation(string filePath, int sheetIndex, string[] lstContrains, int fromRow, int fromColumn, int toRow, int toColumn)
{
FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.ReadWrite);
HSSFWorkbook hssfworkbook = new HSSFWorkbook(file);
if (hssfworkbook != null)
{
CellRangeAddressList rangeList = new CellRangeAddressList(fromRow - 1, toRow - 1, fromColumn - 1, toColumn - 1); // Vì NPOI sử dụng row và column bắt đầu từ 0
DVConstraint dvconstraint = DVConstraint.CreateExplicitListConstraint(lstContrains);
HSSFDataValidation dataValidation = new HSSFDataValidation(rangeList, dvconstraint);
HSSFSheet sheet = (HSSFSheet)hssfworkbook.GetSheetAt(sheetIndex - 1);
if (sheet != null)
{
sheet.AddValidationData(dataValidation);
file = new FileStream(filePath, FileMode.Create);
hssfworkbook.Write(file);
file.Close();
}
}
}
public static void test2()
{
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[] { "itemA111", "itemB22", "itemC33" });
HSSFDataValidation dataValidate = new HSSFDataValidation(regions, constraint);
sheet1.AddValidationData(dataValidate);
MemoryStream ms = new MemoryStream();
hssfworkbook.Write(ms);
string workbookFile = @"D:\\wulei1111.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 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();
}
/// <summary>
/// Create data validation
/// Hungnd 04/04/2013
/// </summary>
/// <param name="sheetIndex"></param>
/// <param name="fromRow"></param>
/// <param name="fromCol"></param>
/// <param name="toRow"></param>
/// <param name="toCol"></param>
/// <param name="validationType">The value in VTValidationType</param>
/// <param name="fromValue"></param>
/// <param name="toValue"></param>
/// <param name="errorTitle">Default "Error value"</param>
/// <param name="errorMessage">Default "Error input value"</param>
/// <param name="errorStyle">Default ErrorStyle.STOP</param>
/// <param name="lstContrains">Default null</param>
public void CreateCellValidation(int sheetIndex,
int fromRow, int fromColumn, int toRow, int toColumn,
int validationType, string fromValue, string toValue, string[] lstContrains = null,
string errorTitle = "Error value", string errorMessage = "Error input value",
int errorStyle = ErrorStyle.STOP
)
{
HSSFWorkbook hssfworkbook = new HSSFWorkbook(sfile);
if (hssfworkbook != null)
{
CellRangeAddressList rangeList = new CellRangeAddressList(fromRow - 1, toRow - 1, fromColumn - 1, toColumn - 1); // Vì NPOI sử dụng row và column bắt đầu từ 0
DVConstraint dvconstraint = null;
if (validationType.Equals(VTValidationType.LIST))
{
dvconstraint = DVConstraint.CreateExplicitListConstraint(lstContrains);
}
else
{
dvconstraint = DVConstraint.CreateNumericConstraint(validationType, OperatorType.BETWEEN, fromValue, toValue);
}
HSSFDataValidation dataValidation = new HSSFDataValidation(rangeList, dvconstraint);
if (!validationType.Equals(VTValidationType.LIST))
{
dataValidation.CreateErrorBox(errorTitle, errorMessage);
dataValidation.ErrorStyle = errorStyle;
}
HSSFSheet sheet = (HSSFSheet)hssfworkbook.GetSheetAt(sheetIndex);
if (sheet != null)
{
sheet.AddValidationData(dataValidation);
sfile = new FileStream(FilePath, FileMode.Create);
hssfworkbook.Write(sfile);
}
}
}
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>
/// Excel 导出
/// </summary>
/// <param name="dataTable">数据源</param>
/// <param name="fileName">保存路径</param>
/// <param name="sheetName">sheet 名称</param>
/// <param name="titleName">标题</param>
public static void ExportExcel(DataTable dataTable, string fileName, string sheetName, string titleName)
{
//创建 Excel 文件
HSSFWorkbook hssfWorkbook = new HSSFWorkbook();
//创建 Excel Sheet
HSSFSheet hssfSheet = hssfWorkbook.CreateSheet(sheetName);
//调色板实例
Color LevelThreeColor = Color.FromArgb(197, 217, 241);
hssfSheet.DefaultColumnWidth = 20;
hssfSheet.DefaultRowHeight = 10;
//下拉列表
CellRangeAddressList regions = new CellRangeAddressList(0, 65535, 12, 12);
DVConstraint constraint = DVConstraint.CreateExplicitListConstraint(new string[] { "未启动", "整改中", "已完成" });
HSSFDataValidation dataValidate = new HSSFDataValidation(regions, constraint);
hssfSheet.AddValidationData(dataValidate);
#region 合并单元格
//合并单元格 , 开始行 , 结束行 ,开始列 ,结束列
List <CellRangeAddress> cellRange = new List <CellRangeAddress>();
List <CellRangeAddress> cellRange2 = new List <CellRangeAddress>();
cellRange.Add(new CellRangeAddress(0, 0, 0, 13));
cellRange.Add(new CellRangeAddress(1, 1, 0, 8));
cellRange.Add(new CellRangeAddress(1, 1, 9, 13));
cellRange2.Add(new CellRangeAddress(2, 3, 0, 0));
cellRange2.Add(new CellRangeAddress(2, 3, 1, 1));
cellRange2.Add(new CellRangeAddress(2, 3, 2, 2));
cellRange2.Add(new CellRangeAddress(2, 3, 3, 3));
cellRange2.Add(new CellRangeAddress(2, 3, 4, 4));
cellRange2.Add(new CellRangeAddress(2, 3, 5, 5));
cellRange2.Add(new CellRangeAddress(2, 3, 6, 6));
cellRange2.Add(new CellRangeAddress(2, 3, 7, 7));
cellRange2.Add(new CellRangeAddress(2, 2, 8, 10));
cellRange2.Add(new CellRangeAddress(2, 3, 11, 11));
cellRange2.Add(new CellRangeAddress(2, 3, 12, 12));
cellRange2.Add(new CellRangeAddress(2, 3, 13, 13));
cellRange.Add(new CellRangeAddress(2, 3, 0, 0));
cellRange.Add(new CellRangeAddress(2, 3, 1, 1));
cellRange.Add(new CellRangeAddress(2, 3, 2, 2));
cellRange.Add(new CellRangeAddress(2, 3, 3, 3));
cellRange.Add(new CellRangeAddress(2, 3, 4, 4));
cellRange.Add(new CellRangeAddress(2, 3, 5, 5));
cellRange.Add(new CellRangeAddress(2, 3, 6, 6));
cellRange.Add(new CellRangeAddress(2, 3, 7, 7));
cellRange.Add(new CellRangeAddress(2, 2, 8, 10));
cellRange.Add(new CellRangeAddress(2, 3, 11, 11));
cellRange.Add(new CellRangeAddress(2, 3, 12, 12));
cellRange.Add(new CellRangeAddress(2, 3, 13, 13));
foreach (CellRangeAddress cell in cellRange)
{
hssfSheet.AddMergedRegion(cell);
}
#endregion
#region 南京分行信息科技工作检查问题整改跟踪信息表
//创建标题列头 xx分行信息科技工作检查问题整改跟踪信息表
HSSFRow head_1_HSSFRow = hssfSheet.CreateRow(0);
head_1_HSSFRow.Height = 200 * 5;
head_1_HSSFRow.CreateCell(0).SetCellValue(titleName);
//创建样式 Style Header
HSSFCellStyle hssfCellStyle = hssfWorkbook.CreateCellStyle();
//创建字体 Font Header
HSSFFont hssfFontHead = (HSSFFont)hssfWorkbook.CreateFont();
//字体
hssfFontHead.FontName = "宋体";
hssfFontHead.FontHeightInPoints = 16;
hssfFontHead.Color = HSSFColor.BLACK.index;
hssfCellStyle.SetFont(hssfFontHead);
head_1_HSSFRow.GetCell(0).CellStyle = hssfCellStyle;
#endregion
#region 检查基本信息 、整改落实情况跟踪
//创建标题列头 检查基本信息、 整改落实情况跟踪
HSSFRow head_2_HSSFRow = hssfSheet.CreateRow(1);
head_2_HSSFRow.HeightInPoints = 20;
head_2_HSSFRow.CreateCell(0).SetCellValue("检查基本信息");
head_2_HSSFRow.CreateCell(9).SetCellValue("整改落实情况跟踪");
//样式
HSSFCellStyle hssf_2_CellStyle = hssfWorkbook.CreateCellStyle();
HSSFFont hssf_2_FontHead = (HSSFFont)hssfWorkbook.CreateFont();
//字体
hssfFontHead.FontName = "宋体";
hssfFontHead.FontHeightInPoints = 9;
hssfFontHead.Color = HSSFColor.BLACK.index;
hssf_2_CellStyle.SetFont(hssf_2_FontHead);
head_2_HSSFRow.GetCell(0).CellStyle = hssf_2_CellStyle;
head_2_HSSFRow.GetCell(9).CellStyle = hssf_2_CellStyle;
#endregion
#region 表列头 、 列名
//样式
HSSFCellStyle hssf_3_CellStyle = hssfWorkbook.CreateCellStyle();
HSSFFont hssf_3_FontHead = (HSSFFont)hssfWorkbook.CreateFont();
hssf_3_CellStyle.FillPattern = CellFillPattern.SOLID_FOREGROUND;
hssf_3_CellStyle.FillBackgroundColor = GetXLColour(hssfWorkbook, LevelThreeColor);
hssf_3_CellStyle.BorderBottom = CellBorderType.THIN;
hssf_3_CellStyle.BorderLeft = CellBorderType.THIN;
hssf_3_CellStyle.BorderRight = CellBorderType.THIN;
hssf_3_CellStyle.BorderTop = CellBorderType.THIN;
//字体
hssf_3_FontHead.FontName = "宋体";
hssf_3_FontHead.FontHeightInPoints = 9;
hssf_3_FontHead.Color = HSSFColor.BLACK.index;
hssf_3_CellStyle.SetFont(hssf_3_FontHead);
HSSFRow content_1_HSSFRow = hssfSheet.CreateRow(2);
content_1_HSSFRow.CreateCell(0).SetCellValue("序号");
content_1_HSSFRow.CreateCell(1).SetCellValue("分行名称");
content_1_HSSFRow.CreateCell(2).SetCellValue("检查开始时间");
content_1_HSSFRow.CreateCell(3).SetCellValue("检查结束时间");
content_1_HSSFRow.CreateCell(4).SetCellValue("检查项分类");
content_1_HSSFRow.CreateCell(5).SetCellValue("问题性质");
content_1_HSSFRow.CreateCell(6).SetCellValue("问题描述");
content_1_HSSFRow.CreateCell(7).SetCellValue("检查意见");
content_1_HSSFRow.CreateCell(8).SetCellValue("整改措施计划");
content_1_HSSFRow.CreateCell(11).SetCellValue("推进情况");
content_1_HSSFRow.CreateCell(12).SetCellValue("整改状态");
content_1_HSSFRow.CreateCell(13).SetCellValue("最终完成时间");
HSSFRow content_2_HSSFRow = hssfSheet.CreateRow(3);
content_2_HSSFRow.CreateCell(8).SetCellValue("整改措施");
content_2_HSSFRow.CreateCell(9).SetCellValue("整改完成标志");
content_2_HSSFRow.CreateCell(10).SetCellValue("计划完成时间");
content_2_HSSFRow.GetCell(8).CellStyle = hssf_3_CellStyle;
content_2_HSSFRow.GetCell(9).CellStyle = hssf_3_CellStyle;
content_2_HSSFRow.GetCell(10).CellStyle = hssf_3_CellStyle;
#endregion
#region 边框、背景颜色设置
//创建样式 Style Bord
HSSFCellStyle hssfCellStyleBord = hssfWorkbook.CreateCellStyle();
//创建字体 Font Bord
hssfCellStyleBord.Alignment = CellHorizontalAlignment.CENTER;
hssfCellStyleBord.VerticalAlignment = CellVerticalAlignment.CENTER;
hssfCellStyleBord.BorderBottom = CellBorderType.THIN;
hssfCellStyleBord.BorderLeft = CellBorderType.THIN;
hssfCellStyleBord.BorderRight = CellBorderType.THIN;
hssfCellStyleBord.BorderTop = CellBorderType.THIN;
HSSFFont hssfFontBord = (HSSFFont)hssfWorkbook.CreateFont();
hssfFontBord.FontName = "宋体";
hssfFontBord.FontHeightInPoints = 12;
hssfFontBord.Color = HSSFColor.BLACK.index;
hssfCellStyleBord.SetFont(hssfFontBord);
//边框
foreach (var cell in cellRange)
{
for (int i = cell.FirstRow; i <= cell.LastRow; i++)
{
HSSFRow row = HSSFCellUtil.GetRow(i, hssfSheet);
for (int j = cell.FirstColumn; j <= cell.LastColumn; j++)
{
HSSFCell singleCell = HSSFCellUtil.GetCell(row, (short)j);
singleCell.CellStyle = hssfCellStyleBord;
}
}
}
//创建样式 Style Bord
HSSFCellStyle hssfCellStyleBord_2 = hssfWorkbook.CreateCellStyle();
hssfCellStyleBord_2.Alignment = CellHorizontalAlignment.CENTER;
hssfCellStyleBord_2.VerticalAlignment = CellVerticalAlignment.CENTER;
hssfCellStyleBord_2.BorderBottom = CellBorderType.THIN;
hssfCellStyleBord_2.BorderLeft = CellBorderType.THIN;
hssfCellStyleBord_2.BorderRight = CellBorderType.THIN;
hssfCellStyleBord_2.BorderTop = CellBorderType.THIN;
hssfCellStyleBord_2.FillPattern = CellFillPattern.SOLID_FOREGROUND;
hssfCellStyleBord_2.FillBackgroundColor = GetXLColour(hssfWorkbook, LevelThreeColor);
hssfCellStyleBord_2.SetFont(hssfFontBord);
foreach (var cell in cellRange2)
{
for (int i = cell.FirstRow; i <= cell.LastRow; i++)
{
HSSFRow row = HSSFCellUtil.GetRow(i, hssfSheet);
for (int j = cell.FirstColumn; j <= cell.LastColumn; j++)
{
HSSFCell singleCell = HSSFCellUtil.GetCell(row, (short)j);
singleCell.CellStyle = hssfCellStyleBord_2;
}
}
}
#endregion
#region 数据内容
try
{
#region 内容样式
HSSFCellStyle hssf_i_CellStyle = hssfWorkbook.CreateCellStyle();
HSSFFont hssf_i_FontHead = (HSSFFont)hssfWorkbook.CreateFont();
hssf_i_CellStyle.Alignment = CellHorizontalAlignment.CENTER;
hssf_i_CellStyle.VerticalAlignment = CellVerticalAlignment.CENTER;
hssf_i_CellStyle.BorderBottom = CellBorderType.THIN;
hssf_i_CellStyle.BorderLeft = CellBorderType.THIN;
hssf_i_CellStyle.BorderRight = CellBorderType.THIN;
hssf_i_CellStyle.BorderTop = CellBorderType.THIN;
//字体
hssf_i_FontHead.FontName = "宋体";
hssf_i_FontHead.FontHeightInPoints = 12;
hssf_i_FontHead.Color = HSSFColor.BLACK.index;
hssf_i_CellStyle.SetFont(hssf_i_FontHead);
hssf_i_CellStyle.IsLocked = false;
#endregion
if (dataTable.Rows.Count > 0)
{
for (int i = 0; i < dataTable.Rows.Count; i++)
{
HSSFRow dataHSSFRow = hssfSheet.CreateRow(i + 4);
dataHSSFRow.HeightInPoints = 20;
dataHSSFRow.CreateCell(0).SetCellValue(dataTable.Rows[i][0].ToString());
dataHSSFRow.CreateCell(1).SetCellValue(dataTable.Rows[i][1].ToString());
dataHSSFRow.CreateCell(2).SetCellValue(dataTable.Rows[i][2].ToString());
dataHSSFRow.CreateCell(3).SetCellValue(dataTable.Rows[i][3].ToString());
dataHSSFRow.CreateCell(4).SetCellValue(dataTable.Rows[i][4].ToString());
dataHSSFRow.CreateCell(5).SetCellValue(dataTable.Rows[i][5].ToString());
dataHSSFRow.CreateCell(6).SetCellValue(dataTable.Rows[i][6].ToString());
dataHSSFRow.CreateCell(7).SetCellValue(dataTable.Rows[i][7].ToString());
dataHSSFRow.CreateCell(8).SetCellValue(string.Empty);
dataHSSFRow.CreateCell(9).SetCellValue(string.Empty);
dataHSSFRow.CreateCell(10).SetCellValue(string.Empty);
dataHSSFRow.CreateCell(11).SetCellValue(string.Empty);
dataHSSFRow.CreateCell(12).SetCellValue(string.Empty);
dataHSSFRow.CreateCell(13).SetCellValue(string.Empty);
for (int j = 0; j < 14; j++)
{
dataHSSFRow.GetCell(j).CellStyle = hssf_i_CellStyle;
}
}
}
hssfSheet.ProtectSheet("21");
}
catch (Exception ex)
{
throw new Exception("导出 Excel 错误 , " + ex.Message);
}
#endregion
//保存文件
using (FileStream fileStream = new FileStream(fileName, FileMode.Create))
{
hssfWorkbook.Write(fileStream);
}
}
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();
}
private void PresetForProductSheet(HSSFSheet prdSheet, ISheet skuSheet, DateTime?sheetDate, ISheet atpSheet, Dictionary <int, ICellStyle> colors)
{
int?ThresholdQty = null;
if (ConfigurationManager.AppSettings["ThresholdQty"] != null)
{
int qty;
if (int.TryParse(ConfigurationManager.AppSettings["ThresholdQty"].ToString(), out qty))
{
ThresholdQty = qty;
}
}
for (int i = 1; i <= skuSheet.LastRowNum; i++)
{
HSSFRow row = (HSSFRow)skuSheet.GetRow(i);
string cellPos = ((HSSFCell)row.Cells[0]).ToString();
string multipleValue = ((HSSFCell)row.Cells[3]).ToString();
string colIdxstring = string.Empty;
int colIdx = -1;
string rowIdxstring = string.Empty;
int rowIdx = 0;
for (int j = 0; j < cellPos.Length; j++)
{
int ascii = (int)cellPos[j];
if (ascii > 57)
{
if (colIdx == -1)
{
colIdx = ascii - 65;
}
else
{
colIdx = (colIdx + 1) * 26 + (ascii - 65);
}
colIdxstring += cellPos[j];
}
else
{
rowIdxstring += cellPos[j];
}
}
rowIdx = int.Parse(rowIdxstring) - 1;
ICell cell = prdSheet.GetRow(rowIdx).GetCell(colIdx);
if (cell == null)
{
WriteToLog(string.Format("Cell[{0}] is not created in sheet[{1}]", cellPos, prdSheet.SheetName));
}
else
{
if (sheetDate.HasValue)
{
try
{
ICell atpCell = atpSheet.GetRow(rowIdx).GetCell(colIdx);
string dateValues = (atpCell == null ? string.Empty : atpSheet.GetRow(rowIdx).GetCell(colIdx).StringCellValue);
string[] arr = dateValues.Split(new char[] { '|', ',' }, StringSplitOptions.RemoveEmptyEntries);
int availbleQty = 0;
if (arr.Length == 1)
{
DateTime atpDate = DateTime.Parse(arr[0]);
if (atpDate <= sheetDate.Value)
{
availbleQty = 999999;
}
}
else
{
for (int j = 0; j < arr.Length; j = j + 2)
{
DateTime atpDate = DateTime.Parse(arr[j]);
if (atpDate <= sheetDate.Value)
{
availbleQty += int.Parse(arr[j + 1]);
}
}
}
var list = colors.Where(kvp => kvp.Key < availbleQty);
if (list.Count() > 0)
{
int colorIdx = list.LastOrDefault().Key;
cell.CellStyle = colors[colorIdx];
}
if (availbleQty > 0)
{
cell.CellStyle.IsLocked = false;
//cell.SetCellValue(availbleQty.ToString());
//if (ThresholdQty.HasValue && availbleQty > ThresholdQty)
//{
// HSSFPatriarch patr = (HSSFPatriarch)prdSheet.CreateDrawingPatriarch();
// HSSFComment comment = patr.CreateCellComment(new HSSFClientAnchor(0, 0, 0, 0, colIdx, rowIdx, colIdx + 1, rowIdx + 1)) as HSSFComment;
// comment.String = new HSSFRichTextString(string.Format("{0}+", ThresholdQty.Value));
// comment.Author = "Plumriver";
// cell.CellComment = comment;
//}
}
}
catch (Exception ex) {
WriteToLog(ex.Message);
}
}
else
{
cell.CellStyle.IsLocked = false;
}
DVConstraint dvConstraint = DVConstraint.CreateCustomFormulaConstraint(string.Format("(MOD(indirect(address(row(),column())) ,{0})=0)", multipleValue));
HSSFDataValidation orderMultipleValidation = new HSSFDataValidation(new CellRangeAddressList(cell.RowIndex, cell.RowIndex, cell.ColumnIndex, cell.ColumnIndex), dvConstraint);
orderMultipleValidation.CreateErrorBox("Multiple Value Cell", string.Format("You must enter a multiple of {0} in this cell.", multipleValue).Replace(".00 ", " ").Replace(".0 ", " "));
prdSheet.AddValidationData(orderMultipleValidation);
}
}
}
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++;
}
}
}
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.
HSSFWorkbook wb = HSSFTestDataSamples.OpenSampleWorkbook("dvEmpty.xls");
int dvRow = 0;
HSSFSheet sheet = (HSSFSheet)wb.GetSheetAt(0);
DVConstraint dc = DVConstraint.CreateNumericConstraint(DVConstraint.ValidationType.INTEGER, DVConstraint.OperatorType.EQUAL, "42", null);
HSSFDataValidation dv = new HSSFDataValidation(new CellRangeAddressList(dvRow, dvRow, 0, 0), dc);
dv.EmptyCellAllowed = (false);
dv.ErrorStyle = (HSSFDataValidation.ERRORSTYLE.STOP);
dv.ShowPromptBox = (true);
dv.CreateErrorBox("Xxx", "Yyy");
dv.SuppressDropDownArrow = (true);
sheet.AddValidationData(dv);
MemoryStream baos = new MemoryStream();
try
{
wb.Write(baos);
}
catch (IOException)
{
throw;
}
byte[] wbData = baos.ToArray();
//if (false)
//{ // TODO (Jul 2008) fix EventRecordFactory to Process unknown records, (and DV records for that matter)
// ERFListener erfListener = null; // new MyERFListener();
// EventRecordFactory erf = new EventRecordFactory(erfListener, null);
// try
// {
// POIFSFileSystem fs = new POIFSFileSystem(new MemoryStream(baos.ToArray()));
// erf.ProcessRecords(fs.CreatePOIFSDocumentReader("Workbook"));
// }
// catch (RecordFormatException)
// {
// throw;
// }
// catch (IOException)
// {
// throw;
// }
//}
// else verify record ordering by navigating the raw bytes
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 AssertFailedException("Identified bug 45519");
}
Assert.AreEqual(DVRecord.sid, nextSid);
}
/// <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);
}
