C# NOPI读取Excel

来源：互联网发布：阿里巴巴排名软件编辑：程序博客网时间：2024/05/29 09:50

上次工作遇到oledb导入Excel 客户环境不支持，用过一个博客的文档，使用起来很方便推荐分享，原文点击打开链接
/// <summary>
/// Excel的辅助类
/// </summary>
public class ExcelHelper
{
/// <summary>
/// 读取excel到datatable中
/// </summary>
/// <param name="excelPath">excel地址</param>
/// <param name="sheetIndex">sheet索引</param>
/// <returns>成功返回datatable，失败返回null</returns>
public static DataTable ImportExcel(string excelPath, int sheetIndex)
{

IWorkbook workbook = null;//全局workbook
ISheet sheet;//sheet
DataTable table = null;
try
{
FileInfo fileInfo = new FileInfo(excelPath);//判断文件是否存在
if (fileInfo.Exists)
{
FileStream fileStream = fileInfo.OpenRead();//打开文件，得到文件流
switch (fileInfo.Extension)
{
//xls是03，用HSSFWorkbook打开，.xlsx是07或者10用XSSFWorkbook打开
case ".xls": workbook = new HSSFWorkbook(fileStream); break;
case ".xlsx": workbook = new XSSFWorkbook(fileStream); break;
default: break;
}
fileStream.Close();//关闭文件流
}
if (workbook != null)
{
sheet = workbook.GetSheetAt(sheetIndex);//读取到指定的sheet
table = new DataTable();//初始化一个table

IRow headerRow = sheet.GetRow(0);//获取第一行，一般为表头
int cellCount = headerRow.LastCellNum;//得到列数

for (int i = headerRow.FirstCellNum; i < cellCount; i++)
{
DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue);//初始化table的列
table.Columns.Add(column);
}
//遍历读取cell
for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++)
{
NPOI.SS.UserModel.IRow row = sheet.GetRow(i);//得到一行
DataRow dataRow = table.NewRow();//新建一个行

for (int j = row.FirstCellNum; j < cellCount; j++)
{
ICell cell = row.GetCell(j);//得到cell
if (cell == null)//如果cell为null，则赋值为空
{
dataRow[j] = "";
}
else
{
dataRow[j] = row.GetCell(j).ToString();//否则赋值
}
}

table.Rows.Add(dataRow);//把行加入到table中

}
}
return table;

}
catch (Exception e)
{
return table;
}
finally
{
//释放资源
if (table != null) { table.Dispose(); }
workbook = null;
sheet = null;
}
}

}

添加引用如下

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;
using System.Data;
using System.Collections;
using NPOI.SS.UserModel;
using NPOI.HSSF.UserModel;
using NPOI.XSSF.UserModel;

后来改动了下成全索引表格导入,不过表头这个问题,没有细致处理,从第一个表之后,后面的表头会一并导入不怎么用有网友需要可以自行改动

/// <summary>
/// Excel的辅助类
/// </summary>
public class ExcelHelper
{
/// <summary>
/// 读取excel到datatable中
/// </summary>
/// <param name="excelPath">excel地址</param>
/// <param name="sheetIndex">sheet索引</param>
/// <returns>成功返回datatable，失败返回null</returns>
public static DataTable ImportExcel(string excelPath, int sheetIndex)
{


IWorkbook workbook = null;//全局workbook
ISheet sheet;//sheet
DataTable table = null;
try
{
FileInfo fileInfo = new FileInfo(excelPath);//判断文件是否存在
if (fileInfo.Exists)
{
FileStream fileStream = fileInfo.OpenRead();//打开文件，得到文件流
switch (fileInfo.Extension)
{
//xls是03，用HSSFWorkbook打开，.xlsx是07或者10用XSSFWorkbook打开
case ".xls": workbook = new HSSFWorkbook(fileStream); break;
case ".xlsx": workbook = new XSSFWorkbook(fileStream); break;
default: break;
}
fileStream.Close();//关闭文件流
}
if (workbook != null)
{
table = new DataTable();//初始化一个table

sheetIndex = workbook.NumberOfSheets;//MX2017-05-02 11:01:39
for (int k = 0; k < sheetIndex; k++)
{
if (k!=0)
{
// table = null;

sheet = null;

}
sheet = workbook.GetSheetAt(k);//读取到指定的sheet


IRow headerRow ;
if (k==0)
{
headerRow = sheet.GetRow(0);//获取第一行，一般为表头
}
else
{
headerRow = sheet.GetRow(1);
}

int cellCount = headerRow.LastCellNum;//得到列数

if (k==0)
{
for (int i = headerRow.FirstCellNum; i < cellCount; i++)
{
DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue);//初始化table的列
table.Columns.Add(column);
}
}

else
{
for (int i = headerRow.FirstCellNum; i < cellCount; i++)

{
// DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue);//初始化table的列
// table.Columns.Add(column);
}
}

//遍历读取cell
for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++)
{
NPOI.SS.UserModel.IRow row = sheet.GetRow(i);//得到一行
DataRow dataRow = table.NewRow();//新建一个行

for (int j = row.FirstCellNum; j < cellCount; j++)
{
ICell cell = row.GetCell(j);//得到cell
if (cell == null)//如果cell为null，则赋值为空
{
dataRow[j] = "";
}
else
{
dataRow[j] = row.GetCell(j).ToString();//否则赋值
}
}

table.Rows.Add(dataRow);//把行加入到table中

}
}

}
return table;

}
catch (Exception e)
{
return table;
}
finally
{
//释放资源
if (table != null) { table.Dispose(); }
workbook = null;
sheet = null;
}
}

}

阅读全文

0 0