How to create a table and export it to Excel?

[dem1st]

What is the simplest way to create a table filled with instances of one category and values of several parameters?
So far, I've only managed to collect the elements of a certain category in the collector.

Document doc = commandData.Application.ActiveUIDocument.Document;
FilteredElementCollector collector = new FilteredElementCollector(doc);            
BuiltInCategory builtInCategory = BuiltInCategory.OST_Walls;
ElementCategoryFilter elementCategoryFilter = new ElementCategoryFilter(builtInCategory);            
IList <Element> allElements = collector.WherePasses(elementCategoryFilter).WhereElementIsNotElementType().ToElements();
            
List<Parameter> ElemWidthList = new List<Parameter>();
           
foreach (Element e in AllWalls)
            {               
               ElemWidthList.Add(e.LookupParameter("Width"));   
             }

PS: I've tried Jeremy's Labs but I couldn't figure out how to find the excel file itself with all the exported elements and also tried CsvHelper but it seems very confusing and the output is just gibberish (image below)

[jeremy_tammik]

From your question, I deduce that you do not need to create a table at all, at least not as far as Revit is concerned.

Apparently, these are the steps that you need to implement:

• Collect the required elements by implementing a suitable filtered element collector
• Access the data of interest, i.e., read the parameter values you are interested in
• Export this data to Excel or CSV

The first two steps are addressed in full by the Revit API getting started material, so I suggest that you work through that in depth first of all:

https://thebuildingcoder.typepad.com/blog/about-the-author.html#2

The third step has nothing to do with the Revit API, so this is not an appropriate place to discuss it. 

However, it is covered in full by several SDK and other samples, including the labs that you refer to:

https://github.com/jeremytammik/AdnRevitApiLabsXtra

Which of the external commands did you explore? I would suggest looking at Lab4_2_ExportParametersToExcel.

I would also suggest exploring the following:

• The Revit SDK sample FireRating
• https://thebuildingcoder.typepad.com/blog/2012/09/exporting-parameter-data-to-excel.html
• https://github.com/jeremytammik/FireRatingCloud
• https://thebuildingcoder.typepad.com/blog/export

I would not export directly to Excel or any other proprietary format.

Using a neutral open format such as CSV seems much more appropriate to me for most scenarios.

[dem1st]

Thank you for your thorough reply Jeremy! You've provided so much help and
support to fellow developers and enriched the community on a big scale!

The one that I explored was Lab_4_2. It runs fine although I wasn't able to
find the excel file itself with everything that's been exported. I guess by
default Excel program opens up after the command finished running, right?
Could you tell why that doesn't happen or where to find the .xls(csv) file?
What could have I missed?

Also, why would you not export directly to Excel? I guess the options are
either the sheet gets saved to a specified path or Excel opens up
immediately after the command finished executing.

[RPTHOMAS108]

There is no advantage to saving in Excel instead of creating a text file with a .csv extension. That extension is often associated with Excel so It'll open it just as it would an xlsx. The only difference is that you would then have to save it as xlsx to keep Excel features used after first opening.

So unless you are using Revit to create Excel sheet with font colours or adding objects such as charts etc. there is no real point in using Excel as a com server because it'll be slower.

[dem1st]

For anyone who is trying to export something to Excel, I figured that that best way is through EPPlus library. It simple, clear and understandable. They require you to acquire a commercial license if you use it for work but if you use any version below 5* it is completely free of charge.

Here's an example of exporting walls.

ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
            Document doc = commandData.Application.ActiveUIDocument.Document;
             
            FilteredElementCollector collector = new FilteredElementCollector(doc);
            IList<Element> _elementsDB = collector.OfCategory(BuiltInCategory.OST_Walls).WhereElementIsNotElementType().ToElements();
            ICollection<ElementId> _elementsIds = collector.OfCategory(BuiltInCategory.OST_Walls).WhereElementIsNotElementType().ToElementIds();        
 
            List<double> _pHeight = new List<double>();
 
            foreach (Element e in _elementsDB)
            {
                if (e.LookupParameter("Height") != null)
                {
                    _pHeight.Add(UnitUtils.ConvertFromInternalUnits(e.LookupParameter("Height").AsDouble(), DisplayUnitType.DUT_MILLIMETERS));
                }
                else
                {
                    continue;
                }
 
            }
            //Excel
            ExcelPackage package = new ExcelPackage();
            ExcelWorksheet ws = package.Workbook.Worksheets.Add("Sheet");
            var file = new FileInfo(@"D:\Walls.xlsx");
 
            using (ExcelRange Rng = ws.Cells["A1:E2"])
            {                
                ExcelTable table = ws.Tables.Add(Rng, "Table");
                table.Columns[0].Name = "Name";
                table.Columns[1].Name = "ID";
                table.Columns[2].Name = "Height";
            }
           
            int col = 1;
            for (int row = 2; row - 1 <= _elementsDB.Count; row++)
            {
                ws.Cells[row, col].Value = _elementsDB[row - 2].Name;
            }
 
            int col2 = 2;
            for (int row = 2; row - 1 <= _elementsIds.Count; row++)
            {
                ws.Cells[row, col2].Value = _elementsIds.ElementAt(row - 2);
            }
 
            int col3 = 3;
            for (int row = 2; row - 1 <= _elementsIds.Count; row++)
            {
                ws.Cells[row, col3].Value = _pHeight[row - 2];
            }
 
            package.SaveAs(new FileInfo(file.ToString()));