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 try47 {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 else61 {62 MessageBox.Show("请选择数据源！");63 }64 #endregion65 }66 else67 {68 MessageBox.Show("请选择数据源！");69 }70 #endregion71 }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 else115 {116 if (k == 1)117 { str = str + content + ","; }118 else if (k == 2)119 {120 str = str + "" + userid + ",'" + content + "',";121 }122 else123 {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 else131 {132 str = str + "'" + content + "',";133 }134 }135 else136 {137 str = str + "'" + content + "',";138 }139 }140 }141 }142 else143 {144 MessageBox.Show("文件内有空数据，请重新导入！");145 }146 #endregion147 if (i == sheet.LastRowNum - 2)148 {149 break;150 }151 }152 else153 {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 else171 {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 ///2 /// 打印导出表头 3 /// 4 /// 5 /// 6 /// 7 ///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 #endregion104 return row1;105 }
1 #region 定义单元格常用到样式的枚举 2 public enum stylexls 3 { 4 头, 5 url, 6 时间, 7 数字, 8 钱, 9 百分比,10 中文大写,11 科学计数法,12 默认13 }14 #endregion15 #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