using NPOI.HSSF.UserModel;
|
using NPOI.SS.UserModel;
|
using System;
|
using System.Collections.Generic;
|
using System.Data;
|
using System.IO;
|
|
namespace TProduct.Common
|
{
|
public static class ExcelHelperEx
|
{
|
/// <summary>
|
/// 将excel导入到datatable
|
/// </summary>
|
/// <param name="fs">Stream文件流</param>
|
/// <param name="isColumnName">第一行是否是列名</param>
|
/// <param name="isReplace">是否替换表头</param>
|
/// <returns>返回datatable</returns>
|
public static DataTable ExcelToDataTable(string fileName, Dictionary<string, object> list = null, bool isColumnName = true, bool isReplace = true)
|
{
|
DataTable dataTable = null;
|
DataColumn column = null;
|
DataRow dataRow = null;
|
IWorkbook workbook = null;
|
ISheet sheet = null;
|
IRow row = null;
|
ICell cell = null;
|
int startRow = 0;
|
FileStream fs = null;
|
try
|
{
|
fs = new FileStream(fileName, FileMode.Open, FileAccess.Read);
|
workbook = new HSSFWorkbook(fs);
|
|
if (workbook != null)
|
{
|
sheet = workbook.GetSheetAt(0);//读取第一个sheet，当然也可以循环读取每个sheet
|
dataTable = new DataTable();
|
if (sheet != null)
|
{
|
int rowCount = sheet.LastRowNum;//总行数
|
if (rowCount > 0)
|
{
|
IRow firstRow = sheet.GetRow(0);//第一行
|
int cellCount = firstRow.LastCellNum;//列数
|
|
//构建datatable的列
|
if (isColumnName)
|
{
|
startRow = 1;//如果第一行是列名，则从第二行开始读取
|
for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
|
{
|
cell = firstRow.GetCell(i);
|
if (cell != null)
|
{
|
if (cell.StringCellValue != null)
|
{
|
column = new DataColumn(cell.StringCellValue);
|
dataTable.Columns.Add(column);
|
}
|
}
|
}
|
}
|
else
|
{
|
for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
|
{
|
column = new DataColumn("column" + (i + 1));
|
dataTable.Columns.Add(column);
|
}
|
}
|
|
//填充行
|
for (int i = startRow; i <= rowCount; ++i)
|
{
|
row = sheet.GetRow(i);
|
if (row == null) continue;
|
|
dataRow = dataTable.NewRow();
|
for (int j = row.FirstCellNum; j < cellCount; ++j)
|
{
|
cell = row.GetCell(j);
|
if (cell == null)
|
{
|
dataRow[j] = "";
|
}
|
else
|
{
|
//CellType(Unknown = -1,Numeric = 0,String = 1,Formula = 2,Blank = 3,Boolean = 4,Error = 5,)
|
switch (cell.CellType)
|
{
|
case CellType.Blank:
|
dataRow[j] = "";
|
break;
|
|
case CellType.Numeric:
|
short format = cell.CellStyle.DataFormat;
|
//对时间格式（2015.12.5、2015/12/5、2015-12-5等）的处理
|
if (format == 14 || format == 31 || format == 57 || format == 58)
|
dataRow[j] = cell.DateCellValue;
|
else
|
dataRow[j] = cell.NumericCellValue;
|
break;
|
|
case CellType.String:
|
dataRow[j] = cell.StringCellValue;
|
break;
|
}
|
}
|
}
|
dataTable.Rows.Add(dataRow);
|
}
|
}
|
}
|
}
|
|
if (isReplace)
|
{
|
if (dataTable != null)
|
{
|
if (list != null)
|
{
|
//循环datatable
|
for (int i = 0; i < dataTable.Columns.Count; i++)
|
{
|
//获取datatable的标头
|
var s = dataTable.Columns[i].ColumnName.Trim();
|
foreach (var item1 in list)
|
{
|
//判断标头与key是否相等
|
if (s.Equals(item1.Value.ToString().Trim()))
|
{
|
//相等替换掉原来的标头
|
dataTable.Columns[i].ColumnName = item1.Key.ToString().Trim();
|
}
|
}
|
}
|
}
|
}
|
}
|
return dataTable;
|
}
|
catch (Exception)
|
{
|
if (fs != null)
|
{
|
fs.Close();
|
}
|
return null;
|
}
|
}
|
|
}
|
}
|
