Import data from excel to revit

[elango_saramani]

I have written a code for importing parameter value for pipe project parameter.The datas are reading from excel but when i run the file data is empty in revit Please let me if there is anything i missed up in the code

using System;
using System.Collections.Generic;
using System.IO;
using Autodesk.Revit.UI;
using Autodesk.Revit.DB;
using OfficeOpenXml; // Ensure EPPlus namespace is used

namespace RevitExcelAddIn
{
public class UpdatePipeParametersCommand : IExternalCommand
{
public Result Execute(ExternalCommandData commandData, ref string message, ElementSet elements)
{
Document doc = commandData.Application.ActiveUIDocument.Document;
string filePath = @"C:\Users\xxxxx\xxx)\Pipe_Schedule1.xlsx";

var familyData = ReadExcelFile(filePath);

foreach (var data in familyData)
{
try
{
UpdateParametersInRevit(doc, data);
}
catch (Exception ex)
{
message = $"An error occurred while updating parameters: {ex.Message}";
return Result.Failed;
}
}

return Result.Succeeded;
}

static List<FamilyData> ReadExcelFile(string filePath)
{
var data = new List<FamilyData>();

FileInfo fileInfo = new FileInfo(filePath);

using (var package = new ExcelPackage(fileInfo))
{
ExcelWorksheet worksheet = package.Workbook.Worksheets[0];

for (int row = 2; row <= worksheet.Dimension.End.Row; row++)
{
var familyType = worksheet.Cells[row, 1].Text;
var familyName = worksheet.Cells[row, 2].Text;
var diameter = worksheet.Cells[row, 3].Text;

var parameters = new Dictionary<string, string>
{
{ "ComponentGroup", worksheet.Cells[row, 4].Text },
{ "ComponentGroupDescription", worksheet.Cells[row, 5].Text },
// Add remaining parameters
};

data.Add(new FamilyData
{
FamilyType = familyType,
FamilyName = familyName,
Diameter = diameter,
Parameters = parameters
});
}
}

return data;
}

static void UpdateParametersInRevit(Document doc, FamilyData data)
{
var collector = new FilteredElementCollector(doc)
.OfCategory(BuiltInCategory.OST_PipeCurves)
.WhereElementIsNotElementType();

foreach (FamilyInstance instance in collector)
{
if (instance.Symbol.Family.Name == data.FamilyName && instance.Symbol.Name == data.FamilyType)
{
using (Transaction trans = new Transaction(doc, "Update Pipe Parameters"))
{
trans.Start();

foreach (var kvp in data.Parameters)
{
Parameter param = instance.LookupParameter(kvp.Key);
if (param != null)
{
try
{
if (param.StorageType == StorageType.String)
{
param.Set(kvp.Value);
}
else if (param.StorageType == StorageType.Double)
{
if (double.TryParse(kvp.Value, out double doubleValue))
{
param.Set(doubleValue);
}
else
{
throw new FormatException($"Cannot convert '{kvp.Value}' to Double.");
}
}
else if (param.StorageType == StorageType.Integer)
{
if (int.TryParse(kvp.Value, out int intValue))
{
param.Set(intValue);
}
else
{
throw new FormatException($"Cannot convert '{kvp.Value}' to Integer.");
}
}
else
{
throw new NotSupportedException($"Parameter storage type '{param.StorageType}' is not supported.");
}
}
catch (Exception ex)
{
TaskDialog.Show("Parameter Update Error", $"Error updating parameter '{kvp.Key}': {ex.Message}");
}
}
}

trans.Commit();
}
}
}
}
}

class FamilyData
{
public string FamilyType { get; set; }
public string FamilyName { get; set; }
public string Diameter { get; set; }
public Dictionary<string, string> Parameters { get; set; }
}
}

[reylorente1]

hello, shouldn't I put MepCurve or Pipe when you run the foreach?

foreach (Pipe instance in collector)
{
if (instance.Name == data.FamilyName && instance.PipeType.Name == data.FamilyType)
{ //......

[elango_saramani]

Updated cod but still not working 

static void UpdateParametersInRevit(Document doc, FamilyData data)
{
var collector = new FilteredElementCollector(doc)
.OfCategory(BuiltInCategory.OST_PipeCurves)
.WhereElementIsNotElementType();

foreach (Pipe instance in collector)
{
if (instance.Name == data.FamilyName && instance.PipeType.Name == data.FamilyType)
{
using (Transaction trans = new Transaction(doc, "Update Pipe Parameters"))
{
trans.Start();

[reylorente1]

And he doesn't ask for your license?
Because when I use Epplus, it always asks me for a license.
Here I show you part of a code...

public static DataTable Data { get; set; }

#region DataReadExcel
/// <summary>
/// Insertar un Excel a DataGrid
/// </summary>
/// <returns></returns>
public static DataTable ReadExcel()
{
string filePath = @"E:\000000 - Carpeta organizada\Agrupar A\A-Excel\Excel\LE.xlsx";

ExcelPackage.LicenseContext = LicenseContext.NonCommercial;

using (ExcelPackage excelPackage = new ExcelPackage(new FileInfo(filePath)))
{
Data = ExcelPackageToDataTable(excelPackage);

return Data;
}
}
#endregion

[Mohamed_Arshad · Accepted solution]

Hi @elango_saramani 

   I have seen few error in your code. As @reylorente1 mentioned you have to implement the License Context for Epplus. I have updated your code little bit. Kindly check it.

Reference Code (TransactionAttribute Missing)

namespace RevitExcelAddIn
{
    [Transaction(TransactionMode.Manual)] ///Missing Transaction Attribute
    public class UpdatePipeParametersCommand : IExternalCommand
    {
        public Result Execute(ExternalCommandData commandData, ref string message, ElementSet elements)
        {
            Document doc = commandData.Application.ActiveUIDocument.Document;
            string filePath = @"C:\Users\xxxxx\xxx)\Pipe_Schedule1.xlsx";

            var familyData = ReadExcelFile(filePath);

            foreach (var data in familyData)
            {
                try
                {
                    UpdateParametersInRevit(doc, data);
                }
                catch (Exception ex)
                {
                    message = $"An error occurred while updating parameters: {ex.Message}";
                    return Result.Failed;
                }
            }

            return Result.Succeeded;
        }
}

LicenseContext Missing

        static List<FamilyData> ReadExcelFile(string filePath)
        {
            ExcelPackage.LicenseContext = LicenseContext.NonCommercial;

            var data = new List<FamilyData>();

            FileInfo fileInfo = new FileInfo(filePath);

            using (var package = new ExcelPackage(fileInfo))
            {
                ExcelWorksheet worksheet = package.Workbook.Worksheets[0];

                for (int row = 2; row <= worksheet.Dimension.End.Row; row++)
                {
                    var familyType = worksheet.Cells[row, 1].Text;
                    var familyName = worksheet.Cells[row, 2].Text;
                    var diameter = worksheet.Cells[row, 3].Text;

                    var parameters = new Dictionary<string, string>
                        {
                            { "ComponentGroup", worksheet.Cells[row, 4].Text },
                            { "ComponentGroupDescription", worksheet.Cells[row, 5].Text },
                            // Add remaining parameters
                        };

                    data.Add(new FamilyData
                    {
                        FamilyType = familyType,
                        FamilyName = familyName,
                        Diameter = diameter,
                        Parameters = parameters
                    });
                }
            }

            return data;
        }

My Suggestion :
    Use Element and ElementType while dealing with Parameters don't convert to particular type. We can able to access Parameter directly from the Element class.

Reference Code

    static void UpdateParametersInRevit(Document doc, FamilyData data)
    {
        var collector = new FilteredElementCollector(doc)
        .OfCategory(BuiltInCategory.OST_PipeCurves)
        .WhereElementIsNotElementType();

        foreach (Element element in collector) ///Convert FamilyInstance to Element
        {
            ///Use Element Type
            ElementType elementType= doc.GetElement(element.GetTypeId()) as ElementType;

            if (elementType.Name == data.FamilyName && elementType.FamilyName == data.FamilyType)
            {
                using (Transaction trans = new Transaction(doc, "Update Pipe Parameters"))
                {
                    trans.Start();

                    foreach (var kvp in data.Parameters)
                    {
                        Parameter param = element.LookupParameter(kvp.Key);
                        if (param != null)
                        {
                            try
                            {
                                if (param.StorageType == StorageType.String)
                                {
                                    param.Set(kvp.Value);
                                }
                                else if (param.StorageType == StorageType.Double)
                                {
                                    if (double.TryParse(kvp.Value, out double doubleValue))
                                    {
                                        param.Set(doubleValue);
                                    }
                                    else
                                    {
                                        throw new FormatException($"Cannot convert '{kvp.Value}' to Double.");
                                    }
                                }
                                else if (param.StorageType == StorageType.Integer)
                                {
                                    if (int.TryParse(kvp.Value, out int intValue))
                                    {
                                        param.Set(intValue);
                                    }
                                    else
                                    {
                                        throw new FormatException($"Cannot convert '{kvp.Value}' to Integer.");
                                    }
                                }
                                else
                                {
                                    throw new NotSupportedException($"Parameter storage type '{param.StorageType}' is not supported.");
                                }
                            }
                            catch (Exception ex)
                            {
                                TaskDialog.Show("Parameter Update Error", $"Error updating parameter '{kvp.Key}': {ex.Message}");
                            }
                        }
                    }

                    trans.Commit();
                }
            }
        }
    }
}

Hope this will Helps 🙂

[Mohamed_Arshad · Accepted solution]

HI @elango_saramani 

Here is the code correction below,

using System;
using System.Collections.Generic;
using System.IO;
using Autodesk.Revit.UI;
using Autodesk.Revit.DB;
using OfficeOpenXml;
using Autodesk.Revit.Attributes; // Ensure EPPlus namespace is used

namespace RevitExcelAddIn
{
    [Transaction(TransactionMode.Manual)] ///Missing Transaction Attribute
    public class UpdatePipeParametersCommand : IExternalCommand
    {
        public Result Execute(ExternalCommandData commandData, ref string message, ElementSet elements)
        {
            Document doc = commandData.Application.ActiveUIDocument.Document;
            string filePath = @"C:\Users\xxxxx\xxx)\Pipe_Schedule1.xlsx";

            var familyData = ReadExcelFile(filePath);

            foreach (var data in familyData)
            {
                try
                {
                    UpdateParametersInRevit(doc, data);
                }
                catch (Exception ex)
                {
                    message = $"An error occurred while updating parameters: {ex.Message}";
                    return Result.Failed;
                }
            }

            return Result.Succeeded;
        }

        static List<FamilyData> ReadExcelFile(string filePath)
        {
            ExcelPackage.LicenseContext = LicenseContext.NonCommercial;

            var data = new List<FamilyData>();

            FileInfo fileInfo = new FileInfo(filePath);

            using (var package = new ExcelPackage(fileInfo))
            {
                ExcelWorksheet worksheet = package.Workbook.Worksheets[0];

                for (int row = 2; row <= worksheet.Dimension.End.Row; row++)
                {
                    var familyType = worksheet.Cells[row, 1].Text;
                    var familyName = worksheet.Cells[row, 2].Text;
                    var diameter = worksheet.Cells[row, 3].Text;

                    var parameters = new Dictionary<string, string>
                        {
                            { "ComponentGroup", worksheet.Cells[row, 4].Text },
                            { "ComponentGroupDescription", worksheet.Cells[row, 5].Text },
                            // Add remaining parameters
                        };

                    data.Add(new FamilyData
                    {
                        FamilyType = familyType,
                        FamilyName = familyName,
                        Diameter = diameter,
                        Parameters = parameters
                    });
                }
            }

            return data;
        }

        static void UpdateParametersInRevit(Document doc, FamilyData data)
        {
            var collector = new FilteredElementCollector(doc)
            .OfCategory(BuiltInCategory.OST_PipeCurves)
            .WhereElementIsNotElementType();

            foreach (Element element in collector) ///Convert FamilyInstance to Element
            {
                ///Use Element Type
                ElementType elementType= doc.GetElement(element.GetTypeId()) as ElementType;

                if (elementType.Name == data.FamilyName && elementType.FamilyName == data.FamilyType)
                {
                    using (Transaction trans = new Transaction(doc, "Update Pipe Parameters"))
                    {
                        trans.Start();

                        foreach (var kvp in data.Parameters)
                        {
                            Parameter param = element.LookupParameter(kvp.Key);
                            if (param != null)
                            {
                                try
                                {
                                    if (param.StorageType == StorageType.String)
                                    {
                                        param.Set(kvp.Value);
                                    }
                                    else if (param.StorageType == StorageType.Double)
                                    {
                                        if (double.TryParse(kvp.Value, out double doubleValue))
                                        {
                                            param.Set(doubleValue);
                                        }
                                        else
                                        {
                                            throw new FormatException($"Cannot convert '{kvp.Value}' to Double.");
                                        }
                                    }
                                    else if (param.StorageType == StorageType.Integer)
                                    {
                                        if (int.TryParse(kvp.Value, out int intValue))
                                        {
                                            param.Set(intValue);
                                        }
                                        else
                                        {
                                            throw new FormatException($"Cannot convert '{kvp.Value}' to Integer.");
                                        }
                                    }
                                    else
                                    {
                                        throw new NotSupportedException($"Parameter storage type '{param.StorageType}' is not supported.");
                                    }
                                }
                                catch (Exception ex)
                                {
                                    TaskDialog.Show("Parameter Update Error", $"Error updating parameter '{kvp.Key}': {ex.Message}");
                                }
                            }
                        }

                        trans.Commit();
                    }
                }
            }
        }
    }

    class FamilyData
    {
        public string FamilyType { get; set; }
        public string FamilyName { get; set; }
        public string Diameter { get; set; }
        public Dictionary<string, string> Parameters { get; set; }
    }
}