NpoiExcel帮助类
对https://blog.csdn.net/qq_43529569/article/details/121691525做的部分修改,部分代码待验证
NpoiExcel帮助类
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.IO;
using System.Windows.Forms;
namespace Common.Helper
{
public class NpoiExcelHelper
{
public IWorkbook RWorkbook = null;
public string OpenExcel()
{
string filePath = string.Empty;
try
{
filePath = GetConfigFileName();
if (string.IsNullOrEmpty(filePath))
return filePath;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "提示", MessageBoxButtons.OK, MessageBoxIcon.Error);
return filePath;
}
return filePath;
}
private string GetConfigFileName()
{
string filePath = string.Empty;
OpenFileDialog fd = new OpenFileDialog();
fd.Filter = "Excel文件|*.xls;*.xlsx;*.xlsm";
fd.RestoreDirectory = true;
fd.InitialDirectory = Environment.CurrentDirectory;
DialogResult dr = fd.ShowDialog();
if (dr == DialogResult.OK || dr == DialogResult.Yes)
{
filePath = fd.FileName;
return filePath;
}
else
{
MessageBox.Show("请选择配置文件");
return "";
}
}
/// <summary>
/// 获取读取 WorkBook
/// </summary>
public void GetReadWorkbook(string excelPath)
{
// 获取扩展名
string _extensionName = System.IO.Path.GetExtension(excelPath);
// 文件流
FileStream _fs = new FileStream(excelPath, FileMode.Open, FileAccess.ReadWrite);
// 把xls写入workbook中 2003版本
if (_extensionName.Equals(".xls"))
{
RWorkbook = new HSSFWorkbook(_fs);
}
// 把xlsx 写入workbook中 2
else if (_extensionName.Equals(".xlsx"))
{
RWorkbook = new XSSFWorkbook(_fs);
}
else
{
RWorkbook = null;
MessageBox.Show("此文件非excel文件,无法读取数据");
}
_fs.Close();
}
/// <summary>
/// 获取表中的Sheet名称
/// </summary>
public List<ISheet> Sheets = null;
/// <summary>
/// 获取所有 Sheet表
/// </summary>
public void GetSheets()
{
// 获取表
Sheets = new List<ISheet>();
var _sheetCount = RWorkbook.NumberOfSheets;
for (int i = 0; i < _sheetCount; i++)
{
Sheets.Add(RWorkbook.GetSheetAt(i));
}
}
/// <summary>
/// 获取 Sheet 表数据
/// </summary>
/// <param name="sheet"></param>
private void GetSheetData(ISheet sheet)
{
IRow _row;
//从第一行开始获取数据
for (int i = 0; i <= sheet.LastRowNum; i++)
{
// 获取具体行
_row = sheet.GetRow(i);
if (_row != null)
{
// 获取行对应的列数
var _column = _row.LastCellNum;
for (int j = 0; j < _column; j++)
{
// 获取某行某列对应的单元格数据
var _cellValue = _row.GetCell(j).ToString()+",";
// 4. 输出单元格数据
//Console.Wlite(_cellValue + " ");
}
}
}
}
/// <summary>
/// 删除其中一个Sheet
/// </summary>
/// <param name="sheetName"></param>
/// <param name="excelPath"></param>
/// <returns></returns>
public bool RemoveOneSheet(string sheetName, string excelPath)
{
// 创建文件流
FileStream _fsWrite = new FileStream(excelPath, FileMode.Open, FileAccess.Write);
try
{
// 1. 通过Sheet名字查找Sheet下标
var _sheetIndex = RWorkbook.GetSheetIndex(sheetName);
if (_sheetIndex >= 0)
{
// 2. 通过Sheet下标移除 Sheet
RWorkbook.RemoveSheetAt(_sheetIndex);
// 3. 对 Workbook 的修改写入文件流,对文件进行相应操作
RWorkbook.Write(_fsWrite, true);
return true;
}
else
{
return false;
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
_fsWrite.Close();
}
}
/// <summary>
/// 清空 Sheet指定行数据
/// </summary>
/// <param name="rowNum"></param>
/// <param name="sheetName"></param>
/// <param name="excelPath"></param>
/// <returns></returns>
public bool EmptySheetRow(int rowNum, string sheetName, string excelPath)
{
FileStream _fsWrite = new FileStream(excelPath, FileMode.Open, FileAccess.Write);
try
{
//1.通过Sheet名 获取对应的 ISheet
ISheet _sheet = RWorkbook.GetSheet(sheetName);
if (_sheet != null)
{
// 2. 定位到要删除的指定行
IRow _row = _sheet.GetRow(rowNum - 1);
if (_row != null)
{
// 3. 清空行数据
_sheet.RemoveRow(_row);
// 4. 对 Workbook 的修改写入文件流,对文件进行相应操作;
RWorkbook.Write(_fsWrite,true);
return true;
}
}
else
{
new Exception("This the Sheet does not exist");
return false;
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
_fsWrite.Close();
}
return false;
}
/// <summary>
/// 修改Sheet行数据
/// </summary>
/// <param name="sheetName"></param>
/// <returns></returns>
private void UpdateSheet(string sheetName, string excelPath, int rowCount)
{
// 创建文件流
FileStream _fsWrite = new FileStream(excelPath, FileMode.Open, FileAccess.Write);
try
{
// 1. 通过Sheet名 获取对应的ISheet--其中 RWorkbook 为读取Excel文档时获取
var _sheet = RWorkbook.GetSheet(sheetName);
// 2. 获取行对应的列数
int _column = _sheet.GetRow(rowCount).LastCellNum;
for (int j = 0; j < _column; j++)
{
// 3. 获取某行某列对应的单元格数据
var _sheetCellValue = _sheet.GetRow(rowCount).GetCell(j);
// 4. 向单元格传值,以覆盖对应的单元格数据
_sheetCellValue.SetCellValue(_sheetCellValue + "Update");
}
// 5. 对 Workbook 的修改写入文件流,对文件进行相应操作
RWorkbook.Write(_fsWrite,true);
}
catch (Exception ex)
{
throw ex;
}
finally
{
// 6. 关闭文件流
_fsWrite.Close();
}
}
/// <summary>
/// 修改 Sheet单元格数据
/// </summary>
/// <param name="sheetName"></param>
/// <returns></returns>
private void UpdateSheet(string sheetName, string excelPath)
{
// 创建文件流
FileStream _fsWrite = new FileStream(excelPath, FileMode.Open, FileAccess.Write);
try
{
// 1. 通过Sheet名 获取对应的ISeet--其中 ReadWorkbook 为读取Excel文档时获取
var _sheet = RWorkbook.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. 获取某行某列对应的单元格数据
var _sheetCellValue = _sheet.GetRow(i).GetCell(j);
// 5. 向单元格传值,以覆盖对应的单元格数据
_sheetCellValue.SetCellValue(_sheetCellValue + "Update");
}
}
// 6. 对 Workbook 的修改写入文件流,对文件进行相应操作
RWorkbook.Write(_fsWrite,true);
}
catch (Exception ex)
{
throw ex;
}
finally
{
// 7. 关闭文件流
_fsWrite.Close();
}
}
/// <summary>
/// 写入IWorkbook
/// </summary>
public IWorkbook WWorkbook = null;
/// <summary>
/// 获取写入WorkBook
/// </summary>
public void GetWriteWorkbook(string excelPath)
{
// 获取扩展名
string _extensionName = System.IO.Path.GetExtension(excelPath);
// 把xls写入workbook中
if (_extensionName.Equals(".xls"))
{
WWorkbook = new HSSFWorkbook();
}
// 把xlsx 写入workbook中
else if (_extensionName.Equals(".xlsx"))
{
WWorkbook = new XSSFWorkbook();
}
else
{
WWorkbook = null;
}
}
// <summary>
/// Table 实体类数据转 表格数据
/// </summary>
/// <param name="excelPath"></param>
/// <returns></returns>
private void TableDataToCell(string excelPath)
{
FileStream _fsWrite = new FileStream(excelPath, FileMode.Create, FileAccess.Write);
try
{
// 1. 在WriteWorkbook 上添加名为 sheetDemo 的数据表
ISheet _sheet = WWorkbook.CreateSheet("sheetDemo ");
// 2. 定义行数
var _rowCount = 8;
// 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. 将表单写入文件流
WWorkbook.Write(_fsWrite,true);
}
catch (Exception ex)
{
throw ex;
}
finally
{
// 关闭文件流
_fsWrite.Close();
}
}
}
}