NPOI 2.3

 样例

//设置员工编号单元格为文本格式  循环效率问题？
            IDataFormat dataformat = workbook.CreateDataFormat();
            ICellStyle style1 = workbook.CreateCellStyle();
            IDataFormat format = workbook.CreateDataFormat();
            style1.DataFormat = dataformat.GetFormat("text");
            for (int i = 1; i < gowthValue.LastRow; i++)
            {
                NPOI.SS.UserModel.IRow rowID = sheet1.CreateRow(i);
                ICell cell1 = rowID.CreateCell(1);
                cell1.CellStyle = style1;
            }

 

//设置员工编号单元格为文本格式 
            IDataFormat dataformat = workbook.CreateDataFormat();
            ICellStyle style1 = workbook.CreateCellStyle();
            IDataFormat format = workbook.CreateDataFormat();
            style1.DataFormat = dataformat.GetFormat("text");
            sheet1.SetDefaultColumnStyle(1, style1);

 

  /// <summary>
        /// 下载导入数据地图模板
        /// </summary>
        /// <returns></returns>
        [HttpGet]
        public FileResult DownloadMapTemplate()
        {
            NpoiMemoryStream memory = new NpoiMemoryStream();
            IWorkbook workbook = new XSSFWorkbook();
            ISheet sheet=workbook.CreateSheet("导入地图数据模板");
            IRow row = sheet.CreateRow(0);
            row.CreateCell(0).SetCellValue("项目名称");
            row.CreateCell(1).SetCellValue("地图名称");
            row.CreateCell(2).SetCellValue("地图类型");
            row.CreateCell(3).SetCellValue("经纬度");
            ISheet sheet1 = workbook.GetSheetAt(0);//获得第一个工作表  
            CellRangeAddressList regions = new CellRangeAddressList(1, 65535, 2, 2);//约束范围：c2到c65535  
            XSSFDataValidationHelper helper = new XSSFDataValidationHelper((XSSFSheet)sheet1);//获得一个数据验证Helper  
            IDataValidation validation = helper.CreateValidation(helper.CreateExplicitListConstraint(new string[]{"项目","标段","桥梁","隧道"}), regions);//创建约束
            validation.CreateErrorBox("错误", "请按右侧下拉箭头选择!");//不符合约束时的提示  
            validation.ShowErrorBox = true;//显示上面提示 = True  
            sheet1.AddValidationData(validation);//添加进去  
            sheet1.ForceFormulaRecalculation = true;  
 
 
            memory.AllowClose = false;
            workbook.Write(memory);
            memory.Flush();
            memory.Position = 0;    // 指定内存流起始值
            
            return File(memory, "application/vnd.ms-excel","地图模板下载.xlsx");
        }

 实战代码

  public FileResult DownloadExcel()
        {
            //获得配置信息
            Models.GrowthValueExcel gowthValue = Common.GrowthValueConfHelper.GetGrowthValueExcel();

            //创建XSSFWorkbook->CreateSheet
            NPOI.XSSF.UserModel.XSSFWorkbook workbook = new NPOI.XSSF.UserModel.XSSFWorkbook();
            NPOI.SS.UserModel.ISheet sheet1 = workbook.CreateSheet(gowthValue.Title);

            //指定列宽度
            sheet1.SetColumnWidth(4, 12 * 256);
            sheet1.SetColumnWidth(5, 20 * 256);

            //设置员工编号单元格为文本格式 
            IDataFormat dataformat = workbook.CreateDataFormat();
            ICellStyle style1 = workbook.CreateCellStyle();
            style1.DataFormat = dataformat.GetFormat("text");
            sheet1.SetDefaultColumnStyle(1, style1);

            //给sheet1添加第一行的头部标题
            NPOI.SS.UserModel.IRow row1 = sheet1.CreateRow(0);
            row1.CreateCell(0).SetCellValue(gowthValue.Name);

            //设置员工编号单元格为文本格式
            row1.CreateCell(1).SetCellValue(gowthValue.UserCode);
            row1.CreateCell(2).SetCellValue(gowthValue.MedalName);
            row1.CreateCell(3).SetCellValue(gowthValue.ScoreReason);
            row1.CreateCell(4).SetCellValue(gowthValue.Score);
            row1.CreateCell(5).SetCellValue(gowthValue.Time);

            //得分项下拉列表
            CellRangeAddressList regions = new CellRangeAddressList(1, gowthValue.LastRow, 2, 2);//约束范围：c2到c65535  
            XSSFDataValidationHelper helper = new XSSFDataValidationHelper((XSSFSheet)sheet1);//获得一个数据验证Helper 
            Dictionary<string, int> MedalDic = Bll.MedalCaChe.GetMedalFromDictionary();
            IDataValidation validation = helper.CreateValidation(helper.CreateExplicitListConstraint(MedalDic.Keys.ToArray()), regions);//创建约束
            validation.CreateErrorBox("错误", "请按右侧下拉箭头选择!");//不符合约束时的提示  
            validation.ShowErrorBox = true;//显示上面提示 = True  
            sheet1.AddValidationData(validation);//添加进去  
            sheet1.ForceFormulaRecalculation = true;

            //通过内存流下载
            NpoiMemoryStream memory = new NpoiMemoryStream();
            memory.AllowClose = false;
            workbook.Write(memory);
            memory.Flush();
            memory.Position = 0;
            return File(memory, "application/vnd.ms-excel", $"{gowthValue.Title}.xlsx");
        }