//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"
}
