asp.net中Excel导入，导出

 

public partial class _Default : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {

    }
    //导出Excel文件
    public void OutputExcel_Click(object sender, EventArgs e)
    {

        string sql = "SELECT * FROM Props_Type";
        DataSet ds = new DataSet();
        SqlConnection con = DBHelper.Connection;
        SqlDataAdapter sda = new SqlDataAdapter(sql, con);
        sda.Fill(ds, "Props_Type");
        StringBuilder sb = new StringBuilder();
        //在Excel文件里面显示的[对应数据库里面的字段]
        sb.AppendLine("道具类型ID:\t"+"道具类型名称:\t");
        foreach (DataRow dr in ds.Tables[0].Rows )
        {
            sb.Append(dr.ItemArray[0].ToString() + "\t");
            sb.Append(dr.ItemArray[1].ToString() + "\t\r\n");
        }
        HttpResponse hr = Page.Response;
        hr.Write("以下是导出的文件内容：\r\n");
        hr.ContentEncoding = Encoding.GetEncoding("GBK");
        //filename="output.xls"我这里写死了的，就是Excel里面Sheet1名字
        hr.AppendHeader("Content-Disposition","attachment;filename=output.xls");
        //这里设置输出的类型
        hr.ContentType = "application/ms-excel";
        hr.Write(sb.ToString () );
        hr.Flush();
        hr.End();
    }
    //导入文件
    protected void ImportExcel_Click(object sender, EventArgs e)
    {
        string filePath = "";
        string getErrorMsg = "";

        DataSet ds = new DataSet();
        if (!fuFile.HasFile)
        {
            Response.Write("<script>alert('请选择你要导入的Excel文件');</script>");
            return;
        }
        //获取文件的后缀名
        string fileExt = System.IO.Path.GetExtension(fuFile.FileName);
        if (fileExt != ".xls")
        {
            Response.Write("<script>alert('文件类型错误！');</script>");
            return;
        }
        //获取绝对路径
        filePath = fuFile.PostedFile.FileName;
        //string conn = "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=\"dBASE IV;HDR=Yes;IMEX=1\";Data Source = " + filePath + "";

        string conn = "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\";Data Source=" + filePath;
        OleDbConnection excelCon = new OleDbConnection(conn);
        //output是Excel文件里面工作表名 默认为Sheet1,后面需要加上$符号
        OleDbDataAdapter odda = new OleDbDataAdapter("SELECT * FROM [output$]",excelCon);
        try
        {
            odda.Fill(ds, "Props_Type");
        }
        catch (Exception ex)
        {
            Response.Write(ex.Message);
            Response.Write("<script>alert('"+ex.Message+"！')</script>");
        }
        finally
        {
            excelCon.Close();
            excelCon.Dispose();
        }
        //将数据写到数据库里面
        if (ds.Tables[0].Rows.Count != 0)
        {
            string sql = "";
            SqlConnection con = DBHelper.Connection;
            SqlCommand sqlCmd = con.CreateCommand();
            SqlTransaction sqlTran = con.BeginTransaction();
            sqlCmd.Transaction = sqlTran;
            try
            {
                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    //道具类型名称为output工作表里面的字段
                    sql = "INSERT INTO Props_Type VALUES('" + ds.Tables[0].Rows[i]["道具类型名称"] + "')";
                    sqlCmd.CommandText = sql;
                    sqlCmd.ExecuteNonQuery();
                   
                }
               
                sqlTran.Commit();
            }
            catch (Exception ex)
            {
                getErrorMsg = ex.Message;
                Response.Write(ex.Message);
                sqlTran.Rollback();
            }
            finally
            {
                con.Close();
                con.Dispose();
            }

            if (getErrorMsg == "")
            {
                Response.Write("<script>alert('导入Excel文件成功！')</script>");
            }
            else { Response.Write("<script>alert('导入Excel文件失败！')</script>"); }

        }
    }
}