NPOI，给指定的excle创建个下拉框验证
NPOI，给指定的excle创建个下拉框验证
先大致看下效果吧
Nuget 搜索 NPOI，一般出来的第一个就是，安装NPOI基础环境
1 using NPOI.HSSF.UserModel;
2 using NPOI.OpenXmlFormats.Spreadsheet;
3 using NPOI.SS.UserModel;
4 using NPOI.SS.Util;
5 using NPOI.XSSF.UserModel;
6 using System;
7 using System.Collections.Generic;
8 using System.IO;
9 using System.Linq;
10 using System.Text;
11 using System.Threading.Tasks;
12 using System.Windows;
13 using System.Windows.Controls;
14 using System.Windows.Data;
15 using System.Windows.Documents;
16 using System.Windows.Input;
17 using System.Windows.Media;
18 using System.Windows.Media.Imaging;
19 using System.Windows.Navigation;
20 using System.Windows.Shapes;
21 using Path = System.IO.Path;
22
23 namespace NPOIDemo
24 {
25 /// <summary>
26 /// MainWindow.xaml 的交互逻辑
27 /// </summary>
28 public partial class MainWindow : Window
29 {
30 public MainWindow()
31 {
32 InitializeComponent();
33
34 var ff = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "服务器导入模板.xlsx");
35 CreateCellDropDownList(
36 ff,
37 new string[] { "1111", "2222", "3333", "4444" },
38 1, 4, 3, 3
39 );
40 System.Diagnostics.Process.Start(ff);
41 }
42
43 /// <summary>
44 /// 给指定的excle添加个下拉框验证
45 /// 备注：只能给未添加下拉框验证的添加，追加的情况不理想
46 /// </summary>
47 /// <param name="exclepath">excle路径</param>
48 /// <param name="dropDownConstraint">有效值集合</param>
49 public void CreateCellDropDownList(string exclepath, string[] dropDownlist, int firstRow = 0, int lastRow = 65535, int firstCol = 0, int lastCol = 256)
50 {
51 FileInfo file = new FileInfo(exclepath);
52 if (!file.Exists)
53 {
54 throw new FileNotFoundException("Excle文件不存在！");
55 }
56 if (dropDownlist == null || dropDownlist.Length == 0)
57 {
58 throw new ValueUnavailableException("有效值集合不允许为空！");
59 }
60 if (firstRow < 0 || lastRow < 0 || firstCol < 0 || lastCol < 0)
61 {
62 throw new IndexOutOfRangeException("索引值下标有误，从0开始！");
63 }
64 file.IsReadOnly = false;
65
66 IWorkbook workbook = null;
67
68 using (FileStream fs = File.Open(file.FullName, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
69 {
70 workbook = new XSSFWorkbook(fs);
71 }
72
73 ISheet sheet = workbook.GetSheetAt(0);
74
75 //设置默认值
76
77 for (int irow = firstRow, length = lastRow; irow <= length; irow++)
78 {
79 var row = sheet.GetRow(irow);
80 if (row == null)
81 {
82 continue;
83 }
84
85 for (int icell = firstCol, lengthc = lastCol; icell <= lengthc; icell++)
86 {
87 var cell = row.GetCell(icell);
88 if (cell == null)
89 {
90 continue;
91 }
92 cell.SetCellValue(dropDownlist.First());
93 }
94 }
95
96 //设置生成下拉框的行和列
97 CellRangeAddressList cellRegions = new CellRangeAddressList(firstRow, lastRow, firstCol, lastCol);
98
99 XSSFDataValidationHelper helper = new XSSFDataValidationHelper((XSSFSheet)sheet);
100 //启用下拉验证
101 var dropDownConstraint = helper.CreateExplicitListConstraint(dropDownlist);
102 IDataValidation dropDownValidation = helper.CreateValidation(dropDownConstraint, cellRegions);
103 sheet.AddValidationData(dropDownValidation);
104 sheet.ValidateMergedRegions();
105
106 var filenew = Path.Combine(file.DirectoryName, "filenew" + Path.GetExtension(file.Name));
107 using (FileStream fs = File.Open(filenew, FileMode.OpenOrCreate, FileAccess.ReadWrite))
108 {
109 workbook.Write(fs);
110 }
111
112 //因为暂时无法解决编辑的情况，所以迂回下，删除原始再覆盖下
113 File.Delete(file.FullName);
114 File.Move(filenew, file.FullName);
115 }
116 }
117 }
特此说明下：添加下拉框数据验证在已经存在的情况不理想，后面生成的会失效，暂时没找到解决办法，只有每次生成的数据操作一个空数据验证的模板
