C# 如何使用NPOI操作Excel以及读取合并单元格等 |
您所在的位置：网站首页 › 如何使用excel合并单元格 › C# 如何使用NPOI操作Excel以及读取合并单元格等 |
C# 如何使用NPOI操作Excel以及读取合并单元格等
|
C#操作Excel方法有很多，以前用的需要电脑安装office才能用，但因为版权问题公司不允许安装office。所以改用NPOI进行Excel操作，基本上一些简单的Excel操作都没有问题，读写合并单元格等都能实现。 NPOI 库下载地址 NPOI百度云下载: https://pan.baidu.com/s/13wiYmZ2txCq79CR-kj9Cvg 提取码: 5x1v 命名空间： using NPOI; using NPOI.XSSF.UserModel; using NPOI.SS.UserModel; using NPOI.HSSF.UserModel; using NPOI.HSSF.Util;简单的保存数据： public void ExcelTest(string path) { IWorkbook workbook = new HSSFWorkbook();//创建Workbook workbook.CreateSheet("sheet1");//创建sheet using (FileStream fs = File.Create(path))//path=mmm.xls; { ISheet sheet = workbook.GetSheetAt(0);//获取sheet sheet.CreateRow(1).CreateCell(0).SetCellValue("nami");//创建第一行/创建第一单元格/设置第一单元格的内容[可以分开创建，但必须先创建行才能创建单元格不然报错] sheet.GetRow(1).CreateCell(1).SetCellValue("robin");//获取第一行/创建第二单元格/设置第二单元格的内容 sheet.CreateRow(2).CreateCell(0).SetCellValue("saber");//创建第二行/创建第一单元格/设置第一单元格的内容 sheet.GetRow(2).CreateCell(1).SetCellValue("luffy");//获取第二行/创建第二单元格/设置第二单元格的内容 sheet.GetRow(1).CreateCell(2).SetCellValue(5); sheet.GetRow(2).CreateCell(2).SetCellValue(2); //添加批注 IDrawing draw = sheet.CreateDrawingPatriarch(); IComment comment = draw.CreateCellComment(new HSSFClientAnchor(0, 0, 0, 0, 1, 2, 4, 4));//里面参数应该是指示批注的位置大小吧 comment.String = new HSSFRichTextString("one-piece");//添加批注内容 comment.Author = "梦琪小生";//添加批注作者 sheet.GetRow(1).GetCell(1).CellComment = comment;//将之前设置的批注给定某个单元格 //单元格格式设置 ICellStyle cellStyle = workbook.CreateCellStyle(); IDataFormat format = workbook.CreateDataFormat(); cellStyle.DataFormat = format.GetFormat("0.00"); sheet.GetRow(2).GetCell(2).CellStyle = cellStyle; //合并单元格 sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, 2)); sheet.CreateRow(0).CreateCell(0).SetCellValue("梦琪小生"); ICellStyle titleStyle = workbook.CreateCellStyle(); IFont titleFont = workbook.CreateFont(); titleFont.FontHeightInPoints = 15;//设置字体大小 titleFont.Color = HSSFColor.BLUE.index;//设置字体颜色 titleStyle.SetFont(titleFont); titleStyle.Alignment = HorizontalAlignment.CENTER;//居中 sheet.GetRow(0).GetCell(0).CellStyle = titleStyle; ICellStyle style = workbook.CreateCellStyle(); style.BorderBottom = BorderStyle.THIN; style.BorderLeft = BorderStyle.THIN; style.BorderRight = BorderStyle.THIN; style.BorderTop = BorderStyle.THIN; sheet.GetRow(1).GetCell(1).CellStyle = style; //插入图片 HSSFClientAnchor anchor2 = new HSSFClientAnchor(0, 0, 0, 0, 0, 5, 6, 10); byte[] bytes = System.IO.File.ReadAllBytes(@"C:\Users\Administrator\Desktop\image\mqxs.png"); int picID = workbook.AddPicture(bytes, PictureType.PNG); IPicture pic = patriarch.CreatePicture(anchor2, picID); pic.Resize(); workbook.Write(fs);//保存文件 } }读取Excel返回DataTable： /// /// 读取Excel[.xls](返回DataTable) /// /// Excel路径 /// public static DataTable ReadExcel(string path) { try { DataTable dt = new DataTable(); using (FileStream fs = new FileStream(path, FileMode.Open)) { IWorkbook workbook = new HSSFWorkbook(fs); ISheet sheet = workbook.GetSheetAt(0); int rfirst = sheet.FirstRowNum; int rlast = sheet.LastRowNum; IRow row = sheet.GetRow(rfirst); int cfirst = row.FirstCellNum; int clast = row.LastCellNum; for (int i = cfirst; i < clast; i++) { if (row.GetCell(i) != null) dt.Columns.Add(row.GetCell(i).StringCellValue, System.Type.GetType("System.String")); } row = null; for (int i = rfirst + 1; i = range.FirstRow && rowIndex = range.FirstColumn && colIndex 255) { throw new Exception("表格数据列超出最大值255"); } IWorkbook workbook = new HSSFWorkbook(); ISheet sheet = workbook.CreateSheet("sheet1"); using (FileStream fs = File.Create(savefile)) { int dtRowCount = dt.Rows.Count; int dtcolumnCount = dt.Columns.Count; #region[表格样式] ICellStyle TitleStyle = MCellStyle.GetCellStyle(workbook, -1, "", 15, true, HSSFColor.Black.Index, HorizontalAlignment.Center, VerticalAlignment.Center, HSSFColor.Grey25Percent.Index); ICellStyle columnStyle = MCellStyle.GetCellStyle(workbook, -1, "", 11, true, HSSFColor.Black.Index, HorizontalAlignment.Center, VerticalAlignment.Center, HSSFColor.Grey25Percent.Index); ICellStyle limeStyle = MCellStyle.GetCellStyle(workbook, -1, "", 11, false, HSSFColor.Black.Index, HorizontalAlignment.Center, VerticalAlignment.Center, HSSFColor.White.Index); ICellStyle roseStyle = MCellStyle.GetCellStyle(workbook, -1, "", 11, false, HSSFColor.Black.Index, HorizontalAlignment.Center, VerticalAlignment.Center, HSSFColor.White.Index); #endregion #region[设置标题] sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 1, 0, dtcolumnCount - 1)); sheet.CreateRow(0); sheet.CreateRow(1); sheet.GetRow(0).CreateCell(0).SetCellValue(title); sheet.GetRow(0).GetCell(0).CellStyle = TitleStyle; #endregion #region[设置表头] IRow row = sheet.CreateRow(2); for (int j = 0; j < dtcolumnCount; j++) { row.CreateCell(j).SetCellValue(dt.Columns[j].ToString()); row.GetCell(j).CellStyle = columnStyle; sheet.SetColumnWidth(j, 20 * 256); } row = null; #endregion #region[设置数据] for (int i = 0; i < dtRowCount; i++) { IRow rows = sheet.CreateRow(i + 3); for (int j = 0; j < dtcolumnCount; j++) { rows.CreateCell(j).SetCellValue(dt.Rows[i][j].ToString()); if (i % 2 == 0) rows.GetCell(j).CellStyle = limeStyle; else rows.GetCell(j).CellStyle = roseStyle; } rows = null; } #endregion #region[设置主边框] sheet.GetRow(0).CreateCell(dtcolumnCount - 1).CellStyle = TitleStyle; sheet.GetRow(1).CreateCell(dtcolumnCount - 1).CellStyle = TitleStyle; //((HSSFSheet)sheet).SetEnclosedBorderOfRegion(new NPOI.SS.Util.CellRangeAddress(0, dtRowCount, 0, dtcolumnCount - 1), BorderStyle.MEDIUM, HSSFColor.Black.Index); #endregion #region[表格样式反设置] TitleStyle = null; columnStyle = null; limeStyle = null; roseStyle = null; #endregion workbook.Write(fs); } sheet = null; workbook = null; return true; } catch { return false; } }创建样式：（2020-10-20增加样式的填充颜色） public static class MCellStyle { /// /// 获取NPOI的单元格样式 /// /// 表格 /// 单元格编号 默认-1即忽略编号 /// 字体名字 /// 字体大小 /// 是否加粗 /// 填充颜色 /// 水平对齐方式 /// 垂直对齐方式 /// 背景颜色 /// 数字的格式"0.00" /// 返回表格单元格样式CellStyle public static ICellStyle GetCellStyle(IWorkbook workbook, short cellstyleindex = -1, string fontname = "", short fontsize = 11, bool IsBold = false, short fillforecolor = HSSFColor.COLOR_NORMAL, HorizontalAlignment halignment = HorizontalAlignment.Left, VerticalAlignment valignment = VerticalAlignment.Center, short fillbackcolor = HSSFColor.COLOR_NORMAL, string format = "") { if (cellstyleindex != -1) { try { return workbook.GetCellStyleAt(cellstyleindex); } catch {; } } if (cellStyle != null) { cellStyle = null; } cellStyle = workbook.CreateCellStyle(); IFont font = workbook.CreateFont(); if (fillforecolor != HSSFColor.COLOR_NORMAL) font.Color = fillforecolor; else font.Color = HSSFColor.Black.Index; if (fontsize > 0) font.FontHeightInPoints = fontsize; if (fontname != "") font.FontName = fontname; if (IsBold) font.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold; cellStyle.SetFont(font); if (fillbackcolor != HSSFColor.COLOR_NORMAL) { cellStyle.FillForegroundColor = fillbackcolor; cellStyle.FillPattern = FillPattern.SolidForeground; } if (format != "") { IDataFormat dataformat = workbook.CreateDataFormat(); cellStyle.DataFormat = dataformat.GetFormat(format); } cellStyle.Alignment = halignment; cellStyle.VerticalAlignment = valignment; cellStyle.BorderLeft = BorderStyle.Thin; cellStyle.BorderRight = BorderStyle.Thin; cellStyle.BorderTop = BorderStyle.Thin; cellStyle.BorderBottom = BorderStyle.Thin; cellStyle.WrapText = true; return cellStyle; } /// /// 获取NPOI的单元格样式（填充颜色精准） /// /// 表格 /// 填充颜色 /// 背景颜色 /// 单元格编号 默认-1即忽略编号 /// 字体名字 /// 字体大小 /// 是否加粗 /// 水平对齐方式 /// 垂直对齐方式 /// 数字的格式"0.00" /// 返回表格单元格样式CellStyle public static ICellStyle GetCellStyle(XSSFWorkbook workbook, Color fillforecolor, Color fillbackcolor, short cellstyleindex = -1, string fontname = "", short fontsize = 11, bool IsBold = false, HorizontalAlignment halignment = HorizontalAlignment.Left, VerticalAlignment valignment = VerticalAlignment.Center, string format = "") { if (cellstyleindex != -1) { try { return workbook.GetCellStyleAt(cellstyleindex); } catch {; } } if (xssfcellStyle != null) { xssfcellStyle = null; } xssfcellStyle = workbook.CreateCellStyle() as XSSFCellStyle; XSSFFont font = workbook.CreateFont() as XSSFFont; font.SetColor(new XSSFColor(new byte[] { fillforecolor.R, fillforecolor.G, fillforecolor.B })); if (fontsize > 0) font.FontHeightInPoints = fontsize; if (fontname != "") font.FontName = fontname; if (IsBold) font.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold; xssfcellStyle.SetFont(font); xssfcellStyle.FillPattern = FillPattern.SolidForeground; if (xssfcellStyle.FillForegroundColorColor == null) xssfcellStyle.FillForegroundColorColor = new XSSFColor(new byte[] { fillbackcolor.R, fillbackcolor.G, fillbackcolor.B }); else ((XSSFColor)xssfcellStyle.FillForegroundColorColor).SetRgb(new byte[] { fillbackcolor.R, fillbackcolor.G, fillbackcolor.B }); if (format != "") { IDataFormat dataformat = workbook.CreateDataFormat(); xssfcellStyle.DataFormat = dataformat.GetFormat(format); } xssfcellStyle.Alignment = halignment; xssfcellStyle.VerticalAlignment = valignment; xssfcellStyle.BorderLeft = BorderStyle.Thin; xssfcellStyle.BorderRight = BorderStyle.Thin; xssfcellStyle.BorderTop = BorderStyle.Thin; xssfcellStyle.BorderBottom = BorderStyle.Thin; xssfcellStyle.WrapText = true; return xssfcellStyle; } /// /// 获取颜色值(不精准) /// /// 颜色RGB /// Excel画布 /// public static short GetColorIndex(this HSSFWorkbook workbook, Color color) { HSSFPalette palette = workbook.GetCustomPalette(); var v = palette.FindSimilarColor(color.R, color.G, color.B); if (v == null) { throw new Exception("Color is not in Palette"); } else return v.Indexed; } private static ICellStyle cellStyle; private static XSSFCellStyle xssfcellStyle; }
扫码关注微信公众号
|
【本文地址】
今日新闻 |
推荐新闻 |