excel操作类
using System;
using System.Configuration;
using System.Web;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;
using NPOI.HSSF.UserModel;
using NPOI.XSSF.UserModel;
using NPOI.SS.UserModel;
using System.IO;
namespace RR.AdminMange.Common
{
///
///
/// 常用工具类——Excel操作类
///
///
///
///
///
///
///
///
///
///
public class ExcelHelper
{
private ExcelHelper() { }
#region EXCEL版本
/// <summary>
/// EXCEL版本
/// </summary>
public enum ExcelVerion
{
/// <summary>
/// Excel97-2003版本
/// </summary>
Excel2003,
/// <summary>
/// Excel2007版本
/// </summary>
Excel2007
}
#endregion
#region 根据EXCEL路径生成OleDbConnectin对象
/// <summary>
/// 根据EXCEL路径生成OleDbConnectin对象
/// </summary>
/// <param name="ExcelFilePath">EXCEL文件相对于站点根目录的路径</param>
/// <param name="Verion">Excel数据驱动版本：97-2003或2007,分别需要安装数据驱动软件</param>
/// <returns>OleDbConnection对象</returns>
public static OleDbConnection CreateConnection(string ExcelFilePath, ExcelVerion Verion)
{
OleDbConnection Connection = null;
string strConnection = string.Empty;
try
{
switch (Verion)
{
case ExcelVerion.Excel2003: //读取Excel97-2003版本
strConnection = "Provider=Microsoft.Jet.OLEDB.4.0; " +
"Data Source=" + ExcelFilePath + ";Extended Properties=Excel 8.0";
break;
case ExcelVerion.Excel2007: //读取Excel2007版本
strConnection = "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties='Excel 12.0;HDR=YES';data source=" + ExcelFilePath;
break;
}
if (!string.IsNullOrEmpty(strConnection)) Connection = new OleDbConnection(strConnection);
}
catch (Exception)
{
}
return Connection;
}
#endregion
#region 创建一个OleDbCommand对象实例
/// <summary>
/// 创建一个OleDbCommand对象实例
/// </summary>
/// <param name="CommandText">SQL命令</param>
/// <param name="Connection">数据库连接对象实例OleDbConnection</param>
/// <param name="OleDbParameters">可选参数</param>
/// <returns></returns>
private static OleDbCommand CreateCommand(string CommandText, OleDbConnection Connection, params System.Data.OleDb.OleDbParameter[] OleDbParameters)
{
if (Connection.State == ConnectionState.Closed)
Connection.Open();
OleDbCommand comm = new OleDbCommand(CommandText, Connection);
if (OleDbParameters != null)
{
foreach (OleDbParameter parm in OleDbParameters)
{
comm.Parameters.Add(parm);
}
}
return comm;
}
#endregion
#region 执行一条SQL语句，返回一个DataSet对象
/// <summary>
/// 执行一条SQL语句，返回一个DataSet对象
/// </summary>
/// <param name="Connection">OleDbConnection对象</param>
/// <param name="CommandText">SQL语句</param>
/// <param name="OleDbParameters">OleDbParameter可选参数</param>
/// <returns>DataSet对象</returns>
public static DataSet ExecuteDataSet(OleDbConnection Connection, string CommandText, params OleDbParameter[] OleDbParameters)
{
DataSet ds = new DataSet();
try
{
OleDbCommand comm = CreateCommand(CommandText, Connection, OleDbParameters);
OleDbDataAdapter da = new OleDbDataAdapter(comm);
da.Fill(ds);
}
catch (Exception)
{
}
finally
{
if (Connection.State == ConnectionState.Open) Connection.Close();
}
return ds;
}
#endregion
#region 执行一条SQL语句,返回一个DataTable对象
/// <summary>
/// 执行一条SQL语句,返回一个DataTable对象
/// </summary>
/// <param name="Connection">OleDbConnection对象</param>
/// <param name="CommandText">SQL语句</param>
/// <param name="OleDbParameters">OleDbParameter可选参数</param>
/// <returns>DataSet对象</returns>
public static DataTable ExecuteDataTable(OleDbConnection Connection, string CommandText, params OleDbParameter[] OleDbParameters)
{
DataTable Dt = null;
try
{
OleDbCommand comm = CreateCommand(CommandText, Connection, OleDbParameters);
OleDbDataAdapter da = new OleDbDataAdapter(comm);
DataSet Ds = new DataSet();
da.Fill(Ds);
Dt = Ds.Tables[0];
}
catch (Exception)
{
}
finally
{
if (Connection.State == ConnectionState.Open) Connection.Close();
}
return Dt;
}
#endregion
#region 表示一组数据命令和一个数据库连接，它们用于填充 DataSet 和更新数据源。
/// <summary>
/// 表示一组数据命令和一个数据库连接，它们用于填充 DataSet 和更新数据源。
/// </summary>
/// <param name="Connection">OleDbConnection对象</param>
/// <param name="CommandText">SQL语句</param>
/// <param name="OleDbParameters">OleDbParameter可选参数</param>
/// <returns></returns>
public static OleDbDataAdapter ExecuteDataAdapter(OleDbConnection Connection, string CommandText, params System.Data.OleDb.OleDbParameter[] OleDbParameters)
{
OleDbDataAdapter Da = null;
try
{
OleDbCommand comm = CreateCommand(CommandText, Connection, OleDbParameters);
Da = new OleDbDataAdapter(comm);
OleDbCommandBuilder cb = new OleDbCommandBuilder(Da);
}
catch (Exception)
{
}
finally
{
if (Connection.State == ConnectionState.Open) Connection.Close();
}
return Da;
}
#endregion
#region 执行数据库语句返回受影响的行数，失败或异常返回-1[通常为:INSERT、DELETE、UPDATE 和 SET 语句等命令]。
/// <summary>
/// 执行数据库语句返回受影响的行数，失败或异常返回-1[通常为:INSERT、DELETE、UPDATE 和 SET 语句等命令]。
/// </summary>
/// <param name="Connection">OleDbConnection对象</param>
/// <param name="CommandText">SQL语句</param>
/// <param name="OleDbParameters">OleDbParameter可选参数</param>
/// <returns>受影响的行数</returns>
public static int ExecuteNonQuery(OleDbConnection Connection, string CommandText, params System.Data.OleDb.OleDbParameter[] OleDbParameters)
{
int i = -1;
try
{
if (Connection.State == ConnectionState.Closed) Connection.Open();
OleDbCommand comm = CreateCommand(CommandText, Connection, OleDbParameters);
i = comm.ExecuteNonQuery();
}
catch (Exception)
{
}
finally
{
if (Connection.State == ConnectionState.Open) Connection.Close();
}
return i;
}
#endregion
#region 执行数据库语句返回第一行第一列，失败或异常返回null
/// <summary>
/// 执行数据库语句返回第一行第一列，失败或异常返回null
/// </summary>
/// <param name="Connection">OleDbConnection对象</param>
/// <param name="CommandText">SQL语句</param>
/// <param name="OleDbParameters">OleDbParameter可选参数</param>
/// <returns>第一行第一列的值</returns>
public static object ExecuteScalar(OleDbConnection Connection, string CommandText, params System.Data.OleDb.OleDbParameter[] OleDbParameters)
{
object Result = null;
try
{
OleDbCommand comm = CreateCommand(CommandText, Connection, OleDbParameters);
Result = comm.ExecuteScalar();
}
catch (Exception)
{
}
finally
{
if (Connection.State == ConnectionState.Open) Connection.Close();
}
return Result;
}
#endregion
#region 执行数据库语句返回一个自进结果集流
/// <summary>
/// 执行数据库语句返回一个自进结果集流
/// </summary>
/// <param name="Connection">OleDbConnection对象</param>
/// <param name="CommandText">SQL语句</param>
/// <param name="OleDbParameters">OleDbParameter可选参数</param>
/// <returns>DataReader对象</returns>
public static OleDbDataReader ExecuteDataReader(OleDbConnection Connection, string CommandText, params System.Data.OleDb.OleDbParameter[] OleDbParameters)
{
OleDbDataReader Odr = null;
try
{
OleDbCommand comm = CreateCommand(CommandText, Connection, OleDbParameters);
Odr = comm.ExecuteReader();
}
catch (Exception)
{
}
finally
{
if (Connection.State == ConnectionState.Open) Connection.Close();
}
return Odr;
}
#endregion
#region 获取Excel中的所有工作簿
/// <summary>
/// 获取Excel中的所有工作簿
/// </summary>
/// <param name="Connection">OleDbConnection对象</param>
/// <returns></returns>
public static DataTable GetWorkBookName(OleDbConnection Connection)
{
DataTable Dt = null;
try
{
if (Connection.State == ConnectionState.Closed) Connection.Open();
Dt = Connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
}
catch (Exception)
{
}
finally
{
if (Connection.State == ConnectionState.Open) Connection.Close();
}
return Dt;
}
#endregion
/// <summary>
/// 读取Excel文件到DataSet中
/// </summary>
/// <param name="filePath">文件路径</param>
/// <returns></returns>
public static DataSet ToDataTable(string filePath,string fileName)
{
string connStr = "";
string fileType = System.IO.Path.GetExtension(fileName);
if (string.IsNullOrEmpty(fileType)) return null;
if (fileType == ".xls")
connStr = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + filePath + ";" + ";Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1\"";
else
connStr = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + filePath + ";" + ";Extended Properties=\"Excel 12.0;HDR=YES;IMEX=1\"";
string sql_F = "Select * FROM [{0}]";
OleDbConnection conn = null;
OleDbDataAdapter da = null;
DataTable dtSheetName = null;
DataSet ds = new DataSet();
try
{
// 初始化连接，并打开
conn = new OleDbConnection(connStr);
conn.Open();
// 获取数据源的表定义元数据
string SheetName = "";
dtSheetName = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
// 初始化适配器
da = new OleDbDataAdapter();
for (int i = 0; i < dtSheetName.Rows.Count; i++)
{
SheetName = (string)dtSheetName.Rows[i]["TABLE_NAME"];
if (SheetName.Contains("$") && !SheetName.Replace("'", "").EndsWith("$"))
{
continue;
}
da.SelectCommand = new OleDbCommand(String.Format(sql_F, SheetName), conn);
DataSet dsItem = new DataSet();
da.Fill(dsItem, SheetName);
ds.Tables.Add(dsItem.Tables[0].Copy());
}
}
catch (Exception ex)
{
}
finally
{
// 关闭连接
if (conn.State == ConnectionState.Open)
{
conn.Close();
da.Dispose();
conn.Dispose();
}
}
return ds;
}
/// <summary>
/// Excel某sheet中内容导入到DataTable中
/// 区分xsl和xslx分别处理
/// </summary>
/// <param name="filePath">Excel文件路径,含文件全名</param>
/// <param name="sheetName">此Excel中sheet名</param>
/// <returns></returns>
public static DataTable ExcelSheetImportToDataTable(string filePath, string sheetName)
{
DataTable dt = new DataTable();
HSSFWorkbook hssfworkbook = new HSSFWorkbook();
XSSFWorkbook xssfworkbook = new XSSFWorkbook();
if (Path.GetExtension(filePath).ToLower() == ".xls".ToLower())
{//.xls
#region .xls文件处理:HSSFWorkbook
try
{
using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read))
{
hssfworkbook = new HSSFWorkbook(file);
}
}
catch (Exception e)
{
throw e;
}
ISheet sheet = hssfworkbook.GetSheet(sheetName);
System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
HSSFRow headerRow = (HSSFRow)sheet.GetRow(0);
//一行最后一个方格的编号 即总的列数
for (int j = 0; j < (sheet.GetRow(0).LastCellNum); j++)
{
//SET EVERY COLUMN NAME
HSSFCell cell = (HSSFCell)headerRow.GetCell(j);
dt.Columns.Add(cell.ToString());
}
while (rows.MoveNext())
{
IRow row = (HSSFRow)rows.Current;
DataRow dr = dt.NewRow();
if (row.RowNum == 0) continue;//The firt row is title,no need import
for (int i = 0; i < row.LastCellNum; i++)
{
if (i >= dt.Columns.Count)//cell count>column count,then break //每条记录的单元格数量不能大于表格栏位数量 20140213
{
break;
}
ICell cell = row.GetCell(i);
//if ((i == 0) && (string.IsNullOrEmpty(cell.ToString()) == true))//每行第一个cell为空,break
//{
// break;
//}
if (cell == null)
{
dr[i] = null;
}
else
{
dr[i] = cell.ToString();
}
}
dt.Rows.Add(dr);
}
#endregion
}
else
{//.xlsx
#region .xlsx文件处理:XSSFWorkbook
try
{
using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read))
{
xssfworkbook = new XSSFWorkbook(file);
}
}
catch (Exception e)
{
throw e;
}
ISheet sheet = xssfworkbook.GetSheet(sheetName);
System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
XSSFRow headerRow = (XSSFRow)sheet.GetRow(0);
//一行最后一个方格的编号 即总的列数
for (int j = 0; j < (sheet.GetRow(0).LastCellNum); j++)
{
//SET EVERY COLUMN NAME
XSSFCell cell = (XSSFCell)headerRow.GetCell(j);
dt.Columns.Add(cell.ToString());
}
while (rows.MoveNext())
{
IRow row = (XSSFRow)rows.Current;
DataRow dr = dt.NewRow();
if (row.RowNum == 0) continue;//The firt row is title,no need import
for (int i = 0; i < row.LastCellNum; i++)
{
if (i >= dt.Columns.Count)//cell count>column count,then break //每条记录的单元格数量不能大于表格栏位数量 20140213
{
break;
}
ICell cell = row.GetCell(i);
if ((i == 0) && (string.IsNullOrEmpty(cell.ToString()) == true))//每行第一个cell为空,break
{
break;
}
if (cell == null)
{
dr[i] = null;
}
else
{
dr[i] = cell.ToString();
}
}
dt.Rows.Add(dr);
}
#endregion
}
return dt;
}
}
}
浙公网安备 33010602011771号