Npoi对Excel进行读写操作Demo (.NetCore 3.1)
转载来源：https://blog.csdn.net/M1234uy/article/details/107695252
前言
Npoi 可以在没有安装Office 的情况写 对 Word 或 Excel 文档进行读写操作。
npoi 是一个开源的C#读写Excel、Word等微软OLE2组件文档的项目。本文主要讲对 Excel 文档进行读写操作。
一、读取 Excel
1-1 读取 Excel 文件
流程图
代码
/// <summary>
/// 读取IWorkbook
/// </summary>
public IWorkbook ReadWorkbook = null;
/// <summary>
/// 获取读取 WorkBook
/// </summary>
public void GetReadWorkbook(string FilePath)
{
// 获取扩展名
string ExtensionName = System.IO.Path.GetExtension(FilePath);
// 文件流
FileStream FileStream = new FileStream(FilePath, FileMode.Open, FileAccess.ReadWrite);
// 把xls写入workbook中 2003版本
if (ExtensionName.Equals(".xls"))
{
ReadWorkbook = new HSSFWorkbook(FileStream);
}
// 把xlsx 写入workbook中 2007版本
else if (ExtensionName.Equals(".xlsx"))
{
ReadWorkbook = new XSSFWorkbook(FileStream);
}
else
{
ReadWorkbook = null;
}
FileStream.Close();
}
1-2 读取 Sheet
1-2-1 获取所有 Sheet
通过 For 循环 Workbook，获取所有 Sheet。
/// <summary>
/// 获取表中的Sheet名称
/// </summary>
public List<ISheet> Sheets = null;
/// <summary>
/// 获取所有 Sheet表
/// </summary>
public void GetSheets()
{
// 获取表
Sheets = new List<ISheet>();
var SheetCount = ReadWorkbook.NumberOfSheets;
for (int i = 0; i < SheetCount; i++)
{
Sheets.Add(ReadWorkbook.GetSheetAt(i));
}
}
1-2-2 获取单个Sheet
通过 Sheet名 获取Sheet有两种方法：
1. 通过 SheetName 获取 Sheet。
// 1. 通过Sheet 名获取 Sheet
int sheetIndex =ReadWorkbook.GetSheet("SheetName")
- 1
- 2
2. 通过 SheetName 获取 Sheet数组对应的 下标。 再通过Sheet下标获取Sheet。
// 2.a通过Sheet 名获取 Sheet数组对应的下标；
int sheetIndex =ReadWorkbook.GetSheetIndex("SheetName")
// 2.b 通过 Sheet下标获取 对应的 Sheet 数据
Isheet sheet =ReadWorkbook.GetSheetAt(sheetIndex )
1-3 读取 Sheet 数据
流程图
代码
/// <summary>
/// 获取 Sheet 表数据
/// </summary>
/// <param name="Sheet"></param>
private void GetSheetData(ISheet Sheet)
{
if (Sheet == default)
{
return null;
}
IRow row;
// 1. 获取行数
var rowCount = Sheet.LastRowNum;
// 从第四行(下标为3)开始获取数据，前三行是表头
// 如果从第一行开始，则i=0就可以了
for (int i = 3; i <= rowCount; i++)
{
var dataTable = new DataTable_Model();
// 获取具体行
row = Sheet.GetRow(i);
if (row != null)
{
// 2. 获取行对应的列数
var column = row.LastCellNum;
for (int j = 0; j < column; j++)
{
// 3. 获取某行某列对应的单元格数据
var cellValue = row.GetCell(j).ToString();
// 4. 输出单元格数据
Console.Wlite(cellValue+" ");
}
// 换行
Console.WliteLine();
}
}
}
二、修改 Excel
2-1 修改单元格数据
流程图
代码
/// <summary>
/// 修改 Field Sheet
/// </summary>
/// <param name="SheetName"></param>
/// <returns></returns>
private void UpdateSheet(string SheetName, string FilePath)
{
// 创建文件流
FileStream fsWrite = new FileStream(FilePath, FileMode.Open, FileAccess.Write);
try
{
// 1. 通过Sheet名 获取对应的ISeet--其中 ReadWorkbook 为读取Excel文档时获取
var sheet = ReadWorkbook.GetSheet(SheetName);
// 2. 获取行数
int rowCount = sheet.LastRowNum;
for (int i = 0; i < rowCount; i++)
{
// 3. 获取行对应的列数
int columnount = sheet.GetRow(i).LastCellNum;
for (int j = 0; j < columnount; j++)
{
// 4. 获取某行某列对应的单元格数据
// 其中前三行设计为表头，故i+3，这里可以自己定义
var sheetCellValue = sheet.GetRow(i + 3).GetCell(j);
// 5. 向单元格传值，以覆盖对应的单元格数据
sheetCellValue.SetCellValue(sheetCellValue + "Update");
}
}
// 6. 对 Workbook 的修改写入文件流，对文件进行相应操作
ReadWorkbook.Write(fsWrite);
}
catch (Exception ex)
{
throw ex;
}
finally
{
// 7. 关闭文件流：报不报错都关闭
fsWrite.Close();
}
}
2-2 修改指定行数据
流程图
代码
/// <summary>
/// 修改 Field Sheet
/// </summary>
/// <param name="SheetName"></param>
/// <returns></returns>
private void UpdateSheet(string SheetName, string FilePath,int RowCount)
{
// 创建文件流
FileStream fsWrite = new FileStream(FilePath, FileMode.Open, FileAccess.Write);
try
{
// 1. 通过Sheet名 获取对应的ISeet--其中 ReadWorkbook 为读取Excel文档时获取
var sheet = ReadWorkbook.GetSheet(SheetName);
// 2. 获取行对应的列数
int column = sheet.GetRow(RowCount).LastCellNum;
for (int j = 0; j < column ; j++)
{
// 3. 获取某行某列对应的单元格数据
// 其中前三行设计为表头，故i+3，这里可以自己定义
var sheetCellValue = sheet.GetRow(RowCount).GetCell(j);
// 4. 向单元格传值，以覆盖对应的单元格数据
sheetCellValue.SetCellValue(sheetCellValue + "Update");
}
// 5. 对 Workbook 的修改写入文件流，对文件进行相应操作
ReadWorkbook.Write(fsWrite);
}
catch (Exception ex)
{
throw ex;
}
finally
{
// 7. 关闭文件流：报不报错都关闭
fsWrite.Close();
}
}
三、删除 Excel
3-1 删除 Sheet 表
流程图
代码
/// <summary>
/// 删除其中一个Sheet
/// Bug:删除后，无Sheet表存在BUG
/// </summary>
/// <param name="SheetName"></param>
/// <param name="FilePath"></param>
/// <returns></returns>
public bool RemoveOneSheet(string SheetName, string FilePath)
{
var Result = false;
// 创建文件流
FileStream fsWrite = new FileStream(FilePath, FileMode.Open, FileAccess.Write);
try
{
// 1. 通过Sheet名字查找Sheet下标
var sheetIndex = ReadWorkbook.GetSheetIndex(SheetName);
if (sheetIndex >= 0)
{
// 2. 通过Sheet下标移除 Sheet
ReadWorkbook.RemoveSheetAt(sheetIndex);
// 3. 对 Workbook 的修改写入文件流，对文件进行相应操作
ReadWorkbook.Write(fsWrite);
Result = true;
}
return Result;
}
catch (Exception ex)
{
throw ex;
}
finally
{
// 4. 关闭文件流：报不报错都关闭
fsWrite.Close();
}
}
【注意】：若Excel 表格删除后 没有Sheet表格，打开Excel时会出现错误。
【参考解决思路】：删除指定Sheet表格后，判断Excel中是否存在Sheet表，若存在则写入Excel表格；反之，删除该Excel文件。（仅供参考）
3-2 清空 Sheet 表指定行数据
只清空数据，不删除改行（ 该方法存在BUG，暂时无法使用。）
流程图
代码
/// <summary>
/// 清空 Sheet指定行数据
/// </summary>
/// <param name="Row"></param>
/// <param name="SheetName"></param>
/// <param name="FilePath"></param>
/// <returns></returns>
public bool EmptySheetRow(int RowNum, string SheetName, string FilePath)
{
var Result = false;
FileStream fsWrite = new FileStream(FilePath, FileMode.Open, FileAccess.Write);
try
{
1. 通过Sheet名 获取对应的 ISheet
ISheet sheet_Table = ReadWorkbook.GetSheet(SheetName);
if (sheet_Table != null)
{
// 2. 定位到要删除的指定行
IRow row = sheet_Table.GetRow(RowNum - 1);
if (row != null)
{
// 3. 清空行数据
sheet_Table.RemoveRow(row);
// 4. 对 Workbook 的修改写入文件流，对文件进行相应操作；
ReadWorkbook.Write(fsWrite);
Result = true;
}
}
else
{
new Exception("This the Sheet does not exist");
}
return Result;
}
catch (Exception ex)
{
throw ex;
}
finally
{
fsWrite.Close();
}
}
3-3 删除 Sheet 表指定行
Sheet 不提供 直接删除行的数据，这里利用上下移动的方法，删除指定行。
向上移动行过程
流程图
代码
/// <summary>
/// 删除 Sheet指定行数据
/// </summary>
/// <param name="StartNum">起始行</param>
/// <param name="EndNum">终止行</param>
/// <param name="SheetName"></param>
/// <param name="FilePath"></param>
/// <returns></returns>
public bool RemoveSheetRow(int StartNum, int EndNum, string SheetName, string FilePath)
{
var Result = false;
FileStream fsWrite = new FileStream(FilePath, FileMode.Open, FileAccess.Write);
try
{
// 1. 通过Sheet名 获取对应的ISheet
ISheet sheetTable = ReadWorkbook.GetSheet(SheetName);
if (sheetTable == null)
{
new Exception("This the Sheet does not exist");
}
// 2. 确定移动行数：保证EndNum >= StartNum
int moveNum = EndNum - StartNum;
// 3. 向上移动moveNum 行
for (int i = 0; i <= moveNum; i++)
{
// 向上移动:第EndNum+1到第sheetTable.LastRowNum+1行向上（-n）移动n行，-n只能为-1
sheetTable.ShiftRows(EndNum, sheetTable.LastRowNum, -1);
// 循环一次则向上移动一次，移动后需要开始移动的行数-1，则需要EndNum -1。不理解的话参考上方图片
EndNum -= 1;
}
//向打开的这个xls文件中写入数据
ReadWorkbook.Write(fsWrite);
Result = true;
return Result;
}
catch (Exception ex)
{
throw ex;
}
finally
{
fsWrite.Close();
}
}
四、写入 Excel
4-1 创建 WorkBook
流程图
代码
/// <summary>
/// 写入IWorkbook
/// </summary>
public IWorkbook WriteWorkbook = null;
/// <summary>
/// 获取写入WorkBook
/// </summary>
public void GetWriteWorkbook(string FilePath)
{
// 获取扩展名
string ExtensionName = System.IO.Path.GetExtension(FilePath);
// 把xls写入workbook中 2003版本
if (ExtensionName.Equals(".xls"))
{
WriteWorkbook = new HSSFWorkbook();
}
// 把xlsx 写入workbook中 2007版本
else if (ExtensionName.Equals(".xlsx"))
{
WriteWorkbook = new XSSFWorkbook();
}
else
{
WriteWorkbook = null;
}
}
4-2 写入 Excel 文件
流程图
代码
/// <summary>
/// Table 实体类数据转 表格数据
/// </summary>
/// <param name="FilePath"></param>
/// <returns></returns>
private void TableDataToCell(string FilePath)
{
FileStream fsWrite = new FileStream(FilePath, FileMode.Create, FileAccess.Write);
try
{
// 1. 在WriteWorkbook 上添加名为 SheetTest 的数据表
ISheet sheet=WriteWorkbook.CreateSheet("SheetTest ");
// 2. 定义行数
var rowCount = 6;
// 3. 定义列数
int columnount = 8;
for (int i = 0; i < rowCount; i++)
{
// 4. 创建行
IRow row = sheet.CreateRow(i);
for (int j = 0; j < columnount; j++)
{
// 5. 创建某行某列对应的单元格
ICell cell = row.CreateCell(j);
// 6. 向单元格添加值
cell.SetCellValue($"第{i}行 第{j}列");
// 添加表格样式
cell.CellStyle = ExpandFliePath.OtherRowStyle();
}
}
//7. 将表单写入文件流
WriteWorkbook.Write(fsWrite);
}
catch (Exception ex)
{
throw ex;
}
finally
{
// 关闭文件流
fsWrite.Close();
}
}
五、单元格样式
定义样式，这里定义的样式比较简单。
单元格样式在创建或获取到单元格时 , cell.CellStyle =OtherRowStyle(); 可以获取该样式。
/// <summary>
/// 样式
/// </summary>
/// <returns></returns>
public static ICellStyle OtherRowStyle()
{
var workbook = WriteWorkbook;
ICellStyle cellStyle = workbook.CreateCellStyle();
IFont font = workbook.CreateFont();
font.FontName = "微软雅黑";
//font.FontHeightInPoints = 15;
设置字体加粗样式
//font.IsBold = true;
// 使用SetFont方法将字体样式添加到单元格样式中
cellStyle.SetFont(font);
// 单元格样式：水平对齐居中
cellStyle.Alignment = HorizontalAlignment.Center;
//边框
cellStyle.BorderBottom = BorderStyle.Thin;
cellStyle.BorderLeft = BorderStyle.Thin;
cellStyle.BorderRight = BorderStyle.Thin;
cellStyle.BorderTop = BorderStyle.Thin;
// 自动换行
cellStyle.WrapText = true;
背景色
//cellStyle.FillForegroundColor = BlueGrey.Index;
return cellStyle;
}
树立目标，保持活力，gogogo！
