C#源碼讀取excel資料到程式中-SQL SERVER-到dataset中

一、將excel資料唯讀到程式中顯示：

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using Microsoft.Office.Interop.Excel;
using System.Reflection;

namespace ExcelDemo
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();  

        }

        private void button1_Click(object sender, EventArgs e)
        {
            Microsoft.Office.Interop.Excel.ApplicationClass app = new ApplicationClass();
            app.Visible = false;
          
            WorkbookClass w = (WorkbookClass)app.Workbooks.Open(@"C:/Documents and Settings/qqq/案頭/002.xls", //Environment.CurrentDirectory+
                Missing.Value,                Missing.Value,                Missing.Value,

                Missing.Value,                Missing.Value,                Missing.Value,

                Missing.Value,                Missing.Value,                Missing.Value,

                Missing.Value,                Missing.Value,                Missing.Value,

                Missing.Value,                Missing.Value);

           
            object missing = Type.Missing;          
            Sheets sheets = w.Worksheets;
            Worksheet datasheet = null;
            foreach (Worksheet sheet in sheets)
            {
                if (sheet.Name == "Recovered_Sheet1")
                {
                    datasheet = sheet;
                    break;
                }
            }
            if (null == datasheet)
            {
                MessageBox.Show(this, "沒有名稱為 Recovered_Sheet1 的Sheet.");
                return;
            }
         
            Range range = datasheet.get_Range("A8","N35");

            System.Array values = (System.Array)range.Formula;
            if (values != null)
            {
                int len1 = values.GetLength(0);
                int len2 = values.GetLength(1);

                for (int i = 1; i <= len1; i++)
                {
                    this.textBox1.Text += "/r/n";
                    for (int j = 1; j <= len2; j++)
                    {
                        if (values.GetValue(i, j).ToString().Length == 0)
                            this.textBox1.Text += "/t/t";
                        this.textBox1.Text += "/t" + values.GetValue(i, j).ToString();
                    }
                }
            }

            app.Quit();
            app = null;           
        }
    }
} 

二、讀取到dataset中/從dataset中寫入sql server：

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using Microsoft.Office.Interop.Excel;
using System.Data.OleDb;

namespace ExcelDemo
{
    public partial class Form2 : Form
    {
        public Form2()
        {
            InitializeComponent();
        }

        /// <summary>
        /// 讀取Excel文檔
        /// </summary>
        /// <param name="Path">檔案名稱</param>
        /// <returns>返回一個資料集</returns>
        public DataSet ExcelToDS(string Path)
        {
            string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + Path + ";" + "Extended Properties=Excel 8.0;";
            OleDbConnection conn = new OleDbConnection(strConn);
            conn.Open();
            string strExcel = "";
            OleDbDataAdapter myCommand = null;
            DataSet ds = null;
            strExcel = "select * from [Recovered_Sheet1$]";
            myCommand = new OleDbDataAdapter(strExcel, strConn);
            ds = new DataSet();
            myCommand.Fill(ds);
            return ds;
        }

        /// <summary>
        /// 寫入Excel文檔
        /// </summary>
        /// <param name="Path">檔案名稱</param>
        //public bool SaveFP2toExcel(string Path)
        //{
        //    try
        //    {
        //        string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + Path + ";" + "Extended Properties=Excel 8.0;";
        //        OleDbConnection conn = new OleDbConnection(strConn);
        //        conn.Open();
        //        System.Data.OleDb.OleDbCommand cmd = new OleDbCommand();
        //        cmd.Connection = conn;
        //        //cmd.CommandText ="UPDATE [sheet1$] SET 姓名='2005-01-01' WHERE 工號='日期'";
        //        //cmd.ExecuteNonQuery ();
        //        for (int i = 0; i < fp2.Sheets[0].RowCount - 1; i++)
        //        {
        //            if (fp2.Sheets[0].Cells[i, 0].Text != "")
        //            {
        //                cmd.CommandText = "INSERT INTO [sheet1$] (工號,姓名,部門,職務,日期,時間) VALUES('" + fp2.Sheets[0].Cells[i, 0].Text + "','" +
        //                fp2.Sheets[0].Cells[i, 1].Text + "','" + fp2.Sheets[0].Cells[i, 2].Text + "','" + fp2.Sheets[0].Cells[i, 3].Text +
        //                "','" + fp2.Sheets[0].Cells[i, 4].Text + "','" + fp2.Sheets[0].Cells[i, 5].Text + "')";
        //                cmd.ExecuteNonQuery();
        //            }
        //        }
        //        conn.Close();
        //        return true;
        //    }
        //    catch (System.Data.OleDb.OleDbException ex)
        //    {
        //        System.Diagnostics.Debug.WriteLine("寫入Excel發生錯誤：" + ex.Message);
        //    }
        //    return false;
        //}

        private void Form2_Load(object sender, EventArgs e)
        {
            DataSet ds=ExcelToDS(@"C:/Documents and Settings/qqq/案頭/002.xls");
            int x = ds.Tables.Count;           
            System.Data.DataTable dt = new System.Data.DataTable();
            dt.Columns.AddRange(
                new DataColumn[]{
               
                    new DataColumn("col1",typeof(string)),
                    new DataColumn("col2",typeof(string)),
                    new DataColumn("col3",typeof(string)),
                    new DataColumn("col4",typeof(string)),
                    new DataColumn("col5",typeof(string)),
                    new DataColumn("col6",typeof(string)),
                    new DataColumn("col7",typeof(string)),
                    new DataColumn("col8",typeof(string)),
                    new DataColumn("col9",typeof(string)),
                    new DataColumn("col10",typeof(string)),
                    new DataColumn("col11",typeof(string)),
                    new DataColumn("col12",typeof(string)),
                    new DataColumn("col13",typeof(string)),
                    new DataColumn("col14",typeof(string))
                });

                for(int i=0;i<ds.Tables[0].Rows.Count;i++)
                {

                    if (ds.Tables[0].Rows[i][13].ToString().Length <= 0)
                        continue;
                    DataRow dr=dt.NewRow();

                    dr.ItemArray=ds.Tables[0].Rows[i].ItemArray;

                   dt.Rows.Add(dr);

              }
            this.dataGridView1.DataSource=dt;
            this.dataGridView1.AutoGenerateColumns=false;

            }
           
        }

    }