using System;using System.Data;using System.Data.OleDb;using System.Text;using System.IO;
namespace Hxh.API{/// <summary>/// ExcelOpration 的摘要说明。/// </summary>public class ExcelOpration{OleDbConnection Conn;string connString ;string FileName;
public ExcelOpration(){//// TODO: 在此处添加构造函数逻辑//}public ExcelOpration(string _FileName){//// TODO: 在此处添加构造函数逻辑//FileName = _FileName;}
/// <summary>/// 打开连接/// </summary>private void Open(){if(Conn==null){//在此处设置访问的数据库文件connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + FileName +";Extended Properties=Excel 8.0;";Conn=new OleDbConnection(connString);Conn.Open();
}else if(Conn.State==System.Data.ConnectionState.Closed)Conn.Open();
}
/// <summary>/// 关闭连接/// </summary>public void Close() {if (Conn != null)Conn.Close();}
/// <summary>/// 导入数据到Excel/// </summary>/// <param name="OutTable"></param>public void ImportData(DataTable OutTable){CreateExcel(OutTable);InsertData(OutTable);}
/// <summary>/// 创建Excel文件和表头/// </summary>private void CreateExcel(DataTable OutTable){StringBuilder sb = new StringBuilder();if(File.Exists(FileName)){File.Delete(FileName);}sb.Append("create table Sheet1(");foreach(DataColumn col in OutTable.Columns){sb.Append(col.ColumnName+" varchar,");}sb.Remove(sb.Length-1,1);sb.Append(")");Open();OleDbCommand OleCmd = new OleDbCommand();OleCmd.Connection = Conn;OleCmd.CommandText = sb.ToString();OleCmd.ExecuteNonQuery();Close();}
/// <summary>/// 插入数据/// </summary>private void InsertData(DataTable OutTable){OleDbCommand OleCmd = new OleDbCommand();OleDbParameter[] parm=new OleDbParameter[OutTable.Columns.Count];StringBuilder sb = new StringBuilder();sb.Append("insert into Sheet1 values(");for(int i=0;i<OutTable.Columns.Count;i++){parm=new OleDbParameter("@P"+OutTable.Columns.ColumnName,OleDbType.VarChar);sb.Append("@P"+OutTable.Columns.ColumnName+",");OleCmd.Parameters.Add(parm);}sb.Remove(sb.Length-1,1);sb.Append(")");Open();OleCmd.Connection = Conn;OleCmd.CommandText = sb.ToString();foreach(DataRow row in OutTable.Rows){for(int i=0;i<OutTable.Columns.Count;i++){parm.Value = row[OutTable.Columns.ColumnName].ToString().Trim();}
OleCmd.ExecuteNonQuery();}Close();
}
/// <summary>/// 从Excel输出数据到数据集/// </summary>/// <returns></returns>public DataSet OutPortData(){DataSet ds = new DataSet();Open();OleDbDataAdapter myAdapter = new OleDbDataAdapter("select * from [Sheet1$]", Conn);myAdapter.Fill(ds,"Input");Close();return ds;}
}}
