怎么用C#语言实现读取EXCEL的表格结构,在把表格中的数据导入到数据库中

Question

怎么用C#语言实现读取EXCEL表格的结构，注：ECXCEL表格里有多张的SHEET表格，而且有的表格不是标准的一个格，然后将EXCEL表格的数据导入到数据库，求高手帮忙。。。

Answer 1

using System;
using System.Collections.Generic;
using System.Text;
using System.Configuration;
using System.Data;
using System.Data.OleDb;

namespace Excel
{
/// <summary>
/// Excel数据交换类
/// </summary>
public class Excel : IDisposable
{
#region 自定义类型
/// <summary>
/// 是否将第一行作为表头
/// </summary>
public enum HDR
{
/// <summary>
/// 将第一行作为表头
/// </summary>
Yes,
/// <summary>
/// 不用第一行作为表头
/// </summary>
No
};
/// <summary>
/// Excel文件格式
/// </summary>
public enum ExcelFileFormat
{
/// <summary>
/// Excel97/2003格式
/// </summary>
Excel97OR2003,
/// <summary>
/// Excel2007格式
/// </summary>
Excel2007
};
#endregion

#region 变量
private HDR _excelHDR = HDR.No;
private ExcelFileFormat _excelformat = ExcelFileFormat.Excel97OR2003;
private string _connectionString2003 = "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=\"Excel 8.0;HDR={1};IMEX=1\";data source=\"{0}\"";
private string _connectionString2007 = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=\"{0}\";Extended Properties=\"Excel 12.0;HDR={1};IMEX=1\";";
private string _connectionString = "";
private string _filename;
private OleDbConnection _connection;
private OleDbTransaction _tran;
#endregion

#region 属性

#region ConnectionString
/// <summary>
/// 获取系统的连接字符串
/// </summary>
public string ConnectionString
{
get
{
return this._connectionString;
}
}
#endregion

#region 是否将第一行作为表头
/// <summary>
/// 获取或设置是否将第一行作为表头
/// </summary>
public HDR ExcelHDR
{
get
{
return this._excelHDR;
}
set
{
this._excelHDR = value;
}
}
#endregion

#region Excel文件格式
/// <summary>
/// 获取或设置当前Excel文件的格式
/// </summary>
public ExcelFileFormat ExcelFormat
{
get
{
return this._excelformat;
}
set
{
this._excelformat = value;
}
}
#endregion

#endregion

#region 构造函数
/// <summary>
/// 创建一个Excel文件链接对象
/// </summary>
/// <param name="filename">Excel文件完整路径</param>
/// <param name="excelFormat">Excel文件格式</param>
public Excel(string filename, ExcelFileFormat excelFormat)
{
this._filename = filename;
if (excelFormat == ExcelFileFormat.Excel97OR2003)
{
this._connectionString = string.Format(this._connectionString2003, filename, this._excelHDR);
this._connection = new OleDbConnection(this._connectionString);
}
else if (excelFormat == ExcelFileFormat.Excel2007)
{
this._connectionString = string.Format(this._connectionString2007, filename, this._excelHDR);
this._connection = new OleDbConnection(this._connectionString);
}
}
/// <summary>
/// 创建一个Excel文件链接对象
/// </summary>
/// <param name="filename">Excel文件完整路径</param>
/// <param name="excelFormat">Excel文件格式</param>
/// <param name="hdr">是否将第一行作为表头</param>
public Excel(string filename, ExcelFileFormat excelFormat, HDR hdr)
{
this._filename = filename;
this._excelHDR = hdr;
if (excelFormat == ExcelFileFormat.Excel97OR2003)
{
this._connectionString = string.Format(this._connectionString2003, filename, this._excelHDR);
this._connection = new OleDbConnection(this._connectionString);
}
else if (excelFormat == ExcelFileFormat.Excel2007)
{
this._connectionString = string.Format(this._connectionString2007, filename, this._excelHDR);
this._connection = new OleDbConnection(this._connectionString);
}
}

~Excel()
{
this.Dispose();
}
#endregion

#region 方法

#region 事务

#region 开始一个Excel文件事务
/// <summary>
/// 开始一个Excel文件事务
/// </summary>
public void BeginTransaction()
{
if (this._connection.State != ConnectionState.Open && this._connection.State != ConnectionState.Connecting)
{
this._connection.Open();
}
this._tran = this._connection.BeginTransaction();
}
#endregion

#region 提交一个Excel文件事务
/// <summary>
/// 提交一个Excel文件事务
/// </summary>
public void CommitTransaction()
{
if (this._tran != null)
{
this._tran.Commit();
}
this.Dispose();
}
#endregion

#region 回滚一个Excel文件事务
/// <summary>
/// 回滚一个Excel文件事务
/// </summary>
public void RollbackTransaction()
{
if (this._tran != null)
{
this._tran.Rollback();
}
this.Dispose();
}
#endregion

#region 关联一个事务
/// <summary>
/// 关联一个事务
/// </summary>
/// <param name="tran">事务对象</param>
/// <param name="comm">命令对象</param>
private void AddTransactionToCommand(OleDbTransaction tran, OleDbCommand comm)
{
if (tran != null)
{
comm.Transaction = tran;
}
}
#endregion

#endregion

#region 查询分析

#region DataSet

#region DataSet QueryDataSet(string sql)
/// <summary>
/// 通过一个Excel-SQL语句查询
/// </summary>
/// <param name="sql">sql</param>
/// <returns>DataSet结果集</returns>
public DataSet QueryDataSet(string sql)
{
OleDbCommand sc = new OleDbCommand(sql, this._connection);
if (this._connection.State != ConnectionState.Open && this._connection.State != ConnectionState.Connecting)
{
this._connection.Open();
}
this.AddTransactionToCommand(this._tran, sc);
OleDbDataAdapter sda = new OleDbDataAdapter(sc);
DataSet ds = new DataSet();
try
{
sda.Fill(ds);
sda.Dispose();
sc.Dispose();
}
catch (Exception e)
{
this.LogException(e);
}
return ds;
}
#endregion

#region DataSet QueryDataSet(string ProcedureName,string[] Parameters,object[] Values)
/// <summary>
/// 通过存储过程与参数进行查询
/// </summary>
/// <param name="ProcedureName">存储过程名</param>
/// <param name="Paramters">参数数组</param>
/// <param name="Values">值数组</param>
/// <returns>DataSet数据集</returns>
public DataSet QueryDataSet(string ProcedureName, string[] Parameters, object[] Values)
{
OleDbCommand sc = new OleDbCommand();
sc.Connection = this._connection;
if (this._connection.State != ConnectionState.Open && this._connection.State != ConnectionState.Connecting)
{
this._connection.Open();
}
this.AddTransactionToCommand(this._tran, sc);
sc.CommandText = ProcedureName;
sc.CommandType = CommandType.StoredProcedure;
for (int i = 0; i < Parameters.Length; i++)
{
sc.Parameters.Add(new OleDbParameter(Parameters[i], Values[i]));
}

OleDbDataAdapter sda = new OleDbDataAdapter(sc);
DataSet ds = new DataSet();
try
{
sda.Fill(ds);
sda.Dispose();
sc.Dispose();
}
catch (Exception e)
{
this.LogException(e);
}
return ds;
}
#endregion

#endregion

#region DataTable

#region DataTable QueryDataTable(string sql)
/// <summary>
/// 通过一个Excel-SQL语句查询
/// </summary>
/// <param name="sql">sql</param>
/// <returns>DataTable结果集</returns>
public DataTable QueryDataTable(string sql)
{
OleDbCommand sc = new OleDbCommand(sql, this._connection);
if (this._connection.State != ConnectionState.Open && this._connection.State != ConnectionState.Connecting)
{
this._connection.Open();
}
this.AddTransactionToCommand(this._tran, sc);
OleDbDataAdapter sda = new OleDbDataAdapter(sc);
DataTable dt = new DataTable();
try
{
sda.Fill(dt);
sda.Dispose();
sc.Dispose();
}
catch (Exception e)
{
this.LogException(e);
}
return dt;
}
#endregion

#region DataTable QueryDataTable(string ProcedureName,string[] Parameters,object[] Values)
/// <summary>
/// 通过存储过程与参数进行查询
/// </summary>
/// <param name="ProcedureName">存储过程名</param>
/// <param name="Paramters">参数数组</param>
/// <param name="Values">值数组</param>
/// <returns>DataTable数据集</returns>
public DataTable QueryDataTable(string ProcedureName, string[] Parameters, object[] Values)
{
OleDbCommand sc = new OleDbCommand();
sc.Connection = this._connection;
if (this._connection.State != ConnectionState.Open && this._connection.State != ConnectionState.Connecting)
{
this._connection.Open();
}
this.AddTransactionToCommand(this._tran, sc);
sc.CommandText = ProcedureName;
sc.CommandType = CommandType.StoredProcedure;
for (int i = 0; i < Parameters.Length; i++)
{
sc.Parameters.Add(new OleDbParameter(Parameters[i], Values[i]));
}
OleDbDataAdapter sda = new OleDbDataAdapter(sc);
DataTable dt = new DataTable();
try
{
sda.Fill(dt);
sda.Dispose();
sc.Dispose();
}
catch (Exception e)
{
this.LogException(e);
}
return dt;
}
#endregion

#endregion

#region void

#region void Query(string sql)
/// <summary>
/// 通过一个Excel-SQL语句查询
/// </summary>
/// <param name="sql">sql</param>
public void Query(string sql)
{
OleDbCommand sc = new OleDbCommand(sql, this._connection);
if (this._connection.State != ConnectionState.Open && this._connection.State != ConnectionState.Connecting)
{
this._connection.Open();
}
this.AddTransactionToCommand(this._tran, sc);
try
{
sc.ExecuteNonQuery();
sc.Dispose();
}
catch (Exception e)
{
this.LogException(e);
}
}
#endregion

#region void Query(string ProcedureName,string[] Parameters,object[] Values)
/// <summary>
/// 通过存储过程与参数进行查询
/// </summary>
/// <param name="ProcedureName">存储过程名</param>
/// <param name="Paramters">参数数组</param>
/// <param name="Values">值数组</param>
/// <returns>DataSet数据集</returns>
public void Query(string ProcedureName, string[] Parameters, object[] Values)
{
OleDbCommand sc = new OleDbCommand();
sc.Connection = this._connection;
if (this._connection.State != ConnectionState.Open && this._connection.State != ConnectionState.Connecting)
{
this._connection.Open();
}
this.AddTransactionToCommand(this._tran, sc);
sc.CommandText = ProcedureName;
sc.CommandType = CommandType.StoredProcedure;
for (int i = 0; i < Parameters.Length; i++)
{
sc.Parameters.Add(new OleDbParameter(Parameters[i], Values[i]));
}
try
{
sc.ExecuteNonQuery();
sc.Dispose();
}
catch (Exception e)
{
this.LogException(e);
}
}
#endregion

#endregion

#endregion

#region 附加功能

#region 获取所有表名称
/// <summary>
/// 获取所有表名称
/// </summary>
/// <returns>string[] 表名称</returns>
public string[] GetShemaTableName()
{

//获取数据表
if (this._connection.State != ConnectionState.Open && this._connection.State != ConnectionState.Connecting)
{
this._connection.Open();
}
try
{
DataTable shemaTable = this._connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
int n = shemaTable.Rows.Count;
string[] strTable = new string[n];
int m = shemaTable.Columns.IndexOf("TABLE_NAME");
for (int i = 0; i < n; i++)
{
DataRow m_DataRow = shemaTable.Rows[i];
strTable[i] = m_DataRow.ItemArray.GetValue(m).ToString();
}
return strTable;
}
catch (Exception e)
{
this.LogException(e);
return null;
}
}
#endregion

#endregion

#region Excel文件操作异常日志
/// <summary>
/// Excel文件错误日志记录
/// </summary>
/// <param name="e">异常信息对象</param>
private void LogException(Exception e)
{
throw new Exception(e.Message);
}
#endregion

#endregion

#region IDisposable 成员

public void Dispose()
{
#region 注销Excel文件事务
if (this._tran != null)
{
this._tran.Dispose();
}
#endregion

if (this._connection != null)
{
#region 关闭Excel文件连接
if (this._connection.State != ConnectionState.Closed)
{
this._connection.Close();
this._connection.Dispose();
}
}
#endregion
}

#endregion
}
}

Answer 2

就是数据的导入导出啊，很麻烦的一个事情，你必须会数据库的导入和导出的做法

Answer 3

我的博客里面有：http://blog.163.com/kymdidicom@126/blog/static/114702287201011102648891/
看看能不能帮到你所要求的