Hi @Alien
To accsess Excel in Dynamo, you need use late binding to access the Excel API. This is because Dynamo is already accessing the Excel API. Challenges may arise when trying to access the API of another package, especially if Dynamo has already established a connection to it. This issue was solve by @mzjensen.
The below code to accses Excel by using late binding
public static string Set_Value_at_Range(string workbookPath,string sheetName, string Range, string Value,bool Visible, bool save,bool Close)
{
try
{
// Specify the file path and name of the Excel file
string filePath = workbookPath;
// Use late binding to create an instance of the Excel application
Type excelType = Type.GetTypeFromProgID("Excel.Application");
object excel = Activator.CreateInstance(excelType);
// Use late binding to open the Excel file
object workbooks = excelType.InvokeMember("Workbooks", BindingFlags.GetProperty, null, excel, null);
object workbook = workbooks.GetType().InvokeMember("Open", BindingFlags.InvokeMethod, null, workbooks, new object[] { filePath });
// Use late binding to access the first worksheet
object sheets = workbook.GetType().InvokeMember("Sheets", BindingFlags.GetProperty, null, workbook, null);
object sheet = sheets.GetType().InvokeMember("Item", BindingFlags.GetProperty, null, sheets, new object[] { sheetName });
if (Visible)
{
excelType.InvokeMember("Visible", BindingFlags.SetProperty, null, excel, new object[] { true });
}
// Use late binding to add a value to cell "C3"
object range = sheet.GetType().InvokeMember("Range", BindingFlags.GetProperty, null, sheet, new object[] { Range });
range.GetType().InvokeMember("Value", BindingFlags.SetProperty, null, range, new object[] { Value });
if (save)
{
// Use late binding to save the changes to the Excel file
workbook.GetType().InvokeMember("Save", BindingFlags.InvokeMethod, null, workbook, null);
}
if (Close)
{
// Use late binding to close the Excel file and quit the application
workbook.GetType().InvokeMember("Close", BindingFlags.InvokeMethod, null, workbook, null);
excelType.InvokeMember("Quit", BindingFlags.InvokeMethod, null, excel, null);
}
return "Done";
}
catch (Exception)
{
return "fail";
}
}
}
i use it in my pak. BriMohareb_XX to accsess excel and create some node like Run Macro from dynamo.
this is not necessarily the best option, but here is an example with OleDbConnection
it works fine with Sandbox and Civil, for Revit a workaround is necessary (need Microsoft Access Runtime) the advantage is that by default it directly returns a DataSet or DataTable
C# Code
using System;
using System.IO;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.Linq;
using System.Runtime.InteropServices;
using System.Reflection;
namespace ExcelUtils
{
public class MyClass
{
/// <summary>
/// Workaround for Revit need to convert xlsx file to xls
/// </summary>
private static (string, bool) ConvertToXLS(string filepath)
{
string hostAppName = System.Diagnostics.Process.GetCurrentProcess().ProcessName;
string pf_path = System.Environment.GetFolderPath(System.Environment.SpecialFolder.ProgramFiles);
bool is_access_install = System.IO.Directory.GetFiles(pf_path + "\\Microsoft Office", "*MSACCESS.EXE", System.IO.SearchOption.AllDirectories).Length > 0;
if (!is_access_install && hostAppName == "Revit")
{
throw new Exception("Microsoft Access Runtime is not Installed\nhttps://www.microsoft.com/fr-fr/download/details.aspx?id=39358\n");
}
if (filepath.EndsWith("xlsx") && hostAppName == "Revit")
{
// Use late binding to create an instance of the Excel application
object excel = Activator.CreateInstance(Type.GetTypeFromProgID("Excel.Application"));
excel.GetType().InvokeMember("Visible", BindingFlags.SetProperty, null, excel, new object[] { false });
excel.GetType().InvokeMember("DisplayAlerts", BindingFlags.SetProperty, null, excel, new object[] { false });
object workbooks = excel.GetType().InvokeMember("Workbooks", BindingFlags.GetProperty, null, excel, null);
object wb = workbooks.GetType().InvokeMember("Open", BindingFlags.InvokeMethod, null, workbooks, new object[] { filepath });
// convert xlsx to xls extension
string newpath = filepath.Remove(filepath.Length - 1);
// Use late binding to saveAs to xls (56 == XlFileFormat.xlExcel8)
wb.GetType().InvokeMember("SaveAs", BindingFlags.InvokeMethod, null, wb, new object[] { newpath, 56, null, null, null, null, null, null, null, null});
// Use late binding to close the Excel file and quit the application
wb.GetType().InvokeMember("Close", BindingFlags.InvokeMethod, null, wb, null);
excel.GetType().InvokeMember("Quit", BindingFlags.InvokeMethod, null, excel, null);
Marshal.ReleaseComObject(wb);
wb = null;
Marshal.ReleaseComObject(excel);
excel = null;
return (newpath, true);
}
else
{
return (filepath, false);
}
}
private static List<object> ConvertToArray(System.Data.DataSet dtSet)
{
List<object> lstout = new List<object>();
foreach (System.Data.DataTable dt in dtSet.Tables)
{
object[] objectArray = dt.Rows.Cast<DataRow>().AsEnumerable().Select(r => r.ItemArray.Select(c => c == DBNull.Value ? "" : c).ToArray()).ToArray();
lstout.Add(objectArray);
}
return lstout;
}
public static dynamic ReadXlsx(string fileName, bool returnArray)
{
(string fixFileName, bool xlsCreated) = ConvertToXLS(fileName);
var connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fixFileName + ";Extended Properties=\"Excel 12.0;IMEX=1;HDR=NO;TypeGuessRows=0;ImportMixedTypes=Text\"";
using (var conn = new OleDbConnection(connectionString))
{
conn.Open();
var sheets = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = "SELECT * FROM [" + sheets.Rows[0]["TABLE_NAME"].ToString() + "] ";
var adapter = new OleDbDataAdapter(cmd);
var ds = new DataSet();
adapter.Fill(ds);
if (!returnArray)
{
return ds;
}
else
{
return ConvertToArray(ds);
}
}
}
}
}
}
