Excel et CSharp

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 :

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é.

• [1]
• [2]

Créer un nouveau classeur

// 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

// Ouvre un nouveau processus Excel. Application excel = new Application(); 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

var worksheet = (Worksheet)workbook.Sheets[0]; 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

// 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.

// 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

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.

// 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

// 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

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

// 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 :

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