mydatatoexcel.aspx.cs

ASP C#代码实例 适合初学人士学习使用

using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Web;
using System.Web.SessionState;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
using System.Data.OleDb;
using System.Configuration;
using Excel;
using Microsoft.Office.Core;
using System.Data.SqlClient;
using System.Diagnostics;
using System.Threading;

namespace Example_11_8
{
	/// <summary>
	/// Summary description for MyDataToExcel.
	/// </summary>
	public class MyDataToExcel : System.Web.UI.Page
	{
		private readonly string SQLCONNECTIONSTRING = ConfigurationSettings.AppSettings["SQLCONNECTIONSTRING"].ToString();
	
		public void Page_Load(Object sender, EventArgs e)
		{
			if (!IsPostBack) 
			{
				CreateExcelTable();
			} 
		}

		private DataSet GetData()
		{
			///从数据库中获取数据
			String cmdText = "Select * from Staff";
			SqlConnection myConnection = new SqlConnection(SQLCONNECTIONSTRING);
			SqlDataAdapter sqldataAdapter=new SqlDataAdapter(cmdText,myConnection);		

			///执行数据库查询
			myConnection.Open();			
			DataSet myds = new DataSet();
			sqldataAdapter.Fill(myds);

			///关闭数据库链接
			myConnection.Close();
			///返回数据集
			return(myds);
		}

		private void CreateExcelTable()
		{
			DataSet ds = GetData();

			Excel.Application excel = new Excel.Application();  
			int rowIndex=1;  
			int colIndex=0;  

			excel.Application.Workbooks.Add(true);  
      
			System.Data.DataTable table = ds.Tables[0];  
       
			//将所得到的表的列名,赋值给单元格  
			foreach(DataColumn col in table.Columns)  
			{  
				colIndex++;   
				excel.Cells[1,colIndex]= col.ColumnName;      
			}  

			//同样方法处理数据  
			foreach(DataRow row in table.Rows)  
			{  
				rowIndex++;  
				colIndex=0;  
				foreach(DataColumn col in table.Columns)  
				{  
					colIndex++;  
					excel.Cells[rowIndex,colIndex]=row[col.ColumnName].ToString();  
				}  
			}  
			//不可见,即后台处理  
			excel.Visible=true; 			
			excel.DisplayAlerts=false;		

			excel.Save(MapPath("ExcelDB/exceltable.xls"));
			excel.Application.Workbooks.Close();
			excel.Application.Quit();
			excel.Quit();
			System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);   
	        //KillProcess("EXCEL.EXE");   
			GC.Collect();
		}

		#region Web Form Designer generated code
		override protected void OnInit(EventArgs e)
		{
			//
			// CODEGEN: This call is required by the ASP.NET Web Form Designer.
			//
			InitializeComponent();
			base.OnInit(e);
		}
		
		/// <summary>
		/// Required method for Designer support - do not modify
		/// the contents of this method with the code editor.
		/// </summary>
		private void InitializeComponent()
		{    
			this.Load += new System.EventHandler(this.Page_Load);

		}
		#endregion
	}
}