匯出 Excel, 執行端需安裝 Excel

1 篇文章 / 0 new
author
匯出 Excel, 執行端需安裝 Excel
有兩種模式,
一為先期連結Excel(會有版本問題), 開發端的電腦必須有安裝需求版本的 excel
另一則為執行時動態連結(無版本問題), 開發端無需安裝 excel

http://support.microsoft.com/kb/302902
http://www.codeproject.com/Articles/10838/How-To-Get-Properties-and-Meth...
using Excel = Microsoft.Office.Interop.Excel
//Early Binding 先期模式, 必須在 專案 ->加入參考 -> COM,選定 指定版本的excel
private void btnPreExcel_Click(object sender, EventArgs e)
{
    Excel.Application xlApp;
    Excel.Workbook xlWorkBook;
    Excel.Worksheet xlWorkSheet;
    object misValue = System.Reflection.Missing.Value;
 
    xlApp = new Excel.ApplicationClass();
    xlWorkBook = xlApp.Workbooks.Add(misValue);
 
    //worksheet = xlWorkBook.Sheets["Sheet1"];
    //worksheet = xlWorkBook.ActiveSheet;
    xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
 
    int i = 0;
    int j = 0;
    for (i = 0; i <= 3; i++)
    {
        for (j = 0; j <= 4; j++)
        {
            xlWorkSheet.Cells[i + 1, j + 1] = i*j;
        }
    }
    xlWorkBook.SaveAs(@"d:\csharp.PreExcel.xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
    xlWorkBook.Close(true, misValue, misValue);
    xlApp.Quit();
 
    releaseObject(xlWorkSheet);
    releaseObject(xlWorkBook);
    releaseObject(xlApp);
 
    MessageBox.Show("Excel d:\\PreExcel.xls 建立完成");
}
private void releaseObject(object obj)
{
    try
    {
        System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
        obj = null;
    }
    catch (Exception ex)
    {
        obj = null;
        MessageBox.Show("Exception Occured while releasing object " + ex.ToString());
    }
    finally
    {
        GC.Collect();
    }
}
//Late Binding 執行時載入(),版本當時機台安裝的Excel為主
private void btnLasExcel_Click(object sender, EventArgs e)
{
    object objApp_Late;
    object objBook_Late;
    object objBooks_Late;
    object objSheets_Late;
    object objSheet_Late;
    object objRange_Late;
    object[] Parameters;
 
    try
    {
        //取得 Excel 物件
        Type objClassType;
        objClassType = Type.GetTypeFromProgID("Excel.Application");
        objApp_Late = Activator.CreateInstance(objClassType);
 
        //取得工作區 workbooks
        objBooks_Late = objApp_Late.GetType().InvokeMember("Workbooks", BindingFlags.GetProperty, null, objApp_Late, null);
 
        //新增 workbook
        objBook_Late = objBooks_Late.GetType().InvokeMember("Add", BindingFlags.InvokeMethod, null, objBooks_Late, null);
 
        //取得當所有工作表 worksheets
        objSheets_Late = objBook_Late.GetType().InvokeMember("Worksheets", BindingFlags.GetProperty, null, objBook_Late, null);
 
        //第一個工作表
        Parameters = new Object[1];
        Parameters[0] = 1;
        objSheet_Late = objSheets_Late.GetType().InvokeMember("Item", BindingFlags.GetProperty, null, objSheets_Late, Parameters);
 
        //取得儲存格 A1
        Parameters = new Object[2];
        Parameters[0] = "A1";
        Parameters[1] = Missing.Value;
        objRange_Late = objSheet_Late.GetType().InvokeMember("Range", BindingFlags.GetProperty, null, objSheet_Late, Parameters);
 
        //寫入 A1
        Parameters = new Object[1];
        Parameters[0] = "Hello, World!";
        objRange_Late.GetType().InvokeMember("Value", BindingFlags.SetProperty, null, objRange_Late, Parameters);
 
        //顯示 Excel
        Parameters = new Object[1];
        Parameters[0] = true;
        objApp_Late.GetType().InvokeMember("Visible", BindingFlags.SetProperty,
            null, objApp_Late, Parameters);
        objApp_Late.GetType().InvokeMember("UserControl", BindingFlags.SetProperty,
            null, objApp_Late, Parameters);
    }
    catch (Exception theException)
    {
        String errorMessage;
        errorMessage = "Error: ";
        errorMessage = String.Concat(errorMessage, theException.Message);
        errorMessage = String.Concat(errorMessage, " Line: ");
        errorMessage = String.Concat(errorMessage, theException.Source);
 
        MessageBox.Show(errorMessage, "Error");
    }
}

使用 先期連結 Microsoft Office XP 需要 Interop 組件 (PIA), http://support.microsoft.com/kb/328912/zh-tw
Free Web Hosting