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);
}
}
}
运行结果如下图所示,可以看到刚刚创建的批注已经被删除了。