using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Data.OleDb;
using System.Collections.Specialized;
using System.IO;
using NPOI.SS.UserModel;
using NPOI.HSSF.UserModel;
using NPOI.XSSF.UserModel;
namespace Common
{
public class ExcelHelper
{
/// <summary>
/// 将excel 转成datatable
/// </summary>
/// <param name="filePath"></param>
/// <returns></returns>
public static DataSet ExecleDataSet(string filePath)
{
string OleDbConnection = "Provider=Microsoft.Jet.OleDb.4.0;" + "data source=" + filePath + ";Extended Properties='Excel 8.0; HDR=YES; IMEX=1'";
OleDbConnection conn = new OleDbConnection(OleDbConnection);
try
{
conn.Open();
}
catch (Exception ee)
{
if (ee.Message.Contains("外部表不是预期的格式"))
{
OleDbConnection = "Provider=Microsoft.Ace.OleDb.12.0;" + "data source=" + filePath + ";Extended Properties='Excel 12.0; HDR=YES; IMEX=1'";
conn.ConnectionString = OleDbConnection;
conn.Open();
}
else
{
return null;
}
}
DataSet ds = new DataSet();
OleDbDataAdapter odda = new OleDbDataAdapter("select * from [Sheet1$]",conn);
odda.Fill(ds);
conn.Close();
return ds;
}
/// <summary>
/// 在DataTable中添加一序号列，编号从1依次递增
/// </summary>
/// <param >DataTable</param>
/// <returns></returns>
public static DataTable AddSeriNumToDataTable(DataTable dt)
{
//需要返回的值
DataTable dtNew;
if (dt.Columns.IndexOf("序号") >= 0)
{
dtNew = dt;
}
else //添加一序号列,并且在第一列
{
int rowLength = dt.Rows.Count;
int colLength = dt.Columns.Count;
DataRow[] newRows = new DataRow[rowLength];
dtNew = new DataTable();
//在第一列添加“序号”列
dtNew.Columns.Add("序号");
for (int i = 0; i < colLength; i++)
{
dtNew.Columns.Add(dt.Columns[i].ColumnName);
//复制dt中的数据
for (int j = 0; j < rowLength; j++)
{
if (newRows[j] == null)
newRows[j] = dtNew.NewRow();
//将其他数据填充到第二列之后，因为第一列为新增的序号列
newRows[j][i + 1] = dt.Rows[j][i];
}
}
foreach (DataRow row in newRows)
{
dtNew.Rows.Add(row);
}
}
//对序号列填充，从1递增
for (int i = 0; i < dt.Rows.Count; i++)
{
dtNew.Rows[i]["序号"] = i + 1;
}
return dtNew;
}
/// <summary>
/// 转换DataTable 标题
/// </summary>
/// <param name="dt"></param>
/// <param name="cols"></param>
public static void ChangeDtTitle(DataTable dt, NameValueCollection cols)
{
foreach (DataColumn dc in dt.Columns)
{
string name = cols[dc.ColumnName];
if (name != null)
dc.ColumnName = name;
}
}
public static DataTable RenderDataTableFromExcel(Stream excelFileStream)
{
using (excelFileStream)
{
IWorkbook workbook = new HSSFWorkbook(excelFileStream);
ISheet sheet = workbook.GetSheetAt(0);//取第一个表
DataTable table = new DataTable();
IRow headerRow = sheet.GetRow(0);//第一行为标题行
int cellCount = headerRow.LastCellNum;//LastCellNum = PhysicalNumberOfCells
int rowCount = sheet.LastRowNum;//LastRowNum = PhysicalNumberOfRows - 1
//handling header.
for (int i = headerRow.FirstCellNum; i < cellCount; i++)
{
DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue);
table.Columns.Add(column);
}
for (int i = (sheet.FirstRowNum + 1); i <= rowCount; i++)
{
IRow row = sheet.GetRow(i);
if (row == null)
break;
if (row != null)
{
if (row.GetCell(0) == null)
{
break;
}
if (row.GetCell(0).ToString().Trim() == "")
{
break;
}
DataRow dataRow = table.NewRow();
for (int j = row.FirstCellNum; j < cellCount; j++)
{
if (row.GetCell(j) != null)
dataRow[j] = row.GetCell(j).ToString();
}
table.Rows.Add(dataRow);
}
}
workbook = null;
sheet = null;
return table;
}
}
public static DataTable RenderDataTableFromExcel2007(Stream excelFileStream)
{
DataTable table = new DataTable();
try
{
using (excelFileStream)
{
IWorkbook workbook = new XSSFWorkbook(excelFileStream);
ISheet sheet = workbook.GetSheetAt(0);//取第一个表
IRow headerRow = sheet.GetRow(0);//第一行为标题行
int cellCount = headerRow.LastCellNum;//LastCellNum = PhysicalNumberOfCells
int rowCount = sheet.LastRowNum;//LastRowNum = PhysicalNumberOfRows - 1
//handling header.
for (int i = headerRow.FirstCellNum; i < cellCount; i++)
{
string columnname = headerRow.GetCell(i).StringCellValue;
if (columnname == "")
continue;
DataColumn column = new DataColumn(columnname);
table.Columns.Add(column);
}
for (int i = (sheet.FirstRowNum + 1); i <= rowCount; i++)
{
IRow row = sheet.GetRow(i);
if (row == null)
break;
if (row.FirstCellNum < 0)
{
continue;
}
else if (row.GetCell(row.FirstCellNum).ToString().Trim() == "")
{
continue;
}
DataRow dataRow = table.NewRow();
if (row != null)
{
for (int j = row.FirstCellNum; j < cellCount; j++)
{
if (row.GetCell(j) != null)
{
switch (row.GetCell(j).CellType)
{ //空数据类型处理
case CellType.Blank:
dataRow[j] = "";
break;
case CellType.String:
dataRow[j] = row.GetCell(j).StringCellValue;
break;
case CellType.Numeric: //数字类型
if (HSSFDateUtil.IsCellDateFormatted(row.GetCell(j)))
{
dataRow[j] = row.GetCell(j).DateCellValue;
}
else
{
dataRow[j] = row.GetCell(j).NumericCellValue;
}
break;
case CellType.Formula:
dataRow[j] = row.GetCell(j).NumericCellValue;
break;
default:
dataRow[j] = "";
break;
}
}
}
}
table.Rows.Add(dataRow);
}
workbook = null;
sheet = null;
return table;
}
}
catch (Exception ex)
{
string message = ex.Message;
return null;
}
}
}
}