OfficeOpenXml导出Excel时添加数据验证

ExcelPackage.LicenseContext = OfficeOpenXml.LicenseContext.NonCommercial;
using (ExcelPackage package = new ExcelPackage())
{
	ExcelWorksheet sheet1 = package.Workbook.Worksheets.Add("Sheet1");
	ExcelWorksheet sheet2 = package.Workbook.Worksheets.Add("Sheet2");

	//创建Sheet2数据用于下拉验证
	var dataList = new List<string>() { "data1","data2","data3"};
	int rowIndex = 1;
	foreach (string data in dataList)
	{
		sheet2.Cells[rowIndex, 1].Value = data;
		rowIndex++;
	}
	//以下是两种添加数据验证的方式
	
	// 要应用数据验证的单元格范围
	var cellRange = sheet1.Cells[1, 1, 10, 1]; 		//开始行，开始列，结束行，结束列
	var dataValidation = cellRange.DataValidation.AddListDataValidation();
	dataValidation.Formula.ExcelFormula = "\"YES,NO\""; 	//可以直接写数据验证允许的值，用英文逗号分隔

	// 要应用数据验证的单元格范围
	var cellRange2 = sheet1.Cells[1, 2, 10, 2]; 	//开始行，开始列，结束行，结束列
	var dataValidation2 = cellRange2.DataValidation.AddListDataValidation();
	dataValidation2.Formula.ExcelFormula = "=Sheet2!$A:$A"; 	//也可以从其他Sheet选择

	byte[] data = package.GetAsByteArray(); 	//将Excel包保存成byte[]，用于后续处理。也可以用package的SaveAs方法保存到文件里面，按需使用
}