C#与EXCEL

最新推荐文章于 2024-01-20 10:14:59 发布

最新推荐文章于 2024-01-20 10:14:59 发布 · 196 阅读

VS 专栏收录该内容

19 篇文章

订阅专栏

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;
        }