Excel导入＆导出

*需要从GridView　“gvGrade"中将显示数据导出　(网上搜来的）

  protected   void  bnToExcel_Click( object  sender, EventArgs e)
     ... {
        Export("application/ms-excel", "学生成绩报表.xls");
    }

     private   void  Export( string  FileType,  string  FileName)
     ... {
        Response.Charset = "GB2312";
        Response.ContentEncoding = System.Text.Encoding.UTF7;
        Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(FileName, Encoding.UTF8).ToString());
        Response.ContentType = FileType;
        this.EnableViewState = false;
        StringWriter tw = new StringWriter();
        HtmlTextWriter hw = new HtmlTextWriter(tw);
        gvGrade.RenderControl(hw);
        Response.Write(tw.ToString());
        Response.End();
    }

 ２.将Excel内容直接导到Access:

前台：

<% ... @ Page Language="C#" AutoEventWireup="true" CodeFile="FromExcel.aspx.cs" Inherits="Admin_FromExcel"  %>

<! 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 > Untitled Page </ title >
</ head >
< body >
     < form  id ="form1"  runat ="server" >
     < div >
         < asp:FileUpload  ID ="fuExcel"  runat ="server"  Style ="z-index: 100; left: 176px; position: absolute;
            top: 146px"   />
         < asp:Button  ID ="bnsubmit"  runat ="server"  OnClick ="bnsubmit_Click"  Style ="z-index: 101;
            left: 258px; position: absolute; top: 196px"  Text ="提交"   />
         < asp:DropDownList  ID ="ddDepart"  runat ="server"  AutoPostBack ="True"  DataSourceID ="dsGetDepart"
            DataTextField ="Department"  DataValueField ="Department"  Style ="z-index: 102; left: 179px;
            position: absolute; top: 96px" >
         </ asp:DropDownList >
         < asp:AccessDataSource  ID ="dsGetDepart"  runat ="server"  DataFile ="~/App_Data/#SGMS_Data.mdb"
            SelectCommand ="SELECT DISTINCT [Department] FROM [Course]" ></ asp:AccessDataSource >
         < asp:Label  ID ="Label1"  runat ="server"  Height ="27px"  Style ="z-index: 103; left: 60px;
            position: absolute; top: 94px"  Text ="请选择专业："  Width ="98px" ></ asp:Label >
         < asp:CheckBox  ID ="cbCourse"  runat ="server"  OnCheckedChanged ="cbCourse_CheckedChanged"
            Style ="z-index: 104; left: 324px; position: absolute; top: 96px"  Text ="课程"  AutoPostBack ="True"   />
         < asp:DropDownList  ID ="ddCourse"  runat ="server"  AutoPostBack ="True"  DataSourceID ="dsCourse"
            DataTextField ="CourseName"  DataValueField ="CourseName"  Style ="z-index: 106; left: 388px;
            position: absolute; top: 95px"  Enabled ="False" >
         </ asp:DropDownList >
         < asp:AccessDataSource  ID ="dsCourse"  runat ="server"  DataFile ="~/App_Data/#SGMS_Data.mdb"
            SelectCommand ="SELECT DISTINCT [CourseName] FROM [Course] WHERE ([Department] = ?)" >
             < SelectParameters >
                 < asp:ControlParameter  ControlID ="ddDepart"  Name ="Department"  PropertyName ="SelectedValue"
                    Type ="String"   />
             </ SelectParameters >
         </ asp:AccessDataSource >
    
     </ div >
     </ form >
</ body >
</ html >

 后台：

using  System;
using  System.Data;
using  System.Data.OleDb;
using  System.Configuration;
using  System.Collections;
using  System.Web;
using  System.Web.Security;
using  System.Web.UI;
using  System.Web.UI.WebControls;
using  System.Web.UI.WebControls.WebParts;
using  System.Web.UI.HtmlControls;
using  System.Text.RegularExpressions;

public   partial   class  Admin_FromExcel : System.Web.UI.Page
... {
    protected void Page_Load(object sender, EventArgs e)
    ...{
        // If not Login
        if (Session["UserName"].ToString().Trim() == "")
        ...{
            Response.Redirect(@"~Login.aspx");
        }
    }
    protected void bnsubmit_Click(object sender, EventArgs e)
    ...{

        if (this.fuExcel.PostedFile != null)
        ...{
            Check&Save#region Check&Save

            string excelName = fuExcel.PostedFile.FileName;
            int index = excelName.LastIndexOf(".");
            string ext = excelName.Substring(index);
            if (ext != ".xls")
            ...{
                Response.Write("文件格式不正确!");
                Response.End();
            }

            DateTime now = DateTime.Now;
            string newName = ddDepart.SelectedItem.Value.ToString() + "_"   //Department
                           + now.Millisecond.ToString()                     //millisecond
                           + fuExcel.PostedFile.ContentLength.ToString()    //Length of file
                           + ext;                                           //Postfix

            if (cbCourse.Checked == true)
            ...{
                newName = ddDepart.SelectedItem.Value.ToString() + "_"      //Department
                        + ddCourse.SelectedItem.Value.ToString()            //Course
                        + ext;                                               //Postfix
            }

            //Save to App_DataExcel_BAK
            fuExcel.PostedFile.SaveAs(Server.MapPath("..") + @"App_DataExcel_BAK" + newName);

            #endregion

            Connect Excel & fill excelDs#region Connect Excel & fill excelDs

            string excelConnStr = "Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source = '"
                             + excelName + "';Extended Properties=Excel 8.0";
            OleDbConnection excelConn = new OleDbConnection(excelConnStr);
            OleDbDataAdapter excelAdapter = new OleDbDataAdapter("select * from [Sheet1$]", excelConn);
            DataSet excelDs = new DataSet();
            excelAdapter.Fill(excelDs);

            #endregion

            Connect DB & fill dbDs#region Connect DB & fill dbDs

            string dbConnString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
                              + Server.MapPath("..") + @"App_Data#SGMS_Data.mdb";
            OleDbConnection dbConn = new OleDbConnection(dbConnString);
            string sql = "SELECT StuID,Department,Course,Score FROM Grade";
            OleDbDataAdapter dbAdapter = new OleDbDataAdapter(sql, dbConn);
            OleDbCommandBuilder dbBuilder = new OleDbCommandBuilder(dbAdapter);
            DataSet dbDs = new DataSet();
            dbAdapter.Fill(dbDs, "Grade");

            #endregion

            Insert data to DB#region Insert data to DB

            int rowCount = excelDs.Tables[0].Rows.Count;
            int colCount = excelDs.Tables[0].Columns.Count;

            if (cbCourse.Checked == false)
            ...{
                for (int i = 0; i < rowCount; i++)
                ...{
                    DataRow newRow = dbDs.Tables[0].NewRow();
                    string tempId = excelDs.Tables[0].Rows[i][0].ToString();
                    string tempScore = excelDs.Tables[0].Rows[i][2].ToString();

                    if (Regex.IsMatch(tempId, @"^[0-9]+$") && Regex.IsMatch(tempScore, @"^[0-9]+$"))
                    ...{
                        newRow[0] = excelDs.Tables[0].Rows[i][0];
                        newRow[1] = ddDepart.SelectedItem.Value.ToString();
                        newRow[2] = excelDs.Tables[0].Rows[i][1].ToString();
                        newRow[3] = excelDs.Tables[0].Rows[i][2];

                        dbDs.Tables[0].Rows.Add(newRow);
                    }
                }
            }
            else
            ...{
                for (int i = 0; i < rowCount; i++)
                ...{
                    DataRow newRow = dbDs.Tables[0].NewRow();
                    string tempId = excelDs.Tables[0].Rows[i][0].ToString();
                    string tempScore = excelDs.Tables[0].Rows[i][1].ToString();

                    if (Regex.IsMatch(tempId, @"^[0-9]+$") && Regex.IsMatch(tempScore, @"^[0-9]+$"))
                    ...{
                        newRow[0] = excelDs.Tables[0].Rows[i][0];
                        newRow[1] = ddDepart.SelectedItem.Value.ToString();
                        newRow[2] = ddCourse.SelectedItem.Value.ToString();
                        newRow[3] = excelDs.Tables[0].Rows[i][1];

                        dbDs.Tables[0].Rows.Add(newRow);
                    }
                }
            }

            dbAdapter.Update(dbDs, "Grade");
            Response.Write("<script language='javascript'>alert('文件上传成功！');</script>");

            #endregion
        }
        else
        ...{
            Response.Write("<script language='javascript'>alert('文件路径有误，请查证！');</script>");
        }
    }

    protected void cbCourse_CheckedChanged(object sender, EventArgs e)
    ...{
        ddCourse.Enabled = !ddCourse.Enabled;
    }
}