Reading from and writing to Excel file.

[haluk_uzuner]

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];

[studio-a-int]

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;

[halukuzuner]

Thanks studio-a-int,
Does it requre MS Office to be installed?

[studio-a-int]

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.

[halukuzuner]

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?

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

[halukuzuner]

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

[reylorente1]

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