C# 使用NPOI导入数据 NPOIHelper.cs
NPOIHelper.cs
C# 全选
/// <summary>
/// NPOI组件帮助类
/// </summary>
public class NPOIHelper
{
/// <summary>
/// 获取Excel第1行列头
/// </summary>
/// <param name="file">excel文件</param>
/// <param name="sheetName">表格名</param>
/// <param name="rowIndex">列头序号，0:第1行</param>
/// <returns></returns>
public static List<String> GetColumnNames(string file, string sheetName, int rowIndex = 0)
{
List<String> result = new List<String>();
IWorkbook workbook;
string fileExt = Path.GetExtension(file).ToLower();
using (FileStream fs = new FileStream(file, FileMode.Open, FileAccess.Read))
{
//XSSFWorkbook 适用XLSX格式，HSSFWorkbook 适用XLS格式
if (fileExt == ".xlsx")
{
workbook = new XSSFWorkbook(fs);
}
else if (fileExt == ".xls")
{
workbook = new HSSFWorkbook(fs);
}
else
{
workbook = null;
}
if (workbook == null) { return null; }
ISheet sheet = workbook.GetSheet(sheetName);
//表头
NPOI.SS.UserModel.IRow header = sheet.GetRow(rowIndex);
for (int i = 0; i < header.LastCellNum; i++)
{
object obj = GetCellValue(file, workbook, header.GetCell(i));
if (obj == null || obj.ToString() == string.Empty)
{
break;
}
else
{
result.Add(obj.ToString());
}
}
}
return result;
}
/// <summary>
/// 获取所有表格名称(Sheet)
/// </summary>
/// <param name="file"></param>
/// <returns></returns>
public static List<String> GetSheetNames(string file)
{
List<String> result = new List<String>();
IWorkbook workbook;
string fileExt = Path.GetExtension(file).ToLower();
using (FileStream fs = new FileStream(file, FileMode.Open, FileAccess.Read))
{
//XSSFWorkbook 适用XLSX格式，HSSFWorkbook 适用XLS格式
if (fileExt == ".xlsx")
{
workbook = new XSSFWorkbook(fs);
}
else if (fileExt == ".xls")
{
workbook = new HSSFWorkbook(fs);
}
else
{
workbook = null;
}
if (workbook == null) { return null; }
for (int i = 0; i <= workbook.NumberOfSheets - 1; i++)
{
result.Add(workbook.GetSheetAt(i).SheetName);
}
}
return result;
}
/// <summary>
/// 获取Excel有效行数
/// </summary>
/// <param name="sheet"></param>
/// <param name="validateColumnIndex">用于校验资料的列序号</param>
/// <returns></returns>
public static int GetMaxRows(ISheet sheet, int validateColumnIndex = 0)
{
if (sheet == null) { return 0; }
try
{
IRow row;
var rowIndex = 0;
object value = null;
while (true)
{
row = sheet.GetRow(rowIndex);
if (row != null)
{
value = GetValueByCellStyle(row.Cells[validateColumnIndex], row.Cells[validateColumnIndex].CellType);
if (value == null || String.IsNullOrWhiteSpace(value.ToString()))
{
return rowIndex;
}
else
{
rowIndex++;
}
}
else //无效行，退出
{
return rowIndex;
}
}
}
catch
{
return sheet.LastRowNum;
}
}
/// <summary>
/// 获取Excel有效列数
/// </summary>
/// <param name="sheet"></param>
/// <param name="validateColumnIndex">用于校验资料的行序号</param>
/// <returns></returns>
public static int GetMaxColumns(ISheet sheet, int validateRowIndex = 0)
{
IRow row = sheet.GetRow(validateRowIndex);
var coIndex = 0;
object value = null;
while (true)
{
if (coIndex >= row.PhysicalNumberOfCells)
{
return row.PhysicalNumberOfCells;
}
value = GetValueByCellStyle(row.Cells[coIndex], row.Cells[coIndex].CellType);
if (value == null || String.IsNullOrWhiteSpace(value.ToString()))
{
return coIndex;
}
else
{
coIndex++;
}
}
}
/// <summary>
/// Excel导入成DataTable
/// </summary>
/// <param name="file">导入路径(包含文件名与扩展名)</param>
/// <returns></returns>
public static DataTable ToTable(string file, string sheetName, Type type)
{
DataTable dt = new DataTable();
IWorkbook workbook;
string fileExt = Path.GetExtension(file).ToLower();
using (FileStream fs = new FileStream(file, FileMode.Open, FileAccess.Read))
{
//XSSFWorkbook 适用XLSX格式，HSSFWorkbook 适用XLS格式
if (fileExt == ".xlsx")
{
workbook = new XSSFWorkbook(fs);
}
else if (fileExt == ".xls")
{
workbook = new HSSFWorkbook(fs);
}
else
{
workbook = null;
}
if (workbook == null) { return null; }
ISheet sheet = workbook.GetSheet(sheetName);
var maxRows = GetMaxRows(sheet, 0); //excel有效资料行数
var maxCols = GetMaxColumns(sheet, 0);//excel有效资料列数
//表头
IRow header = sheet.GetRow(sheet.FirstRowNum);
List<int> columns = new List<int>();
for (int i = 0; i < maxCols; i++)
{
object obj = GetCellValue(file, workbook, header.GetCell(i));
var colType = type.GetProperty(obj.ToString()).PropertyType;
if (colType.IsGenericType)
{
colType = colType.GenericTypeArguments[0];
}
dt.Columns.Add(new DataColumn(obj.ToString(), colType));
columns.Add(i);
}
//数据 , 从第2行开始
for (int i = sheet.FirstRowNum + 1; i <= maxRows - 1; i++)
{
DataRow dr = dt.NewRow();
foreach (int j in columns)
{
try
{
dr[j] = GetCellValue(file, workbook, sheet.GetRow(i).GetCell(j));
}
catch (Exception ee)
{
continue;
}
}
dt.Rows.Add(dr);
}
}
return dt;
}
/// <summary>
/// 获取单元格类型
/// </summary>
/// <param name="cell"></param>
/// <returns></returns>
private static object GetCellValue(string strFileName, IWorkbook workbook, ICell cell)
{
if (cell == null) return null;
switch (cell.CellType)
{
case CellType.Blank: //BLANK:
return null;
case CellType.Boolean: //BOOLEAN:
return cell.BooleanCellValue;
case CellType.Numeric: //NUMERIC:
if (DateUtil.IsCellDateFormatted(cell))
return cell.DateCellValue;
else
return cell.NumericCellValue;
case CellType.String: //STRING:
return cell.StringCellValue;
case CellType.Error: //ERROR:
return cell.ErrorCellValue;
case CellType.Formula: //FORMULA:
object rv = null;
if (Path.GetExtension(strFileName).ToLower().Trim() == ".xlsx")
{
XSSFFormulaEvaluator eva = new XSSFFormulaEvaluator(workbook);
if (eva.Evaluate(cell).CellType == CellType.Numeric)
{
rv = eva.Evaluate(cell).NumberValue;
}
else
{
rv = eva.Evaluate(cell).StringValue;
}
}
else
{
HSSFFormulaEvaluator eva = new HSSFFormulaEvaluator(workbook);
if (eva.Evaluate(cell).CellType == CellType.Numeric)
{
rv = eva.Evaluate(cell).NumberValue;
}
else
{
rv = eva.Evaluate(cell).StringValue;
}
}
return rv;
default:
return "=" + cell.CellFormula;
}
}
/// <summary>
/// 根据单元格的类型获取单元格的值
/// </summary>
/// <param name="rowCell"></param>
/// <param name="type"></param>
/// <returns></returns>
public static string GetValueByCellStyle(ICell rowCell, CellType? type)
{
string value = string.Empty;
switch (type)
{
case CellType.String:
value = rowCell.StringCellValue;
break;
case CellType.Numeric:
if (DateUtil.IsCellInternalDateFormatted(rowCell))
{
value = DateTime.FromOADate(rowCell.NumericCellValue).ToString();
}
else if (DateUtil.IsCellDateFormatted(rowCell))
{
value = DateTime.FromOADate(rowCell.NumericCellValue).ToString();
}
//有些情况，时间搓？数字格式化显示为时间,不属于上面两种时间格式
else if (rowCell.CellStyle.GetDataFormatString() == null)
{
value = DateTime.FromOADate(rowCell.NumericCellValue).ToString();
}
else
{
value = rowCell.NumericCellValue.ToString();
}
break;
case CellType.Boolean:
value = rowCell.BooleanCellValue.ToString();
break;
case CellType.Error:
value = ErrorEval.GetText(rowCell.ErrorCellValue);
break;
case CellType.Formula:
// TODO: 是否存在 嵌套 公式类型
value = GetValueByCellStyle(rowCell, rowCell?.CachedFormulaResultType);
break;
}
return value;
}
/// <summary>
/// Excel文件是否有效
/// </summary>
/// <param name="file"></param>
/// <param name="msg"></param>
/// <returns></returns>
internal static bool IsFileValid(string file, out string msg)
{
msg = string.Empty;
try
{
if (!File.Exists(file)) throw new Exception("Excel文件不存在！");
IWorkbook workbook;
string fileExt = Path.GetExtension(file).ToLower();
using (FileStream fs = new FileStream(file, FileMode.Open, FileAccess.Read))
{
//XSSFWorkbook 适用XLSX格式，HSSFWorkbook 适用XLS格式
if (fileExt == ".xlsx")
{
workbook = new XSSFWorkbook(fs);
}
else if (fileExt == ".xls")
{
workbook = new HSSFWorkbook(fs);
}
else
{
workbook = null;
}
if (workbook == null)
{
throw new Exception("加载Excel文件失败！");
}
workbook.Close();
}
return true;
}
catch (Exception ex)
{
msg = ex.Message;
return false;
}
}
}版权声明：本文为开发框架文库发布内容,转载请附上原文出处连接
NewDoc C/S框架网
