首页 > 代码库 > NPOI操作Excel
NPOI操作Excel
创建Excel
1 #region 操作Excel 2 3 private void ToReadSimpleExcel() 4 { 5 StringBuilder builder = new StringBuilder(); 6 using (FileStream fs = File.OpenRead(@"d:/1.xls")) 7 { 8 HSSFWorkbook wk = new HSSFWorkbook(fs); 9 for (int i = 0; i < wk.NumberOfSheets; i++)10 {11 ISheet sheet = wk.GetSheetAt(i);12 for (int j = 0; j < sheet.LastRowNum; j++)13 {14 IRow row = sheet.GetRow(j);15 if (row != null)16 {17 builder.Append("------------");18 for (int k = 0; k < row.LastCellNum; k++)19 {20 ICell cell = row.GetCell(k);21 if (cell != null)22 {23 builder.Append(cell.ToString());24 }25 }26 }27 }28 }29 builder.ToString();30 using (StreamWriter wr = new StreamWriter(new FileStream(@"d:/1.txt", FileMode.Append)))31 {32 wr.Write(builder.ToString());33 wr.Flush();34 }35 36 }37 38 }39 40 #endregion
向Excel中固定插入数据
1 #region 向固定Excel中插入数据 2 3 4 private void ToReadExcel() 5 { 6 HSSFWorkbook wk = new HSSFWorkbook(); 7 //创作ceshi表 8 ISheet tb = wk.CreateSheet("ceshi"); 9 //创建一行。次行为第二行10 IRow row = tb.CreateRow(1);11 for (int i = 0; i < 20; i++)12 {13 ICell cell = row.CreateCell(i); //在第二行中创建单元格14 cell.SetCellValue(i); //循环往第二个单元格中添加数据15 }16 using (FileStream fs = File.OpenWrite(@"c:/1.xls"))17 {18 wk.Write(fs);19 }20 }21 22 #endregion
定义单元格常用的样式枚举
1 #region 定义单元格常用的样式枚举 2 3 public enum stylexls 4 { 5 头, 6 url, 7 时间, 8 数字, 9 钱,10 百分比,11 中文大写,12 科学计数法,13 默认14 }15 16 #endregion
定义单元格常用到样式
1 #region 定义单元格常用到样式 2 3 private static ICellStyle Getcellstyle(IWorkbook wb, stylexls str) 4 { 5 ICellStyle cellStyle = wb.CreateCellStyle(); 6 7 //定义几种字体 8 //也可以一种字体，写一些公共属性，然后在下面需要时加特殊的 9 IFont font12 = wb.CreateFont(); 10 font12.FontHeightInPoints = 10; 11 font12.FontName = "微软雅黑"; 12 13 14 IFont font = wb.CreateFont(); 15 font.FontName = "微软雅黑"; 16 //font.Underline = 1;下划线 17 18 19 IFont fontcolorblue = wb.CreateFont(); 20 fontcolorblue.Color = HSSFColor.OLIVE_GREEN.BLUE.index; 21 fontcolorblue.IsItalic = true; //下划线 22 fontcolorblue.FontName = "微软雅黑"; 23 24 25 //边框 26 cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.DOTTED; 27 cellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.HAIR; 28 cellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.HAIR; 29 cellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.DOTTED; 30 //边框颜色 31 cellStyle.BottomBorderColor = HSSFColor.OLIVE_GREEN.BLUE.index; 32 cellStyle.TopBorderColor = HSSFColor.OLIVE_GREEN.BLUE.index; 33 34 //背景图形，我没有用到过。感觉很丑 35 //cellStyle.FillBackgroundColor = HSSFColor.OLIVE_GREEN.BLUE.index; 36 //cellStyle.FillForegroundColor = HSSFColor.OLIVE_GREEN.BLUE.index; 37 cellStyle.FillForegroundColor = HSSFColor.WHITE.index; 38 // cellStyle.FillPattern = FillPatternType.NO_FILL; 39 cellStyle.FillBackgroundColor = HSSFColor.BLUE.index; 40 41 //水平对齐 42 cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.LEFT; 43 44 //垂直对齐 45 cellStyle.VerticalAlignment = VerticalAlignment.CENTER; 46 47 //自动换行 48 cellStyle.WrapText = true; 49 50 //缩进;当设置为1时，前面留的空白太大了。希旺官网改进。或者是我设置的不对 51 cellStyle.Indention = 0; 52 53 //上面基本都是设共公的设置 54 //下面列出了常用的字段类型 55 switch (str) 56 { 57 case stylexls.头: 58 // cellStyle.FillPattern = FillPatternType.LEAST_DOTS; 59 cellStyle.SetFont(font12); 60 break; 61 case stylexls.时间: 62 IDataFormat datastyle = wb.CreateDataFormat(); 63 64 cellStyle.DataFormat = datastyle.GetFormat("yyyy/mm/dd"); 65 cellStyle.SetFont(font); 66 break; 67 case stylexls.数字: 68 cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00"); 69 cellStyle.SetFont(font); 70 break; 71 case stylexls.钱: 72 IDataFormat format = wb.CreateDataFormat(); 73 cellStyle.DataFormat = format.GetFormat("￥#,##0"); 74 cellStyle.SetFont(font); 75 break; 76 case stylexls.url: 77 fontcolorblue.Underline = 1; 78 cellStyle.SetFont(fontcolorblue); 79 break; 80 case stylexls.百分比: 81 cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00%"); 82 cellStyle.SetFont(font); 83 break; 84 case stylexls.中文大写: 85 IDataFormat format1 = wb.CreateDataFormat(); 86 cellStyle.DataFormat = format1.GetFormat("[DbNum2][$-804]0"); 87 cellStyle.SetFont(font); 88 break; 89 case stylexls.科学计数法: 90 cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00E+00"); 91 cellStyle.SetFont(font); 92 break; 93 case stylexls.默认: 94 cellStyle.SetFont(font); 95 break; 96 } 97 return cellStyle; 98 } 99 100 #endregion
插入标题各种样式
1 #region 插入标题各种样式 2 private void CreateCommonExcel() 3 { 4 IWorkbook wb = new HSSFWorkbook(); 5 //创建表 6 ISheet sh = wb.CreateSheet("zhiyuan"); 7 //设置单元的宽度 8 sh.SetColumnWidth(0, 15 * 256); 9 sh.SetColumnWidth(1, 35 * 256);10 sh.SetColumnWidth(2, 15 * 256);11 sh.SetColumnWidth(3, 10 * 256);12 int i = 0;13 14 #region 合并单元格15 16 sh.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, 3));17 18 IRow row0 = sh.CreateRow(0);19 row0.Height = 20 * 20;20 ICell icelltop0 = row0.CreateCell(0);21 22 icelltop0.CellStyle = Getcellstyle(wb, stylexls.头);23 icelltop0.SetCellValue("标题合并单元");24 25 #endregion26 27 i++;28 29 #region 设置表头30 31 IRow row1 = sh.CreateRow(1);32 row1.Height = 20 * 20;33 ICell iCell = row1.CreateCell(0);34 iCell.CellStyle = Getcellstyle(wb, stylexls.头);35 iCell.SetCellValue("网站名");36 ICell iCell2 = row1.CreateCell(1);37 iCell2.CellStyle = Getcellstyle(wb, stylexls.头);38 iCell2.SetCellValue("网址");39 ICell iCell3 = row1.CreateCell(2);40 iCell3.CellStyle = Getcellstyle(wb, stylexls.头);41 iCell3.SetCellValue("百度快照");42 ICell iCell4 = row1.CreateCell(3);43 iCell4.CellStyle = Getcellstyle(wb, stylexls.头);44 iCell4.SetCellValue("百度收录");45 46 using (FileStream st = File.OpenWrite(@"d:/ceshi.xls"))47 {48 wb.Write(st);49 System.Web.HttpContext.Current.Response.Write("<script>alert(‘成功‘)</script>");50 }51 52 #endregion53 } 54 #endregion
NPOI操作Excel
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。