Excel et CSharp

De Banane Atomic
Révision datée du 16 août 2018 à 08:19 par Nicolas (discussion | contributions)
(diff) ← Version précédente | Voir la version actuelle (diff) | Version suivante → (diff)
Aller à la navigationAller à la recherche

C# 4

Avec le compilateur C# 4, methods returning object are treated as if they return dynamic in order to simulate the VBA semantics of COM Variants.
Because the return value of worksheet.Cells[...] is Object, worksheet.get_Range is dispatched dynamically, and the dynamic COM binder does not support the get_Range syntax exposed in C# before indexed properties were supported.
Il faut donc utiliser une nouvelle syntaxe :

Csharp.svg
worksheet.get_Range(...);  // devient
worksheet.Range[...];

range.set_Value(Missing.Value, ...);  // devient
range.Value2 = ...

Sinon erreur : System.__ComObject ne contient pas de définition pour get_Range.

Une autre solution consiste à ne pas incorporer les types d'interopérabilité :
Propriétés d'Interop.Excel → Incorporer les types d'interopérabilité = False
VS 2010 convertit automatiquement l'élément Object retourné en type dynamic si l'assembly est référencé par l'option de compilateur /link ou, de façon équivalente, si la propriété Incorporer les types d'interopérabilité Excel a la valeur true. True est la valeur par défaut de cette propriété.

Créer un nouveau classeur

Csharp.svg
// Ouvre un nouveau processus Excel.
Application excel = new ApplicationClass();
// Add utilise XlWBATemplate.xlWBATWorksheet par défaut
Workbook workbook = excel.Workbooks.Add(Type.Missing);

Le paramètre attendu de la méthode Add est un élément de l'énumération XlWBATemplate

Ouvrir un classeur Excel

Csharp.svg
// Ouvre un nouveau processus Excel.
Application excel = new ApplicationClass();
Workbook workbook = excel.Workbooks.Open(
    filePath,     // Chemin vers le classeur excel
    2,            // UpdateLinks 1=User choice(default), 2=Never update, 3=Always update
    false,        // ReadOnly
    Type.Missing, // Format(delimiter character) 1=Tabs
    Type.Missing, // Password pour la lecture
    Type.Missing, // Password pour l'écriture
    Type.Missing, // IgnoreReadOnlyRecommended
    Type.Missing, // Origin XlPlatform.xlWindows
    Type.Missing, // Delimiter, à utiliser avec Format=6(Custom character)
    Type.Missing, // Editable 
                  // (pour les Excel 4.0 add-in et les templates) false(default)
    true,         // Notify false(default)
    Type.Missing, // Converter
    Type.Missing, // AddToMru (ajout à la liste des fichiers récemment ouverts)
                  // false(default)
    Type.Missing, // Local, true enregistre les fichiers par rapport 
                  // à la langue d'Excel, false (par défaut) enregistre 
                  // les fichiers par rapport à la langue de VBA (en-us).
    Type.Missing);// CorruptLoad xlNormalLoad(default)

Lire et écrire dans les cellules

Csharp.svg
Range cellA1 = worksheet.get_Range("A1", Type.Missing);
Range cellA1 = worksheet.get_Range(worksheet.Cells[1, 1], Type.Missing);
Range cellA1 = worksheet.get_Range(1.ToExcelColumnFormat() + "1"], Type.Missing);

object cellA1Value = cellA1.get_Value(Type.Missing);

cellA1.set_Value(Type.Missing, 9);

// écriture d'un tableau d'éléments
var matrix = new object[3, 4] { { 1, 2, 3, 4 }, { null, 2, null, null }, { "1", "2", 3, "4" } };

var range = worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[matrix.GetLength(0), matrix.GetLength(1)]);
range.Value2 = matrix;

Conversion numéro de colonne vers lettre de colonne

Csharp.svg
// Méthode d'extension numéro de colonne -> lettre de colonne
public static string ToExcelColumnFormat(this int integer)
{
    string columnString = string.Empty;
    while (integer > 0)
    {
        int currentLetterNumber = (integer - 1) % 26;
        char currentLetter = (char)(currentLetterNumber + 65);
        columnString = string.Format("{0}{1}", currentLetter, columnString);
        integer = (integer - (currentLetterNumber + 1)) / 26;
    }
    return columnString;
}

Sauvegarder un classeur Excel

Référence COM : Microsoft Excel Object Library ou Référence .NET Microsoft.Office.Interop.Excel.

Csharp.svg
// Si le classeur existe déjà on a juste besoin de le sauvegarder
workbook.Save();

// S'il n'existe pas encore ou que l'on veut le changer de nom ou d'emplacement
workbook.SaveAs(
    path,                          // Chemin vers le classeur excel
    XlFileFormat.xlWorkbookNormal, // FileFormat
    Type.Missing,                  // Password pour la lecture
    Type.Missing,                  // Password pour l'écriture
    false,                         // ReadOnlyRecommended
    false,                         // CreateBackup
    XlSaveAsAccessMode.xlNoChange, // AccessMode
    XlSaveConflictResolution.xlUserResolution, // ConflictResolution
    true,                          // AddToMru (ajout à la liste des fichiers récemment ouverts)
                                   // default=false
    Type.Missing,                  // TextCodepage
    Type.Missing,                  // TextVisualLayout
    true);                         // Local, true enregistre les fichiers par rapport 
                                   // à la langue d'Excel, false (par défaut) enregistre 
                                   // les fichiers par rapport à la langue de VBA (en-us).

Libérer les objets COM et fermer Excel

Csharp.svg
if (workbook != null) workbook.Close(Type.Missing, Type.Missing, Type.Missing);
if (excel != null) excel.Quit();

ComObject.ReleaseComObject(worksheet);
ComObject.ReleaseComObject(excel);

GC.Collect(); // Forcer la collecte. Libération du processus Excel.

// Méthode custom pour libérer les objets COM
public static void ReleaseComObject(object comObject)
{
    if (comObject == null) return;

    try
    {
        while (Marshal.ReleaseComObject(comObject) > 0) ;
    }
    finally
    {
        comObject = null;
    }
}


// Ancienne méthode
Application excel = new Application(); // Lancement du processus Excel
Workbook wb = excel.Workbooks.Add(Missing.Value);
Worksheet ws = wb.Worksheets.Add(Missing.Value, Missing.Value, 
                                 Missing.Value, Missing.Value);
Range r = ws.get_Range((Range)ws.Cells[1, 4], (Range)ws.Cells[4, 4]);
r.set_Value(Missing.Value, "0");
// Fermeture du processus Workbook et de l'application
wb.Close();
excel.Quit();
// Libération des objets COM
Marshal.ReleaseComObject(r);
Marshal.ReleaseComObject(ws); // Inutil de libérer wb
Marshal.ReleaseComObject(excel);
// Forcer la collecte. Libération du processus Excel.
GC.Collect();

Liaisons entre fichiers Excel

Les liaison vers les fichiers du dossier courant ou de sous-dossiers sont enregistrés dans Excel en chemin relatif.
Les liaison vers les fichiers de dossiers appartenant à d'autres branches sont enregistrés dans Excel en chemin absolu.
Excel renvoie les liaison en chemin absolu, pour les chemins relatifs il complète avec le chemin du classeur ouvert.

Csharp.svg
// Récupération et modification de liaisons.
Array xlLinks = (Array)workbook.LinkSources(XlLink.xlExcelLinks);
if (xlLinks != null)
{
    for (int i = 1; i <= xlLinks.Length; i++) // GetValue commence à 1.
    {
        // Récupération du chemin de la liaison.
        string linkPath = (string)xlLinks.GetValue(i);
        // Modification de la liaison.
        workbook.ChangeLink(linkPath, newLinkPath, XlLinkType.xlLinkTypeExcelLinks);
    }
    workbook.Save();
}

// Liaison entre la cellule A1 et la cellule B1 de la feuille Feuille du
// fichier FichierExcel du répertoire C:\Dossier.
worksheet.get_Range("A1", Missing.Value).Formula = "='C:\\Dossier\\[FichierExcel.xls]Feuille'!$B$1";

Liaisons entre cellules

Csharp.svg
// Liaison entre les cellules A1-5 et la cellule B1.
worksheet.get_Range("A1", "A5").Formula = "=$B$1";

// Liaison entre les cellules A1-5 et les cellules B1-5.
worksheet.get_Range("A1", "A5").Formula = "=B1";

Couleurs

Csharp.svg
public void ColorRange(Range range, string color, bool background)
{
    var oleColor = ColorTranslator.ToOle(Color.FromName(color));

    if (background)
    {
        range.Interior.Color = oleColor;
    }
    else
    {
        range.Font.Color = oleColor;
    }
}

public void ColorRow(int rowIndex, string color, bool background)
{
    ColorRange(
        Worksheet.get_Range(columnIndex.ToExcelColumnFormat() + "1", Type.Missing).EntireRow, 
            color, background);
}

public void ColorColumn(int columnIndex, string color, bool background)
{
    ColorRange(
        Worksheet.get_Range(columnIndex.ToExcelColumnFormat() + "1", Type.Missing).EntireColumn, 
            color, background);
}

Figer les lignes et les colonnes

Csharp.svg
// Gèle les 10 premières lignes
worksheet.Application.ActiveWindow.SplitRow = 10;

// Gèle les 5 premières colonnes
worksheet.Application.ActiveWindow.SplitColumn = 5;

worksheet.Application.ActiveWindow.FreezePanes = true;

Old format or invalid type library

Quand un programme fait un appel COM à Excel, il passe la culture locale du système. Cela pose problème avec l'édition US d'Excel qui n'accepte que les appels COM en culture US.
Le message d'erreur « Old format or invalid type library » est généré si depuis un ordinateur en culture autre qu'US on fais un appel COM à un Excel en édition US.
Solutions :

  • Installer le MUI pack pour Excel.
  • Installer Excel non US (de préférence celui correspondant à la culture de l'ordinateur).
  • Changer la culture de l'ordinateur en US.
  • Forcer la culture US lors des appels COM :
Csharp.svg
public void SetPropertyInEnUs(object target, string propertyName, object value)
{
    target.GetType().InvokeMember(propertyName,
        System.Reflection.BindingFlags.SetProperty |
        System.Reflection.BindingFlags.Public |
        System.Reflection.BindingFlags.Instance,
        null, target, new object[] { value }, new System.Globalization.CultureInfo(1033));
}

public object GetPropertyInEnUs(object target, string propertyName)
{
    return target.GetType().InvokeMember(propertyName,
        System.Reflection.BindingFlags.GetProperty |
        System.Reflection.BindingFlags.Public |
        System.Reflection.BindingFlags.Instance,
        null, target, null, new System.Globalization.CultureInfo(1033));
}

public object InvokeMethodInEnUs(object target, string methodName, params object[] args)
{
    return target.GetType().InvokeMember(methodName,
        System.Reflection.BindingFlags.InvokeMethod |
        System.Reflection.BindingFlags.Public |
        System.Reflection.BindingFlags.Instance,
        null, target, args, new System.Globalization.CultureInfo(1033));
}

Liste des méthodes et propriétés qui ont besoin de forcer la culture pour fonctionner :

  • Application
    • DisplayAlerts
  • Workbooks
    • Open()
    • Save()
    • LinkSources()
  • Range
    • Value2