c# npoi创建数据有效性约束制定列和单元格分别设置

葩熊

已于 2023-10-07 17:30:52 修改

阅读量414

点赞数

文章标签：数据库前端 c#

于 2023-10-07 17:29:27 首次发布

本文链接：https://blog.csdn.net/sinat_36500530/article/details/133648170

版权

public ActionResult Export()
{

var codes
string templateFileName = Path.Combine(Server.MapPath("/ExcelTemplate"), "Auto.xlsx");

var fileName = @"Auto.xlsx";

ExportExcelXlsxHelper ex = new ExportExcelXlsxHelper();
ex.InitEmptyExcel(); // 初始化后, 可以在外部引用excelWorkbook, currentSheet
var excelWorkbook = ex.excelWorkbook;
using (FileStream file = new FileStream(templateFileName, FileMode.Open, FileAccess.Read))
{
excelWorkbook = new XSSFWorkbook(file);

file.Close();
}
var currentSheet = (XSSFSheet)excelWorkbook.GetSheet("Sheet1");
ISheet sheet2 = (XSSFSheet)excelWorkbook.GetSheet("options");
// 创建数据有效性约束并应用于单元格范围
XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper(currentSheet);
var logisticCompany = codes.Where(o => o.TypeId == 77).Select(o => o.TextValue).ToArray();
if (logisticCompany != null && logisticCompany.Length != 0)
{

CellRangeAddressList rangeLogisticCompany = new CellRangeAddressList(1, 100, 1, 1); // 列1，行1到100
XSSFDataValidationConstraint constraintLogisticCompany = (XSSFDataValidationConstraint)
dvHelper.CreateExplicitListConstraint(logisticCompany);

XSSFDataValidation dataValidationLogisticCompany = (XSSFDataValidation)
dvHelper.CreateValidation(constraintLogisticCompany, rangeLogisticCompany);

dataValidationLogisticCompany.ShowErrorBox = true; // 如果需要显示错误提示框

currentSheet.AddValidationData(dataValidationLogisticCompany);

}
var from = codes.ToArray();
var fromNo =ToArray();
if (from != null && from.Length != 0)
{
for (int rowIndex = 0; rowIndex < from.Length; rowIndex++)
{
IRow row = sheet2.GetRow(rowIndex);
if (row == null)
{
row = sheet2.CreateRow(rowIndex);
}
ICell cell = row.GetCell(4); // 指定列索引
if (cell == null)
{
cell = row.CreateCell(4);
}
// 设置单元格的值
cell.SetCellValue(from[rowIndex]);

}
}
if (fromNo!= null && fromNo.Length != 0)
{
for (int rowIndex = 0; rowIndex < fromNo.Length; rowIndex++)
{
IRow row = sheet2.GetRow(rowIndex);
if (row == null)
{
row = sheet2.CreateRow(rowIndex);
}
ICell cell = row.GetCell(5); // 指定列索引
if (cell == null)
{
cell = row.CreateCell(5);
}
// 设置单元格的值
cell.SetCellValue(fromNo[rowIndex]);
}
}

for (int i = 1; i <= 100; i++)
{
var j = i+ 1;
string formula = "IF($A$"+ j + " = \"OverSea\", 'options'!$E$1:$E$" + from.Length+ ",'options'!$F$1:$F$" + fromNo.Length+" )";

CellRangeAddressList rangeTo = new CellRangeAddressList(i, i, 4, 4); // 列1，行1到100
XSSFDataValidationConstraint constraintTo = (XSSFDataValidationConstraint)
dvHelper.CreateFormulaListConstraint(formula);

XSSFDataValidation dataValidationTo = (XSSFDataValidation)
dvHelper.CreateValidation(constraintTo, rangeTo);

dataValidationTo.ShowErrorBox = true; // 如果需要显示错误提示框
currentSheet.AddValidationData(dataValidationTo);
}
var toOversea = codes.Where(o => o.TypeId == 78).Select(o =>o.TextValue).ToArray();
var toNoOversea=Business.WareHouse.LoadWareHouse().GroupBy(o => o.City).Select(group => group.First().City).Distinct().Where(o=>!string.IsNullOrEmpty(o)).ToArray();
if (toOversea != null && toOversea.Length != 0)
{
for (int rowIndex = 0; rowIndex < toOversea.Length; rowIndex++)
{
IRow row = sheet2.GetRow(rowIndex);
if (row == null)
{
row = sheet2.CreateRow(rowIndex);
}
ICell cell = row.GetCell(6); // 指定列索引
if (cell == null)
{
cell = row.CreateCell(6);
}
// 设置单元格的值
cell.SetCellValue(toOversea[rowIndex]);

}
}
if (toNoOversea != null && toNoOversea.Length != 0)
{
for (int rowIndex = 0; rowIndex < toNoOversea.Length; rowIndex++)
{
IRow row = sheet2.GetRow(rowIndex);
if (row == null)
{
row = sheet2.CreateRow(rowIndex);
}
ICell cell = row.GetCell(7); // 指定列索引
if (cell == null)
{
cell = row.CreateCell(7);
}
// 设置单元格的值
cell.SetCellValue(toNoOversea[rowIndex]);
}
}

for (int i = 1; i <= 100; i++)
{
var j = i + 1;
string formula = "IF($A$" + j + " = \"OverSea\", 'options'!$G$1:$G$" + toOversea.Length + ",'options'!$H$1:$H$" + toNoOversea.Length + " )";

CellRangeAddressList rangeTo = new CellRangeAddressList(i, i, 5, 5); // 列1，行1到100
XSSFDataValidationConstraint constraintTo = (XSSFDataValidationConstraint)
dvHelper.CreateFormulaListConstraint(formula);

XSSFDataValidation dataValidationTo = (XSSFDataValidation)
dvHelper.CreateValidation(constraintTo, rangeTo);

dataValidationTo.ShowErrorBox = true; // 如果需要显示错误提示框
currentSheet.AddValidationData(dataValidationTo);
}

ExportExcelXlsxHelper.NPOIMemoryStream ms = new ExportExcelXlsxHelper.NPOIMemoryStream();
excelWorkbook.Write(ms);
ex.DownLoad(fileName, ms);
ms = null;
excelWorkbook.Clear();
excelWorkbook.Close();

GC.Collect();

return new EmptyResult();
}
}
}