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);}}}}
若有收获，就点个赞吧
0 人点赞
