using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace ConsoleApp
{
/// <summary>
/// NPOIExcel 针对2.5.1.0出的NPOI操作Excel类的常用方法；
/// </summary>
public class NPOIHelper
{
/// <summary>
/// 记录打开的Excel的路径
/// </summary>
private string npoiFileName;
/// <summary>
/// 工作簿，全局变量
/// </summary>
protected IWorkbook workbook;
/// <summary>
/// 获取工作表或是创建的
/// </summary>
private NPOI.SS.UserModel.ISheet sheet;
/// <summary>
/// 构造方法
/// </summary>
public NPOIHelper()
{
npoiFileName = "";
workbook = new XSSFWorkbook();
}
/// <summary>
/// 打开
/// </summary>
/// <param name="filename">excel文件路径</param>
public void Open(string filename)
{
using (FileStream fileStream = new FileStream(filename, FileMode.Open, FileAccess.Read))
{
string ext = Path.GetExtension(filename).ToLower();
if (ext == ".xlsx")
workbook = new XSSFWorkbook(fileStream);
else
{
workbook = new HSSFWorkbook(fileStream);
}
}
npoiFileName = filename;
}
/// <summary>
/// 创建一个Excel对象，该对象为可见的
/// </summary>
public void Create(string sheetname = "Sheet1")
{
sheet = workbook.CreateSheet(sheetname);
}
/// <summary>
/// 获取一个工作表
/// </summary>
/// <param name="SheetName">工作表名称</param>
/// <returns></returns>
public ISheet GetSheet(string SheetName)
{
return (sheet = workbook.GetSheet(SheetName) ?? workbook.CreateSheet(SheetName));
}
/// <summary>
/// 添加一个工作表
/// </summary>
/// <param name="SheetName">工作表名称</param>
/// <returns></returns>
public ISheet AddSheet(string SheetName)
{
ISheet s = workbook.CreateSheet(SheetName);
return s;
}
/// <summary>
/// 删除一个工作表
/// </summary>
/// <param name="SheetName">工作表名称</param>
public void DelSheet(string SheetName)
{
int index = workbook.GetNameIndex(SheetName);
workbook.RemoveSheetAt(index);
}
/// <summary>
/// 重命名一个工作表
/// </summary>
/// <param name="OldSheetName">老工作表名称</param>
/// <param name="NewSheetName">新工作表名称</param>
/// <returns></returns>
public ISheet ReNameSheet(string OldSheetName, string NewSheetName)
{
int index = workbook.GetNameIndex(OldSheetName);
workbook.SetSheetName(index, NewSheetName);
return workbook.GetSheetAt(index);
}
/// <summary>
/// 设置单元格的值
/// </summary>
/// <param name="sheetName">工作表名称</param>
/// <param name="row">行</param>
/// <param name="col">列</param>
/// <param name="value">设置的值</param>
private void SetCellValue(ISheet sheetName, int row, int col, object value)
{
IRow _row = sheetName.GetRow(row) ?? sheetName.CreateRow(row);
ICell cell = _row.GetCell(col) ?? _row.CreateCell(col);
string valuetype = value.GetType().Name.ToLower();
switch (valuetype)
{
case "string"://字符串类型
case "system.string":
case "datetime":
case "system.datetime"://日期类型
case "boolean"://布尔型
case "system.boolean"://布尔型
cell.SetCellType(CellType.String);
cell.SetCellValue(value.ToString());
break;
case "byte":
case "int":
case "int16":
case "int32":
case "int64":
case "system.int16"://整型
case "system.int32":
case "system.int64":
case "system.byte":
cell.SetCellType(CellType.Numeric);
cell.SetCellValue(Convert.ToInt32(value));
break;
case "single":
case "system.single":
case "double":
case "system.double":
case "decimal":
case "system.decimal":
cell.SetCellType(CellType.Numeric);
cell.SetCellValue(Convert.ToDouble(value));
break;
case "dbnull"://空值处理
case "system.dbnull"://空值处理
cell.SetCellValue("");
break;
default:
cell.SetCellValue(value.ToString());
break;
}
}
/// <summary>
/// 要设值的工作表的名称 X行Y列 value 值
/// </summary>
/// <param name="sheetName">工作表名</param>
/// <param name="row">行</param>
/// <param name="col">列</param>
/// <param name="value">插入的值</param>
public void SetCellValue(string sheetName, int row, int col, object value)
{
ISheet s = GetSheet(sheetName);
SetCellValue(s, row, col, value);
}
/// <summary>
/// 获取单元格值
/// </summary>
/// <param name="sheetName">工作表名</param>
/// <param name="row">行</param>
/// <param name="col">列</param>
/// <returns></returns>
public ICell GetCell(string sheetName, int row, int col)
{
return GetSheet(sheetName).GetRow(row).Cells[col];
}
/// <summary>
/// 获取单元格值
/// </summary>
/// <param name="sheetName">工作表名</param>
/// <param name="row">行</param>
/// <param name="col">列</param>
/// <returns></returns>
private ICell GetCell(ISheet sheetName, int row, int col)
{
//return ws.GetRow(row).Cells[col];
return sheetName.GetRow(row).GetCell(col);
}
/// <summary>
/// 获取当前sheet的所有有数据的单元格
/// </summary>
/// <param name="sheetName"></param>
/// <returns></returns>
public List<ICell> GetHasValueCell(string sheetName)
{
List<ICell> values = new List<ICell>();
var sheet = GetSheet(sheetName);
int maxRow = sheet.LastRowNum + 1;
int maxCol = MaxCellIndex(sheetName);
for (int row = 0; row < maxRow; row++)
{
for (int col = 0; col < maxCol; col++)
{
try
{
//这一行查不到值的话会报错
ICell cell = sheet.GetRow(row)?.GetCell(col);
if (cell != null) values.Add(cell);
}
catch (Exception)
{
}
}
}
return values;
}
/// <summary>
/// 得到最大有效数据行索引
/// </summary>
/// <param name="sheetName"></param>
/// <returns></returns>
public int MaxRowIndex(string sheetName)
{
var sheet = GetSheet(sheetName);
return sheet.LastRowNum;
}
/// <summary>
/// 得到最大有效数据列从1开始
/// </summary>
/// <param name="sheetName"></param>
/// <returns></returns>
public int MaxCellIndex(string sheetName)
{
int maxCellIndex = -1;
var sheet = GetSheet(sheetName);
for (int rowCnt = sheet.FirstRowNum; rowCnt <= sheet.LastRowNum; rowCnt++)//迭代所有行
{
IRow row = sheet.GetRow(rowCnt);
if (row != null && row.LastCellNum > maxCellIndex)
{
maxCellIndex = row.LastCellNum;
}
}
return maxCellIndex;
}
/// <summary>
/// 得到最大行和最大列的索引行从0开始列从0开始
/// </summary>
/// <param name="sheetName"></param>
/// <returns></returns>
public (int, int) GetMaxRowIndexAndCellIndex(string sheetName)
{
return (MaxRowIndex(sheetName), MaxCellIndex(sheetName) - 1);
}
/// <summary>
/// 得到最大行和最大列的索引，行从0开始列从1开始
/// </summary>
/// <param name="sheetName"></param>
/// <returns></returns>
public (int, int) GetMaxRowIndexAndCell(string sheetName)
{
return (MaxRowIndex(sheetName), MaxCellIndex(sheetName));
}
/// <summary>
/// 清除某行的值和样式
/// </summary>
/// <param name="sheetName"></param>
/// <param name="rowIndex"></param>
public void ClearRowValue(string sheetName, int rowIndex)
{
var sheet = GetSheet(sheetName);
if (sheet == null) return;
IRow row = sheet.GetRow(rowIndex);
if (row == null) return;
for (int i = 0; i < row.LastCellNum; i++)
{
try
{
ICell cell = row.GetCell(i);
cell.CellStyle = null;
cell.SetCellValue(string.Empty);
}
catch (Exception)
{
}
}
}
/// <summary>
/// 获取起止范围内的行列值
/// </summary>
/// <param name="sheetName"></param>
/// <param name="startRow"></param>
/// <param name="startCol"></param>
/// <param name="endRow"></param>
/// <param name="endCol"></param>
/// <returns></returns>
public IList<ICell> GetCellsOfRange(string sheetName, int startRow, int startCol, int endRow, int endCol)
{
return (GetCellsOfRange(GetSheet(sheetName), startRow, startCol, endRow, endCol));
}
/// <summary>
/// 获取起止范围内的行列值
/// </summary>
/// <param name="sheetName"></param>
/// <param name="startRow"></param>
/// <param name="startCol"></param>
/// <param name="endRow"></param>
/// <param name="endCol"></param>
/// <returns></returns>
private IList<ICell> GetCellsOfRange(ISheet sheetName, int startRow, int startCol, int endRow, int endCol)
{
IList<ICell> allCell = new List<ICell>();
for (int i = startRow; i <= endRow; i++)
for (int j = startCol; j <= endCol; j++)
{
allCell.Add(GetCell(sheetName, i, j));
}
return allCell;
}
/// <summary>
/// 合并单元格
/// </summary>
/// <param name="sheetName"></param>
/// <param name="startRow"></param>
/// <param name="endRow"></param>
/// <param name="startCol"></param>
/// <param name="endCol"></param>
private void MergedCells(ISheet sheetName, int startRow, int endRow, int startCol, int endCol)
{
//CellRangeAddress四个参数为：起始行，结束行，起始列，结束列
var region = new CellRangeAddress(startRow, endRow, startCol, endCol);
sheetName.AddMergedRegion(region);
}
/// <summary>
/// 合并单元格
/// </summary>
/// <param name="sheetName"></param>
/// <param name="startRow"></param>
/// <param name="startCol"></param>
/// <param name="endRow"></param>
/// <param name="endCol"></param>
public void MergedCells(string sheetName, int startRow, int startCol, int endRow, int endCol)
{
MergedCells(GetSheet(sheetName), startRow, endRow, startCol, endCol);
}
/// <summary>
/// 文档另存为
/// </summary>
/// <param name="FileName"></param>
/// <returns></returns>
public bool SaveAs(string FileName)
{
npoiFileName = FileName;
try
{
using (FileStream fs = new FileStream(FileName, FileMode.Create, FileAccess.Write))
{
workbook.Write(fs);
}
return true;
}
catch
{
return false;
}
}
/// <summary>
/// 关闭
/// </summary>
public void Close()
{
workbook.Close();
}
/// <summary>
/// 自适应宽度
/// </summary>
/// <param name="sheetName">表名</param>
/// <param name="startCol">起始列</param>
/// <param name="endCol">结束列</param>
public void AutoColumnWidth(string sheetName, int startCol, int endCol)
{
AutoColumnWidth(GetSheet(sheetName), startCol, endCol);
}
/// <summary>
/// 自适应宽度
/// </summary>
/// <param name="sheet"></param>
/// <param name="cols"></param>
private void AutoColumnWidth(ISheet sheet, int startCol, int endCol)
{
for (int col = startCol; col <= endCol; col++)
{
sheet.AutoSizeColumn(col);//但是其实还是比实际文本要宽
}
}
/// <summary>
/// 设置起止范围的行高，单位为磅
/// </summary>
/// <param name="sheetName">工作表名称</param>
/// <param name="startRow">起始行</param>
/// <param name="endRow">结束行</param>
/// <param name="heightValue">设置的高值</param>
public void SetRowsHeight(string sheetName, int startRow, int endRow, int heightValue)
{
ISheet sheet = GetSheet(sheetName);
for (int i = startRow; i <= endRow; i++)
{
//sheet.GetRow(i).Height = Height * 20;
sheet.GetRow(i).HeightInPoints = heightValue;
}
}
/// <summary>
/// 设置起止列的宽度,单位为字符
/// </summary>
/// <param name="sheetName">工作表名称</param>
/// <param name="startCol">起始列</param>
/// <param name="endCol">结束列</param>
/// <param name="widthValue">设置的宽度值</param>
public void SetColumnsWidth(string sheetName, int startCol, int endCol, int widthValue)
{
ISheet sheet = GetSheet(sheetName);
for (int j = startCol; j <= endCol; j++)
{
sheet.SetColumnWidth(j, widthValue * 256);
}
}
/// <summary>
/// 插入新行
/// </summary>
/// <param name="sheetName">工作表名</param>
/// <param name="insertRowIndex">插入的行索引位置</param>
/// <param name="insertRowCount">插入的行数量</param>
/// <param name="formatRowIndex">获取插入行的参照样式的行索引</param>
public void InsertRow(string sheetName, int insertRowIndex, int insertRowCount, int formatRowIndex)
{
ISheet sheet = GetSheet(sheetName);
IRow formatRow = sheet.GetRow(formatRowIndex);
InsertRow(sheet, insertRowIndex, insertRowCount, formatRow);
}
/// <summary>
/// 插入新行
/// </summary>
/// <param name="sheetName"></param>
/// <param name="insertRowIndex"></param>
/// <param name="insertRowCount"></param>
/// <param name="formatRow"></param>
private void InsertRow(ISheet sheetName, int insertRowIndex, int insertRowCount, IRow formatRow)
{
sheet.ShiftRows(insertRowIndex, sheet.LastRowNum, insertRowCount, true, false);
for (int i = insertRowIndex; i < insertRowIndex + insertRowCount; i++)
{
IRow targetRow = null;
ICell sourceCell = null;
ICell targetCell = null;
targetRow = sheet.CreateRow(i);
for (int m = formatRow.FirstCellNum; m < formatRow.LastCellNum; m++)
{
sourceCell = formatRow.GetCell(m);
if (sourceCell == null)
{
continue;
}
targetCell = targetRow.CreateCell(m);
targetCell.CellStyle = sourceCell.CellStyle;
targetCell.SetCellType(sourceCell.CellType);
}
}
for (int i = insertRowIndex; i < insertRowIndex + insertRowCount; i++)
{
IRow firstTargetRow = sheet.GetRow(i);
ICell firstSourceCell = null;
ICell firstTargetCell = null;
for (int m = formatRow.FirstCellNum; m < formatRow.LastCellNum; m++)
{
firstSourceCell = formatRow.GetCell(m, MissingCellPolicy.CREATE_NULL_AS_BLANK);
if (firstSourceCell == null)
{
continue;
}
firstTargetCell = firstTargetRow.GetCell(m, MissingCellPolicy.CREATE_NULL_AS_BLANK);
firstTargetCell.CellStyle = firstSourceCell.CellStyle;
firstTargetCell.SetCellType(firstSourceCell.CellType);
}
}
}
//--------------------------------------------------------------------新增的方法-------------------------------------
#region 自定义样式
/// <summary>
/// 自定义样式
/// </summary>
/// <param name="fontSize"></param>
/// <param name="fontName"></param>
/// <param name="horizontalAlignment"></param>
/// <param name="verticalAlignment"></param>
/// <returns></returns>
public ICellStyle CreateCellStyle(int fontSize, string fontName, bool fontBold, HorizontalAlignment horizontal, VerticalAlignment vertical, BorderStyle borderStyle)
{
ICellStyle style = workbook.CreateCellStyle();
IFont font = workbook.CreateFont();
font.FontName = fontName;
font.FontHeightInPoints = (short)fontSize;
if (fontBold) font.Boldweight = (short)FontBoldWeight.Bold;//字体加粗
style.Alignment = horizontal;
style.VerticalAlignment = vertical;
style.SetFont(font);
style.BorderLeft = borderStyle;
style.BorderRight = borderStyle;
style.BorderTop = borderStyle;
style.BorderBottom = borderStyle;
return style;
}
/// <summary>
/// 得到数据行常规样式（11 字号+居中对齐）
/// </summary>
/// <returns></returns>
public ICellStyle GetNormalCellStyle()
{
return GetNormalCellStyle(11, HorizontalAlignment.Center);
}
public ICellStyle GetNormalCellStyle(int fontSize, HorizontalAlignment horizontal)
{
return CreateCellStyle(fontSize, "Arial", false, horizontal, VerticalAlignment.Center, BorderStyle.Thin);
}
/// <summary>
/// 得到标题样式(20字号+加粗+居中对齐)
/// </summary>
/// <returns></returns>
public ICellStyle GetHeaderCellStyle()
{
return CreateCellStyle(20, "Arial", true, HorizontalAlignment.Center, VerticalAlignment.Center, BorderStyle.Thin);
}
#endregion
#region 设置行列的边框，以及样式
/// <summary>
/// 设置指定列，指定范围行的边框 （传从0 开始的索引）
/// </summary>
public void SetCellRangeBorder(string sheetName, int cellIndex, int startRow, int endRow, ICellStyle cellStyle)
{
SetRengeBorder(sheetName, startRow, cellIndex, endRow, cellIndex, cellStyle);
}
/// <summary>
/// 设置指定行指定范围列的边框（传从0 开始的索引）
/// </summary>
public void SetRowRangeBorder(string sheetName, int rowIndex, int startCell, int endCell, ICellStyle cellStyle)
{
SetRengeBorder(sheetName, rowIndex, startCell, rowIndex, endCell, cellStyle);
}
/// <summary>
/// 设置指定范围的边框，（传从0 开始的索引）
/// </summary>
public void SetRengeBorder(string sheetName, int startRow, int startCell, int endRow, int endCell, ICellStyle cellStyle)
{
var sheet = GetSheet(sheetName);
for (int i = startRow; i <= endRow; i++)
{
for (int n = startCell; n <= endCell; n++)
{
var row = sheet.GetRow(i) ?? sheet.CreateRow(i);
var cell = row.GetCell(n);
if (cell == null)
{
cell = sheet.GetRow(i).CreateCell(n);
}
cell.CellStyle = cellStyle;
}
}
}
#endregion
#region 合并单元格，带样式
/// <summary>
/// 合并单元格常规样式
/// </summary>
/// <param name="sheetName"></param>
/// <param name="startRow"></param>
/// <param name="startCol"></param>
/// <param name="endRow"></param>
/// <param name="endCol"></param>
public void MergedCellsStyle(string sheetName, int startRow, int startCol, int endRow, int endCol)
{
MergedCellsStyle(sheetName, startRow, startCol, endRow, endCol, GetNormalCellStyle());
}
/// <summary>
/// 合并单元格指定样式
/// </summary>
/// <param name="sheetName"></param>
/// <param name="startRow"></param>
/// <param name="startCol"></param>
/// <param name="endRow"></param>
/// <param name="endCol"></param>
/// <param name="cellStyle"></param>
public void MergedCellsStyle(string sheetName, int startRow, int startCol, int endRow, int endCol, ICellStyle cellStyle)
{
MergedCells(GetSheet(sheetName), startRow, endRow, startCol, endCol);
for (int i = startRow; i <= endRow; i++)
{
SetRowRangeBorder(sheetName, i, startCol, endCol, cellStyle);
}
}
#endregion
/// <summary>
/// 设置表格内有效数据行样式（全部行）(填充完数据后调用)
/// </summary>
/// <param name="sheetName"></param>
/// <param name="cellStyle">样式</param>
/// <param name="isIgnoreHeader">是否忽略掉标题，前两行</param>
/// <param name="isAutoW">内容是否自适应宽度</param>
public void SetValidDataRowStyle(string sheetName, ICellStyle cellStyle, bool isIgnoreHeader = true, bool isAutoW = true)
{
var (row, cell) = GetMaxRowIndexAndCellIndex(sheetName);
int startRow = isIgnoreHeader ? 2 : 0;
SetRengeBorder(sheetName, startRow, 0, row, cell, cellStyle);
if (isAutoW) AutoColumnWidth(sheetName, 0, cell);
}
}
}
