NPOI导入excel

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("导出文件时出错,文件可能正被打开！
" + 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         }

View Code

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) + "中有非法字符;
";
 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) + "中有非法字符;
";
 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         }

View Code

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         }

View Code

 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

View Code