NPOI生成单元格（列）下拉框

客户提出能否将导入模板中，课程一列添加下拉框方便选择，不用手输入，以减少输入错误的可能性。于是在网上找了点代码，稍加整理后，形成了以下方案，代码部分：

一：生成课程列表，并放置在excel的单独sheet中。

IList<CourseCodeInfo> list = StudentBus.GetSubjectInterface().GetList(0, "", "Name");

var CourseSheetName = "Course";

var RangeName = "dicRange";

ISheet CourseSheet = workbook.CreateSheet(CourseSheetName);

CourseSheet.CreateRow(0).CreateCell(0).SetCellValue("课程列表（用于生成课程下拉框，请勿修改）");

for (var i = 1; i < list.Count; i++)

{

      CourseSheet.CreateRow(i).CreateCell(0).SetCellValue(list[i-1].Name);

}

二：生成一个列表引用区域，方便需要的的地方加载这个引用区域。

IName range = workbook.CreateName();

range.RefersToFormula = string.Format("{0}!$A$2:$A${1}",CourseSheetName,list.Count.ToString());
range.NameName = RangeName;

三：引用以上生成的引用区域。

CellRangeAddressList regions = new CellRangeAddressList(1, 65535,4, 4);

DVConstraint constraint = DVConstraint.CreateFormulaListConstraint(RangeName);

HSSFDataValidation dataValidate = new HSSFDataValidation(regions, constraint);

sheet.AddValidationData(dataValidate);

备注：代码区域二和网上的代码略有不同，网上代码一般为：

HSSFName range = hssfworkbook.CreateName();

range.Reference = "ShtDictionary!$A1:$A3";

range.NameName = "dicRange";

但在实际编写代码过程中，发现Hssfworkbook.CreateName()生成的是IName，无法直接转换为HSSFName，于是改为：

IName range = workbook.CreateName();
range.RefersToFormula = string.Format("{0}!$A$2:$A${1}",CourseSheetName,list.Count.ToString());
range.NameName = RangeName;

运行也正常，奇怪为什么和网上代码不同呢？估计与NOPI版本有关吧。

后记：近期有园子里的朋友问有无源代码，我就将原来的代码找出来，贴在下面，供各位参考（代码写的久远，请勿见笑）。

        public ActionResult GetExcelTeacherClassTemplate()

        {

            HSSFWorkbook workbook = new HSSFWorkbook();

            ISheet sheet = workbook.CreateSheet("sheet1");

            IRow row = sheet.CreateRow();

            row.CreateCell().SetCellValue("姓名");

            row.CreateCell().SetCellValue("身份证号");

            row.CreateCell().SetCellValue("年级");

            row.CreateCell().SetCellValue("班级");

            row.CreateCell().SetCellValue("课程");

            row.CreateCell().SetCellValue("角色（班主任、单科老师）");

            IRow row1 = sheet.CreateRow();

            row1.CreateCell().SetCellValue("张峰");

            row1.CreateCell().SetCellValue("");

            row1.CreateCell().SetCellValue("小学六年级");

            row1.CreateCell().SetCellValue("4班");

            row1.CreateCell().SetCellValue("语文");

            row1.CreateCell().SetCellValue("单科老师");

            var ic = workbook.CreateCellStyle();

            ic.DataFormat = HSSFDataFormat.GetBuiltinFormat("@");

            sheet.SetDefaultColumnStyle(, ic);

            sheet.SetColumnWidth(, );

            sheet.SetColumnWidth(, );

            sheet.SetColumnWidth(, );

            sheet.SetColumnWidth(, );

            IList<CourseCodeInfo> list = StudentBus.GetSubjectInterface().GetList(, "", "Name");

            var CourseSheetName = "Course";

            var RangeName = "dicRange";

            ISheet CourseSheet = workbook.CreateSheet(CourseSheetName);

            CourseSheet.CreateRow().CreateCell().SetCellValue("课程列表（用于生成课程下拉框，请勿修改）");

            for (var i = ; i < list.Count; i++)

            {

                CourseSheet.CreateRow(i).CreateCell().SetCellValue(list[i-].Name);

            }

            IName range = workbook.CreateName();

            range.RefersToFormula = string.Format("{0}!$A$2:$A${1}",CourseSheetName,list.Count.ToString());

            range.NameName = RangeName;

            //

            CellRangeAddressList regions = new CellRangeAddressList(, ,, );

            DVConstraint constraint = DVConstraint.CreateFormulaListConstraint(RangeName);

            HSSFDataValidation dataValidate = new HSSFDataValidation(regions, constraint);

            sheet.AddValidationData(dataValidate);

            System.IO.MemoryStream ms = new System.IO.MemoryStream();

            workbook.Write(ms);

            Response.AddHeader("Content-Disposition", "attachment; filename=" + Server.UrlEncode("教师授课模板.xls"));

            Response.BinaryWrite(ms.ToArray());

            workbook = null;

            ms.Close();

            ms.Dispose();

            return null;

        }

秒客网

NPOI生成单元格（列）下拉框

相关文章