NPOI在Excel文件中添加批注

1、前言

最近工作中涉及到了Excel批注的添加，经过一番摸索之后终于搞定，下面开始介绍使用NPOI添加批注的方法。

2、实现方法

2.1、创建新的xlsx文件并添加批注

using System;
using System.IO;

namespace NPOIApp
{
    class Program
    {
        static void Main(string[] args)
        {
            using (FileStream stream = new FileStream(@"C:\Users\dongshenfeng\Desktop\test.xlsx", FileMode.Create))
            {
                NPOI.SS.UserModel.IWorkbook workbook = new NPOI.XSSF.UserModel.XSSFWorkbook();
                NPOI.SS.UserModel.ISheet sheet = workbook.CreateSheet("测试表");
                NPOI.SS.UserModel.IDrawing drawing = sheet.CreateDrawingPatriarch();
                NPOI.SS.UserModel.IRow row = sheet.CreateRow(0);
                NPOI.SS.UserModel.ICell cell;

                // 创建单元格样式
                NPOI.SS.UserModel.ICellStyle cellStyle = workbook.CreateCellStyle();
                cellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
                cellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
                cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
                cellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
                cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
                cellStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;

                // 创建第一列批注
                NPOI.SS.UserModel.IComment comment1 = drawing.CreateCellComment(new NPOI.XSSF.UserModel.XSSFClientAnchor(0, 0, 0, 0, 1, 0, 3, 3));
                comment1.Author = "Herry";
                comment1.String = new NPOI.XSSF.UserModel.XSSFRichTextString("这是编号列");

                // 创建第二列批注
                NPOI.SS.UserModel.IComment comment2 = drawing.CreateCellComment(new NPOI.XSSF.UserModel.XSSFClientAnchor(0, 0, 0, 0, 2, 1, 4, 4));
                comment2.Author = "Herry";
                comment2.String = new NPOI.XSSF.UserModel.XSSFRichTextString("这是名称列");

                // 第一列：编号
                cell = row.CreateCell(0);
                cell.CellComment = comment1;
                cell.CellStyle = cellStyle;
                cell.SetCellValue("编号");

                // 第二列：名称
                cell = row.CreateCell(1);
                cell.CellComment = comment2;
                cell.CellStyle = cellStyle;
                cell.SetCellValue("名称");

                // 写入文件流
                workbook.Write(stream);
                workbook.Close();
            }
            Console.ReadKey(true);
        }
    }
}

运行结果如下图所示：

2.2、读取已有xlsx文件并添加批注
当前有一份测试数据，如下图所示：

下面来给第一行第一列和第一行第二列添加批注

using System;
using System.IO;

namespace NPOIApp
{
    class Program
    {
        static void Main(string[] args)
        {
            // 读取文件流
            NPOI.SS.UserModel.IWorkbook workbook;
            using (FileStream stream = new FileStream(@"C:\Users\dongshenfeng\Desktop\test.xlsx", FileMode.Open, FileAccess.Read))
            {
                workbook = new NPOI.XSSF.UserModel.XSSFWorkbook(stream);
            }

            // 获取工作表
            NPOI.SS.UserModel.ISheet sheet = workbook.GetSheetAt(0);
            NPOI.SS.UserModel.IDrawing drawing = sheet.CreateDrawingPatriarch();

            // 创建第一列批注
            NPOI.SS.UserModel.IComment comment1 = drawing.CreateCellComment(new NPOI.XSSF.UserModel.XSSFClientAnchor(0, 0, 0, 0, 1, 0, 3, 3));
            comment1.Author = "Herry";
            comment1.String = new NPOI.XSSF.UserModel.XSSFRichTextString("这是编号列");

            // 创建第二列批注
            NPOI.SS.UserModel.IComment comment2 = drawing.CreateCellComment(new NPOI.XSSF.UserModel.XSSFClientAnchor(0, 0, 0, 0, 2, 0, 4, 3));
            comment2.Author = "Herry";
            comment2.String = new NPOI.XSSF.UserModel.XSSFRichTextString("这是名称列");

            // 设置批注
            NPOI.SS.UserModel.IRow row = sheet.GetRow(0);
            row.GetCell(0).CellComment = comment1;
            row.GetCell(1).CellComment = comment2;

            // 写入文件流
            using (FileStream stream = new FileStream(@"C:\Users\dongshenfeng\Desktop\test.xlsx", FileMode.Create, FileAccess.Write))
            {
                workbook.Write(stream);
                workbook.Close();
            }
            Console.ReadKey(true);
        }
    }
}

运行结果如下图所示：

2.3、修改已有xlsx文件中的批注文本

using System;
using System.IO;

namespace NPOIApp
{
    class Program
    {
        static void Main(string[] args)
        {
            // 读取文件流
            NPOI.SS.UserModel.IWorkbook workbook;
            using (FileStream stream = new FileStream(@"C:\Users\dongshenfeng\Desktop\test.xlsx", FileMode.Open, FileAccess.Read))
            {
                workbook = new NPOI.XSSF.UserModel.XSSFWorkbook(stream);
            }

            // 删除批注
            NPOI.SS.UserModel.ISheet sheet = workbook.GetSheetAt(0);
            NPOI.SS.UserModel.IRow row = sheet.GetRow(0);
            row.GetCell(0).CellComment.String = new NPOI.XSSF.UserModel.XSSFRichTextString("这是修改后的编号列");
            row.GetCell(1).CellComment.String = new NPOI.XSSF.UserModel.XSSFRichTextString("这是修改后的名称列");

            // 写入文件流
            using (FileStream stream = new FileStream(@"C:\Users\dongshenfeng\Desktop\test.xlsx", FileMode.Create, FileAccess.Write))
            {
                workbook.Write(stream);
                workbook.Close();
            }
            Console.ReadKey(true);
        }
    }
}

运行结果如下图所示：

2.4、删除已有xlsx文件中的批注

using System;
using System.IO;

namespace NPOIApp
{
    class Program
    {
        static void Main(string[] args)
        {
            // 读取文件流
            NPOI.SS.UserModel.IWorkbook workbook;
            using (FileStream stream = new FileStream(@"C:\Users\dongshenfeng\Desktop\test.xlsx", FileMode.Open, FileAccess.Read))
            {
                workbook = new NPOI.XSSF.UserModel.XSSFWorkbook(stream);
            }

            // 删除批注
            NPOI.SS.UserModel.ISheet sheet = workbook.GetSheetAt(0);
            NPOI.SS.UserModel.IRow row = sheet.GetRow(0);
            row.GetCell(0).RemoveCellComment();
            row.GetCell(1).RemoveCellComment();

            // 写入文件流
            using (FileStream stream = new FileStream(@"C:\Users\dongshenfeng\Desktop\test.xlsx", FileMode.Create, FileAccess.Write))
            {
                workbook.Write(stream);
                workbook.Close();
            }
            Console.ReadKey(true);
        }
    }
}

运行结果如下图所示，可以看到刚刚创建的批注已经被删除了。