void Button2_Click(object sender, EventArgs e) { NpoiLib npl = new NpoiLib(); List<Row> list = npl.ReadExcel(Server.MapPath("text.xls"), 0); Response.Write("<table><tr><td>111</td><td>222</td><td>333</td></tr>"); int i = 0; string[] explicitListValues = new string[] { "AS", "CE CP", "SC IC", "Others" }; foreach (Row r in list) { if (r != null) { Cell c = r.GetCell(0); NPOI.SS.Util.CellRangeAddressList ranglist = new NPOI.SS.Util.CellRangeAddressList(); ranglist.AddCellRangeAddress(new CellRangeAddress(0, 10, 0, 3)); DVConstraint dvconstraint = DVConstraint.CreateExplicitListConstraint(explicitListValues); HSSFDataValidation dataValidation = new HSSFDataValidation(ranglist, dvconstraint); ((HSSFSheet)c.Sheet).AddValidationData(dataValidation); string v1 = npl.ReadRowData(r, 0); string v2 = npl.ReadRowData(r, 1); string v3 = npl.ReadRowData(r, 2); Response.Write("<tr><td>" + v1 + "</td><td>" + v2 + "</td><td>" + v3 + "</td></tr>"); } } Response.Write("</table>"); }
static void Main(string[] args) { InitializeWorkbook(); ISheet sheet1 = hssfworkbook.CreateSheet("Sheet1"); ISheet sheet2 = hssfworkbook.CreateSheet("Sheet2"); //create three items in Sheet2 IRow row0 = sheet2.CreateRow(0); ICell 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 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()"); } }
private void CopyRowAreaApplyDetail(HSSFWorkbook workbook, ISheet sourceWorksheet, ISheet destinationWorksheet, int sourceRowNum, int destinationRowNum, int rowNum) { var getLastRowNum = destinationWorksheet.LastRowNum; for (int i = 0; i < rowNum; i++) { CopyRow(workbook, sourceWorksheet, destinationWorksheet, sourceRowNum + i, destinationRowNum + i); } var markNumConstraint = DVConstraint.CreateNumericConstraint(0x02, 0x06, "0", null); var markNumColumn_4 = new CellRangeAddressList(getLastRowNum, getLastRowNum + rowNum, 4, 4); var markdvNum_4 = new HSSFDataValidation(markNumColumn_4, markNumConstraint); destinationWorksheet.AddValidationData(markdvNum_4); }
private void CopyRowAreaApply(HSSFWorkbook workbook, ISheet sourceWorksheet, ISheet destinationWorksheet, int sourceRowNum, int destinationRowNum, int rowNum) { var getLastRowNum = destinationWorksheet.LastRowNum; for (int i = 0; i < rowNum; i++) { CopyRow(workbook, sourceWorksheet, destinationWorksheet, sourceRowNum + i, destinationRowNum + i); } var markListConstraint = DVConstraint.CreateExplicitListConstraint(new string[] { "氣體", "液體", "固體" }); var markNumConstraint = DVConstraint.CreateNumericConstraint(0x02, 0x06, "0", null); var markUnitLongConstraint = DVConstraint.CreateExplicitListConstraint(new string[] { "kcal/m³", "kcal/L", "kcal/kg" }); var markUnitShortConstraint = DVConstraint.CreateExplicitListConstraint(new string[] { "m³", "kL", "kg" }); var markColumn = new CellRangeAddressList(getLastRowNum, getLastRowNum + rowNum, 3, 3); var markdv = new HSSFDataValidation(markColumn, markListConstraint); var markULColumn = new CellRangeAddressList(getLastRowNum, getLastRowNum + rowNum, 6, 6); var markULdv = new HSSFDataValidation(markULColumn, markUnitLongConstraint); var markSLColumn = new CellRangeAddressList(getLastRowNum, getLastRowNum + rowNum, 8, 8); var markSLdv = new HSSFDataValidation(markSLColumn, markUnitShortConstraint); var markNumColumn_5 = new CellRangeAddressList(getLastRowNum, getLastRowNum + rowNum, 5, 5); var markdvNum_5 = new HSSFDataValidation(markNumColumn_5, markNumConstraint); var markNumColumn_7 = new CellRangeAddressList(getLastRowNum, getLastRowNum + rowNum, 7, 7); var markdvNum_7 = new HSSFDataValidation(markNumColumn_7, markNumConstraint); var markNumColumn_9 = new CellRangeAddressList(getLastRowNum, getLastRowNum, 9, 9); var markdvNum_9 = new HSSFDataValidation(markNumColumn_9, markNumConstraint); var markNumColumn_10 = new CellRangeAddressList(getLastRowNum, getLastRowNum, 10, 10); var markdvNum_10 = new HSSFDataValidation(markNumColumn_10, markNumConstraint); destinationWorksheet.AddValidationData(markdv); destinationWorksheet.AddValidationData(markULdv); destinationWorksheet.AddValidationData(markSLdv); destinationWorksheet.AddValidationData(markdvNum_5); destinationWorksheet.AddValidationData(markdvNum_7); destinationWorksheet.AddValidationData(markdvNum_9); destinationWorksheet.AddValidationData(markdvNum_10); }
public ActionResult Template(int? tagId) { var basicInfoSheetName = ProUploadService.BASIC_SHEET; var moreInfoSheetName = ProUploadService.MORE_SHEET; var supportSheetName = "不要修改"; var headerLabels = new Dictionary<string, dynamic>() { {"商品代码",new {dataformat=0,width=10}}, {"商品名称",new {dataformat=0,width=20}}, {"描述",new {dataformat=0,width=50}}, {"吊牌价",new {dataformat=2,width=8}}, {"现价",new {dataformat=2,width=8}}, {"品牌名",new {dataformat=0,width=20}}, { "分类名",new {dataformat=0,width=20}}, {"门店名",new {dataformat=0,width=20}}, { "促销活动编码",new {dataformat=0,width=20}}, { "专题编码(多个以,分割)",new {dataformat=0,width=20}}, {"可销售",new {dataformat=2,width=5}}, {"商品货号",new {dataformat=0,width=10}} }; var workbook = new HSSFWorkbook(); var headerLabelCellStyle = workbook.CreateCellStyle(); headerLabelCellStyle.BorderBottom = BorderStyle.THIN; headerLabelCellStyle.BorderLeft = BorderStyle.THIN; headerLabelCellStyle.BorderRight = BorderStyle.THIN; headerLabelCellStyle.BorderTop = BorderStyle.THIN; headerLabelCellStyle.WrapText = true; var headerLabelFont = workbook.CreateFont(); headerLabelFont.Boldweight = (short)FontBoldWeight.BOLD; headerLabelCellStyle.SetFont(headerLabelFont); //set support sheet var supportSheet = workbook.CreateSheet(supportSheetName); workbook.SetSheetHidden(workbook.GetSheetIndex(supportSheet), true); Func<int, dynamic, int> supportFill = (rowIndex, data) => { var brandRow = supportSheet.CreateRow(rowIndex++); var brandCodeCol = brandRow.CreateCell(1); brandCodeCol.SetCellType(CellType.STRING); brandCodeCol.SetCellValue(data.Id); var brandNameCol = brandRow.CreateCell(2); brandNameCol.SetCellType(CellType.STRING); brandCodeCol.SetCellValue(data.Name); return rowIndex; }; int brandRowIndex = 0; foreach (var brand in _brandRepo.Get(b => b.Status != (int)DataStatus.Deleted).OrderBy(b => b.Name).Select(b => new { Id = b.Id, Name = b.Name })) { brandRowIndex = supportFill(brandRowIndex, brand); } int tagRowIndex = brandRowIndex; var tagLinq = _tagRepo.Get(b => b.Status != (int)DataStatus.Deleted).OrderBy(b => b.Name).Select(b => new { Id = b.Id, Name = b.Name }); //if (tagId.HasValue) // tagLinq = tagLinq.Where(t => t.Id == tagId.Value); foreach (var tag in tagLinq.OrderBy(t => t.Name)) { tagRowIndex = supportFill(tagRowIndex, tag); } int storeRowIndex = tagRowIndex; foreach (var store in _storeRepo.Get(b => b.Status != (int)DataStatus.Deleted).OrderBy(b => b.Name).Select(b => new { Id = b.Id, Name = b.Name })) { storeRowIndex = supportFill(storeRowIndex, store); } //set basic sheet var sheet1 = workbook.CreateSheet(basicInfoSheetName); //workbook.SetSheetOrder(basicInfoSheetName, 0); var rowFirst = sheet1.CreateRow(0); Action<int, string, dynamic> cellSetting = (cellindex, desc, option) => { var cell = rowFirst.CreateCell(cellindex); cell.SetCellType(CellType.STRING); cell.SetCellValue(desc); cell.CellStyle = headerLabelCellStyle; sheet1.SetColumnWidth(cellindex, option.width * 255); var currentCellStyle = workbook.CreateCellStyle(); currentCellStyle.DataFormat = (short)option.dataformat; sheet1.SetDefaultColumnStyle(cellindex, currentCellStyle); }; int index = 0; foreach (var key in headerLabels.Keys) { cellSetting(index++, key, headerLabels[key]); } //set constraint DVConstraint brandConstaint = DVConstraint.CreateFormulaListConstraint(string.Format("'{0}'!$B$1:$B${1}", supportSheetName, brandRowIndex)); CellRangeAddressList brandaddressList = new CellRangeAddressList(1, 1000, 5, 5); HSSFDataValidation branddataValidation = new HSSFDataValidation(brandaddressList, brandConstaint); branddataValidation.SuppressDropDownArrow = false; sheet1.AddValidationData(branddataValidation); DVConstraint tagConstaint = DVConstraint.CreateFormulaListConstraint(string.Format("'{0}'!$B${1}:$B${2}", supportSheetName, brandRowIndex + 1, tagRowIndex)); CellRangeAddressList tagaddressList = new CellRangeAddressList(1, 1000, 6, 6); HSSFDataValidation tagdataValidation = new HSSFDataValidation(tagaddressList, tagConstaint); tagdataValidation.SuppressDropDownArrow = false; sheet1.AddValidationData(tagdataValidation); DVConstraint storeConstaint = DVConstraint.CreateFormulaListConstraint(string.Format("'{0}'!$B${1}:$B${2}", supportSheetName, tagRowIndex + 1, storeRowIndex)); CellRangeAddressList storeaddressList = new CellRangeAddressList(1, 1000, 7, 7); HSSFDataValidation storedataValidation = new HSSFDataValidation(storeaddressList, storeConstaint); storedataValidation.SuppressDropDownArrow = false; sheet1.AddValidationData(storedataValidation); DVConstraint is4saleConstaint = DVConstraint.CreateExplicitListConstraint(new string[] { ProUploadService.IS_4SALE_YES, ProUploadService.IS_4SALE_NO }); CellRangeAddressList is4saleaddressList = new CellRangeAddressList(1, 1000, 10, 10); HSSFDataValidation is4saledataValidation = new HSSFDataValidation(is4saleaddressList, is4saleConstaint); is4saledataValidation.SuppressDropDownArrow = false; sheet1.AddValidationData(is4saledataValidation); //set sheet2 //create property value sheet var propertyLinq = _propertyRepo.Get(b => b.Status != (int)DataStatus.Deleted) .Join(Context.Set<TagEntity>(), o => o.CategoryId, i => i.Id, (o, i) => new { P = o, C = i }) .OrderBy(b => b.P.CategoryId) .ThenBy(b => b.P.PropertyDesc); int propertyRowIndex = storeRowIndex; foreach (var tag in tagLinq) { var propertyFromIndex = propertyRowIndex; foreach (var property in propertyLinq.Where(p => p.C.Id == tag.Id).Select(p=>new {Id = p.P.Id,Name=p.P.PropertyDesc})) { propertyRowIndex = supportFill(propertyRowIndex, property); } if (propertyRowIndex > propertyFromIndex) { var rName = workbook.CreateName(); rName.RefersToFormula = string.Format("'{0}'!$B${1}:$B${2}", supportSheetName, propertyFromIndex + 1, propertyRowIndex); rName.NameName = tag.Name; } foreach (var property in propertyLinq.Where(p => p.C.Id == tag.Id)) { int valueFromIndex = propertyRowIndex; foreach (var pvalue in _valueRepo.Get(b => b.Status != (int)DataStatus.Deleted && b.PropertyId == property.P.Id).Select(p => new { Id = p.Id, Name = p.ValueDesc })) { propertyRowIndex = supportFill(propertyRowIndex, pvalue); } if (propertyRowIndex > valueFromIndex) { var rName = workbook.CreateName(); rName.RefersToFormula = string.Format("'{0}'!$B${1}:$B${2}", supportSheetName, valueFromIndex + 1, propertyRowIndex); rName.NameName = string.Format("{0}_{1}", property.C.Name, property.P.PropertyDesc); } } } var moreheaderLabels = new Dictionary<string, dynamic>() { {"商品代码",new {dataformat=0,width=10}}, {"属性名",new {dataformat=0,width=20}}, {"属性值",new {dataformat=0,width=10}} }; var sheet2 = workbook.CreateSheet(moreInfoSheetName); var morerowFirst = sheet2.CreateRow(0); int moreCellIndex = 0; foreach (var key in moreheaderLabels.Keys) { var cell = morerowFirst.CreateCell(moreCellIndex); cell.SetCellType(CellType.STRING); cell.SetCellValue(key); cell.CellStyle = headerLabelCellStyle; sheet2.SetColumnWidth(moreCellIndex, moreheaderLabels[key].width * 255); var currentCellStyle = workbook.CreateCellStyle(); currentCellStyle.DataFormat = (short)moreheaderLabels[key].dataformat; sheet2.SetDefaultColumnStyle(moreCellIndex, currentCellStyle); moreCellIndex++; } for (int i = 3; i <= 11; i++) { var cell = morerowFirst.CreateCell(i); cell.SetCellType(CellType.STRING); cell.SetCellValue(string.Empty); cell.CellStyle = headerLabelCellStyle; sheet2.SetColumnWidth(i, 10 * 255); } //set merge cell for last cell sheet2.AddMergedRegion(new CellRangeAddress(0, 0, 2, 11)); //set constraint DVConstraint codeConstaint = DVConstraint.CreateFormulaListConstraint(string.Format("'{0}'!$A${1}:$A${2}", basicInfoSheetName, 2, 1000)); CellRangeAddressList codeaddressList = new CellRangeAddressList(1, 1000, 0, 0); HSSFDataValidation codedataValidation = new HSSFDataValidation(codeaddressList, codeConstaint); codedataValidation.SuppressDropDownArrow = false; sheet2.AddValidationData(codedataValidation); for (int i = 1; i < 1000; i++) { //create hide helper cell sheet2.SetArrayFormula(string.Format("VLOOKUP($A${0},'{1}'!$A$1:$L$1000,7,FALSE)&\"_\"&$B${0}",i+1,basicInfoSheetName), new CellRangeAddress(i,i,12,12)); //create hide helper cell sheet2.SetArrayFormula(string.Format("VLOOKUP($A${0},'{1}'!$A$1:$L$1000,7,FALSE)", i + 1, basicInfoSheetName), new CellRangeAddress(i, i, 13, 13)); sheet2.SetColumnHidden(12, true); sheet2.SetColumnHidden(13, true); DVConstraint pConstaint = DVConstraint.CreateFormulaListConstraint(string.Format("INDIRECT($N${0})", i + 1)); // DVConstraint.CreateFormulaListConstraint(string.Format("'{0}'!$B${1}:$B${2}", supportSheetName, storeRowIndex + 1, propertyRowIndex)); CellRangeAddressList paddressList = new CellRangeAddressList(i,i, 1, 1); HSSFDataValidation pdataValidation = new HSSFDataValidation(paddressList, pConstaint); pdataValidation.SuppressDropDownArrow = false; sheet2.AddValidationData(pdataValidation); //set constraint DVConstraint pvConstaint = DVConstraint.CreateFormulaListConstraint(string.Format("INDIRECT($M${0})", i + 1)); CellRangeAddressList pvaddressList = new CellRangeAddressList(i, i, 2, 11); HSSFDataValidation pvdataValidation = new HSSFDataValidation(pvaddressList, pvConstaint); pvdataValidation.SuppressDropDownArrow = false; sheet2.AddValidationData(pvdataValidation); } workbook.SetActiveSheet(workbook.GetSheetIndex(sheet1)); var ms = new MemoryStream(); workbook.Write(ms); ms.Position = 0; var downloadName = tagId.HasValue ? string.Format("商品上传模版-{0}.xls", tagId.Value) : "商品上传模块.xls"; return File(ms, "application/vnd.ms-excel", downloadName); }
/// <summary> /// 生成下拉列表 /// 此方法暂时不能使用 /// </summary> /// <param name="sheet"></param> /// <param name="firstRow"></param> /// <param name="lastRow"></param> /// <param name="FirstCol"></param> /// <param name="LastCol"></param> /// <param name="arr"></param> public void WriteDropDownList2(Sheet sheet, int firstRow, int lastRow, int FirstCol, int LastCol, string[] data) { string tmpShtDictionary = "tmpShtDictionary"; Sheet tmpSheet = _workbook.CreateSheet(tmpShtDictionary); for (int i = 0; i < data.Length; i++) { tmpSheet.CreateRow(i).CreateCell(0).SetCellValue(data[i]); } Name range = _workbook.CreateName(); range.RefersToFormula = tmpShtDictionary + "!$A1:$A" + data.Length.ToString(); range.NameName = "dicRange"; NPOI.SS.Util.CellRangeAddressList ranglist = new NPOI.SS.Util.CellRangeAddressList(); ranglist.AddCellRangeAddress(new CellRangeAddress(firstRow, lastRow, FirstCol, LastCol)); DVConstraint dvconstraint = DVConstraint.CreateFormulaListConstraint(range.NameName); HSSFDataValidation dataValidation = new HSSFDataValidation(ranglist, dvconstraint); ((HSSFSheet)sheet).AddValidationData(dataValidation); _workbook.RemoveSheetAt(_workbook.GetSheetIndex(tmpSheet)); _workbook.RemoveName(range.NameName); }
/// <summary> /// 生成下拉列表 /// </summary> /// <param name="sheet"></param> /// <param name="firstRow"></param> /// <param name="lastRow"></param> /// <param name="FirstCol"></param> /// <param name="LastRow"></param> /// <param name="arr"></param> public void WriteDropDownList(Sheet sheet, int firstRow, int lastRow, int FirstCol, int LastCol, string[] arr) { NPOI.SS.Util.CellRangeAddressList ranglist = new NPOI.SS.Util.CellRangeAddressList(); ranglist.AddCellRangeAddress(new CellRangeAddress(firstRow, lastRow, FirstCol, LastCol)); DVConstraint dvconstraint = DVConstraint.CreateExplicitListConstraint(arr); HSSFDataValidation dataValidation = new HSSFDataValidation(ranglist, dvconstraint); ((HSSFSheet)sheet).AddValidationData(dataValidation); }
//数据有效性以及下拉框的设置 private static HSSFDataValidation CreateDataValidation(int index, Tk5FieldInfoEx fieldInfo) { CellRangeAddressList region = new CellRangeAddressList(1, 65535, index, index); DVConstraint constraint = null; HSSFDataValidation dataValidate = null; if (fieldInfo.Decoder != null && fieldInfo.Decoder.Type == DecoderType.CodeTable) { IEnumerable<IDecoderItem> data = GetDecoderItem(fieldInfo); if (data != null) { List<string> optionList = new List<string>(); foreach (IDecoderItem item in data) { if (item != null) { TkDebug.AssertArgumentNullOrEmpty(item.Name, "item.Name", null); optionList.Add(item.Name); } } constraint = DVConstraint.CreateExplicitListConstraint(optionList.ToArray()); } } else { if (fieldInfo.InternalControl != null && fieldInfo.InternalControl.SrcControl == ControlType.CheckBox) { constraint = DVConstraint.CreateExplicitListConstraint(new string[] { "√", "" }); } else { switch (fieldInfo.DataType) { case TkDataType.DateTime: case TkDataType.Date: constraint = DVConstraint.CreateDateConstraint(6, "1900-01-01", null, "yyyy-MM-dd"); break; case TkDataType.Double: case TkDataType.Decimal: case TkDataType.Money: constraint = DVConstraint.CreateNumericConstraint(2, 1, "1", "0"); break; case TkDataType.Long: case TkDataType.Int: case TkDataType.Short: case TkDataType.Byte: case TkDataType.Bit: constraint = DVConstraint.CreateNumericConstraint(1, 1, "1", "0"); break; default: break; } } } if (constraint != null) { dataValidate = new HSSFDataValidation(region, constraint); dataValidate.CreateErrorBox("error", "You must input a valid value!"); } return dataValidate; }
//#region 设计Excel模板 //public static byte[] GetTemplate(Tk5ListMetaData metaInfos) //{ // MemoryStream ms = new MemoryStream(); // using (ms) // { // HSSFWorkbook workbook = new HSSFWorkbook(); // HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet(metaInfos.Table.TableDesc); // HSSFRow row = (HSSFRow)sheet.CreateRow(0); // int index = 0; // foreach(Tk5FieldInfoEx meta) // } //} //#endregion #region 相关的方法2 //public static void InitializeWorkbook(HSSFWorkbook workbook) //{ // ////create a entry of DocumentSummaryInformation // if (workbook == null) // workbook = new HSSFWorkbook(); // //HSSFFont font1 = workbook.CreateFont(); // //HSSFCellStyle Style = workbook.CreateCellStyle(); // //font1.FontHeightInPoints = 10; // //font1.FontName = "新細明體"; // //Style.SetFont(font1); // //for (int i = 0; i < workbook.NumberOfSheets; i++) // //{ // // HSSFSheet Sheets = workbook.GetSheetAt(0); // // for (int k = Sheets.FirstRowNum; k <= Sheets.LastRowNum; k++) // // { // // HSSFRow row = Sheets.GetRow(k); // // for (int l = row.FirstCellNum; l < row.LastCellNum; l++) // // { // // HSSFCell Cell = row.GetCell(l); // // Cell.CellStyle = Style; // // } // // } // //} // DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation(); // dsi.Company = "測試公司"; // workbook.DocumentSummaryInformation = dsi; // ////create a entry of SummaryInformation // SummaryInformation si = PropertySetFactory.CreateSummaryInformation(); // si.Subject = "測試公司Excel檔案"; // si.Title = "測試公司Excel檔案"; // si.Author = "killysss"; // si.Comments = "謝謝您的使用!"; // workbook.SummaryInformation = si; //} ////对象保存在流中,可以通过以下方法输出到浏览器,或是保存到硬盘中: //public void SaveToFile(MemoryStream ms, string fileName) //{ // using (FileStream fs = new FileStream(fileName, FileMode.Create, FileAccess.Write)) // { // byte[] data = ms.ToArray(); // fs.Write(data, 0, data.Length); // fs.Flush(); // data = null; // } //} ////public void RenderToBrowser(MemoryStream ms, HttpContext context, string fileName) ////{ //// if (context.Request.Browser.Browser == "IE") //// fileName = HttpUtility.UrlEncode(fileName); //// context.Response.AddHeader("Content-Disposition", "attachment;fileName=" + fileName); //// context.Response.BinaryWrite(ms.ToArray()); ////} #endregion #region 导出Excel public static void ExportEasy(string strFileName) { HSSFWorkbook workbook = new HSSFWorkbook(); //ISheet sheet2 = workbook.CreateSheet("ShtDirectionary"); //sheet2.CreateRow(0).CreateCell(0).SetCellValue("Red"); //sheet2.CreateRow(1).CreateCell(0).SetCellValue("Yellow"); //sheet2.CreateRow(2).CreateCell(0).SetCellValue("Black"); //sheet2.CreateRow(3).CreateCell(0).SetCellValue("White"); //sheet2.CreateRow(4).CreateCell(0).SetCellValue("Green"); //IName range = workbook.CreateName(); //range.RefersToFormula = "ShtDirectionary!$A1:A4"; //range.NameName = "dicRange"; ISheet sheet = workbook.CreateSheet("Test"); IRow row = sheet.CreateRow(0); ICell cell1 = row.CreateCell(0); ICell cell2 = row.CreateCell(1); ICell cell3 = row.CreateCell(2); IDataFormat format = workbook.CreateDataFormat(); ICellStyle cellStyle1 = workbook.CreateCellStyle(); cellStyle1.DataFormat = format.GetFormat("yyyy-MM-dd HH:mm"); sheet.SetDefaultColumnStyle(1, cellStyle1); cell1.SetCellValue("日期列"); cell2.SetCellValue("数值列"); cell3.SetCellValue("总公司"); //CellRangeAddressList regions = new CellRangeAddressList(1, 65535, 0, 0); ////DVConstraint constraint = DVConstraint.CreateFormulaListConstraint("dicRange"); //DVConstraint constraint = DVConstraint.CreateExplicitListConstraint(new string[] { "Red", "Yellow", "Black" }); //HSSFDataValidation dataValidate1 = new HSSFDataValidation(regions, constraint); //((HSSFSheet)sheet).AddValidationData(dataValidate1); CellRangeAddressList regions1 = new CellRangeAddressList(1, 65535, 0, 0); CellRangeAddressList regions2 = new CellRangeAddressList(1, 65535, 1, 1); CellRangeAddressList regions3 = new CellRangeAddressList(1, 65535, 2, 2); // string formulation = "left(right(A1,3),1)=\".\""; // string formula = "LEFT(RIGHT(A6,3),1)=\".\""; // DVConstraint constraint = DVConstraint.CreateExplicitListConstraint(new string[] { "√", " " }); DVConstraint constraint1 = DVConstraint.CreateDateConstraint(6, "1900-01-01", null, "yyyy-MM-dd"); DVConstraint constraint2 = DVConstraint.CreateNumericConstraint(6, 6, "0", null); DVConstraint constraint3 = DVConstraint.CreateExplicitListConstraint(new string[] { "√"}); HSSFDataValidation dataValidate1 = new HSSFDataValidation(regions1, constraint1); dataValidate1.CreateErrorBox("error", "You must input a date"); // dataValidate1.EmptyCellAllowed = false; HSSFDataValidation dataValidate2 = new HSSFDataValidation(regions2, constraint2); dataValidate2.CreateErrorBox("error", "You must input a valid value!"); HSSFDataValidation dataValidate3 = new HSSFDataValidation(regions3, constraint3); dataValidate3.CreateErrorBox("error", "You must input a valid value!"); ((HSSFSheet)sheet).AddValidationData(dataValidate2); ((HSSFSheet)sheet).AddValidationData(dataValidate1); ((HSSFSheet)sheet).AddValidationData(dataValidate3); #region 已注释内容(作为参考) //cellStyle1.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin; //cellStyle1.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin; //cellStyle1.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin; //cellStyle1.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin; //cellStyle1.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; //cellStyle1.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center; //cellStyle1.ShrinkToFit = true; //ICellStyle cellStyle2 = workbook.CreateCellStyle(); //cellStyle2.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin; //cellStyle2.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin; //cellStyle2.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; //cellStyle2.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center; //cellStyle2.ShrinkToFit = true; //ICellStyle cellStyle3 = workbook.CreateCellStyle(); //cellStyle3.CloneStyleFrom(cellStyle2); //cellStyle3.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin; //ICellStyle cellStyle4 = workbook.CreateCellStyle(); //cellStyle4.CloneStyleFrom(cellStyle2); //cellStyle4.DataFormat = format.GetFormat("@"); //ICellStyle cellStyle5 = workbook.CreateCellStyle(); //cellStyle5.CloneStyleFrom(cellStyle4); //cellStyle5.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin; //// InitializeWorkbook(workbook); //sheet.SetColumnWidth(0, 15 * 256); //sheet.SetColumnWidth(1, 10 * 256); //sheet.SetColumnWidth(2, 19 * 256); //sheet.Header.Center = "zll NPOI Test"; //sheet.Footer.Left = "Copyright mitu Team"; //sheet.Footer.Right = "created by 2015"; //ICellStyle style1 = workbook.CreateCellStyle(); //// 设置字体 //IFont font1 = workbook.CreateFont(); //font1.FontName = "黑体"; //font1.FontHeightInPoints = 12; //font1.Color = NPOI.HSSF.Util.HSSFColor.Green.Index; //style1.SetFont(font1); ////设置对其方式 //style1.Alignment = HorizontalAlignment.Center; //style1.VerticalAlignment = VerticalAlignment.Center; //style1.WrapText = true; //ICellStyle cellStyle = workbook.CreateCellStyle(); //cellStyle.DataFormat = format.GetFormat("yyyy-MM-dd"); ////设置页眉页脚,打印预览 //sheet.Header.Center = "This is a test sheet"; //sheet.Footer.Left = "Copyright NPOI Team"; //sheet.Footer.Right = "created by 2015-01-20"; ////填充表头 //IRow dataRow = sheet.CreateRow(0); //dataRow.HeightInPoints = 30; //foreach (DataColumn column in dtSource.Columns) //{ // sheet.SetColumnWidth(0, 10 * 256); // ICell cell = dataRow.CreateCell(column.Ordinal); // cell.CellStyle = style1; // cell.SetCellValue(column.ColumnName); //} ////填充内容 //for (int i = 0; i < dtSource.Rows.Count; i++) //{ // dataRow = sheet.CreateRow(i + 1); // for (int j = 0; j < dtSource.Columns.Count; j++) // { // ICell cell = dataRow.CreateCell(j); // cell.SetCellValue(Convert.ToDateTime(dtSource.Rows[i][j])); // cell.CellStyle = cellStyle; // } //} #endregion //保存 using (MemoryStream ms = new MemoryStream()) { using (FileStream fs = new FileStream(strFileName, FileMode.Create, FileAccess.Write)) { workbook.Write(fs); } } }
/// <summary> /// Creates a data validation object /// </summary> /// <param name="dataValidation">The Data validation object settings</param> public void AddValidationData(HSSFDataValidation dataValidation) { if (dataValidation == null) { throw new ArgumentException("objValidation must not be null"); } DataValidityTable dvt = _sheet.GetOrCreateDataValidityTable(); DVRecord dvRecord = dataValidation.CreateDVRecord(_workbook); dvt.AddDataValidation(dvRecord); }
private static HSSFDataValidation DisplayMsg(CellRangeAddressList region, DVConstraint constraint, string errorMsg) { HSSFDataValidation dataValidate = new HSSFDataValidation(region, constraint); dataValidate.CreateErrorBox("error", errorMsg); return dataValidate; }