Startseite Archive nach Kategorien ".Net" (Seite 5)

Kategorie: .Net

[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();
        }
    }
}

[C#] technischen Wert auf 0…100% normieren 

/// <summary>
/// normiert einen technischen Wert abhängig von dessen Minimum und Maximum auf 0...100%
/// </summary>
/// <param name="val">Wert(techn.)</param>
/// <param name="min">Minimum (techn.)</param>
/// <param name="max">Maximum (techn.)</param>
/// <returns>normierter Wert</returns>
public double Normieren(double val, double min, double max)
{
    return (100.0f * ((val - min) / (max - min)));
}

[C#] Gleitkommazahlen richtig vergleichen 

using System;

/// <summary>
/// freeware helper class for little math problems
/// (W) 2014 by admin of codezentrale.6x.to
/// </summary>
public static class MathTools
{
    /// <summary>
    /// Epsilon for float comparision
    /// </summary>
    public const float EPSILON_FLOAT = 1.192093E-07f;
    /// <summary>
    /// Epsilon for double comparision
    /// </summary>
    public const double EPSILON_DOUBLE = 2.22044604925031E-16;

    /// <summary>
    /// Gleitkommavergleich für float
    /// </summary>
    /// <param name="f1"->1. Zahl</param>
    /// <param name="f2"->2. Zahl</param>
    /// <returns>true, wenn gleich</returns>
    public static bool FloatIsEqual(float f1, float f2)
    {
        return (Math.Abs(f1 - f2) < EPSILON_FLOAT);
    }
    /// <summary>
    /// Gleitkommavergleich für double
    /// </summary>
    /// <param name="d1"->1. Zahl</param>
    /// <param name="d2"->2. Zahl</param>
    /// <returns>true, wenn gleich</returns>
    public static bool DoubleIsEqual(double d1, double d2)
    {
        return (Math.Abs(d1 - d2) < EPSILON_DOUBLE);
    }
}

TreeView Node rekursiv über String suchen 

/// <summary->
/// sucht TreeNode rekursiv
/// </summary->
/// <param name=&quot;n&quot;->StartNode</param->
/// <param name=&quot;txt&quot;->Suchtext</param->
/// <returns->gefundene TreeNode oder null</returns->
public TreeNode FindNode(TreeNode n, string txt)
{
    TreeNode r = null;

    foreach (TreeNode tn in n.Nodes)
    {
        if (tn.Text == txt)
        {
            r = tn;
            break;
        }

        r = FindNode(tn, txt);

        if (r != null) break;
    }

    return r;
}

Generische Listenklasse mit Indexer und Enumerator 

using System;
using System.Collections;
using System.Collections.Generic;
/// <summary>
/// generic list class with indexer and enumerator
/// freeware 2011 by admin of codezentrale.6x.to
/// </summary>
/// <typeparam name="T">datatype you want to manage</typeparam>
public class GenericList<T> : IEnumerable, IDisposable
{
    /// <summary>
    /// internal class, with interface for IEnumerable and IEnumerator
    /// </summary>
    private class GenericListEnumerator : IEnumerator
    {
        private int pos = -1;
        private GenericList<T> _t;

        public GenericListEnumerator(GenericList<T> t)
        {
            this._t = t;
        }

        public bool MoveNext()
        {
            if (pos < _t.Count - 1)
            {
                pos++;
                return true;
            }
            else
            {
                return false;
            }
        }

        public void Reset()
        {
            pos = -1;
        }

        public object Current
        {
            get
            {
                try
                {
                    return _t[pos];
                }
                catch (IndexOutOfRangeException)
                {
                    throw new InvalidOperationException();
                }
            }
        }
    }

    private List<T> _ObjectList = new List<T>();
    private bool _bDisposed = false;

    /// <summary>
    /// object counter
    /// </summary>
    public int Count
    {
        get { return _ObjectList.Count; }
    }
    /// <summary>
    /// indexer for direct index based access (e.g. mylist[1])
    /// </summary>
    /// <param name="pos"></param>
    /// <returns></returns>
    public T this[int pos]
    {
        get { return _ObjectList[pos]; }
        set { _ObjectList[pos] = value; }
    }

    public GenericList()
    {
    }

    // destructor, makro for 'protected override void Finalize()'
    ~GenericList()
    {
        this.Dispose(false);
    }

    // public Dispose-method for cleanup
    public void Dispose()
    {
        this.Dispose(true);
    }

    // internal Dispose-method
    private void Dispose(bool bDisposing)
    {
        if (!_bDisposed)
        {
            if (bDisposing)
            {
                // e.g. free managed resources here
            }
        }

        _bDisposed = true;
    }

    /// <summary>
    /// interface function for IEnumerable and IEnumerator
    /// </summary>
    /// <returns></returns>
    public IEnumerator GetEnumerator()
    {
        return new GenericListEnumerator(this);
    }
    /// <summary>
    /// add object to list
    /// </summary>
    /// <param name="obj">your object</param>
    public void Add(T obj)
    {
        _ObjectList.Add(obj);
    }
    /// <summary>
    /// clear list
    /// </summary>
    public void Clear()
    {
        _ObjectList.Clear();
    }
    /// <summary>
    /// swap objects inside list
    /// </summary>
    /// <param name="index1">index number one</param>
    /// <param name="index2">index number one</param>
    public void Swap(int index1, int index2)
    {
        if ((index1 >= 0) && (index2 >= 0) && (index1 < _ObjectList.Count) && (index2 < _ObjectList.Count))
        {
            T temp = _ObjectList[index1];
            _ObjectList[index1] = _ObjectList[index2];
            _ObjectList[index2] = temp;
        }
    }
    /// <summary>
    /// remove object from list
    /// </summary>
    /// <param name="obj"></param>
    public void Remove(T obj)
    {
        _ObjectList.Remove(obj);
    }
}

RichTextBox Text einfärben 

private void InsertColoredText(string txt, Color col)
{
    int pos = rtb.TextLength;
    string s = txt + Environment.NewLine;

    rtbTestStatus.AppendText(s);
    rtbTestStatus.Select(pos, s.Length);
    rtbTestStatus.SelectionColor = col;
    rtbTestStatus.DeselectAll();            
}

StringBuilder verwenden 

using System.Text; 

StringBuilder sb = new StringBuilder();
// Zeichen anhängen
sb.Append("Hans" + Environment.NewLine);
// Zeile anhägen
sb.AppendLine("Hilde");
// Zeichen einfügen
strB.Insert(0, "Udo");
// Zeichen ersetzen
strBuilder = strBuilder.Replace("Hans", "Horst");
// Zugriff auf die gesamte Zeichenfolge
Console.WriteLine(sb.ToString());
// Zeichen löschen
sb.Remove(0, sb.Length);
oder
sb.Length = 0;
oder
sb.Clear(); // ab .NET 4.0

UpDown-Button in einer ListView-Zelle (SubItem) anzeigen

Der UpDown-Button muss zur Designzeit auf dem Listview vorhanden sein!

// Eigenschaften des UpDownButtons setzen
UDBtn.BorderStyle = BorderStyle.None;
UDBtn.Visible = false;
UDBtn.ValueChanged += new System.EventHandler(UDBtn_ValueChanged);

// Ereignis ItemSelectionChanged des ListViews behandeln
private void ListView1_ItemSelectionChanged(object sender, ListViewItemSelectionChangedEventArgs e)
{
    if (e.IsSelected)
    {
        this.SetBtn(e.Item);
        UDBtn.Visible = true;
    }
    else
    {
        UDBtn.Visible = false;
        e.Item.SubItems[ListView1.Columns.Count - 1].Text = UDBtn.Value.ToString();
    }
}

// Funktion setzt den Button in die akt. Zeile und jeweils letzte Spalte (ListView1.Columns.Count - 1) des ListViews
// Werte für X- und Y-Offset müssen ggf. Angepasst werden
private void SetBtn(ListViewItem lvi)
{
    int iLeft = 0;

    if (lvi != null)
    {
        if (lvi.Selected)
        {
            // adjust position values here, to fit correctly
            UDBtn.Top = 38 + lvi.Position.Y - 20;

            for (int iCol = 0; iCol < ListView1.Columns.Count; iCol++)
            {
                iLeft += ListView1.Columns&#91;iCol&#93;.Width;
            }

            UDBtn.Left = iLeft - 39;
            UDBtn.Width = ListView1.Columns&#91;ListView1.Columns.Count - 1&#93;.Width - 1;
            UDBtn.Value = decimal.Parse(lvi.SubItems&#91;ListView1.Columns.Count - 1&#93;.Text);
        }
    }
    else
    {
        UDBtn.Visible = false;
    }
}

// Ereignis ValueChanged des Buttons
private void UDBtn_ValueChanged(object sender, System.EventArgs e)
{
    ListViewItem lvi = (ListView1.SelectedItems.Count -> 0) ? lvi = ListView1.SelectedItems[0] : null;

    if (lvi != null)
    {
        lvi.SubItems[ListView1.Columns.Count - 1].Text = UDBtn.Value.ToString();
    }
}

Verhindern, dass TreeNode bei Rechtsklick deselektiert wird

Oft kommt es vor, dass man in einem TreeView eine TreeNode per Rechtsklick selektiert, zum Beispiel beim gleichzeitigen Anzeigen eines ContextMenueStrips. Dabei “schnappt” meist die Auswahl wieder auf die zuvor ausgewählte TreeNode zurück und das falsche Element ist gewählt. Um das zu verhindern muss man folgenden Code implementieren:

private void TreeView1_MouseUp(object sender, MouseEventArgs e)
{
    if (e.Button == MouseButtons.Right)
    {
        TreeNode tnd = TreeView1.GetNodeAt(e.X, e.Y);
        if (tnd != null)
        {
            TreeView1.SelectedNode = tnd;
        }
    }
}

Stringliterale in Hexcodes wandeln 

/// Beim Speichern von Strings in Textdateien möchte man manchmal, dass Literale (Zeilenumbrüche, Tabs usw.)
/// nicht interpretiert werden, sondern als hexadezimale Codierungen gespeichert werden.
/// die statische Klasse wandelt alle ASCII-Steuerzeichen in einem String mit ASCIICode < 32 in die entsprechenden hexadezimalen Pendants um.
using System;

/// <summary>
/// freeware helper class for converting string formats
/// (W) 2011 by admin of codezentrale.6x.to
/// </summary>
public static class StringConverter
{
    /// <summary>
    /// hex replace of literals
    /// </summary>
    /// <param name="path">a string</param>
    /// <returns>string with replaced literals</returns>
    public static string ReplaceLiterals(string inputstring)
    {
        string output = string.Empty;
        
        foreach (char t in inputstring)
        {
            int ascii = Convert.ToInt32(t);
            output += (ascii < 32) ? string.Format("&#x{0:X02};", ascii) : t.ToString();
        }

        return output;
    }
}

Beispiel:

string sInput = "Hallo" + Environment.NewLine + "Welt!"

// "Hallo Welt!"
string sOutPut = StringConverter.ReplaceLiterals(sInput));