C# gridview导入excel数据

C#2005 Excel导入数据到GridView的类

把你代码给出来看看。。GR绑EXCEL？我还得是把EXCEL的数据读取到DATASET里面，这样GR绑定就好办了。。

class ExcelCtrl
    {
        #region 将DataGridView控件中数据导出到Excel        /// <summary>
        /// 将DataGridView控件中数据导出到Excel
        /// </summary>
        /// <param name="gridView">DataGridView对象</param>
        /// <param name="isShowExcle">是否显示Excel界面</param>
        /// <returns></returns>
        public bool ExportDataGridview(DataGridView gridView, bool isShowExcle,string path)
        {
            if (gridView.Rows.Count == 0)
                return false;
            //建立Excel对象
            Excel.Application excel = new Excel.Application();
            Excel.Workbook myWorkBook=excel.Application.Workbooks.Add(true);
            excel.Visible = isShowExcle;            //生成字段名称
            for (int i = 0; i < gridView.ColumnCount; i++)
            {
                excel.Cells[1, i + 1] = gridView.Columns[i].HeaderText;
            }
            //填充数据
            for (int i = 0; i < gridView.Rows.Count - 1; i++)
            {
                for (int j = 0; j < gridView.Columns.Count; j++)
                {
                    if (gridView[j, i].ValueType == typeof(string))
                    {
                        excel.Cells[i + 2, j + 1] = gridView[j, i].Value.ToString();
                    }
                    else
                    {
                        excel.Cells[i + 2, j + 1] = gridView[j, i].Value.ToString();
                    }
                }
            }
            object missing = System.Reflection.Missing.Value;
            excel.ActiveWorkbook.Saved = true;
            excel.ActiveWorkbook.SaveAs(path,
                Microsoft.Office.Interop.Excel.XlFileFormat.xlExcel9795,
                missing, missing, false,
                false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange,
                missing, missing, missing, missing, missing);
            myWorkBook.Close(missing, missing, missing);
            myWorkBook = null;
            excel.Quit();            return true;
        }        #endregion        #region 将DataGridView控件中数据导出到Excel，excel格式本身就可以充当数据库，所以我以这种方式进行查询读取        public DataTable ImportExcel(string path)
        {
            string Connecstr = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path
                + ";Extended Properties='Excel 8.0;HDR=YES; IMEX=1'";
            OleDbConnection Con = new OleDbConnection(Connecstr);
            Con.Open();
            string strCommand = "SELECT   *   FROM   [Sheet1$]";
            OleDbDataAdapter adapter = new OleDbDataAdapter(strCommand, Con);
            DataSet DS = new DataSet();
            adapter.Fill(DS, "[Sheet1$]");
            DataTable dt=DS.Tables[0];
            Con.Close();
            return dt;
        }        #endregion
    }

/// <summary>
        ///  导出Exel
        /// </summary>
        /// <param name="ctl">导出数据的控件载体</param>
        /// <param name="FileName">导出Exel的默认名字</param>
        public static void ToExcel(System.Web.UI.Control ctl, string FileName)
        {            HttpContext.Current.Response.Charset = "UTF-8";
            HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.Default;
            HttpContext.Current.Response.ContentType = "application/ms-excel";
            HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment;filename=" + "" + FileName + ".xls");
            ctl.Page.EnableViewState = false;
            System.IO.StringWriter tw = new System.IO.StringWriter();
            System.Web.UI.HtmlTextWriter hw = new System.Web.UI.HtmlTextWriter(tw);
            ctl.RenderControl(hw);
            HttpContext.Current.Response.Write(tw.ToString());
            HttpContext.Current.Response.End();
        }

前台代码：
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
     <asp:FileUpload ID="File1" runat="server" />

    <asp:Button ID="Button2" runat="server" onclick="Button2_Click" Text="Button" />
     <asp:GridView ID="DataGrid1" runat="server">
<RowStyle CssClass="sectiontableentry2" />
<AlternatingRowStyle CssClass="sectiontableentry1" />
</asp:GridView>
    </div>
    </form>
</body>
</html>后台代码：
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Data.OleDb;
public partial class _Default : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {    }
    protected void Button2_Click(object sender, EventArgs e)
    {
        string sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" +
      "Data Source=" + File1.PostedFile.FileName.ToString() + ";" +
      "Extended Properties=Excel 8.0;";        //建立EXCEL的连接
        OleDbConnection objConn = new OleDbConnection(sConnectionString);        objConn.Open();        OleDbCommand objCmdSelect = new OleDbCommand("SELECT * FROM [Sheet1＄]", objConn);
        OleDbDataAdapter objAdapter1 = new OleDbDataAdapter();
        objAdapter1.SelectCommand = objCmdSelect;        DataSet objDataset1 = new DataSet();        objAdapter1.Fill(objDataset1, "XLData");        DataGrid1.DataSource = objDataset1.Tables[0].DefaultView; //测试代码,用来测试是否能读出EXCEL上面的数据
        DataGrid1.DataBind();
    }
}

private static DataSet NewMethod(string ExcelName, string Sheet)
  {
  DataSet ds = new DataSet();
  string MyConnectionstring = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + @System.Web.HttpContext.Current.Server.MapPath("ExcelTemp") + @"\" + ExcelName+ ";Extended Properties='Excel 8.0;HDR=NO;IMEX=1'";
  string str = "SELECT * FROM [" + Sheet + "$]";
  using(OleDbConnection myconn = new OleDbConnection(MyConnectionstring))
  {
  myconn.Open();
  OleDbDataAdapter adp = new OleDbDataAdapter(str, myconn);
  adp.Fill(ds);
  myconn.Close();
  }
  return ds;
  }
SqlDataUpdater更新数据

     GridView.DataSource=dt;
     GridView.DataBind();
     ViewState["result"]=dt;
//汇出Excel
    protected void Btn_OutExcel_Click(object sender, EventArgs e)
    {
        DataTable ExcelData = (DataTable)ViewState["result"];
        if (ExcelData.Rows.Count > 0)
        {
            //定义头
            eo.SetExcelWorkSheet("第一个资料:");
            //定义栏位
            string[] s ={ "ID", "Name", "Class" };
           SetDynDt(eo, 0, s);            //写入数据
            for (int a = 0; a < ExcelData.Rows.Count; a++)
            {
                for (int b = 0; b < ExcelData.Columns.Count; b++)
                {
                    eo.SetRangeValue(a + 1, b, a + 1, b, false, "center", 10, true, "", ExcelData.Rows[a][b].ToString());
                }
            }
            //輸出Excel檔案
            Response.ContentType = "application/vnd.ms-excel";
            //取得excel中的byte[]
            Response.BinaryWrite(eo.GetExcelStream());
            Response.AddHeader("content-disposition", "attachment;filename=" + "DemoExcel" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls");
            Response.End();
        }

    }    //定义表头栏
    protected void SetDynDt(ExcelOutput eoo, int Rowcount, string[] TitleList)
    {
        for (int i = 0; i < TitleList.Length; i++)
        {
            eoo.SetRangeValue(Rowcount, i, Rowcount, i, false, "center", 13, true, "", TitleList[i].ToString());
        }
        Rowcount++;
    }

C#下读写Excel文件的方法，可以参考一下。
http://blog.csdn.net/HuangTao_xa/archive/2010/04/23/5519710.aspx

http://download.csdn.net/source/1862524

这个就成！都得到dataset了，直接操作数据行，导入不就得了

调试易

C# gridview导入excel数据

解决方案 »