datetoexcel.aspx.cs

B/S asp.net的C#编的网站

using System;
using System.Data;
using System.Data.SqlClient;
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 Microsoft.Office.Interop.Excel;


public partial class DateToExcel : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {             
                //创建Excel文件
                CreateExcelTable();            
        }
    }

    private readonly string constr = ConfigurationManager.AppSettings["constr"].ToString();
    
    private DataSet GetData()
    { 
       
        //读取数据库数据      
        SqlConnection conn = new SqlConnection(constr);
        DataSet myDS = new DataSet();

        try
        {
            string sql = "Select Distinct * from [Team],[Teacher],[Player] where Team.TeamID=Teacher.TeamID AND Player.TeamID=Team.TeamID";
            SqlDataAdapter sda = new SqlDataAdapter(sql, conn);           

            conn.Open();
            
            sda.Fill(myDS);
        }
        catch (SqlException ex)
        {
            ex.ToString();
        }
        finally
        {
            conn.Close();
        }


        return myDS;
    }

    private void CreateExcelTable()
    {
        //从DataSet中读取数据
        DataSet ds = GetData();
        //创建Excel对象      
        Microsoft.Office.Interop.Excel.Application excel = new Application(); 
         
        //设置行和列
        int rowindex = 1;
        int colindex = 0;

        try
        {
            //添加Excel对象的WorkBooks
            excel.Application.Workbooks.Add(true);
            System.Data.DataTable tabel = ds.Tables[0];

            //将所得表的列名赋值给单元格
            foreach (DataColumn col in tabel.Columns)
            {
                colindex++;
                excel.Cells[1, colindex] = col.ColumnName;
            }
            foreach (DataRow row in tabel.Rows)
            {
                rowindex++;
                colindex = 0;
                foreach (DataColumn col in tabel.Columns)
                {
                    colindex++;
                    excel.Cells[rowindex, colindex] = row[col.ColumnName].ToString();
                }
            }


            //后台处理
            excel.Visible = false;
            excel.DisplayAlerts = false;


            //保存
            excel.Save(MapPath(""));
            //excel.ActiveWorkbook.SaveAs(strExcelFileName + ".XLS", XlFileFormat.xlExcel9795, null, null, false, false, XlSaveAsAccessMode.xlNoChange, null, null, null, null, null);
            excel.Application.Workbooks.Close();
            excel.Application.Quit();
            excel.Quit();
        }
        catch (Exception e)
        {
            e.ToString();
        }
        finally
        {
            System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
            //垃圾收集
            GC.Collect();
        }
    
    
    }
}