NPOI,给指定的excle创建个下拉框验证
先大致看下效果吧
Nuget 搜索 NPOI,一般出来的第一个就是,安装NPOI基础环境
using NPOI.HSSF.UserModel;
using NPOI.OpenXmlFormats.Spreadsheet;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Data;
using System.Windows.Documents;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Imaging;
using System.Windows.Navigation;
using System.Windows.Shapes;
using Path = System.IO.Path;
namespace NPOIDemo
{
/// <summary>
/// MainWindow.xaml 的交互逻辑
/// </summary>
public partial class MainWindow : Window
{
public MainWindow()
{
InitializeComponent();
var ff = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "服务器导入模板.xlsx");
CreateCellDropDownList(
ff,
new string[] { "1111", "2222", "3333", "4444" },
1, 4, 3, 3
);
System.Diagnostics.Process.Start(ff);
}
/// <summary>
/// 给指定的excle添加个下拉框验证
/// 备注:只能给未添加下拉框验证的添加,追加的情况不理想
/// </summary>
/// <param name="exclepath">excle路径</param>
/// <param name="dropDownConstraint">有效值集合</param>
public void CreateCellDropDownList(string exclepath, string[] dropDownlist, int firstRow = 0, int lastRow = 65535, int firstCol = 0, int lastCol = 256)
{
FileInfo file = new FileInfo(exclepath);
if (!file.Exists)
{
throw new FileNotFoundException("Excle文件不存在!");
}
if (dropDownlist == null || dropDownlist.Length == 0)
{
throw new ValueUnavailableException("有效值集合不允许为空!");
}
if (firstRow < 0 || lastRow < 0 || firstCol < 0 || lastCol < 0)
{
throw new IndexOutOfRangeException("索引值下标有误,从0开始!");
}
file.IsReadOnly = false;
IWorkbook workbook = null;
using (FileStream fs = File.Open(file.FullName, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
{
workbook = new XSSFWorkbook(fs);
}
ISheet sheet = workbook.GetSheetAt(0);
//设置默认值
for (int irow = firstRow, length = lastRow; irow <= length; irow++)
{
var row = sheet.GetRow(irow);
if (row == null)
{
continue;
}
for (int icell = firstCol, lengthc = lastCol; icell <= lengthc; icell++)
{
var cell = row.GetCell(icell);
if (cell == null)
{
continue;
}
cell.SetCellValue(dropDownlist.First());
}
}
//设置生成下拉框的行和列
CellRangeAddressList cellRegions = new CellRangeAddressList(firstRow, lastRow, firstCol, lastCol);
XSSFDataValidationHelper helper = new XSSFDataValidationHelper((XSSFSheet)sheet);
//启用下拉验证
var dropDownConstraint = helper.CreateExplicitListConstraint(dropDownlist);
IDataValidation dropDownValidation = helper.CreateValidation(dropDownConstraint, cellRegions);
sheet.AddValidationData(dropDownValidation);
sheet.ValidateMergedRegions();
var filenew = Path.Combine(file.DirectoryName, "filenew" + Path.GetExtension(file.Name));
using (FileStream fs = File.Open(filenew, FileMode.OpenOrCreate, FileAccess.ReadWrite))
{
workbook.Write(fs);
}
//因为暂时无法解决编辑的情况,所以迂回下,删除原始再覆盖下
File.Delete(file.FullName);
File.Move(filenew, file.FullName);
}
}
}
特此说明下:添加下拉框数据验证在已经存在的情况不理想,后面生成的会失效,暂时没找到解决办法,只有每次生成的数据操作一个空数据验证的模板