C#如何使用NPOI操作Excel以及读取合并单元格等

OStack程序员社区-中国程序员成长平台 › 门户 › 编程› C++›C++教程

原作者: [db:作者] 来自: [db:来源] 收藏邀请

C#操作Excel方法有很多，以前用的需要电脑安装office才能用，但因为版权问题公司不允许安装office。所以改用NPOI进行Excel操作，基本上一些简单的Excel操作都没有问题，读写合并单元格等都能实现。

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：

        /// <summary>
        /// 读取Excel[.xls](返回DataTable)
        /// </summary>
        /// <param name="path">Excel路径</param>
        /// <returns></returns>
        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 <= rlast; i++)
                    {
                        DataRow r = dt.NewRow();
                        IRow ir = sheet.GetRow(i);
                        for (int j = cfirst; j < clast; j++)
                        {
                            if (ir.GetCell(j) != null)
                            {
                                r[j] = ir.GetCell(j).ToString();
                            }
                        }
                        dt.Rows.Add(r);
                        ir = null;
                        r = null;
                    }
                    sheet = null;
                    workbook = null;
                }
                return dt;
            }
            catch
            {
                System.Windows.Forms.MessageBox.Show("Excel格式错误或者Excel正由另一进程在访问");
                return null;
            }
        }

Ok,NPOI也用了一段时间了....

讲一点经验之谈...关于NPOI的单元格样式CellStyles个数是有限制的4000个，所以大家设置单元格样式的时候尽量不要再for循环里面定义，可以在for循环外围定义好使用...减少CellStyles个数,Ok，主要要讲的是下面的那一条....

((HSSFSheet)sheet).SetEnclosedBorderOfRegion(new NPOI.SS.Util.CellRangeAddress(0, 50, 0, 100), BorderStyle.MEDIUM, HSSFColor.BLACK.index);

这句的功能是给合并后的单元格加外边框。

比如这条语句的意思是将单元格0行0列到50行100列绘制一个整体的外边框。用到是很好用...但是很占资源，不知道为什么就这么一句话会占好几个CellStyles...而且速度较慢....

所以这个功能在数据量小的可以用，问题不大，速度基本影响也不大，但数据量一大...这个相对就会拖累速度了又太占资源.

小生我就在这边吃过亏，就是这条语句导致CellStyles个数不够用[当然不是一句就会有问题咯....因为很多地方合并单元格然后加外边框...最终就悲剧了....]....小生觉得这是NPOI的缺陷...

NPOI操作Excel使用说明：

官网教程相当详细，需进一步学习的请移步至官网学习 http://tonyqus.sinaapp.com/npoi2tutorial

2017/05/05补充：

C# Color转NPOI颜色：

/// <summary>
        /// 获取颜色值
        /// </summary>
        /// <param name="color">颜色RGB</param>
        /// <param name="workbook">Excel画布</param>
        /// <returns></returns>
        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.GetIndex();
        }

2018/4/8补充：

获取合并单元格

        /// <summary>
        /// 获取当前单元格所在的合并单元格的位置
        /// </summary>
        /// <param name="sheet">sheet表单</param>
        /// <param name="rowIndex">行索引 0开始</param>
        /// <param name="colIndex">列索引 0开始</param>
        /// <param name="start">合并单元格左上角坐标</param>
        /// <param name="end">合并单元格右下角坐标</param>
        /// <returns>返回false表示非合并单元格</returns>
        private static bool IsMergeCell(ISheet sheet, int rowIndex, int colIndex, out Point start, out Point end)
        {
            bool result = false;
            start = new Point(0, 0);
            end = new Point(0, 0);
            if ((rowIndex < 0) || (colIndex < 0)) return result;
            int regionsCount = sheet.NumMergedRegions;
            for (int i = 0; i < regionsCount; i++)
            {
                CellRangeAddress range = sheet.GetMergedRegion(i);
                //sheet.IsMergedRegion(range); 
                if (rowIndex >= range.FirstRow && rowIndex <= range.LastRow && colIndex >= range.FirstColumn && colIndex <= range.LastColumn)
                {
                    start = new Point(range.FirstRow, range.FirstColumn);
                    end = new Point(range.LastRow, range.LastColumn);
                    result = true;
                    break;
                }
            }
            return result;
        }

2020-09-29补充：（为啥这么多年了，还补充...因为换公司又用到了，就补充一下）

将DataTable导出Excel：

/// <summary>
        /// 创建Excel
        /// </summary>
        /// <param name="savefile">保存路径</param>
        /// <param name="title">表格标题</param>
        /// <param name="dt">表格数据</param>
        /// <returns></returns>
        public static bool CreateExcel(string savefile, string title, DataTable dt)
        {
            try
            {
                if (dt.Columns.Count > 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
    {
        /// <summary>
        /// 获取NPOI的单元格样式
        /// </summary>
        /// <param name="workbook">表格</param>
        /// <param name="cellstyleindex">单元格编号 默认-1即忽略编号</param>
        /// <param name="fontname">字体名字</param>
        /// <param name="fontsize">字体大小</param>
        /// <param name="IsBold">是否加粗</param>
        /// <param name="fillforecolor">填充颜色</param>
        /// <param name="halignment">水平对齐方式</param>
        /// <param name="valignment">垂直对齐方式</param>
        /// <param name="fillbackcolor">背景颜色</param>
        /// <param name="format">数字的格式"0.00"</param>
        /// <returns>返回表格单元格样式CellStyle</returns>
        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;
        }
        /// <summary>
        /// 获取NPOI的单元格样式（填充颜色精准）
        /// </summary>
        /// <param name="workbook">表格</param>
        /// <param name="fillforecolor">填充颜色</param>
        /// <param name="fillbackcolor">背景颜色</param>
        /// <param name="cellstyleindex">单元格编号 默认-1即忽略编号</param>
        /// <param name="fontname">字体名字</param>
        /// <param name="fontsize">字体大小</param>
        /// <param name="IsBold">是否加粗</param>
        /// <param name="halignment">水平对齐方式</param>
        /// <param name="valignment">垂直对齐方式</param>
        /// <param name="format">数字的格式"0.00"</param>
        /// <returns>返回表格单元格样式CellStyle</returns>
        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;
        }
        /// <summary>
        /// 获取颜色值(不精准)
        /// </summary>
        /// <param name="color">颜色RGB</param>
        /// <param name="workbook">Excel画布</param>
        /// <returns></returns>
        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;
    }

扫码关注微信公众号

鲜花

握手

雷人

路过

鸡蛋

该文章已有0人参与评论

请发表评论

全部评论

专题导读

More+

10-27 六六分期app的软件客服如何联系？(六六分期

11-06 可心卡盟:win10系统火狐flash插件崩溃怎么

11-06 亲亲特价:怎么删除回收站图标

11-06 济南大学虚拟社区:鲁大师节能降温的具体办

11-06 xlueops.exe:无线网络安装向导

11-06 女斗合众国:win7系统cf与主机连接不稳定怎

11-06 0xc000022-[cf烟雾头]cf怎么调烟雾头

11-06 qizideyouhuo:应用程序无法正常启动0xc0000

11-06 ipz-185:win7系统vcf文件怎么打开

11-06 傻哥蹦迪:win10系统s4怎么打开usb调试

11-06 八神浩树gtaste:回收站清空了怎么恢复

11-06 妖尾之黑色守护:win10系统电脑没有1440x900

11-06 校园至尊魔王小说:win7系统浏览网页时字体

11-06 女斗合众国:win10系统访问共享文件夹提示请

11-06 tokyo hot n0654:恢复win7系统默认字体一招

11-06 雨酷仙境:设置win7系统转移临时文件夹腾出

11-06 阿穆纳伊之杖:win7系统开始菜单在右边还原

11-06 tunespotting:win10系统火狐flash插件总是

11-06 甘尔葛分析师：计谋网站seo关键词暴涨有什

11-06 蔡贵霖: 计谋网站seo关键词暴涨有什么秘密

11-06 博益网首页:ao3网页版进入不了解决方法

11-06 漏斗子专栏: 网站数据分析小白易懂精华篇

11-06 见证双虹怎么做:win7系统开启telnet命令的

11-06 颾狐蝶蜋:系统资源不足无法完成请求的服务

11-06 国光中学校歌:提交网站到alexa查询详细步骤

11-06 西安有情天:静态网页和动态网页的区别

11-06 红木雅尚斋:外部链接构造对网站的好处

11-06 前官礼遇：防止域名劫持–增强域安全性的10

11-06 密传二转答案: 中文分词算法有哪些

11-06 金泉家园邮编:百度快照劫持的表现及应对方

如何处理C++构造函数中的错误——兼谈不同语言的错误处理_转 ...发布时间：2022-07-13

C++pair（对组）的简单了解发布时间：2022-07-13

剪的笔顺,诠释剪的笔画,认识剪的部首

1 六六分期app的软件客服如何联系？(六六分期

六六分期app的软件客服如何联系？不知道吗？加qq群【895510560】即可！标题：六六分期

阅读：19190|2023-10-27

2 可心卡盟:win10系统火狐flash插件崩溃怎么

今天小编告诉大家如何处理win10系统火狐flash插件总是崩溃的问题，可能很多用户都不知

阅读：9988|2022-11-06

3 亲亲特价:怎么删除回收站图标

今天小编告诉大家如何对win10系统删除桌面回收站图标进行设置，可能很多用户都不知道

阅读：8326|2022-11-06

4 济南大学虚拟社区:鲁大师节能降温的具体办

今天小编告诉大家如何对win10系统电脑设置节能降温的设置方法，想必大家都遇到过需要

阅读：8696|2022-11-06

5 xlueops.exe:无线网络安装向导

我们在使用xp系统的过程中,经常需要对xp系统无线网络安装向导设置进行设置，可能很多

阅读：8639|2022-11-06

6 女斗合众国:win7系统cf与主机连接不稳定怎

今天小编告诉大家如何处理win7系统玩cf老是与主机连接不稳定的问题，可能很多用户都不

阅读：9657|2022-11-06

7 0xc000022-[cf烟雾头]cf怎么调烟雾头

电脑对日常生活的重要性小编就不多说了，可是一旦碰到win7系统设置cf烟雾头的问题，很

阅读：8624|2022-11-06

8 qizideyouhuo:应用程序无法正常启动0xc0000

我们在日常使用电脑的时候，有的小伙伴们可能在打开应用的时候会遇见提示应用程序无法

阅读：7998|2022-11-06

9 ipz-185:win7系统vcf文件怎么打开

今天小编告诉大家如何对win7系统打开vcf文件进行设置，可能很多用户都不知道怎么对win

阅读：8656|2022-11-06

10 傻哥蹦迪:win10系统s4怎么打开usb调试

今天小编告诉大家如何对win10系统s4开启USB调试模式进行设置，可能很多用户都不知道怎

阅读：7535|2022-11-06

客服电话

电子邮件

C#如何使用NPOI操作Excel以及读取合并单元格等

请发表评论

全部评论

上一篇：

下一篇：

bradtraversy/iweather: Ionic 3 mobile we

joaomh/curso-de-matlab

断牙刷新位置时间（断牙属性及刷新位置介绍

rugk/mastodon-simplified-federation: Sim

PureSwift/BluetoothLinux: Pure Swift Lin

剪的笔顺,诠释剪的笔画,认识剪的部首

六六分期app的软件客服如何联系？(六六分期

florent37/ViewAnimator: A fluent Android

florent37/Shrine-MaterialDesign2: implem

CVE-2020-36276

SimpleSoftwareIO/simple-sms: Send and re

关于我们

产品与服务

解决方案

139-2527-9053