C# 对Excel 表格进行查询,写入
C# 对xls 文件进行查询和写入
##查询xls
void selectXls()
{
//String Con = @"Provider=Microsoft.Jet.OleDb.4.0;Data Source=D:\list.mdb";
if (!File.Exists(AgreementFile))
{
MessageBox.Show(@"D:\workfile\list.xls 不存在");
}
else
{
String Con = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + AgreementFile + "; Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'"; ;
OleDbConnection dbcon = new OleDbConnection(Con);
dbcon.Open();
try
{
string sql = "SELECT F1,F14,F17,F18,F19,F20 FROM [合同申报单$] WHERE F1=\"" + Name + "\"";
//string sql = "SELECT * FROM [喷码$] ";
OleDbDataAdapter OleDaExcel = new OleDbDataAdapter(sql, dbcon);
DataSet OleDsExcel = new DataSet();
OleDaExcel.Fill(OleDsExcel, "合同申报单");
this.Invoke((Action)delegate ()
{
dataGridView2.DataSource = OleDsExcel.Tables[0];
showbox("查询完成");
time_select = DateTime.Now.ToString("HH:mm:ss");
showbox(time_select);
SelcetResult = true;
});
dbcon.Close();
NUM = dataGridView2.Rows[0].Cells[1].Value.ToString();
io = Convert.ToInt32(NUM);
this.Invoke((Action)delegate
{
label8.Text = io.ToString();
});
}
catch (Exception e)
{
showbox(e.ToString());
}
}
}
写入xls
void InsertXls()
{
if (!File.Exists(RegisterFIle))
{
File.Copy(RegisterFIle_old, RegisterFIle);
}
string timenow = DateTime.Now.ToString("HH:mm:ss");
try
{
NMAE_CLient = dataGridView2.Rows[0].Cells[0].Value.ToString();
NUM = dataGridView2.Rows[0].Cells[1].Value.ToString();
Label1 = dataGridView2.Rows[0].Cells[2].Value.ToString();
Label2 = dataGridView2.Rows[0].Cells[3].Value.ToString();
Label3 = dataGridView2.Rows[0].Cells[4].Value.ToString();
Label4 = dataGridView2.Rows[0].Cells[5].Value.ToString();
}
catch (Exception x)
{
MessageBox.Show("请先查询");
return;
}
String Con = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + RegisterFIle + "; Extended Properties='Excel 8.0;HDR=Yes;IMEX=3'"; ;
OleDbConnection dbcon = new OleDbConnection(Con);
dbcon.Open();
OleDbCommand Accesscomm = new OleDbCommand();
string sql = string.Format("INSERT INTO [喷码$](F2,F8,F4,F5,F6,F7,F14,F15,F16)VALUES('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}')", NMAE_CLient, NUM, Label1, Label2, Label3, Label4, time_StartMark, time_select, time_StopMark);
Accesscomm.CommandText = sql;
Accesscomm.Connection = dbcon;
int i=Accesscomm.ExecuteNonQuery();
showbox("写入完成");
dbcon.Close();
}