一段欣赏的代码共享
一段代码共享 包括
1类的定义，典型的面向对象思想的实例
2从xml文件中读取数据
3从excel中读取数据
using System;
using System.Collections;
using System.Data;
using System.Xml;
using System.Data.OleDb;
namespace ImportHelper
{
internal class ImportTableInfo
{
private string _name = string.Empty;
private string _desc = string.Empty;
private ImportColumnInfoCollection _columnInfos = new ImportColumnInfoCollection();
public ImportTableInfo()
{
}
public string Name
{
get{ return _name;}
set{ _name = value;}
}
public string Description
{
get{ return _desc;}
set{_desc = value;}
}
public ImportColumnInfoCollection ColumnInfos
{
get{ return _columnInfos;}
set{_columnInfos = value;}
}
}
internal class ImportColumnInfo
{
private string _name = string.Empty;
private string _desc = string.Empty;
private bool _optional = true;
public ImportColumnInfo(string Name, string Description, string Optional)
{
_name = Name;
_desc = Description;
try
{
_optional = bool.Parse(Optional);
}
catch
{
_optional = false;
}
}
public string Name
{
get{ return _name;}
set{ _name = value;}
}
public string Description
{
get{ return _desc;}
set{_desc = value;}
}
public bool Optional
{
get{ return _optional;}
set{_optional = value;}
}
}
internal class ImportColumnInfoCollection:System.Collections.CollectionBase
{
public ImportColumnInfoCollection()
{
}
public void Add(ImportColumnInfo columnInfo)
{
this.List.Add(columnInfo);
}
public ImportColumnInfo this[int i]
{
get
{
return (ImportColumnInfo)List[i];
}
set
{
List[i] = value;
}
}
public void Remove(ImportColumnInfo columnInfo)
{
int i= this.IndexOf(columnInfo);
if(i>0 && i<this.Count)
{
this.RemoveAt(i);
}
}
public int IndexOf(ImportColumnInfo columnInfo)
{
for(int i=0;i<this.Count;i++)
{
ImportColumnInfo columnInf = this[i];
if(columnInf == columnInfo || (columnInfo != null && columnInf != null && columnInf.Name == columnInfo.Name))
{
return i;
}
}
return -1;
}
}
/**//// <summary>
/// 模板的辅助类
/// </summary>
internal class TemplateHelper
{
public TemplateHelper()
{
}
//获得填充数据的ImportTableInfo
public static ImportTableInfo GetImportTableInfo(string tblName)
{
if(tblName==null||tblName.Trim()=="")
{
throw new ApplicationException("please transfer param!");
}
//获取列集合 读取xml文件
string strDicPath=System.Configuration.ConfigurationSettings.AppSettings["ImportDataTemplate"].Trim(); //从fig文件中获取xml文件所在的目录
string strXmlPath=System.IO.Path.Combine(strDicPath,tblName+".xml"); //模板均以导入目标表 表名来命名
ImportColumnInfoCollection columnColl=new ImportColumnInfoCollection();
XmlDocument xmlDoc=new XmlDocument();
xmlDoc.Load(strXmlPath);
XmlNode baseNode=xmlDoc.DocumentElement.SelectSingleNode("/Table[@Name='"+tblName.Trim()+"']");
XmlNodeList nodeList=baseNode.SelectNodes("./Column");
foreach(XmlNode node in nodeList)
{
if(node!=null)
{
columnColl.Add(new ImportColumnInfo(node.Attributes["Name"].InnerText.Trim(),node.Attributes["Description"].InnerText.Trim(),node.Attributes["Optional"].InnerText.Trim()));
}
}
ImportTableInfo tblInfo=new ImportTableInfo();
tblInfo.Name=tblName.Trim();
tblInfo.ColumnInfos=columnColl;
//tblInfo.Description=desc;
return tblInfo;
}
}
/**//// <summary>
/// 从Excel中获取数据,并以DataSet的方式返回给调用者
/// Date:2005.01.22
/// </summary>
public class ExcelGateway
{
private System.Data.OleDb.OleDbConnection excelConnection; //excelConnection连接
private string excelConnectionString;//连接字符串
private System.Data.OleDb.OleDbDataAdapter excelAdapter; //执行SQL
private System.Data.OleDb.OleDbCommand excelCommand; //执行SQL
private DataSet dataSet;//数据集
/**//// <summary>
/// 构造函数,实例化的时候创建一个excelConnection对象
/// </summary>
/// <param name="strFilePath">Excel的存放路径</param>
/// <param name="isQuery">是否对Excel做只读操作</param>
public ExcelGateway(string strFilePath,bool isQuery)
{
//连接字符串
//查询时
if(isQuery)
{
excelConnectionString="Provider=Microsoft.Jet.OLEDB.4.0; Data Source= "+strFilePath+"; Extended Properties='Excel 8.0;HDR=YES;IMEX=1'";
}
//插入更新时
else
{
excelConnectionString="Provider=Microsoft.Jet.OLEDB.4.0; Data Source= "+strFilePath+"; Extended Properties=Excel 8.0";
}
//打开连接
excelConnection=new OleDbConnection(excelConnectionString);
}
/**//// <summary>
/// 打开Excel数据库连接
/// </summary>
public void Open()
{
//如果当前对象存在
if(excelConnection!=null)
{
excelConnection.Open();
}
}
/**//// <summary>
/// 关闭Excel数据库连接
/// </summary>
public void Close()
{
//如果当前对象存在
if(excelConnection!=null)
{
excelConnection.Close();
}
}
/**//// <summary>
/// 以DataSet的方式返回Excel中的数据
/// </summary>
/// <param name="sql">查询的SQL</param>
/// <returns>返回结果集</returns>
public DataSet ExcuteSqlForDst(string sql)
{
//实例化Adapter类
excelAdapter=new OleDbDataAdapter(sql,excelConnection);
//数据集
dataSet=new DataSet();
//填充dataSet
excelAdapter.Fill(dataSet);
//返回dataSet
return(dataSet);
}
/**//// <summary>
/// 通过传递参数的方式,执行Sql语句
/// </summary>
/// <param name="sql">SQL语句</param>
/// <param name="SqlParameter">参数</param>
/// <param name="SqlParameterValue">参数值</param>
/// <returns></returns>
public DataSet ExcuteParameterSqlForDst(string sql,string[] SqlParameter,object[] SqlParameterValue)
{
DataSet ds=new DataSet();
excelAdapter=new OleDbDataAdapter();
if(SqlParameter.Length==SqlParameterValue.Length)
{
excelCommand=new OleDbCommand();
//对于每一个参数,直接匹配
for(int i=0;i<SqlParameter.Length;i++)
{
OleDbParameter para=new OleDbParameter();
excelCommand.Connection=excelConnection;//数据库连接
para.ParameterName=SqlParameter[i]; //参数名
para.Value=SqlParameterValue[i]; //参数值
excelCommand.Parameters.Add(para); //增加参数
}
try
{
excelAdapter.SelectCommand=excelCommand;
excelAdapter.Fill(ds);
return(ds);
}
catch
{
throw new Exception("获取数据出错!");
}
}
else
{
throw new Exception("给定的参数和参数值不匹配！");
}
}
/**//// <summary>
/// 执行不需要返回值的SQL语句,比如插入,删除操作
/// 如不能正确执行,回滚操作,并抛出失败异常
/// 如果回滚失败,抛出回滚失败异常
/// </summary>
/// <param name="sql">待执行的SQL</param>
public void ExecuteNoquery(string sql)
{
try
{
//实例化Adapter类
excelCommand=new OleDbCommand(sql,excelConnection);
excelCommand.Transaction=excelConnection.BeginTransaction();
excelCommand.ExecuteNonQuery();
try
{
excelCommand.Transaction.Commit();
}
catch
{
try
{
excelCommand.Transaction.Rollback();
}
catch
{
throw new Exception("数据库事务回滚失败！");
}
}
}
catch(Exception ex)
{
throw new Exception(ex.Message);
}
}
}
}
附：
1读取的xml文件
<? xml version = " 1.0 " encoding = " utf-8 " ?>
<!-- 配置注意事项
1 , < Table Name = " TblName " > 中Name的值为导入数据的目标表的表名
2 , <Column Name="Account" Description="帐号" Optional="False" /> 为导入数据的列，Name 是字段名，Description是描述,Optional为True该列数据是可为空
-->
< Table Name = " TblName " >
< Column Name = " Account " Description = "帐号 " Optional = " False " />
< Column Name = " UserName " Description = "用户名 " Optional = " False " />
< Column Name = " Password " Description = " 密码 " Optional = " False " />
<Column Name="Department" Description="部门" Optional="False" />
</ Table >
2使用ExcelGateway类的代码
private void lbtnImport_Click( object sender, System.EventArgs e)
{
string suffix = System.IO.Path.GetExtension(this.UpLoadFile.PostedFile.FileName); //UpLoadFile是file类型html控件，取得读取文件的扩展名，以判断是否是excel文件
if(suffix.Trim().ToUpper() != ".XLS" && suffix.Trim().ToUpper() != ".XLT")
{
Function.PageAlert("文件类型不正确"); //公用方法弹出提示信息
return;
}
string onlyFileName = System.IO.Path.GetFileName(this.UpLoadFile.PostedFile.FileName);
string uploadFilePath = System.Configuration.ConfigurationSettings.AppSettings["UploadFilePath"];//文件上传到服务器的目录 if(!System.IO.Directory.Exists(uploadFilePath))
{
System.IO.Directory.CreateDirectory(uploadFilePath);
}
string savedFilePath = System.IO.Path.Combine(uploadFilePath,onlyFileName);
this.UpLoadFile.PostedFile.SaveAs(savedFilePath); //把excel文件上传到服务器
string[] sheetNames = SheetName.Text.Trim().Split(new char[',']);//excel的sheet
try
{
string sql="";
ExcelGateway excelG = new ExcelGateway(savedFilePath,true);
excelG.Open();
try
{
for(int i=0;i<sheetNames.Length;i++)
{
sql=string.Format("SELECT * FROM [{0}$]",sheetNames[i]);
DataSet ds = excelG.ExcuteSqlForDst(sql);//获取到数据填充到DataSet
}
// InvbitWrapper bitWrapper = new InvbitWrapper();
// bitWrapper.ImportInvBitData(ds);
}
finally
{
excelG.Close();
}
}
catch(Exception ex)
{
Function.PageAlert(ex.ToString());
}
}
