[c#]代码库
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) 回复
??
回复评论