Excel帮助类
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 SmooreCommon.Excel { /// <summary> /// NPOIExcel /// </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) { if (value == null) return; 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 = (short)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; cell.CellStyle.BorderLeft = BorderStyle.Thin; cell.CellStyle.BorderRight = BorderStyle.Thin; cell.CellStyle.BorderTop = BorderStyle.Thin; cell.CellStyle.BorderBottom = BorderStyle.Thin; } } /// <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); } } /// <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); } } } } }