Revit二次开发_将数据导入Excel

有时需要将Revit模型的一些信息提取到Excel中进行处理或者是作为记录进行储存，但也许是因为Revit的数据结构相对复杂，并不支持直接将数据导出Excel，所以平时通过二次开发将信息输出到Excel中。

常使用的输出方法有三个，分别是com组件；NPOI库；Epplus库。

com组件需要电脑安装Excel软件，由于Excel版本比较多，导出的时候要注意版本的问题。下面的代码通过com组件的方法导出模型中的一张明细表。

//使用Excel2013，引用Microsoft Excel 15.0 Object Library

using Autodesk.Revit.DB;
using Autodesk.Revit.UI;
using Autodesk.Revit.Attributes;
using System.IO;
using System.Reflection;
using EXCEL = Microsoft.Office.Interop.Excel;

namespace RevitAddinTestClass
{
 [Transaction(TransactionMode.Manual)]
 class ViewScheduleExport : IExternalCommand
 {
 public Result Execute(ExternalCommandData commandData, ref string message, ElementSet elements)
 {
 Document document = commandData.Application.ActiveUIDocument.Document;

 //获取项目中的一张门明细表
 FilteredElementCollector collector = new FilteredElementCollector(document);
 foreach (ViewSchedule vs in collector.OfClass(typeof(ViewSchedule)))
 {
 if (vs.Name == "门明细表")
 {
 //Excel文件路径
 string path = @"D:LSTTestViewSchedule.xlsx";
 //如文件已存在则删除
 if (File.Exists(path)) File.Delete(path);
 //创建Excel文件
 object nothing = Missing.Value;
 EXCEL.Application excelApplication = new EXCEL.ApplicationClass();
 EXCEL.Workbook excelWorkBook = excelApplication.Workbooks.Add(nothing);
 EXCEL.Worksheet excelWorkSheet = excelWorkBook.Sheets[1] as EXCEL.Worksheet;

 //获取表格的行列数
 int rows, cols;
 TableSectionData data = vs.GetTableData().GetSectionData(SectionType.Body);
 rows = data.NumberOfRows;
 cols = data.NumberOfColumns;

 //导入数据
 for (int i = 0; i < rows; i++)
 {
 for(int j = 0; j < cols; j++)
 {
 EXCEL.Range cell = excelWorkSheet.Cells[i + 1, j + 1] as EXCEL.Range;

 //获取明细表中的字符串
 cell.Value = vs.GetCellText(SectionType.Body, i, j);

 //表头字体设置为粗体
 if (cell.Row == 1)
 {
 cell.Font.Bold = true;
 }
 //添加边框线
 cell.BorderAround2();
 }
 }

 //保存文件
 excelWorkBook.Close(true, path);
 excelApplication.Quit();
 excelApplication = null;

 continue;
 }
 }

 return Result.Succeeded;
 }
 }
}

NPOI与Epplus都是开源免费的，NPOI库用的比较少，因为它只支持03和07版的Excel，但它不需要电脑安装有Excel软件。下面的代码读取模型中的建筑标高，然后通过NPOI库在Excel中制作一个层高表。

using System;
using System.Collections.Generic;
using System.IO;
using Autodesk.Revit.DB;
using Autodesk.Revit.UI;
using Autodesk.Revit.Attributes;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;

namespace RevitAddinTestClass
{
 [Transaction(TransactionMode.Manual)]
 class LevelsScheduleExport : IExternalCommand
 {
 public Result Execute(ExternalCommandData commandData, ref string message, ElementSet elements)
 {
 Document document = commandData.Application.ActiveUIDocument.Document;

 //获取所有建筑标高
 Dictionary<double, string> levelDic = new Dictionary<double, string>();
 List<double> elevationList = new List<double>();

 FilteredElementCollector collector = new FilteredElementCollector(document);
 foreach(Level l in collector.OfCategory(BuiltInCategory.OST_Levels).WhereElementIsNotElementType())
 {
 if (l.get_Parameter(BuiltInParameter.LEVEL_IS_BUILDING_STORY).AsInteger() == 1)
 {
 double elevation = Math.Round(UnitUtils.ConvertFromInternalUnits(l.Elevation, DisplayUnitType.DUT_METERS), 2);
 string levelName = l.Name;
 try
 {
 if (l.GetParameters("避难层")[0].AsInteger() == 1) levelName += "(避难层)";
 }
 catch
 {
 //to do
 }
 levelDic.Add(elevation, levelName);
 elevationList.Add(elevation);
 }
 }

 //按标高的高度排序
 elevationList.Sort();

 //Excel文件路径
 string path = @"D:LSTTestLevelSchedule.xls";
 //如文件已存在则删除
 if (File.Exists(path)) File.Delete(path);
 //创建Excel文件
 HSSFWorkbook excelWorkBook = new HSSFWorkbook();
 ISheet excelWorkSheet = excelWorkBook.CreateSheet("层高表");
 //格式
 ICellStyle cellStyle = excelWorkBook.CreateCellStyle();
 cellStyle.BorderLeft = BorderStyle.Thin;
 cellStyle.BorderTop = BorderStyle.Thin;
 cellStyle.BorderRight = BorderStyle.Thin;
 cellStyle.BorderBottom = BorderStyle.Thin;
 cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00");
 //表头
 IRow hRow = excelWorkSheet.CreateRow(0);

 ICell hCell0 = hRow.CreateCell(0);
 hCell0.SetCellValue("楼层");
 hCell0.CellStyle = cellStyle;

 ICell hCell1 = hRow.CreateCell(1);
 hCell1.SetCellValue("层高");
 hCell1.CellStyle = cellStyle;

 ICell hCell2 = hRow.CreateCell(2);
 hCell2.SetCellValue("标高(m)");
 hCell2.CellStyle = cellStyle;

 //计算高差并写入数据
 for (int i = 0; i < elevationList.Count; i++)
 {
 double currentElve, upElve, height;
 string currentLevel;

 currentElve = elevationList[i];
 currentLevel = levelDic[currentElve];
 if (i == elevationList.Count - 1)
 {
 upElve = 0;
 height = 0;
 }
 else
 {
 upElve = elevationList[i + 1];
 height = upElve - currentElve;
 }

 //写入数据
 IRow dRow = excelWorkSheet.CreateRow(i + 1);

 ICell dCell0 = dRow.CreateCell(0);
 dCell0.SetCellValue(currentLevel);
 dCell0.CellStyle = cellStyle;

 ICell dCell1 = dRow.CreateCell(1);
 if (height == 0)
 {
 dCell1.SetCellValue("");
 }
 else
 {
 dCell1.SetCellValue(height);
 }
 dCell1.CellStyle = cellStyle;

 ICell dCell2 = dRow.CreateCell(2);
 dCell2.SetCellValue(currentElve);
 dCell2.CellStyle = cellStyle;
 }

 //保存文件
 using (FileStream fs = new FileStream(path, FileMode.Create, FileAccess.Write))
 {
 excelWorkBook.Write(fs);
 }

 return Result.Succeeded;
 }
 }
}

Epplus库也不需要电脑安装Excel，但只支持xlsx格式的excel文件，网上的一些评论是导出的效率及稳定性都比NPOI好，但由于没进行过非常大数据量的导出，所以暂时没有体现出来。 下面代码将模型中的管道信息按照一定的规则处理后导出到Excel中，然后在Excel中简单的做个数据透视即可获得对应的工程量。

using System.IO;
using Autodesk.Revit.DB;
using Autodesk.Revit.UI;
using Autodesk.Revit.Attributes;
using Autodesk.Revit.DB.Plumbing;
using OfficeOpenXml;
using OfficeOpenXml.Style;

namespace RevitAddinTestClass
{
 [Transaction(TransactionMode.Manual)]
 class PipeSchedule : IExternalCommand
 {
 public Result Execute(ExternalCommandData commandData, ref string message, ElementSet elements)
 {
 Document document = commandData.Application.ActiveUIDocument.Document;

 //Excel文件路径
 string path = @"D:LSTTestPipeSchedule.xlsx";
 //如文件已存在则删除
 if (File.Exists(path)) File.Delete(path);
 //创建Excel文件
 ExcelPackage package = new ExcelPackage(new FileInfo(path));
 ExcelWorksheet excelWorkSheet = package.Workbook.Worksheets.Add("管道数据");
 //表头
 string[] hearName = { "Id", "系统", "项目名称", "材质", "规格", "连接方式", "单位", "工程量" };
 for(int i = 0; i< hearName.Length; i++)
 {
 ExcelRange hCell = excelWorkSheet.Cells[1, i + 1];
 hCell.Value = hearName[i];
 //格式
 hCell.Style.Font.Bold = true;
 hCell.Style.Border.BorderAround(ExcelBorderStyle.Thin);
 }

 //获得所有管道数据
 List<object[]> pipeDataList = new List<object[]>();
 FilteredElementCollector collector = new FilteredElementCollector(document);
 foreach(Pipe p in collector.OfClass(typeof(Pipe)).WhereElementIsNotElementType())
 {
 string pipeId, pipeSys, pipeItemName, pipeSize, pipeMaterial, pipeConnect, pipeUnit;
 double pipeQuantity;
 //系统缩写
 string abbr = p.get_Parameter(BuiltInParameter.RBS_DUCT_PIPE_SYSTEM_ABBREVIATION_PARAM).AsString();
 //读取数据
 pipeId = p.Id.ToString();
 pipeSys = GetPipeSys(abbr);
 pipeItemName = p.get_Parameter(BuiltInParameter.RBS_PIPING_SYSTEM_TYPE_PARAM).AsValueString().Split('_')[1];
 pipeSize = p.get_Parameter(BuiltInParameter.RBS_CALCULATED_SIZE).AsString().Split(' ')[0];
 pipeMaterial = GetPipeMaterial(Convert.ToDouble(pipeSize), abbr);
 pipeConnect=GetPipeConnect(Convert.ToDouble(pipeSize),pipeMaterial);
 pipeUnit = "m";
 pipeQuantity = UnitUtils.ConvertFromInternalUnits(p.get_Parameter(BuiltInParameter.CURVE_ELEM_LENGTH).AsDouble(), DisplayUnitType.DUT_METERS);

 object[] pipeData = { pipeId, pipeSys, pipeItemName, pipeMaterial, "DN" + pipeSize, pipeConnect, pipeUnit, pipeQuantity };
 pipeDataList.Add(pipeData);
 }

 //写入数据
 for(int i = 0; i < pipeDataList.Count; i++)
 {
 object[] pipeData = pipeDataList[i];
 for(int j = 0; j < pipeData.Length; j++)
 {
 ExcelRange dCell = excelWorkSheet.Cells[i + 2, j + 1];
 dCell.Value = pipeData[j];
 dCell.Style.Border.BorderAround(ExcelBorderStyle.Thin);
 }
 }

 //保存
 package.Save();
 package.Dispose();

 return Result.Succeeded;
 }

 string GetPipeSys(string abbreviation)
 {
 Dictionary<string, string> sysDic = new Dictionary<string, string>();
 sysDic.Add("ZP", "消防系统");
 sysDic.Add("X", "消防系统");
 sysDic.Add("J", "给水系统");
 sysDic.Add("F", "排水系统");
 sysDic.Add("W", "排水系统");

 return sysDic[abbreviation];
 }

 string GetPipeMaterial(double pipeSize,string abbreviation)
 {
 string material = "未定义";
 switch (abbreviation)
 {
 case "ZP":
 material = "镀锌钢管";
 break;
 case "X":
 material = "镀锌钢管";
 break;
 case "J":
 if (pipeSize > 50)
 {
 material = "钢塑复合管";
 }
 else
 {
 material = "PP-R管";
 }
 break;
 case "F":
 material = "PVC-U管";
 break;
 case "W":
 material = "PVC-U管";
 break; 
 }
 return material;
 }

 string GetPipeConnect(double pipeSize,string material)
 {
 string connect = "未定义";
 switch (material)
 {
 case "PVC-U管":
 connect = "粘接";
 break;
 case "PP-R管":
 connect = "热熔";
 break;
 case "钢塑复合管":
 if (pipeSize > 65)
 {
 connect = "卡箍";
 }
 else
 {
 connect = "螺纹";
 }
 break;
 case "镀锌钢管":
 if (pipeSize > 65)
 {
 connect = "卡箍";
 }
 else
 {
 connect = "螺纹";
 }
 break;
 }
 return connect;
 }
 }
}

微信公众号：xuebim

关注建筑行业BIM发展、研究建筑新技术，汇集建筑前沿信息！

← 微信扫一扫，关注我们+