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

[摘要] 有时需要将Revit模型的一些信息提取到Excel中进行处理或者是作为记录进行储存，但也许是因为Revit的数据结构相对复杂，并不支持直接将数据

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

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

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

//使用Excel2013，引用Microsoft Excel 15.0 Object Libraryusing 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; } }}