样例
//设置员工编号单元格为文本格式 循环效率问题？ 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"); }