Create_excel_document.py

# Load routines for working with excel spreadsheets
import openpyxl # A Python library to read/write Excel 2010 xlsx/xlsm files - https://openpyxl.readthedocs.io/en/default/
from openpyxl import load_workbook
from openpyxl import Workbook
from openpyxl.styles import Border as PyXLBorder
from openpyxl.styles import Side as PyXLSide
from openpyxl.styles import Alignment as PyXLAlignment
from openpyxl.styles import Font as PyXLFont
from openpyxl.styles import PatternFill as PyXLPatternFill
from openpyxl.comments import Comment as PyXLComment
from openpyxl.worksheet.table import Table as PyXLTable
from openpyxl.worksheet.table import TableStyleInfo as PyXLTableStyleInfo

import itertools # Functions creating iterators for efficient looping - https://docs.python.org/2/library/itertools.html

#region GenerateExcelSpreadsheet with data
#This function add the individual row information to the "Readme" sheet
def AddRowTowsinfo(Row, Name, Type, Description, ws_info, sheetname):
    thin = PyXLSide(border_style="thin", color="000000")
    normal = PyXLSide(border_style="medium", color="000000")
    double = PyXLSide(border_style="double", color="000000")

    RowSTr = str(Row)
    if type(Type) == str:
        if Type == 'Type':
            ws_info['B' + RowSTr] = "Parameter Name"
            ws_info['B' + RowSTr].border = PyXLBorder(top=double, bottom=double, left=normal, right=normal)
            ws_info['B' + RowSTr].font = PyXLFont(b=True, color="000000")
            ws_info['C' + RowSTr] = "Sheet"
            ws_info['C' + RowSTr].border = PyXLBorder(top=double, bottom=double, left=normal, right=normal)
            ws_info['C' + RowSTr].font = PyXLFont(b=True, color="000000")
            ws_info['C' + RowSTr].alignment = PyXLAlignment(horizontal="center", vertical="center")
            ws_info['D' + RowSTr] = Type
            ws_info['D' + RowSTr].border = PyXLBorder(top=double, bottom=double, left=normal, right=normal)
            ws_info['D' + RowSTr].font = PyXLFont(b=True, color="000000")
            ws_info['D' + RowSTr].alignment = PyXLAlignment(horizontal="center", vertical="center")
            ws_info['E' + RowSTr] = Description
            ws_info['E' + RowSTr].border = PyXLBorder(top=double, bottom=double, left=normal, right=normal)
            ws_info['E' + RowSTr].font = PyXLFont(b=True, color="000000")
    else:
            ws_info['B' + RowSTr] = Name
            ws_info['B' + RowSTr].border = PyXLBorder(bottom=normal, left=normal, right=normal)
            ws_info['C' + RowSTr] = str(sheetname.title)
            ws_info['C' + RowSTr].border = PyXLBorder(bottom=normal, left=normal, right=normal)

            if Type == 1: #scalar
                ws_info['D' + RowSTr] = 'Scalar'
            elif Type > 1: #vector
                ws_info['D' + RowSTr] = 'Vector'

            ws_info['E' + RowSTr] = Description
            ws_info['D' + RowSTr].border = PyXLBorder(bottom=normal, left=normal, right=normal)
            ws_info['E' + RowSTr].border = PyXLBorder(bottom=normal, left=normal, right=normal)


# region Auxiliary subroutine definition region
#get the excel column letter for excel from a number
def Excelcolumn_string(n):
    string = ""
    while n > 0:
        n, remainder = divmod(n - 1, 26)
        string = chr(65 + remainder) + string
    return string


#Generates the individual data pages in the output spreadsheet
def GenerateDatasheet(TimeseriesData, DatasetDesignation, FontColor, workBook, worksheet, worksheetColor, ref, ws_info):

    thin = PyXLSide(border_style="thin", color=FontColor)
    normal = PyXLSide(border_style="medium", color=FontColor)
    double = PyXLSide(border_style="double", color=FontColor)

    IzipInput = []
    names = []
    Comments = []
    units = []

    worksheet.sheet_properties.tabColor = worksheetColor
    NumKeys = len(list(TimeseriesData.keys()))

    for counter in range(0, NumKeys):
        Currentkey = list(TimeseriesData.keys())[counter]
        IzipInput.append(TimeseriesData[Currentkey].Data)
        names.append(TimeseriesData[Currentkey].Description.split(':')[0])
        units.append(TimeseriesData[Currentkey].Unit)
        Comments.append(TimeseriesData[Currentkey].Description)

    data = list(itertools.zip_longest(*IzipInput))
    worksheet.append(names)
    worksheet.append(units)

    for row in data:
        worksheet.append(row)

    lendata = len(data)

    ws_info.merge_cells('B' + str(ref) + ':E' + str(ref))
    ws_info['B' + str(ref)] = DatasetDesignation
    ws_info['B' + str(ref)].alignment = PyXLAlignment(horizontal="center", vertical="center")
    ws_info['B' + str(ref)].border = PyXLBorder(top=double, bottom=double, left=normal)
    ws_info['C' + str(ref)].border = PyXLBorder(top=double, bottom=double)
    ws_info['D' + str(ref)].border = PyXLBorder(top=double, bottom=double)
    ws_info['E' + str(ref)].border = PyXLBorder(top=double, bottom=double, right=normal)
    ws_info['B' + str(ref)].font = PyXLFont(b=True, color=FontColor)
    ws_info['B' + str(ref)].fill = PyXLPatternFill("solid", fgColor=worksheetColor)


    for column in range(1, len(data[0]) + 1):
        Colname = Excelcolumn_string(column)
        Rangestring = '$' + Colname + '$3:$' + Colname + '$' + str(len(TimeseriesData[list(TimeseriesData.keys())[column-1]].Data) - 1 + 3)
        workBook.create_named_range(names[column - 1], worksheet, Rangestring)
        worksheet[Colname + '1'].comment = PyXLComment(Comments[column - 1], 'Leon (dklestjo)')
        AddRowTowsinfo(ref + column, names[column - 1], 0, Comments[column - 1].split(':\n')[1], ws_info, worksheet)

    ref = ref + len(data[0]) + 1
    Colname = Excelcolumn_string(len(row))
#
    tab = PyXLTable(displayName=DatasetDesignation, ref="A1:" + Colname + str(lendata + 1))
    for column_cells in worksheet.columns:
        length = max(len(str(cell.value)) + 5 for cell in column_cells)
        worksheet.column_dimensions[column_cells[0].column_letter].width = length

    for rows in worksheet.iter_rows(min_row=1, max_row=len(data)+2, min_col=1):
        for cell in rows:
            cell.fill = PyXLPatternFill(fgColor=worksheetColor, fill_type="solid")
            cell.font =PyXLFont(b=True, color=FontColor)
            cell.border = PyXLBorder(left=double, right=double)

    return ref


# Main function that generates the excel output for the data being generated
# Remember the workbook template for the spreadsheet is located here: \\dkafil-msc\DIC-DATA\Templates\DICTemplate.xlsx'
def GenerateExcelDocument(FileName, Testplaninfo, Timeseries, PlotColor, name, Mainfolder):

    TestplanId = Timeseries[name]['Post']['TestPlan']['TestplanId']

    wb = load_workbook(filename = r'\\dkafil-msc\DIC-DATA\06_Scripts\New---DIC-Tensile-Test---py3\Templates\DICTemplate.xlsx')

    dest_filename = FileName + '.xlsx'

    thin = PyXLSide(border_style="thin", color="000000")
    normal = PyXLSide(border_style="medium", color="000000")
    double = PyXLSide(border_style="double", color="000000")

    ws = wb.active
    ws.sheet_properties.tabColor = PlotColor.Orange[1:7]
    ws.title = "Test Plan Information"

    #create Information sheet:
    ws.merge_cells('B2:C2')
    ws['B2'] = 'Test Plan: Batch Information'
    ws['B2'].border = PyXLBorder(top=double, bottom=double, left=normal, right=normal)
    ws['C2'].border = PyXLBorder(top=double, bottom=double, left=normal, right=normal)
    ws['B2'].font = PyXLFont(b=True, color="000000")
    ws['B2'].fill = PyXLPatternFill("solid", fgColor=PlotColor.Orange[1:7])
    ws['B3'] = 'Information'
    ws['B3'].border = PyXLBorder(bottom=double, left=normal, right=thin)
    ws['B3'].font = PyXLFont(b=True, color="000000")
    ws['C3'] = 'Value'
    ws['C3'].border = PyXLBorder(bottom=double, right=normal)
    ws['C3'].font = PyXLFont(b=True, color="000000")

    ws['B4'] = 'Test ID'
    ws['C4'] = Testplaninfo.TestID
    wb.create_named_range('TestID', ws, "$C$4")
    ws['C4'].comment = PyXLComment('The Specimen unique Autogenerated test ID', 'Leon (dklestjo)')
    ws['B5'] = 'Granta ID, if applicable'
    ws['C5'] = str(Testplaninfo.GrantaID)
    wb.create_named_range('GrantaID', ws, '$C$5')
    ws['C5'].comment = PyXLComment('The Specimen Granta ID number, if applicable', 'Leon (dklestjo)')
    ws['B6'] = 'Test Engineer'
    ws['C6'] = Testplaninfo.TestEngineer
    wb.create_named_range('TestEngineer', ws, '$C$6')
    ws['C6'].comment = PyXLComment('The test engineer who performed the physical test', 'Leon (dklestjo)')
    ws['B7'] = 'Test Date and Time'
    ws['C7'] = Testplaninfo.TestDateAndTime
    wb.create_named_range('TestDateandTime', ws, '$C$7')
    ws['C7'].comment = PyXLComment('The date and time when the physical testing of the batch was started', 'Leon (dklestjo)')
    ws['B8'] = 'Specimen Fabrication Date'
    ws['C8'] = Testplaninfo.SpecimenFabricationDate
    wb.create_named_range('SpecimenFabricationDate', ws, '$C$8')
    ws['C8'].comment = PyXLComment('The fabrication date of the test specimens.', 'Leon (dklestjo)')
    ws['B9'] = 'Material Designation'
    ws['C9'] = Testplaninfo.MaterialDesignation
    wb.create_named_range('MaterialDesignation', ws, '$C$9')
    ws['C9'].comment = PyXLComment('LEGO material name (if the given material has been released for production ,otherwise \"other\")', 'Leon (dklestjo)')
    ws['B10'] = 'Nonstandard Material Designation'
    ws['C10'] = Testplaninfo.NonstandardMaterialDesignation
    wb.create_named_range('NonstandardMaterialDesignation', ws, '$C$10')
    ws['C10'].comment = PyXLComment('The non-standard internal designation of the material (for non-approved materials)', 'Leon (dklestjo)')
    ws['B11'] = 'Material Grade / Trade Name'
    ws['C11'] = Testplaninfo.MaterialGradeTradeName
    wb.create_named_range('MaterialGradeTradeName', ws, '$C$11')
    ws['C11'].comment = PyXLComment('The material supplier grade name / trade name of the material', 'Leon (dklestjo)')
    ws['B12'] = 'Manufacturing process'
    ws['C12'] = Testplaninfo.ManufacturingProcess
    wb.create_named_range('Manufacturingprocess', ws, '$C$12')
    ws['C12'].comment = PyXLComment('The manufacturing process employed for the fabrication of the given specimens', 'Leon (dklestjo)')
    ws['B13'] = 'Material Color'
    ws['C13'] = Testplaninfo.MaterialColor
    wb.create_named_range('MaterialColor', ws, '$C$13')
    ws['C13'].comment = PyXLComment('Material color designation, if applicable', 'Leon (dklestjo)')

    ws['B14'] = 'Total Number of Test Specimens'
    ws['C14'] = str(Testplaninfo.TotalNumberOfTestSpecimens)
    wb.create_named_range('TotalNumberofTestSpecimens', ws, '$C$14')
    ws['C14'].comment = PyXLComment('The total number of test specimens in the batch', 'Leon (dklestjo)')
    ws['B15'] = 'Tensile Tester Programme'
    ws['C15'] = Testplaninfo.TensileTesterProgramme
    wb.create_named_range('TensileTesterProgramme', ws, '$C$15')
    ws['C15'].comment = PyXLComment('The Name of the tensile tester programme employed for the test', 'Leon (dklestjo)')

    ws['B16'] = 'Test Type'
    ws['C16'] = Testplaninfo.TestType
    wb.create_named_range('TestType', ws, '$C$16')
    ws['C16'].comment = PyXLComment('The type of the test employed to generate the data', 'Leon (dklestjo)')

    ws['B17'] = 'Pattern Method'
    ws['B17'].border = PyXLBorder(left=normal, right=thin, bottom=double)
    wb.create_named_range('PatternMethod', ws, '$C$17')
    ws['C17'].comment = PyXLComment('Method employed for adding speckle pattern', 'Leon (dklestjo)')
    ws['C17'] = Testplaninfo.PatternMethod
    ws['C17'].border = PyXLBorder(right=normal, bottom=double)

    ws_info=wb.create_sheet(title="Readme")
    ws_info.sheet_properties.tabColor = PlotColor.white[1:7]
    ws_info.merge_cells('B2:E2')
    ws_info['B2'] = 'Parameters in spreadsheet'
    ws_info['B2'].alignment = PyXLAlignment(horizontal="center", vertical="center")
    ws_info['B2'].border = PyXLBorder(top=double, bottom=double, left=normal)
    ws_info['C2'].border = PyXLBorder(top=double, bottom=double)
    ws_info['D2'].border = PyXLBorder(top=double, bottom=double)
    ws_info['E2'].border = PyXLBorder(top=double, bottom=double, right=normal)

    ws_info['B2'].font = PyXLFont(b=True, color="000000")
    ws_info['B2'].fill = PyXLPatternFill("solid", fgColor="D3D3D3")
    ws_info.merge_cells('B3:E3')
    ws_info['B3'] = 'Test Plan Batch Parameters'
    ws_info['B3'].border = PyXLBorder(top=double, bottom=double, left=normal, right=normal)
    ws_info['C3'].border = PyXLBorder(top=double, bottom=double)
    ws_info['D3'].border = PyXLBorder(top=double, bottom=double)
    ws_info['E3'].border = PyXLBorder(top=double, bottom=double,  right=normal)
    ws_info['B3'].alignment = PyXLAlignment(horizontal="center", vertical="center")
    ws_info['B3'].font = PyXLFont(b=True, color="000000")
    ws_info['B3'].fill = PyXLPatternFill("solid", fgColor=PlotColor.Orange[1:7])
    ref=0
    AddRowTowsinfo(ref + 4, 'Parameter Name','Type','Description',ws_info, ws)
    AddRowTowsinfo(ref + 5, 'TestID', 1,'The Specimen unique Autogenerated test ID', ws_info, ws)
    AddRowTowsinfo(ref + 6, 'GrantaID', 1 ,'The Specimen Granta ID number, if applicable', ws_info, ws)
    AddRowTowsinfo(ref + 7, 'TestEngineer', 1,'The test engineer who performed the physical test', ws_info, ws)
    AddRowTowsinfo(ref + 8, 'TestDateandTime', 1,'The date and time when the physical testing of the batch was started', ws_info, ws)
    AddRowTowsinfo(ref + 9, 'SpecimenFabricationDate', 1,'The fabrication date of the test specimens', ws_info, ws)
    AddRowTowsinfo(ref + 10, 'MaterialDesignation', 1,'LEGO material name (if the given material has been released for production ,otherwise \"other\")', ws_info, ws)
    AddRowTowsinfo(ref + 11, 'NonstandardMaterialDesignation', 1,'The non-standard internal designation of the material (for non-approved materials)', ws_info, ws)
    AddRowTowsinfo(ref + 12, 'MaterialGradeTradeName', 1,'The material supplier grade name / trade name of the material', ws_info, ws)
    AddRowTowsinfo(ref + 13, 'Manufacturingprocess', 1,'The manufacturing process employed for the fabrication of the given specimens', ws_info, ws)
    AddRowTowsinfo(ref + 14, 'MaterialColor', 1,'Material color designation, if applicable', ws_info, ws)
    AddRowTowsinfo(ref + 15, 'TotalNumberofTestSpecimens', 1,'The total number of test specimens in the batch', ws_info, ws)
    AddRowTowsinfo(ref + 16, 'TensileTesterProgramme', 1,'The Name of the tensile tester programme employed for the test', ws_info, ws)
    AddRowTowsinfo(ref + 17, 'TestType', 1,'The type of the test employed to generate the data', ws_info, ws)
    AddRowTowsinfo(ref + 18, 'PatternMethod', 1,'Method employed for adding speckle pattern', ws_info, ws)
    ref = 19 + ref

    for counter in range(2, 500, 1):
        rowstring = str(counter)
        ws['B'+rowstring].alignment = PyXLAlignment(horizontal="center", vertical="center")
        ws['C'+rowstring].alignment = PyXLAlignment(horizontal="center", vertical="center")

    for counter in range(4,17,1):
        rowstring=str(counter)
        ws['B'+rowstring].border = PyXLBorder(left=normal, bottom=normal, right=thin)
        ws['C'+rowstring].border = PyXLBorder(right=normal, bottom=normal)

    ws.title = "Test Plan Information"
    ws.merge_cells('B19:C19')
    ws['B19'] = 'Test Plan: Specimen Information'
    ws['B19'].fill = PyXLPatternFill("solid", fgColor=PlotColor.Orange[1:7])
    ws['B19'].border = PyXLBorder(top=double, bottom=double, left=normal, right=normal)
    ws['C19'].border = PyXLBorder(top=double, bottom=double, left=normal, right=normal)
    ws['B19'].font = PyXLFont(b=True, color="000000")
    ws['B20'] = 'Information'
    ws['B20'].border = PyXLBorder(bottom=double, left=normal, right=thin)
    ws['B20'].font = PyXLFont(b=True, color="000000")
    ws['C20'] = 'Value'
    ws['C20'].border = PyXLBorder(bottom=double, right=normal)
    ws['C20'].font = PyXLFont(b=True, color="000000")

    ws_info.merge_cells('B' + str(ref)+':E' +str(ref))
    ws_info['B'+str(ref)] = 'Test Plan: Specimen Information Parameters'
    ws_info['B'+str(ref)].alignment = PyXLAlignment(horizontal="center", vertical="center")
    ws_info['B'+str(ref)].border = PyXLBorder(top=double, bottom=double, left=normal)
    ws_info['C' + str(ref)].border = PyXLBorder(top=double, bottom=double)
    ws_info['D' + str(ref)].border = PyXLBorder(top=double, bottom=double)
    ws_info['E' + str(ref)].border = PyXLBorder(top=double, bottom=double, right=normal)
    ws_info['B'+str(ref)].font = PyXLFont(b=True, color="000000")
    ws_info['B'+str(ref)].fill = PyXLPatternFill("solid", fgColor=PlotColor.Orange[1:7])

    for counter in range(0, len(Testplaninfo.Specimendata)):
        if Timeseries[list(Timeseries.keys())[0]]['Post']['TestPlan']['TestplanId'] == Testplaninfo.Specimendata[counter]['Number']:
            ws['B21'] = 'Specimen number'
            ws['C21'] = Testplaninfo.Specimendata[counter]['Number']
            ws['C21'].comment = PyXLComment('Number of the current specimen', 'Leon (dklestjo)')
            wb.create_named_range('Specimennumber', ws, '$C$21')
            AddRowTowsinfo(ref + 1, 'Specimennumber',1, 'Number of the current specimen', ws_info, ws)

            ws['B22'] = 'Specimen Type'
            ws['C22'] = Testplaninfo.Specimendata[counter]['Specimen Type']
            wb.create_named_range('SpecimenType', ws, '$C$22')
            ws['C22'].comment = PyXLComment('The type of the test specimen.', 'Leon (dklestjo)')
            AddRowTowsinfo(ref +2 , 'SpecimenType',1, 'The type of the test specimen', ws_info, ws)

            ws['B23'] = 'Approved material or Material under development'
            ws['C23'] = Testplaninfo.Specimendata[counter]['AppOrDev.']
            wb.create_named_range('ApprovedmaterialorMaterialunderdevelopment', ws, '$C$23')
            ws['C23'].comment = PyXLComment('Is this an approved Lego Material, or material development related', 'Leon (dklestjo)')
            AddRowTowsinfo(ref + 3, 'ApprovedmaterialorMaterialunderdevelopment',1, 'Is this an approved Lego Material, or material development related', ws_info, ws)

            ws['B24'] = 'Test Speed mm/min'
            ws['C24'] = Testplaninfo.Specimendata[counter]['TestSpeed']
            wb.create_named_range('TestSpeedmmmin', ws, '$C$24')
            ws['C24'].comment = PyXLComment('The reference test speed used for the given test', 'Leon (dklestjo)')
            AddRowTowsinfo(ref + 4, 'TestSpeedmmmin',1, 'The reference test speed used for the given test', ws_info, ws)

            ws['B25'] = 'Specimen Orientation'
            ws['C25'] = Testplaninfo.Specimendata[counter]['Orientation']
            wb.create_named_range('SpecimenOrientation', ws, '$C$25')
            ws['C25'].comment = PyXLComment('Designation of the orientation of the given specimen, if applicable', 'Leon (dklestjo)')
            AddRowTowsinfo(ref + 5 , 'SpecimenOrientation',1, 'Designation of the orientation of the given specimen, if applicable', ws_info, ws)

            ws['B26'] = 'Special Specimen Comments'
            ws['C26'] = Testplaninfo.Specimendata[counter]['SpecSpeComments']
            wb.create_named_range('SpecialSpecimenComments', ws, '$C$26')
            ws['C26'].comment = PyXLComment('Any special comments related to the given specimen are outlined here', 'Leon (dklestjo)')
            AddRowTowsinfo(ref + 6, 'SpecialSpecimenComments',1, 'Any special comments related to the given specimen are outlined here', ws_info, ws)

            ws['B27'] = 'Experimental Observations'
            ws['C27'] = Testplaninfo.Specimendata[counter]['Experimental Observations']
            wb.create_named_range('ExperimentalObservations', ws, '$C$27')
            ws['C27'].comment = PyXLComment('Any special Experimental observations for the specimen are inputted here', 'Leon (dklestjo)')
            AddRowTowsinfo(ref + 7 , 'ExperimentalObservations',1, 'Any special Experimental observations for the specimen are inputted here', ws_info, ws)

    ref = ref + 8

    for counter in range(21,27,1):
        rowstring=str(counter)
        ws['B'+rowstring].border = PyXLBorder(left=normal, bottom=normal, right=thin)
        ws['C'+rowstring].border = PyXLBorder(right=normal, bottom=normal)

    ws['B27'].border = PyXLBorder(left=normal, bottom=double, right=thin)
    ws['C27'].border = PyXLBorder(right=normal, bottom=double)
    ws['B29'] = 'Specimen Granta Record ID'

    ws.merge_cells('B29:C29')
    ws['B29'].fill = PyXLPatternFill("solid", fgColor=PlotColor.Orange[1:7])
    ws['B29'].border = PyXLBorder(top=double, bottom=double, left=normal, right=normal)
    ws['C29'].border = PyXLBorder(top=double, bottom=double, left=normal, right=normal)
    ws['B29'].font = PyXLFont(b=True, color="000000")

    ws_info.merge_cells('B' + str(ref)+':E' +str(ref))
    ws_info['B'+str(ref)] = 'Specimen Granta Record ID'
    ws_info['B'+str(ref)].alignment = PyXLAlignment(horizontal="center", vertical="center")
    ws_info['B'+str(ref)].border = PyXLBorder(top=double, bottom=double, left=normal)
    ws_info['C' + str(ref)].border = PyXLBorder(top=double, bottom=double)
    ws_info['D' + str(ref)].border = PyXLBorder(top=double, bottom=double)
    ws_info['E' + str(ref)].border = PyXLBorder(top=double, bottom=double, right=normal)
    ws_info['B'+str(ref)].font = PyXLFont(b=True, color="000000")
    ws_info['B'+str(ref)].fill = PyXLPatternFill("solid", fgColor=PlotColor.Orange[1:7])

    ws.merge_cells('B30:C30')
    ws['B30'] = Testplaninfo.TestID+'_Specimen_'+str(ws['C21'].value)
    wb.create_named_range('SpecimenGrantaRecordID', ws, '$B$30')
    AddRowTowsinfo(ref + 1, 'SpecimenGrantaRecordID', 1,'The unique Granta Record ID for the specimen', ws_info, ws)
    ref = ref + 2
    ws['B30'].border = PyXLBorder(top=double, bottom=double, left=normal, right=normal)
    ws['C30'].border = PyXLBorder(top=double, bottom=double, left=normal, right=normal)

    ws.merge_cells('E2:F2')
    ws['E2'] = 'Comments section'
    ws['E2'].border = PyXLBorder(left=normal, bottom=double, top=double)
    ws['F2'].border = PyXLBorder(right=normal, bottom=double, top=double)
    ws['E2'].alignment = PyXLAlignment(horizontal="center", vertical="center")
    ws['E2'].font = PyXLFont(b=True, color="000000")
    ws['E2'].fill = PyXLPatternFill("solid", fgColor=PlotColor.Orange[1:7])

    ws_info.merge_cells('B' + str(ref)+':E' +str(ref))
    ws_info['B'+str(ref)] = 'Comments section'
    ws_info['B'+str(ref)].alignment = PyXLAlignment(horizontal="center", vertical="center")
    ws_info['B'+str(ref)].border = PyXLBorder(top=double, bottom=double, left=normal)
    ws_info['C' + str(ref)].border = PyXLBorder(top=double, bottom=double)
    ws_info['D' + str(ref)].border = PyXLBorder(top=double, bottom=double)
    ws_info['E' + str(ref)].border = PyXLBorder(top=double, bottom=double, right=normal)
    ws_info['B'+str(ref)].font = PyXLFont(b=True, color="000000")
    ws_info['B'+str(ref)].fill = PyXLPatternFill("solid", fgColor=PlotColor.Orange[1:7])

    counter=3
    if len(Testplaninfo.SpecimenFabricationDateComment) > 0:
        ws['E'+str(counter)] = 'Specimen Fabrication date comment'
        ws['F'+str(counter)] = Testplaninfo.SpecimenFabricationDateComment

        counter = counter + 1
    else:
        ws['E' + str(counter)] = 'Specimen Fabrication date comment'
        ws['F' + str(counter)] = 'None'
        counter = counter + 1
    wb.create_named_range('SpecimenFabricationdatecomment', ws, '$F$' + str(counter-1))
    ws['$F$' + str(counter-1)].comment = PyXLComment('Comment relating to the specimen fabrication date, if any','Leon (dklestjo)')
    AddRowTowsinfo(ref + 1, 'SpecimenFabricationdatecomment', 1, 'Comment relating to the specimen fabrication date, if any', ws_info, ws)

    if len(Testplaninfo.MaterialDesignationComment) > 0:
        ws['E' + str(counter)] = 'Material Designation Comment'
        ws['F' + str(counter)] = Testplaninfo.MaterialDesignationComment
        counter = counter + 1
    else:
        ws['E' + str(counter)] = 'Material Designation Comment'
        ws['F' + str(counter)] = 'None'
        counter = counter + 1
    wb.create_named_range('MaterialDesignationComment', ws, '$F$' + str(counter-1))
    ws['$F$' + str(counter-1)].comment = PyXLComment('Comment relating to the material designation, if any', 'Leon (dklestjo)')
    AddRowTowsinfo(ref + 2, 'MaterialDesignationComment', 1, 'Comment relating to the material designation, if any', ws_info, ws)

    if len(Testplaninfo.NonstandardMaterialDesignationComment) > 0:
        ws['E' + str(counter)] = 'Non-standard Material Designation Comment'
        ws['F' + str(counter)] = Testplaninfo.NonstandardMaterialDesignationComment
        counter = counter + 1
    else:
        ws['E' + str(counter)] = 'Non-standard Material Designation Comment'
        ws['F' + str(counter)] = 'None'
        counter = counter + 1
    wb.create_named_range('NonstandardMaterialDesignationComment', ws, '$F$' + str(counter-1))
    ws['$F$' + str(counter-1)].comment = PyXLComment('Comment relating to the non-standard material designation, if any', 'Leon (dklestjo)')
    AddRowTowsinfo(ref + 3, 'NonstandardMaterialDesignationComment', 1, 'Comment relating to the non-standard material designation, if any', ws_info, ws)

    if len(Testplaninfo.MaterialGradeTradeNameComment) > 0:
        ws['E' + str(counter)] = 'Material Grade / Trade Name Comment'
        ws['F' + str(counter)] = Testplaninfo.MaterialGradeTradeNameComment
        counter = counter + 1
    else:
        ws['E' + str(counter)] = 'Material Grade / Trade Name Comment'
        ws['F' + str(counter)] = 'None'
        counter = counter + 1
    wb.create_named_range('MaterialGradeTradeNameComment', ws, '$F$' + str(counter-1))
    ws['$F$' + str(counter-1)].comment = PyXLComment('Comment relating to the Material Grade / Trade Name, if any', 'Leon (dklestjo)')
    AddRowTowsinfo(ref + 4, 'MaterialGradeTradeNameComment', 1, 'Comment relating to the Material Grade / Trade Name, if any', ws_info, ws)

    if len(Testplaninfo.ManufacturingProcessComment) > 0:
        ws['E' + str(counter)] = 'Manufacturing Process Comment'
        ws['F' + str(counter)] = Testplaninfo.ManufacturingProcessComment
        counter = counter + 1
    else:
        ws['E' + str(counter)] = 'Manufacturing Process Comment'
        ws['F' + str(counter)] = 'None'
        counter = counter + 1
    wb.create_named_range('ManufacturingProcessComment', ws, '$F$' + str(counter-1))
    ws['$F$' + str(counter-1)].comment = PyXLComment('Comment relating to the Manufacturing process, if any', 'Leon (dklestjo)')
    AddRowTowsinfo(ref + 5, 'ManufacturingProcessComment', 1, 'Comment relating to the Manufacturing process, if any', ws_info, ws)

    if len(Testplaninfo.MaterialColorComment) > 0:
        ws['E' + str(counter)] = 'Material Color Comment'
        ws['F' + str(counter)] = Testplaninfo.MaterialColorComment
        counter = counter + 1
    else:
        ws['E' + str(counter)] = 'Material Color Comment'
        ws['F' + str(counter)] = 'None'
        counter = counter + 1
    wb.create_named_range('MaterialColorComment', ws, '$F$' + str(counter - 1))
    ws['$F$' + str(counter - 1)].comment = PyXLComment('Comment relating to the material color, if any', 'Leon (dklestjo)')
    AddRowTowsinfo(ref + 6, 'MaterialColorComment', 1, 'Comment relating to the material color, if any', ws_info, ws)

    if len(Testplaninfo.TotalNumberOfTestSpecimensComment) > 0:
        ws['E' + str(counter)] = 'Total Number Of Test Specimens Comment'
        ws['F' + str(counter)] = Testplaninfo.TotalNumberOfTestSpecimensComment
        counter = counter + 1
    else:
        ws['E' + str(counter)] = 'Total Number Of Test Specimens Comment'
        ws['F' + str(counter)] = 'None'
        counter = counter + 1
    wb.create_named_range('TotalNumberOfTestSpecimensComment', ws, '$F$' + str(counter - 1))
    ws['$F$' + str(counter - 1)].comment = PyXLComment('Comment relating to the total number of specimens in the batch, if any', 'Leon (dklestjo)')
    AddRowTowsinfo(ref + 7, 'TotalNumberOfTestSpecimensComment', 1, 'Comment relating to the total number of specimens in the batch, if any', ws_info, ws)

    if len(Testplaninfo.TensileTesterProgrammeComment) > 0:
        ws['E' + str(counter)] = 'Tensile Tester Programme Comment'
        ws['F' + str(counter)] = Testplaninfo.TensileTesterProgrammeComment
        counter = counter + 1
    else:
        ws['E' + str(counter)] = 'Tensile Tester Programme Comment'
        ws['F' + str(counter)] = 'None'
        counter = counter + 1
    wb.create_named_range('TensileTesterProgrammeComment', ws, '$F$' + str(counter - 1))
    ws['$F$' + str(counter - 1)].comment = PyXLComment('Comment relating to the tensile tester programme', 'Leon (dklestjo)')
    AddRowTowsinfo(ref + 8, 'TensileTesterProgrammeComment', 1, 'Comment relating to the tensile tester programme', ws_info, ws)

    if len(Testplaninfo.TestTypeComment) > 0:
        ws['E' + str(counter)] = 'Test Type Comment'
        ws['F' + str(counter)] = Testplaninfo.TestTypeComment
        counter = counter + 1
    else:
        ws['E' + str(counter)] = 'Test Type Comment'
        ws['F' + str(counter)] = 'None'
        counter = counter + 1
    wb.create_named_range('TestTypeComment', ws, '$F$' + str(counter - 1))
    ws['$F$' + str(counter - 1)].comment = PyXLComment('Comment relating to the test type', 'Leon (dklestjo)')
    AddRowTowsinfo(ref + 9, 'TestTypeComment', 1, 'Comment relating to the test type', ws_info, ws)

    if len(Testplaninfo.PatternMethodComment) > 0:
        ws['E' + str(counter)] = 'Pattern Method Comment'
        ws['F' + str(counter)] = Testplaninfo.PatternMethodComment
        counter = counter + 1
    else:
        ws['E' + str(counter)] = 'Pattern Method Comment'
        ws['F' + str(counter)] = 'None'
        counter = counter + 1
    wb.create_named_range('PatternMethodComment', ws, '$F$' + str(counter - 1))
    ws['$F$' + str(counter - 1)].comment = PyXLComment('Comment relating to the Patterning Method for the specimen', 'Leon (dklestjo)')
    AddRowTowsinfo(ref + 10, 'PatternMethodComment', 1, 'Comment relating to the Patterning Method for the specimen', ws_info, ws)

    for counter in range(3,counter,1):
        rowstring=str(counter)
        ws['E'+rowstring].alignment = PyXLAlignment(horizontal="center", vertical="center")
        ws['F'+rowstring].alignment = PyXLAlignment(horizontal="center", vertical="center")
        ws['E'+rowstring].border = PyXLBorder(left=normal, bottom=normal, right=thin)
        ws['F'+rowstring].border = PyXLBorder(right=normal, bottom=normal)
    rowstring = str(counter)
    ws['E' + rowstring].border = PyXLBorder(left=normal, bottom=double, right=thin)
    ws['F' + rowstring].border = PyXLBorder(right=normal, bottom=double)

    ref = ref + 11
    ws_info.merge_cells('B' + str(ref) + ':E' + str(ref))
    ws_info['B' + str(ref)] = 'Test Plan: General comments for the experiment'
    ws_info['B' + str(ref)].alignment = PyXLAlignment(horizontal="center", vertical="center")
    ws_info['B' + str(ref)].border = PyXLBorder(top=double, bottom=double, left=normal)
    ws_info['C' + str(ref)].border = PyXLBorder(top=double, bottom=double)
    ws_info['D' + str(ref)].border = PyXLBorder(top=double, bottom=double)
    ws_info['E' + str(ref)].border = PyXLBorder(top=double, bottom=double, right=normal)
    ws_info['B' + str(ref)].font = PyXLFont(b=True, color="000000")
    ws_info['B' + str(ref)].fill = PyXLPatternFill("solid", fgColor=PlotColor.Orange[1:7])

    rowstring = str(counter+2)
    ws.merge_cells('E'+rowstring+':F'+rowstring)
    ws['E'+rowstring] = 'Test Plan: General comments for the experiment'
    ws['E' + rowstring].alignment = PyXLAlignment(horizontal="center", vertical="center")
    ws['E' + rowstring].border = PyXLBorder(top=double, bottom=double, left=normal, right=normal)
    ws['F' + rowstring].border = PyXLBorder(top=double, bottom=double, left=normal, right=normal)
    ws['E' + rowstring].font = PyXLFont(b=True, color="000000")
    ws['E' + rowstring].fill = PyXLPatternFill("solid", fgColor=PlotColor.Orange[1:7])

    rowstring = str(counter + 3)

    ws.merge_cells('E' + rowstring + ':F27')
    for counter2 in range(counter + 3, 27, 1):
        row=str(counter2)
        ws['E' + row].border = PyXLBorder(left=normal)
        ws['F' + row].border = PyXLBorder(right=normal)
        ws['E27'].border = PyXLBorder(left=normal, bottom=double)
        ws['F27'].border = PyXLBorder(right=normal, bottom=double)

    ws['E' + rowstring] = Testplaninfo.GeneralComments
    wb.create_named_range('GeneralCommentsForTheExperiment', ws, '$E$'+rowstring)
    ws['$E$'+rowstring].comment = PyXLComment('General comments added for the test series','Leon (dklestjo)')
    AddRowTowsinfo(ref + 1, 'GeneralCommentsForTheExperiment', 1, 'General comments added for the test series',ws_info, ws)
    ref = ref + 2
    ws['E' + rowstring].alignment = PyXLAlignment(horizontal="center", vertical="center")
    ws['E' + rowstring].border = PyXLBorder(top=double, bottom=double, left=normal, right=normal)
    ws['F' + rowstring].border = PyXLBorder(top=double, bottom=double, left=normal, right=normal)

    for column_cells in ws.columns:
        length = max(len(str(cell.value)) + 5 for cell in column_cells)
        ws.column_dimensions[column_cells[0].column_letter].width = length

    print("Information sheet is created.")

    ws_eng = wb.create_sheet(title="Engineering_Response")
    ref = GenerateDatasheet(TimeseriesData=Timeseries[name]['Post']['Eng'], DatasetDesignation="Engineering_Response",
                            FontColor=PlotColor.white[1:7], workBook = wb, worksheet = ws_eng,
                            worksheetColor=PlotColor.Eng[1:7], ref=ref, ws_info=ws_info)
    print("Sheet for Engineering_Response is created.")

    ws_Volkst = wb.create_sheet(title="Volume_Constant_Response")
    ref = GenerateDatasheet(TimeseriesData=Timeseries[name]['Post']['Volkst'],
                            DatasetDesignation="Volume_Constant_Response", FontColor=PlotColor.white[1:7],
                            workBook = wb, worksheet= ws_Volkst, worksheetColor=PlotColor.VolCst[1:7], ref=ref,
                            ws_info=ws_info)
    print("Sheet for Volume_Constant_Response is created.")

    ws_Tester = wb.create_sheet(title="General_And_Tensile_Tester")
    ref = GenerateDatasheet(TimeseriesData=Timeseries[name]['Post']['Gen'], DatasetDesignation="Specimen_Ten_Test",
                            FontColor=PlotColor.black[1:7], workBook=wb, worksheet=ws_Tester,
                            worksheetColor=PlotColor.Turquise[1:7], ref=ref, ws_info=ws_info)
    print("Sheet for General_And_Tensile_Tester is created.")


    for column_cells in ws_info.columns:
        length = max(len(str(cell.value)) + 5 for cell in column_cells)
        ws_info.column_dimensions[column_cells[0].column_letter].width = length

    for cell in ws['C']:
        cell.alignment = PyXLAlignment(horizontal="center", vertical="center")
    for cell in ws['D']:
        cell.alignment = PyXLAlignment(horizontal="center", vertical="center")

    print("Saving file...")

    wb.save(filename=Mainfolder + '\\PostOut\\' + FileName + "_Specimen" + TestplanId + "_Report.xlsx")