简介:
NPOI是源于一个用于读取xls,doc,ppt文档的POI 项目,POI是Java项目,后面因为有.Net的市场,于是将POI移植到.Net上便是NPOI。
特点:
对环境没有过多要求,不需要Windows系统预装office环境。特别适用于服务器上,一般服务器不会装office。
缺点:
在Excel数据量大的情况下,渲染Excel样式性能较慢。
具体实现:
1、dll下载以及引用
下载地址:https://npoi.codeplex.com/releases。或者通过NuGet直接搜索下载。
引用下面几个dll
![](https://img.laitimes.com/img/9ZDMuAjOiMmIsIjOiQnIsIyZuBnL3MjM3QTM1YTMyITMxgTMwIzLc52YucWbp5GZzNmLn9Gbi1yZtl2Lc9CX6MHc0RHaiojIsJye.png)
2、引用命名空间
using NPOI.HSSF.UserModel;
using NPOI.HSSF.Util;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using NPOI.XSSF.UserModel;
3、具体实现
1、全局变量
#region 私有变量
/// <summary>
/// Excel文件工作簿
/// </summary>
private static IWorkbook _workbook;
/// <summary>
/// Excel文件工作表
/// </summary>
private static ISheet _worksheet;
/// <summary>
/// Excel文件路径
/// </summary>
private static string _filePath = string.Empty;
/// <summary>
/// excel版本
/// 默认为2007
/// </summary>
private static string _excelVersion = "2007";
#endregion
2、调用方法
/// <summary>
/// 创建一个空白的Excel文件
/// 用于写Excel
/// 写的时候,还需要添加Sheet
/// </summary>
/// <param name="strFilePath">文件路径</param>
public static void CreateExcel(string strFilePath)
{
_filePath = strFilePath;
using (FileStream fs = new FileStream(strFilePath, FileMode.Create, FileAccess.ReadWrite))
{
if (strFilePath.EndsWith(".xls"))//97-2003低版本的
{
_workbook = new HSSFWorkbook();
_excelVersion = "2003";
}
else//高版本的2007
{
_workbook = new XSSFWorkbook();
_excelVersion = "2007";
}
CreateSheet();
fs.Close();
}
}
/// <summary>
/// 保存Excel
/// </summary>
public static void SaveExcel()
{
if (!File.Exists(_filePath))
{
throw new Exception(string.Format("文件:{0},不存在!", _filePath));
}
using (FileStream fsOut = new FileStream(_filePath, FileMode.Create, FileAccess.Write))
{
_workbook.Write(fsOut); //写入到当前已经打开的excel文件
fsOut.Close();
}
}
/// <summary>
/// Excel文件另存为
/// </summary>
/// <param name="strSavePath">文件命名</param>
public static void SaveExcelAs(string strSavePath)
{
if (!Directory.Exists(Path.GetDirectoryName(strSavePath)))
{
throw new Exception(string.Format("文件另存为的目录:{0},不存在", Path.GetDirectoryName(strSavePath)));
}
using (FileStream fsOut = new FileStream(strSavePath, FileMode.Create, FileAccess.Write))
{
_workbook.Write(fsOut); //写入到excel文件
fsOut.Close();
}
}
/// <summary>
/// 关闭Exel文件
/// 部分变量初始化
/// </summary>
public static void CloseExcel()
{
if (_workbook != null)
{
_workbook.Close();
_workbook = null;
}
if (_worksheet != null)
{
_workbook = null;
}
GC.Collect();
}
/// <summary>
/// 设置sheet
/// 默认的活动的sheet
/// </summary>
/// <returns></returns>
public static void SetSheet()
{
if (_workbook == null)
{
throw new Exception("工作簿为null,不能获取sheet");
}
ISheet sheet = _workbook.GetSheetAt(_workbook.ActiveSheetIndex);
if (sheet == null)
{
throw new Exception("获取到的sheet为null");
}
_worksheet = sheet;
}
/// <summary>
/// 设置指定索引的sheet
/// </summary>
/// <param name="index"></param>
/// <returns></returns>
public static void SetSheet(int index)
{
if (_workbook == null)
{
throw new Exception("工作簿为null,不能获取sheet");
}
if (index >= GetSheetNum() || index < 0)
{
throw new Exception(string.Format("Sheet索引[{0}]不能大于等于Sheet数量[{1}],或者小于0", index, GetSheetNum()));
}
ISheet sheet = _workbook.GetSheetAt(index);
if (sheet == null)
{
throw new Exception("获取到的sheet为null");
}
_worksheet = sheet;
}
/// <summary>
/// 设置指定名称的sheet
/// </summary>
/// <param name="index"></param>
/// <returns></returns>
/// </summary>
/// <param name="strSheetName"></param>
/// <returns></returns>
public static void SetSheet(string strSheetName)
{
if (_workbook == null)
{
throw new Exception("工作簿为null,不能获取sheet");
}
ISheet sheet = _workbook.GetSheet(strSheetName);
if (sheet == null)
{
throw new Exception("获取到的sheet为null");
}
_worksheet = sheet;
}
/// <summary>
/// 获取工作表数量
/// </summary>
/// <returns></returns>
public static int GetSheetNum()
{
if (_workbook == null)
{
throw new Exception("工作簿为null,不能获取sheet");
}
return _workbook.NumberOfSheets;
//CellRangeAddress.ValueOf(range);
//ICellStyle cellStyle = _workbook.CreateCellStyle();
}
/// <summary>
/// 创建sheet
/// </summary>
public static void CreateSheet()
{
if (_workbook == null)
{
throw new Exception("工作簿为null,不能获取sheet");
}
_worksheet = _workbook.CreateSheet();
}
/// <summary>
/// 创建指定名称的sheet
/// </summary>
/// <param name="strSheetName"></param>
public static void CreateSheet(string strSheetName)
{
if (_workbook == null)
{
throw new Exception("工作簿为null,不能获取sheet");
}
_worksheet= _workbook.CreateSheet(strSheetName);
}
/// <summary>
/// 删除指定索引的sheet
/// </summary>
/// <param name="index"></param>
public static void DeleteSheet(int index)
{
if (index >= GetSheetNum() || index < 0)
{
throw new Exception(string.Format("Sheet索引[{0}]不能大于等于Sheet数量[{1}],或者小于0", index, GetSheetNum()));
}
_workbook.RemoveSheetAt(index);
}
/// <summary>
/// 获取总行数
/// </summary>
/// <returns></returns>
public static int GetRowNum()
{
if (_worksheet == null)
{
throw new Exception("工作表不能为null");
}
return _worksheet.LastRowNum;
}
/// <summary>
/// 获取指定行的总列数
/// </summary>
/// <param name="rowIndex">从1开始</param>
/// <returns></returns>
public static int GetColNum(int rowIndex)
{
if (rowIndex < 1)
{
throw new Exception("参数rowInde不能小于1");
}
if (_worksheet == null)
{
throw new Exception("工作表不能为null");
}
return _worksheet.GetRow(rowIndex-1).LastCellNum;
}
/// <summary>
/// 获取指定单元格的值
/// </summary>
/// <param name="rowIndex">从1开始</param>
/// <param name="colIndex">从1开始</param>
/// <returns></returns>
public static string GetCellValue(int rowIndex, int colIndex)
{
if (rowIndex < 1 || colIndex < 1)
{
throw new Exception("参数rowIndex或colIndex不能小于1");
}
if (_worksheet == null)
{
throw new Exception("工作表不能为null");
}
object result = "";
if (_worksheet.GetRow(rowIndex - 1) == null)
{
return "";
}
ICell cell = _worksheet.GetRow(rowIndex - 1).GetCell(colIndex - 1);
if (cell != null)
{
switch (cell.CellType)//需要根据不同的单元格格式获取值,有公式的按照公式计算出结果
{
case CellType.Blank:
result = "";
break;
case CellType.Boolean:
result = cell.BooleanCellValue;
break;
case CellType.Error:
result = cell.ErrorCellValue;
break;
case CellType.Formula://公式
try
{
IFormulaEvaluator formulaEvaluator;
if (_excelVersion == "2007")
{
formulaEvaluator = new XSSFFormulaEvaluator(_workbook);
}
else
{
formulaEvaluator = new HSSFFormulaEvaluator(_workbook);
}
formulaEvaluator.EvaluateInCell(cell);
result = cell.ToString();
}
catch
{
if (DateUtil.IsCellDateFormatted(cell))//日期
{
result = cell.DateCellValue;
}
else
{
result = cell.NumericCellValue;
}
}
break;
case CellType.Numeric:
if (DateUtil.IsCellDateFormatted(cell))//日期
{
result = cell.DateCellValue;
}
else
{
result = cell.NumericCellValue;
}
break;
case CellType.String:
result = cell.StringCellValue.Trim();
break;
case CellType.Unknown:
result = cell.ToString();
break;
default:
break;
}
return result.ToString();
}
else
{
return null;
}
}
/// <summary>
/// 获取单元格的值
/// </summary>
/// <param name="rowIndex">行</param>
/// <param name="colIndex">列</param>
/// <returns></returns>
public static string GetCellValueNotForm(int rowIndex, int colIndex)
{
//不需要根据不同单元格的格式获取值。
if (rowIndex < 1 || colIndex < 1)
{
throw new Exception("参数rowIndex或colIndex不能小于1");
}
if (_worksheet == null)
{
throw new Exception("工作表不能为null");
}
object result = "";
if (_worksheet.GetRow(rowIndex - 1) == null)
{
return "";
}
ICell cell = _worksheet.GetRow(rowIndex - 1).GetCell(colIndex - 1);
if (cell != null)
{
return cell.ToString();
}
else
{
return null;
}
}
/// <summary>
/// 设置指定单元格的值
/// </summary>
/// <param name="rowIndex">从1开始</param>
/// <param name="colIndex">从1开始</param>
/// <param name="value">值</param>
/// <param name="valueType">值类型,默认为1
/// 0:数字格式
/// 1:字符串格式
/// 2:公式格式
/// 3:日期格式
/// 4:布尔类型值
/// </param>
public static void SetCellValue(int rowIndex, int colIndex, string value,int valueType=1)
{
if (rowIndex < 1 || colIndex < 1)
{
throw new Exception("参数rowIndex或colIndex不能小于1");
}
if (_worksheet == null)
{
throw new Exception("工作表不能为null");
}
IRow row = _worksheet.GetRow(rowIndex - 1);
if (row == null)//为NULL,则创建行
{
row = _worksheet.CreateRow(rowIndex - 1);
}
ICell cell = row.GetCell(colIndex - 1);
if (cell == null)//为NULL,则创建单元格
{
cell = row.CreateCell(colIndex - 1);
}
switch (valueType)
{
case 0://数字格式
cell.SetCellValue(string.IsNullOrEmpty(value) ? 0 : double.Parse(value));
break;
case 1://字符串格式,默认值
cell.SetCellValue(value);
break;
case 2://公式
cell.SetCellFormula(value);
break;
case 3://日期时间
cell.SetCellValue(string.IsNullOrEmpty(value) ? DateTime.Now : Convert.ToDateTime(value));
break;
case 4://日期时间
cell.SetCellValue(string.IsNullOrEmpty(value)?false:Convert.ToBoolean(value));
break;
default:
cell.SetCellValue(value);
break;
}
}
一起分享知识~