NPOI导入excel
2024-09-02 10:50:20
1、引用NPOI；
using NPOI.HSSF.UserModel;
using NPOI.HSSF.Util;
using NPOI.SS.UserModel;
2、导出excel
1 private void btnadd_MouseUp(object sender, MouseButtonEventArgs e)
2 {
3 try
4 {
5 #region 打印导出无统计数据
6 if (dt != null && dt.Rows.Count > 0)
7 {
8 //创建工作薄
9 HSSFWorkbook wb = new HSSFWorkbook();
10 //创建一个名称为mySheet的表
11 ISheet sh = wb.CreateSheet("mySheet");
12 #region 设置表格内容
13 for (int i = 0; i < dt.Rows.Count; i++)
14 {
15 SetRow(wb, sh, i * 2);//设置表头
16 IRow row = sh.CreateRow(i * 2 + 1);
17 for (int j = 2; j < dt.Columns.Count - 7; j++)
18 {
19 if (j < 4)
20 {
21 string content = dt.Rows[i][j].ToString();
22 ICell cell = row.CreateCell(j - 2);
23 cell.SetCellValue(content);
24 }
25 else if (j > 4)
26 {
27 string content = dt.Rows[i][j].ToString();
28 ICell cell = row.CreateCell(j - 3);
29 cell.SetCellValue(content);
30 }
31 }
32 }
33 string saveFileName = "人员工资表.xls";
34 //FileStream fs=new FileStream();
35 SaveFileDialog saveDialog = new SaveFileDialog();
36 saveDialog.DefaultExt = "xls";
37 saveDialog.Filter = "Excel文件|*.xls";
38 saveDialog.FileName = saveFileName;
39 saveDialog.ShowDialog();
40 saveFileName = saveDialog.FileName;
41 if (saveFileName.IndexOf(":") < 0) return; //被点了取消
42 if (saveFileName != "")
43 {
44 using (FileStream fs = File.OpenWrite(saveDialog.FileName))//打开一个xls文件，如果没有则自行创建，如果存在myxls.xls文件则在创建是不要打开该文件
45 {
46 try
47 {
48 wb.Write(fs);
49 MessageBox.Show("导出成功！", "提示", MessageBoxButton.OK, MessageBoxImage.Asterisk);
50 }
51 catch (Exception ex)
52 {
53 MessageBox.Show("导出文件时出错,文件可能正被打开！\n" + ex.Message, "提示", MessageBoxButton.OK, MessageBoxImage.Error);
54 }
55 fs.Flush();
56 fs.Dispose();
57 fs.Close();
58 }
59 }
60 else
61 {
62 MessageBox.Show("请选择数据源！");
63 }
64 #endregion
65 }
66 else
67 {
68 MessageBox.Show("请选择数据源！");
69 }
70 #endregion
71 }
72 catch (Exception ex)
73 {
74 MessageBox.Show(ex.Message, "提示", MessageBoxButton.OK, MessageBoxImage.Error);
75 }
76 }
3、导入excel
1 private void btnexport_MouseUp(object sender, MouseButtonEventArgs e)
2 {
3 try
4 {
5 string str = "";
6 string Error = "";
7 OpenFileDialog of = new OpenFileDialog();
8 of.DefaultExt = "xls";
9 of.Filter = "Excel文件|*.xls";
10 of.ShowDialog();
11 if (string.IsNullOrEmpty(of.FileName))
12 {
13 return;
14 }
15 if (of.CheckFileExists == true) //路径存在
16 {
17 string path = of.FileName;
18 using (FileStream fs = File.OpenRead(path)) //打开myxls.xls文件
19 {
20 HSSFWorkbook wk = new HSSFWorkbook(fs); //把xls文件中的数据写入wk中
21 #region 验证
22 for (int i = 0; i < wk.NumberOfSheets; i++) //NumberOfSheets是myxls.xls中总共的表数
23 {
24 ISheet sheet = wk.GetSheetAt(i); //读取当前表数据
25 for (int j = 1; j <= sheet.LastRowNum; j++) //LastRowNum 是当前表的总行数
26 {
27 IRow row = sheet.GetRow(j); //读取当前行数据
28 if (row != null)
29 {
30 for (int k = 0; k <= row.LastCellNum; k++) //LastCellNum 是当前行的总列数
31 {
32 if (k > 29)
33 {
34 break;
35 }
36 ICell cell = row.GetCell(k); //当前表格
37 if (cell != null)
38 {
39 string content = cell.ToString();
40 #region 验证
41
42 if (k > 3 && k < 30)
43 {
44 if (!new System.Text.RegularExpressions.Regex(@"^(([1-9]{1}\d*)|([0]{1}))(\.(\d){1,2})?$").IsMatch(content.Trim()))
45 {
46 Error += "表" + (i + 1) + "行" + (j + 1) + "列" + (k + 1) + "中有非法字符;\r\n";
47 }
48 }
49 else if (k == 1)
50 {
51 if (!new System.Text.RegularExpressions.Regex(@"^(([1-9]{1}\d*)|([0]{1}))(\.(\d){1,2})?$").IsMatch(content.Trim()))
52 {
53 Error += "表" + (i + 1) + "行" + (j + 1) + "列" + (k + 1) + "中有非法字符;\r\n";
54 }
55 }
56 #endregion
57 }
58 }
59 }
60 }
61 }
62 if (Error.Length > 0)
63 {
64 MessageBox.Show(Error + "请验证！", "提示", MessageBoxButton.OK, MessageBoxImage.Error);
65 return;
66 }
67
68 #endregion
69
70 for (int i = 0; i < wk.NumberOfSheets; i++) //NumberOfSheets是myxls.xls中总共的表数
71 {
72 ISheet sheet = wk.GetSheetAt(i); //读取当前表数据
73 string depid = "";
74 string userid = "";
75 string deptname = "";
76 for (int j = 1; j <= sheet.LastRowNum; j++) //LastRowNum 是当前表的总行数
77 {
78 IRow row = sheet.GetRow(j); //读取当前行数据
79 if (row != null)
80 {
81 str = str + "insert into wage(depid,depname,mon,userid,username,code,gwgz,xl,jishu,zili,jbgz,gl,weisheng,menzhen,tizu,zjjt,jiaotong,zinv,zbbt,jixiao,bufa,ycxj,yjlgz,yfhj,jfz,baoyang,yibao,shiye,fangjin,nianjin,nashui,sfgz,createdate) values(";
82 for (int k = 0; k <= row.LastCellNum; k++) //LastCellNum 是当前行的总列数
83 {
84 if (k > 29)
85 {
86 break;
87 }
88 ICell cell = row.GetCell(k); //当前表格
89 if (cell != null)
90 {
91 string content = cell.ToString();
92
93 #region 验证
94 if (!string.IsNullOrEmpty(content))
95 {
96 if (k == 0)
97 {
98 string sql = @"select depid,userid from wage where depname='" + content + "'";
99 DataSet ds = new DataBase().GetDataSet(sql);
100 DataTable newDT = ds.Tables[0];
101 depid = newDT.Rows[0][0].ToString();
102 userid = newDT.Rows[0][1].ToString();
103 if (string.IsNullOrEmpty(depid))
104 {
105 depid = "99999";
106 }
107 if (string.IsNullOrEmpty(userid))
108 {
109 userid = "1122222";
110 }
111 deptname = content;
112 str = str + "" + depid + ",'" + deptname + "',";
113 }
114 else
115 {
116 if (k == 1)
117 { str = str + content + ","; }
118 else if (k == 2)
119 {
120 str = str + "" + userid + ",'" + content + "',";
121 }
122 else
123 {
124 if (k > 3)
125 {
126 if (!new System.Text.RegularExpressions.Regex(@"^(([1-9]{1}\d*)|([0]{1}))(\.(\d){1,2})?$").IsMatch(content.Trim()))
127 {
128 MessageBox.Show("文件数据内有非数字，请修改！");
129 }
130 else
131 {
132 str = str + "'" + content + "',";
133 }
134 }
135 else
136 {
137 str = str + "'" + content + "',";
138 }
139 }
140 }
141 }
142 else
143 {
144 MessageBox.Show("文件内有空数据，请重新导入！");
145 }
146 #endregion
147 if (i == sheet.LastRowNum - 2)
148 {
149 break;
150 }
151 }
152 else
153 {
154 MessageBox.Show("文件为空，请重新导入！");
155 }
156 }
157 //str = str.ToString().Substring(0, str.Length - 1);
158 str = str + "'" + DateTime.Now + "'";
159 str = str + ");";
160 }
161 int result = new DataBase().ExecuteSQL(str);
162 str = "";
163 }
164 }
165 MessageBox.Show("导入成功", "提示", MessageBoxButton.OK, MessageBoxImage.Asterisk);
166 //重新绑定
167 BindData(int.Parse(common.SelectedValue.ToString()));
168 }
169 }
170 else
171 {
172 MessageBox.Show("文件不存在", "提示", MessageBoxButton.OK, MessageBoxImage.Error);
173 }
174 }
175 catch (Exception ex)
176 {
177 MessageBox.Show(ex.Message, "提示", MessageBoxButton.OK, MessageBoxImage.Error);
178 }
179
180 }
4、公共方法
1 /// <summary>
2 /// 打印导出表头
3 /// </summary>
4 /// <param name="wb"></param>
5 /// <param name="sh"></param>
6 /// <param name="num"></param>
7 /// <returns></returns>
8 public IRow SetRow(HSSFWorkbook wb, ISheet sh, int num)
9 {
10 #region 设置表头
11 IRow row1 = sh.CreateRow(num);
12 row1.Height = 22 * 22;
13 ICell icell1top = row1.CreateCell(0);
14 icell1top.CellStyle = Getcellstyle(wb, stylexls.头);
15 icell1top.SetCellValue("部门");
16 ICell icell2top = row1.CreateCell(1);
17 icell2top.CellStyle = Getcellstyle(wb, stylexls.头);
18 icell2top.SetCellValue("月份");
19 ICell icell3top = row1.CreateCell(2);
20 icell3top.CellStyle = Getcellstyle(wb, stylexls.头);
21 icell3top.SetCellValue("职员");
22 ICell icell4top = row1.CreateCell(3);
23 icell4top.CellStyle = Getcellstyle(wb, stylexls.头);
24 icell4top.SetCellValue("人员编码");
25 ICell icell5top = row1.CreateCell(4);
26 icell5top.CellStyle = Getcellstyle(wb, stylexls.头);
27 icell5top.SetCellValue("岗位工资");
28 ICell icell6top = row1.CreateCell(5);
29 icell6top.CellStyle = Getcellstyle(wb, stylexls.头);
30 icell6top.SetCellValue("学历");
31 ICell icell7top = row1.CreateCell(6);
32 icell7top.CellStyle = Getcellstyle(wb, stylexls.头);
33 icell7top.SetCellValue("技术");
34 ICell icell8top = row1.CreateCell(7);
35 icell8top.CellStyle = Getcellstyle(wb, stylexls.头);
36 icell8top.SetCellValue("资历");
37 ICell icell9top = row1.CreateCell(8);
38 icell9top.CellStyle = Getcellstyle(wb, stylexls.头);
39 icell9top.SetCellValue("基本工资");
40 ICell icell10top = row1.CreateCell(9);
41 icell10top.CellStyle = Getcellstyle(wb, stylexls.头);
42 icell10top.SetCellValue("工龄");
43 ICell icell11top = row1.CreateCell(10);
44 icell11top.CellStyle = Getcellstyle(wb, stylexls.头);
45 icell11top.SetCellValue("卫生");
46 ICell icell12top = row1.CreateCell(11);
47 icell12top.CellStyle = Getcellstyle(wb, stylexls.头);
48 icell12top.SetCellValue("门诊");
49 ICell icell13top = row1.CreateCell(12);
50 icell13top.CellStyle = Getcellstyle(wb, stylexls.头);
51 icell13top.SetCellValue("提租");
52 ICell icell14top = row1.CreateCell(13);
53 icell14top.CellStyle = Getcellstyle(wb, stylexls.头);
54 icell14top.SetCellValue("专家津贴");
55 ICell icell15top = row1.CreateCell(14);
56 icell15top.CellStyle = Getcellstyle(wb, stylexls.头);
57 icell15top.SetCellValue("专家交通");
58 ICell icell16top = row1.CreateCell(15);
59 icell16top.CellStyle = Getcellstyle(wb, stylexls.头);
60 icell16top.SetCellValue("子女");
61 ICell icell17top = row1.CreateCell(16);
62 icell17top.CellStyle = Getcellstyle(wb, stylexls.头);
63 icell17top.SetCellValue("值班补贴");
64 ICell icell18top = row1.CreateCell(17);
65 icell18top.CellStyle = Getcellstyle(wb, stylexls.头);
66 icell18top.SetCellValue("绩效");
67 ICell icell19top = row1.CreateCell(18);
68 icell19top.CellStyle = Getcellstyle(wb, stylexls.头);
69 icell19top.SetCellValue("补发");
70 ICell icell20top = row1.CreateCell(19);
71 icell20top.CellStyle = Getcellstyle(wb, stylexls.头);
72 icell20top.SetCellValue("一次性奖");
73 ICell icell21top = row1.CreateCell(20);
74 icell21top.CellStyle = Getcellstyle(wb, stylexls.头);
75 icell21top.SetCellValue("月奖励工资");
76 ICell icell22top = row1.CreateCell(21);
77 icell22top.CellStyle = Getcellstyle(wb, stylexls.头);
78 icell22top.SetCellValue("应发合计");
79 ICell icell23top = row1.CreateCell(22);
80 icell23top.CellStyle = Getcellstyle(wb, stylexls.头);
81 icell23top.SetCellValue("局租房");
82 ICell icell24top = row1.CreateCell(23);
83 icell24top.CellStyle = Getcellstyle(wb, stylexls.头);
84 icell24top.SetCellValue("保养");
85 ICell icell25top = row1.CreateCell(24);
86 icell25top.CellStyle = Getcellstyle(wb, stylexls.头);
87 icell25top.SetCellValue("医保");
88 ICell icell26top = row1.CreateCell(25);
89 icell26top.CellStyle = Getcellstyle(wb, stylexls.头);
90 icell26top.SetCellValue("失业");
91 ICell icell27top = row1.CreateCell(26);
92 icell27top.CellStyle = Getcellstyle(wb, stylexls.头);
93 icell27top.SetCellValue("房金");
94 ICell icell28top = row1.CreateCell(27);
95 icell28top.CellStyle = Getcellstyle(wb, stylexls.头);
96 icell28top.SetCellValue("年金");
97 ICell icell29top = row1.CreateCell(28);
98 icell29top.CellStyle = Getcellstyle(wb, stylexls.头);
99 icell29top.SetCellValue("纳税");
100 ICell icell30top = row1.CreateCell(29);
101 icell30top.CellStyle = Getcellstyle(wb, stylexls.头);
102 icell30top.SetCellValue("实发工资");
103 #endregion
104 return row1;
105 }
1 #region 定义单元格常用到样式的枚举
2 public enum stylexls
3 {
4 头,
5 url,
6 时间,
7 数字,
8 钱,
9 百分比,
10 中文大写,
11 科学计数法,
12 默认
13 }
14 #endregion
15 #region 定义单元格常用到的样式
16 static ICellStyle Getcellstyle(IWorkbook wb, stylexls str)
17 {
18 ICellStyle cellStyle = wb.CreateCellStyle();
19
20 //定义几种字体
21 //也可以一种字体，写一些公共属性，然后在下面需要时加特殊的
22 IFont font12 = wb.CreateFont();
23 font12.FontHeightInPoints = 10;
24 font12.FontName = "微软雅黑";
25
26
27 IFont font = wb.CreateFont();
28 font.FontName = "微软雅黑";
29 //font.Underline = 1;下划线
30
31
32 IFont fontcolorblue = wb.CreateFont();
33 fontcolorblue.Color = HSSFColor.OliveGreen.Blue.Index;
34 fontcolorblue.IsItalic = true;//下划线
35 fontcolorblue.FontName = "微软雅黑";
36
37
38 //边框
39 cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
40 cellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
41 cellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
42 cellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
43 //水平对齐
44 cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Left;
45
46 //垂直对齐
47 cellStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
48
49 //自动换行
50 cellStyle.WrapText = true;
51
52 //缩进;当设置为1时，前面留的空白太大了。希旺官网改进。或者是我设置的不对
53 cellStyle.Indention = 0;
54
55 //上面基本都是设共公的设置
56 //下面列出了常用的字段类型
57 switch (str)
58 {
59 case stylexls.头:
60 // cellStyle.FillPattern = FillPatternType.LEAST_DOTS;
61 cellStyle.SetFont(font12);
62 break;
63 case stylexls.时间:
64 IDataFormat datastyle = wb.CreateDataFormat();
65
66 cellStyle.DataFormat = datastyle.GetFormat("yyyy/mm/dd");
67 cellStyle.SetFont(font);
68 break;
69 case stylexls.数字:
70 cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00");
71 cellStyle.SetFont(font);
72 break;
73 case stylexls.钱:
74 IDataFormat format = wb.CreateDataFormat();
75 cellStyle.DataFormat = format.GetFormat("￥#,##0");
76 cellStyle.SetFont(font);
77 break;
78 case stylexls.百分比:
79 cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00%");
80 cellStyle.SetFont(font);
81 break;
82 case stylexls.中文大写:
83 IDataFormat format1 = wb.CreateDataFormat();
84 cellStyle.DataFormat = format1.GetFormat("[DbNum2][$-804]0");
85 cellStyle.SetFont(font);
86 break;
87 case stylexls.科学计数法:
88 cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00E+00");
89 cellStyle.SetFont(font);
90 break;
91 case stylexls.默认:
92 cellStyle.SetFont(font);
93 break;
94 }
95 return cellStyle;
96
97
98 }
99 #endregion
最新文章
- bzoj4196 [Noi2015]软件包管理器 树链剖分+线段树
- ASP.NET Core 源码阅读笔记(3) ---Microsoft.AspNetCore.Hosting
- 开坑，Unix环境高级编程，转行之路又得缓缓了
- Java对信号的处理
- *[codility]AscendingPaths
- 【转】MyBatis学习总结(三)——优化MyBatis配置文件中的配置
- C51 库函数(3)
- Android SQLite的使用2（非原创）
- 浅谈：配置本地yum源（centos）
- C#串口通信程序详解
- php基本（四）表单验证
- selenium——键盘操作
- Machine Learning 第三周
- JS-斜杠和反斜杠的转换
- k8s(5)-拓展服务
- enq: FB - contention
- CentOS FTP服务器系统套件全面讲解
- 逻辑回归应用之Kaggle泰坦尼克之灾
- git and github问题集锦
- hive的用户和用户权限