C#实现的Excel文件操作类实例

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; } }}