安裝 依賴庫：NPOI

創(chuàng)建XSSFWorkbook和Sheet
設(shè)置表頭行
添加數(shù)據(jù)驗(yàn)證：
身份證列用COUNTIF公式確保唯一
地址相關(guān)列鎖定固定值
學(xué)段列設(shè)置三級(jí)下拉
固定值通過單元格賦值實(shí)現(xiàn)
輸出

using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using NPOI.SS.Util;
using System.IO;/// <summary>
/// 下載模板
/// </summary>
/// <returns></returns>
public ActionResult DownloadTemplate(DownloadTemplateDto input) {if (input.total< 1) throw new UserFriendlyException("人數(shù)不能為0");var stream = GenerateExcel(input.total);var data= stream.ToArray();return new FileContentResult(data, "application/octet-stream");//application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
}private MemoryStream GenerateExcel(int rowTotal)
{rowTotal = rowTotal + 10;//總行數(shù)// 1. 創(chuàng)建工作簿和工作表IWorkbook workbook = new XSSFWorkbook();ISheet sheet = workbook.CreateSheet("基礎(chǔ)信息");// 創(chuàng)建文本格式樣式（用于身份證號(hào)列）ICellStyle textStyle = workbook.CreateCellStyle();IDataFormat textFormat = workbook.CreateDataFormat();textStyle.DataFormat = textFormat.GetFormat("@"); // "@" 表示文本格式// 2. 創(chuàng)建列名行// 2. 設(shè)置列名（共12列）string[] headers = { "姓名", "身份證號(hào)", "現(xiàn)住省", "現(xiàn)住市", "現(xiàn)住區(qū)", "聯(lián)系電話","詳細(xì)地址", "學(xué)校名稱", "學(xué)校編號(hào)", "學(xué)段", "年級(jí)", "班級(jí)" };IRow headerRow = sheet.CreateRow(0);for (int i = 0; i < headers.Length; i++){headerRow.CreateCell(i).SetCellValue(headers[i]);}// 3. 設(shè)置數(shù)據(jù)驗(yàn)證規(guī)則IDataValidationHelper validationHelper = sheet.GetDataValidationHelper();// 規(guī)則1：身份證號(hào)列唯一性驗(yàn)證CellRangeAddressList idRange = new CellRangeAddressList(1, rowTotal, 1, 1); // B列IDataValidationConstraint idConstraint = validationHelper.CreateCustomConstraint("COUNTIF(B:B, B1)=1");IDataValidation idValidation = validationHelper.CreateValidation(idConstraint, idRange);idValidation.CreateErrorBox("重復(fù)錯(cuò)誤", "身份證號(hào)必須唯一！");sheet.AddValidationData(idValidation);// 規(guī)則2：固定值驗(yàn)證（省、市、區(qū)、學(xué)校名稱、學(xué)校編號(hào)）SetFixedValue(sheet, 2, "北京市", rowTotal); // C列：現(xiàn)住省SetFixedValue(sheet, 3, "昌平區(qū)", rowTotal); // D列：現(xiàn)住市SetFixedValue(sheet, 4, "A街道", rowTotal); // E列：街道SetFixedValue(sheet, 7, deptName, rowTotal); // H列：學(xué)校名稱SetFixedValue(sheet, 8, deptId, rowTotal); // I列：學(xué)校編號(hào)// 規(guī)則3：學(xué)段下拉選擇（小學(xué)/初中/高中）CellRangeAddressList stageRange = new CellRangeAddressList(1, rowTotal, 9, 9); // J列IDataValidationConstraint stageConstraint = validationHelper.CreateExplicitListConstraint(new string[] { "小學(xué)", "初中", "高中", "高三", "中專", "技校", "大學(xué)" });IDataValidation stageValidation = validationHelper.CreateValidation(stageConstraint, stageRange);stageValidation.CreateErrorBox("輸入錯(cuò)誤", "請(qǐng)選擇下拉選中對(duì)應(yīng)值！");sheet.AddValidationData(stageValidation);// 規(guī)則4：年級(jí)CellRangeAddressList grade = new CellRangeAddressList(1, rowTotal, 10, 10); // K列IDataValidationConstraint gradeConstraint = validationHelper.CreateExplicitListConstraint(new string[] { "一年級(jí)", "二年級(jí)", "三年級(jí)", "四年級(jí)", "五年級(jí)", "六年級(jí)","初一", "初二", "初三", "高一", "高二", "高三", "大一", "大二", "大三", "大四" });IDataValidation gradeValidation = validationHelper.CreateValidation(gradeConstraint, grade);gradeValidation.CreateErrorBox("輸入錯(cuò)誤", "請(qǐng)選擇下拉選中對(duì)應(yīng)值！");sheet.AddValidationData(gradeValidation);for (int rowIndex = 1; rowIndex <= rowTotal; rowIndex++){IRow row = sheet.GetRow(rowIndex) ?? sheet.CreateRow(rowIndex);ICell idCell = row.GetCell(idCardColumnIndex) ?? row.CreateCell(idCardColumnIndex);// 應(yīng)用文本樣式（即使單元格為空）idCell.CellStyle = textStyle;// 如果已有數(shù)據(jù)，確保以文本格式存儲(chǔ)if (idCell.StringCellValue != null){idCell.SetCellValue(idCell.StringCellValue);}
}// 5. 流式輸出MemoryStream stream = new MemoryStream();workbook.Write(stream, true);stream.Position = 0; // 重置流位置 return stream;
}// 輔助方法：為整列設(shè)置固定值 添加樣式參數(shù)
private void SetFixedValue(ISheet sheet, int columnIndex, string value, int rowTotal, ICellStyle textStyle)
{for (int rowIndex = 1; rowIndex <= rowTotal; rowIndex++){IRow row = sheet.GetRow(rowIndex) ?? sheet.CreateRow(rowIndex);ICell cell = row.GetCell(columnIndex) ?? row.CreateCell(columnIndex);cell.SetCellValue(value);// 如果是身份證列（索引1），應(yīng)用文本樣式if (columnIndex == 1){cell.CellStyle = textStyle;}}
}

測試

修改下載后的文件后綴為xlsx格式即可打開