using System; |
using System.Collections.Generic; |
using System.Text; |
using System.Data.OleDb; |
using System.Data; |
namespace Sale_Comp_KR_Report_Mapping |
{ |
class ExcelBase |
{ |
/// <summary> |
/// 连接Excel数据库 |
/// </summary> |
/// <param name="Path">文件路径</param> |
/// <returns>返回连接对象</returns> |
public static OleDbConnection ConnExcel( string Path) |
{ |
try |
{ |
//HDR=Yes,这代表第一行是标题,不做为数据使用 |
//HDR=NO,则表示第一行不是标题,做为数据来使用。系统默认的是YES。 |
|
//当 IMEX=0 时为“汇出模式”,这个模式开启的 Excel 档案只能用来做“写入”用途。 |
//当 IMEX=1 时为“汇入模式”,这个模式开启的 Excel 档案只能用来做“读取”用途。 |
//当 IMEX=2 时为“连結模式”,这个模式开启的 Excel 档案可同时支援“读取”与“写入” |
|
//string strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Path + ";Extended Properties =\"Excel 12.0;\""; //IMEX=1; |
// string strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Path + ";Extended Properties =\"Excel 12.0;HDR=YES;IMEX=2;\""; //IMEX=1; |
string strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Path + ";Extended Properties =\"Excel 12.0;\"" ; //IMEX=1; |
OleDbConnection conn = new OleDbConnection(strConn); |
conn.Open(); |
return conn; |
} |
catch (Exception ex) |
{ |
return null ; |
} |
} |
/// <summary> |
/// 在Excel中查找数据 |
/// </summary> |
/// <param name="strConn">连接字符串</param> |
/// <param name="Sql">查询语句</param> |
/// <returns>返回查询到的数据集</returns> |
public static DataSet SelectExcel(OleDbConnection conn, string Sql) |
{ |
try |
{ |
string strExcel = "" ; |
OleDbDataAdapter myCommand = null ; |
DataSet ds = null ; |
strExcel = Sql; |
myCommand = new OleDbDataAdapter(strExcel, conn); |
ds = new DataSet(); |
myCommand.Fill(ds); |
return ds; |
} |
catch (Exception ex) |
{ |
return null ; |
} |
} |
/// <summary> |
/// 更新Excel文档 |
/// </summary> |
/// <param name="Path">文件名称</param> |
/// <returns> |
/// -1:发生错误 |
/// other:更新的条数 |
/// </returns> |
public static int UpdateExcel(OleDbConnection conn, string Sql) |
{ |
try |
{ |
string strExcel = "" ; |
OleDbCommand myCommand = null ; |
strExcel = Sql; |
myCommand = new OleDbCommand(strExcel, conn); |
int resultnum = myCommand.ExecuteNonQuery(); |
return resultnum; |
} |
catch (Exception ex) |
{ |
return -1; |
} |
} |
/// <summary> |
/// 关闭Excel数据库 |
/// </summary> |
/// <param name="conn">数据库连接对象</param> |
public static void CloseExcel(OleDbConnection conn) |
{ |
conn.Close(); |
} |
/// <summary> |
/// 读取Excel文档 |
/// </summary> |
/// <param name="Path">文件名称</param> |
/// <returns>返回一个数据集</returns> |
public static DataSet ExcelToDS( string Path, string Sql) |
{ |
try |
{ |
string strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Path + ";Extended Properties =\"Excel 12.0;HDR=YES;IMEX=1;\"" ; //IMEX=1; |
OleDbConnection conn = new OleDbConnection(strConn); |
conn.Open(); |
string strExcel = "" ; |
OleDbDataAdapter myCommand = null ; |
DataSet ds = null ; |
strExcel = Sql; |
myCommand = new OleDbDataAdapter(strExcel, strConn); |
ds = new DataSet(); |
myCommand.Fill(ds); |
conn.Close(); |
return ds; |
} |
catch (Exception ex) |
{ |
return null ; |
} |
} |
/// <summary> |
/// 更新Excel文档 |
/// </summary> |
/// <param name="Path">文件名称</param> |
/// <returns> |
/// -1:发生错误 |
/// other:更新的条数 |
/// </returns> |
public static int UpdateToExcel( string Path, string Sql) |
{ |
try |
{ |
string strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Path + ";Extended Properties =\"Excel 12.0;HDR=FALSE;IMEX=1;\"" ; //IMEX=1; |
int resultnum = 0; |
OleDbConnection conn = new OleDbConnection(strConn); |
conn.Open(); |
string strExcel = "" ; |
OleDbCommand myCommand = null ; |
strExcel = Sql; |
myCommand = new OleDbCommand(strExcel, conn); |
resultnum = myCommand.ExecuteNonQuery(); |
conn.Close(); |
return resultnum; |
} |
catch (Exception ex) |
{ |
return -1; |
} |
} |
} |
} |
by: 发表于:2018-01-29 11:01:02 顶(0) | 踩(0) 回复
??
回复评论