当前位置:编程学习 > C#/ASP.NET >>

DataTable導出到Excel

谁给个代码 参考下。。。 --------------------编程问答-------------------- 我有等等 --------------------编程问答-------------------- http://topic.csdn.net/u/20091110/16/5ed6d2e1-04cb-4412-9976-97e849c77ac5.html

参考 --------------------编程问答--------------------
#region 普通的Excel导出

    /// <summary>

    /// DataTable To Excel

    /// </summary>

    /// <param name="dt">DataTable Name</param>

    /// <param name="typeid">1,Excel 2,XML</param>

    /// <param name="FileName">文件名</param>

    public void CreateExcel(System.Data.DataTable dt, string typeid, string FileName)

    {

        HttpResponse resp;

        resp = Page.Response;

        resp.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");

        resp.ContentType = "application/ms-excel";


        resp.AddHeader("Content-Disposition",

"attachment; filename=" + System.Web.HttpUtility.UrlEncode(FileName, System.Text.Encoding.UTF8) + ".xls");


        this.EnableViewState = false;


        string colHeaders = "", Is_item = "";

        int i = 0;


        //定义表对象与行对象,同时使用DataSet对其值进行初始化

        DataRow[] myRow = dt.Select("");

        //typeid=="1"时导出为Excel格式文件;typeid=="2"时导出为XML文件

        if (typeid == "1")

        {


            //取得数据表各列标题,标题之间以\t分割,最后一个列标题后加回车符

            for (i = 0; i < dt.Columns.Count; i++)

            {

                colHeaders += dt.Columns[i].Caption.ToString() + "\t";

            }

            colHeaders += "\n";


            resp.Write(colHeaders);

            //逐行处理数据

            foreach (DataRow row in myRow)

            {

                //在当前行中,逐列取得数据,数据之间以\t分割,结束时加回车符\n

                for (i = 0; i < dt.Columns.Count; i++)

                {

                    Is_item += row[i].ToString() + "\t";

                }

                Is_item += "\n";

                resp.Write(Is_item);

                Is_item = "";

            }

        }

        else

        {

            if (typeid == "2")

            {

                //从DataSet中直接导出XML数据并且写到HTTP输出流中

                resp.Write(dt.DataSet.GetXml());

            }

        }

        //写缓冲区中的数据到HTTP头文件中

        resp.End();

    }

    #endregion
http://hi.baidu.com/niki250/blog/item/97305d17129f60044a90a794.html --------------------编程问答-------------------- /// <summary>
    /// DataTable導出到Excel.繁體OS,無亂碼問題.
    /// </summary>
    /// <param name="dt"></param>
    /// <param name="strFileName">含.xls後綴</param>
    public void DownloadAsExcel(DataTable dt, string strFileName)
    {
        try
        {
            StringWriter sw = new StringWriter();
            string colstr = "";
            foreach (DataColumn col in dt.Columns)
            {
                colstr += col.ColumnName + "\t";
            }
            sw.WriteLine(colstr);

            foreach (DataRow row in dt.Rows)
            {
                colstr = "";
                foreach (DataColumn col in dt.Columns)
                {
                    colstr += row[col.ColumnName].ToString() + "\t";
                }
                sw.WriteLine(colstr);
            }
            sw.Close();
            System.Web.HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment; filename=" + strFileName + "");
            System.Web.HttpContext.Current.Response.ContentType = "application/ms-excel";
            System.Web.HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.Default;
            System.Web.HttpContext.Current.Response.Write(sw);
            System.Web.HttpContext.Current.Response.End();
        }
        catch (Exception ex)
        {
            lblMessage.Text = ex.Message;
        }
    } --------------------编程问答--------------------
    //先将Excel传到服务器上面
    protected void Button1_Click(object sender, EventArgs e)
    {
        string Excel = string.Empty;
        Boolean fileOK = false;
        String path = Server.MapPath("~/UploadExcel/");
        if (FileUpload1.HasFile)
        {
            String fileExtension =
                System.IO.Path.GetExtension(FileUpload1.FileName).ToLower();
            String[] allowedExtensions = 
                { ".xls" };
            for (int i = 0; i < allowedExtensions.Length; i++)
            {
                if (fileExtension == allowedExtensions[i])
                {
                    fileOK = true;
                }
            }
        }

        if (fileOK)
        {
            try
            {
                //FileUpload1.PostedFile.SaveAs(path
                //    + FileUpload1.FileName);
                //Excel = FileUpload1.PostedFile.FileName;
                Excel=path + FileUpload1.FileName;

                Excel = path + System.IO.Path.GetFileName(FileUpload1.FileName);
                FileUpload1.PostedFile.SaveAs(Excel);

                
               // Response.Write(Excel);
                DataTableToDB(Excel);
                //Label1.Text = "File uploaded!";
            }
            catch (Exception ex)
            {
                Response.Write(ex.Message);
                Label1.Text = "上传失败!";
            }
        }
        else
        {
            Label1.Text = "文件格式不对.";
        }

    }
    public static void DataTableToDB(string Ex)
    {
        ASP.admin_bjproduct_list_aspx list = new admin_bjproduct_list_aspx();
        BJProductCategory CC = new BJProductCategory();
       
        DataTable dtExcel = ExcelToDataTable(Ex, "Sheet1");
        DT = CreateParentTable();
        try
        {
            for (int i = 0; i < dtExcel.Rows.Count; i++)
            {
                if (dtExcel.Rows[i][0].ToString() != null || dtExcel.Rows[i][0].ToString() != "")
                {

                    DR = DT.NewRow();
                    int PID = CategoryID(dtExcel.Rows[i][2].ToString());
                    CC.ID = PID;
                    WebManager.GetInfo(CC);
                    if (CC.Title == "")
                    {
                        continue;
                    }
                    else
                    {
                        DR["Keyword"] = dtExcel.Rows[i][0].ToString();
                        DR["Title"] = dtExcel.Rows[i][1].ToString();
                        DR["ParentID"] = PID;
                     
                        DR["tmp1"] = dtExcel.Rows[i][3].ToString();
                        DR["tmp2"] = float.Parse(dtExcel.Rows[i][4].ToString());
                        DR["tmp3"] = dtExcel.Rows[i][5].ToString();

                    }
                    DT.Rows.Add(DR);

                }
                else
                {
                    list.AlertMsg("该行数据为空!");
                }
            }

        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.Message);
        }
         

        using (System.Data.SqlClient.SqlBulkCopy bulkCopy =
                           new System.Data.SqlClient.SqlBulkCopy(@"Data Source=DGIDC11016\WS_HGO;Initial Catalog=F:\我的单子\200904\项目\ZYT\APP_DATA\BJYZT.MDF;User ID=sa;Password=123456;max pool size=512;pooling=true;"))
        {
            bulkCopy.DestinationTableName =
                "BJProduct";
              bulkCopy.ColumnMappings.Add("Keyword", "Keyword");
            bulkCopy.ColumnMappings.Add("Title", "Title");
            bulkCopy.ColumnMappings.Add("ParentID", "ParentID");
         
            bulkCopy.ColumnMappings.Add("tmp1", "tmp1");
            bulkCopy.ColumnMappings.Add("tmp2", "tmp2");
            bulkCopy.ColumnMappings.Add("tmp3", "tmp3");

            try
            {
                bulkCopy.WriteToServer(DT);
                list.AlertMsg("数据导入成功!");
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                DT.Clear();
            }
        }
    }
--------------------编程问答--------------------
 //将Excel中的内容插入到DataTable中
    public static DataTable ExcelToDataTable(string strExcelFileName, string strSheetName)
    {
        string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + strExcelFileName + ";" + "Extended Properties=Excel 8.0;";
        string strExcel = string.Format("select * from [{0}$]", strSheetName);
        DataSet ds = new DataSet();

        using (System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection(strConn))
        {
            conn.Open();
            System.Data.OleDb.OleDbDataAdapter adapter = new System.Data.OleDb.OleDbDataAdapter(strExcel, strConn);
            adapter.Fill(ds);
            conn.Close();
            conn.Dispose();
        }

        return ds.Tables[0];
    }
    //获取父类ID
    public static int CategoryID(string category)
    {
        int CID = 1;
        string s = "cn";
        System.Data.SqlClient.SqlConnection Conn = new System.Data.SqlClient.SqlConnection();

        Conn.ConnectionString = @"Data Source=DGIDC11016\WS_HGO;Initial Catalog=F:\我的单子\200904\项目\ZYT\APP_DATA\BJYZT.MDF;User ID=sa;Password=123456;";
        Conn.Open();
        string sqlcategory = "insert into BJProductCategory (Title,ParentID,Version) values('" + category + "'," + 1 + ",'" + s + "')";
        System.Data.SqlClient.SqlCommand Com = new System.Data.SqlClient.SqlCommand(sqlcategory, Conn);

        int i = Com.ExecuteNonQuery();
        if (i > 0)
        {
            //取出最大值就是当前插入的ID
            string str = "select max(ID) AS ID from BJProductCategory";
            System.Data.SqlClient.SqlCommand Cmd = new System.Data.SqlClient.SqlCommand(str, Conn);
            CID = Convert.ToInt32(Cmd.ExecuteScalar());
        }
        Conn.Close();
        Conn.Dispose();
        return CID;


    }
    //Title,ParentID,tmp1,tmp2,tmp3,Version
    public static DataTable CreateParentTable()
    {
  ParentTable.Columns.Clear();
        DC = new DataColumn();
        DC.ColumnName = "Title";
        DC.DataType = System.Type.GetType("System.String");
        ParentTable.Columns.Add(DC);

        DC = new DataColumn();
        DC.ColumnName = "Keyword";
        DC.DataType = System.Type.GetType("System.String");
        ParentTable.Columns.Add(DC);
        
        DC = new DataColumn();
        DC.ColumnName = "ParentID";
        DC.DataType = System.Type.GetType("System.Int32");
        ParentTable.Columns.Add(DC);
        
        DC = new DataColumn();
        DC.ColumnName = "tmp1";
        DC.DataType = System.Type.GetType("System.String");
        ParentTable.Columns.Add(DC);
        DC = new DataColumn();
        DC.ColumnName = "tmp2";
        DC.DataType = System.Type.GetType("System.String");
        ParentTable.Columns.Add(DC);
        DC = new DataColumn();
        DC.ColumnName = "tmp3";
        DC.DataType = System.Type.GetType("System.String");
        ParentTable.Columns.Add(DC);
        DC = new DataColumn();
        DC.ColumnName = "Version";
        DC.DataType = System.Type.GetType("System.String");
        ParentTable.Columns.Add(DC);
        return ParentTable;
    }
    
    BJProductCategory Category = new BJProductCategory();
    BJProduct Product = new BJProduct(); 
    protected void Button2_Click(object sender, EventArgs e)
    {
        string Type = TxtCategory.Text.Trim();
        if (Type == "")
        {
            string script = "<script>";
            script += "alert('您删除的分类不能为空!')";
            script += "</";
            script += "script>";
            Page.RegisterStartupScript("", script);
        }
        else
        {
            Category.Title = Type;
            DataTable DTParent = WebManager.List(Category, "", "ID", "&title%", "OrderNo");
            if (DTParent.Rows.Count>0)
            {
                for (int j = 0; j < DTParent.Rows.Count; j++)
                {
                    Product.ParentID = int.Parse(DTParent.Rows[j][0].ToString());
                    DataTable dt = WebManager.List(Product, "", "ID,Title,ParentID", "&parentid=", "[OrderNo]");
                    if (dt.Rows.Count > 0)
                    {
                        for (int i = 0; i < dt.Rows.Count; i++)
                        {
                            Product.ID = int.Parse(dt.Rows[i][0].ToString());
                            WebManager.Delete(Product);
                            
                            Category.ID = int.Parse(dt.Rows[i][2].ToString());
                            WebManager.Delete(Category);
                        }
                    }
                }
                AlertMsg("删除成功!");
            } 
               
            else
            {
                AlertMsg("没有此分类信息!");
            }
        }

    }
    public void AlertMsg(string Msg)
    {
        string script = "<script>";
        script += "alert('" + Msg + "')";
        script += "</";
        script += "script>";        
        Page.RegisterStartupScript("", script);
    }
--------------------编程问答-------------------- 我的这个是可以将产品分类,产品名称一起倒入到Excel中去的
如果单独到某一张表的话
google --------------------编程问答-------------------- mark,学习 --------------------编程问答-------------------- 貌似你们都忘了一个很难缠的问题:程序推出后EXCEL进程一直在内存中,打开任务管理器可以看到
补充:.NET技术 ,  C#
CopyRight © 2012 站长网 编程知识问答 www.zzzyk.com All Rights Reserved
部份技术文章来自网络,