C# Excel文件操作类 C#实现的Excel文件操作类实例
songkexin 人气:0想了解C#实现的Excel文件操作类实例的相关内容吗,songkexin在本文为您仔细讲解C# Excel文件操作类的相关知识和一些Code实例,欢迎阅读和指正,我们先划重点:C#,Excel文件,操作类,下面大家一起来学习吧。
本文实例讲述了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; } } }
希望本文所述对大家C#程序设计有所帮助。
加载全部内容