asp.net 操作EXCEL

 

1、直接操作EXCEL

(1)把EXCEL数据导入临时表：

 private void InSert()//原方法导入EXCEL数据
    {
        if (this.TBData.Text.Trim() != "")
        {
            CMS.BLL.FundRankManage cim = new CMS.BLL.FundRankManage();
            //string sql1 = "SELECT * INTO XLImport5 FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=e://website//xzsec//dbf//200806.xls', 'SELECT * FROM [Sheet1$]')";
            string sql = @"drop table CMS_JJFJT SELECT * INTO CMS_JJFJT FROM  OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=e://website//xzsec//dbf//" + this.TBData.Text.Trim() + ".xls', 'SELECT * FROM [Sheet1$]')";
            //string sql = @"drop table CMS_JJFJT SELECT * INTO CMS_JJFJT FROM  OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=d://website//xzsec.com//dbf//" + this.TBData.Text.Trim() + ".xls', 'SELECT * FROM [Sheet1$]')";
            cim.GetExeCmdResult(sql);
            cim.Dispose();
            this.LBMsg.Text = "导入成功!";
        }
        else
        {
            this.LBMsg.Text = "请输入要更新的文件名称!";
        }
    }

(2)，将临时表的所有数据及时间段一同插入新表：

//更新帐户信息
    private void UpdataInfo()
    {
        CMS.BLL.FundRankManage cim = new CMS.BLL.FundRankManage();
      
        System.Collections.ArrayList al = cim.Get("dateKind=" + this.TBData.Text.Trim());
        if (al.Count>0)
        {
            cim.Delete("this.dateKind='"+this.TBData.Text.Trim()+"'");
        }
        string sql = "insert into CMS_FundRankT SELECT * ,'"+this.TBData.Text.Trim()+"' FROM CMS_JJFJT";
        cim.GetExeCmdResult(sql);
        cim.Dispose();    
    }

注：如果网站文件和数据库服务器是分开的，如果采用第一种方法则需要将EXCEL放到数据库服务器相应的文件夹中。第二种则是放到网站文件中。

2、将EXCEL做为数据源

 private void GetExcel()
    {
        CMS.BLL.FundRankManage frm = new CMS.BLL.FundRankManage();
        string strConn;
        strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=e://website//xzsec//dbf//200806.xls;" + "Extended Properties=Excel 8.0";
        OleDbConnection myConnection = new OleDbConnection(strConn);
        OleDbDataAdapter myDa = new OleDbDataAdapter("select   *   from   [sheet1$]", myConnection);
        DataSet myDataSet = new DataSet();
        myConnection.Open();
        myDa.Fill(myDataSet, "ExcelInfo");
        myConnection.Close();
        DataTable dt = myDataSet.Tables["ExcelInfo"];
       
        for (int i = 0; i < dt.Rows.Count; i++)
        {
            CMS.Model.CMS_FundRankT frt = new CMS.Model.CMS_FundRankT();
            frt.id = dt.Rows[i][0].ToString();
            frt.name = dt.Rows[i][1].ToString();
            frt.sczhpj = dt.Rows[i][2].ToString();
            frt.zhpj = dt.Rows[i][3].ToString();
            frt.ynpj = dt.Rows[i][4].ToString();
            frt.rnpj = dt.Rows[i][5].ToString();
            frt.ynpjy = dt.Rows[i][6].ToString();
            frt.rnpjy = dt.Rows[i][7].ToString();
            frt.snpj = dt.Rows[i][8].ToString();
            frt.ynzhb = dt.Rows[i][9].ToString();
            frt.rnzhb = dt.Rows[i][10].ToString();
            frt.ynpjr = dt.Rows[i][11].ToString();
            frt.rnpjr = dt.Rows[i][12].ToString();
            frt.tzlx = dt.Rows[i][13].ToString();
            frt.glgs = dt.Rows[i][14].ToString();
            frt.dateKind = this.TBData.Text.Trim();
             if (this.isExist(frt.id))
            {
                frm.Delete("this.id='" + frt.id + "'");
            }
            frm.ModelObj=frt;
            frm.Insert();
        }
        frm.Dispose();
    }