c# 写入数据到excel文件

来源：互联网发布：南京江北新区网络问政编辑：程序博客网时间：2024/05/16 01:56

using System;using System.Collections.Generic;using System.ComponentModel;using System.Data;using System.Data.OleDb;using System.Data.SqlClient;using System.Drawing;using System.Linq;using System.Text;using System.Windows.Forms;using System.Web;using System.Configuration;namespace WindowsFormsApplication1{    public partial class Form1 : Form    {        public Form1()        {            InitializeComponent();        }        private void Form1_Load(object sender, EventArgs e)        {            //SqlConnection cn = new SqlConnection();            //cn.ConnectionString = @"Server=.;DataBase=MyFirstDBApp;UID=123456;PWD=123";            //cn.Open ();            //Console.WriteLine(cn.State);            //Console.ReadLine();            string connectionString = "Provider=Microsoft.Jet.OleDb.4.0; data source=c:\\office.xls; Extended Properties=Excel 8.0;";            string selectString = "INSERT INTO  [Sheet1$](id) VALUES('123')"; //Office is the named range.            OleDbConnection con = new OleDbConnection(connectionString);            OleDbCommand cmd = new OleDbCommand(selectString, con);            try            {                con.Open();                MessageBox.Show(con.State.ToString());                cmd.ExecuteNonQuery();            }            catch (Exception ex)            {                MessageBox.Show(ex.Message);            }            finally            {                con.Dispose();            }        }    }}

</pre><pre name="code" class="csharp">

</pre><pre name="code" class="csharp">//遍历表名

</pre><pre name="code" class="csharp">         string cs = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\\office.xls;Excel 8.0;HDR=Yes;IMEX=1";            OleDbConnection cn = new OleDbConnection(cs);            cn.Open();            DataTable ttable = cn.GetSchema("Tables");            foreach (DataRow row in ttable.Rows)                if (row["Table_Type"].ToString() == "TABLE")                    MessageBox.Show(row["Table_Name"].ToString());

//读取行数据

<pre name="code" class="csharp">                using (OleDbConnection ole_conn = new OleDbConnection(connectionString))                    {                        ole_conn.Open();                        using (OleDbCommand ole_cmd = ole_conn.CreateCommand())                        {                            //类似SQL的查询语句这个[Sheet1$对应Excel文件中的一个工作表]                            ole_cmd.CommandText = "select * from [Sheet1$]";.//查询ole_cmd.CommandText = "select * from [Sheet1$] where id = '123'";

                            OleDbDataAdapter adapter = new OleDbDataAdapter(ole_cmd);                            DataSet ds = new DataSet();                            adapter.Fill(ds, "Sheet1");                            for (int i = 1; i < ds.Tables[0].Rows.Count; i++)                            {                                MessageBox.Show(ds.Tables[0].Rows[i]["id"].ToString()+ds .Tables [0].Rows [i]["name"].ToString ());                            }                        }                    }            }             catch (SqlException EX)            {             }

0 0