一.读取Excel数据，并显示

1.配置文件

<configuration>

    <system.web>

        <compilation debug="true" targetFramework="4.0" />

    </system.web>

  <appSettings>

    <!--连接03版本的-->

    <!--<add key="SQLCONNECTIONSTRING" value="Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=Excel 8.0;Data Source="/>

    <add key="DBPATH" value="App_Data\username.xls"/>-->

   

    <!--连接07版本的-->

    <add key="SQLCONNECTIONSTRING" value="Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=Excel 12.0 Xml;Data Source=" />

    <add key="DBPATH" value="App_Data\usernames.xlsx"/>

    <!--连接07access数据库-->

    <add key="OLEDBCONNECTIONSTRING" value="Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=Access 12.0 Xml;Data Source=" />

    <!--<add key="DBPATH" value="App_Data\username.accdb"/>-->

    <!--使用.net生成Excel表格-->

    <add key="SQLCONNECTIONSTR" value="data source=.;uid=sa;pwd=123456;database=students;pooling=true"/>

  </appSettings>

</configuration>

2.

using System;

using System.Configuration;

using System.Data;

using System.Data.OleDb;

namespace WebApplication

{

    public partial class MyExcelToDG : System.Web.UI.Page

    {

        private readonly string SQLCONNECTIONSTRING = ConfigurationManager.AppSettings["SQLCONNECTIONSTRING"].ToString();

        private readonly string DBPATH = ConfigurationManager.AppSettings["DBPATH"].ToString();

        protected void Page_Load(object sender, EventArgs e)

        {

            if (!IsPostBack)

            {

                GridView1.DataSource = CreateDataSource();

                GridView1.DataBind();

            }

        }

        private DataSet CreateDataSource()

        {

            //设置Excel的文件访问地址

            String ExcelDBPath = SQLCONNECTIONSTRING + Server.MapPath(DBPATH) + ";";

            //定义访问Excel文件的连接

            OleDbConnection conn = new OleDbConnection(ExcelDBPath);

            //OleDbDataAdapter da = new OleDbDataAdapter("SELECT * FROM[username$]",conn);

            OleDbDataAdapter da = new OleDbDataAdapter("SELECT * FROM students", conn);

            DataSet ds = new DataSet();

            da.Fill(ds);

            return ds;

        }

    }

}

 

 

 

 

二.将sqlserver中的数据导出到Excel中

using System;

using System.Configuration;

using System.Data;

using System.Data.SqlClient;

namespace WebApplication

{

    public partial class MyDataToExcel : System.Web.UI.Page

    {

        private readonly string SQLCONNECTIONSTR = ConfigurationManager.AppSettings["SQLCONNECTIONSTR"].ToString();

        protected void Page_Load(object sender, EventArgs e)

        {

            if (!IsPostBack)

            {

                //创建Excel文件

                try

                {

                    CreateExcelTable();

                    Response.Write("<script>alert('成功导出!')</script>");

                }

                catch (Exception)

                {

                    Response.Write("<script>alert('发生错误!')</script>");

                }

            }

        }

        //获取数据

        private DataSet GetData()

        {

            //从数据库中获取数据

            String cmdText = "Select * from Student_Info";

            using (SqlConnection conn = new SqlConnection(SQLCONNECTIONSTR))

            {

                SqlDataAdapter da = new SqlDataAdapter(cmdText, conn);

                conn.Open();

                DataSet ds = new DataSet();

                da.Fill(ds);

                return (ds);

            }

        }

        //创建Excel文件

        private void CreateExcelTable()

        {

            //从数据库获取数据

            DataSet ds = GetData();

            //创建Excel对象

            Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();

            //设置行和列的索引

            int rowIndex = 1;

            int collndex = 0;

            //添加Excel对象的WorkBooks

            excel.Application.Workbooks.Add(true);

            System.Data.DataTable table = ds.Tables[0];

            //将所得到的表的列名，赋给单元格

            foreach (DataColumn col in table.Columns)

            {

                //添加列名

                collndex++;

                excel.Cells[1, collndex] = col.ColumnName;

            }

            //同样的方法处理数据

            foreach(DataRow row in table.Rows)

            {

                rowIndex++;

                collndex = 0;

                foreach (DataColumn col in table.Columns)

                {

                    collndex++;

                    excel.Cells[rowIndex, collndex] = row[col.ColumnName].ToString();

                }

            }

            //不可见，即后台处理

            excel.Visible = false;

            excel.DisplayAlerts = false;

            //保存刚才创建的Excel表格

            excel.Save(MapPath("App_Data/ExcelTable.xls"));

            excel.Application.Workbooks.Close();

            excel.Application.Quit();

            excel.Quit();

            //释放使用的Excel对象

            System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);

            GC.Collect();

        }

    }

}