//The top method of the event handler // This is called by Revit after the corresponding // external event was raised (by the modeless form) // and Revit reached the time at which it could call // the event's handler (i.e. this object) public void Execute(UIApplication uiapp) { //Creates new instance everytime Excute is called by the IExternalEventHandler var instance = new RevitUtils(); try { switch (Request.Take()) { case RequestId.None: { return; // no request at this time -> we can leave immediately } case RequestId.CreateBillofQuantities: { instance.CreateBillOfQuantities(uiapp); break; } default: { // some kind of a warning here should // notify us about an unexpected request break; } } } finally { Application.thisApp.WakeFormUp(); } return; }
public static void CreateBillofQuantitiesSpreadsheet(UIApplication uiapp, List <ET> ETS, string docTitle) { List <BQ> BQS = RevitUtils.RetrieveBQData(uiapp, ETS); #region Excel Sheet "Bill of Quantities" Excel.Worksheet worksheetBQ = (Excel.Worksheet)workbook.Worksheets.Add(); // adds a new worksheet worksheetBQ.Name = "Bill of Quantities"; // worksheetBQ Name #endregion // Launch or access Excel via COM Interop #region Header formatting //cells E1 to E10 Excel.Range rangeE = worksheetBQ.Range["E1:E10"]; rangeE.Font.Bold = true; rangeE.Font.Color = System.Drawing.Color.FromArgb(0, 51, 102); //merge of cells E1 to E10 for (int row = 1; row <= 10; row++) { worksheetBQ.Range[worksheetBQ.Cells[row, 5], worksheetBQ.Cells[row, 7]].Merge(); } //cells E2 and E3 worksheetBQ.Cells[2, 5] = "REVIT MODEL DOCUMENT"; worksheetBQ.Cells[3, 5] = docTitle; // cell E9 worksheetBQ.Cells[9, 5] = "BILL OF QUANTITIES"; // cell E10 string monthyear = DateTime.Now.ToString("MMMM yyyy", CultureInfo.CurrentCulture).ToUpper(); worksheetBQ.Cells[10, 5] = monthyear; #endregion // Header formatting #region Columns Width worksheetBQ.Columns["A:A"].ColumnWidth = 4.57; worksheetBQ.Columns["B:B"].ColumnWidth = 21.43; worksheetBQ.Columns["C:C"].ColumnWidth = 43.57; worksheetBQ.Columns["D:D"].ColumnWidth = 5.43; worksheetBQ.Columns["E:E"].ColumnWidth = 12.43; worksheetBQ.Columns["F:F"].ColumnWidth = 13.29; worksheetBQ.Columns["G:G"].ColumnWidth = 13.29; worksheetBQ.Columns["H:H"].ColumnWidth = 15.86; #endregion // Columns Width #region HEADER - 1ST ROW - "Bill of Quantities" // header cells merge worksheetBQ.Range["B12:B13"].Merge(); worksheetBQ.Range["C12:C13"].Merge(); worksheetBQ.Range["D12:D13"].Merge(); worksheetBQ.Range["E12:E13"].Merge(); worksheetBQ.Range["F12:F13"].Merge(); worksheetBQ.Range["G12:H12"].Merge(); // header cells names worksheetBQ.Cells[12, 2] = "Nº ART."; //B12 worksheetBQ.Cells[12, 3] = "DESIGNATION"; //C12 worksheetBQ.Cells[12, 4] = "UN"; //D12 worksheetBQ.Cells[12, 5] = "QUANT."; //E12 worksheetBQ.Cells[12, 6].Style.WrapText = true; // to visible the line brakes //F12 worksheetBQ.Cells[12, 6] = "PRICE\n PER UNIT"; //F12 worksheetBQ.Cells[12, 7] = "COST"; //G12 worksheetBQ.Cells[13, 7] = "PARTIAL"; //G13 worksheetBQ.Cells[13, 8] = "TOTAL"; //H13 // header cells range Excel.Range rangeH = worksheetBQ.Range["B12:H13"]; // header cells style rangeH.Style.Font.Name = "Arial"; rangeH.Style.Font.Size = 10; rangeH.Font.Bold = true; // header cells centered and aligned rangeH.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; rangeH.VerticalAlignment = Excel.XlHAlign.xlHAlignCenter; // header cells boundaries rangeH.Borders.LineStyle = Excel.XlLineStyle.xlContinuous; #endregion // HEADER - 1ST ROW - "Bill of Quantities" #region OTHER ROWS - "Bill of Quantities" #region Table filling int rowBQ = 14; var BQSSorted = BQS.AsQueryable().OrderBy(bq => bq.KeyValue).ToList(); foreach (var ListBQ in BQSSorted) { ++rowBQ; // +1 row worksheetBQ.Cells[rowBQ, 2] = ListBQ.KeyValue; if (ListBQ.KeyValue == "MISSING") { Excel.Range range = worksheetBQ.Cells[rowBQ, 2]; range.Font.Color = System.Drawing.Color.FromArgb(255, 0, 0); // Missing KeyValue } Excel.Range range3 = worksheetBQ.Cells[rowBQ, 3]; range3.Font.Bold = true; range3.Style.WrapText = true; range3.Value = ListBQ.AssemblyDesc; if (ListBQ.AssemblyDesc == "MISSING") { range3.Font.Color = System.Drawing.Color.FromArgb(255, 0, 0); // Missing AssemblyDesc } Excel.Range range4 = worksheetBQ.Cells[rowBQ + 1, 3]; worksheetBQ.Cells[rowBQ + 1, 3] = ListBQ.KeyText; if (ListBQ.KeyText == "MISSING") { range4.Font.Color = System.Drawing.Color.FromArgb(255, 0, 0); // Missing KeynoteText } worksheetBQ.Cells[rowBQ, 4] = ListBQ.Unit; worksheetBQ.Range[worksheetBQ.Cells[rowBQ, 4], worksheetBQ.Cells[rowBQ + 1, 4]].Merge(); Excel.Range range5 = worksheetBQ.Cells[rowBQ, 5]; // column E range5.NumberFormat = "###0.00"; // column E in euros range5.Value = ListBQ.Quant; worksheetBQ.Range[worksheetBQ.Cells[rowBQ, 5], worksheetBQ.Cells[rowBQ + 1, 5]].Merge(); Excel.Range range6 = worksheetBQ.Cells[rowBQ, 6]; // column F range6.NumberFormat = "###0.00 €"; // column F in euros range6.Value = ListBQ.PrUnit; worksheetBQ.Range[worksheetBQ.Cells[rowBQ, 6], worksheetBQ.Cells[rowBQ + 1, 6]].Merge(); Excel.Range range7 = worksheetBQ.Cells[rowBQ, 7]; // column G range7.NumberFormat = "###0.00 €"; // column G in euros range7.Value = ListBQ.Partial; worksheetBQ.Range[worksheetBQ.Cells[rowBQ, 7], worksheetBQ.Cells[rowBQ + 1, 7]].Merge(); ++rowBQ; // +1 linha ++rowBQ; // +1 linha } Excel.Range range8 = worksheetBQ.Cells[15, 8]; // cell H15 range8.NumberFormat = "###0.00 €"; // cell H15 in euros range8.Value = "=Sum(G15:G" + rowBQ + ")"; worksheetBQ.Range[worksheetBQ.Cells[15, 8], worksheetBQ.Cells[16, 8]].Merge(); #endregion // Table filling #region Table formatting // gridlines turned off excel.ActiveWindow.DisplayGridlines = false; // table aligned worksheetBQ.Range["B15:C" + rowBQ].VerticalAlignment = Excel.XlHAlign.xlHAlignGeneral; worksheetBQ.Range["D15:H" + rowBQ].HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; worksheetBQ.Range["D15:H" + rowBQ].VerticalAlignment = Excel.XlHAlign.xlHAlignCenter; // table left boarders worksheetBQ.Range["B14:B" + rowBQ].Borders.get_Item(Excel.XlBordersIndex.xlEdgeLeft).LineStyle = Excel.XlLineStyle.xlContinuous; worksheetBQ.Range["D14:D" + rowBQ].Borders.get_Item(Excel.XlBordersIndex.xlEdgeLeft).LineStyle = Excel.XlLineStyle.xlContinuous; worksheetBQ.Range["F14:F" + rowBQ].Borders.get_Item(Excel.XlBordersIndex.xlEdgeLeft).LineStyle = Excel.XlLineStyle.xlContinuous; worksheetBQ.Range["G14:G" + rowBQ].Borders.get_Item(Excel.XlBordersIndex.xlEdgeLeft).LineStyle = Excel.XlLineStyle.xlContinuous; worksheetBQ.Range["I14:I" + rowBQ].Borders.get_Item(Excel.XlBordersIndex.xlEdgeLeft).LineStyle = Excel.XlLineStyle.xlContinuous; // table bottom boarders worksheetBQ.Range["B" + rowBQ + ":H" + rowBQ].Borders.get_Item(Excel.XlBordersIndex.xlEdgeBottom).LineStyle = Excel.XlLineStyle.xlContinuous; // Columns B and C text aligned to the left Excel.Range range9 = worksheetBQ.Range["B:C"]; range9.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft; #endregion // Table formatting #endregion // OTHER ROWS - "Bill of Quantities" }