1、为项目添加NPOI引用。
1 public IRow SetTJRow(HSSFWorkbook wb, ISheet sh) 2 { 3 #region 设置表头 4 IRow row1 = sh.CreateRow(0); 5 row1.Height = 20 * 20; 6 ICell icell1top = row1.CreateCell(0); 7 icell1top.CellStyle = Getcellstyle(wb, stylexls.头); 8 icell1top.SetCellValue("部门"); 9 ICell icell2top = row1.CreateCell(1); 10 icell2top.CellStyle = Getcellstyle(wb, stylexls.头); 11 icell2top.SetCellValue("职员"); 12 ICell icell3top = row1.CreateCell(2); 13 icell3top.CellStyle = Getcellstyle(wb, stylexls.头); 14 icell3top.SetCellValue("人员编码"); 15 ICell icell4top = row1.CreateCell(3); 16 icell4top.CellStyle = Getcellstyle(wb, stylexls.头); 17 icell4top.SetCellValue("月份"); 18 ICell icell5top = row1.CreateCell(4); 19 icell5top.CellStyle = Getcellstyle(wb, stylexls.头); 20 icell5top.SetCellValue("岗位工资"); 21 ICell icell6top = row1.CreateCell(5); 22 icell6top.CellStyle = Getcellstyle(wb, stylexls.头); 23 icell6top.SetCellValue("学历"); 24 ICell icell7top = row1.CreateCell(6); 25 icell7top.CellStyle = Getcellstyle(wb, stylexls.头); 26 icell7top.SetCellValue("技术"); 27 ICell icell8top = row1.CreateCell(7); 28 icell8top.CellStyle = Getcellstyle(wb, stylexls.头); 29 icell8top.SetCellValue("资历"); 30 ICell icell9top = row1.CreateCell(8); 31 icell9top.CellStyle = Getcellstyle(wb, stylexls.头); 32 icell9top.SetCellValue("基本工资"); 33 ICell icell10top = row1.CreateCell(9); 34 icell10top.CellStyle = Getcellstyle(wb, stylexls.头); 35 icell10top.SetCellValue("工龄"); 36 ICell icell11top = row1.CreateCell(10); 37 icell11top.CellStyle = Getcellstyle(wb, stylexls.头); 38 icell11top.SetCellValue("卫生"); 39 ICell icell12top = row1.CreateCell(11); 40 icell12top.CellStyle = Getcellstyle(wb, stylexls.头); 41 icell12top.SetCellValue("门诊"); 42 ICell icell13top = row1.CreateCell(12); 43 icell13top.CellStyle = Getcellstyle(wb, stylexls.头); 44 icell13top.SetCellValue("提租"); 45 ICell icell14top = row1.CreateCell(13); 46 icell14top.CellStyle = Getcellstyle(wb, stylexls.头); 47 icell14top.SetCellValue("专家津贴"); 48 ICell icell15top = row1.CreateCell(14); 49 icell15top.CellStyle = Getcellstyle(wb, stylexls.头); 50 icell15top.SetCellValue("专家交通"); 51 ICell icell16top = row1.CreateCell(15); 52 icell16top.CellStyle = Getcellstyle(wb, stylexls.头); 53 icell16top.SetCellValue("子女"); 54 ICell icell30top = row1.CreateCell(16); 55 icell30top.CellStyle = Getcellstyle(wb, stylexls.头); 56 icell30top.SetCellValue("值班补贴"); 57 ICell icell17top = row1.CreateCell(17); 58 icell17top.CellStyle = Getcellstyle(wb, stylexls.头); 59 icell17top.SetCellValue("绩效"); 60 ICell icell18top = row1.CreateCell(18); 61 icell18top.CellStyle = Getcellstyle(wb, stylexls.头); 62 icell18top.SetCellValue("补发"); 63 ICell icell19top = row1.CreateCell(19); 64 icell19top.CellStyle = Getcellstyle(wb, stylexls.头); 65 icell19top.SetCellValue("一次性奖"); 66 ICell icell20top = row1.CreateCell(20); 67 icell20top.CellStyle = Getcellstyle(wb, stylexls.头); 68 icell20top.SetCellValue("月奖励工资"); 69 ICell icell21top = row1.CreateCell(21); 70 icell21top.CellStyle = Getcellstyle(wb, stylexls.头); 71 icell21top.SetCellValue("应发合计"); 72 ICell icell22top = row1.CreateCell(22); 73 icell22top.CellStyle = Getcellstyle(wb, stylexls.头); 74 icell22top.SetCellValue("局租房"); 75 ICell icell23top = row1.CreateCell(23); 76 icell23top.CellStyle = Getcellstyle(wb, stylexls.头); 77 icell23top.SetCellValue("保养"); 78 ICell icell24top = row1.CreateCell(24); 79 icell24top.CellStyle = Getcellstyle(wb, stylexls.头); 80 icell24top.SetCellValue("医保"); 81 ICell icell25top = row1.CreateCell(25); 82 icell25top.CellStyle = Getcellstyle(wb, stylexls.头); 83 icell25top.SetCellValue("失业"); 84 ICell icell26top = row1.CreateCell(26); 85 icell26top.CellStyle = Getcellstyle(wb, stylexls.头); 86 icell26top.SetCellValue("房金"); 87 ICell icell27top = row1.CreateCell(27); 88 icell27top.CellStyle = Getcellstyle(wb, stylexls.头); 89 icell27top.SetCellValue("年金"); 90 ICell icell28top = row1.CreateCell(28); 91 icell28top.CellStyle = Getcellstyle(wb, stylexls.头); 92 icell28top.SetCellValue("纳税"); 93 ICell icell29top = row1.CreateCell(29); 94 icell29top.CellStyle = Getcellstyle(wb, stylexls.头); 95 icell29top.SetCellValue("实发工资"); 96 //ICell icell30top = row1.CreateCell(29); 97 //icell30top.CellStyle = Getcellstyle(wb, stylexls.头); 98 //icell30top.SetCellValue("实发工资"); 99 100 #endregion 101 return row1; 102 }
1 #region 定义单元格常用到样式的枚举 2 public enum stylexls 3 { 4 头, 5 url, 6 时间, 7 数字, 8 钱, 9 百分比, 10 中文大写, 11 科学计数法, 12 默认 13 } 14 #endregion
private void btnadd_MouseUp(object sender, MouseButtonEventArgs e) { try { #region 打印导出统计数据 if (dt != null && dt.Rows.Count > 0) { //创建工作薄 HSSFWorkbook wb = new HSSFWorkbook(); //创建一个名称为mySheet的表 ISheet sh = wb.CreateSheet("mySheet"); #region 设置表格内容 SetTJRow(wb, sh);//设置表头 for (int i = 0; i < dt.Rows.Count; i++) { IRow row = sh.CreateRow(i + 1); for (int j = 3; j < dt.Columns.Count; j++) { string content = dt.Rows[i][j].ToString(); if (j == 3) { ICell cell = row.CreateCell(j - 3); cell.SetCellValue(content); } else if (j > 4) { if (dt.Rows[i][j - 2].ToString().Contains("小计")) { ICell cell = row.CreateCell(j - 4); cell.SetCellValue("总人数:" + dt.Rows[i][j - 1].ToString()); } else if (dt.Rows[i][j - 2].ToString().Contains("总计")) { ICell cell = row.CreateCell(j - 4); cell.SetCellValue("总人数:" + dt.Rows[i][j - 1].ToString()); } else { ICell cell = row.CreateCell(j - 4); cell.SetCellValue(content); } } } } string saveFileName = "人员工资统计表.xls"; //FileStream fs=new FileStream(); SaveFileDialog saveDialog = new SaveFileDialog(); saveDialog.DefaultExt = "xls"; saveDialog.Filter = "Excel文件|*.xls"; saveDialog.FileName = saveFileName; saveDialog.ShowDialog(); saveFileName = saveDialog.FileName; if (saveFileName.IndexOf(":") < 0) return; //被点了取消 if (saveFileName != "") { using (FileStream fs = File.OpenWrite(saveDialog.FileName))//打开一个xls文件，如果没有则自行创建，如果存在myxls.xls文件则在创建是不要打开该文件 { try { wb.Write(fs); MessageBox.Show("导出成功！", "提示", MessageBoxButton.OK, MessageBoxImage.Asterisk); } catch (Exception ex) { MessageBox.Show("导出文件时出错,文件可能正被打开！ " + ex.Message, "提示", MessageBoxButton.OK, MessageBoxImage.Error); } } } else { MessageBox.Show("请选择数据源！"); } #endregion } else { MessageBox.Show("请选择数据源！"); } #endregion } catch (Exception ex) { MessageBox.Show(ex.Message, "提示", MessageBoxButton.OK, MessageBoxImage.Error); } }
1 #region 定义单元格常用到的样式 2 static ICellStyle Getcellstyle(IWorkbook wb, stylexls str) 3 { 4 ICellStyle cellStyle = wb.CreateCellStyle(); 5 6 //定义几种字体 7 //也可以一种字体，写一些公共属性，然后在下面需要时加特殊的 8 IFont font12 = wb.CreateFont(); 9 font12.FontHeightInPoints = 10; 10 font12.FontName = "微软雅黑"; 11 12 13 IFont font = wb.CreateFont(); 14 font.FontName = "微软雅黑"; 15 //font.Underline = 1;下划线 16 17 18 IFont fontcolorblue = wb.CreateFont(); 19 fontcolorblue.Color = HSSFColor.OliveGreen.Blue.Index; 20 fontcolorblue.IsItalic = true;//下划线 21 fontcolorblue.FontName = "微软雅黑"; 22 23 24 //边框 25 cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin; 26 cellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin; 27 cellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin; 28 cellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin; 29 //水平对齐 30 cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Left; 31 32 //垂直对齐 33 cellStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center; 34 35 //自动换行 36 cellStyle.WrapText = true; 37 38 //缩进;当设置为1时，前面留的空白太大了。希旺官网改进。或者是我设置的不对 39 cellStyle.Indention = 0; 40 41 //上面基本都是设共公的设置 42 //下面列出了常用的字段类型 43 switch (str) 44 { 45 case stylexls.头: 46 // cellStyle.FillPattern = FillPatternType.LEAST_DOTS; 47 cellStyle.SetFont(font12); 48 break; 49 case stylexls.时间: 50 IDataFormat datastyle = wb.CreateDataFormat(); 51 52 cellStyle.DataFormat = datastyle.GetFormat("yyyy/mm/dd"); 53 cellStyle.SetFont(font); 54 break; 55 case stylexls.数字: 56 cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00"); 57 cellStyle.SetFont(font); 58 break; 59 case stylexls.钱: 60 IDataFormat format = wb.CreateDataFormat(); 61 cellStyle.DataFormat = format.GetFormat("￥#,##0"); 62 cellStyle.SetFont(font); 63 break; 64 case stylexls.百分比: 65 cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00%"); 66 cellStyle.SetFont(font); 67 break; 68 case stylexls.中文大写: 69 IDataFormat format1 = wb.CreateDataFormat(); 70 cellStyle.DataFormat = format1.GetFormat("[DbNum2][$-804]0"); 71 cellStyle.SetFont(font); 72 break; 73 case stylexls.科学计数法: 74 cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00E+00"); 75 cellStyle.SetFont(font); 76 break; 77 case stylexls.默认: 78 cellStyle.SetFont(font); 79 break; 80 } 81 return cellStyle; 82 83 84 } 85 #endregion