"All structural estimated in Model export to excel \n\nUpdated : 01-05-2024\n\n Shared by : TUMCIVIL"

__author__ = 'TUMCIVIL'
__version__ = '1.0.0'
# __title__ = 'Export to Excel By Auto Code'
__title__ = 'Export Structural BOQ to Excel'

from tabnanny import check
import xlrd
import xlsxwriter
import os
from pyrevit import forms
import re
import pyrevit

import Autodesk
from Autodesk.Revit.DB import *
import Autodesk.Revit.DB as DB
from Autodesk.Revit.DB.Structure import *

# import Footing

doc = __revit__.ActiveUIDocument.Document
uidoc = __revit__.ActiveUIDocument


def heading():
    # set Column
    ws1.set_column('A:A', 0.85)
    ws1.set_column('B:B', 15)
    ws1.set_column('C:C', 0.85)
    ws1.set_column('D:D', 0.85)
    ws1.set_column('E:E', 78.13)
    ws1.set_column('F:F', 0.85)
    ws1.set_column('G:G', 0.85)
    ws1.set_column('H:H', 8)
    ws1.set_column('I:I', 0.85)
    ws1.set_column('J:J', 0.85)
    ws1.set_column('K:K', 13)
    ws1.set_column('L:L', 0.85)
    ws1.set_column('M:M', 0.85)
    ws1.set_column('N:N', 15.13)
    ws1.set_column('O:O', 0.85)
    ws1.set_column('P:P', 0.85)
    ws1.set_column('Q:Q', 15.13)
    ws1.set_column('R:R', 0.85)
    ws1.set_column('S:S', 0.85)
    ws1.set_column('T:T', 15.13)
    ws1.set_column('U:U', 0.85)
    ws1.set_column('V:V', 0.85)
    ws1.set_column('W:W', 15.13)
    ws1.set_column('X:X', 0.85)
    ws1.set_column('Y:Y', 0.85)
    ws1.set_column('Z:Z', 15.13)
    ws1.set_column('AA:AA', 0.85)

    # set row
    for i in range(20000):
        ws1.set_row(i, 16.5, )

    # Hide Gridline
    ws1.hide_gridlines(2)
    # Create Head Table
    for i in ['B10:B14', 'E10:E14', 'H10:H14', 'K10:K14', 'N10:Q10', 'N13:N14', 'Q13:Q14', 'T10:W10', 'T13:T14',
              'W13:W14', 'Z10:Z14']:
        ws1.merge_range(i, None, option)
    for i in range(27):
        ws1.write(8, i, None, option_top)
        ws1.write(14, i, None, option_bottom)
    for i in range(9, 14):
        for j in [2, 5, 8, 11, 17, 23, 26]:
            ws1.write(i, j, None, option_right)
    for i in [2, 5, 8, 11, 17, 23, 26]:
        # for i in [2, 5, 8, 11, 14, 17, 20,23,26]:
        ws1.write(8, i, None, option_corner_topRight)
        ws1.write(14, i, None, option_corner_bottomRight)

    for i in range(12, 23):
        ws1.write(11, i, None, option_top)

    for i in [14, 17, 20, 23]:
        ws1.write(11, i, None, option_corner_topRight)
        ws1.write(14, i, None, option_corner_bottomRight)
    for i in range(12, 14):
        for j in [14, 20]:
            ws1.write(i, j, None, option_right)

    m = {'B10': 'ITEM', 'E10': 'DESCRIPTION'}
    for i, j in m.items():
        ws1.write(i, j, option)
    m1 = {'H10': 'UNIT', 'K10': 'TOTAL QTY', 'N10': 'MATERIAL', 'T10': 'LABOUR', 'Z10': 'AMOUNT\n(THB)'}
    for i, j in m1.items():
        ws1.write(i, j, option)
    m2 = {'N13': 'UNIT RATE\n(THB)', 'Q13': 'COST\n(THB)', 'T13': 'UNIT RATE\n(THB)', 'W13': 'COST\n(THB)'}
    for i, j in m2.items():
        ws1.write(i, j, option)
    m3 = {'B3': 'PROJECT :', 'B5': 'LOCATION :'}
    for i, j in m3.items():
        ws1.write(i, j, option4)
    ws1.write('W3', 'DATE :', option11)
    ws1.write('Z3', '=NOW()', option10)

    # Create Topic Summery

    rowHeadStart = 15
    rowHeadEnd = 51 + 1
    # rowRight(rowHeadStart, rowHeadEnd)
    k = {1: rowHeadStart + 1, 2: rowHeadStart + 10}
    for i, j in k.items():
        d1 = codeElement(i)
        ws1.write(j, 1, i, option)
        ws1.write(j, 4, d1, option1)

    k1 = [101, 102, 103, 104]
    j = rowHeadStart + 2
    for i in k1:
        j = j + 1
        d1 = codeElement(i)
        ws1.write(j, 1, i, option5)
        ws1.write(j, 4, d1, option6)

    k2 = {201: rowHeadStart + 12, 202: rowHeadStart + 20 + 1, 203: rowHeadStart + 26 + 1}
    for i, j in k2.items():
        d1 = codeElement(i)
        ws1.write(j, 1, i, option)
        ws1.write(j, 4, d1, option7)

    k3 = [2011, 2012, 2013, 2014, 2015, 2016]
    j = rowHeadStart + 13
    for i in k3:
        j = j + 1
        d1 = codeElement(i)
        ws1.write(j, 1, i, option5)
        ws1.write(j, 4, d1, option6)

    k4 = [2021, 2022, 2023]
    # k4 = [2021,20211,20212,202121,202122, 2022,20221,20222,202221,202222, 2023]
    j = rowHeadStart + 21 + 1
    for i in k4:
        j = j + 1
        d1 = codeElement(i)
        ws1.write(j, 1, i, option5)
        ws1.write(j, 4, d1, option6)

    k5 = [2031, 2032, 2033, 2034, 2035]
    j = rowHeadStart + 27 + 1
    for i in k5:
        j = j + 1
        d1 = codeElement(i)
        ws1.write(j, 1, i, option5)
        ws1.write(j, 4, d1, option6)

    # k6 = [(18,21,"1"),(29,47,"2")]
    # for i in k6:
    #     rows = i[0][0][0]
    #     rows1 = i[0][1][0]
    #     d1 = i[0][0][1]
    #     subTotal(rows,rows1,d1)
    # subTotal2(18,23,"1")
    # subTotal2(29,49,"2")

    rowRight(rowHeadStart, rowHeadEnd)
    subTotal2(rowHeadStart + 3, rowHeadStart + 8, "1")
    subTotal2(rowHeadStart + 14, rowHeadStart + 35, "2")
    sumaryLine2(rowHeadEnd, "Grand Total Prices")
    # rowTop(rowHeadEnd)
    # print("rowHeadEnd = {}".format(rowHeadEnd))
    # return rowHeadEnd


def heading2():
    # set Column
    ws2.set_column('A:A', 0.85)
    ws2.set_column('B:B', 15)
    ws2.set_column('C:C', 0.85)
    ws2.set_column('D:D', 0.85)
    ws2.set_column('E:E', 78.13)
    ws2.set_column('F:F', 0.85)
    ws2.set_column('G:G', 0.85)
    ws2.set_column('H:H', 8)
    ws2.set_column('I:I', 0.85)
    ws2.set_column('J:J', 0.85)
    ws2.set_column('K:K', 13)
    ws2.set_column('L:L', 0.85)
    ws2.set_column('M:M', 0.85)
    ws2.set_column('N:N', 15.13)
    ws2.set_column('O:O', 0.85)
    ws2.set_column('P:P', 0.85)
    ws2.set_column('Q:Q', 15.13)
    ws2.set_column('R:R', 0.85)
    ws2.set_column('S:S', 0.85)
    ws2.set_column('T:T', 15.13)
    ws2.set_column('U:U', 0.85)
    ws2.set_column('V:V', 0.85)
    ws2.set_column('W:W', 15.13)
    ws2.set_column('X:X', 0.85)
    ws2.set_column('Y:Y', 0.85)
    ws2.set_column('Z:Z', 15.13)
    ws2.set_column('AA:AA', 0.85)

    # set row
    for i in range(20000):
        ws2.set_row(i, 16.5, )

    # Hide Gridline
    ws2.hide_gridlines(2)
    # Create Head Table
    for i in ['B10:B14', 'E10:E14', 'H10:H14', 'K10:K14', 'N10:Q10', 'N13:N14', 'Q13:Q14', 'T10:W10', 'T13:T14',
              'W13:W14', 'Z10:Z14']:
        ws2.merge_range(i, None, option)
    for i in range(27):
        ws2.write(8, i, None, option_top)
        ws2.write(14, i, None, option_bottom)
    for i in range(9, 14):
        for j in [2, 5, 8, 11, 17, 23, 26]:
            ws2.write(i, j, None, option_right)
    for i in [2, 5, 8, 11, 17, 23, 26]:
        # for i in [2, 5, 8, 11, 14, 17, 20,23,26]:
        ws2.write(8, i, None, option_corner_topRight)
        ws2.write(14, i, None, option_corner_bottomRight)

    for i in range(12, 23):
        ws2.write(11, i, None, option_top)

    for i in [14, 17, 20, 23]:
        ws2.write(11, i, None, option_corner_topRight)
        ws2.write(14, i, None, option_corner_bottomRight)
    for i in range(12, 14):
        for j in [14, 20]:
            ws2.write(i, j, None, option_right)
    m = {'B10': 'ITEM', 'E10': 'DESCRIPTION'}
    for i, j in m.items():
        ws2.write(i, j, option)
    m1 = {'H10': 'UNIT', 'K10': 'TOTAL QTY', 'N10': 'MATERIAL', 'T10': 'LABOUR', 'Z10': 'AMOUNT\n(THB)'}
    for i, j in m1.items():
        ws2.write(i, j, option)
    m2 = {'N13': 'UNIT RATE\n(THB)', 'Q13': 'COST\n(THB)', 'T13': 'UNIT RATE\n(THB)', 'W13': 'COST\n(THB)'}
    for i, j in m2.items():
        ws2.write(i, j, option)
    m3 = {'B3': 'PROJECT :', 'B5': 'LOCATION :'}
    for i, j in m3.items():
        ws2.write(i, j, option4)
    ws2.write('W3', 'DATE :', option11)
    ws2.write('Z3', '=NOW()', option10)

    # Create Topic Summery

    rowHeadStart2 = 15
    rowHeadEnd2 = 51 + 1
    # rowRight(rowHeadStart, rowHeadEnd)
    k = {1: rowHeadStart2 + 1, 2: rowHeadStart2 + 10}
    for i, j in k.items():
        d1 = codeElement(i)
        ws2.write(j, 1, i, option)
        ws2.write(j, 4, d1, option1)

    k1 = [101, 102, 103, 104]
    j = rowHeadStart2 + 2
    for i in k1:
        j = j + 1
        d1 = codeElement(i)
        ws2.write(j, 1, i, option5)
        ws2.write(j, 4, d1, option6)

    k2 = {201: rowHeadStart2 + 12, 202: rowHeadStart2 + 20 + 1, 203: rowHeadStart2 + 26 + 1}
    for i, j in k2.items():
        d1 = codeElement(i)
        ws2.write(j, 1, i, option)
        ws2.write(j, 4, d1, option7)

    k3 = [2011, 2012, 2013, 2014, 2015, 2016]
    j = rowHeadStart2 + 13
    for i in k3:
        j = j + 1
        d1 = codeElement(i)
        ws2.write(j, 1, i, option5)
        ws2.write(j, 4, d1, option6)

    k4 = [2021, 2022, 2023]
    # k4 = [2021,20211,20212,202121,202122, 2022,20221,20222,202221,202222, 2023]
    j = rowHeadStart2 + 21 + 1
    for i in k4:
        j = j + 1
        d1 = codeElement(i)
        ws2.write(j, 1, i, option5)
        ws2.write(j, 4, d1, option6)

    k5 = [2031, 2032, 2033, 2034, 2035]
    j = rowHeadStart2 + 27 + 1
    for i in k5:
        j = j + 1
        d1 = codeElement(i)
        ws2.write(j, 1, i, option5)
        ws2.write(j, 4, d1, option6)

    rowRight2(rowHeadStart2, rowHeadEnd2)
    # subTotal2(18,23,"1")


def codeElement(d1):
    data = {1: "UNDERGROUND-STRUCTURE WORKS",
            101: "EARTH WORKS",
            1011: "EXCAVATION SOIL",
            10111: "Excavation soil footing",
            10112: "Excavation soil ground beam",
            1012: "BACKFILL SOIL",
            10121: "Backfill soil footing",
            10122: "Backfill soil ground beam",
            1013: "DISPOSAL SOIL",
            10131: "Disposal soil footing",
            10132: "Disposal soil ground beam",
            1014: "VOLUME CONCRETE LEAN",
            10141: "Volume concrete lean footing",
            10142: "Volume concrete lean ground beam",
            1015: "COMPACTION SAND",
            10151: "Compaction sand footing",
            10152: "Compaction sand ground beam",
            1016: "PLASTIC SHEET",
            1017: "CRUSHED STONE",
            1018: "LATERITE SOIL",
            102: "PILE WORKS",
            1021: "PILE I SHAPE",
            1022: "PILE SQUARE SHAPE",
            1023: "PILE BORED",
            1024: "PILE SPAN",
            1025: "PILE HEXAGON",
            103: "FOOTING WORKS",
            1031: "CONCRETE FOOTING",
            10311: "VOLUME CONCRETE STRUCTURAL FOOTING",
            10312: "FORMWORK FOOTING",
            10313: "REINFOREMENT FOOTING",
            104: "WATERPROOFING SYSTEMS",
            1041: "WATER STOP",
            1042: "WATERPROOFING MEMBRANE",
            2: "SUPER-STRUCTURE WORKS",
            201: "STRUCTURAL CONCRETE WORKS",
            2011: "STRUCTURAL CONCRETE COLUMNS",
            20111: "VOLUME CONCRETE STRUCTURAL COLUMNS",
            20112: "FORMWORK COLUMNS",
            20113: "REINFOREMENT COLUMNS",
            2012: "STRUCTURAL CONCRETE BEAMS",
            20121: "VOLUME CONCRETE STRUCTURAL BEAMS",
            20122: "FORMWORK BEAMS",
            20123: "REINFOREMENT BEAMS",
            2013: "STRUCTURAL FLOORS/DROP PANELS",
            20131: "STRUCTURAL FLOORS",
            201311: "VOLUME CONCRETE STRUCTURAL FLOORS",
            201312: "FORMWORK FLOORS",
            201313: "REINFOREMENT FLOORS",
            20132: "STRUCTURAL DROP PANELS",
            201321: "VOLUME CONCRETE STRUCTURAL DROP PANELS",
            201322: "FORMWORK DROP PANELS",
            201323: "REINFOREMENT DROP PANELS",
            20133: "REINFOREMENT STRUCTURAL FLOORS/DROP PANELS",

            2014: "CONCRETE STRUCTURAL LIFT CORE WALL",
            20141: "VOLUME CONCRETE STRUCTURAL LIFT CORE WALL",
            20142: "FORMWORK LIFT CORE WALL",
            20143: "REINFOREMENT LIFT CORE WALL",
            2015: "JOINT FLOORS",
            20151: "EXPANSION JOINT FLOORS",
            20152: "CONTRACTION JOINT FLOORS",
            20153: "LONGITUDINAL JOINT FLOORS",

            2016: "STRUCTURAL CONCRETE COBELS",
            20161: "VOLUME CONCRETE STRUCTURAL COBELS",
            20162: "FORMWORK COBELS",
            20163: "REINFOREMENT COBELS",

            2017: "CONCRETE STRUCTURAL STAIRS",
            20171: "VOLUME CONCRETE STRUCTURAL STAIRS",
            20172: "FORMWORK STAIRS",
            20173: "REINFOREMENT STAIRS",
            202: "STRUCTURAL STEEL WORKS",
            2021: "STEEL COLUMNS",
            20211: "WEIGHT OF STEEL COLUMNS",
            20212: "SURFACE AREA OF STEEL COLUMNS",
            202121: "PREPARE AND APPLY ANTI-RUST PAINT TO STEEL COLUMNS",
            202122: "PREPARE AND APPLY 3-HOUR FIREPROOFING TO STEEL COLUMNS",
            2022: "STEEL BEAMS",
            20221: "WEIGHT OF STEEL BEAMS",
            20222: "SURFACE AREA OF STEEL BEAMS",
            202221: "PREPARE AND APPLY ANTI-RUST PAINT TO STEEL BEAMS",
            202222: "PREPARE AND APPLY 3-HOUR FIREPROOFING TO STEEL BEAMS",
            2023: "METAL FLOOR DECK",
            203: "PRECAST CONCRETE WORKS",
            2031: "PRECAST CONCRETE FLOORS",
            20311: "HOLLOW CORE SLABS/PLANK SLABS",
            20312: "TOPPING OF HOLLOW CORE SLABS/PLANK SLABS",
            20313: "REINFOREMENT TOPPING OF HOLLOW CORE SLABS/PLANK SLABS",
            2032: "PRECAST CONCRETE COLUMNS",
            2033: "PRECAST CONCRETE BEAMS",
            2034: "PRECAST CONCRETE WALLS",
            2035: "PRECAST CONCRETE STAIRS",

            }
    for i in data:
        if i == d1:
            v1 = data[i]
    return v1


def codeElement2(d1):
    data = {1: 17,
            101: 19,
            102: 20,
            103: 21,
            104: 22,
            2: 26,
            2011: 30,
            2012: 31,
            2013: 32,
            2014: 33,
            2015: 34,
            2016: 35,
            2021: 39,
            2022: 40,
            2023: 41,
            2031: 45,
            2032: 46,
            2033: 47,
            2034: 48,
            2035: 49,

            }
    for i in data:
        if i == d1:
            v1 = data[i]
    return v1


def rowRight(start, end):
    for i in range(start, end):
        for j in [2, 5, 8, 11, 14, 17, 20, 23, 26]:
            # for j in [2, 5, 6, 7, 8, 9, 10, 11, 12]:
            ws1.write(i, j, None, option_right)


def rowRight2(start, end):
    for i in range(start, end):
        for j in [2, 5, 8, 11, 14, 17, 20, 23, 26]:
            # for j in [2, 5, 6, 7, 8, 9, 10, 11, 12]:
            ws2.write(i, j, None, option_right)


def rowCornerTopRight(rows):
    for i in [rows]:  # fixed
        for j in [2, 5, 8, 11, 14, 17, 20, 23, 26]:
            ws1.write(i, j, None, option_corner_topRight)


def rowCornerBottomRight(rows):
    for i in [rows]:  # vary rows
        for j in [2, 5, 8, 11, 14, 17, 20, 23, 26]:
            ws1.write(i, j, None, option_corner_bottomRight)


def rowTop(rows):
    for i in range(27):
        ws1.write(rows, i, None, option_top)


def rowBottom(rows):
    for i in range(27):
        ws1.write(rows, i, None, option_bottom)


def subTotal(rows, rows1, d1):
    d2 = codeElement(int(d1))
    d3 = "Subtotal " + d1 + "  " + d2
    d4 = codeElement2(int(d1))
    ws1.set_row(rows1, 24.75, )
    for i in [rows1]:  # vary rows
        for j in [3, 4, 15, 16, 21, 22, 24, 25]:
            ws1.write(i - 1, j, None, option_bottom)
            ws1.write(i + 1, j, None, option_top)
    for i in [rows1]:  # vary rows
        # for j in [5,14,20]:
        for j in [5, 17, 23, 26]:
            ws1.write(i - 1, j, None, option_corner_bottomRight)
            ws1.write(i + 1, j, None, option_corner_topRight)

    ws1.write(rows1, 4, d3, option19)
    ws1.write_formula(rows1, 16, '=SUM(Q{0}:Q{1})'.format(rows + 1, rows1 - 1), option16)
    ws1.write_formula(rows1, 22, '=SUM(W{0}:W{1})'.format(rows + 1, rows1 - 1), option16)
    ws1.write_formula(rows1, 25, '=SUM(Z{0}:Z{1})'.format(rows + 1, rows1 - 1), option16)
    ws1.write(rows1, 3, None, option17)
    ws1.write(rows1, 5, None, option18)
    ws1.write(rows1, 15, None, option17)
    ws1.write(rows1, 17, None, option18)
    ws1.write(rows1, 21, None, option17)
    ws1.write(rows1, 23, None, option18)
    ws1.write(rows1, 24, None, option17)
    ws1.write(rows1, 26, None, option18)
    ws1.write_formula('Q{}'.format(d4), '=Q{0}'.format(rows1 + 1), option9)
    ws1.write_formula('W{}'.format(d4), '=W{0}'.format(rows1 + 1), option9)
    ws1.write_formula('Z{}'.format(d4), '=Z{0}'.format(rows1 + 1), option9)


def subTotal2(rows, rows1, d1):
    d2 = codeElement(int(d1))
    d3 = "Subtotal " + d1 + "  " + d2
    d4 = codeElement2(int(d1))
    ws1.set_row(rows1, 24.75, )
    for i in [rows1]:  # vary rows
        for j in [3, 4, 15, 16, 21, 22, 24, 25]:
            # for j in [3,4,12,13,15,16,18,19,21,22,24,25]:
            ws1.write(i - 1, j, None, option_bottom)
            ws1.write(i + 1, j, None, option_top)
    for i in [rows1]:  # vary rows
        # for j in [5,14,20]:
        for j in [5, 17, 23, 26]:
            # for j in [5,14,17,20,23,26]:
            ws1.write(i - 1, j, None, option_corner_bottomRight)
            ws1.write(i + 1, j, None, option_corner_topRight)
    ws1.write(rows1, 4, d3, option19)
    ws1.write_formula(rows1, 16, '=SUM(Q{0}:Q{1})'.format(rows + 1, rows1 - 1), option16)
    ws1.write_formula(rows1, 22, '=SUM(W{0}:W{1})'.format(rows + 1, rows1 - 1), option16)
    ws1.write_formula(rows1, 25, '=SUM(Z{0}:Z{1})'.format(rows + 1, rows1 - 1), option16)
    ws1.write(rows1, 3, None, option17)
    ws1.write(rows1, 5, None, option18)
    ws1.write(rows1, 15, None, option17)
    ws1.write(rows1, 17, None, option18)
    ws1.write(rows1, 21, None, option17)
    ws1.write(rows1, 23, None, option18)
    ws1.write(rows1, 24, None, option17)
    ws1.write(rows1, 26, None, option18)


def sumaryLine(rows):
    rows = rows - 1
    rowTop(rows + 1)
    ws1.set_row(rows + 2, 22.5, )
    for i in [1, 3]:
        ws1.set_row(rows + i, 6, )
    for i in [rows + 1]:  # vary rows
        for j in [14, 17, 20, 23, 26]:
            ws1.write(i, j, None, option_corner_topRight)
    for i in [rows + 2]:  # vary rows
        for j in [14, 17, 20, 23, 26]:
            ws1.write(i, j, None, option_right)
    rowTop(rows + 4)  # vary rows
    for i in [rows + 3]:  # vary rows
        for j in [14, 17, 20, 23, 26]:  # fixed
            ws1.write(i, j, None, option_corner_bottomRight)


def sumaryLine2(rows, text):
    if text != "Grand Total Prices":
        d2 = codeElement(int(text))
        d3 = text + " " + d2
    rows = rows - 1
    rowTop(rows + 1)
    ws1.set_row(rows + 2, 22.5, )
    for i in [1, 3]:
        ws1.set_row(rows + i, 6, )

    for i in [rows + 1]:  # vary rows
        for j in [2, 5, 14, 17, 20, 23, 26]:
            # for j in [2,5,8,11,14,17,20,23,26]:
            ws1.write(i, j, None, option_corner_topRight)
    for i in [rows + 2]:  # vary rows
        for j in [2, 14, 17, 20, 23, 26]:
            ws1.write(i, j, None, option_right)
    rowTop(rows + 4)  # vary rows
    for i in [rows + 3]:  # vary rows
        for j in [2, 14, 17, 20, 23, 26]:  # fixed
            ws1.write(i, j, None, option_corner_bottomRight)
    for i in [3, 4, 15, 16, 21, 22, 24, 25]:
        ws1.write(rows + 1, i, None, option21)
        ws1.write(rows + 3, i, None, option24)
    for i in [3, 15, 21, 24]:
        ws1.write(rows + 2, i, None, option24)
    for i in [5, 17, 23, 26]:
        ws1.write(rows + 1, i, None, option22)
        ws1.write(rows + 2, i, None, option25)
        ws1.write(rows + 3, i, None, option25)

    if text == "Grand Total Prices":
        ws1.write(rows + 2, 4, text, option20)
        ws1.write_formula(rows + 2, 16, '=Q24+Q50', option23)
        ws1.write_formula(rows + 2, 22, '=W24+W50', option23)
        ws1.write_formula(rows + 2, 25, '=Z24+Z50', option23)
    elif text == "1":
        ws1.write(rows + 2, 4, d3, option20)
        ws1.write_formula(rows + 2, 16, '=Q24', option23)
        ws1.write_formula(rows + 2, 22, '=W24', option23)
        ws1.write_formula(rows + 2, 25, '=Z24', option23)
    elif text == "2":
        ws1.write(rows + 2, 4, d3, option20)
        ws1.write_formula(rows + 2, 16, '=Q50', option23)
        ws1.write_formula(rows + 2, 22, '=W50', option23)
        ws1.write_formula(rows + 2, 25, '=Z50', option23)

def writeDicUnderline(k):
    for i, j in k.items():
        d1 = codeElement(i)  # fixed
        ws1.write(j, 1, i, option)
        ws1.write(j, 4, d1, option1)


def writeDicBold(k):
    for i, j in k.items():
        d1 = codeElement(i)  # fixed
        ws1.write(j, 1, i, option)
        ws1.write(j, 4, d1, option4)


def writeDicNormal(k):
    for i, j in k.items():
        d1 = codeElement(i)  # fixed
        ws1.write(j, 1, i, option6)
        ws1.write(j, 4, d1, option8)


def checkParameter(parameter):
    storageType = parameter.StorageType
    if (storageType.ToString() == "ElementId"):
        value = parameter.AsValueString()
    return value


def formworkColumn(width, heigth, length):
    area = (width + heigth) * 2 * length
    return area


def formworkBeams(width, heigth, cutlength):
    area = (width + 2 * heigth) * cutlength
    return area


def formworkGroundBeams(heigth, cutlength):
    area = 2 * heigth * cutlength
    return area


def formworkDropPanel(coreThick, perimeter):
    area = coreThick * perimeter
    return area


def footing():
    foundations = DB.FilteredElementCollector(doc).OfCategory(DB.BuiltInCategory.OST_StructuralFoundation) \
        .WhereElementIsNotElementType() \
        .ToElements()

    beams = DB.FilteredElementCollector(doc).OfCategory(DB.BuiltInCategory.OST_StructuralFraming) \
        .WhereElementIsNotElementType() \
        .ToElements()

    rebar = DB.FilteredElementCollector(doc).OfCategory(DB.BuiltInCategory.OST_Rebar) \
        .WhereElementIsNotElementType() \
        .ToElements()

    out3 = []
    # rowHeadStart = 15
    rowHeadEnd = 55
    # print("rowHeadStart = {}".format(rowHeadStart))
    # print("rowHeadEnd = {}".format(rowHeadEnd))
    for b2 in beams:

        q2 = []
        try:
            checkGroundBeam = b2.LookupParameter("Ground Beam (or not)").AsInteger()
        except:
            checkGroundBeam = 0

        # print ("checkGroundBeam = {}".format(checkGroundBeam))
        if checkGroundBeam == 1:
            for j in b2.Parameters:
                if j.IsShared and j.Definition.Name == "Excavation Soil":
                    # excavationSoil = (b2.get_Parameter(j.GUID).AsDouble()) * 0.028317
                    excavationSoil = UnitUtils.ConvertFromInternalUnits(b2.get_Parameter(j.GUID).AsDouble(),
                                                                        UnitTypeId.CubicMeters)
                    # UnitUtils.ConvertFromInternalUnits(e.get_Parameter(BuiltInParameter.HOST_VOLUME_COMPUTED).AsDouble(), UnitTypeId.CubicMeters)
                if j.IsShared and j.Definition.Name == "Backfill Soil":
                    # backfillSoil = (b2.get_Parameter(j.GUID).AsDouble()) * 0.028317
                    backfillSoil = UnitUtils.ConvertFromInternalUnits(b2.get_Parameter(j.GUID).AsDouble(),
                                                                      UnitTypeId.CubicMeters)
                if j.IsShared and j.Definition.Name == "Disposal Soil":
                    # disposalSoil = (b2.get_Parameter(j.GUID).AsDouble()) * 0.028317
                    disposalSoil = UnitUtils.ConvertFromInternalUnits(b2.get_Parameter(j.GUID).AsDouble(),
                                                                      UnitTypeId.CubicMeters)
                if j.IsShared and j.Definition.Name == "Volume Concrete":
                    # volumeConcrete = (b2.get_Parameter(j.GUID).AsDouble()) * 0.028317
                    volumeConcrete = UnitUtils.ConvertFromInternalUnits(b2.get_Parameter(j.GUID).AsDouble(),
                                                                        UnitTypeId.CubicMeters)
                if j.IsShared and j.Definition.Name == "Volume Lean":
                    # volumeLean = (b2.get_Parameter(j.GUID).AsDouble()) * 0.028317
                    volumeLean = UnitUtils.ConvertFromInternalUnits(b2.get_Parameter(j.GUID).AsDouble(),
                                                                    UnitTypeId.CubicMeters)
                if j.IsShared and j.Definition.Name == "Volume Compaction Sand":
                    # volumeCompSand = (b2.get_Parameter(j.GUID).AsDouble()) * 0.028317
                    volumeCompSand = UnitUtils.ConvertFromInternalUnits(b2.get_Parameter(j.GUID).AsDouble(),
                                                                        UnitTypeId.CubicMeters)
                if j.IsShared and j.Definition.Name == "Formwork":
                    # formwork = (b2.get_Parameter(j.GUID).AsDouble()) * 0.092903
                    formwork = UnitUtils.ConvertFromInternalUnits(b2.get_Parameter(j.GUID).AsDouble(),
                                                                  UnitTypeId.SquareMeters)

            q2.append(excavationSoil)  # Excavation Soil 0
            q2.append(backfillSoil)  # Backfill Soil 1
            q2.append(disposalSoil)  # Disposal Soil 2
            q2.append(volumeConcrete)  # Volume Concrete 3
            q2.append(volumeLean)  # Volume Lean 4
            q2.append(volumeCompSand)  # Volume Compaction Sand 5
            q2.append(formwork)  # Formwork 6

            out3.append(q2)
    # print("out3 = {}".format(out3))

    if len(foundations) == 0 and len(out3) == 0:
        rowsUp = rowHeadEnd + 1
        status = 1
        return rowsUp, status
    else:
        out = []
        out1 = []
        out2 = []
        rows = 0
        familyType = []
        for e in foundations:
            p = []
            id = e.Id
            name = e.Name
            nameFooting = e.LookupParameter("Name Footing").AsString()
            family = e.get_Parameter(BuiltInParameter.ELEM_FAMILY_PARAM)
            value = checkParameter(family)
            # location = e.LookupParameter("Location").AsString()
            if value != "Wall Foundation" and value != "Foundation Slab":
                p.append(id)  # Id 0
                p.append(name)  # Name 1
                p.append(nameFooting)  # Name Footing 2
                p.append(value)  # family Name 3
                # p.append(location)  # Loction 4

                out.append(p)
        # print("out= {}".format(out))

        for e1 in foundations:
            p1 = []

            family1 = e1.get_Parameter(BuiltInParameter.ELEM_FAMILY_PARAM)
            value1 = checkParameter(family1)
            if value1 != "Wall Foundation" and value1 != "Foundation Slab":
                id1 = e1.Id
                name1 = e1.Name

                if bool(re.match("Pile", name1)) == True:
                    # p1.append(c)  # Code_Pile 0
                    # pileLength = (e1.LookupParameter("Pile Length").AsDouble()) * 0.3047999
                    pileLength = UnitUtils.ConvertFromInternalUnits(e1.LookupParameter("Pile Length").AsDouble(),
                                                                    UnitTypeId.Meters)
                    # print("pileLength= {}".format(round(pileLength,2)))
                    combilePile = name1 + "x" + str(round(pileLength, 2)) + " m."
                    nc = 1
                    p1.append(combilePile)  # Type of Pile 0
                    p1.append(nc)  # count Pile 1
                    if bool(re.match("Pile I", name1)) == True:
                        p1.append(1021)  # Code Pile 2
                    elif bool(re.match("Pile Square", name1)) == True:
                        p1.append(1022)  # Code Pile 2
                    elif bool(re.match("Pile Bored", name1)) == True:
                        p1.append(1023)  # Code Pile 2
                    elif bool(re.match("Pile Span", name1)) == True:
                        p1.append(1024)  # Code Pile 2    
                    elif bool(re.match("Pile Hexagon", name1)) == True:
                        p1.append(1025)  # Code Pile 2 
                    out1.append(p1)
        # print("out1= {}".format(out1))

        for e2 in foundations:
            p2 = []
            name1 = e2.Name
            family2 = e2.get_Parameter(BuiltInParameter.ELEM_FAMILY_PARAM)
            value2 = checkParameter(family2)
            if value2 != "Wall Foundation" and value2 != "Foundation Slab" and bool(re.match("Pile", name1)) == False:
                nameFooting = e2.LookupParameter("Name Footing").AsString()
                # print("nameFooting 0 = {}".format(nameFooting))
                if nameFooting == "" or nameFooting is None:
                    nameFooting = "None Data"
                size = e2.LookupParameter("Size").AsString()
                if size == "" or size is None:
                    size = "None Data"
                location = e2.LookupParameter("Location").AsString()
                if location == "" or location is None:
                    location = "None Data"
                strengthCon = e2.LookupParameter("Strength Concrete").AsString()
                if strengthCon == "" or strengthCon is None:
                    strengthCon = "None Data"

                for j in e2.Parameters:
                    if j.IsShared and j.Definition.Name == "Excavation Soil":
                        # excavationSoil = (e2.get_Parameter(j.GUID).AsDouble()) * 0.028317
                        excavationSoil = UnitUtils.ConvertFromInternalUnits(e2.get_Parameter(j.GUID).AsDouble(),
                                                                            UnitTypeId.CubicMeters)
                    if j.IsShared and j.Definition.Name == "Backfill Soil":
                        # backfillSoil = (e2.get_Parameter(j.GUID).AsDouble()) * 0.028317
                        backfillSoil = UnitUtils.ConvertFromInternalUnits(e2.get_Parameter(j.GUID).AsDouble(),
                                                                          UnitTypeId.CubicMeters)
                    if j.IsShared and j.Definition.Name == "Disposal Soil":
                        # disposalSoil = (e2.get_Parameter(j.GUID).AsDouble()) * 0.028317
                        disposalSoil = UnitUtils.ConvertFromInternalUnits(e2.get_Parameter(j.GUID).AsDouble(),
                                                                          UnitTypeId.CubicMeters)
                    if j.IsShared and j.Definition.Name == "Volume Concrete":
                        # volumeConcrete = (e2.get_Parameter(j.GUID).AsDouble()) * 0.028317
                        volumeConcrete = UnitUtils.ConvertFromInternalUnits(e2.get_Parameter(j.GUID).AsDouble(),
                                                                            UnitTypeId.CubicMeters)
                    if j.IsShared and j.Definition.Name == "Volume Lean":
                        # volumeLean = (e2.get_Parameter(j.GUID).AsDouble()) * 0.028317
                        volumeLean = UnitUtils.ConvertFromInternalUnits(e2.get_Parameter(j.GUID).AsDouble(),
                                                                        UnitTypeId.CubicMeters)
                    if j.IsShared and j.Definition.Name == "Volume Compaction Sand":
                        # volumeCompSand = (e2.get_Parameter(j.GUID).AsDouble()) * 0.028317
                        volumeCompSand = UnitUtils.ConvertFromInternalUnits(e2.get_Parameter(j.GUID).AsDouble(),
                                                                            UnitTypeId.CubicMeters)
                    if j.IsShared and j.Definition.Name == "Formwork":
                        # formwork = (e2.get_Parameter(j.GUID).AsDouble()) * 0.092903
                        formwork = UnitUtils.ConvertFromInternalUnits(e2.get_Parameter(j.GUID).AsDouble(),
                                                                      UnitTypeId.SquareMeters)

                p2.append(nameFooting)  # Name Footing 0
                p2.append(size)  # size 1
                p2.append(location)  # Location 2
                p2.append(strengthCon)  # Stength Concrete 3
                p2.append(excavationSoil)  # Excavation Soil 4
                p2.append(backfillSoil)  # Backfill Soil 5
                p2.append(disposalSoil)  # Disposal Soil 6
                p2.append(volumeConcrete)  # Volume Concrete 7
                p2.append(volumeLean)  # Volume Lean 8
                p2.append(volumeCompSand)  # Volume Compaction Sand 9
                p2.append(formwork)  # Formwork 10

                out2.append(p2)
        # print("out2= {}".format(out2))

        total_ExcavationSoil_Footing = round(sum(float(c[4]) for c in out2), 2)
        # print ("total_ExcavationSoil_Footing = {}".format(total_ExcavationSoil_Footing))
        total_BackfillSoil_Footing = round(sum(float(c[5]) for c in out2), 2)
        # print ("total_BackfillSoil_Footing = {}".format(total_BackfillSoil_Footing))
        total_DisposalSoil_Footing = round(sum(float(c[6]) for c in out2), 2)
        # print ("total_DisposalSoil_Footing = {}".format(total_DisposalSoil_Footing))
        total_VolConcrete_Footing = round(sum(float(c[7]) for c in out2), 2)
        # print ("total_VolConcrete_Footing = {}".format(total_VolConcrete_Footing))
        total_volLean_Footing = round(sum(float(c[8]) for c in out2), 2)
        # print ("total_volLean_Footing = {}".format(total_volLean_Footing))
        total_volCompacSand_Footing = round(sum(float(c[9]) for c in out2), 2)
        # print ("total_volCompacSand_Footing = {}".format(total_volCompacSand_Footing))
        total_formwork_Footing = round(sum(float(c[10]) for c in out2), 2)
        # print ("total_formwork_Footing = {}".format(total_formwork_Footing))

        total_ExcavationSoil_Beam = round(sum(float(d[0]) for d in out3), 2)
        total_BackfillSoil_Beam = round(sum(float(d[1]) for d in out3), 2)
        total_DisposalSoil_Beam = round(sum(float(d[2]) for d in out3), 2)
        # total_VolConcrete_Beam = sum(float(c[3]) for c in out3)
        total_volLean_Beam = round(sum(float(d[4]) for d in out3), 2)
        total_volCompacSand_Beam = round(sum(float(d[5]) for d in out3), 2)
        # total_formwork_Beam = sum(float(c[10]) for c in out3)

        sort_Pile1 = (sorted(set([e1[0] for e1 in out1])))
        # print (sort_Pile1)
        sort_CodePile1 = (sorted(set([e1[2] for e1 in out1])))
        # print (sort_CodePile1)

        sort_NameFooting = (sorted(set([e[0] for e in out2])))  # Sorted Name Footing
        # print(sort_NameFooting)
        sort_Strength = (sorted(set([e[3] for e in out2])))  # Sorted Strength Concrete
        # print(sort_Strength)
        sort_Location = (sorted(set([e[2] for e in out2])))  # Sorted Location
        # print(sort_Strength)

        nstrength = 0
        n = 0
        for i in sort_Strength:
            for j in sort_NameFooting:
                n = len([v for v in out2 if v[3] == i and v[0] == j])
                # print(n)
                if n > 0:
                    nstrength = nstrength + 1
        # print (nstrength)
        if len(out2) != 0 and len(out3) != 0:
            rows = rowHeadEnd + 31
        elif len(out2) == 0 or len(out3) == 0:
            rows = rowHeadEnd + 26
        if len(out2) != 0 and len(out1) != 0:
            rowsConFooting = nstrength + 3 * len(sort_Strength) + 2
            rowsFooting = 3 * len(sort_CodePile1) + len(sort_Pile1) + rows + 5
            rowsConFinish = rowsFooting + 3 + rowsConFooting
            rowsUp = rowsConFinish + nstrength + 8
        elif len(out2) != 0 and len(out1) == 0:
            rowsConFooting = nstrength + 3 * len(sort_Strength) + 2
            rowsFooting = rows + 1
            rowsConFinish = rowsFooting + 3 + rowsConFooting
            rowsUp = rowsConFinish + nstrength + 8
        else:
            rowsUp = rowHeadEnd + 27
        # print ("rowsUp = {}".format(rowsUp))

        rowCornerTopRight(rowHeadEnd)
        # rowRight(rowHeadEnd, rowsUp)
        rowRight(rowHeadEnd + 1, rowsUp)
        # sumaryLine(rowsUp)
        rowsStart = rowHeadEnd + 7
        d1 = codeElement(1)  # fixed
        ws1.write(rowHeadEnd + 1, 1, 1, option)
        ws1.write(rowHeadEnd + 1, 4, d1, option1)

        k = {101: rowHeadEnd + 3}  # fixed
        # k = {101: 53}  # fixed

        for i, j in k.items():
            d2 = codeElement(i)
            ws1.write(j, 1, i, option)
            ws1.write(j, 4, d2, option4)

        if len(out2) != 0 and len(out3) != 0:
            k1 = [1011, 1012, 1013, 1014, 1015]  # fixed
            j = rowHeadEnd
            for i in k1:
                j = j + 5
                d3 = codeElement(i)
                ws1.write(j, 1, i, option)
                ws1.write(j, 4, d3, option4)

            k2 = [10111, 10112]  # fixed
            j = rowHeadEnd + 6
            for i in k2:
                j = j + 1
                d4 = codeElement(i)
                ws1.write(j, 1, i, option5)
                ws1.write(j, 4, d4, option6)
                ws1.write(j, 7, "m3", option8)
                ws1.write_formula(j, 16, '=K{0} * N{1}'.format(j + 1, j + 1), option9)
                ws1.write_formula(j, 22, '=K{0} * T{1}'.format(j + 1, j + 1), option9)
                ws1.write_formula(j, 25, '= Q{0} + W{1}'.format(j + 1, j + 1), option9)
                ws1.write_formula(j, 13, "", option15)
                ws1.write_formula(j, 19, "", option15)

            k3 = [10121, 10122]  # fixed
            j = rowHeadEnd + 11
            for i in k3:
                j = j + 1
                d5 = codeElement(i)
                ws1.write(j, 1, i, option5)
                ws1.write(j, 4, d5, option6)
                ws1.write(j, 7, "m3", option8)
                ws1.write_formula(j, 16, '=K{0} * N{1}'.format(j + 1, j + 1), option9)
                ws1.write_formula(j, 22, '=K{0} * T{1}'.format(j + 1, j + 1), option9)
                ws1.write_formula(j, 25, '= Q{0} + W{1}'.format(j + 1, j + 1), option9)
                ws1.write_formula(j, 13, "", option15)
                ws1.write_formula(j, 19, "", option15)

            k4 = [10131, 10132]  # fixed
            j = rowHeadEnd + 16
            for i in k4:
                j = j + 1
                d6 = codeElement(i)
                ws1.write(j, 1, i, option5)
                ws1.write(j, 4, d6, option6)
                ws1.write(j, 7, "m3", option8)
                ws1.write_formula(j, 16, '=K{0} * N{1}'.format(j + 1, j + 1), option9)
                ws1.write_formula(j, 22, '=K{0} * T{1}'.format(j + 1, j + 1), option9)
                ws1.write_formula(j, 25, '= Q{0} + W{1}'.format(j + 1, j + 1), option9)
                ws1.write_formula(j, 13, "", option15)
                ws1.write_formula(j, 19, "", option15)

            k5 = [10141, 10142]  # fixed
            j = rowHeadEnd + 21
            for i in k5:
                j = j + 1
                d7 = codeElement(i)
                ws1.write(j, 1, i, option5)
                ws1.write(j, 4, d7, option6)
                ws1.write(j, 7, "m3", option8)
                ws1.write_formula(j, 16, '=K{0} * N{1}'.format(j + 1, j + 1), option9)
                ws1.write_formula(j, 22, '=K{0} * T{1}'.format(j + 1, j + 1), option9)
                ws1.write_formula(j, 25, '= Q{0} + W{1}'.format(j + 1, j + 1), option9)
                ws1.write_formula(j, 13, "", option15)
                ws1.write_formula(j, 19, "", option15)

            k6 = [10151, 10152]  # fixed
            j = rowHeadEnd + 26
            for i in k6:
                j = j + 1
                d8 = codeElement(i)
                ws1.write(j, 1, i, option5)
                ws1.write(j, 4, d8, option6)
                ws1.write(j, 7, "m3", option8)
                ws1.write_formula(j, 16, '=K{0} * N{1}'.format(j + 1, j + 1), option9)
                ws1.write_formula(j, 22, '=K{0} * T{1}'.format(j + 1, j + 1), option9)
                ws1.write_formula(j, 25, '= Q{0} + W{1}'.format(j + 1, j + 1), option9)
                ws1.write_formula(j, 13, "", option15)
                ws1.write_formula(j, 19, "", option15)

            ws1.write(rowHeadEnd + 7, 10, total_ExcavationSoil_Footing, option9)  # fixed
            ws1.write(rowHeadEnd + 8, 10, total_ExcavationSoil_Beam, option9)  # fixed
            ws1.write(rowHeadEnd + 12, 10, total_BackfillSoil_Footing, option9)  # fixed
            ws1.write(rowHeadEnd + 13, 10, total_BackfillSoil_Beam, option9)  # fixed
            ws1.write(rowHeadEnd + 17, 10, total_DisposalSoil_Footing, option9)  # fixed
            ws1.write(rowHeadEnd + 18, 10, total_DisposalSoil_Beam, option9)  # fixed
            ws1.write(rowHeadEnd + 22, 10, total_volLean_Footing, option9)  # fixed
            ws1.write(rowHeadEnd + 23, 10, total_volLean_Beam, option9)  # fixed
            ws1.write(rowHeadEnd + 27, 10, total_volCompacSand_Footing, option9)  # fixed
            ws1.write(rowHeadEnd + 28, 10, total_volCompacSand_Beam, option9)  # fixed
            # rows = 75
            subTotal(rowsStart, rowHeadEnd + 30, "101")


        elif len(out2) != 0 and len(out3) == 0:
            k1 = [1011, 1012, 1013, 1014, 1015]  # fixed
            j = rowHeadEnd + 1
            for i in k1:
                j = j + 4
                d3 = codeElement(i)
                ws1.write(j, 1, i, option)
                ws1.write(j, 4, d3, option4)

            k2 = [10111, 10121, 10131, 10141, 10151]  # fixed
            j = rowHeadEnd + 3
            for i in k2:
                j = j + 4
                d4 = codeElement(i)
                ws1.write(j, 1, i, option5)
                ws1.write(j, 4, d4, option6)
                ws1.write(j, 7, "m3", option8)
                ws1.write_formula(j, 16, '=K{0} * N{1}'.format(j + 1, j + 1), option9)
                ws1.write_formula(j, 22, '=K{0} * T{1}'.format(j + 1, j + 1), option9)
                ws1.write_formula(j, 25, '= Q{0} + W{1}'.format(j + 1, j + 1), option9)
                ws1.write_formula(j, 13, "", option15)
                ws1.write_formula(j, 19, "", option15)

            # for i in [57]:
            for i in [rowHeadEnd + 7]:
                ws1.write(i, 10, total_ExcavationSoil_Footing, option9)  # fixed
                ws1.write(i + 4, 10, total_BackfillSoil_Footing, option9)  # fixed
                ws1.write(i + 8, 10, total_DisposalSoil_Footing, option9)  # fixed
                ws1.write(i + 12, 10, total_volLean_Footing, option9)  # fixed
                ws1.write(i + 16, 10, total_volCompacSand_Footing, option9)  # fixed
            # rows = 70
            subTotal(rowsStart, rowHeadEnd + 25, "101")
            # subTotal(75,"101")

        elif len(out2) == 0 and len(out3) != 0:
            k1 = [1011, 1012, 1013, 1014, 1015]  # fixed
            j = rowHeadEnd + 1
            for i in k1:
                j = j + 4
                d3 = codeElement(i)
                ws1.write(j, 1, i, option)
                ws1.write(j, 4, d3, option4)

            k2 = [10112, 10122, 10132, 10142, 10152]  # fixed
            j = rowHeadEnd + 3
            for i in k2:
                j = j + 4
                d4 = codeElement(i)
                ws1.write(j, 1, i, option5)
                ws1.write(j, 4, d4, option6)
                ws1.write(j, 7, "m3", option8)
                ws1.write_formula(j, 16, '=K{0} * N{1}'.format(j + 1, j + 1), option9)
                ws1.write_formula(j, 22, '=K{0} * T{1}'.format(j + 1, j + 1), option9)
                ws1.write_formula(j, 25, '= Q{0} + W{1}'.format(j + 1, j + 1), option9)
                ws1.write_formula(j, 13, "", option15)
                ws1.write_formula(j, 19, "", option15)

            # for i in [57]:
            for i in [rowHeadEnd + 7]:
                ws1.write(i, 10, total_ExcavationSoil_Beam, option9)  # fixed
                ws1.write(i + 4, 10, total_BackfillSoil_Beam, option9)  # fixed
                ws1.write(i + 8, 10, total_DisposalSoil_Beam, option9)  # fixed
                ws1.write(i + 12, 10, total_volLean_Beam, option9)  # fixed
                ws1.write(i + 16, 10, total_volCompacSand_Beam, option9)  # fixed
            # rows = 70
            subTotal(rowsStart, rowHeadEnd + 25, "101")
            # subTotal(75,"101")

        if len(out1) != 0:
            total_Pile = 0
            rowsStart = rows + 1 + 4
            k = {102: rowsStart - 4}  # FOOTING WORK
            for i, j in k.items():
                d2 = codeElement(i)
                ws1.write(j, 1, i, option)
                ws1.write(j, 4, d2, option4)
            rows = rows + 2
            for i in sort_CodePile1:
                rows = rows + 1
                d2 = codeElement(int(i))
                ws1.write(rows, 1, int(i), option)
                ws1.write(rows, 4, d2, option4)
                rows = rows + 2
                t1 = 0.0
                change = 0.0

                for j in sort_Pile1:
                    t = [v for v in out1 if v[2] == i and v[0] == j]
                    total_Pile = sum((c[1]) for c in t)
                    if len(t) != 0:
                        t1 = t1 + 0.001
                        change = float(i) + t1
                        ws1.write(rows, 1, change, option13)
                        ws1.write(rows, 4, j, option6)
                        ws1.write(rows, 7, 'No.', option8)
                        ws1.write(rows, 10, total_Pile, option14)
                        ws1.write_formula(rows, 16, '=K{0} * N{1}'.format(rows + 1, rows + 1), option9)
                        ws1.write_formula(rows, 22, '=K{0} * T{1}'.format(rows + 1, rows + 1), option9)
                        ws1.write_formula(rows, 25, '= Q{0} + W{1}'.format(rows + 1, rows + 1), option9)
                        ws1.write_formula(rows, 13, "", option15)
                        ws1.write_formula(rows, 19, "", option15)
                        rows = rows + 1
            subTotal(rowsStart, rowsFooting - 2, "102")

        k = {103: rowsFooting, 1031: rowsFooting + 2}  # FOOTING WORK
        for i, j in k.items():
            d2 = codeElement(i)
            ws1.write(j, 1, i, option)
            ws1.write(j, 4, d2, option4)

        rowsConFoStart = rowsFooting + 4
        k = {10311: rowsConFoStart}  # concrete
        for i, j in k.items():
            d2 = codeElement(i)
            ws1.write(j, 1, i, option)
            ws1.write(j, 4, d2, option4)
        t1 = 0.0
        t2 = 0
        change = 0.0
        rowsStart = rowsConFoStart + 4

        # print("out2= {}".format(out2))
        for i in sort_Strength:
            r1 = "Reinfored concrete strength {} as specified :".format(i)
            ws1.write(rowsConFoStart + 2 + t2, 4, r1, option1)
            rowsTT = rowsConFoStart + 4 + t2
            total_vol = 0.0
            GrandTotal_vol = 0.0
            for j in sort_NameFooting:
                # for k in sort_Location:
                t = [v for v in out2 if v[3] == i and v[0] == j]
                # print("t = {}".format(t))
                total_vol = round(sum(float(c[7]) for c in t), 2)
                GrandTotal_vol = GrandTotal_vol + total_vol
                if len(t) != 0:
                    t1 = t1 + 0.001
                    change = 10311 + t1
                    # print("len(t) = {}".format(len(t)))
                    # print("t[0][1] = {}".format(t[0][1]))
                    k = j + "-" + t[0][1] + " - " + "(" + str(len(t)) + " Nos" + ")"
                    ws1.write(rowsTT, 1, change, option13)
                    ws1.write(rowsTT, 4, k, option6)
                    ws1.write(rowsTT, 7, 'm3', option8)
                    ws1.write(rowsTT, 10, total_vol, option9)
                    ws1.write_formula(rowsTT, 16, '=K{0} * N{1}'.format(rowsTT + 1, rowsTT + 1), option9)
                    ws1.write_formula(rowsTT, 22, '=K{0} * T{1}'.format(rowsTT + 1, rowsTT + 1), option9)
                    ws1.write_formula(rowsTT, 25, '= Q{0} + W{1}'.format(rowsTT + 1, rowsTT + 1), option9)
                    ws1.write_formula(rowsTT, 13, "", option15)
                    ws1.write_formula(rowsTT, 19, "", option15)
                    rowsTT = rowsTT + 1
                    t2 = t2 + 1
            t2 = t2 + 3
        k = {10312: (rowsConFinish + 1)}  # concrete
        for i, j in k.items():
            d2 = codeElement(i)
            ws1.write(j, 1, i, option)
            ws1.write(j, 4, d2, option4)
        r2 = "Sawn formwork to sided of :"
        ws1.write((rowsConFinish + 3), 4, r2, option1)
        rowsTT = rowsConFinish + 5
        total_formwork = 0.0
        t1 = 0.0
        grandTotal_formwork = 0.0
        for i in sort_NameFooting:
            t = [v for v in out2 if v[0] == i]
            total_formwork = round(sum(float(c[10]) for c in t), 2)
            grandTotal_formwork = grandTotal_formwork + total_formwork
            if len(t) != 0:
                t1 = t1 + 0.001
                change = 10312 + t1
                k = i + "-" + t[0][1] + " - " + "(" + str(len(t)) + " Nos" + ")"
                ws1.write(rowsTT, 1, change, option13)
                ws1.write(rowsTT, 4, k, option6)
                ws1.write(rowsTT, 7, 'm2', option8)
                ws1.write(rowsTT, 10, total_formwork, option9)
                ws1.write_formula(rowsTT, 16, '=K{0} * N{1}'.format(rowsTT + 1, rowsTT + 1), option9)
                ws1.write_formula(rowsTT, 22, '=K{0} * T{1}'.format(rowsTT + 1, rowsTT + 1), option9)
                ws1.write_formula(rowsTT, 25, '= Q{0} + W{1}'.format(rowsTT + 1, rowsTT + 1), option9)
                ws1.write_formula(rowsTT, 13, "", option15)
                ws1.write_formula(rowsTT, 19, "", option15)
                rowsTT = rowsTT + 1

        out4 = []
        for e4 in rebar:
            p4 = []
            hostCat = e4.get_Parameter(BuiltInParameter.REBAR_HOST_CATEGORY).AsInteger()
            if hostCat == 6:
                partition = e4.get_Parameter(BuiltInParameter.NUMBER_PARTITION_PARAM).AsString()
                if partition == "" or partition is None:
                    partition = "None Defined"
                typeRebar = e4.get_Parameter(BuiltInParameter.ELEM_TYPE_PARAM)
                value4 = checkParameter(typeRebar)
                gradeRebar = e4.LookupParameter("Grade Rebar").AsString()
                group = e4.LookupParameter("Group").AsString()
                if group is None or group == "":
                    group = "None Defined"
                location = e4.LookupParameter("Location").AsString()
                if location is None or location == "":
                    location = "None Defined"
                # volRebar = (e4.get_Parameter(BuiltInParameter.REINFORCEMENT_VOLUME).AsDouble())* 0.028317
                volRebar = UnitUtils.ConvertFromInternalUnits(
                    e4.get_Parameter(BuiltInParameter.REINFORCEMENT_VOLUME).AsDouble(), UnitTypeId.CubicMeters)
                weigthRebar = volRebar * 7850
                # totalLengthRebar = (e4.get_Parameter(BuiltInParameter.REBAR_ELEM_TOTAL_LENGTH).AsDouble())* 0.3048
                totalLengthRebar = UnitUtils.ConvertFromInternalUnits(
                    e4.get_Parameter(BuiltInParameter.REBAR_ELEM_TOTAL_LENGTH).AsDouble(), UnitTypeId.Meters)

                p4.append(hostCat)  # Name host Category 0
                p4.append(partition)  # Name Footing Rebar 1
                p4.append(value4)  # Type Rebar 2
                p4.append(gradeRebar)  # Grade Rebar 3
                p4.append(group)  # Group Footing Rebar 4
                p4.append(location)  # Location Footing Rebar 5
                p4.append(volRebar)  # Volume Rebar 6
                p4.append(weigthRebar)  # Weigth Rebar 7
                p4.append(totalLengthRebar)  # Total Length Rebar 8

                out4.append(p4)
        # print("out4 = {}".format(out4))
        if len(out4) > 0:
            sort_Patition = (sorted(set([e[1] for e in out4])))  # Sorted Name Footing Rebar
            sort_TypeRebar = (sorted(set([e[2] for e in out4])))  # Sorted Type Rebar
            sort_GradeRebar = (sorted(set([e[3] for e in out4])))  # Sorted Location
            sort_Group = (sorted(set([e[4] for e in out4])))  # Sorted Group
            sort_Location = (sorted(set([e[5] for e in out4])))  # Sorted Location

            nstrength = 0
            n = 0
            for i in sort_GradeRebar:
                for j in sort_TypeRebar:
                    n = len([v for v in out4 if v[3] == i and v[2] == j])
                    if n > 0:
                        nstrength = nstrength + 1

            rowsUp1 = rowsUp - 2
            rowsRebarStart = rowsUp1 + 2
            rowsRebarEnd = nstrength + 3 * len(sort_GradeRebar) + 2
            rowsUp = rowsUp1 + rowsRebarEnd + 2
            rowRight(rowsUp1, rowsUp)

            # print ("rowsUp1 = {}".format(rowsUp1))
            # print ("rowsRebarStart  = {}".format(rowsRebarStart ))
            # print ("rowsRebarEnd = {}".format(rowsRebarEnd))
            # print ("rowsUp = {}".format(rowsUp))
            # rowsTT = rowsUp1
            writeDicBold({10313: rowsUp1})
            # print ("rowsTT = {}".format(rowsTT))
            change = 0.0
            t1 = 0.0
            t2 = 0
            total_wei = 0
            GrandTotal_wei = 0
            # for g in sort_Group:
            #     g1 = "Group of "+g
            #     ws1.write(rows)
            for i in sort_GradeRebar:
                # print ("rowsTT2 = {}".format(rowsTT))
                r1 = "Deformed steel rod reinforcement to {} as specified :".format(i)
                ws1.write(rowsUp1 + 2 + t2, 4, r1, option1)
                rowsTT = rowsUp1 + 4 + t2
                # print ("rowsTT3 = {}".format(rowsTT))
                for j in sort_TypeRebar:
                    t = [v for v in out4 if v[3] == i and v[2] == j]
                    # print (t)
                    total_wei = round(sum(float(c[7]) for c in t), 2)
                    if len(t) != 0:
                        t1 = t1 + 0.001
                        change = 10313 + t1
                        try:
                            k1 = "- " + j
                        except:
                            j = "None data"
                            k1 = "- " + j
                        # print("{} :{} = {:,.2f} kg.".format(change, k1, total_wei))
                        # print ("rowsTT6 = {}".format(rowsTT))
                        ws1.write(rowsTT, 1, change, option13)
                        ws1.write(rowsTT, 4, k1, option6)
                        ws1.write(rowsTT, 7, 'kg.', option8)
                        ws1.write(rowsTT, 10, total_wei, option9)
                        ws1.write_formula(rowsTT, 16, '=K{0} * N{1}'.format(rowsTT + 1, rowsTT + 1), option9)
                        ws1.write_formula(rowsTT, 22, '=K{0} * T{1}'.format(rowsTT + 1, rowsTT + 1), option9)
                        ws1.write_formula(rowsTT, 25, '= Q{0} + W{1}'.format(rowsTT + 1, rowsTT + 1), option9)
                        ws1.write_formula(rowsTT, 13, "", option15)
                        ws1.write_formula(rowsTT, 19, "", option15)
                        rowsTT = rowsTT + 1
                        GrandTotal_wei = GrandTotal_wei + total_wei
                        t2 = t2 + 1
                    # print ("rowsTT4 = {}".format(rowsTT))
                t2 = t2 + 3
                # print ("rowsTT5 = {}".format(rowsTT))
                # print ("t2 = {}".format(t2))
            # print ("t2 = {}".format(t2))
        # print ("sort_GradeRebar = {}".format(sort_GradeRebar))
        # print ("sort_TypeRebar = {}".format(sort_TypeRebar))

        subTotal(rowsStart, rowsUp - 2, "103")

        status = 0
        return rowsUp + 4, status


def column(rowsUp):
    rowsUp = rowsUp - 1
    # print("rowsUp = {}".format(rowsUp))
    columns = DB.FilteredElementCollector(doc).OfCategory(DB.BuiltInCategory.OST_StructuralColumns) \
        .WhereElementIsNotElementType() \
        .ToElements()

    rebar = DB.FilteredElementCollector(doc).OfCategory(DB.BuiltInCategory.OST_Rebar) \
        .WhereElementIsNotElementType() \
        .ToElements()

    out = []
    familyType = []
    for e in columns:
        p = []
        # id = e.Id
        # name = e.Name
        strType = e.StructuralMaterialType
        if strType == StructuralMaterialType.Concrete:
            id = e.Id
            name = e.Name
            level = e.get_Parameter(BuiltInParameter.SCHEDULE_LEVEL_PARAM)
            value = checkParameter(level)
            strMat = e.get_Parameter(BuiltInParameter.STRUCTURAL_MATERIAL_PARAM)
            value1 = checkParameter(strMat)
            length = (e.get_Parameter(BuiltInParameter.INSTANCE_LENGTH_PARAM).AsDouble()) * 0.3048
            volume = (e.get_Parameter(BuiltInParameter.HOST_VOLUME_COMPUTED).AsDouble()) * 0.028317
            nameColumn = e.LookupParameter("Name Column").AsString()
            # print (nameBeam)
            size = e.LookupParameter("Size").AsString()
            location = e.LookupParameter("Location").AsString()
            strengthCon = e.LookupParameter("Strength Concrete").AsString()
            # print (strengthCon)

            id2 = e.GetTypeId()
            if id2 == ElementId.InvalidElementId:
                familyType.append(None)
            else:
                familyType.append(doc.GetElement(id2))
            builtInParamType = BuiltInParameter.ALL_MODEL_TYPE_MARK
            for i in familyType:
                typeMark = i.get_Parameter(builtInParamType)
                try:
                    width = (i.LookupParameter("b").AsDouble()) * 0.3048
                    heigth = (i.LookupParameter("h").AsDouble()) * 0.3048
                except:
                    width = 0.0
                    heigth = 0.0
                finally:
                    area = formworkColumn(width, heigth, length)

            p.append(id)  # Id 0
            p.append(strType)  # Structural Type 1
            p.append(nameColumn)  # Name Column 2
            p.append(size)  # size 3
            p.append(location)  # Location(Level) 4
            p.append(strengthCon)  # Stength Concrete 5
            p.append(volume)  # Volume 6
            p.append(area)  # Formwork 7
            out.append(p)
    # print(out)
    check1 = [v for v in out if v[1] == StructuralMaterialType.Concrete]
    # print("check1 = {}".format(len(check1)))
    if len(check1) == 0:
        # print("check1-1 = {}".format(len(check1)))
        status = 1
        return rowsUp + 1, status

    else:
        check1 = [v for v in out if v[1] == StructuralMaterialType.Concrete]
        # print(check1)
        # print("check1-2 = {}".format(len(check1)))
        sort_NameColumn = (sorted(set([e[2] for e in check1])))  # Sorted Name Column
        sort_size = (sorted(set([e[3] for e in check1])))  # Sorted size
        sort_Level = (sorted(set([e[4] for e in check1])))  # Sorted Location
        # print(sort_Level)
        sort_Strength = (sorted(set([e[5] for e in check1])))  # Sorted Strength Concrete

        nstrength = 0
        n = 0
        for i in sort_Strength:
            for j in sort_NameColumn:
                for k in sort_size:
                    for l in sort_Level:
                        n = len([v for v in check1 if v[5] == i and v[2] == j and v[3] == k and v[4] == l])
                        # print(n)
                        if n > 0:
                            nstrength = nstrength + 1
        # print (nstrength)
        rowsUp1 = rowsUp
        rowsConColStart = rowsUp1 + 7
        rowsConEnd = nstrength + 3 * len(sort_Strength) + 6
        rowFormColStart = rowsUp1 + rowsConEnd + 3
        rowFormColEnd = nstrength + 7
        rowsUp = rowsUp1 + rowsConEnd + rowFormColEnd + 3

        rowRight(rowsUp1, rowsUp)

        rowsStart = rowsConColStart + 4
        # check1 = [v for v in out if v[1] == StructuralMaterialType.Concrete]
        # print(check1)
        if len(check1) != 0:
            writeDicUnderline({2: rowsUp1 + 1})
            writeDicBold({201: rowsUp1 + 3, 2011: rowsUp1 + 5, 20111: rowsUp1 + 7})
            t2 = 0
            change = 0.0
            t1 = 0.0
            for i in sort_Strength:
                r1 = "Reinfored concrete strength {} as specified :".format(i)
                ws1.write(rowsConColStart + 2 + t2, 4, r1, option1)
                rowsTT = rowsConColStart + 4 + t2
                # print("Reinforced concrete strength {} as specified  : ".format(i))
                total_vol = 0
                GrandTotal_vol = 0

                for j in sort_NameColumn:
                    for k in sort_size:
                        for l in sort_Level:
                            t = [v for v in check1 if v[5] == i and v[2] == j and v[3] == k and v[4] == l]
                            # print (t)
                            total_vol = round(sum(float(c[6]) for c in t), 2)
                            GrandTotal_vol = GrandTotal_vol + total_vol
                            if len(t) != 0:
                                t1 = t1 + 0.001
                                change = 20111 + t1
                                try:
                                    k1 = j + "-" + t[0][3] + ";" + t[0][4] + " - " + "(" + str(len(t)) + " Nos" + ")"
                                except:
                                    j = "None data"
                                    l1 = "None data"
                                    m = "None data"
                                    k1 = j + "-" + l1 + ";" + m
                                ws1.write(rowsTT, 1, change, option13)
                                ws1.write(rowsTT, 4, k1, option6)
                                ws1.write(rowsTT, 7, 'm3', option8)
                                ws1.write(rowsTT, 10, total_vol, option9)
                                ws1.write_formula(rowsTT, 16, '=K{0} * N{1}'.format(rowsTT + 1, rowsTT + 1), option9)
                                ws1.write_formula(rowsTT, 22, '=K{0} * T{1}'.format(rowsTT + 1, rowsTT + 1), option9)
                                ws1.write_formula(rowsTT, 25, '= Q{0} + W{1}'.format(rowsTT + 1, rowsTT + 1), option9)
                                ws1.write_formula(rowsTT, 13, "", option15)
                                ws1.write_formula(rowsTT, 19, "", option15)
                                rowsTT = rowsTT + 1
                                t2 = t2 + 1
                t2 = t2 + 3

        # check2 = [v for v in out if v[1] == StructuralMaterialType.Concrete]
        if len(check1) != 0:
            writeDicBold({20112: rowFormColStart})
            r2 = "Sawn formwork to sided of :"
            ws1.write((rowFormColStart + 2), 4, r2, option1)
            rowsTT = rowFormColStart + 4
            total_formwork = 0.0
            t1 = 0
            GrandTotal_area = 0

            for i in sort_NameColumn:
                for j in sort_size:
                    for k in sort_Level:
                        total_area = 0
                        t = [v for v in check1 if v[2] == i and v[3] == j and v[4] == k]
                        # print (t)
                        total_formwork = round(sum(float(c[7]) for c in t), 2)
                        # print (total_area)
                        GrandTotal_area = GrandTotal_area + total_formwork
                        if len(t) != 0:
                            t1 = t1 + 0.001
                            change = 20112 + t1
                            try:
                                k1 = i + "-" + t[0][3] + ";" + t[0][4] + " - " + "(" + str(len(t)) + " Nos" + ")"
                            except:
                                i = "None data"
                                l1 = "None data"
                                m = "None data"
                                k1 = i + "-" + l1 + ";" + m
                            ws1.write(rowsTT, 1, change, option13)
                            ws1.write(rowsTT, 4, k1, option6)
                            ws1.write(rowsTT, 7, 'm2', option8)
                            ws1.write(rowsTT, 10, total_formwork, option9)
                            ws1.write_formula(rowsTT, 16, '=K{0} * N{1}'.format(rowsTT + 1, rowsTT + 1), option9)
                            ws1.write_formula(rowsTT, 22, '=K{0} * T{1}'.format(rowsTT + 1, rowsTT + 1), option9)
                            ws1.write_formula(rowsTT, 25, '= Q{0} + W{1}'.format(rowsTT + 1, rowsTT + 1), option9)
                            ws1.write_formula(rowsTT, 13, "", option15)
                            ws1.write_formula(rowsTT, 19, "", option15)
                            rowsTT = rowsTT + 1

        out4 = []
        for e4 in rebar:
            p4 = []
            hostCat = e4.get_Parameter(BuiltInParameter.REBAR_HOST_CATEGORY).AsInteger()
            if hostCat == 2:
                partition = e4.get_Parameter(BuiltInParameter.NUMBER_PARTITION_PARAM).AsString()
                if partition == "" or partition == None:
                    partition = "None Defined"
                typeRebar = e4.get_Parameter(BuiltInParameter.ELEM_TYPE_PARAM)
                value4 = checkParameter(typeRebar)
                gradeRebar = e4.LookupParameter("Grade Rebar").AsString()
                group = e4.LookupParameter("Group").AsString()
                if group == None or group == "":
                    group = "None Defined"
                location = e4.LookupParameter("Location").AsString()
                if location == None or location == "":
                    location = "None Defined"
                volRebar = (e4.get_Parameter(BuiltInParameter.REINFORCEMENT_VOLUME).AsDouble()) * 0.028317
                weigthRebar = volRebar * 7850
                totalLengthRebar = (e4.get_Parameter(BuiltInParameter.REBAR_ELEM_TOTAL_LENGTH).AsDouble()) * 0.3048

                p4.append(hostCat)  # Name host Category 0
                p4.append(partition)  # Name Footing Rebar 1
                p4.append(value4)  # Type Rebar 2
                p4.append(gradeRebar)  # Grade Rebar 3
                p4.append(group)  # Group Footing Rebar 4
                p4.append(location)  # Location Footing Rebar 5
                p4.append(volRebar)  # Volume Rebar 6
                p4.append(weigthRebar)  # Weigth Rebar 7
                p4.append(totalLengthRebar)  # Total Length Rebar 8

                out4.append(p4)
        # print("out4 = {}".format(out4))
        if len(out4) > 0:
            sort_Patition = (sorted(set([e[1] for e in out4])))  # Sorted Name Footing Rebar
            sort_TypeRebar = (sorted(set([e[2] for e in out4])))  # Sorted Type Rebar
            sort_GradeRebar = (sorted(set([e[3] for e in out4])))  # Sorted Location
            sort_Group = (sorted(set([e[4] for e in out4])))  # Sorted Group
            sort_Location = (sorted(set([e[5] for e in out4])))  # Sorted Location

            nstrength = 0
            n = 0
            for i in sort_GradeRebar:
                for j in sort_TypeRebar:
                    n = len([v for v in out4 if v[3] == i and v[2] == j])
                    if n > 0:
                        nstrength = nstrength + 1

            rowsUp1 = rowsUp - 2
            rowsRebarStart = rowsUp1 + 2
            rowsRebarEnd = nstrength + 3 * len(sort_GradeRebar) + 2
            rowsUp = rowsUp1 + rowsRebarEnd + 2
            rowRight(rowsUp1, rowsUp)

            writeDicBold({20113: rowsUp1})
            change = 0.0
            t1 = 0.0
            t2 = 0
            total_wei = 0
            GrandTotal_wei = 0
            for i in sort_GradeRebar:
                r1 = "Deformed steel rod reinforcement to {} as specified :".format(i)
                ws1.write(rowsUp1 + 2 + t2, 4, r1, option1)
                rowsTT = rowsUp1 + 4 + t2
                for j in sort_TypeRebar:
                    t = [v for v in out4 if v[3] == i and v[2] == j]
                    total_wei = round(sum(float(c[7]) for c in t), 2)
                    if len(t) != 0:
                        t1 = t1 + 0.001
                        change = 20113 + t1
                        try:
                            k1 = "- " + j
                        except:
                            j = "None data"
                            k1 = "- " + j
                        ws1.write(rowsTT, 1, change, option13)
                        ws1.write(rowsTT, 4, k1, option6)
                        ws1.write(rowsTT, 7, 'kg.', option8)
                        ws1.write(rowsTT, 10, total_wei, option9)
                        ws1.write_formula(rowsTT, 16, '=K{0} * N{1}'.format(rowsTT + 1, rowsTT + 1), option9)
                        ws1.write_formula(rowsTT, 22, '=K{0} * T{1}'.format(rowsTT + 1, rowsTT + 1), option9)
                        ws1.write_formula(rowsTT, 25, '= Q{0} + W{1}'.format(rowsTT + 1, rowsTT + 1), option9)
                        ws1.write_formula(rowsTT, 13, "", option15)
                        ws1.write_formula(rowsTT, 19, "", option15)
                        rowsTT = rowsTT + 1
                        GrandTotal_wei = GrandTotal_wei + total_wei
                        t2 = t2 + 1
                t2 = t2 + 3

        subTotal(rowsStart, rowsUp - 2, "2011")
        status = 0
        return rowsUp + 1, status


def beams(rowsUp):
    beams = DB.FilteredElementCollector(doc).OfCategory(DB.BuiltInCategory.OST_StructuralFraming) \
        .WhereElementIsNotElementType() \
        .ToElements()

    rebar = DB.FilteredElementCollector(doc).OfCategory(DB.BuiltInCategory.OST_Rebar) \
        .WhereElementIsNotElementType() \
        .ToElements()

    out = []
    familyType = []
    for e in beams:
        p = []
        # id = e.Id
        # name = e.Name
        strType = e.StructuralMaterialType
        if strType == StructuralMaterialType.Concrete:
            id = e.Id
            name = e.Name
            level = e.get_Parameter(BuiltInParameter.INSTANCE_REFERENCE_LEVEL_PARAM)
            value = checkParameter(level)
            strMat = e.get_Parameter(BuiltInParameter.STRUCTURAL_MATERIAL_PARAM)
            value1 = checkParameter(strMat)
            cutlength = e.get_Parameter(BuiltInParameter.STRUCTURAL_FRAME_CUT_LENGTH).AsDouble() * 0.3048
            try:
                cutlength = e.get_Parameter(BuiltInParameter.STRUCTURAL_FRAME_CUT_LENGTH).AsDouble() * 0.3048
            except:
                cutlength = 0.0
            volume = (e.get_Parameter(BuiltInParameter.HOST_VOLUME_COMPUTED).AsDouble()) * 0.028317
            nameBeam = e.LookupParameter("Name Beam").AsString()
            if nameBeam == "" or nameBeam is None:
                nameBeam = "None Data"
            size = e.LookupParameter("Size").AsString()
            if size == "" or size is None:
                size = "None Data"
            location = e.LookupParameter("Location").AsString()
            if location == "" or location is None:
                location = "None Data"
            strengthCon = e.LookupParameter("Strength Concrete").AsString()
            if strengthCon == "" or strengthCon is None:
                strengthCon = "None Data"
            try:
                checkGroundBeam = e.LookupParameter("Ground Beam (or not)").AsInteger()
            except:
                checkGroundBeam = 0
            group = e.LookupParameter("Group").AsString()
            if group is None or group == "":
                group1 = "None Defined"
            else:
                group1 = group

            id2 = e.GetTypeId()
            if id2 == ElementId.InvalidElementId:
                familyType.append(None)
            else:
                familyType.append(doc.GetElement(id2))
            builtInParamType = BuiltInParameter.ALL_MODEL_TYPE_MARK
            for i in familyType:
                typeMark = i.get_Parameter(builtInParamType)
                if cutlength == 0.0:
                    # print("cutlength = {}".format(cutlength))
                    try:
                        cutlength = (i.LookupParameter("cutlength").AsDouble()) * 0.3048
                    except:
                        pass
                    # print("cutlength2 = {}".format(cutlength))
                try:
                    width = (i.LookupParameter("b").AsDouble()) * 0.3048
                    heigth = (i.LookupParameter("h").AsDouble()) * 0.3048
                except:
                    width = 0.0
                    heigth = 0.0
                finally:
                    if checkGroundBeam == 0:
                        area = formworkBeams(width, heigth, cutlength)
                    else:
                        area = formworkGroundBeams(heigth, cutlength)

            p.append(id)  # Id 0
            p.append(strType)  # Structural Type 1
            p.append(nameBeam)  # Name Beam 2
            p.append(size)  # size 3
            p.append(location)  # Level 4
            p.append(strengthCon)  # Stength Concrete 5
            p.append(volume)  # Volume 6
            p.append(area)  # Formwork 7
            p.append(group1)  # Group 8

            out.append(p)
    # print("out= {}".format(out))

    check1 = [v for v in out if v[1] == StructuralMaterialType.Concrete]
    if len(check1) == 0:
        status = 1
        return rowsUp, status
    else:
        sort_NameBeam = (sorted(set([e[2] for e in check1])))  # Sorted Name Beam
        sort_size = (sorted(set([e[3] for e in check1])))  # Sorted size
        sort_Level = (sorted(set([e[4] for e in check1])))  # Sorted Location
        sort_Strength = (sorted(set([e[5] for e in check1])))  # Sorted Strength Concrete
        sort_Group = (sorted(set([e[8] for e in check1])))  # Sorted Group

        nstrength = 0
        n = 0
        for i in sort_Strength:
            for g in sort_Group:
                for j in sort_NameBeam:
                    for k in sort_size:
                        for l in sort_Level:
                            n = len([v for v in check1 if
                                     v[5] == i and v[2] == j and v[3] == k and v[4] == l and v[8] == g])
                            # print(n)
                            if n > 0:
                                nstrength = nstrength + 1
        # print (nstrength)
        rowsUp1 = rowsUp
        rowsConBeamStart = rowsUp1 - 1
        # print("rowsConBeamStart = {}".format(rowsConBeamStart))
        rowsConBeamEnd = nstrength + 3 * len(sort_Strength) + len(sort_Group) * 3 + 1
        # print("rowsConBeamEnd = {}".format(rowsConBeamEnd))
        rowFormBeamStart = rowsConBeamStart + rowsConBeamEnd + 2
        rowFormBeamEnd = nstrength + len(sort_Group) * 3 + 3
        # print("rowFormBeamEnd = {}".format(rowFormBeamEnd))
        rowsUp = rowsConBeamStart + rowsConBeamEnd + rowFormBeamEnd + 3 + 2

        rowRight(rowsUp1 - 1, rowsUp)
        # rowsStart = rowsConBeamStart + 6

        # check1 = [v for v in out if v[1] == StructuralMaterialType.Concrete]
        if len(check1) != 0:
            if statusColumn == 1:
                rowsUp1 = rowsUpFooting - 1
                # print("rowsUp1 = {}".format(rowsUp1))
                rowsConBeamStart = rowsUp1 + 5
                # print("rowsConBeamStart = {}".format(rowsConBeamStart))
                rowsConBeamEnd = nstrength + 3 * len(sort_Strength) + len(sort_Group) * 3 + 1
                # print("rowsConBeamEnd = {}".format(rowsConBeamEnd))
                rowFormBeamStart = rowsConBeamStart + rowsConBeamEnd + 3 - 1
                # rowFormBeamStart = rowsUp1 + rowsConBeamEnd+3
                # print("rowFormBeamStart = {}".format(rowFormBeamStart))
                rowFormBeamEnd = nstrength + len(sort_Group) * 3 + 12 - 4
                # print("rowFormBeamEnd = {}".format(rowFormBeamEnd))
                rowsUp = rowsConBeamStart + rowsConBeamEnd + rowFormBeamEnd
                # print("rowsUp1 = {}".format(rowsUp))
                # rowTop(rowsUp1)
                # rowCornerTopRight(rowsUp1)
                rowRight(rowsUp1 + 1, rowsUp)
                writeDicUnderline({2: rowsUp1 + 1})
                writeDicBold({201: rowsUp1 + 3})
                # rowsUp1=rowsUp1+5
                # rowsConBeamStart=rowsUp1+3

            rowsStart = rowsConBeamStart + 6 + 2
            writeDicBold({2012: rowsConBeamStart, 20121: rowsConBeamStart + 2})  # Start STRUCTURAL CONCRETE BEAMS
            t2 = 0
            change = 0.0
            t1 = 0.0
            # rowsTT = rowsConBeamStart+4
            for i in sort_Strength:
                # rowsTT = rowsTT + 2
                r1 = "Reinfored concrete strength {} as specified :".format(i)
                # ws1.write(rowsConBeamStart + 4+ t2, 4, r1, option4)
                ws1.write(rowsConBeamStart + 4 + t2, 4, r1, option1)
                rowsTT = rowsConBeamStart + 6 + t2
                # print("Reinforced concrete strength {} as specified  : ".format(i))
                total_vol = 0
                GrandTotal_vol = 0
                for g in sort_Group:
                    g1 = "To " + g
                    ws1.write(rowsTT, 4, g1, option1)
                    rowsTT = rowsTT + 2
                    for j in sort_NameBeam:
                        for k in sort_size:
                            for l in sort_Level:
                                t = [v for v in check1 if
                                     v[5] == i and v[2] == j and v[3] == k and v[4] == l and v[8] == g]
                                # print (t)
                                total_vol = round(sum(float(c[6]) for c in t), 2)
                                GrandTotal_vol = GrandTotal_vol + total_vol
                                if len(t) != 0:
                                    t1 = t1 + 0.001
                                    change = 20121 + t1
                                    try:
                                        k1 = j + "-" + t[0][3] + ";" + t[0][4] + " - " + "(" + str(
                                            len(t)) + " Nos" + ")"
                                    except:
                                        j = "None data"
                                        l1 = "None data"
                                        m = "None data"
                                        k1 = j + "-" + l1 + ";" + m

                                    ws1.write(rowsTT, 1, change, option13)
                                    ws1.write(rowsTT, 4, k1, option6)
                                    ws1.write(rowsTT, 7, 'm3', option8)
                                    ws1.write(rowsTT, 10, total_vol, option9)
                                    ws1.write_formula(rowsTT, 16, '=K{0} * N{1}'.format(rowsTT + 1, rowsTT + 1),
                                                      option9)
                                    ws1.write_formula(rowsTT, 22, '=K{0} * T{1}'.format(rowsTT + 1, rowsTT + 1),
                                                      option9)
                                    ws1.write_formula(rowsTT, 25, '= Q{0} + W{1}'.format(rowsTT + 1, rowsTT + 1),
                                                      option9)
                                    ws1.write_formula(rowsTT, 13, "", option15)
                                    ws1.write_formula(rowsTT, 19, "", option15)
                                    rowsTT = rowsTT + 1
                                    t2 = t2 + 1
                    t2 = t2 + 3
                    rowsTT = rowsTT + 1
                t2 = t2 + 2

        # check2 = [v for v in out if v[1] == StructuralMaterialType.Concrete]
        if len(check1) != 0:
            writeDicBold({20122: rowFormBeamStart})
            r2 = "Sawn formwork to sided of :"
            ws1.write((rowFormBeamStart + 2), 4, r2, option1)
            rowsTT = rowFormBeamStart + 4
            total_formwork = 0.0
            t1 = 0
            GrandTotal_area = 0
            for g in sort_Group:
                g1 = "To " + g
                ws1.write(rowsTT, 4, g1, option1)
                rowsTT = rowsTT + 2
                for i in sort_NameBeam:
                    for j in sort_size:
                        for k in sort_Level:
                            total_area = 0
                            t = [v for v in check1 if v[2] == i and v[3] == j and v[4] == k and v[8] == g]
                            # print (t)
                            total_formwork = round(sum(float(c[7]) for c in t), 2)
                            # print (total_area)
                            GrandTotal_area = GrandTotal_area + total_formwork
                            if len(t) != 0:
                                t1 = t1 + 0.001
                                change = 20122 + t1
                                try:
                                    k1 = i + "-" + t[0][3] + ";" + t[0][4] + " - " + "(" + str(len(t)) + " Nos" + ")"
                                except:
                                    i = "None data"
                                    l1 = "None data"
                                    m = "None data"
                                    k1 = i + "-" + l1 + ";" + m
                                ws1.write(rowsTT, 1, change, option13)
                                ws1.write(rowsTT, 4, k1, option6)
                                ws1.write(rowsTT, 7, 'm2', option8)
                                ws1.write(rowsTT, 10, total_formwork, option9)
                                ws1.write_formula(rowsTT, 16, '=K{0} * N{1}'.format(rowsTT + 1, rowsTT + 1), option9)
                                ws1.write_formula(rowsTT, 22, '=K{0} * T{1}'.format(rowsTT + 1, rowsTT + 1), option9)
                                ws1.write_formula(rowsTT, 25, '= Q{0} + W{1}'.format(rowsTT + 1, rowsTT + 1), option9)
                                ws1.write_formula(rowsTT, 13, "", option15)
                                ws1.write_formula(rowsTT, 19, "", option15)
                                rowsTT = rowsTT + 1
                rowsTT = rowsTT + 1
        # print("rowsUp2 = {}".format(rowsUp))

        out4 = []
        for e4 in rebar:
            p4 = []
            hostCat = e4.get_Parameter(BuiltInParameter.REBAR_HOST_CATEGORY).AsInteger()
            if hostCat == 3:
                partition = e4.get_Parameter(BuiltInParameter.NUMBER_PARTITION_PARAM).AsString()
                if partition == "" or partition is None:
                    partition = "None Defined"
                typeRebar = e4.get_Parameter(BuiltInParameter.ELEM_TYPE_PARAM)
                value4 = checkParameter(typeRebar)
                gradeRebar = e4.LookupParameter("Grade Rebar").AsString()
                group = e4.LookupParameter("Group").AsString()
                if group is None or group == "":
                    group = "None Defined"
                location = e4.LookupParameter("Location").AsString()
                if location is None or location == "":
                    location = "None Defined"
                volRebar = (e4.get_Parameter(BuiltInParameter.REINFORCEMENT_VOLUME).AsDouble()) * 0.028317
                weigthRebar = volRebar * 7850
                totalLengthRebar = (e4.get_Parameter(BuiltInParameter.REBAR_ELEM_TOTAL_LENGTH).AsDouble()) * 0.3048

                p4.append(hostCat)  # Name host Category 0
                p4.append(partition)  # Name Footing Rebar 1
                p4.append(value4)  # Type Rebar 2
                p4.append(gradeRebar)  # Grade Rebar 3
                p4.append(group)  # Group Footing Rebar 4
                p4.append(location)  # Location Footing Rebar 5
                p4.append(volRebar)  # Volume Rebar 6
                p4.append(weigthRebar)  # Weigth Rebar 7
                p4.append(totalLengthRebar)  # Total Length Rebar 8

                out4.append(p4)

        if len(out4) > 0:
            sort_Patition = (sorted(set([e[1] for e in out4])))  # Sorted Name Footing Rebar
            sort_TypeRebar = (sorted(set([e[2] for e in out4])))  # Sorted Type Rebar
            sort_GradeRebar = (sorted(set([e[3] for e in out4])))  # Sorted Location
            sort_Group = (sorted(set([e[4] for e in out4])))  # Sorted Group
            sort_Location = (sorted(set([e[5] for e in out4])))  # Sorted Location

            nstrength = 0
            n = 0
            for i in sort_GradeRebar:
                for j in sort_TypeRebar:
                    n = len([v for v in out4 if v[3] == i and v[2] == j])
                    if n > 0:
                        nstrength = nstrength + 1

            rowsUp1 = rowsUp - 2
            rowsRebarStart = rowsUp1 + 2
            rowsRebarEnd = nstrength + 3 * len(sort_GradeRebar) + 2
            rowsUp = rowsUp1 + rowsRebarEnd + 2
            rowRight(rowsUp1, rowsUp)

            writeDicBold({20123: rowsUp1})
            change = 0.0
            t1 = 0.0
            t2 = 0
            total_wei = 0
            GrandTotal_wei = 0
            for i in sort_GradeRebar:
                r1 = "Deformed steel rod reinforcement to {} as specified :".format(i)
                ws1.write(rowsUp1 + 2 + t2, 4, r1, option1)
                rowsTT = rowsUp1 + 4 + t2
                for j in sort_TypeRebar:
                    t = [v for v in out4 if v[3] == i and v[2] == j]
                    total_wei = round(sum(float(c[7]) for c in t), 2)
                    if len(t) != 0:
                        t1 = t1 + 0.001
                        change = 20123 + t1
                        try:
                            k1 = "- " + j
                        except:
                            j = "None data"
                            k1 = "- " + j
                        ws1.write(rowsTT, 1, change, option13)
                        ws1.write(rowsTT, 4, k1, option6)
                        ws1.write(rowsTT, 7, 'kg.', option8)
                        ws1.write(rowsTT, 10, total_wei, option9)
                        ws1.write_formula(rowsTT, 16, '=K{0} * N{1}'.format(rowsTT + 1, rowsTT + 1), option9)
                        ws1.write_formula(rowsTT, 22, '=K{0} * T{1}'.format(rowsTT + 1, rowsTT + 1), option9)
                        ws1.write_formula(rowsTT, 25, '= Q{0} + W{1}'.format(rowsTT + 1, rowsTT + 1), option9)
                        ws1.write_formula(rowsTT, 13, "", option15)
                        ws1.write_formula(rowsTT, 19, "", option15)
                        rowsTT = rowsTT + 1
                        GrandTotal_wei = GrandTotal_wei + total_wei
                        t2 = t2 + 1
                t2 = t2 + 3

        subTotal(rowsStart, rowsUp - 2, "2012")
        status = 0
        return rowsUp + 1, status


def floors(rowsUp):
    floors = DB.FilteredElementCollector(doc).OfCategory(DB.BuiltInCategory.OST_Floors) \
        .WhereElementIsNotElementType() \
        .ToElements()

    rebar = DB.FilteredElementCollector(doc).OfCategory(DB.BuiltInCategory.OST_Rebar) \
        .WhereElementIsNotElementType() \
        .ToElements()

    out = []
    familyType = []
    for e in floors:
        p = []
        id = e.Id
        name = e.Name
        level = e.get_Parameter(BuiltInParameter.SCHEDULE_LEVEL_PARAM)
        value = checkParameter(level)
        strMat = e.get_Parameter(BuiltInParameter.FLOOR_PARAM_IS_STRUCTURAL).AsInteger()
        try:
            coreThick = (e.get_Parameter(BuiltInParameter.STRUCTURAL_FLOOR_CORE_THICKNESS).AsDouble()) * 0.3048
        except:
            coreThick = 0

        volume = e.get_Parameter(BuiltInParameter.HOST_VOLUME_COMPUTED).AsDouble() * 0.028317
        nameFloor = e.LookupParameter("Name Floor").AsString()
        size = e.LookupParameter("Size").AsString()
        location = e.LookupParameter("Location").AsString()
        strengthCon = e.LookupParameter("Strength Concrete").AsString()
        try:
            isDropPanel = e.LookupParameter("Is Drop Panel").AsInteger()
        except:
            isDropPanel = 0
        try:
            isToppingSlab = e.LookupParameter("Is Topping Slab").AsInteger()
        except:
            isToppingSlab = 0

        perimeter = e.get_Parameter(BuiltInParameter.HOST_PERIMETER_COMPUTED).AsDouble() * 0.3047999
        if isDropPanel == 0:
            area = (e.get_Parameter(BuiltInParameter.HOST_AREA_COMPUTED).AsDouble()) * 0.092903
        else:
            area = formworkDropPanel(coreThick, perimeter) + (
                e.get_Parameter(BuiltInParameter.HOST_AREA_COMPUTED).AsDouble()) * 0.092903

        id2 = e.GetTypeId()
        if id2 == ElementId.InvalidElementId:
            familyType.append(None)
        else:
            familyType.append(doc.GetElement(id2))
        builtInParamType = BuiltInParameter.ALL_MODEL_TYPE_MARK
        for i in familyType:
            typeMark = i.get_Parameter(builtInParamType)

        p.append(id)  # Id 0
        p.append(value)  # Level 1
        p.append(name)  # Type Name 2
        p.append(strMat)  # Material 3
        p.append(typeMark.AsString())  # Type Mark 4
        p.append(coreThick)  # 5
        p.append(area)  # Formwork 6
        p.append(volume)  # Volume 7
        p.append(nameFloor)  # NameFloor 8
        p.append(size)  # Size 9
        p.append(location)  # Location 10
        p.append(strengthCon)  # StrengthCon 11
        p.append(perimeter)  # perimeter 12
        p.append(isDropPanel)  # Is Drop Panel 13
        p.append(isToppingSlab)  # Is Topping Slab 14
        out.append(p)
    # print("out = {}".format(out))

    if len(out) == 0:
        status = 1
        return rowsUp, status
    else:
        check1 = [v for v in out if v[3] == 1 and v[13] == 0 and v[14] == 0]
        # print ("check1 = {}".format(check1))
        # print ("check1 = {}".format(len(check1)))
        if len(check1) != 0:
            sort_NameFloor = (sorted(set([e[8] for e in check1 if e[3] == 1])))  # Sorted Namefloor
            sort_size = (sorted(set([e[9] for e in check1 if e[3] == 1])))  # Sorted size
            sort_Location = (sorted(set([e[10] for e in check1 if e[3] == 1])))  # Sorted Location
            sort_Strength = (sorted(set([e[11] for e in check1 if e[3] == 1])))  # Sorted Strength Concrete

            nstrength = 0
            n = 0
            for i in sort_Strength:
                for j in sort_NameFloor:
                    for k in sort_size:
                        for l in sort_Location:
                            n = len([v for v in check1 if v[11] == i and v[8] == j and v[9] == k and v[10] == l])
                            # print("n = {}".format(n))
                            if n > 0:
                                nstrength = nstrength + 1
            # print ("nstrength = {}".format(nstrength))
            rowsUp1 = rowsUp - 1
            rowsConFloorStart = rowsUp1 + 5
            rowsConFloorEnd = rowsConFloorStart + nstrength + 3 * len(sort_Strength) + 1
            rowFormFloorStart = rowsConFloorEnd
            rowFormFloorEnd = rowFormFloorStart + nstrength + 4
            rowsUp = rowFormFloorEnd + 3

            rowRight(rowsUp1, rowsUp)
            rowsStart = rowsConFloorStart + 3

            if len(check1) != 0:
                if statusColumn == 1 and statusBeam == 1:
                    rowsUp1 = rowsUpFooting + 4
                    rowsConFloorStart = rowsUp1 + 5
                    rowsConFloorEnd = rowsConFloorStart + nstrength + 3 * len(sort_Strength) + 1
                    rowFormFloorStart = rowsConFloorEnd
                    rowFormFloorEnd = rowFormFloorStart + nstrength + 4
                    rowsUp = rowFormFloorEnd + 3

                    # rowTop(rowsUpFooting)
                    # rowCornerTopRight(rowsUpFooting)
                    rowRight(rowsUpFooting + 1, rowsUp)
                    writeDicUnderline({2: rowsUpFooting})
                    writeDicBold({201: rowsUpFooting + 2})
                    rowsStart = rowsConFloorStart + 3

                elif statusBeam == 1:
                    rowsUp1 = rowsUpColumn - 1
                    rowsConFloorStart = rowsUp1 + 5
                    rowsConFloorEnd = rowsConFloorStart + nstrength + 3 * len(sort_Strength) + 1
                    rowFormFloorStart = rowsConFloorEnd
                    rowFormFloorEnd = rowFormFloorStart + nstrength + 4
                    rowsUp = rowFormFloorEnd + 3

                writeDicBold(
                    {2013: rowsUp1, 20131: rowsUp1 + 2, 201311: rowsUp1 + 4})  # Start STRUCTURAL CONCRETE FLOOR
                t2 = 0
                change = 0.0
                t1 = 0.0
                for i in sort_Strength:
                    r1 = "Reinfored concrete strength {} as specified :".format(i)
                    ws1.write(rowsConFloorStart + 1 + t2, 4, r1, option1)
                    rowsTT = rowsConFloorStart + 3 + t2
                    # print("Reinforced concrete strength {} as specified  : ".format(i))
                    total_vol = 0
                    GrandTotal_vol = 0

                    for j in sort_NameFloor:
                        for k in sort_size:
                            for l in sort_Location:
                                t = [v for v in check1 if
                                     v[11] == i and v[8] == j and v[3] == 1 and v[13] == 0 and v[9] == k and v[10] == l]
                                # print (t)
                                total_vol = round(sum(float(c[7]) for c in t), 2)
                                GrandTotal_vol = GrandTotal_vol + total_vol
                                if len(t) != 0:
                                    t1 = t1 + 0.001
                                    change = 201311 + t1

                                    try:
                                        k1 = j + "-" + t[0][9] + ";" + t[0][10] + " - " + "(" + str(
                                            len(t)) + " Nos" + ")"
                                    except:
                                        j = "None data"
                                        l1 = "None data"
                                        m = "None data"
                                        k1 = j + "-" + l1 + ";" + m

                                    ws1.write(rowsTT, 1, change, option13)
                                    ws1.write(rowsTT, 4, k1, option6)
                                    ws1.write(rowsTT, 7, 'm3', option8)
                                    ws1.write(rowsTT, 10, total_vol, option9)
                                    ws1.write_formula(rowsTT, 16, '=K{0} * N{1}'.format(rowsTT + 1, rowsTT + 1),
                                                      option9)
                                    ws1.write_formula(rowsTT, 22, '=K{0} * T{1}'.format(rowsTT + 1, rowsTT + 1),
                                                      option9)
                                    ws1.write_formula(rowsTT, 25, '= Q{0} + W{1}'.format(rowsTT + 1, rowsTT + 1),
                                                      option9)
                                    ws1.write_formula(rowsTT, 13, "", option15)
                                    ws1.write_formula(rowsTT, 19, "", option15)
                                    rowsTT = rowsTT + 1
                                    t2 = t2 + 1
                    t2 = t2 + 3

                # check2 = [v for v in out if v[3] == 1 and v[15] == 0]
                if len(check1) != 0:
                    writeDicBold({201312: rowFormFloorStart})
                    r2 = "Sawn formwork to sided of :"
                    ws1.write((rowFormFloorStart + 2), 4, r2, option1)
                    rowsTT = rowFormFloorStart + 4
                total_formwork = 0.0
                change = 0.0
                t1 = 0
                GrandTotal_area = 0

                for i in sort_NameFloor:
                    for j in sort_size:
                        for k in sort_Location:
                            total_formwork = 0
                            t = [v for v in check1 if
                                 v[8] == i and v[3] == 1 and v[13] == 0 and v[9] == j and v[10] == k]
                            # print (t)
                            total_formwork = round(sum(float(c[6]) for c in t), 2)
                            # print (total_area)
                            GrandTotal_area = GrandTotal_area + total_formwork
                            if len(t) != 0:
                                t1 = t1 + 0.001
                                change = 201312 + t1

                                try:
                                    k1 = i + "-" + t[0][9] + ";" + t[0][10] + " - " + "(" + str(len(t)) + " Nos" + ")"
                                except:
                                    i = "None data"
                                    l1 = "None data"
                                    m = "None data"
                                    k1 = i + "-" + l1 + ";" + m

                                ws1.write(rowsTT, 1, change, option13)
                                ws1.write(rowsTT, 4, k1, option6)
                                ws1.write(rowsTT, 7, 'm2', option8)
                                ws1.write(rowsTT, 10, total_formwork, option9)
                                ws1.write_formula(rowsTT, 16, '=K{0} * N{1}'.format(rowsTT + 1, rowsTT + 1), option9)
                                ws1.write_formula(rowsTT, 22, '=K{0} * T{1}'.format(rowsTT + 1, rowsTT + 1), option9)
                                ws1.write_formula(rowsTT, 25, '= Q{0} + W{1}'.format(rowsTT + 1, rowsTT + 1), option9)
                                ws1.write_formula(rowsTT, 13, "", option15)
                                ws1.write_formula(rowsTT, 19, "", option15)
                                rowsTT = rowsTT + 1
                statusFloor = 0
                rowsUpOkFloor = rowsUp + 1

        else:
            # rowsUp1 = rowsUp-1
            statusFloor = 1
            rowsUpNoFloor = rowsUp

        check2 = [v for v in out if v[3] == 1 and v[13] == 1 and v[14] == 0]
        # print ("check2 = {}".format(check2))
        # print ("check2 = {}".format(len(check2)))
        sort_NameFloor = (sorted(set([e[8] for e in check2 if e[3] == 1])))  # Sorted Namefloor
        sort_size = (sorted(set([e[9] for e in check2 if e[3] == 1])))  # Sorted size
        sort_Location = (sorted(set([e[10] for e in check2 if e[3] == 1])))  # Sorted Location
        sort_Strength = (sorted(set([e[11] for e in check2 if e[3] == 1])))  # Sorted Strength Concrete

        nstrength = 0
        n = 0
        for i in sort_Strength:
            for j in sort_NameFloor:
                for k in sort_size:
                    for l in sort_Location:
                        n = len([v for v in check2 if v[11] == i and v[8] == j and v[9] == k and v[10] == l])
                        # print("n = {}".format(n))
                        if n > 0:
                            nstrength = nstrength + 1
        # print("len(check2) = {}".format(len(check2)))
        if len(check2) != 0:
            if statusFloor == 0:
                rowsUp2 = rowsUpOkFloor - 3
                rowsConFloorStart = rowsUp2 + 2
                rowsConFloorEnd = rowsConFloorStart + nstrength + 3 * len(sort_Strength) + 1
                rowFormFloorStart = rowsConFloorEnd + 1
                rowFormFloorEnd = rowFormFloorStart + nstrength + 4
                rowsUp = rowFormFloorEnd + 3

            else:
                rowsUp2 = rowsUpNoFloor
                rowsConFloorStart = rowsUp2 + 5
                rowsConFloorEnd = rowsConFloorStart + nstrength + 3 * len(sort_Strength) + 1
                rowFormFloorStart = rowsConFloorEnd
                rowFormFloorEnd = rowFormFloorStart + nstrength + 4
                rowsUp = rowFormFloorEnd + 3
            rowRight(rowsUp2 - 1, rowsUp)

            if statusColumn == 1 and statusBeam == 1 and statusFloor == 1:
                rowsUp2 = rowsUpFooting + 7
                rowsConFloorStart = rowsUp2 + 2
                rowsConFloorEnd = rowsConFloorStart + nstrength + 3 * len(sort_Strength) + 1
                rowFormFloorStart = rowsConFloorEnd + 1
                rowFormFloorEnd = rowFormFloorStart + nstrength + 4
                rowsUp = rowFormFloorEnd + 3

                rowTop(rowsUpFooting)
                rowCornerTopRight(rowsUpFooting)
                rowRight(rowsUpFooting + 1, rowsUp)
                writeDicUnderline({2: rowsUpFooting + 1})
                writeDicBold({201: rowsUpFooting + 3})
                writeDicBold({2013: rowsUpFooting + 5})


            elif statusBeam == 1 and statusFloor == 1:
                rowsUp2 = rowsUpColumn + 2
                rowsConFloorStart = rowsUp2 + 2
                rowsConFloorEnd = rowsConFloorStart + nstrength + 3 * len(sort_Strength) + 1
                rowFormFloorStart = rowsConFloorEnd + 1
                rowFormFloorEnd = rowFormFloorStart + nstrength + 4
                rowsUp = rowFormFloorEnd + 3

                writeDicBold({2013: rowsUpColumn})


            elif statusFloor == 1:
                rowsUp2 = rowsUpBeam + 2
                rowsConFloorStart = rowsUp2 + 2
                rowsConFloorEnd = rowsConFloorStart + nstrength + 3 * len(sort_Strength) + 1
                rowFormFloorStart = rowsConFloorEnd + 1
                rowFormFloorEnd = rowFormFloorStart + nstrength + 4
                rowsUp = rowFormFloorEnd + 3

                writeDicBold({2013: rowsUpBeam})

            writeDicBold({20132: rowsUp2, 201321: rowsUp2 + 2})  # Start STRUCTURAL CONCRETE FLOOR
            t2 = 0
            change = 0.0
            t1 = 0.0
            for i in sort_Strength:
                r1 = "Reinfored concrete strength {} as specified :".format(i)
                ws1.write(rowsConFloorStart + 2 + t2, 4, r1, option1)
                rowsTT = rowsConFloorStart + 4 + t2
                # print("Reinforced concrete strength {} as specified  : ".format(i))
                total_vol = 0
                GrandTotal_vol = 0

                for j in sort_NameFloor:
                    for k in sort_size:
                        for l in sort_Location:
                            t = [v for v in check2 if
                                 v[11] == i and v[8] == j and v[3] == 1 and v[13] == 1 and v[9] == k and v[10] == l]
                            # print (t)
                            total_vol = round(sum(float(c[7]) for c in t), 2)
                            GrandTotal_vol = GrandTotal_vol + total_vol
                            if len(t) != 0:
                                t1 = t1 + 0.001
                                change = 201321 + t1

                                try:
                                    k1 = j + "-" + t[0][9] + ";" + t[0][10] + " - " + "(" + str(len(t)) + " Nos" + ")"
                                except:
                                    j = "None data"
                                    l1 = "None data"
                                    m = "None data"
                                    k1 = j + "-" + l1 + ";" + m

                                ws1.write(rowsTT, 1, change, option13)
                                ws1.write(rowsTT, 4, k1, option6)
                                ws1.write(rowsTT, 7, 'm3', option8)
                                ws1.write(rowsTT, 10, total_vol, option9)
                                ws1.write_formula(rowsTT, 16, '=K{0} * N{1}'.format(rowsTT + 1, rowsTT + 1), option9)
                                ws1.write_formula(rowsTT, 22, '=K{0} * T{1}'.format(rowsTT + 1, rowsTT + 1), option9)
                                ws1.write_formula(rowsTT, 25, '= Q{0} + W{1}'.format(rowsTT + 1, rowsTT + 1), option9)
                                ws1.write_formula(rowsTT, 13, "", option15)
                                ws1.write_formula(rowsTT, 19, "", option15)
                                rowsTT = rowsTT + 1
                                t2 = t2 + 1
                t2 = t2 + 3

            if len(check2) != 0:
                writeDicBold({201322: rowFormFloorStart})
                r2 = "Sawn formwork to sided of :"
                ws1.write((rowFormFloorStart + 2), 4, r2, option1)
                rowsTT = rowFormFloorStart + 4
            total_formwork = 0.0
            t1 = 0
            GrandTotal_area = 0

            for i in sort_NameFloor:
                for j in sort_size:
                    for k in sort_Location:
                        total_formwork = 0
                        t = [v for v in check2 if v[8] == i and v[3] == 1 and v[13] == 1 and v[9] == j and v[10] == k]
                        # print (t)
                        # total_formwork = sum(float(c[6]) for c in t)
                        total_formwork = round(sum(float(c[6]) for c in t), 2)
                        # print (total_area)
                        GrandTotal_area = GrandTotal_area + total_formwork
                        if len(t) != 0:
                            t1 = t1 + 0.001
                            change = 201322 + t1

                            try:
                                k1 = i + "-" + t[0][9] + ";" + t[0][10] + " - " + "(" + str(len(t)) + " Nos" + ")"
                            except:
                                i = "None data"
                                l1 = "None data"
                                m = "None data"
                                k1 = i + "-" + l1 + ";" + m

                            ws1.write(rowsTT, 1, change, option13)
                            ws1.write(rowsTT, 4, k1, option6)
                            ws1.write(rowsTT, 7, 'm2', option8)
                            ws1.write(rowsTT, 10, total_formwork, option9)
                            ws1.write_formula(rowsTT, 16, '=K{0} * N{1}'.format(rowsTT + 1, rowsTT + 1), option9)
                            ws1.write_formula(rowsTT, 22, '=K{0} * T{1}'.format(rowsTT + 1, rowsTT + 1), option9)
                            ws1.write_formula(rowsTT, 25, '= Q{0} + W{1}'.format(rowsTT + 1, rowsTT + 1), option9)
                            ws1.write_formula(rowsTT, 13, "", option15)
                            ws1.write_formula(rowsTT, 19, "", option15)
                            rowsTT = rowsTT + 1

        out4 = []
        for e4 in rebar:
            p4 = []
            hostCat = e4.get_Parameter(BuiltInParameter.REBAR_HOST_CATEGORY).AsInteger()
            if hostCat == 5:
                partition = e4.get_Parameter(BuiltInParameter.NUMBER_PARTITION_PARAM).AsString()
                if partition == "" or partition == None:
                    partition = "None Defined"
                typeRebar = e4.get_Parameter(BuiltInParameter.ELEM_TYPE_PARAM)
                value4 = checkParameter(typeRebar)
                gradeRebar = e4.LookupParameter("Grade Rebar").AsString()
                group = e4.LookupParameter("Group").AsString()
                if group == None or group == "":
                    group = "None Defined"
                location = e4.LookupParameter("Location").AsString()
                if location == None or location == "":
                    location = "None Defined"
                volRebar = (e4.get_Parameter(BuiltInParameter.REINFORCEMENT_VOLUME).AsDouble()) * 0.028317
                weigthRebar = volRebar * 7850
                totalLengthRebar = (e4.get_Parameter(BuiltInParameter.REBAR_ELEM_TOTAL_LENGTH).AsDouble()) * 0.3048

                p4.append(hostCat)  # Name host Category 0
                p4.append(partition)  # Name Footing Rebar 1
                p4.append(value4)  # Type Rebar 2
                p4.append(gradeRebar)  # Grade Rebar 3
                p4.append(group)  # Group Footing Rebar 4
                p4.append(location)  # Location Footing Rebar 5
                p4.append(volRebar)  # Volume Rebar 6
                p4.append(weigthRebar)  # Weigth Rebar 7
                p4.append(totalLengthRebar)  # Total Length Rebar 8

                out4.append(p4)

        if len(out4) > 0:
            sort_Patition = (sorted(set([e[1] for e in out4])))  # Sorted Name Footing Rebar
            sort_TypeRebar = (sorted(set([e[2] for e in out4])))  # Sorted Type Rebar
            sort_GradeRebar = (sorted(set([e[3] for e in out4])))  # Sorted Location
            sort_Group = (sorted(set([e[4] for e in out4])))  # Sorted Group
            sort_Location = (sorted(set([e[5] for e in out4])))  # Sorted Location

            nstrength = 0
            n = 0
            for i in sort_GradeRebar:
                for j in sort_TypeRebar:
                    n = len([v for v in out4 if v[3] == i and v[2] == j])
                    if n > 0:
                        nstrength = nstrength + 1

            rowsUp1 = rowsUp - 2
            rowsRebarStart = rowsUp1 + 2
            rowsRebarEnd = nstrength + 3 * len(sort_GradeRebar) + 2
            rowsUp = rowsUp1 + rowsRebarEnd + 2
            rowRight(rowsUp1, rowsUp)

            writeDicBold({20133: rowsUp1})
            change = 0.0
            t1 = 0.0
            t2 = 0
            total_wei = 0
            GrandTotal_wei = 0
            for i in sort_GradeRebar:
                r1 = "Deformed steel rod reinforcement to {} as specified :".format(i)
                ws1.write(rowsUp1 + 2 + t2, 4, r1, option1)
                rowsTT = rowsUp1 + 4 + t2
                for j in sort_TypeRebar:
                    t = [v for v in out4 if v[3] == i and v[2] == j]
                    total_wei = round(sum(float(c[7]) for c in t), 2)
                    if len(t) != 0:
                        t1 = t1 + 0.001
                        change = 20133 + t1
                        try:
                            k1 = "- " + j
                        except:
                            j = "None data"
                            k1 = "- " + j
                        ws1.write(rowsTT, 1, change, option13)
                        ws1.write(rowsTT, 4, k1, option6)
                        ws1.write(rowsTT, 7, 'kg.', option8)
                        ws1.write(rowsTT, 10, total_wei, option9)
                        ws1.write_formula(rowsTT, 16, '=K{0} * N{1}'.format(rowsTT + 1, rowsTT + 1), option9)
                        ws1.write_formula(rowsTT, 22, '=K{0} * T{1}'.format(rowsTT + 1, rowsTT + 1), option9)
                        ws1.write_formula(rowsTT, 25, '= Q{0} + W{1}'.format(rowsTT + 1, rowsTT + 1), option9)
                        ws1.write_formula(rowsTT, 13, "", option15)
                        ws1.write_formula(rowsTT, 19, "", option15)
                        rowsTT = rowsTT + 1
                        GrandTotal_wei = GrandTotal_wei + total_wei
                        t2 = t2 + 1
                t2 = t2 + 3

        subTotal(rowsStart, rowsUp - 2, "2013")
        status = 0
        return rowsUp + 1, status


def coreWalls(rowsUp):
    walls = DB.FilteredElementCollector(doc).OfCategory(DB.BuiltInCategory.OST_Walls) \
        .WhereElementIsNotElementType() \
        .ToElements()

    rebar = DB.FilteredElementCollector(doc).OfCategory(DB.BuiltInCategory.OST_Rebar) \
        .WhereElementIsNotElementType() \
        .ToElements()

    if len(walls) == 0:
        status = 1
        return rowsUp, status

    else:
        out = []
        familyType = []
        for e in walls:
            p = []
            id = e.Id
            name = e.Name
            strMat = e.get_Parameter(BuiltInParameter.WALL_STRUCTURAL_SIGNIFICANT).AsInteger()
            Level = e.get_Parameter(BuiltInParameter.WALL_BASE_CONSTRAINT)
            value = checkParameter(Level)
            length = (e.get_Parameter(BuiltInParameter.CURVE_ELEM_LENGTH).AsDouble()) * 0.3048
            volume = (e.get_Parameter(BuiltInParameter.HOST_VOLUME_COMPUTED).AsDouble()) * 0.028317
            formwork = (e.get_Parameter(BuiltInParameter.HOST_AREA_COMPUTED).AsDouble()) * 0.092903 * 2
            nameCoreWall = e.LookupParameter("Name Core Wall").AsString()
            size = e.LookupParameter("Size").AsString()
            location = e.LookupParameter("Location").AsString()
            strengthCon = e.LookupParameter("Strength Concrete").AsString()

            p.append(id)  # Id 0
            p.append(name)  # Name 1
            p.append(value)  # level 2
            p.append(strMat)  # Structure 3
            p.append(volume)  # Volume Concrete 4
            p.append(formwork)  # Formwork 5
            p.append(nameCoreWall)  # Name Core Wall 6
            p.append(size)  # Size 7
            p.append(location)  # Location 8
            p.append(strengthCon)  # Strength Concrete 9
            out.append(p)
        # print("out = {}".format(out))
        check1 = [v for v in out if v[3] == 1]
        # print ("check1 = {}".format(check1))
        # print ("check1 = {}".format(len(check1)))
        sort_NameCoreWall = (sorted(set([e[6] for e in check1 if e[3] == 1])))  # Sorted NameCoreWall
        sort_size = (sorted(set([e[7] for e in check1 if e[3] == 1])))  # Sorted size Wall
        sort_Location = (sorted(set([e[8] for e in check1 if e[3] == 1])))  # Sorted Location
        sort_Strength = (sorted(set([e[9] for e in check1 if e[3] == 1])))  # Sorted Strength Concrete
        nstrength = 0
        n = 0
        for i in sort_Strength:
            for j in sort_NameCoreWall:
                for k in sort_size:
                    for l in sort_Location:
                        n = len([v for v in check1 if v[9] == i and v[6] == j and v[7] == k and v[8] == l])
                        # print(n)
                        if n > 0:
                            nstrength = nstrength + 1
        # print (nstrength)
        rowsUp1 = rowsUp - 1
        rowsConCoreWallStart = rowsUp1
        rowsConCoreWallEnd = nstrength + 3 * len(sort_Strength) + 1
        rowFormCoreWallStart = rowsUp1 + rowsConCoreWallEnd + 3
        rowFormCoreWallEnd = nstrength + 7
        rowsUp = rowsUp1 + rowsConCoreWallEnd + rowFormCoreWallEnd + 3

        rowRight(rowsUp1, rowsUp)
        rowsStart = rowsConCoreWallStart + 6

        if len(check1) != 0:
            if statusColumn == 1 and statusBeam == 1 and statusFloor == 1:
                rowsUp1 = rowsUpFooting - 1
                rowsConCoreWallStart = rowsUp1 + 5
                rowsConCoreWallEnd = nstrength + 3 * len(sort_Strength) + 1
                rowFormCoreWallStart = rowsUp1 + rowsConCoreWallEnd + 8
                rowFormCoreWallEnd = nstrength + 12
                rowsUp = rowsUp1 + rowsConCoreWallEnd + rowFormCoreWallEnd + 3
                # rowTop(rowsUp1)
                # rowCornerTopRight(rowsUp1)
                rowRight(rowsUp1 + 1, rowsUp)
                writeDicUnderline({2: rowsUp1 + 1})
                writeDicBold({201: rowsUp1 + 3})
                rowsStart = rowsConCoreWallStart + 6
                # rowsUp1=rowsUp1+5
                # rowsConBeamStart=rowsUp1+3

            writeDicBold(
                {2014: rowsConCoreWallStart, 20141: rowsConCoreWallStart + 2})  # Start STRUCTURAL CONCRETE BEAMS
            t2 = 0
            change = 0.0
            t1 = 0.0
            for i in sort_Strength:
                r1 = "Reinfored concrete strength {} as specified :".format(i)
                ws1.write(rowsConCoreWallStart + 4 + t2, 4, r1, option1)
                rowsTT = rowsConCoreWallStart + 6 + t2
                # print("Reinforced concrete strength {} as specified  : ".format(i))
                total_vol = 0
                GrandTotal_vol = 0

                for j in sort_NameCoreWall:
                    for k in sort_size:
                        for l in sort_Location:
                            t = [v for v in check1 if v[9] == i and v[6] == j and v[7] == k and v[8] == l]
                            # print (t)
                            total_vol = round(sum(float(c[4]) for c in t), 2)
                            # total_formwork = round(sum(float(c[5]) for c in t),2)
                            GrandTotal_vol = GrandTotal_vol + total_vol
                            if len(t) != 0:
                                t1 = t1 + 0.001
                                change = 20141 + t1

                                try:
                                    k1 = j + "-" + t[0][7] + ";" + t[0][8] + " - " + "(" + str(len(t)) + " Nos" + ")"
                                except:
                                    j = "None data"
                                    l1 = "None data"
                                    m1 = "None data"
                                    k1 = j + "-" + l1 + ";" + m1

                                ws1.write(rowsTT, 1, change, option13)
                                ws1.write(rowsTT, 4, k1, option6)
                                ws1.write(rowsTT, 7, 'm3', option8)
                                ws1.write(rowsTT, 10, total_vol, option9)
                                ws1.write_formula(rowsTT, 16, '=K{0} * N{1}'.format(rowsTT + 1, rowsTT + 1), option9)
                                ws1.write_formula(rowsTT, 22, '=K{0} * T{1}'.format(rowsTT + 1, rowsTT + 1), option9)
                                ws1.write_formula(rowsTT, 25, '= Q{0} + W{1}'.format(rowsTT + 1, rowsTT + 1), option9)
                                ws1.write_formula(rowsTT, 13, "", option15)
                                ws1.write_formula(rowsTT, 19, "", option15)
                                rowsTT = rowsTT + 1
                                t2 = t2 + 1
                t2 = t2 + 3

        # check2 = [v for v in out if v[3] == 1]
        if len(check1) != 0:
            writeDicBold({20142: rowFormCoreWallStart})
            r2 = "Sawn formwork to sided of :"
            ws1.write((rowFormCoreWallStart + 2), 4, r2, option1)
            rowsTT = rowFormCoreWallStart + 4
            total_formwork = 0.0
            t1 = 0
            GrandTotal_area = 0

            for i in sort_NameCoreWall:
                for j in sort_size:
                    for k in sort_Location:
                        total_formwork = 0
                        t = [v for v in check1 if v[6] == i and v[7] == j and v[8] == k]
                        # print (t)
                        total_formwork = round(sum(float(c[5]) for c in t), 2)
                        # print (total_area)
                        GrandTotal_area = GrandTotal_area + total_formwork
                        if len(t) != 0:
                            t1 = t1 + 0.001
                            change = 20142 + t1

                            try:
                                k1 = i + "-" + t[0][7] + ";" + t[0][8] + " - " + "(" + str(len(t)) + " Nos" + ")"
                            except:
                                i = "None data"
                                l1 = "None data"
                                m = "None data"
                                k1 = i + "-" + l1 + ";" + m

                            ws1.write(rowsTT, 1, change, option13)
                            ws1.write(rowsTT, 4, k1, option6)
                            ws1.write(rowsTT, 7, 'm2', option8)
                            ws1.write(rowsTT, 10, total_formwork, option9)
                            ws1.write_formula(rowsTT, 16, '=K{0} * N{1}'.format(rowsTT + 1, rowsTT + 1), option9)
                            ws1.write_formula(rowsTT, 22, '=K{0} * T{1}'.format(rowsTT + 1, rowsTT + 1), option9)
                            ws1.write_formula(rowsTT, 25, '= Q{0} + W{1}'.format(rowsTT + 1, rowsTT + 1), option9)
                            ws1.write_formula(rowsTT, 13, "", option15)
                            ws1.write_formula(rowsTT, 19, "", option15)
                            rowsTT = rowsTT + 1

        out4 = []
        for e4 in rebar:
            p4 = []
            hostCat = e4.get_Parameter(BuiltInParameter.REBAR_HOST_CATEGORY).AsInteger()
            if hostCat == 4:
                partition = e4.get_Parameter(BuiltInParameter.NUMBER_PARTITION_PARAM).AsString()
                if partition == "" or partition is None:
                    partition = "None Defined"
                typeRebar = e4.get_Parameter(BuiltInParameter.ELEM_TYPE_PARAM)
                value4 = checkParameter(typeRebar)
                gradeRebar = e4.LookupParameter("Grade Rebar").AsString()
                group = e4.LookupParameter("Group").AsString()
                if group is None or group == "":
                    group = "None Defined"
                location = e4.LookupParameter("Location").AsString()
                if location is None or location == "":
                    location = "None Defined"
                volRebar = (e4.get_Parameter(BuiltInParameter.REINFORCEMENT_VOLUME).AsDouble()) * 0.028317
                weigthRebar = volRebar * 7850
                totalLengthRebar = (e4.get_Parameter(BuiltInParameter.REBAR_ELEM_TOTAL_LENGTH).AsDouble()) * 0.3048

                p4.append(hostCat)  # Name host Category 0
                p4.append(partition)  # Name Footing Rebar 1
                p4.append(value4)  # Type Rebar 2
                p4.append(gradeRebar)  # Grade Rebar 3
                p4.append(group)  # Group Footing Rebar 4
                p4.append(location)  # Location Footing Rebar 5
                p4.append(volRebar)  # Volume Rebar 6
                p4.append(weigthRebar)  # Weigth Rebar 7
                p4.append(totalLengthRebar)  # Total Length Rebar 8

                out4.append(p4)

        if len(out4) > 0:
            sort_Patition = (sorted(set([e[1] for e in out4])))  # Sorted Name Footing Rebar
            sort_TypeRebar = (sorted(set([e[2] for e in out4])))  # Sorted Type Rebar
            sort_GradeRebar = (sorted(set([e[3] for e in out4])))  # Sorted Location
            sort_Group = (sorted(set([e[4] for e in out4])))  # Sorted Group
            sort_Location = (sorted(set([e[5] for e in out4])))  # Sorted Location

            nstrength = 0
            n = 0
            for i in sort_GradeRebar:
                for j in sort_TypeRebar:
                    n = len([v for v in out4 if v[3] == i and v[2] == j])
                    if n > 0:
                        nstrength = nstrength + 1

            rowsUp1 = rowsUp - 2
            rowsRebarStart = rowsUp1 + 2
            rowsRebarEnd = nstrength + 3 * len(sort_GradeRebar) + 2
            rowsUp = rowsUp1 + rowsRebarEnd + 2
            rowRight(rowsUp1, rowsUp)

            writeDicBold({20143: rowsUp1})
            change = 0.0
            t1 = 0.0
            t2 = 0
            total_wei = 0
            GrandTotal_wei = 0
            for i in sort_GradeRebar:
                r1 = "Deformed steel rod reinforcement to {} as specified :".format(i)
                ws1.write(rowsUp1 + 2 + t2, 4, r1, option1)
                rowsTT = rowsUp1 + 4 + t2
                for j in sort_TypeRebar:
                    t = [v for v in out4 if v[3] == i and v[2] == j]
                    total_wei = round(sum(float(c[7]) for c in t), 2)
                    if len(t) != 0:
                        t1 = t1 + 0.001
                        change = 20143 + t1
                        try:
                            k1 = "- " + j
                        except:
                            j = "None data"
                            k1 = "- " + j
                        ws1.write(rowsTT, 1, change, option13)
                        ws1.write(rowsTT, 4, k1, option6)
                        ws1.write(rowsTT, 7, 'kg.', option8)
                        ws1.write(rowsTT, 10, total_wei, option9)
                        ws1.write_formula(rowsTT, 16, '=K{0} * N{1}'.format(rowsTT + 1, rowsTT + 1), option9)
                        ws1.write_formula(rowsTT, 22, '=K{0} * T{1}'.format(rowsTT + 1, rowsTT + 1), option9)
                        ws1.write_formula(rowsTT, 25, '= Q{0} + W{1}'.format(rowsTT + 1, rowsTT + 1), option9)
                        ws1.write_formula(rowsTT, 13, "", option15)
                        ws1.write_formula(rowsTT, 19, "", option15)
                        rowsTT = rowsTT + 1
                        GrandTotal_wei = GrandTotal_wei + total_wei
                        t2 = t2 + 1
                t2 = t2 + 3

        subTotal(rowsStart, rowsUp - 2, "2014")
    status = 0
    return rowsUp + 1, status


def jointCon(rowsUp):
    genericModel = DB.FilteredElementCollector(doc).OfCategory(DB.BuiltInCategory.OST_GenericModel) \
        .WhereElementIsNotElementType() \
        .ToElements()
    out = []
    familyType = []
    for e in genericModel:
        p = []
        id = e.Id
        name = e.Name
        level = e.get_Parameter(BuiltInParameter.ELEM_FAMILY_PARAM)
        value = checkParameter(level)

        if value.upper() == "EXPANSION JOINT" or value.upper() == "CONTRACTION JOINT" or value.upper() == "LONGITUDINAL JOINT":
            nameGeneric = e.LookupParameter("Name Genreric").AsString()
            if nameGeneric == "" or nameGeneric is None:
                nameGeneric = "None Data"
            size = e.LookupParameter("Size").AsString()
            if size == "" or size is None:
                size = "None Data"
            location = e.LookupParameter("Location").AsString()
            if location == "" or location is None:
                location = "None Data"
            length = (e.get_Parameter(BuiltInParameter.FAMILY_LINE_LENGTH_PARAM).AsDouble()) * 0.3048

            p.append(value.upper())  # Level 0
            p.append(nameGeneric)  # Name Generic 1
            p.append(size)  # size 2
            p.append(location)  # Location(Level) 3
            p.append(length)  # Length 4

            out.append(p)

    # print("out = {}".format(out))

    # check1 = [v for v in out if v[1] == "EXPANSION JOINT" or v[1] == "CONTRACTION JOINT" or v[1] == "LONGITUDINAL JOINT"]
    if len(out) == 0:
        status = 1
        return rowsUp, status
    else:
        if statusFooting == 1 and statusColumn == 1 and statusBeam == 1 and statusFloor == 1 and statusCoreWall == 1:
            # print("rowsUp = {}".format(rowsUp))
            rowsUp1 = rowsUp
            writeDicUnderline({2: rowsUp1})
            rowsUp = rowsUp1 + 2
            writeDicBold({201: rowsUp, 2015: rowsUp + 2})
            rowsUp = rowsUp + 4
            # print("rowsUp1 = {}".format(rowsUp))
            # rowRight(rowsUp1, rowsUp)
        elif statusFooting == 0 and statusColumn == 1 and statusBeam == 1 and statusFloor == 1 and statusCoreWall == 1:
            # print("rowsUp1 = {}".format(rowsUp))
            rowsUp1 = rowsUp
            # rowRight(rowsUp1, rowsUp)
            writeDicUnderline({2: rowsUp1})
            rowsUp = rowsUp1 + 2
            writeDicBold({201: rowsUp, 2015: rowsUp + 2})
            rowsUp = rowsUp + 4
            # rowRight(rowsUp1, rowsUp)
        elif statusFooting == 0 and statusColumn == 0 or statusBeam == 0 or statusFloor == 0 or statusCoreWall == 0:
            # print("rowsUp2 = {} ".format(rowsUp))
            rowsUp1 = rowsUp - 1
            # writeDicUnderline({2: rowsUp1})
            # rowsUp = rowsUp1+2
            writeDicBold({2015: rowsUp1})
            rowsUp = rowsUp + 1
            # rowRight(rowsUp1, rowsUp)

        check1 = [v for v in out if v[0] == "EXPANSION JOINT"]
        # if check1 != 0:
        # print("check1 = {}".format(check1))
        if len(check1) == 0:
            rowsUp = rowsUp
        else:
            sort_NameExpansionJoint = (sorted(set([e[1] for e in check1])))  # Sorted Name Expansion Joint
            sort_size = (sorted(set([e[2] for e in check1])))  # Sorted Size
            sort_Location = (sorted(set([e[3] for e in check1])))  # Sorted Location

            nstrength = 0
            n = 0
            for i in sort_NameExpansionJoint:
                for j in sort_size:
                    for k in sort_Location:
                        n = len([v for v in check1 if v[1] == i and v[2] == j and v[3] == k])
                        # print(v)
                        if n > 0:
                            nstrength = nstrength + 1
            # print("nstrength = {}".format(nstrength))
            rowsStart = rowsUp + 2
            rowsExpanStart = rowsUp
            rowsExpanEnd = rowsExpanStart + nstrength + 2
            rowsUp = rowsExpanEnd + 3

            # print("rowsUp3 = {} ".format(rowsUp))
            # rowRight(rowsUp1, rowsUp)

            writeDicBold({20151: rowsExpanStart})
            rowsTT = rowsExpanStart + 2
            total_length = 0.0
            GrandTotal_length = 0.0
            t1 = 0.0
            change = 0.0
            for i in sort_NameExpansionJoint:
                for j in sort_size:
                    for k in sort_Location:
                        t = [v for v in check1 if v[1] == i and v[2] == j and v[3] == k]
                        # print("t = {}".format(t))
                        total_length = round(sum(float(c[4]) for c in t), 2)
                        # total_formwork = round(sum(float(c[5]) for c in t),2)
                        GrandTotal_length = GrandTotal_length + total_length
                        if len(t) != 0:
                            t1 = t1 + 0.001
                            change = 20151 + t1

                            try:
                                k1 = i + ";" + t[0][2] + ";" + t[0][3] + " - " + "(" + str(len(t)) + " Nos" + ")"
                            except:
                                i = "None data"
                                l1 = "None data"
                                m1 = "None data"
                                k1 = j + "-" + l1 + ";" + m1
                            ws1.write(rowsTT, 1, change, option13)
                            ws1.write(rowsTT, 4, k1, option6)
                            ws1.write(rowsTT, 7, 'm.', option8)
                            ws1.write(rowsTT, 10, total_length, option9)
                            ws1.write_formula(rowsTT, 16, '=K{0} * N{1}'.format(rowsTT + 1, rowsTT + 1), option9)
                            ws1.write_formula(rowsTT, 22, '=K{0} * T{1}'.format(rowsTT + 1, rowsTT + 1), option9)
                            ws1.write_formula(rowsTT, 25, '= Q{0} + W{1}'.format(rowsTT + 1, rowsTT + 1), option9)
                            ws1.write_formula(rowsTT, 13, "", option15)
                            ws1.write_formula(rowsTT, 19, "", option15)
                            rowsTT = rowsTT + 1

        # print("rowsUp4 = {} ".format(rowsUp))
        check2 = [v for v in out if v[0] == "CONTRACTION JOINT"]
        # if check2 != 0:
        # print("check2 = {}".format(check2))
        if len(check2) == 0:
            rowsUp = rowsUp
        else:
            sort_NameContractionJoint = (sorted(set([e[1] for e in check2])))  # Sorted Name Expansion Joint
            sort_size = (sorted(set([e[2] for e in check2])))  # Sorted Size
            sort_Location = (sorted(set([e[3] for e in check2])))  # Sorted Location

            nstrength = 0
            n = 0
            for i in sort_NameContractionJoint:
                for j in sort_size:
                    for k in sort_Location:
                        n = len([v for v in check2 if v[1] == i and v[2] == j and v[3] == k])
                        # print(v)
                        if n > 0:
                            nstrength = nstrength + 1
            # print("nstrength = {}".format(nstrength))
            rowsContracStart = rowsUp - 2
            rowsContracEnd = rowsContracStart + nstrength + 2
            # rowsStart =rowsContracStart+2
            rowsUp = rowsContracEnd + 3

            # print("rowsUp5 = {} ".format(rowsUp))
            # rowRight(rowsUp1, rowsUp)

            writeDicBold({20152: rowsContracStart})
            rowsTT = rowsContracStart + 2
            total_length = 0.0
            GrandTotal_length = 0.0
            t1 = 0.0
            change = 0.0
            for i in sort_NameContractionJoint:
                for j in sort_size:
                    for k in sort_Location:
                        t = [v for v in check2 if v[1] == i and v[2] == j and v[3] == k]
                        # print("t = {}".format(t))
                        total_length = round(sum(float(c[4]) for c in t), 2)
                        # total_formwork = round(sum(float(c[5]) for c in t),2)
                        GrandTotal_length = GrandTotal_length + total_length
                        if len(t) != 0:
                            t1 = t1 + 0.001
                            change = 20152 + t1

                            try:
                                k1 = i + ";" + t[0][2] + ";" + t[0][3] + " - " + "(" + str(len(t)) + " Nos" + ")"
                            except:
                                i = "None data"
                                l1 = "None data"
                                m1 = "None data"
                                k1 = j + "-" + l1 + ";" + m1
                            ws1.write(rowsTT, 1, change, option13)
                            ws1.write(rowsTT, 4, k1, option6)
                            ws1.write(rowsTT, 7, 'm.', option8)
                            ws1.write(rowsTT, 10, total_length, option9)
                            ws1.write_formula(rowsTT, 16, '=K{0} * N{1}'.format(rowsTT + 1, rowsTT + 1), option9)
                            ws1.write_formula(rowsTT, 22, '=K{0} * T{1}'.format(rowsTT + 1, rowsTT + 1), option9)
                            ws1.write_formula(rowsTT, 25, '= Q{0} + W{1}'.format(rowsTT + 1, rowsTT + 1), option9)
                            ws1.write_formula(rowsTT, 13, "", option15)
                            ws1.write_formula(rowsTT, 19, "", option15)
                            rowsTT = rowsTT + 1

        rowRight(rowsUp1, rowsUp)
        subTotal(rowsStart, rowsUp - 2, "2015")
    # print("rowsUp6 = {} ".format(rowsUp))     

    status = 0
    return rowsUp + 1, status


def cobelConc(rowsUp):
    cobelConc = DB.FilteredElementCollector(doc).OfCategory(DB.BuiltInCategory.OST_StructConnections) \
        .WhereElementIsNotElementType() \
        .ToElements()

    rebar = DB.FilteredElementCollector(doc).OfCategory(DB.BuiltInCategory.OST_Rebar) \
        .WhereElementIsNotElementType() \
        .ToElements()

    out = []
    for e in cobelConc:
        p = []
        id = e.Id
        name = e.Name
        level = e.get_Parameter(BuiltInParameter.ELEM_FAMILY_PARAM)
        value = checkParameter(level)
        if value.upper() == "STRUCTURAL CONCRETE COBELS":
            nameColumn = e.LookupParameter("Name Column").AsString()
            if nameColumn == "" or nameColumn is None:
                nameColumn = "Name of COBELS has not defined"
            size = e.LookupParameter("Size").AsString()
            if size == "" or size is None:
                size = "Size has not defined"
            location = e.LookupParameter("Location").AsString()
            if location == "" or location is None:
                location = "Location has not defined"
            strengthCon = e.LookupParameter("Strength Concrete").AsString()
            if strengthCon == "" or strengthCon is None:
                strengthCon = "Strength of concrete has not defined"
            volume = (e.get_Parameter(BuiltInParameter.HOST_VOLUME_COMPUTED).AsDouble()) * 0.028317
            formwork = (e.LookupParameter("formwork").AsDouble()) * 0.092903

            p.append(value.upper())  # Level 0
            p.append(nameColumn)  # Name Generic 1
            p.append(size)  # size 2
            p.append(location)  # Location(Level) 3
            p.append(strengthCon)  # Location(Level) 4
            p.append(volume)  # Volume Concrete Cobel 5
            p.append(formwork)  # Formwork Concrete Cobel 6

            out.append(p)

    # print("out = {}".format(out))

    if len(out) == 0:
        status = 1
        return rowsUp, status
    else:
        sort_NameColumn = (sorted(set([e[1] for e in out])))  # Sorted Name Colbel
        sort_size = (sorted(set([e[2] for e in out])))  # Sorted size Colbel
        sort_Location = (sorted(set([e[3] for e in out])))  # Sorted Location
        sort_Strength = (sorted(set([e[4] for e in out])))  # Sorted Strength Concrete
        nstrength = 0
        n = 0
        for i in sort_Strength:
            for j in sort_NameColumn:
                for k in sort_size:
                    for l in sort_Location:
                        n = len([v for v in out if v[4] == i and v[1] == j and v[2] == k and v[3] == l])
                        # print(n)
                        if n > 0:
                            nstrength = nstrength + 1
        # print (nstrength)
        rowsUp1 = rowsUp - 1
        rowsConCorbelStart = rowsUp1
        rowsConCorbelEnd = nstrength + 3 * len(sort_Strength) + 1
        rowFormCorbelStart = rowsUp1 + rowsConCorbelEnd + 3
        rowFormCorbelEnd = nstrength + 7
        rowsUp = rowsUp1 + rowsConCorbelEnd + rowFormCorbelEnd + 3

        rowRight(rowsUp1, rowsUp)
        rowsStart = rowsConCorbelStart + 6
        if len(out) != 0:
            if statusColumn == 1 and statusBeam == 1 and statusFloor == 1 and statusCoreWall == 1 and statusCobelConc == 1:
                rowsUp1 = rowsUpFooting - 1
                rowsConCorbelStart = rowsUp1 + 5
                rowsConCorbelEnd = nstrength + 3 * len(sort_Strength) + 1
                rowFormCorbelStart = rowsUp1 + rowsConCorbelEnd + 8
                rowFormCorbelEnd = nstrength + 12
                rowsUp = rowsUp1 + rowsConCorbelEnd + rowFormCorbelEnd + 3
                # rowTop(rowsUp1)
                # rowCornerTopRight(rowsUp1)
                rowRight(rowsUp1 + 1, rowsUp)
                writeDicUnderline({2: rowsUp1 + 1})
                writeDicBold({201: rowsUp1 + 3})
                rowsStart = rowsConCorbelStart + 6
                # rowsUp1=rowsUp1+5
                # rowsConBeamStart=rowsUp1+3

            writeDicBold({2016: rowsConCorbelStart, 20161: rowsConCorbelStart + 2})  # Start STRUCTURAL CONCRETE BEAMS
            t2 = 0
            change = 0.0
            t1 = 0.0
            for i in sort_Strength:
                r1 = "Reinfored concrete strength {} as specified :".format(i)
                ws1.write(rowsConCorbelStart + 4 + t2, 4, r1, option1)
                rowsTT = rowsConCorbelStart + 6 + t2
                # print("Reinforced concrete strength {} as specified  : ".format(i))
                total_vol = 0
                GrandTotal_vol = 0

                for j in sort_NameColumn:
                    for k in sort_size:
                        for l in sort_Location:
                            t = [v for v in out if v[4] == i and v[1] == j and v[2] == k and v[3] == l]
                            # print (t)
                            total_vol = round(sum(float(c[5]) for c in t), 2)
                            # total_formwork = round(sum(float(c[5]) for c in t),2)
                            GrandTotal_vol = GrandTotal_vol + total_vol
                            if len(t) != 0:
                                t1 = t1 + 0.001
                                change = 20161 + t1

                                try:
                                    k1 = j + "-" + t[0][2] + ";" + t[0][3] + " - " + "(" + str(len(t)) + " Nos" + ")"
                                except:
                                    j = "None data"
                                    l1 = "None data"
                                    m1 = "None data"
                                    k1 = j + "-" + l1 + ";" + m1

                                ws1.write(rowsTT, 1, change, option13)
                                ws1.write(rowsTT, 4, k1, option6)
                                ws1.write(rowsTT, 7, 'm3', option8)
                                ws1.write(rowsTT, 10, total_vol, option9)
                                ws1.write_formula(rowsTT, 16, '=K{0} * N{1}'.format(rowsTT + 1, rowsTT + 1), option9)
                                ws1.write_formula(rowsTT, 22, '=K{0} * T{1}'.format(rowsTT + 1, rowsTT + 1), option9)
                                ws1.write_formula(rowsTT, 25, '= Q{0} + W{1}'.format(rowsTT + 1, rowsTT + 1), option9)
                                ws1.write_formula(rowsTT, 13, "", option15)
                                ws1.write_formula(rowsTT, 19, "", option15)
                                rowsTT = rowsTT + 1
                                t2 = t2 + 1
                t2 = t2 + 3

        if len(out) != 0:
            writeDicBold({20162: rowFormCorbelStart})
            r2 = "Sawn formwork to sided of :"
            ws1.write((rowFormCorbelStart + 2), 4, r2, option1)
            rowsTT = rowFormCorbelStart + 4
            total_formwork = 0.0
            t1 = 0
            GrandTotal_area = 0

            for i in sort_NameColumn:
                for j in sort_size:
                    for k in sort_Location:
                        total_formwork = 0
                        t = [v for v in out if v[1] == i and v[2] == j and v[3] == k]
                        # print (t)
                        total_formwork = round(sum(float(c[6]) for c in t), 2)
                        # print (total_area)
                        GrandTotal_area = GrandTotal_area + total_formwork
                        if len(t) != 0:
                            t1 = t1 + 0.001
                            change = 20162 + t1

                            try:
                                k1 = i + "-" + t[0][2] + ";" + t[0][3] + " - " + "(" + str(len(t)) + " Nos" + ")"
                            except:
                                i = "None data"
                                l1 = "None data"
                                m = "None data"
                                k1 = i + "-" + l1 + ";" + m

                            ws1.write(rowsTT, 1, change, option13)
                            ws1.write(rowsTT, 4, k1, option6)
                            ws1.write(rowsTT, 7, 'm2', option8)
                            ws1.write(rowsTT, 10, total_formwork, option9)
                            ws1.write_formula(rowsTT, 16, '=K{0} * N{1}'.format(rowsTT + 1, rowsTT + 1), option9)
                            ws1.write_formula(rowsTT, 22, '=K{0} * T{1}'.format(rowsTT + 1, rowsTT + 1), option9)
                            ws1.write_formula(rowsTT, 25, '= Q{0} + W{1}'.format(rowsTT + 1, rowsTT + 1), option9)
                            ws1.write_formula(rowsTT, 13, "", option15)
                            ws1.write_formula(rowsTT, 19, "", option15)
                            rowsTT = rowsTT + 1

        out4 = []
        for e4 in rebar:
            p4 = []
            hostCat = e4.get_Parameter(BuiltInParameter.REBAR_HOST_CATEGORY).AsInteger()
            if hostCat == 0:
                partition = e4.get_Parameter(BuiltInParameter.NUMBER_PARTITION_PARAM).AsString()
                if partition == "" or partition is None:
                    partition = "Partition has not defined"
                typeRebar = e4.get_Parameter(BuiltInParameter.ELEM_TYPE_PARAM)
                value4 = checkParameter(typeRebar)
                gradeRebar = e4.LookupParameter("Grade Rebar").AsString()
                group = e4.LookupParameter("Group").AsString()
                if group is None or group == "":
                    group = "Group has not defined"
                location = e4.LookupParameter("Location").AsString()
                if location is None or location == "":
                    location = "Location has not defined"
                volRebar = (e4.get_Parameter(BuiltInParameter.REINFORCEMENT_VOLUME).AsDouble()) * 0.028317
                weigthRebar = volRebar * 7850
                totalLengthRebar = (e4.get_Parameter(BuiltInParameter.REBAR_ELEM_TOTAL_LENGTH).AsDouble()) * 0.3048

                p4.append(hostCat)  # Name host Category 0
                p4.append(partition)  # Name Footing Rebar 1
                p4.append(value4)  # Type Rebar 2
                p4.append(gradeRebar)  # Grade Rebar 3
                p4.append(group)  # Group Footing Rebar 4
                p4.append(location)  # Location Footing Rebar 5
                p4.append(volRebar)  # Volume Rebar 6
                p4.append(weigthRebar)  # Weigth Rebar 7
                p4.append(totalLengthRebar)  # Total Length Rebar 8

                out4.append(p4)

        if len(out4) > 0:
            sort_Patition = (sorted(set([e[1] for e in out4])))  # Sorted Name Footing Rebar
            sort_TypeRebar = (sorted(set([e[2] for e in out4])))  # Sorted Type Rebar
            sort_GradeRebar = (sorted(set([e[3] for e in out4])))  # Sorted Location
            sort_Group = (sorted(set([e[4] for e in out4])))  # Sorted Group
            sort_Location = (sorted(set([e[5] for e in out4])))  # Sorted Location

            nstrength = 0
            n = 0
            for i in sort_GradeRebar:
                for j in sort_TypeRebar:
                    n = len([v for v in out4 if v[3] == i and v[2] == j])
                    if n > 0:
                        nstrength = nstrength + 1

            rowsUp1 = rowsUp - 2
            rowsRebarStart = rowsUp1 + 2
            rowsRebarEnd = nstrength + 3 * len(sort_GradeRebar) + 2
            rowsUp = rowsUp1 + rowsRebarEnd + 2
            rowRight(rowsUp1, rowsUp)

            writeDicBold({20163: rowsUp1})
            change = 0.0
            t1 = 0.0
            t2 = 0
            total_wei = 0
            GrandTotal_wei = 0
            for i in sort_GradeRebar:
                r1 = "Deformed steel rod reinforcement to {} as specified :".format(i)
                ws1.write(rowsUp1 + 2 + t2, 4, r1, option1)
                rowsTT = rowsUp1 + 4 + t2
                for j in sort_TypeRebar:
                    t = [v for v in out4 if v[3] == i and v[2] == j]
                    total_wei = round(sum(float(c[7]) for c in t), 2)
                    if len(t) != 0:
                        t1 = t1 + 0.001
                        change = 20163 + t1
                        try:
                            k1 = "- " + j
                        except:
                            j = "None data"
                            k1 = "- " + j
                        ws1.write(rowsTT, 1, change, option13)
                        ws1.write(rowsTT, 4, k1, option6)
                        ws1.write(rowsTT, 7, 'kg.', option8)
                        ws1.write(rowsTT, 10, total_wei, option9)
                        ws1.write_formula(rowsTT, 16, '=K{0} * N{1}'.format(rowsTT + 1, rowsTT + 1), option9)
                        ws1.write_formula(rowsTT, 22, '=K{0} * T{1}'.format(rowsTT + 1, rowsTT + 1), option9)
                        ws1.write_formula(rowsTT, 25, '= Q{0} + W{1}'.format(rowsTT + 1, rowsTT + 1), option9)
                        ws1.write_formula(rowsTT, 13, "", option15)
                        ws1.write_formula(rowsTT, 19, "", option15)
                        rowsTT = rowsTT + 1
                        GrandTotal_wei = GrandTotal_wei + total_wei
                        t2 = t2 + 1
                t2 = t2 + 3

        subTotal(rowsStart, rowsUp - 2, "2016")

    status = 0
    return rowsUp + 1, status


def steelColumn(rowsUp):
    columns = DB.FilteredElementCollector(doc).OfCategory(DB.BuiltInCategory.OST_StructuralColumns) \
        .WhereElementIsNotElementType() \
        .ToElements()

    out = []
    familyType = []
    for e in columns:
        p = []
        # id = e.Id
        # name = e.Name
        strType = e.StructuralMaterialType
        if strType == StructuralMaterialType.Steel:
            id = e.Id
            name = e.Name
            level = e.get_Parameter(BuiltInParameter.SCHEDULE_LEVEL_PARAM)
            value = checkParameter(level)
            strMat = e.get_Parameter(BuiltInParameter.STRUCTURAL_MATERIAL_PARAM)
            value1 = checkParameter(strMat)
            length = (e.get_Parameter(BuiltInParameter.INSTANCE_LENGTH_PARAM).AsDouble()) * 0.3048
            volume = (e.get_Parameter(BuiltInParameter.HOST_VOLUME_COMPUTED).AsDouble()) * 0.028317
            nameColumn = e.LookupParameter("Name Column").AsString()
            size = e.LookupParameter("Size").AsString()
            location = e.LookupParameter("Location").AsString()
            fireproofing = e.LookupParameter("3-HR Fireproofing (or not)").AsInteger()
            fireproofingName = e.LookupParameter("3-HR Fireproofing Name").AsString()
            try:
                group = e.LookupParameter("Group").AsString()
            except:
                group1 = "Group has not defined"

            if group is None or group == "":
                group1 = "Group has not defined"
            else:
                group1 = group

            id = e.GetTypeId()
            if id == ElementId.InvalidElementId:
                familyType.append(None)
            else:
                familyType.append(doc.GetElement(id))
            # print(familyType)
            for i in familyType:
                try:
                    weightId = i.LookupParameter("Weight Steel").AsDouble()
                    # width = (i.LookupParameter("b").AsDouble())*0.3048
                except:
                    weightId = 0.0
                finally:
                    weightSteel = weightId * length
                try:
                    surfaceId = (i.LookupParameter("Perimeter").AsDouble())
                except:
                    surfaceId = 0.0
                finally:
                    surfaceArea = surfaceId * length

            p.append(id)  # Id 0
            p.append(strType)  # Structural Type 1
            p.append(nameColumn)  # Name Column 2
            p.append(size)  # size 3
            p.append(location)  # Location(Level) 4
            p.append(weightId)  # Weight Steel per meter 5
            p.append(volume)  # Volume 6
            p.append(weightSteel)  # Weight Steel 7
            p.append(surfaceArea)  # Surface Area 8
            p.append(fireproofing)  # 3-HR FIREPROOFING 9
            p.append(group1)  # Group 10
            p.append(fireproofingName)  # 3-HR FIREPROOFING NAME 11
            out.append(p)

    # print(out)
    check1 = [v for v in out if v[1] == StructuralMaterialType.Steel]
    if len(check1) == 0:
        status = 1
        return rowsUp, status
    else:
        sort_NameColumn = (sorted(set([e[2] for e in check1])))  # Sorted Name Steel
        sort_Size = (sorted(set([e[3] for e in check1])))  # Sorted Size
        sort_Level = (sorted(set([e[4] for e in check1])))  # Sorted Location
        sort_Group = (sorted(set([e[10] for e in check1])))  # Sorted Group
        sort_3HRName = (sorted(set([e[11] for e in check1])))  # Sorted 3-HR FIREPROOFING NAME

        nstrength = 0
        nstrength2 = 0
        nstrength3 = 0
        nstrength4 = "N"
        nstrength5 = "N"
        n = 0
        n2 = 0
        n3 = 0
        u1 = 0

        check2 = [v for v in check1 if v[9] == 1]
        if len(check2) == 0:
            for g in sort_Group:
                for i in sort_NameColumn:
                    for j in sort_Level:
                        n = len([v for v in check1 if v[2] == i and v[4] == j and v[10] == g])
                        # print(n)
                        if n > 0:
                            nstrength = nstrength + 1
        else:
            # for g in sort_Group:
            for h in sort_3HRName:
                for g in sort_Group:
                    for i in sort_NameColumn:
                        for j in sort_Level:
                            n = len([v for v in check1 if v[2] == i and v[4] == j and v[10] == g])
                            # print(n)
                            if n > 0:
                                nstrength = nstrength + 1
                            n2 = len([v for v in check1 if
                                      v[2] == i and v[4] == j and v[9] == 1 and v[10] == g and v[11] == h])
                            n3 = len([v for v in check1 if
                                      v[2] == i and v[4] == j and v[9] == 0 and v[10] == g and v[11] == h])
                            # print("n2 = {}".format(n2))
                            if n2 > 0:
                                nstrength2 = nstrength2 + 1
                                nstrength4 = "N"
                            if n3 > 0:
                                nstrength3 = nstrength3 + 1
                                nstrength4 = "Y"
                        if nstrength4 == "Y":
                            nstrength5 = "Y"
                        else:
                            nstrength5 = "N"
                    if nstrength5 == "Y":
                        nstrength5 = "Y"
                        u1 = u1 + 1
                    else:
                        nstrength5 = "N"

        rowsUp1 = rowsUp - 1
        # print("rowsUp1 = {}".format(rowsUp1))
        # print("nstrength2 = {}".format(nstrength2))
        # print("nstrength3 = {}".format(nstrength3))
        rowsWeightStart = rowsUp1
        rowsWeightEnd = nstrength + len(sort_Group) * 3 + rowsWeightStart + 6
        rowSurfaceStart = rowsWeightEnd
        # print("rowSurfaceStart = {}".format(rowSurfaceStart)) 
        rowSurfaceEnd = nstrength + len(sort_Group) * 3 + rowSurfaceStart + 4
        if nstrength2 == 0:
            rowsUp = rowSurfaceEnd + 2
        else:
            rowSurfaceStart2 = rowSurfaceEnd
            rowSurfaceEnd2 = nstrength2 + (len(sort_Group) - u1) * 3 + rowSurfaceStart2 + (len(sort_3HRName)) * 2 + 2
            # rowSurfaceEnd2 = nstrength2+(len(sort_Group))*3 +rowSurfaceStart2+(len(sort_3HRName))*2+2
            rowsUp = rowSurfaceEnd2 + 2

        rowsStart = rowsWeightStart + 8

        if statusColumn == 1 and statusBeam == 1 and statusFloor == 1 and statusCoreWall == 1:
            writeDicBold({2: rowsUpFooting})
            rowsUp1 = rowsUpFooting
            rowsWeightStart = rowsUp1 + 2
            rowsWeightEnd = nstrength + len(sort_Group) * 3 + rowsWeightStart + 4 + 2
            rowSurfaceStart = rowsWeightEnd
            rowSurfaceEnd = nstrength + len(sort_Group) * 3 + rowSurfaceStart + 4
            if nstrength2 == 0:
                rowsUp = rowSurfaceEnd + 2
            else:
                rowSurfaceStart2 = rowSurfaceEnd
                rowSurfaceEnd2 = nstrength2 + (len(sort_Group) - u1) * 3 + rowSurfaceStart2 + (
                    len(sort_3HRName)) * 2 + 2
                # rowSurfaceEnd2 = nstrength2+(len(sort_Group))*3 +rowSurfaceStart2+(len(sort_3HRName))*2+2
                rowsUp = rowSurfaceEnd2 + 2
            rowsStart = rowsWeightStart + 8

        rowRight(rowsUp1, rowsUp)
        writeDicBold({202: rowsWeightStart, 2021: rowsWeightStart + 2, 20211: rowsWeightStart + 4})
        rowsTT = rowsWeightStart + 6
        change = 0.0
        t1 = 0.0
        total_wei = 0
        GrandTotal_wei = 0

        for g in sort_Group:
            g1 = "TO " + g
            ws1.write(rowsTT, 4, g1, option1)
            rowsTT = rowsTT + 2
            for i in sort_NameColumn:
                for j in sort_Level:
                    t = [v for v in check1 if v[2] == i and v[4] == j and v[10] == g]
                    # print (t)
                    total_wei = round(sum(float(c[7]) for c in t), 2)
                    # total_area = round(sum(float(c[7]) for c in t),2)

                    if len(t) != 0:
                        t1 = t1 + 0.001
                        change = 20211 + t1
                        try:
                            k1 = i + ";" + t[0][3] + ";" + t[0][4] + " - " + "(" + str(len(t)) + " Nos" + ")"
                        except:
                            i = "None data"
                            l1 = "None data"
                            m = "None data"
                            k1 = i + ";" + l1 + ";" + m
                        # print("{} :{} = {:,.2f} kg.".format(change, k1, total_wei))
                        ws1.write(rowsTT, 1, change, option13)
                        ws1.write(rowsTT, 4, k1, option6)
                        ws1.write(rowsTT, 7, 'kg.', option8)
                        ws1.write(rowsTT, 10, total_wei, option9)
                        ws1.write_formula(rowsTT, 16, '=K{0} * N{1}'.format(rowsTT + 1, rowsTT + 1), option9)
                        ws1.write_formula(rowsTT, 22, '=K{0} * T{1}'.format(rowsTT + 1, rowsTT + 1), option9)
                        ws1.write_formula(rowsTT, 25, '= Q{0} + W{1}'.format(rowsTT + 1, rowsTT + 1), option9)
                        ws1.write_formula(rowsTT, 13, "", option15)
                        ws1.write_formula(rowsTT, 19, "", option15)
                        rowsTT = rowsTT + 1
                        GrandTotal_wei = GrandTotal_wei + total_wei
            rowsTT = rowsTT + 1

        # print("rowSurfaceStart = {}".format(rowSurfaceStart))
        writeDicBold({20212: rowSurfaceStart, 202121: rowSurfaceStart + 2})
        rowsTT = rowSurfaceStart + 4
        change = 0.0
        t1 = 0.0
        total_area = 0.0
        GrandTotal_area = 0.0

        for g in sort_Group:
            g1 = "TO " + g
            ws1.write(rowsTT, 4, g1, option1)
            rowsTT = rowsTT + 2
            for i in sort_NameColumn:
                for j in sort_Level:
                    t = [v for v in check1 if v[2] == i and v[4] == j and v[10] == g]
                    # total_area = sum(float(c[8]) for c in t)
                    total_area = round(sum(float(c[8]) for c in t), 2)
                    # GrandTotal_area = GrandTotal_area+total_area
                    if len(t) != 0:
                        t1 = t1 + 0.001
                        change = 202121 + t1
                        try:
                            k1 = i + ";" + t[0][3] + ";" + t[0][4] + " - " + "(" + str(len(t)) + " Nos" + ")"
                        except:
                            i = "None data"
                            l1 = "None data"
                            m = "None data"
                            k1 = i + ";" + l1 + ";" + m
                        ws1.write(rowsTT, 1, change, option13)
                        ws1.write(rowsTT, 4, k1, option6)
                        ws1.write(rowsTT, 7, 'm2', option8)
                        ws1.write(rowsTT, 10, total_area, option9)
                        ws1.write_formula(rowsTT, 16, '=K{0} * N{1}'.format(rowsTT + 1, rowsTT + 1), option9)
                        ws1.write_formula(rowsTT, 22, '=K{0} * T{1}'.format(rowsTT + 1, rowsTT + 1), option9)
                        ws1.write_formula(rowsTT, 25, '= Q{0} + W{1}'.format(rowsTT + 1, rowsTT + 1), option9)
                        ws1.write_formula(rowsTT, 13, "", option15)
                        ws1.write_formula(rowsTT, 19, "", option15)
                        rowsTT = rowsTT + 1

                        # print("{} :{} = {:,.2f} sq.m.".format(change,k1,total_area))
                        GrandTotal_area = GrandTotal_area + total_area
            rowsTT = rowsTT + 1

        if nstrength2 != 0:
            writeDicBold({202122: rowSurfaceStart2})
            rowsTT = rowSurfaceStart2 + 2
            change = 0.0
            t1 = 0.0
            total_area = 0.0
            GrandTotal_area = 0.0

            # print("sort_Group = {}".format(sort_Group))
            # print("sort_NameBeam = {}".format(sort_NameBeam))
            # print("sort_Level = {}".format(sort_Level))
            for h in sort_3HRName:
                ws1.write(rowsTT, 4, h, option1)
                rowsTT = rowsTT + 2
                for g in sort_Group:
                    g1 = "TO " + g
                    a1 = 1
                    n4 = 0
                    for i in sort_NameColumn:
                        # print("i = {}".format(i))
                        for j in sort_Level:
                            # print("j = {}".format(j))
                            t = [v for v in check1 if
                                 v[2] == i and v[4] == j and v[9] == 1 and v[10] == g and v[11] == h]
                            # print("len(t) = {}".format(len(t)))
                            # print("a1 = {}".format(a1))
                            if len(t) != 0 and a1 == 1:
                                ws1.write(rowsTT, 4, g1, option1)
                                rowsTT = rowsTT + 2
                                n4 = n4 + 1

                            total_area = round(sum(float(c[8]) for c in t), 2)
                            # GrandTotal_area = GrandTotal_area+total_area
                            if len(t) != 0:
                                t1 = t1 + 0.001
                                change = 202122 + t1
                                try:
                                    k1 = i + ";" + t[0][3] + ";" + t[0][4] + " - " + "(" + str(len(t)) + " Nos" + ")"
                                except:
                                    i = "None data"
                                    l1 = "None data"
                                    m = "None data"
                                    k1 = i + ";" + l1 + ";" + m
                                ws1.write(rowsTT, 1, change, option13)
                                ws1.write(rowsTT, 4, k1, option6)
                                ws1.write(rowsTT, 7, 'm2', option8)
                                ws1.write(rowsTT, 10, total_area, option9)
                                ws1.write_formula(rowsTT, 16, '=K{0} * N{1}'.format(rowsTT + 1, rowsTT + 1), option9)
                                ws1.write_formula(rowsTT, 22, '=K{0} * T{1}'.format(rowsTT + 1, rowsTT + 1), option9)
                                ws1.write_formula(rowsTT, 25, '= Q{0} + W{1}'.format(rowsTT + 1, rowsTT + 1), option9)
                                ws1.write_formula(rowsTT, 13, "", option15)
                                ws1.write_formula(rowsTT, 19, "", option15)
                                rowsTT = rowsTT + 1

                                # print("{} :{} = {:,.2f} sq.m.".format(change,k1,total_area))
                                GrandTotal_area = GrandTotal_area + total_area
                                a1 = 0
                                # print("len(t) = {}".format(len(t)))
                                # print("a1 = {}".format(a1))
                        # a1 =0
                    if n4 == 0:
                        rowsTT = rowsTT
                        # print("rowsTT = {}".format(rowsTT))
                    else:
                        rowsTT = rowsTT + 1
                        # print("rowsTT1 = {}".format(rowsTT))
                    # rowsTT = rowsTT + 1

        subTotal(rowsStart, rowsUp - 2, "2021")

    status = 0
    return rowsUp + 1, status


def steelBeam(rowsUp):
    beams = DB.FilteredElementCollector(doc).OfCategory(DB.BuiltInCategory.OST_StructuralFraming) \
        .WhereElementIsNotElementType() \
        .ToElements()

    out = []
    # out2 = []
    familyType = []
    for e in beams:
        p = []
        # id = e.Id
        # name = e.Name

        strType = e.StructuralMaterialType
        if strType == StructuralMaterialType.Steel:
            id = e.Id
            name = e.Name
            level = e.get_Parameter(BuiltInParameter.INSTANCE_REFERENCE_LEVEL_PARAM)
            value = checkParameter(level)
            strMat = e.get_Parameter(BuiltInParameter.STRUCTURAL_MATERIAL_PARAM)
            value1 = checkParameter(strMat)
            cutlength = e.get_Parameter(BuiltInParameter.STRUCTURAL_FRAME_CUT_LENGTH).AsDouble() * 0.3048
            volume = (e.get_Parameter(BuiltInParameter.HOST_VOLUME_COMPUTED).AsDouble()) * 0.028317
            nameBeam = e.LookupParameter("Name Beam").AsString()
            size = e.LookupParameter("Size").AsString()
            location = e.LookupParameter("Location").AsString()
            fireproofing = e.LookupParameter("3-HR Fireproofing (or not)").AsInteger()
            fireproofingName = e.LookupParameter("3-HR Fireproofing Name").AsString()
            try:
                group = e.LookupParameter("Group").AsString()
            except:
                group1 = "Group has not defined"

            if group is None or group == "":
                group1 = "Group has not defined"
            else:
                group1 = group
            try:
                fireproofingName = e.LookupParameter("3-HR Fireproofing Name").AsString()
            except:
                fireproofingName = "Name has not defined"

            if fireproofingName is None or fireproofingName == "":
                fireproofingName = "Name has not defined"
            # else:
            #     fireproofingName = fireproofingName

            id = e.GetTypeId()
            if id == ElementId.InvalidElementId:
                familyType.append(None)
            else:
                familyType.append(doc.GetElement(id))
            # print(familyType)
            for i in familyType:
                try:
                    weightId = i.LookupParameter("Weight Steel").AsDouble()
                    # width = (i.LookupParameter("b").AsDouble())*0.3048
                except:
                    weightId = 0.0
                finally:
                    weightSteel = weightId * cutlength
                try:
                    surfaceId = (i.LookupParameter("Perimeter").AsDouble())
                except:
                    surfaceId = 0.0
                finally:
                    surfaceArea = surfaceId * cutlength

            p.append(id)  # Id 0
            p.append(strType)  # Structural Type 1
            p.append(nameBeam)  # Name Beam 2
            p.append(size)  # size 3
            p.append(location)  # Level 4
            p.append(weightId)  # Weight Steel per meter 5
            p.append(volume)  # Volume 6
            p.append(weightSteel)  # Weight Steel 7
            p.append(surfaceArea)  # Surface Area 8
            p.append(fireproofing)  # 3-HR FIREPROOFING 9
            p.append(group1)  # Group 10
            p.append(fireproofingName)  # 3-HR FIREPROOFING NAME 11

            out.append(p)

    # print(out)
    check1 = [v for v in out if v[1] == StructuralMaterialType.Steel]
    if len(check1) == 0:
        status = 1
        return rowsUp, status
    else:
        sort_NameBeam = (sorted(set([e[2] for e in check1])))  # Sorted Name Steel
        sort_Size = (sorted(set([e[3] for e in check1])))  # Sorted Size
        sort_Level = (sorted(set([e[4] for e in check1])))  # Sorted Location
        sort_Group = (sorted(set([e[10] for e in check1])))  # Sorted Group
        sort_3HRName = (sorted(set([e[11] for e in check1])))  # Sorted 3-HR FIREPROOFING NAME
        nstrength = 0
        nstrength2 = 0
        nstrength3 = 0
        nstrength4 = "N"
        nstrength5 = "N"
        n = 0
        n2 = 0
        n3 = 0
        u1 = 0

        check2 = [v for v in check1 if v[9] == 1]
        if len(check2) == 0:
            for g in sort_Group:
                for i in sort_NameBeam:
                    for j in sort_Level:
                        n = len([v for v in check1 if v[2] == i and v[4] == j and v[10] == g])
                        # print(n)
                        if n > 0:
                            nstrength = nstrength + 1
        else:
            for h in sort_3HRName:
                # for h in sort_3HRName:
                for g in sort_Group:
                    for i in sort_NameBeam:
                        for j in sort_Level:
                            n = len([v for v in check1 if v[2] == i and v[4] == j and v[10] == g])
                            # print(n)
                            if n > 0:
                                nstrength = nstrength + 1
                            n2 = len([v for v in check1 if
                                      v[2] == i and v[4] == j and v[9] == 1 and v[10] == g and v[11] == h])
                            n3 = len([v for v in check1 if
                                      v[2] == i and v[4] == j and v[9] == 0 and v[10] == g and v[11] == h])
                            # print("n2 = {}".format(n2))
                            if n2 > 0:
                                nstrength2 = nstrength2 + 1
                                nstrength4 = "N"
                            if n3 > 0:
                                nstrength3 = nstrength3 + 1
                                nstrength4 = "Y"
                        if nstrength4 == "Y":
                            nstrength5 = "Y"
                        else:
                            nstrength5 = "N"
                    if nstrength5 == "Y":
                        nstrength5 = "Y"
                        u1 = u1 + 1
                    else:
                        nstrength5 = "N"
                if nstrength5 == "Y":
                    nstrength5 = "Y"
                    u1 = u1 + 1
                else:
                    nstrength5 = "N"

        # print("nstrength2 = {}".format(nstrength2))
        # print("nstrength3 = {}".format(nstrength3))
        # print("u1 = {}".format(u1))
        rowsUp1 = rowsUp - 1
        # print("rowsUp1-1 = {}".format(rowsUp1))
        rowsWeightStart = rowsUp1
        rowsWeightEnd = nstrength + len(sort_Group) * 3 + rowsWeightStart + 4
        rowSurfaceStart = rowsWeightEnd
        rowSurfaceEnd = nstrength + len(sort_Group) * 3 + rowSurfaceStart + 4

        if nstrength2 == 0:
            rowsUp = rowSurfaceEnd + 2
            # print("rowsUp = {}".format(rowsUp)) 
        else:
            rowSurfaceStart2 = rowSurfaceEnd
            rowSurfaceEnd2 = nstrength2 + (len(sort_Group) - u1) * 3 + rowSurfaceStart2 + (len(sort_3HRName)) * 2 + 2
            # rowSurfaceEnd2 = nstrength2+(len(sort_Group))*3 +rowSurfaceStart2+(len(sort_3HRName)*2*len(sort_Group))+2
            rowsUp = rowSurfaceEnd2 + 2
            # print("rowsUp2 = {}".format(rowsUp)) 
        rowsStart = rowsWeightStart + 6

        if statusColumn == 1 and statusBeam == 1 and statusFloor == 1 and statusCoreWall == 1 and statusSteelColumn == 1:
            writeDicBold({2: rowsUpFooting})
            rowsUp1 = rowsUpFooting
            rowsWeightStart = rowsUp1 + 2
            rowsWeightEnd = nstrength + len(sort_Group) * 3 + rowsWeightStart + 4 + 2
            rowSurfaceStart = rowsWeightEnd
            rowSurfaceEnd = nstrength + len(sort_Group) * 3 + rowSurfaceStart + 4
            if nstrength2 == 0:
                rowsUp = rowSurfaceEnd + 2
                # print("rowsUp-3 = {}".format(rowsUp)) 
            else:
                rowSurfaceStart2 = rowSurfaceEnd
                # rowSurfaceEnd2 = nstrength2+(len(sort_Group))*3 +rowSurfaceStart2+(len(sort_3HRName))*2+2
                rowSurfaceEnd2 = nstrength2 + (len(sort_Group) - u1) * 3 + rowSurfaceStart2 + (
                    len(sort_3HRName)) * 2 + 2
                # rowSurfaceEnd2 = nstrength2+(len(sort_Group))*3 +rowSurfaceStart2+(len(sort_3HRName)*2*len(sort_Group))+2
                rowsUp = rowSurfaceEnd2 + 2
                # print("rowsUp-4 = {}".format(rowsUp)) 

            writeDicBold({202: rowsWeightStart})
            rowsWeightStart = rowsWeightStart + 2
            rowsStart = rowsWeightStart + 6

        elif statusSteelColumn == 1:
            # rowsUp1 = rowsUpSteelColumn-1
            # print("rowsUp1-2 = {}".format(rowsUp1))
            rowsWeightStart = rowsUp1
            # print("rowsWeightStart = {}".format(rowsWeightStart))
            # print("nstrength = {}".format(nstrength))
            # print("len(sort_Group)*3 = {}".format(len(sort_Group)*3))

            rowsWeightEnd = nstrength + len(sort_Group) * 3 + rowsWeightStart + 4 + 2

            rowSurfaceStart = rowsWeightEnd
            # print("rowSurfaceStart = {}".format(rowSurfaceStart))
            rowSurfaceEnd = nstrength + len(sort_Group) * 3 + rowSurfaceStart + 4
            if nstrength2 == 0:
                rowsUp = rowSurfaceEnd + 2
                # print("rowsUp-5 = {}".format(rowsUp)) 
            else:
                rowSurfaceStart2 = rowSurfaceEnd
                rowSurfaceEnd2 = nstrength2 + (len(sort_Group) - u1) * 3 + rowSurfaceStart2 + (
                    len(sort_3HRName)) * 2 + 2
                rowsUp = rowSurfaceEnd2 + 2
                # print("rowsUp6 = {}".format(rowsUp)) 
            writeDicBold({202: rowsWeightStart})
            rowsWeightStart = rowsWeightStart + 2
            rowsStart = rowsWeightStart + 6

        rowRight(rowsUp1, rowsUp)
        writeDicBold({2022: rowsWeightStart, 20221: rowsWeightStart + 2})
        rowsTT = rowsWeightStart + 4
        change = 0.0
        t1 = 0.0
        total_wei = 0
        GrandTotal_wei = 0

        for g in sort_Group:
            g1 = "TO " + g
            ws1.write(rowsTT, 4, g1, option1)
            rowsTT = rowsTT + 2
            for i in sort_NameBeam:
                for j in sort_Level:
                    t = [v for v in check1 if v[2] == i and v[4] == j and v[10] == g]
                    total_wei = round(sum(float(c[7]) for c in t), 2)
                    # total_area = round(sum(float(c[7]) for c in t),2)
                    if len(t) != 0:
                        t1 = t1 + 0.001
                        change = 20221 + t1
                        try:
                            k1 = i + ";" + t[0][3] + ";" + t[0][4] + " - " + "(" + str(len(t)) + " Nos" + ")"
                        except:
                            i = "None data"
                            l1 = "None data"
                            m = "None data"
                            k1 = i + ";" + l1 + ";" + m
                        ws1.write(rowsTT, 1, change, option13)
                        ws1.write(rowsTT, 4, k1, option6)
                        ws1.write(rowsTT, 7, 'kg.', option8)
                        ws1.write(rowsTT, 10, total_wei, option9)
                        ws1.write_formula(rowsTT, 16, '=K{0} * N{1}'.format(rowsTT + 1, rowsTT + 1), option9)
                        ws1.write_formula(rowsTT, 22, '=K{0} * T{1}'.format(rowsTT + 1, rowsTT + 1), option9)
                        ws1.write_formula(rowsTT, 25, '= Q{0} + W{1}'.format(rowsTT + 1, rowsTT + 1), option9)
                        ws1.write_formula(rowsTT, 13, "", option15)
                        ws1.write_formula(rowsTT, 19, "", option15)
                        rowsTT = rowsTT + 1
                        GrandTotal_wei = GrandTotal_wei + total_wei
            rowsTT = rowsTT + 1

        writeDicBold({20222: rowSurfaceStart, 202221: rowSurfaceStart + 2})
        rowsTT = rowSurfaceStart + 4
        change = 0.0
        t1 = 0.0
        total_area = 0.0
        GrandTotal_area = 0.0

        for g in sort_Group:
            g1 = "TO " + g
            ws1.write(rowsTT, 4, g1, option1)
            rowsTT = rowsTT + 2
            for i in sort_NameBeam:
                for j in sort_Level:
                    t = [v for v in check1 if v[2] == i and v[4] == j and v[10] == g]
                    # total_area = sum(float(c[8]) for c in t)
                    total_area = round(sum(float(c[8]) for c in t), 2)
                    if len(t) != 0:
                        t1 = t1 + 0.001
                        change = 202221 + t1
                        try:
                            k1 = i + ";" + t[0][3] + ";" + t[0][4] + " - " + "(" + str(len(t)) + " Nos" + ")"
                        except:
                            i = "None data"
                            l1 = "None data"
                            m = "None data"
                            k1 = i + ";" + l1 + ";" + m
                        ws1.write(rowsTT, 1, change, option13)
                        ws1.write(rowsTT, 4, k1, option6)
                        ws1.write(rowsTT, 7, 'm2', option8)
                        ws1.write(rowsTT, 10, total_area, option9)
                        ws1.write_formula(rowsTT, 16, '=K{0} * N{1}'.format(rowsTT + 1, rowsTT + 1), option9)
                        ws1.write_formula(rowsTT, 22, '=K{0} * T{1}'.format(rowsTT + 1, rowsTT + 1), option9)
                        ws1.write_formula(rowsTT, 25, '= Q{0} + W{1}'.format(rowsTT + 1, rowsTT + 1), option9)
                        ws1.write_formula(rowsTT, 13, "", option15)
                        ws1.write_formula(rowsTT, 19, "", option15)
                        rowsTT = rowsTT + 1

                        GrandTotal_area = GrandTotal_area + total_area
            rowsTT = rowsTT + 1

        if nstrength2 != 0:
            writeDicBold({202222: rowSurfaceStart2})
            rowsTT = rowSurfaceStart2 + 2
            change = 0.0
            t1 = 0.0
            total_area = 0.0
            GrandTotal_area = 0.0

            for h in sort_3HRName:
                ws1.write(rowsTT, 4, h, option1)
                rowsTT = rowsTT + 2
                for g in sort_Group:
                    g1 = "TO " + g
                    a1 = 1
                    n4 = 0
                    # for h in sort_3HRName:
                    for i in sort_NameBeam:
                        for j in sort_Level:
                            t = [v for v in check1 if
                                 v[2] == i and v[4] == j and v[9] == 1 and v[10] == g and v[11] == h]
                            if len(t) != 0 and a1 == 1:
                                ws1.write(rowsTT, 4, g1, option1)
                                rowsTT = rowsTT + 2
                                n4 = n4 + 1

                            # total_area = sum(float(c[8]) for c in t)
                            total_area = round(sum(float(c[8]) for c in t), 2)

                            if len(t) != 0:
                                t1 = t1 + 0.001
                                change = 202222 + t1
                                try:
                                    k1 = i + ";" + t[0][3] + ";" + t[0][4] + " - " + "(" + str(len(t)) + " Nos" + ")"
                                except:
                                    i = "None data"
                                    l1 = "None data"
                                    m = "None data"
                                    k1 = i + ";" + l1 + ";" + m
                                ws1.write(rowsTT, 1, change, option13)
                                ws1.write(rowsTT, 4, k1, option6)
                                ws1.write(rowsTT, 7, 'm2', option8)
                                ws1.write(rowsTT, 10, total_area, option9)
                                ws1.write_formula(rowsTT, 16, '=K{0} * N{1}'.format(rowsTT + 1, rowsTT + 1), option9)
                                ws1.write_formula(rowsTT, 22, '=K{0} * T{1}'.format(rowsTT + 1, rowsTT + 1), option9)
                                ws1.write_formula(rowsTT, 25, '= Q{0} + W{1}'.format(rowsTT + 1, rowsTT + 1), option9)
                                ws1.write_formula(rowsTT, 13, "", option15)
                                ws1.write_formula(rowsTT, 19, "", option15)
                                rowsTT = rowsTT + 1

                                GrandTotal_area = GrandTotal_area + total_area
                                a1 = 0

                    if n4 == 0:
                        rowsTT = rowsTT
                    else:
                        rowsTT = rowsTT + 1
        # print("rowsUp = {}".format(rowsUp))          
        subTotal(rowsStart, rowsUp - 2, "2022")

    status = 0
    return rowsUp + 1, status


def precastFloor(rowsUp):
    beams = DB.FilteredElementCollector(doc).OfCategory(DB.BuiltInCategory.OST_StructuralFraming) \
        .WhereElementIsNotElementType() \
        .ToElements()

    out = []
    familyType = []
    for e in beams:
        p = []
        # id = e.Id
        # name = e.Name
        strType = e.StructuralMaterialType
        if strType == StructuralMaterialType.PrecastConcrete:
            id = e.Id
            name = e.Name
            level = e.get_Parameter(BuiltInParameter.INSTANCE_REFERENCE_LEVEL_PARAM)
            value = checkParameter(level)
            # strMat = e.get_Parameter(BuiltInParameter.STRUCTURAL_MATERIAL_PARAM)
            # value1 = checkParameter(strMat)
            cutlength = e.get_Parameter(BuiltInParameter.STRUCTURAL_FRAME_CUT_LENGTH).AsDouble() * 0.3048
            volume = (e.get_Parameter(BuiltInParameter.HOST_VOLUME_COMPUTED).AsDouble()) * 0.028317
            nameBeam = e.LookupParameter("Name Beam").AsString()
            size = e.LookupParameter("Size").AsString()
            location = e.LookupParameter("Location").AsString()
            strengthCon = e.LookupParameter("Strength Concrete").AsString()
            try:
                group = e.LookupParameter("Group").AsString()
            except:
                group1 = "None Defined"

            if group is None or group == "":
                group1 = "None Defined"
            else:
                group1 = group

            id2 = e.GetTypeId()
            if id2 == ElementId.InvalidElementId:
                familyType.append(None)
            else:
                familyType.append(doc.GetElement(id2))
            builtInParamType = BuiltInParameter.ALL_MODEL_TYPE_MARK
            for i in familyType:
                typeMark = i.get_Parameter(builtInParamType)
                try:
                    width = (i.LookupParameter("b").AsDouble()) * 0.3048
                    heigth = (i.LookupParameter("h").AsDouble()) * 0.3048
                except:
                    width = 0.0
                    heigth = 0.0
                finally:
                    area = width * cutlength

            p.append(id)  # Id 0
            p.append(strType)  # Structural Type 1
            p.append(nameBeam)  # Name Beam 2
            p.append(size)  # size 3
            p.append(location)  # Level 4
            p.append(cutlength)  # Length Precast 5
            p.append(volume)  # Volume 6
            p.append(area)  # Formwork 7
            p.append(group1)  # Group 8

            out.append(p)
    # print(out)

    check1 = [v for v in out if v[1] == StructuralMaterialType.PrecastConcrete]
    # print(check1)
    if len(check1) == 0:
        status = 1
        return rowsUp, status
    else:
        sort_NameBeam = (sorted(set([e[2] for e in check1])))  # Sorted Name Precast Floor
        sort_Size = (sorted(set([e[3] for e in check1])))  # Sorted Size
        sort_Level = (sorted(set([e[4] for e in check1])))  # Sorted Location
        sort_Length = (sorted(set([e[5] for e in check1])))  # Sorted Length of Precast
        sort_Group = (sorted(set([e[8] for e in check1])))  # Sorted Group
        nstrength = 0
        nstrength2 = 0
        nstrength3 = 0
        n = 0
        n2 = 0
        n3 = 0
        u1 = "N"
        for g in sort_Group:
            for i in sort_NameBeam:
                for j in sort_Size:
                    for k in sort_Level:
                        n = len([v for v in check1 if v[2] == i and v[3] == j and v[4] == k and v[8] == g])
                        # print(n)
                        if n > 0:
                            nstrength = nstrength + 1

        rowsUp1 = rowsUp - 1
        rowsPrecastStart = rowsUp1
        rowsPrecastEnd = nstrength + len(sort_Group) * 3 + rowsPrecastStart + 6
        rowsUp = rowsPrecastEnd
        rowsStart = rowsPrecastStart + 8
        if statusColumn == 1 and statusBeam == 1 and statusFloor == 1 and statusCoreWall == 1 and statusSteelColumn == 1 and statusSteelBeam == 1:
            writeDicBold({2: rowsUpFooting})
            rowsUp1 = rowsUpFooting
            rowsPrecastStart = rowsUp1 + 2
            rowsPrecastEnd = nstrength + len(sort_Group) * 3 + rowsPrecastStart + 6
            rowsUp = rowsPrecastEnd
            rowsStart = rowsPrecastStart + 8

        rowRight(rowsUp1, rowsUp)
        writeDicBold({203: rowsPrecastStart, 2031: rowsPrecastStart + 2, 20311: rowsPrecastStart + 4})
        rowsTT = rowsPrecastStart + 6
        change = 0.0
        t1 = 0.0
        total_area = 0.0
        GrandTotal_area = 0.0

        for g in sort_Group:
            g1 = "Group of " + g
            ws1.write(rowsTT, 4, g1, option1)
            rowsTT = rowsTT + 2
            for i in sort_NameBeam:
                for j in sort_Size:
                    for k in sort_Level:
                        t = [v for v in check1 if v[2] == i and v[3] == j and v[4] == k and v[8] == g]
                        # total_area = sum(float(c[7]) for c in t)
                        total_area = round(sum(float(c[7]) for c in t), 2)

                        if len(t) != 0:
                            t1 = t1 + 0.001
                            change = 20311 + t1
                            try:
                                k1 = i + ";" + t[0][3] + ";" + t[0][4] + " - " + "(" + str(len(t)) + " Pieces" + ")"
                                # k1 = i + ";" + t[0][3] + ";" + t[0][4]
                            except:
                                i = "None data"
                                l1 = "None data"
                                m = "None data"
                                k1 = i + ";" + l1 + ";" + m

                            ws1.write(rowsTT, 1, change, option13)
                            ws1.write(rowsTT, 4, k1, option6)
                            ws1.write(rowsTT, 7, 'm2', option8)
                            ws1.write(rowsTT, 10, total_area, option9)
                            ws1.write_formula(rowsTT, 16, '=K{0} * N{1}'.format(rowsTT + 1, rowsTT + 1), option9)
                            ws1.write_formula(rowsTT, 22, '=K{0} * T{1}'.format(rowsTT + 1, rowsTT + 1), option9)
                            ws1.write_formula(rowsTT, 25, '= Q{0} + W{1}'.format(rowsTT + 1, rowsTT + 1), option9)
                            ws1.write_formula(rowsTT, 13, "", option15)
                            ws1.write_formula(rowsTT, 19, "", option15)
                            rowsTT = rowsTT + 1

                            GrandTotal_area = GrandTotal_area + total_area

            rowsTT = rowsTT + 1

    floors = DB.FilteredElementCollector(doc).OfCategory(DB.BuiltInCategory.OST_Floors) \
        .WhereElementIsNotElementType() \
        .ToElements()

    out1 = []
    familyType = []
    for e in floors:
        p1 = []

        strMat = e.get_Parameter(BuiltInParameter.FLOOR_PARAM_IS_STRUCTURAL).AsInteger()
        try:
            coreThick = (e.get_Parameter(BuiltInParameter.STRUCTURAL_FLOOR_CORE_THICKNESS).AsDouble()) * 0.3048
        except:
            coreThick = 0
        try:
            isDropPanel = e.LookupParameter("Is Drop Panel").AsInteger()
        except:
            isDropPanel = 0
        try:
            isToppingSlab = e.LookupParameter("Is Topping Slab").AsInteger()
        except:
            isToppingSlab = 0

        perimeter = e.get_Parameter(BuiltInParameter.HOST_PERIMETER_COMPUTED).AsDouble() * 0.3047999
        if isDropPanel == 0:
            area = (e.get_Parameter(BuiltInParameter.HOST_AREA_COMPUTED).AsDouble()) * 0.092903
        else:
            area = formworkDropPanel(coreThick, perimeter) + (
                e.get_Parameter(BuiltInParameter.HOST_AREA_COMPUTED).AsDouble()) * 0.092903

        if strMat == 1 and isDropPanel == 0 and isToppingSlab == 1:
            id = e.Id
            name = e.Name
            level = e.get_Parameter(BuiltInParameter.SCHEDULE_LEVEL_PARAM)
            value = checkParameter(level)
            volume = e.get_Parameter(BuiltInParameter.HOST_VOLUME_COMPUTED).AsDouble() * 0.028317
            nameFloor = e.LookupParameter("Name Floor").AsString()
            size = e.LookupParameter("Size").AsString()
            location = e.LookupParameter("Location").AsString()
            strengthCon = e.LookupParameter("Strength Concrete").AsString()
            reinforcementTopping = e.LookupParameter("ReinforcementTopping").AsString()

            try:
                group = e.LookupParameter("Group").AsString()
            except:
                group1 = "None Defined"

            if group is None or group == "":
                group1 = "None Defined"
            else:
                group1 = group

            id2 = e.GetTypeId()
            if id2 == ElementId.InvalidElementId:
                familyType.append(None)
            else:
                familyType.append(doc.GetElement(id2))
            builtInParamType = BuiltInParameter.ALL_MODEL_TYPE_MARK
            for i in familyType:
                typeMark = i.get_Parameter(builtInParamType)

            p1.append(id)  # Id 0
            p1.append(value)  # Level 1
            p1.append(name)  # Type Name 2
            p1.append(strMat)  # Material 3
            p1.append(typeMark.AsString())  # Type Mark 4
            p1.append(coreThick)  # 5
            p1.append(area)  # Formwork 6
            p1.append(volume)  # Volume 7
            p1.append(nameFloor)  # NameFloor 8
            p1.append(size)  # Size 9
            p1.append(location)  # Location 10
            p1.append(strengthCon)  # StrengthCon 11
            p1.append(perimeter)  # perimeter 12
            p1.append(isDropPanel)  # Is Drop Panel 13
            p1.append(isToppingSlab)  # Is Topping Slab 14
            p1.append(group1)  # Group 15
            p1.append(reinforcementTopping)  # Wire Mesh Steel of Topping 16

            out1.append(p1)
    # print("out1 = {}".format(out1))
    check1 = [v for v in out1 if v[3] == 1 and v[13] == 0 and v[14] == 1]
    # print ("check1 = {}".format(check1))
    # print ("check1 = {}".format(len(check1)))
    sort_NameFloor = (sorted(set([e[8] for e in check1 if e[3] == 1])))  # Sorted Namefloor
    sort_size = (sorted(set([e[9] for e in check1 if e[3] == 1])))  # Sorted size
    sort_Location = (sorted(set([e[10] for e in check1 if e[3] == 1])))  # Sorted Location
    sort_Strength = (sorted(set([e[11] for e in check1 if e[3] == 1])))  # Sorted Strength Concrete
    sort_Group = (sorted(set([e[15] for e in check1])))  # Sorted Group
    sort_Reinforcement = (sorted(set([e[16] for e in check1])))  # Sorted Wire Mesh Steel of Topping

    nstrength = 0
    nstrength2 = 0
    n = 0
    g2 = 0
    g3 = 0

    for g in sort_Group:
        g2 = g2 + 1
        for i in sort_Strength:
            for j in sort_NameFloor:
                for k in sort_size:
                    for l in sort_Location:
                        n = len(
                            [v for v in check1 if v[11] == i and v[8] == j and v[9] == k and v[10] == l and v[15] == g])
                        # print("n = {}".format(n))
                        if n > 0:
                            nstrength = nstrength + 1

    writeDicBold({20312: rowsUp1})  # Start STRUCTURAL CONCRETE FLOOR
    # rowsTT = rowsConFloorStart
    t2 = 0
    change = 0.0
    t1 = 0.0
    # g2 = "Start"
    # g2 = 0
    # g3 = 0
    rowsTT = rowsUp1 + 2
    # print("rowsTT-1 = {}".format(rowsTT))
    for g in sort_Group:
        g2 = "Start"

        for i in sort_Strength:
            i2 = "Start"
            total_vol = 0
            GrandTotal_vol = 0
            total_area = 0.0
            GrandTotal_area = 0.0

            for j in sort_NameFloor:
                for k in sort_size:
                    for l in sort_Location:
                        # g3 = g
                        t = [v for v in check1 if v[11] == i and v[8] == j and v[9] == k and v[10] == l and v[15] == g]
                        if len(t) != 0 and i2 != i:
                            if g2 != "Start":
                                rowsTT = rowsTT + 1
                            r1 = "Reinfored concrete strength {} as specified :".format(i)
                            # print("r1-1 = {}".format(r1))
                            # print("rowsTT-3 = {}".format(rowsTT))
                            ws1.write(rowsTT, 4, r1, option1)
                            # print("rowsTT-4 = {}".format(rowsTT))
                            rowsTT = rowsTT + 2
                            # print ("g2-3 = {}".format(g2))
                            # print ("g3-2 = {}".format(g3))

                            # print("rowsTT-5 = {}".format(rowsTT))
                            g2 = g
                            i2 = i
                            # print("g2-3 = {}".format(g2))
                            # print("i-3 = {}".format(i))
                            # print("i2-3 = {}".format(i2)) 
                            # print("rowsTT-3 = {}".format(rowsTT))


                        if len(t) != 0:
                            total_vol = round(sum(float(c[7]) for c in t), 2)
                            GrandTotal_vol = GrandTotal_vol + total_vol
                            total_area = round(sum(float(c[6]) for c in t), 2)
                            GrandTotal_area = GrandTotal_vol + total_area
                            t1 = t1 + 0.001
                            change = 20312 + t1

                            try:
                                k1 = j + ";" + t[0][9] + ";" + t[0][10] + " - " + "(Volume = " + str(
                                    total_vol) + " m3" + ")"
                            except:
                                j = "None data"
                                l1 = "None data"
                                m = "None data"
                                k1 = j + "-" + l1 + ";" + m

                            # print("k1 = {}".format(k1))
                            ws1.write(rowsTT, 1, change, option13)
                            ws1.write(rowsTT, 4, k1, option6)
                            ws1.write(rowsTT, 7, 'm2', option8)
                            ws1.write(rowsTT, 10, total_area, option9)
                            ws1.write_formula(rowsTT, 16, '=K{0} * N{1}'.format(rowsTT + 1, rowsTT + 1), option9)
                            ws1.write_formula(rowsTT, 22, '=K{0} * T{1}'.format(rowsTT + 1, rowsTT + 1), option9)
                            ws1.write_formula(rowsTT, 25, '= Q{0} + W{1}'.format(rowsTT + 1, rowsTT + 1), option9)
                            ws1.write_formula(rowsTT, 13, "", option15)
                            ws1.write_formula(rowsTT, 19, "", option15)
                            rowsTT = rowsTT + 1
                            t2 = t2 + 1
            t2 = t2 + 3
        rowsTT = rowsTT + 1
        # print("t2-5 = {}".format(t2))
        # print("rowsTT-6 = {}".format(rowsTT))

    rowsUp = rowsTT + 2
    rowRight(rowsUp1, rowsUp)

    rowsUp1 = rowsUp - 2
    # print("rowsUp1  = {}".format(rowsUp1 ))
    writeDicBold({20313: rowsUp1})  # Start STRUCTURAL CONCRETE FLOOR
    # rowsTT = rowsConFloorStart
    t2 = 0
    change = 0.0
    t1 = 0.0
    total_area = 0.0
    GrandTotal_area = 0.0

    rowsTT = rowsUp1 + 2
    # print("rowsTT-1 = {}".format(rowsTT))
    for g in sort_Group:
        # g2 = "Start"
        g1 = "Group of " + g
        ws1.write(rowsTT, 4, g1, option1)
        # print("g1 = {}".format(g1))
        rowsTT = rowsTT + 2
        for i in sort_NameFloor:
            for j in sort_size:
                for k in sort_Location:
                    for l in sort_Reinforcement:
                        # g3 = g
                        t = [v for v in check1 if v[8] == i and v[9] == j and v[10] == k and v[16] == l and v[15] == g]

                        if len(t) != 0:
                            total_area = round(sum(float(c[6]) for c in t), 2)
                            GrandTotal_area = GrandTotal_vol + total_area
                            t1 = t1 + 0.001
                            change = 20313 + t1

                            try:
                                k1 = l + " - (" + i + ";" + t[0][9] + ";" + t[0][10] + ")"
                            except:
                                j = "None data"
                                l1 = "None data"
                                m = "None data"
                                k1 = j + "-" + l1 + ";" + m

                            ws1.write(rowsTT, 1, change, option13)
                            ws1.write(rowsTT, 4, k1, option6)
                            ws1.write(rowsTT, 7, 'm2', option8)
                            ws1.write(rowsTT, 10, total_area, option9)
                            ws1.write_formula(rowsTT, 16, '=K{0} * N{1}'.format(rowsTT + 1, rowsTT + 1), option9)
                            ws1.write_formula(rowsTT, 22, '=K{0} * T{1}'.format(rowsTT + 1, rowsTT + 1), option9)
                            ws1.write_formula(rowsTT, 25, '= Q{0} + W{1}'.format(rowsTT + 1, rowsTT + 1), option9)
                            ws1.write_formula(rowsTT, 13, "", option15)
                            ws1.write_formula(rowsTT, 19, "", option15)
                            rowsTT = rowsTT + 1
                            t2 = t2 + 1

                t2 = t2 + 3

        rowsTT = rowsTT + 1

    rowsUp = rowsTT + 2
    rowRight(rowsUp1, rowsUp)
    subTotal(rowsStart, rowsUp - 2, "2031")

    status = 0
    return rowsUp + 1, status


if __name__ == '__main__':
    path = forms.pick_excel_file(save=True)
    # path = "C:\Users\GP75 Leopard 9SE\Desktop\Pycharm\Test Pycharm\Test\Result_Beam.xlsx"
    wb = xlsxwriter.Workbook(path)
    ws1 = wb.add_worksheet("STRUCTURAL")

    option = wb.add_format({
        'bold': True,
        'font': 'Arial',
        'font_size': '11',
        'align': 'center',
        'valign': 'vcenter',
        'text_wrap': True
        # 'align': 'hcenter'
    })

    option1 = wb.add_format({
        'bold': True,
        'font': 'Arial',
        'font_size': '11',
        # 'font_color': 'red'
        'underline': '1',
        'align': 'left',
        'valign': 'vcenter',
        'text_wrap': True
    })
    option2 = wb.add_format({
        'bold': True,
        'font': 'Arial',
        'font_size': '11',
        'align': 'center',
        'valign': 'vcenter',
        'left': 2,
        'right': 2,
        'text_wrap': True
    })
    option3 = wb.add_format({
        'bold': True,
        'font': 'Arial',
        'font_size': '11',
        'align': 'center',
        'valign': 'vcenter',
        'top': 2,
        'left': 2,
        'right': 2,
        'text_wrap': True
    })
    option4 = wb.add_format({
        'bold': True,
        'font': 'Arial',
        'font_size': '11',
        'align': 'left',
        'valign': 'vcenter',
        'text_wrap': True
        # 'align': 'hcenter'
    })
    option5 = wb.add_format({
        'bold': False,
        'font': 'Arial',
        'font_size': '11',
        'align': 'center',
        'valign': 'vcenter',
        'text_wrap': True
        # 'align': 'hcenter'
    })
    option6 = wb.add_format({
        'bold': False,
        'font': 'Arial',
        'font_size': '11',
        'align': 'left',
        'valign': 'vcenter',
        'text_wrap': True
        # 'align': 'hcenter'
    })
    option7 = wb.add_format({
        'bold': True,
        'font': 'Arial',
        'font_size': '11',
        'align': 'left',
        'valign': 'vcenter',
        'text_wrap': True
        # 'align': 'hcenter'
    })
    option8 = wb.add_format({
        'bold': False,
        'font': 'Arial',
        'font_size': '11',
        'align': 'center',
        'valign': 'vcenter',
        # 'left': 2,
        # 'right': 2
    })
    option9 = wb.add_format({
        'bold': False,
        'font': 'Arial',
        'font_size': '11',
        'align': 'right',
        # 'align': 'center',
        'valign': 'vcenter',
        # 'left': 2,
        # 'right': 2,
        'num_format': 4,
        'font_color': 'red'
    })
    option10 = wb.add_format({
        'bold': False,
        'font': 'Arial',
        'font_size': '11',
        'align': 'center',
        'valign': 'vcenter',
        'num_format': 15
    })
    option11 = wb.add_format({
        'bold': True,
        'font': 'Arial',
        'font_size': '11',
        'align': 'right',
        'valign': 'vcenter',
        'text_wrap': True
        # 'align': 'hcenter'
    })
    option12 = wb.add_format({
        'bold': False,
        'font': 'Arial',
        'font_size': '11',
        'align': 'center',
        'valign': 'vcenter',
        'left': 2,
        'right': 2,
        'font_script': 1
    })
    option13 = wb.add_format({
        'bold': False,
        'font': 'Arial',
        'font_size': '11',
        'align': 'center',
        'valign': 'vcenter',
        'num_format': '0.000'
    })
    option14 = wb.add_format({
        'bold': False,
        'font': 'Arial',
        'font_size': '11',
        'align': 'right',
        'valign': 'vcenter',
        # 'left': 2,
        # 'right': 2,
        'num_format': '0',
        'font_color': 'red'
    })
    option15 = wb.add_format({
        'bold': False,
        'font': 'Arial',
        'font_size': '11',
        'align': 'right',
        'valign': 'vcenter',
        'num_format': 4,
        'font_color': 'blue'
    })
    option16 = wb.add_format({
        'bold': False,
        'font': 'Arial',
        'font_size': '11',
        'align': 'right',
        'valign': 'vcenter',
        'num_format': 4,
        'font_color': 'red',
        'bg_color': '#CCC0DA'
    })
    option17 = wb.add_format({
        # 'bold': False,
        # 'font': 'Arial',
        # 'font_size': '11',
        # 'align': 'right',
        # 'valign': 'vcenter',
        # 'num_format': 4,
        # 'font_color':'red',
        'bg_color': '#CCC0DA'
    })
    option18 = wb.add_format({
        # 'bold': False,
        # 'font': 'Arial',
        # 'font_size': '11',
        'right': 2,
        # 'valign': 'vcenter',
        # 'num_format': 4,
        # 'font_color':'red',
        'bg_color': '#CCC0DA'
    })
    option19 = wb.add_format({
        'bold': True,
        'font': 'Arial',
        'font_size': '11',
        'align': 'left',
        'valign': 'vcenter',
        'text_wrap': True,
        'bg_color': '#CCC0DA'
    })
    option20 = wb.add_format({
        'bold': True,
        'font': 'Arial',
        'font_size': '13',
        'align': 'center',
        'valign': 'vcenter',
        'text_wrap': True,
        'bg_color': '#FCD5B4'
    })
    option21 = wb.add_format({
        # 'num_format': 4,
        'top': 2,
        'bg_color': '#FCD5B4'
    })
    option22 = wb.add_format({
        'top': 2,
        'right': 2,
        'bg_color': '#FCD5B4'
    })
    option23 = wb.add_format({
        'bold': False,
        'font': 'Arial',
        'font_size': '13',
        'align': 'right',
        'valign': 'vcenter',
        'num_format': 4,
        'font_color': 'red',
        'bg_color': '#FCD5B4'
    })
    option24 = wb.add_format({
        # 'bold': False,
        # 'font': 'Arial',
        # 'font_size': '11',
        # 'align': 'right',
        # 'valign': 'vcenter',
        # 'num_format': 4,
        # 'font_color':'red',
        'bg_color': '#FCD5B4'
    })
    option25 = wb.add_format({
        # 'bold': False,
        # 'font': 'Arial',
        # 'font_size': '11',
        'right': 2,
        # 'valign': 'vcenter',
        # 'num_format': 4,
        # 'font_color':'red',
        'bg_color': '#FCD5B4'
    })

    option_border = wb.add_format({'border': 2})
    option_top = wb.add_format({'top': 2})
    option_bottom = wb.add_format({'bottom': 2})
    option_left = wb.add_format({'left': 2})
    option_right = wb.add_format({'right': 2})
    option_corner_topRight = wb.add_format({'top': 2, 'right': 2})
    option_corner_topLeft = wb.add_format({'top': 2, 'left': 2})
    option_corner_bottomRight = wb.add_format({'bottom': 2, 'right': 2})
    option_corner_bottomLeft = wb.add_format({'bottom': 2, 'left': 2})
    option_left_right = wb.add_format({'left': 2, 'right': 2})
    option_top_left_right = wb.add_format({'top': 2, 'left': 2, 'right': 2})
    option_bottom_left_right = wb.add_format({'bottom': 2, 'left': 2, 'right': 2})

    heading()
    # rowHeadEnd = rowHeadEnd
    rowsUp = footing()
    rowsUpFooting = rowsUp[0]
    statusFooting = rowsUp[1]
    # print ("rowsUpFooting = {}".format(rowsUp[0]))
    # print ("stausFooting = {}".format(rowsUp[1]))

    rowsUp = column(rowsUpFooting)
    # print("rowUp = {}".format(rowsUp[0]))
    rowsUpColumn = rowsUp[0]
    statusColumn = rowsUp[1]
    # print ("rowsUpColumn = {}".format(rowsUp[0]))
    # print ("stausColumn = {}".format(rowsUp[1]))

    rowsUp = beams(rowsUpColumn)
    rowsUpBeam = rowsUp[0]
    statusBeam = rowsUp[1]
    # print ("rowsUpBeam = {}".format(rowsUp[0]))
    # print ("stausBeam = {}".format(rowsUp[1]))

    rowsUp = floors(rowsUpBeam)
    rowsUpFloor = rowsUp[0]
    statusFloor = rowsUp[1]
    # print ("rowsUpFloor = {}".format(rowsUp[0]))
    # print ("stausFloor = {}".format(rowsUp[1]))
    rowsUp = coreWalls(rowsUpFloor)
    rowsUpCoreWall = rowsUp[0]
    statusCoreWall = rowsUp[1]
    # print ("rowsUpCoreWall = {}".format(rowsUp[0]))
    # print ("stausCoreWall = {}".format(rowsUp[1]))

    rowsUp = jointCon(rowsUpCoreWall)
    rowsUpJointCon = rowsUp[0]
    statusJointCon = rowsUp[1]

    rowsUp = cobelConc(rowsUpJointCon)
    rowsUpCobelConc = rowsUp[0]
    statusCobelConc = rowsUp[1]

    rowsUp = steelColumn(rowsUpCobelConc)
    rowsUpSteelColumn = rowsUp[0]
    statusSteelColumn = rowsUp[1]
    # print ("rowsUpSteelColumn = {}".format(rowsUp[0]))
    # print ("stausSteelColumn = {}".format(rowsUp[1]))

    rowsUp = steelBeam(rowsUpSteelColumn)
    rowsUpSteelBeam = rowsUp[0]
    statusSteelBeam = rowsUp[1]
    # print ("rowsUpSteelBeam = {}".format(rowsUp[0]))
    # print ("stausSteelBeam = {}".format(rowsUp[1]))

    rowsUp = precastFloor(rowsUpSteelBeam)  # *
    rowsUpPrecastFloor = rowsUp[0]  # *
    statusPrecastFloor = rowsUp[1]  # *
    # print ("rowsUpPrecastFloor = {}".format(rowsUp[0]))
    # print ("statusPrecastFloor = {}".format(rowsUp[1]))


    if statusFooting == 1 and (
            statusColumn == 0 or statusBeam == 0 or statusFloor == 0 or statusCoreWall == 0 or statusJointCon == 0 or statusCobelConc == 0 or statusSteelColumn == 0 or statusSteelBeam == 0 or statusPrecastFloor == 0):
        rowCornerTopRight(rowsUpFooting - 1)
        sumaryLine(rowsUpPrecastFloor - 1)
    elif statusFooting == 0 and (
            statusColumn == 1 and statusBeam == 1 and statusFloor == 1 and statusCoreWall == 1 and statusJointCon == 1 and statusCobelConc == 1 and statusSteelColumn == 1 and statusSteelBeam == 1 and statusPrecastFloor == 1):
        sumaryLine(rowsUpFooting - 4)
    elif statusFooting == 0 and (
            statusColumn == 0 or statusBeam == 0 or statusFloor == 0 or statusCoreWall == 0 or statusJointCon == 0 or statusCobelConc == 0 or statusSteelColumn == 0 or statusSteelBeam == 0 or statusPrecastFloor == 0):
        sumaryLine(rowsUpFooting - 4)
        rowCornerTopRight(rowsUpFooting - 1)
        # print("rowsUpPrecastFloor - 1 = {}".format(rowsUpPrecastFloor - 1))
        sumaryLine(rowsUpPrecastFloor - 1)
    elif statusFooting == 0 and statusColumn == 1 and statusBeam == 1 and statusFloor == 1 and statusCoreWall == 1 and statusJointCon == 1 and statusCobelConc == 1 and statusSteelColumn == 1 and statusSteelBeam == 1 and statusPrecastFloor == 0:
        sumaryLine(rowsUpFooting - 4)
        sumaryLine(rowsUpPrecastFloor - 1)


    wb.close()
    # os.system('start "excel" "C:\Users\GP75 Leopard 9SE\Desktop\Pycharm\Test Pycharm\Test\Result2.xlsx"')
    os.system('start "excel" "{}"'.format(path))
