Reading from and writing to Excel file.

Reading from and writing to Excel file.

haluk_uzuner
Explorer Explorer
1,536 Views
8 Replies
Message 1 of 9

Reading from and writing to Excel file.

haluk_uzuner
Explorer
Explorer

Hi,
This topic is disccussd in this forum before. But I coudn'find a working example.

I want to read some data from excel file. I have tried "Microsoft.Office.Interop.Excel" and ClosedXML references. Both are didn't work. Either in a macro or in an addin. While reading the file it gives error. The picture below is from a macro. But when I tried the same code in stand alone windows application it works perfectly. But it revit it returns error.

Is tehere a sust limitation on excel files  in Revit api? (I could read txt file in revit macro.)

 

Brief code is below.

using Excel = Microsoft.Office.Interop.Excel;


var excel = new Excel.Application(); //This line retuern error.
var workbook = excel.Workbooks.Open(excelPath);
var worksheet = workbook.Worksheets[1];

 

haluk_uzuner_0-1730310741778.png

 

0 Likes
Accepted solutions (1)
1,537 Views
8 Replies
Replies (8)
Message 2 of 9

studio-a-int
Advocate
Advocate

We use OpenXml day in/day out to read/write to .xlsx and it works with no issues.

Here is the complete sample code from Microsoft to parse and read a large spreadsheet document:

 

https://learn.microsoft.com/en-us/office/open-xml/spreadsheet/how-to-parse-and-read-a-large-spreadsh...

 

Install DocumentFormat.OpenXml via NuGet and then:

 

using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;

0 Likes
Message 3 of 9

halukuzuner
Advocate
Advocate
Thanks studio-a-int,
Does it requre MS Office to be installed?
0 Likes
Message 4 of 9

studio-a-int
Advocate
Advocate

No, MS Office doesn't need to be installed.

 

You will be using only the open source package developed by Microsoft.

 

https://learn.microsoft.com/en-us/office/open-xml/open-xml-sdk

 

which has an MIT License:

 

https://licenses.nuget.org/MIT

 

If you/your company wants, it can use LibreOffice or OpenOffice to perform the same tasks like MS Office.

 

https://www.libreoffice.org/

 

https://www.openoffice.org/

 

If you distribute your apps as single .dll, you will need Fody (or similar) for weaving your .net assemblies.

 

0 Likes
Message 5 of 9

halukuzuner
Advocate
Advocate

Hi @studio-a-int,

I used sample SAX approach code in this page .

https://learn.microsoft.com/en-us/office/open-xml/spreadsheet/how-to-parse-and-read-a-large-spreadsh...

 

// The SAX approach.
static void ReadExcelFileSAX(string fileName)
{
    using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(fileName, false))
    {

        WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart ?? spreadsheetDocument.AddWorkbookPart();
        WorksheetPart worksheetPart = workbookPart.WorksheetParts.First();

        OpenXmlReader reader = OpenXmlReader.Create(worksheetPart);
        string text;
        while (reader.Read())
        {
            if (reader.ElementType == typeof(CellValue))
            {
                text = reader.GetText();
                Console.Write(text + " ");
            }
        }


        Console.WriteLine();
        Console.ReadKey();
    }
}

I tried a sample macro in Revit 2025. The code is below.
using Autodesk.Revit.DB;
using Autodesk.Revit.DB.Mechanical;
using Autodesk.Revit.UI;
using Autodesk.Revit.UI.Selection;

using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using System;
using System.Linq;

namespace HelloWorld
{
    [Autodesk.Revit.Attributes.Transaction(Autodesk.Revit.Attributes.TransactionMode.Manual)]
    [Autodesk.Revit.DB.Macros.AddInId("972CCC9D-F011-4430-B5C5-F757BFE97FD4")]
    public partial class ThisApplication
    {
    private void Module_Startup(object? sender, EventArgs e)
    {
        SampleMacro();
    }

    private void Module_Shutdown(object? sender, EventArgs e)
    {
    }

    // Comment out below method to add a sample Macro
    public void SampleMacro()
    {
        string fileName = @"C:\Worksets.xlsx";

        using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(fileName, false))
        {

            WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart ?? spreadsheetDocument.AddWorkbookPart();
            WorksheetPart worksheetPart = workbookPart.WorksheetParts.First();

         OpenXmlReader reader = OpenXmlReader.Create(worksheetPart);    //  This line prevents Macro manager to run macro.
        //     string text;
        //     while (reader.Read())
        //     {
        //         if (reader.ElementType == typeof(CellValue))
        //         {
        //             text = reader.GetText();
        //             //Console.Write(text + " ");
        //             TaskDialog.Show("Hello World!", text + " ");
        //         }
        //     TaskDialog.Show("Hello World!", "Exit the function SampleMacro()");
        //     }
         }
    }
    }
}
 
When I uncomment the line "
OpenXmlReader reader = OpenXmlReader.Create(worksheetPart);    //  This line prevents Macro manager to run macro."
 
It prevent macro manager to run macro. Waht could be the problem?
halukuzuner_0-1730363433704.png

 

0 Likes
Message 6 of 9

reylorente1
Collaborator
Collaborator
Accepted solution

Hola,

Check out this video:

https://www.youtube.com/watch?v=GpFilBCYimI&ab_channel=Simple_Coding

 Adding Microsoft.Office.Interop.Excel to VS Code - YouTube

Regarding ClosedXML, it gives me an error in the macro, but in VS 2022, I found that in Autocad 2025 (C:Program FilesAutodeskAutoCAD 2025) there is a ClosedXML , I loaded it with Dependency in VS and it worked for me.
I hope it helps you.
Here I made a small example:

private void CreatetExcel(Document doc)
{
    
    string excelFilePath = @"E:\Rey\Prueba.xlsx";

    
    FilteredElementCollector colWall = new FilteredElementCollector(doc)
        .WhereElementIsNotElementType()
        .OfCategory(BuiltInCategory.INVALID)
        .OfClass(typeof(Wall));

    string sheetName = "Wall";

    // Crear el libro de Excel  
    XLWorkbook book = new XLWorkbook();

    // Crear una Hoja de Excel  
    book.AddWorksheet(sheetName);

    // Obtener la Hoja de excel recién creada  
    var ws = book.Worksheet(sheetName);

    // Crear el encabezado  
    ws.Cell(1, 1).Value = "Nombre";
    ws.Cell(1, 2).Value = "ID";


    // Estilo del encabezado: Negrita y centrado  
    ws.Row(1).Style.Font.Bold = true;
    ws.Row(1).CellsUsed().Style.Alignment.Horizontal = 
          XLAlignmentHorizontalValues.Center;

    // Inicializar la fila para datos  
    int fila = 2;

    // Recorrer los elementos filtrados  
    foreach (Wall wall in colWall)
    {
        // Obtener nombre e ID  
        string nombre = wall.Name;
        string id = wall.Id.ToString();

        // Escribir en las celdas  
        ws.Cell(fila, 1).Value = nombre;
        ws.Cell(fila, 2).Value = id;

        // Incrementar la fila para la próxima iteración  
        fila++;
    }

    // Ajustar el contenido de las columnas  
    ws.Columns().AdjustToContents();


    // Guardar el archivo de Excel  
    book.SaveAs(excelFilePath);

    // Abrir el archivo en Excel  
    System.Diagnostics.Process.Start(new 
   System.Diagnostics.ProcessStartInfo()
    {
        FileName = excelFilePath,
        UseShellExecute = true 
    });
}
Message 7 of 9

halukuzuner
Advocate
Advocate

@reylorente1  tank you for your answer.
I tried your code with "C:\Program Files\Autodesk\AutoCAD 2025\ClosedXML.dll" . It worked. It is weird that it doesn't work with closedxml assembly from other sources. I tried from nuget and other sources. None of them worked.

0 Likes
Message 8 of 9

reylorente1
Collaborator
Collaborator

You are right. Why?. I do not know.
I switched to Epplus.Me it works in the macro and also in VS
I installed the latest version, but the Dimension property doesn't recognize it.
Here's an example:

 

 

using Autodesk.Revit.DB;
using Autodesk.Revit.UI;
using OfficeOpenXml;//Install Epplus 7.1.0
public void ReadExcel()
      {


      	//Document doc = this.ActiveUIDocument.Document;

         try
         {
            string excelFilePath = @"E:\Rey\Prueba.xlsx";

            FileInfo existingFile = new FileInfo(excelFilePath);
            using (ExcelPackage excelPackage = new ExcelPackage(existingFile))
            {
               ExcelWorksheet firstWorksheet = excelPackage.Workbook.Worksheets.First();

               // Utiliza la propiedad Dimension para obtener el rango de celdas con datos  
               var dimension = firstWorksheet.Dimension;

               if (dimension != null) // Verificar si la dimensión tiene datos  
               {
                  int colCount = dimension.End.Column;
                  int rowCount = dimension.End.Row;

                  // Inicializar el mensaje  
                  //message = "Nombres e ID:\n\n";

                  // Hacemos un foreachloop anidado:  
                  string datos = "Los valores de cada fila son: " + "\n\n";
                  foreach (int i in Enumerable.Range(2, rowCount - 1)) // Comenzar desde la fila 2  
                  {
                     // Obtener valores por fila  
                     string nombre = firstWorksheet.Cells[i, 1].Value?.ToString() ?? "NULL"; 
                     string id = firstWorksheet.Cells[i, 2].Value?.ToString() ?? "NULL";     

                     // Usar string.Format para crear la salida formateada  
                     string filaDatos = string.Format("Nombre: {0} ... ID: <{1}>", nombre, id);

                     datos += filaDatos + "\n"; // Agregar la fila formateada a la cadena de datos  
                  }
                  TaskDialog.Show("Contenido Excel", datos);
               }
               else
               {
                  TaskDialog.Show("Info", "There is no data in the spreadsheet.");
                     
               }

               return ;
            }
         }
         catch (Exception ex)
         {
            TaskDialog.Show("Info", "Error: " + ex.Message);
            
         }
      }

 

0 Likes
Message 9 of 9

AmitMetz
Explorer
Explorer

Hi, i encountered the same problem while using ClosedXml. all worked fine outside of revit but when running the plugin from inside revit it crashed.
the reason is that when you install Nuget package of ClosedXml version 0.95.4.0 (that's the latest version as i understand which works with Revit)  it automatically install the packages it based on:

  • DocumentFormat.OpenXml version 2.7.2.
  • ExcelNumberFormat version 1.0.10.0

But when Revit is launched it loads on startup DocumentFormat.OpenXml version 2.12.3 and this difference between plugin version and what revit loads cause the plugin to crash.
you can check which version is currently running in revit with the code:

    var assembly = typeof(OpenXmlElement).Assembly;
    var name = assembly.GetName().ToString();
    var version = assembly.GetName().Version?.ToString();
    var location = assembly.Location;

    TaskDialog.Show("OpenXmlElement Info", $"Named: {name}\nVersion: {version}\nLoaded from: {location}");


the solution is called Assembly Binding Redirection
you need to create an app.config file in the same directory where the pluginName.dll (in visual studio right click on project -> add -> New item -> Application Configuration File) and add the code below. make sure it copied to the output directory set the "Copy to Output Directory" property to "Copy if newer" or "Copy always".

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <runtime>
    <assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1">
      <dependentAssembly>
        <assemblyIdentity name="DocumentFormat.OpenXml"
                          publicKeyToken="8fb06cb64d019a17"
                          culture="neutral" />
        <bindingRedirect oldVersion="0.0.0.0-2.12.3.0" newVersion="2.12.3.0" />
      </dependentAssembly>
    </assemblyBinding>
  </runtime>
</configuration>

 

0 Likes