- 但使用 NetOffice 的缺點為，所宣告 Excel 的物件在使用完畢，需逐一釋放用 Excel物件
- Code
using System;
using System.IO;
using System.Runtime.InteropServices;
using Excel = Microsoft.Office.Interop.Excel;
namespace CAExcel
{
///
/// Create Excel
/// IDisposable: https://msdn.microsoft.com/zh-tw/library/system.idisposable(v=vs.110).aspx
///
class CreateExcel : IDisposable
{
///
/// The save path
///
private string sSavePath = @"D:\DemoExcel.xlsx";
///
/// 設定必要的物件
/// 按照順序分別是 Application > Workbook > Worksheet > Range > Cell
/// (1) Application ：代表一個 Excel 程序
/// (2) WorkBook ：代表一個 Excel 工作簿
/// (3) WorkSheet ：代表一個 Excel 工作表，一個 WorkBook 包含好幾個工作表
/// (4) Range ：代表 WorkSheet 中的多個單元格區域
/// (5) Cell ：代表 WorkSheet 中的一個單元格
/// Creae an Excel application instance
///
private Excel.Application xlApp = null;
///
/// 代表一個 Excel 工作簿
///
private Excel.Workbooks xlWorkBooks = null;
///
/// 代表一個 Excel 工作簿
///
private Excel.Workbook xlWorkBook = null;
///
/// 代表一個 Excel 工作表，一個 WorkBook 預設值包含 3 工作表
///
private Excel.Sheets xlWorkSheets = null;
///
/// Sheet Page 1
///
private Excel.Worksheet xlWorkSheetPage1 = null;
///
/// Sheet Page 2
///
private Excel.Worksheet xlWorkSheetPage2 = null;
///
/// Sheet Page 3
///
private Excel.Worksheet xlWorkSheetPage3 = null;
///
/// 釋放資源
///
private bool bDisposed = false;
///
/// 建構子
///
public CreateExcel()
{
}
///
/// 解構子
///
~CreateExcel()
{
Dispose(false);
}
///
/// 釋放資源(程式設計師呼叫)
///
public void Dispose()
{
Dispose(true);
GC.SuppressFinalize(this); //要求系統不要呼叫指定物件的完成項。
}
///
/// 釋放資源(給系統呼叫的)
///
protected virtual void Dispose(bool IsDisposing)
{
if (bDisposed)
return;
if (IsDisposing)
{
// Free any other managed objects here.
}
// Free any unmanaged objects here.
bDisposed = true;
}
///
/// Execute
///
///
public bool Execute()
{
//Create an Excel workbook instance and open it from the predefined location
object misValue = System.Reflection.Missing.Value;
try
{
// Creat Excel Application
xlApp = new Excel.Application();
if (xlApp == null)
{
Console.WriteLine("Excel is not properly installed!!");
return false;
}
// 產生 Workbook 物件，並加入 Application
xlWorkBooks = xlApp.Workbooks;
xlWorkBook = xlWorkBooks.Add();
xlWorkSheets = xlWorkBook.Sheets;
xlWorkSheetPage1 = xlWorkSheets[1];
xlWorkSheetPage2 = xlWorkSheets[2];
xlWorkSheetPage3 = xlWorkSheets[3];
//Delete Sheet 2 & 3
xlWorkSheetPage2.Delete();
xlWorkSheetPage3.Delete();
////////////////////////////////////////////////////////////////////////
// 先判斷檔案是否存在，若存在就先刪除再存檔
if (File.Exists(sSavePath))
File.Delete(sSavePath);
// Save
xlWorkBook.SaveAs(sSavePath);
xlApp.Visible = false;
xlApp.UserControl = false;
// Close the file
xlWorkBook.Close();
// Tell Excel to quit
xlApp.Quit();
return true;
}
catch(Exception ex)
{
Console.WriteLine(ex);
return false;
}
finally
{
// This sample has only one COM object declared.
// For programs with multiple COM objects declared, repeat the following two
// lines of code for each COM object in reverse order of creation.
if (xlWorkSheetPage1 != null)
Marshal.ReleaseComObject(xlWorkSheetPage1);
if (xlWorkSheetPage2 != null)
Marshal.ReleaseComObject(xlWorkSheetPage2);
if (xlWorkSheetPage3 != null)
Marshal.ReleaseComObject(xlWorkSheetPage3);
if (xlWorkSheets != null)
Marshal.ReleaseComObject(xlWorkSheets);
if (xlWorkBook != null)
Marshal.ReleaseComObject(xlWorkBook);
if (xlWorkBooks != null)
Marshal.ReleaseComObject(xlWorkBooks);
if (xlApp != null)
Marshal.ReleaseComObject(xlApp);
// Set each COM Object to null
xlWorkSheetPage1 = null;
xlWorkSheetPage2 = null;
xlWorkSheetPage3 = null;
xlWorkSheets = null;
xlWorkBook = null;
xlWorkBooks = null;
xlApp = null;
// After all of the COM objects have been released and set to null, do the following:
GC.Collect(); // Start .NET CLR Garbage Collection
GC.WaitForPendingFinalizers(); // Wait for Garbage Collection to finish
}
}
}
}
using(CreateExcel ce = new CreateExcel())
{
ce.Execute();
}
- 目前的方法是匯整網路上的各方法，因此程式中 GC 次數呼叫了兩次，事後會針對此校能進行調校
- REF:
- http://blog.darkthread.net/post-2013-05-14-excel-interop-notes.aspx
- https://www.add-in-express.com/creating-addins-blog/2013/11/05/release-excel-com-objects/
- http://blog.darkthread.net/post-2013-05-14-excel-interop-notes.aspx
沒有留言:
張貼留言