using System;
using System.Drawing;
using System.Collections;
using System.ComponentModel;
using System.Windows.Forms;
using System.Data;
using System.Data.SqlClient;
//DataGrid導出Excel時，需要引用COM組件“Microsoft Excel 11.0 Object Library”與“Microsoft Office 11.0 Object Library”
using Excel = Microsoft.Office.Interop.Excel;
using System.Reflection;
using System.IO;
namespace DataGrid_To_Excel
{
	/// <summary>
	/// Form1 的摘要說明。
	/// </summary>
	public class Form1 : System.Windows.Forms.Form
	{
		private System.Windows.Forms.DataGrid dataGrid1;
		private System.Windows.Forms.MainMenu mainMenu1;
		private System.Windows.Forms.MenuItem menuItem1;
		private System.Windows.Forms.MenuItem menuItem2;
		/// <summary>
		/// 必需的設計器變量。
		/// </summary>
		private System.ComponentModel.Container components = null;

		public Form1()
		{
			//
			// Windows 窗體設計器支持所必需的
			//
			InitializeComponent();

			//
			// TODO: 在 InitializeComponent 調用后添加任何構造函數代碼
			//
		}

		/// <summary>
		/// 清理所有正在使用的資源。
		/// </summary>
		protected override void Dispose( bool disposing )
		{
			if( disposing )
			{
				if (components != null) 
				{
					components.Dispose();
				}
			}
			base.Dispose( disposing );
		}

		#region Windows 窗體設計器生成的代碼
		/// <summary>
		/// 設計器支持所需的方法 - 不要使用代碼編輯器修改
		/// 此方法的內容。
		/// </summary>
		private void InitializeComponent()
		{
			this.dataGrid1 = new System.Windows.Forms.DataGrid();
			this.mainMenu1 = new System.Windows.Forms.MainMenu();
			this.menuItem1 = new System.Windows.Forms.MenuItem();
			this.menuItem2 = new System.Windows.Forms.MenuItem();
			((System.ComponentModel.ISupportInitialize)(this.dataGrid1)).BeginInit();
			this.SuspendLayout();
			// 
			// dataGrid1
			// 
			this.dataGrid1.DataMember = "";
			this.dataGrid1.Dock = System.Windows.Forms.DockStyle.Fill;
			this.dataGrid1.HeaderForeColor = System.Drawing.SystemColors.ControlText;
			this.dataGrid1.Location = new System.Drawing.Point(0, 0);
			this.dataGrid1.Name = "dataGrid1";
			this.dataGrid1.Size = new System.Drawing.Size(292, 266);
			this.dataGrid1.TabIndex = 0;
			// 
			// mainMenu1
			// 
			this.mainMenu1.MenuItems.AddRange(new System.Windows.Forms.MenuItem[] {
																					  this.menuItem2,
																					  this.menuItem1});
			this.mainMenu1.RightToLeft = System.Windows.Forms.RightToLeft.No;
			// 
			// menuItem1
			// 
			this.menuItem1.Index = 1;
			this.menuItem1.Text = "導出Excel";
			this.menuItem1.Click += new System.EventHandler(this.menuItem1_Click);
			// 
			// menuItem2
			// 
			this.menuItem2.Index = 0;
			this.menuItem2.Text = "顯示數據";
			this.menuItem2.Click += new System.EventHandler(this.menuItem2_Click);
			// 
			// Form1
			// 
			this.AutoScaleBaseSize = new System.Drawing.Size(6, 14);
			this.ClientSize = new System.Drawing.Size(292, 266);
			this.Controls.Add(this.dataGrid1);
			this.Menu = this.mainMenu1;
			this.Name = "Form1";
			this.Text = "Form1";
			((System.ComponentModel.ISupportInitialize)(this.dataGrid1)).EndInit();
			this.ResumeLayout(false);

		}
		#endregion

		/// <summary>
		/// 應用程序的主入口點。
		/// </summary>
		[STAThread]
		static void Main() 
		{
			Application.Run(new Form1());
		}

		private void menuItem1_Click(object sender, System.EventArgs e)
		{
			exportToExcel();
		}

		private void menuItem2_Click(object sender, System.EventArgs e)
		{
			SqlConnection connection = new SqlConnection();
			SqlCommand command = new SqlCommand();
			SqlDataAdapter adapter = new SqlDataAdapter();
			DataSet ds = new DataSet();
			
			connection.ConnectionString = "packet size=4096;user id=cap;password=cap;data source=hzx;persist security info=False;initial catalog=Northwind";
			command.CommandText = "SELECT * FROM Orders";
			command.Connection = connection;
			adapter.SelectCommand = command;
			adapter.Fill(ds,"Orders");

//			方法一
//			this.dataGrid1.DataSource = ds;//可以成功顯示數據,只用此句，則有導航功能，如果連同下一句共同使用，則直接顯示數據。
//			this.dataGrid1.DataMember = ds.Tables[0].TableName;
//			方法二
//			this.dataGrid1.SetDataBinding(ds,"Orders");
//			方法三
			this.dataGrid1.DataSource = ds.Tables[0];
 		}
		private void exportToExcel()
		{
			DataTable table= (DataTable)this.dataGrid1.DataSource;
			if(table==null) return;

			string saveFileName="";
			bool fileSaved=false;
			SaveFileDialog saveDialog=new SaveFileDialog();
			saveDialog.DefaultExt ="xls";
			saveDialog.Filter="Excel文件|*.xls";
			saveDialog.FileName ="Sheet1";
			saveDialog.ShowDialog();
			saveFileName=saveDialog.FileName;
			if(saveFileName.IndexOf(":")<0) return; //被點了取消
   
			Excel.Application xlApp=new Excel.Application();

			if(xlApp==null)
			{
				MessageBox.Show("無法創建Excel對象，可能您的機子未安裝Excel");
				return;
			}

			Excel.Workbooks workbooks=xlApp.Workbooks;
			Excel.Workbook workbook=workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
			Excel.Worksheet worksheet=(Excel.Worksheet)workbook.Worksheets[1];//取得sheet1
			Excel.Range range;

			string oldCaption=this.dataGrid1.CaptionText;
			long totalCount=table.Rows.Count;
			long rowRead=0;
			float percent=0;

			worksheet.Cells[1,1]=this.dataGrid1.CaptionText;
			//寫入字段
			for(int i=0;i<table.Columns.Count;i++)
			{
				worksheet.Cells[2,i+1]=table.Columns[i].ColumnName;  
				range=(Excel.Range)worksheet.Cells[2,i+1];
				range.Interior.ColorIndex = 15;
				range.Font.Bold = true;
  
			}
			//寫入數值
			this.dataGrid1.CaptionVisible = true;
			for(int r=0;r<table.Rows.Count;r++)
			{
				for(int i=0;i<table.Columns.Count;i++)
				{
					worksheet.Cells[r+3,i+1]=table.Rows[r][i];     
				}
				rowRead++;
				percent=((float)(100*rowRead))/totalCount;    
				this.dataGrid1.CaptionText = "正在導出數據["+ percent.ToString("0.00")  +"%]...";
				Application.DoEvents();
			}
			this.dataGrid1.CaptionVisible = false;
			this.dataGrid1.CaptionText = oldCaption;

			range=worksheet.get_Range(worksheet.Cells[2,1],worksheet.Cells[table.Rows.Count+2,table.Columns.Count]);
			range.BorderAround(Excel.XlLineStyle.xlContinuous,Excel.XlBorderWeight.xlThin,Excel.XlColorIndex.xlColorIndexAutomatic,null);
   
			range.Borders[Excel.XlBordersIndex.xlInsideHorizontal].ColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic;
			range.Borders[Excel.XlBordersIndex.xlInsideHorizontal].LineStyle =Excel.XlLineStyle.xlContinuous;
			range.Borders[Excel.XlBordersIndex.xlInsideHorizontal].Weight =Excel.XlBorderWeight.xlThin;

			if(table.Columns.Count>1)
			{
				range.Borders[Excel.XlBordersIndex.xlInsideVertical].ColorIndex =Excel.XlColorIndex.xlColorIndexAutomatic;
				range.Borders[Excel.XlBordersIndex.xlInsideVertical].LineStyle = Excel.XlLineStyle.xlContinuous;
				range.Borders[Excel.XlBordersIndex.xlInsideVertical].Weight = Excel.XlBorderWeight.xlThin;
			}

			if(saveFileName!="")
			{    
				try
				{
					workbook.Saved =true;   
					workbook.SaveCopyAs(saveFileName);
					fileSaved=true;
				}
				catch(Exception ex)
				{
					fileSaved=false;
					MessageBox.Show("導出文件時出錯,文件可能正被打開！\n"+ex.Message);
				}
			}
			else
			{
				fileSaved=false;
			}   
			xlApp.Quit();   
			GC.Collect();//強行銷毀
			if(fileSaved && File.Exists(saveFileName)) System.Diagnostics.Process.Start(saveFileName);
		}
	}
}