1.配置EXCEL链接
public OleDbConnection getExcelConn(string filepath) { string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filepath + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=2'"; OleDbConnection OleConn = new OleDbConnection(strConn); return OleConn; }
2.操作EXCEL
String filepath = path + "xls\\" + Program.username + "_" + damlname + ".xls"; OleDbConnection conn = getExcelConn(filepath); conn.Open(); OleDbCommand cmd = new OleDbCommand(); cmd.Connection = conn; cmd.CommandText = "INSERT INTO [" + damlname + "$] (dh,qzh,ndu,jgname,bgqx,hh,jh,ptotal) values('" + nowdh + "','" + qzh + "','" + ndu + "','" + jgname + "','" + qx + "','" + hh + "','" + jh + "','" + ptotal + "')"; cmd.ExecuteNonQuery(); conn.Close();
3.查询EXCEL
public string getjgdm(string jgname) { string jgdmpath = path + "xls\\jgdmdzb.xls"; OleDbConnection OleConn = getExcelConn(jgdmpath); OleConn.Open(); String sql = "SELECT 机构代码 FROM [机构代码对照表$] where 机构名称='" + jgname + "'";//可是更改Sheet名称,比如sheet2,等等 OleDbDataAdapter OleDaExcel = new OleDbDataAdapter(sql, OleConn); DataSet OleDsExcle = new DataSet(); OleDaExcel.Fill(OleDsExcle, "机构代码对照表"); OleConn.Close(); System.Data.DataTable table = OleDsExcle.Tables[0]; string jgdm = table.Rows[0][0].ToString(); return jgdm; }