C# 使用NPOI 操作 Excel 文件

使用

在项目上右击选择→管理NuGet程序包

搜索 NPOI 点击安装

创建 Excel 文件

创建 XLS 文件

//创建一个excel
HSSFWorkbook workbook = new HSSFWorkbook();
//创建一张sheet
ISheet sheet = workbook.CreateSheet("sheet1");
//使用文件流写入数据
using (FileStream fileStream = new FileStream("test.xls",FileMode.Create))
{
    workbook.Write(fileStream);
}

创建 XLSX 文件

//创建一个excel
HSSFWorkbook workbook = new HSSFWorkbook();
//创建一张sheet
ISheet sheet = workbook.CreateSheet("sheet1");
//使用文件流写入数据
using (FileStream fileStream = new FileStream("test.xlsx",FileMode.Create))
{
    workbook.Write(fileStream);
}

合并单元格

//创建一个excel
HSSFWorkbook workbook = new HSSFWorkbook();
//创建一张sheet
ISheet sheet = workbook.CreateSheet("sheet1");
//设置要合并的单元格区域
//CellRangeAddress参数：起始行 结束行 起始列 结束列
sheet.AddMergedRegion(new CellRangeAddress(0,0,0,0));
//设置要合并的单元格区域跳过单元格是否有公式引用、是否与已合并区域合并校验
sheet.AddMergedRegionUnsafe(new CellRangeAddress(0, 0, 0, 0));

设置单元格格式

对齐

//创建样式对象
ICellStyle cellStyle = workbook.CreateCellStyle();
//设置水平对齐方式
cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
//设置垂直对齐方式
cellStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
//设置缩进
cellStyle.Indention = 3;
//自动换行
cellStyle.WrapText = true;
//缩小字体填充
cellStyle.ShrinkToFit = true;
//设置文字旋转
cellStyle.Rotation = 90;

字体

//创建字体对象
IFont font = workbook.CreateFont();
//设置字体
font.FontName = "宋体";
//是否加粗
font.IsBold = true;
//是否斜体
font.IsItalic = true;
//设置字号 二选一
font.FontHeightInPoints = 23;
font.FontHeight = 23;
//设置下划线
font.Underline = FontUnderlineType.Single;
//设置字体颜色
font.Color = HSSFColor.Black.Index;
//删除线
font.IsStrikeout = true;
//上标
font.TypeOffset = FontSuperScript.Super;
//下标
font.TypeOffset = FontSuperScript.Sub;

边框

//设置边框样式
cellStyle.BorderLeft = BorderStyle.Thin;
//设置边框颜色
cellStyle.LeftBorderColor = HSSFColor.Red.Index;
//设置边框对角线样式
cellStyle.BorderDiagonalLineStyle = BorderStyle.Thin
//设置边框对角线颜色
cellStyle.BorderDiagonalColor = HSSFColor.Red.Index;
//设置对角线条数
cellStyle.BorderDiagonal = BorderDiagonal.Both;

填充

//自定义颜色 xls
HSSFPalette palette = ((HSSFWorkbook)workbook).GetCustomPalette();
//SetColorAtIndex 参数：要替换的颜色索引，红，绿，蓝
palette.SetColorAtIndex(23, 0, 112, 192);
cellStyle.FillBackgroundColor = palette.FindColor(0, 112, 192).Indexed;

//自定义颜色 xlsx
//无需替换原有的颜色索引
((XSSFColor)cellStyle.FillBackgroundColorColor).SetRgb(new byte[] { 0, 176, 240 });

保护

//锁定
cellStyle.IsLocked = true;
//隐藏
cellStyle.IsHidden = true;

数字

//数值
cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00");
//货币
cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("￥#,##0");
//百分比
cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00%");
//科学计数
cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00E+00");
//文本
cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("@");
//日期
cellStyle.DataFormat = workbook.CreateDataFormat().GetFormat("yyyy/mm/dd");

下拉框

直接传递下拉列表数组

//创建下拉列表对象 参数：起始行，结束行，起始列，结束列
CellRangeAddressList regions = new CellRangeAddressList(0, 65535, 0, 0);
DVConstraint constraint = DVConstraint.CreateExplicitListConstraint(new string[] { "男", "女" });
HSSFDataValidation dataValidate = new HSSFDataValidation(regions, constraint);
sheet.AddValidationData(dataValidate);

创建一个sheet专门用于储存下拉列表的值

//创建性别字典sheet
ISheet sexSheet = workbook.CreateSheet("SexDictionary");
sexSheet.CreateRow(0).CreateCell(0).SetCellValue("男");
sexSheet.CreateRow(1).CreateCell(0).SetCellValue("女");

//创建指向刚添加下拉数据区域的公式
IName range = workbook.CreateName();
range.RefersToFormula = "SexDictionary!$A$1:$A$2";
range.NameName = "SexRange";

//赋值
CellRangeAddressList regions = new CellRangeAddressList(0, 65535, 0, 0);
DVConstraint constraint = DVConstraint.CreateFormulaListConstraint("SexRange");
HSSFDataValidation dataValidate = new HSSFDataValidation(regions, constraint);

//添加数据校验提示
dataValidate.CreateErrorBox("输入不合法", "请输入下拉列表中的值");
dataValidate.ShowErrorBox = true;
sheet.AddValidationData(dataValidate);
//隐藏字典sheet
workbook.SetSheetHidden(1, true);

公式

校验

数值

CellRangeAddressList regions = new CellRangeAddressList(0, 65535, 0, 0);

HSSFDataValidationHelper helper = new HSSFDataValidationHelper((HSSFSheet)sheet);

//设置校验约束 参数：验证类型，操作类型，开始值，结束值
IDataValidationConstraint constraint = helper.CreateNumericConstraint(ValidationType.INTEGER, OperatorType.BETWEEN, "0", "100");
IDataValidationHelper dataValidationHelper = new HSSFDataValidationHelper((HSSFSheet)sheet);
IDataValidation validation = dataValidationHelper.CreateValidation(constraint, regions);

//添加数据校验提示
validation.CreateErrorBox("输入不合法", "请输入0-100之间的数");
validation.ShowErrorBox = true;
sheet.AddValidationData(validation);

日期

CellRangeAddressList regions = new CellRangeAddressList(0, 65535, 0, 0);

IDataValidationHelper helper = sheet.GetDataValidationHelper();

//设置校验约束 操作类型，开始日期，结束日期，日期格式
IDataValidationConstraint constraint = helper.CreateDateConstraint(OperatorType.BETWEEN, "2022-01-01", "2022-12-31", "yyyy-MM-dd");
IDataValidation validation = helper.CreateValidation(constraint, regions);

//添加数据校验提示 弹窗提示
validation.CreateErrorBox("输入不合法", "请输入2022-01-01~2022-12-31之间的日期");
validation.ShowErrorBox = true;

//添加数据校验提示 提示符提示
validation.CreatePromptBox("输入不合法", "请输入2022-01-01~2022-12-31之间的日期");
validation.ShowPromptBox = true;

sheet.AddValidationData(validation);

时间

//赋值
CellRangeAddressList regions = new CellRangeAddressList(0, 65535, 0, 0);

IDataValidationHelper helper = sheet.GetDataValidationHelper();

//设置校验约束 操作类型，开始时间，结束时间
IDataValidationConstraint constraint = helper.CreateTimeConstraint(OperatorType.BETWEEN, "08:00", "09:00");
IDataValidation validation = helper.CreateValidation(constraint, regions);

//添加数据校验提示 弹窗提示
validation.CreateErrorBox("输入不合法", "请输入08:00~09:00之间的时间");
validation.ShowErrorBox = true;

sheet.AddValidationData(validation);