C# asp.net 数据库导出；徐敬标学习，分享；

此处为css类；

using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using Microsoft.Office.Interop.Excel;
using System.Data.OleDb;

/// <summary>
///excell 的摘要说明
/// </summary>
public class excell
{
	public excell()
	{
		//
		//TODO: 在此处添加构造函数逻辑
		//
	}
    // 第一步代码：
    /// <summary>
    /// 读取EXCEL内容返回一个DATASET
    /// </summary>
    /// <param name="strxls">execl 地址</param>
    /// <param name="strSheetName">EXECL里面表的名称</param>
    /// <returns></returns>
    public static DataSet getExcelDataSet(string strxls, string strSheetName)
    {
        //注意连接的驱动是 Microsoft.Jet.OLEDB.4.0，附件属性还有：Extended Properties=Excel 8.0//表示是97-2000版本
        //，HDR=YES/NO//第一行是否为标题， IMEX=1//表示以文本读取。
        string strConn = "Provider=Microsoft.ACE.OLEDB.12.0;" + "data source=" + strxls + ";Extended Properties='Excel 12.0 Xml;HDR=YES;IMEX=1'";
        OleDbConnection myOleConn = new System.Data.OleDb.OleDbConnection(strConn);
        OleDbDataAdapter MyCommand = new System.Data.OleDb.OleDbDataAdapter("select * from [" + strSheetName + "$]", myOleConn);
        DataSet DS = new System.Data.DataSet();
        MyCommand.Fill(DS);
        myOleConn.Close();
        return DS;

    }

    /// <summary>
    /// 导入表方法
    /// </summary>
    /// <param name="filePath">excel路径</param>
    /// <returns></returns>
    public static DataSet GetExcelContent(string filePath)
    {
        //excel2007,兼容2003   
        string strCon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties='Excel 12.0 Xml;HDR=YES;IMEX=1';";
        //excel2003   
        // string strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties='Excel 8.0;HDR=No;IMEX=1'";   
        System.Data.OleDb.OleDbConnection myConn = new System.Data.OleDb.OleDbConnection(strCon);
        myConn.Open();
        //获取excel第一标签名   
       System.Data.DataTable schemaTable = myConn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null);
        string tableName = "";//标签名  
        for (int i = 0; i < schemaTable.Rows.Count; i++)
        {
            if (schemaTable.Rows[i][2].ToString().Trim() == "Sheet1$")
            {
                tableName = "Sheet1$";
            }
        }
        if (tableName == "")
        {
            MessageBox.AlertToUrl("工作表名子错！！", "#");
        }


        string strCom = "SELECT *  FROM [" + tableName + "]";//查询语句   

        System.Data.OleDb.OleDbDataAdapter myCommand = new System.Data.OleDb.OleDbDataAdapter(strCom, myConn);
        //创建一个DataSet对象      
        DataSet myDataSet = new DataSet();
        //得到自己的DataSet对象      
        myCommand.Fill(myDataSet);
        //关闭此数据链接      
        myConn.Close();
        return myDataSet;
    }
}

 

 

此为处理程序；

protected void LinkButton1_Click(object sender, EventArgs e)
    {
        string val = "";
        val = RadioButtonList1.SelectedValue;
        if (val == null || val == "")
        {
            MessageBox.AlertToUrl("请选择所要到出的表", "excelchu.aspx");
        }
        if (FileUpload1.HasFile) //判断上传的文件不是空的
        {
            //上传文件的全路径   
            string path = FileUpload1.PostedFile.FileName;
            //文件全名   
            path = path.Substring(path.LastIndexOf("\\") + 1);
            //文件后缀   
            path = path.Substring(path.LastIndexOf("."));
            //判断excel文件   
            if (path.ToLower() != ".xls" && path.ToLower() != ".xlsx")
            {
                this.ClientScript.RegisterStartupScript(this.GetType(), "", "<script>alert('请选择excel文件')</script>");
                return;
            }

            //上传文件新名,防止重名   
            path = DateTime.Now.ToString("yyyyMMddmmssffff") + path;
            string time = DateTime.Now.ToString("yyyyMMddmmssffff");
            string PicPath = Server.MapPath(".");
            PicPath = PicPath.Substring(0, PicPath.LastIndexOf("\\"));       //从后截取\\以后

            //创建excel文件保存路径   
            Directory.CreateDirectory(PicPath + "\\excel\\" + time);

            string weji = Directory.CreateDirectory(PicPath + "\\excel\\" + time).Name;



            string ming = "Sheet1";
            //保存路径   
            path = PicPath + "\\excel\\" + weji + "\\" + path;
            //保存   
            FileUpload1.PostedFile.SaveAs(path);

            //用gridview显示   

            DataSet aa = excell.getExcelDataSet(path, ming);

            dts = aa.Tables[0];
            if (dts.Rows.Count > 0)
            {
                string colHeaders = ""; string ls_item = "";
                //定义表对象与行对象，同时用DataSet对其值进行初始化
                DataTable dt = dts;
                DataRow[] myRow = dt.Select();//可以类似dt.Select("id>10")之形式达到数据筛选目的
                int i = 0;
                int cl = dt.Columns.Count;
                //取得数据表各列标题，各标题之间以\t分割，最后一个列标题后加回车符
                for (i = 1; i < cl; i++)
                {
                    if (i == (cl - 1))//最后一列，加\n
                    {
                        colHeaders += dt.Columns[i].Caption.ToString() + "";
                    }
                    else
                    {
                        colHeaders += dt.Columns[i].Caption.ToString() + ",";
                    }
                }
                //Response.Write(colHeaders);


                //向HTTP输出流中写入取得的数据信息

                //逐行处理数据  
                foreach (DataRow row in myRow)
                {
                    //当前行数据写入HTTP输出流，并且置空ls_item以便下行数据    
                    for (i = 1; i < cl; i++)
                    {
                        if (i == (cl - 1))//最后一列，加\n
                        {
                            ls_item += "'" + row[i].ToString() + "'";
                        }
                        else
                        {
                            ls_item += "'" + row[i].ToString() + "',";
                        }


                    }
                    // Response.Write(ls_item);
                    string abs = new shubiao().inst(val, colHeaders, ls_item);

                    ls_item = "";
                    // Response.Write(abs);
                }
                // Response.Write(ls_item);

                //Response.Write("<script>alert('" + dts.Rows.Count + "条导入成功')</script>");
                MessageBox.AlertToUrl("" + dts.Rows.Count + "条导入成功！！", "excelru.aspx");

            }
            else
            {
                MessageBox.AlertToUrl("工作表名子错！！", "excelru.aspx");
            }
        }
        else
        {
            MessageBox.AlertToUrl("请选择要上传的文件！！", "excelru.aspx");
        }
    }