．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_Datausername.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_Datausernames.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_Datausername.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();
         } 
     }
 }