從DataTable匯出到Excel

2010-04-09

8554
0

前陣子用到要將查詢到的資料醫出到Excel中，
並調整格式合併格式...
所以上網查了一些資料...
並自己做個記錄。

			//需加入參考
			//References右鍵AddReferences => COM => Microsoft Excel 10.0 Object Library
			//在References會多Excel及Microsoft.Office.Core

			DataTable thisTable = this.dsQuery.Tables["test_table"];

			Excel.Application oXL;
			Excel._Workbook oWB;
			Excel._Worksheet oSheet;
			Excel.Range oRng;

			try
			{
				oXL = new Excel.Application();
				//加入新的活頁簿
				oWB =(Excel._Workbook)oXL.Workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
				//引用工作表
				oSheet = (Excel._Worksheet)oWB.ActiveSheet;

				//Sheet名稱
				oSheet.Name = "Excel測試文件";

				//加入內容
				oSheet.Cells[1, 1] = "Excel測試文件";

				for(int i = 1; i <= thisTable.Rows.Count; i++)
				{
					for(int j = 1; j <= thisTable.Columns.Count; j++)
					{
						oSheet.Cells[i + 1, j] = thisTable.Rows[i - 1][j - 1];
					}
				}

				//合併儲存格範圍
				oRng = oSheet.get_Range(oSheet.Cells[1, 1], oSheet.Cells[1, thisTable.Columns.Count]);   
				oRng.MergeCells = true;//合併儲存格
				oRng.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;//文字對齊方式
				oRng.NumberFormat = "yyyy-MM-dd";//設定欄位格式
				oRng.ColumnWidth = 20;//設定欄位寬度

				//存檔
				string SavePath = @"D:\";
				string FileName = "Excel測試文件";

				//若為EXCEL2000, 將最後一個參數拿掉即可
				oWB.SaveAs(SavePath + FileName, Excel.XlFileFormat.xlWorkbookNormal, 
					null, null, false, false, Excel.XlSaveAsAccessMode.xlShared, 
					false, false, null, null, null);

				//關閉文件
				oWB.Close(null, null, null);
				oXL.Workbooks.Close();
				oXL.Quit();

				//釋放資源
				System.Runtime.InteropServices.Marshal.ReleaseComObject(oXL);
				System.Runtime.InteropServices.Marshal.ReleaseComObject(oSheet);
				System.Runtime.InteropServices.Marshal.ReleaseComObject(oWB);
				oSheet = null;
				oWB = null;
				oXL = null;

				System.Diagnostics.Process process = new System.Diagnostics.Process(); 
				process.StartInfo.FileName = @"D:\" + FileName + ".xls";
				process.Start(); 
				process.Close(); 

				//刪除檔案
				if(File.Exists(@"D:\" + FileName + ".xls"))
					File.Delete(@"D:\" + FileName + ".xls");
			}
			catch(Exception ex)
			{
				throw ex;
			}

Jenny:
    陽光令人愉快,雨水令人振作,
    風聲令人奮起,雪花令人興奮,
    沒有所謂的壞天氣!!
    只有不同的好天氣!!

回首頁