．NET操作Excel

一.读取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();
        }
    }
}