[C#] Excel Interop

Möchte man von .NET aus auf Excel zugreifen kann man sich sogenannter Interop-Mechanismen von .NET bedienen. Dazu müssen bestimmte Office-Bibliotheken eingebunden werden.
Dabei ist folgendes zu beachten:

• Versionsnummern aller eingebundener Office-DLLs beachten, d.h. die Versionen müssen gleich sein
• für die Bibliotheksverweise im Projektbaum unter Verweise->Rechtsklick->Eigenschaften->Lokale Kopie = true setzen
• ggf. auch die Redistributable Primary Interop Assemblies (PIA) auf dem Zielsystem nachinstallieren
• Wird mit einer englischen Version von Excel gearbeitet, darf das lokale Gebietsschema nicht verschieden von Englisch konfiguriert sein. Andernfalls muss das lokale Gebietsschema auf Englisch umgestellt werden: Link
• ab .NET 4.0 wurde mit dem dynamic-Datentyp eine Vereinfachung des Zugriffes per Interop ermöglicht (s.u.).

// Verweise->Verweis hinzufügen...->.NET->Microsoft.Office.Interop.Excel
using Excel = Microsoft.Office.Interop.Excel;
// Verweise->Verweis hinzufügen...->COM->Microsoft Office Excel 12.0 Object Library
using Microsoft.Office.Core; // für MsoTriState

public void ExcelExample()
{
    Excel.Application excel = null;
    Excel.Workbook book = null;
    Excel.Worksheet sheet = null;
    Excel.Worksheet new_sheet = null;
    
    try
    {
        // open excel
        excel = new Excel.Application();
        // set visible
        excel.Visible = true;
        // no update
        excel.ScreenUpdating = false;
        // display no alerts
        excel.DisplayAlerts = false;
        // always one sheet in new workbook
        excel.SheetsInNewWorkbook = 1;

        // add new workbook
        book = excel.Workbooks.Add(Missing.Value);

        // get current (first) sheet of workbook
        sheet = book.Worksheets[1] as Excel.Worksheet;
        // set sheet name
        sheet.Name = "Testresult";
        
        // merge cell range
        sheet.get_Range("A1:D1", Type.Missing).Merge(Type.Missing);
        
        // colwidth for column A
        // Values are not in pixel!
        (sheet.Columns["A", Type.Missing] as Excel.Range).ColumnWidth = 25.0;
        
        // text (row, column)
        sheet.Cells[1, 1] = "text";
        
        // bold range
        sheet.get_Range("A1:A7", Type.Missing).Font.Bold = true;
        
        // border around cell A1
        sheet.get_Range("A1", Type.Missing).BorderAround(Type.Missing, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, Type.Missing);
        
        // border around range A1:B1
        sheet.get_Range("A1:B1", Type.Missing).BorderAround(Type.Missing, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, Type.Missing);
        
        // font color for cell A1
        // needs always Colortranslator!
        sheet.get_Range("A1", Type.Missing).Font.Color = ColorTranslator.ToOle(Color.Green);
        
        // new worksheet after first (current) worksheet
        new_sheet = book.Worksheets.Add(Type.Missing, book.Worksheets[1], Type.Missing, Type.Missing) as Excel.Worksheet;
        new_sheet.Name = "sheet1";
        
        // insert picture upper left to new sheet
        // first version with shapes, without STA-Thread problems
        new_sheet.Shapes.AddPicture(@"c:\temp\test.bmp", MsoTriState.msoFalse, MsoTriState.msoCTrue, 0.0f, 0.0f, -1.0f, -1.0f);
        
        // second version without the need of Microsoft.Office.Core
        // but STA-Thread-problems
        Clipboard.SetDataObject(Image.FromFile(@"c:\temp\test.bmp"), true);
        new_sheet.Paste(new_sheet.Cells[1, 1], Type.Missing);
        
        // save Excel-file
        // try different Excel.XlFileFormat
        book.SaveAs(@"c:\temp\test1", Excel.XlFileFormat.xlWorkbookNormal, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);

        // path + name + extension of saved ExcelWorkbook
        string fullfilename = book.FullName;
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.Message + Environment.NewLine + ex.StackTrace);
    }
    finally
    {
        // release new_sheet
        if (new_sheet != null)
        {
            Marshal.ReleaseComObject(new_sheet);
            new_sheet = null;
        }

        // release first sheet
        if (sheet != null)
        {
            Marshal.ReleaseComObject(sheet);
            sheet = null;
        }

        // release workbook
        if (book != null)
        {
            try
            {
                book.Close(Type.Missing, Type.Missing, Type.Missing);
            }
            catch (Exception)
            {
            }

            Marshal.ReleaseComObject(book);
            book = null;
        }

        // release excel
        if (excel != null)
        {
            try
            {
                excel.Workbooks.Close();
            }
            catch (Exception)
            {
            }

            try
            {
                excel.Quit();
            }
            catch (Exception)
            {
            }

            Marshal.ReleaseComObject(excel);
            excel = null;
        }

        GC.Collect();
    }
}

Ab .NET 4.0 kann der Code durch den dynamic-Datentyp vereinfacht werden. Die Typecasts und überflüssigen Type.Missing fehlen und die Parametrierung der COM-Funktionen kann nun direkt per Bezeichner: erfolgen:

// ab .NET 4.0
// Verweise->Verweis hinzufügen...->.NET->Microsoft.Office.Interop.Excel
using Excel = Microsoft.Office.Interop.Excel;
// Verweise->Verweis hinzufügen...->COM->Microsoft Office Excel 12.0 Object Library
using Microsoft.Office.Core; // für MsoTriState

private void ExcelExample(string savename)
{
    Excel.Application excel = null;
    Excel.Workbook book = null;
    Excel.Worksheet sheet = null;
    Excel.Worksheet new_sheet = null;

    try
    {
        // open excel
        excel = new Excel.Application();

        // set visible
        excel.Visible = true;
        // no update
        excel.ScreenUpdating = false;
        // display no alerts
        excel.DisplayAlerts = false;
        // always one sheet in new workbook
        excel.SheetsInNewWorkbook = 1;

        // add new workbook
        book = excel.Workbooks.Add();

        // get current (first) sheet of workbook
        sheet = book.Worksheets[1];
        // set sheet name
        sheet.Name = "Testresult";

        // merge cell range
        sheet.get_Range("A1:D1").Merge();

        // colwidth for column A
        // Values are not in pixel!
        sheet.Columns["A"].ColumnWidth = 25.0;

        // text (row, column)
        sheet.Cells[1, 1] = "Testeintrag";

        // bold range
        sheet.get_Range("A1:D1").Font.Bold = true;

        // border around cell A1:D1
        sheet.get_Range("A1:D1").BorderAround(Weight: Excel.XlBorderWeight.xlThin, ColorIndex: Excel.XlColorIndex.xlColorIndexAutomatic);

        // border around range A1:B1
        sheet.get_Range("A2:B2").BorderAround(Weight: Excel.XlBorderWeight.xlThin, ColorIndex: Excel.XlColorIndex.xlColorIndexAutomatic);

        // font color for cell A1
        // needs always Colortranslator!
        sheet.get_Range("A1").Font.Color = ColorTranslator.ToOle(Color.Green);

        // new worksheet after first (current) worksheet
        new_sheet = book.Worksheets.Add(After: book.Worksheets[1]);
        new_sheet.Name = "new_sheet";

        // insert picture upper left to new sheet
        // first version with shapes, without STA-Thread problems
        new_sheet.Shapes.AddPicture(@"c:\temp\test.bmp", MsoTriState.msoFalse, MsoTriState.msoCTrue, 0.0f, 0.0f, -1.0f, -1.0f);
        
        // second version without the need of Microsoft.Office.Core
        // but STA-Thread-problems
        Clipboard.SetDataObject(Image.FromFile(@"c:\temp\test.bmp"), true);
        new_sheet.Paste(new_sheet.Cells[1, 1]);

        // save Excel-file
        // try different Excel.XlFileFormat
        book.SaveAs(Filename: @"c:\temp\test1", FileFormat:  Excel.XlFileFormat.xlWorkbookNormal, AccessMode: Excel.XlSaveAsAccessMode.xlNoChange);

        fullfilename = book.FullName;
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.Message + Environment.NewLine + ex.StackTrace);
    }
    finally
    {
        // release workbook
        if (book != null)
        {
            book.Close();
        }

        // release excel
        if (excel != null)
        {
            excel.Quit();
        }
    }
}