using System;
using NPOI.HSSF.UserModel;
using NPOI.XSSF.UserModel;
using NPOI.SS.UserModel;
using System.IO;
using System.Collections.Generic;
using NPOI.SS.Util;
using System.Text;
namespace ExamKing.Application.Utils.Excel
{
/// <summary>
/// NPOIExcel 针对2.5.1.0出的NPOI操作Excel类的常用方法;
/// </summary>
public class NPOIExcel
{
/// <summary>
/// 记录打开的Excel的路径
/// </summary>
private string npoiFileName;
/// <summary>
/// 工作簿,全局变量
/// </summary>
protected IWorkbook workbook;
/// <summary>
/// 获取工作表或是创建的
/// </summary>
private NPOI.SS.UserModel.ISheet sheet;
/// <summary>
/// 构造方法
/// </summary>
public NPOIExcel()
{
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>
/// 设置单元格的属性(字体,大小,颜色,对齐方式)(前提是该行列范围内有值,否则报错)
/// </summary>
/// <param name="sheetName"></param>
/// <param name="startRow">起始行</param>
/// <param name="startCol">起始列</param>
/// <param name="endRow">结束行</param>
/// <param name="endCol">结束列</param>
/// <param name="fontName">字体</param>
/// <param name="indexedColors">字体颜色</param>
/// <param name="fontSize">字体大小</param>
/// <param name="horizontalAlignment">水平排列</param>
public void SetCellProperty(string sheetName, int startRow, int startCol, int endRow, int endCol, string fontName, IndexedColors indexedColors, int fontSize, HorizontalAlignment horizontalAlignment)
{
SetCellProperty(GetSheet(sheetName), startRow, startCol, endRow, endCol, fontName, indexedColors, fontSize, horizontalAlignment);
}
/// <summary>
/// 设置一个单元格的属性(字体,大小,颜色,对齐方式)
/// </summary>
/// <param name="sheetName"></param>
/// <param name="Startx"></param>
/// <param name="Starty"></param>
/// <param name="Endx"></param>
/// <param name="Endy"></param>
/// <param name="fontName"></param>
/// <param name="indexedColors"></param>
/// <param name="fontSize"></param>
/// <param name="horizontalAlignment"></param>
private void SetCellProperty(ISheet sheetName, int startRow, int startCol, int endRow, int endCol, string fontName, IndexedColors indexedColors, int fontSize, HorizontalAlignment horizontalAlignment)
{
ICellStyle style = workbook.CreateCellStyle();
IFont font = workbook.CreateFont();
font.Color = indexedColors.Index;
font.FontName = fontName;
font.FontHeightInPoints = fontSize;
//font.Boldweight = Boldweight;//字体加粗
style.Alignment = horizontalAlignment;
//style.VerticalAlignment = VerticalAlignment.Center; //垂直居中
style.SetFont(font);
foreach (ICell cell in GetCellsOfRange(sheetName, startRow, startCol, endRow, endCol))
{
cell.CellStyle = style;
}
}
/// <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四个参数为:起始行,结束行,起始列,结束列
sheetName.AddMergedRegion(new CellRangeAddress(startRow, endRow, startCol, endCol));
}
/// <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(3, widthValue * 256);
}
}
}
}