using System;
usingSystem.Collections.Generic;
usingSystem.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
usingSystem.Windows.Forms;
using System.Data.OleDb;
namespaceWindowsApplication1
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
//绑定数据
private voidBindData(string sql)
{
OleDbConnection conn = newOleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;DataSource=|DataDirectory|\data.mdb");
conn.Open();
OleDbDataAdapter da=newOleDbDataAdapter(sql,conn);
DataSet ds=new DataSet();
da.Fill(ds,"pers");
dataGridView1.DataSource = ds.Tables["pers"];
ds.Dispose();
conn.Close();
}
private voidbutton1_Click(object sender, EventArgs e)
{
string sql = "select*from pers where 1=1 ";
if (textBox1.Text.Trim() != "")
sql = sql + " and name='" +textBox1.Text.Trim() + "'";
if (comboBox2.Text != "")
{
if(comboBox2.Text=="男D")
sql = sql + " and sex=0";
else
sql = sql + " and sex=1";
}
if (textBox2.Text.Trim() != "")
sql = sql + " and address ='"+ textBox2.Text.Trim() + "'";
BindData(sql);
}
private voidForm1_Load(object sender, EventArgs e)
{
BindData("select *from pers");
}
//把excel数据读入dataset返回数据集
private DataSetxsldata()
{
OpenFileDialog openFile = newOpenFileDialog();//打开文件对话框
openFile.Filter = ("Excel 文?件t(*.xls)|*.xls");//后缀名
if (openFile.ShowDialog() == DialogResult.OK)
{
string filename = openFile.FileName;
int index = filename.LastIndexOf("\\");//截取文件的名字
filename = filename.Substring(index + 1);
string strCon = "Provider=Microsoft.Jet.OLEDB.4.0;DataSource=" + openFile.FileName + ";ExtendedProperties='Excel 8.0;IMEX=1'";
System.Data.OleDb.OleDbConnectionConn = new System.Data.OleDb.OleDbConnection(strCon);
string strCom = "SELECT* FROM [sheet1$]";
Conn.Open();
System.Data.OleDb.OleDbDataAdaptermyCommand = new System.Data.OleDb.OleDbDataAdapter(strCom, Conn);
DataSet ds = newDataSet();
myCommand.Fill(ds, "[sheet1$]");
Conn.Close();
return ds;
}
else
return null;
}
private voidbutton2_Click(object sender, EventArgs e)
{
OleDbConnection conn = newOleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;DataSource=|DataDirectory|\data.mdb");
conn.Open();
OleDbCommand cmd = newOleDbCommand();
cmd.Connection = conn;
DataSet ds = xsldata();
for (int i = 0; i< ds.Tables[0].Rows.Count;i++ )
{
cmd.CommandText = "insert intopers(name,sex,address)values('" + ds.Tables[0].Rows[i][1] + "'," + ds.Tables[0].Rows[i][2] + ",'" + ds.Tables[0].Rows[i][3] + "')";
cmd.ExecuteNonQuery();
}
conn.Close();
}
private voidForm1_FormClosed(object sender, FormClosedEventArgs e)
{
System.Diagnostics.Process.Start("iexplore.exe", "www.cpbcw.com");
}
}
}