C操作EXCEL全解代码--资料

C勰作EXCELS解(代码)

提示：运行之前必须先引用Interop.Excel.dll模块

using System;

using System.Collections.Generic;

using System.Text;

using System.Data;

using System.Data.OleDb;

using System.Windows.Forms;

using System.Reflection;

using Excel;

namespace AnalysisSystem.DB

{

public class ExcelOperation

{

private string _fileName;// 保存路径名

public ExcelOperation(string fileName)

{

_fileName = fileName;

}

private OleDbConnection GetConnection()

{

string connectString = string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=Excel 8.0”,_fileName);

OleDbConnection myConn = new OleDbConnection(connectString);// 建立链接

return myConn;

public System.Data.DataTable ExecuteTableResult(string strSql)

(

System.Data.DataTable dt = new System.Data.DataTable();

try

(

OleDbConnection conn = this.GetConnection();

OleDbDataAdapter da = new OleDbDataAdapter(strSql, conn);// 执行相关SQL 语句

da.Fill(dt);

}

catch (System.Exception ex)

(

//do nothing

}

return dt;

}

public DataSet ExecuteSetResult(string strSql,string table_name)

(

DataSet ds = new DataSet();

string temp_name = "[" + table_name + "$]”;

try

(

OleDbConnection conn = this.GetConnection();

OleDbDataAdapter da = new OleDbDataAdapter(strSql, conn);

da.Fill(ds,temp_name);

}

catch (System.Exception ex)

(

//do nothing

} return ds;

}

public string ExecuteOneResult(string strSql)

(

string result = "";

System.Data.DataTable dt = new System.Data.DataTable();

try

(

OleDbConnection conn = this.GetConnection();

OleDbDataAdapter da = new OleDbDataAdapter(strSql, conn); da.Fill(dt);

}

catch (System.Exception ex)

(

//do nothing

}

if (dt != null && dt.Rows.Count > 0)

(

result = dt.Rows[0][0].ToString();

}

return result;

}

public void ExecuteNonResult(string strSql)

(

try

(

OleDbConnection conn = this.GetConnection();

OleDbCommand cmd = new OleDbCommand(strSql, conn);

cmd.ExecuteNonQuery();

}

catch (System.Exception ex)

(

//do nothing

}

}

private _Workbook W_B(Excel.Application app)

(

Workbooks workbooks = app.Workbooks;

_Workbook workbook = workbooks.Add(XlWBATemplate.xlWBATWorksheet);

return workbook;

}

private _Worksheet W_S(_Workbook work_book)

(

Sheets sheets = work_book.Worksheets;

—Worksheet worksheet = (_Worksheet)sheets.get_Item(1);// 获取选择第一个表, 本身默认名称为Sheetl

return worksheet;

}

//从DataGridView中导出数据到Excel表，单表导出

public void Excel_out(DataGridView dataGridViewl)

(