C# SQL EXCEL ACCESS全面使用详解

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.OleDb;

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

        private void button1_Click(object sender, EventArgs e)
        {

//下面是EXCEL的数据读取
            string strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=F://eattonton//self//programming//ShipDrafting//ShipDrafting//data.xls;Extended Properties=Excel 8.0";
            OleDbConnection myConn = new OleDbConnection(strCon);
            string strCom = "SELECT * FROM[Area$]";
            myConn.Open();
            OleDbDataAdapter myCommand = new OleDbDataAdapter(strCom,myConn);
            DataSet myDataSet = new DataSet();
            myCommand.Fill(myDataSet, "[Area$]");
            myConn.Close();

            dataGrid1.DataMember = "[Area$]";
            dataGrid1.DataSource = myDataSet;
        }

        private void button2_Click(object sender, EventArgs e)
        {
             Excel.Application xls_exp = null;
             xls_exp = new Excel.Application();
             xls_exp.Application.Workbooks.Add(true);
             xls_exp.Cells[1, 1] = "aa";
             xls_exp.Visible = true;
        }

        private void button3_Click(object sender, EventArgs e)
        {
            string filename;
            string s_1;
            double D_1;
            Excel.Application ExcelObj = null;

            ExcelObj = new Excel.Application();
            if (ExcelObj == null)
            {
                MessageBox.Show("ERROR!");
                System.Windows.Forms.Application.Exit();
            }

            filename = "F://eattonton//self//programming//ShipDrafting//ShipDrafting//data.xls";
            Excel.Workbook theWorkbook = ExcelObj.Workbooks.Open(filename, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
 
            //取得工作簿(workbook)中表单的集合(sheets)
            Excel.Sheets sheets = theWorkbook.Worksheets;
            //取得表单集合中唯一的一个表(worksheet)
            Excel.Worksheet worksheet = (Excel.Worksheet)sheets.get_Item(2);
            Excel.Range myrange = worksheet.get_Range("A1","AC112");
            Array values = (Array)myrange.Formula;
            s_1 = values.GetValue(1,10).ToString();
            MessageBox.Show(s_1);

            ExcelObj.Workbooks.Close();
            ExcelObj.Quit();

        }

    }
}

/////////////////////////////////////////////////////////////////////
string strDSN = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=meTest.MDB";
string strSQL = "SELECT * FROM Developer";
//实例化OleDbConnection对象
OleDbConnection myConn = new OleDbConnection(strDSN);
//实例化OleDbCommand对象
OleDbCommand myCmd = new OleDbCommand(strSQL,myConn);
//实例化OleDbDataReader对象
OleDbDataReader datareader = null;
//连接数据库,读取数据
try
{
   myConn.Open();
   datareader = myCmd.ExecuteReader();
   while(datareader.Read())
   {
        //datareader["name"];
   }
}
catch(Exception e)
{
   MessageBox.Show("error");
}
finally
{
}

////////////////////////////////////////////////////////////////
string strDSN = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=meTest.MDB";
string strSQL = "INSERT INTO * VALUES *";
//实例化OleDbConnection对象
OleDbConnection myConn = new OleDbConnection(strDSN);
//实例化OleDbCommand对象
OleDbCommand myCmd = new OleDbCommand(strSQL,myConn);

try
{
        myConn.Open();
        myCmd.ExecuteNonQuery();
}
catch(Exception e)
{

}
finally
{
  myConn.Close();
}

//////////////////////////////////////////////////////
           OleDbDataAdapter adapter = new OleDbDataAdapter();

            adapter.SelectCommand = new OleDbCommand(strSQL, myConn);
            DataSet dset = new DataSet("myData");
            adapter.Fill(dset);

            DataTable dTable = dset.Tables[0];
            DataRow dtRow;

            for (i = 0; i < dTable.Rows.Count;i++ )
            {
                dtRow = dTable.Rows[i];
               // dT_1 = Convert.ToDouble(dtRow["fill"].ToString());
                dT_1 = (double)dtRow["fill"];
                dT_2 = Convert.ToDouble(s_1);  //修改后的值
                if (dT_1 >= dT_2)
                {
                    //MessageBox.Show(dT_1.ToString());
                    break;
                }

            }

//////////////////////////////////////////////////////////////////////

          string strSQL;
            string s_1,s_2;
            int i = 0;
            double dT_1, dT_2, dT_3, dT_4, dT_5,dT_6;

            s_1 = (string)category.Items[category.SelectedIndex];
            strSQL = string.Format("SELECT * FROM 舱容表 WHERE category='{0}'", s_1);
            //实例化OleDbCommand对象
            OleDbCommand myCmd = new OleDbCommand(strSQL, myConn);
            //实例化OleDbDataReader对象
            OleDbDataReader datareader = null;

            if (cargoList.ColumnCount > 0)
            {
                try
                {
                    datareader = myCmd.ExecuteReader();
                    cargoList.DataSource = null;
                    cargoList.RowCount = 50;
                    while (datareader.Read())
                    {
                        s_1 = (string)datareader["des"];
                        s_2 = (string)datareader["name"];
                        dT_1 = (double)datareader["fill"];
                        dT_2 = (double)datareader["volume"];
                        dT_3 = (double)datareader["LCG"];
                        dT_4 = (double)datareader["TCG"];
                        dT_5 = (double)datareader["VCG"];
                        dT_6 = (double)datareader["IMOM"];
                        cargoList[0, i].Value = s_1;
                        cargoList[1, i].Value = dT_1.ToString();
                        cargoList[2, i].Value = dT_2.ToString();
                        cargoList[3, i].Value = dT_3.ToString();
                        cargoList[4, i].Value = dT_4.ToString();
                        cargoList[5, i].Value = dT_5.ToString();
                        cargoList[6, i].Value = dT_6.ToString();
                        cargoList[7, i].Value = s_2;
                        i++;          
                    }
                    cargoList.RowCount = i+1;
                }
                catch
                {
                }
            }

 

 

////////////////////////////////////////////////////////////////////////////////

string strDSN="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=meTest.mdb";
string strSQL="SELECT * FROM Developer";

OleDbConnection myConn = new OleDbConnection(strDSN);
OleDbDataAdapter myCmd = new OleDbDataAdapter(strSQL,myConn);
DataSet dtSet = new DataSet();

myConn.Open();

myCmd.Fill(dtSet,"Developer");

DataTable dTable = dtSet.Table[0];
foreach(DataRow dtRow in dTable.Rows)
{

 

}

myConn.Close();