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: 在此處添加構(gòu)造函數(shù)邏輯  // } public ExcelOpration(string _FileName) {  //  // TODO: 在此處添加構(gòu)造函數(shù)邏輯  //  FileName = _FileName; } /// <summary> /// 打開連接 /// </summary> private void Open() {  if(Conn==null)  {  //在此處設(shè)置訪問的數(shù)據(jù)庫文件  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> /// 關(guān)閉連接 /// </summary> public void Close() {  if (Conn != null)  Conn.Close(); } /// <summary> /// 導(dǎo)入數(shù)據(jù)到Excel /// </summary> /// <param name="OutTable"></param> public void ImportData(DataTable OutTable) {  CreateExcel(OutTable);  InsertData(OutTable); } /// <summary> /// 創(chuàng)建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> /// 插入數(shù)據(jù) /// </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輸出數(shù)據(jù)到數(shù)據(jù)集 /// </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; } }}