Esempio n. 1
0
class ExcelXWriter:
    def __init__(self, path):
        self.path = path
        self.book = Workbook(path)
        self.sheet = self.book.add_worksheet()
        self.column_to_fill = 0
        
    def write_col(self, column_name, column_content):
        row = 0
        self.sheet.write(row, self.column_to_fill, column_name)
        row += 1
        for x in column_content:
            self.sheet.write(row, self.column_to_fill, x)
            row += 1
        self.column_to_fill += 1
    # write_note(self, note):
    def save(self):
        self.book.close()
Esempio n. 2
0
class Json2Xls(object):
    """Json2Xls API 接口
    :param string xls_filename: 指定需要生成的的excel的文件名
    :param string json_data: 指定json数据来源,
       可以是一个返回json的url,
       也可以是一行json字符串,
       也可以是一个包含每行一个json的文本文件
    :param string method: 当数据来源为url时,请求url的方法,
       默认为get请求
    :param dict params: get请求参数,默认为 :py:class:`None`
    :param dict post_data: post请求参数,默认为 :py:class:`None`
    :param dict headers: 请求url时的HTTP头信息 (字典、json或文件)
    :param bool form_encoded: post请求时是否作为表单请求,默认为 :py:class:`False`
    :param string sheet_name: Excel的sheet名称,默认为 sheet0
    :param string title_style: Excel的表头样式,默认为 :py:class:`None`
    :param function json_dumps: 带ensure_ascii参数的json.dumps(),
                                默认参数值为 :py:class:`False`
    :param function json_loads: 带object_pairs_hook参数的json.loads(),默认为保持原始排序
    :param bool dumps: 生成excel时对表格内容执行json_dumps,默认为 :py:class:`False`
    """
    def __init__(self,
                 xls_filename,
                 json_data,
                 method='get',
                 params=None,
                 post_data=None,
                 headers=None,
                 form_encoded=False,
                 dumps=False,
                 sheet_name='sheet0',
                 title_style=None):
        self.json_dumps = partial(json.dumps, ensure_ascii=False)
        self.json_loads = partial(json.loads, object_pairs_hook=OrderedDict)

        self.sheet_name = sheet_name
        self.xls_filename = xls_filename
        self.json_data = json_data
        self.method = method
        self.params = params
        self.post_data = post_data
        self.headers = headers
        self.form_encoded = form_encoded
        self.dumps = dumps

        self.__check_file_suffix()

        # self.book = Workbook(encoding='utf-8', style_compression=2)
        # self.sheet = self.book.add_sheet(self.sheet_name)

        self.start_row = 0

        # self.title_style = xlwt.easyxf(
        #     title_style or 'font: name Arial, bold on;'
        #     'align: vert centre, horiz center;'
        #     'borders: top 1, bottom 1, left 1, right 1;'
        #     'pattern: pattern solid, fore_colour lime;')

    def __sheet_write(self, row, col, value):
        pass

    def __check_file_suffix(self):
        suffix = self.xls_filename.split('.')[-1]
        self.excel_type = suffix
        if '.' not in self.xls_filename:
            self.xls_filename += '.xls'
        elif suffix == 'xls':
            self.book = Workbook(encoding='utf-8', style_compression=2)
            self.sheet = self.book.add_sheet(self.sheet_name)

            self.start_row = 0

            # self.title_style = xlwt.easyxf(
            #     title_style or 'font: name Arial, bold on;'
            #     'align: vert centre, horiz center;'
            #     'borders: top 1, bottom 1, left 1, right 1;'
            #     'pattern: pattern solid, fore_colour lime;')
        elif suffix == 'xlsx':
            self.book = xlsxwriter.Workbook(self.xls_filename)
            self.sheet = self.book.add_worksheet(self.sheet_name)

            self.start_row = 0

            # self.title_style = xlwt.easyxf(
            #     title_style or 'font: name Arial, bold on;'
            #     'align: vert centre, horiz center;'
            #     'borders: top 1, bottom 1, left 1, right 1;'
            #     'pattern: pattern solid, fore_colour lime;')
        else:
            raise Exception('filename suffix must be .xls')

    def __get_json(self):
        data = None
        try:
            # with open(self.json_data, encoding='utf-8') as f:
            data = self.json_loads(self.json_data)
        except Exception:
            if os.path.isfile(self.json_data):
                with open(self.json_data, 'r', encoding='utf-8') as source:
                    try:
                        data = self.json_loads(source.read().replace('\n', ''))
                    except Exception:
                        source.seek(0)
                        data = [self.json_loads(line) for line in source]
            else:
                if self.headers and os.path.isfile(self.headers):
                    with open(self.headers) as headers_txt:
                        self.headers = self.json_loads(
                            headers_txt.read().decode('utf-8').replace(
                                '\n', ''))
                elif isinstance(self.headers, ("".__class__, u"".__class__)):
                    self.headers = self.json_loads(self.headers)
                try:
                    if self.method.lower() == 'get':
                        resp = requests.get(self.json_data,
                                            params=self.params,
                                            headers=self.headers)
                        data = resp.json()
                    else:
                        if isinstance(
                                self.post_data,
                            ("".__class__, u"".__class__)) and os.path.isfile(
                                self.post_data):
                            with open(self.post_data, 'r',
                                      encoding='utf-8') as source:
                                self.post_data = self.json_loads(
                                    source.read().replace('\n', ''))
                        if not self.form_encoded:
                            self.post_data = self.json_dumps(self.post_data)
                        resp = requests.post(self.json_data,
                                             data=self.post_data,
                                             headers=self.headers)
                        data = resp.json()
                except Exception as e:
                    print(e)
        return data

    def __fill_title(self, data):
        '''生成默认title'''
        data = self.flatten(data)
        for index, key in enumerate(data.keys()):
            if self.dumps:
                key = self.json_dumps(key)
            try:
                self.sheet.col(index).width = (len(key) + 1) * 256
            except Exception:
                pass
            if self.excel_type == "xls":
                self.sheet.row(self.start_row).write(index,
                                                     key.decode('utf-8'),
                                                     self.title_style)
            if self.excel_type == "xlsx":
                self.sheet.write(self.start_row, index, key.decode('utf-8'))
        self.start_row += 1

    def __fill_data(self, data):
        '''生成默认sheet'''
        data = self.flatten(data)
        for index, value in enumerate(data.values()):
            if self.dumps:
                value = self.json_dumps(value)
            self.auto_width(self.start_row, index, value)
            if self.excel_type == "xls":
                self.sheet.row(self.start_row).write(index, value)
            if self.excel_type == "xlsx":
                self.sheet.write(self.start_row, index, value)

        self.start_row += 1

    def auto_width(self, row, col, value):
        '''单元格宽度自动伸缩
        :param int row: 单元格所在行下标
        :param int col: 单元格所在列下标
        :param int value: 单元格中的内容
        '''

        try:
            self.sheet.row(row).height_mismatch = True
            # self.sheet.row(row).height = 0
            width = self.sheet.col(col).width
            new_width = min((len(value) + 1) * 256, 256 * 50)
            self.sheet.col(col).width = width \
                if width > new_width else new_width
        except Exception:
            pass

    def flatten(self, data_dict, parent_key='', sep='.'):
        '''对套嵌的dict进行flatten处理为单层dict
        :param dict data_dict: 需要处理的dict数据。
        :param str parent_key: 上层字典的key,默认为空字符串。
        :param str sep: 套嵌key flatten后的分割符, 默认为“.” 。
        '''

        out = {}

        def _flatten(x, parent_key, sep):
            if isinstance(x, collections.MutableMapping):
                for a in x:
                    _flatten(x[a], parent_key + a + sep, sep)
            elif isinstance(x, collections.MutableSequence):
                i = 0
                for a in x:
                    _flatten(a, parent_key + str(i) + sep, sep)
                    i += 1
            else:
                if not isinstance(x, ("".__class__, u"".__class__)):
                    x = str(x)
                out[parent_key[:-1].encode('utf-8')] = x

        _flatten(data_dict, parent_key, sep)
        return OrderedDict(out)

    def make(self, title_callback=None, body_callback=None):
        '''生成Excel。
        :param func title_callback: 自定义生成Execl表头的回调函数。
           默认为 :py:class:`None`,即采用默认方法生成
        :param func body_callback: 自定义生成Execl内容的回调函数。
           默认为 :py:class:`None`,即采用默认方法生成
        '''

        data = self.__get_json()
        if not isinstance(data, (dict, list)):
            raise Exception('The %s is not a valid json format' % type(data))
        if isinstance(data, dict):
            data = [data]

        if title_callback is not None:
            title_callback(self, data[0])
        else:
            self.__fill_title(data[0])

        if body_callback is not None:
            for d in data:
                body_callback(self, d)
        else:
            for d in data:
                self.__fill_data(d)

        if self.excel_type == "xls":
            self.book.save(self.xls_filename)
        if self.excel_type == "xlsx":
            self.book.close()
Esempio n. 3
0
    def part1(self, fichier1, fichier2, rapport_name, vproject, vfilter):
        self.progress_bar["maximum"] = 100

        self.bpetit_emp['bg'] = '#006738'
        self.progress_bar.grid(row=6, column=0, pady=2)
        self.progress_bar["value"] = 5
        root.update()
        # !/usr/bin/env python
        # coding: utf-8

        path = fichier1
        classeur = xlrd.open_workbook(path)
        self.progress_bar["value"] = 20
        root.update()
        nom_des_feuilles = classeur.sheet_names()
        #feuille = classeur.sheet_by_name(nom_des_feuilles[2])
        feuille = classeur.sheet_by_name("ECU Dashboard")

        #############################################################################################
        #############################################################################################
        def data_frame12(colonne1, colonne2, colonne3, colonne4, colonne5,
                         colonne6):
            data = pd.DataFrame({
                "Part": (colonne1),
                "SSR Decision Trend": (colonne2),
                "Update_date": (colonne3),
                "Issues/Concerns": (colonne4),
                "Review/SSR_Status": (colonne5),
                "Expected Action": (colonne6)
            })
            return data

        dff = pd.read_excel(path, sheet_name="ECU Dashboard", skiprows=3)

        List_part = dff['ECU ID']
        liste_SSR_Status = dff["SSR status"]
        Sentence = dff["Review Plans & Commitments / Action plan/ Remarks"]

        term = "NEWDATE"

        term1 = "Issues/Concerns:"
        term2 = "Review/SSR Status:"
        term3 = "  "
        i = 0
        for sentence in list(Sentence):
            i += 1

            sentence = str(sentence).replace('\n', 'sautligne')
            sentence = sentence.replace('Review /SSR', 'Review/SSR')
            sentence = sentence.replace('Review / SSR ', 'Review/SSR')
            sentence = sentence.replace('Review/ SSR', 'Review/SSR')
            sentence = sentence.replace('Review/SSR status',
                                        'Review/SSR Status')
            sentence = sentence.replace('Review/SSRstatus',
                                        'Review/SSR Status')
            sentence = sentence.replace('Review/SSRStatus',
                                        'Review/SSR Status')
            sentence = sentence.replace('Review/SSR Status :',
                                        'Review/SSR Status:')

            sentence = sentence.replace('Issues/ Concerns', 'Issues/Concerns')
            sentence = sentence.replace('Issues /Concerns', 'Issues/Concerns')
            sentence = sentence.replace('Issues / Concerns', 'Issues/Concerns')
            sentence = sentence.replace('Issues/Concerns :',
                                        'Issues/Concerns:')

            list2 = re.findall("\d\d-\d\d-\d{4}", sentence)
            for formatdate in list2:
                sentence = sentence.replace(formatdate + " :",
                                            formatdate + ":")
            try:
                premieredate = list2[0]
                list2 = [s for s in list2 if s != premieredate]
                for formatdate in list2:
                    sentence = sentence.split(formatdate + ":")[0]
                    sentence = sentence.split(formatdate + "sautligne")[0]
            except:
                1
            # on recupere le blocke le plus recent
            block = sentence.split("NEWDATE")[0]

            try:
                if term1 in block and term2 in block and re.search(
                        'Issues/Concerns:(.*)Review/SSR Status:',
                        block).group(1) != '' and re.search(
                            'Review/SSR Status:(.*)', block).group(1):
                    # on recupere la date (première occurence)
                    self.Liste_date.append(
                        re.findall("\d\d-\d\d-\d{4}", block)[0])
                    # on recupere les Issues/Concerns (première occurence)
                    issue = block.split('Review/SSR Status:')[0]
                    issue = re.findall('Issues/Concerns:(.*)', issue)[0]
                    issue = issue.replace('sautlignesautligne', 'sautligne')
                    # on rajoute les retours à la ligne
                    try:
                        # print(re.search('sautligne(.*)', review).group(1))
                        if issue[0:9] == 'sautligne':
                            issue = issue[9::]
                    except:
                        issue = issue

                    issue = issue.replace('sautligne', '\n')
                    self.Liste_issues.append(issue)
                    # on recupere les reviews (première occurence)

                    review = re.search('Review/SSR Status:(.*)',
                                       block).group(1)
                    self.List_date = re.findall("\d\d-\d\d-\d{4}", review)
                    for k in self.List_date:
                        review = review.split('sautligne' + k)[0]
                    review = review.replace('sautlignesautligne', 'sautligne')
                    # on rajoute les retours à la ligne
                    try:
                        # print(re.search('sautligne(.*)', review).group(1))
                        if review[0:9] == 'sautligne':
                            review = review[9::]
                    except:
                        review = review

                    review = review.replace('sautligne', '\n')
                    self.Liste_review.append(review)
                    self.Liste_comments.append(term3)

                    # liste_comments.append(" {}".format(feuille.cell_value(i,64)))
                else:
                    self.Liste_review.append(review)
                    self.Liste_comments.append(term3)
                    self.Liste_issues.append(issue)

                    self.Liste_date.append(".")
            except:
                self.Liste_date.append(".")
                self.Liste_review.append(".")
                self.Liste_comments.append(".")
                self.Liste_issues.append(".")

        print(len(List_part), ' ,', len(liste_SSR_Status), ' ,',
              len(self.Liste_date), ' ,', len(self.Liste_issues), ' ,',
              len(self.Liste_review, ), ' ,', len(self.Liste_comments))
        ee = data_frame12(List_part, liste_SSR_Status, self.Liste_date,
                          self.Liste_issues, self.Liste_review,
                          self.Liste_comments)

        import numpy as np
        ea = ee.to_numpy()
        import matplotlib.pyplot as plt
        import numpy as np
        import datetime
        # premier tableau=================================================
        data = pd.read_excel(fichier2, "°Cover Page", skiprows=9, index_col=1)
        data = data.drop(['R_KPI_MILESTONE', 'Trend'], axis=1)
        e1 = data.iloc[0:4, 0:1]

        # deuxieme tableau===============================================
        data1 = pd.read_excel(fichier2,
                              "°Cover Page",
                              skiprows=43,
                              index_col=2)
        data1 = data1.reset_index(drop=True)
        data1 = data1.drop(['Unnamed: 0', 'Unnamed: 1'], axis=1)
        e2 = data1.iloc[0:4, 0:11]
        self.progress_bar["value"] = 30
        root.update()
        time.sleep(0.5)
        # GRAPHE ==========================================================
        import matplotlib.pyplot as plt
        plt.rcParams.update({
            "pgf.texsystem":
            "pdflatex",
            "pgf.preamble": [
                r"\usepackage[utf8x]{inputenc}",
                r"\usepackage[T1]{fontenc}",
                r"\usepackage{cmbright}",
            ]
        })

        CWmax = e2['Unnamed: 3'][0].isocalendar()[1]
        x = []
        for i in range(CWmax, CWmax - 10, -1):
            x.append('CW' + str(i))

        self.progress_bar["value"] = 40
        root.update()
        y1 = e2.loc[1]
        y2 = e2.loc[2]
        y3 = e2.loc[3]
        plt.figure(figsize=(10, 5))
        plt.grid(True)
        plt.plot(x, y1, label='Coverage', lw=3)
        plt.plot(x, y2, label='Completness', lw=3)
        plt.plot(x, y3, label='Consistency', lw=3)
        self.progress_bar["value"] = 50
        time.sleep(1)
        root.update()
        plt.title('Milestone trend')
        plt.xlabel('Calendar Week')
        plt.ylabel('Kpi (%)')
        plt.legend()
        ax = plt.gca()
        ax.invert_xaxis()
        plt.savefig("fig.png")
        eb = e1.to_numpy()
        self.progress_bar["value"] = 60
        time.sleep(0.5)

        from openpyxl.utils.dataframe import dataframe_to_rows
        from openpyxl.chart import BarChart, Series, Reference

        path = vproject
        # data = pd.read_excel(path, skiprows=3, sheet_name=4)
        data = pd.read_excel(path, "Hard points", skiprows=3)
        id_list = list(data['ID'])
        veh_Project_list = list(data['Veh Project'])
        parts_list = list(data['Parts'])
        status_list = list(data['Status'])

        # Create a workbook and add a worksheet.
        workbook = Workbook()
        worksheet = workbook.active
        worksheet.title = 'Report.xlsx'

        # Add a bold format to use to highlight cells.
        header_formatfont = Font(bold=True, )
        header_formattxt = Alignment(wrap_text=True)

        ## pour le petit tableau
        worksheet['A1'].value = 'KPI'
        worksheet['B1'].value = 'Completness'
        worksheet['C1'].value = 'Consistency'
        worksheet['B2'].value = 'Target = 100%'
        worksheet['C2'].value = 'Target = 80%'

        liste = ['A1', 'A2', 'B2', 'B1', 'C1', 'C2']
        for cell in liste:
            worksheet[cell].font = header_formatfont
            worksheet[cell].alignment = header_formattxt

        # data, workbook, and worksheet are the same as in the BarChart example
        tab = Table(displayName="Table1", ref="A1:C3")

        # I list out the 4 show-xyz options here for reference
        style = TableStyleInfo(name="TableStyleMedium9",
                               showFirstColumn=False,
                               showLastColumn=False,
                               showRowStripes=True,
                               showColumnStripes=False)
        tab.tableStyleInfo = style
        worksheet.add_table(tab)
        worksheet.column_dimensions['A'].width = 20
        worksheet.column_dimensions['B'].width = 20
        worksheet.column_dimensions['C'].width = 20
        worksheet.column_dimensions['D'].width = 70
        worksheet.column_dimensions['E'].width = 70
        worksheet.column_dimensions['F'].width = 40
        worksheet.column_dimensions['G'].width = 40
        self.progress_bar["value"] = 70
        time.sleep(1)
        root.update()

        # pour le grand tableau
        worksheet['A25'].value = 'PART'
        worksheet['B25'].value = 'SSR Decision Trend'
        worksheet['C25'].value = 'Update_date'
        worksheet['D25'].value = 'Issues/Concerns'
        worksheet['E25'].value = 'Review/SSR_Status'
        worksheet['F25'].value = 'Hard points and Risks IDs'
        worksheet['G25'].value = 'Expected Action'

        liste = ['A25', 'B25', 'C25', 'D25', 'E25', 'F25', 'G25']
        for cell in liste:
            worksheet[cell].font = header_formatfont
            worksheet[cell].alignment = header_formattxt

        # Petit tableau
        roww = 3
        coll = 0
        worksheet.cell(roww, 2).value = str(eb[1])
        worksheet.cell(roww, 3).value = str(eb[2])

        filename3 = vfilter
        wb3 = load_workbook(filename3)
        ws3 = wb3.worksheets[0]
        mr = ws3.max_row
        mc = ws3.max_column

        filter = []
        for i in range(1, mr + 1):
            c = ws3.cell(row=i, column=1)
            filter.append(c.value.upper())

        # Grand Tableau
        expenses = ea
        row = 26
        col = 1
        #expenses1 = ec

        col2 = 6

        for aa, bb, cc, dd, ff, gg in (expenses):
            if str(aa).strip().upper() in filter:
                worksheet.cell(row, col).value = aa
                worksheet.cell(row, col).alignment = Alignment(wrapText=True,
                                                               vertical='top')
                worksheet.cell(row, col + 1).value = bb
                worksheet.cell(row,
                               col + 1).alignment = Alignment(wrapText=True,
                                                              vertical='top')
                worksheet.cell(row, col + 2).value = cc
                worksheet.cell(row,
                               col + 2).alignment = Alignment(vertical='top',
                                                              wrapText=True)
                worksheet.cell(row, col + 3).value = "'" + str(dd).strip()
                worksheet.cell(row,
                               col + 3).alignment = Alignment(vertical='top',
                                                              wrapText=True)
                worksheet.cell(row, col + 4).value = "'" + str(ff).strip()
                worksheet.cell(row,
                               col + 4).alignment = Alignment(vertical='top',
                                                              wrapText=True)
                worksheet.cell(row, col + 6).value = gg  #str(gg).strip()
                worksheet.cell(row,
                               col + 6).alignment = Alignment(vertical='top',
                                                              wrapText=True)

                v_hp = ""
                v_part = ""
                v_final = ""

                v_hp = ""
                v_part = ""
                v_final = ""
                for i in range(len(id_list)):
                    v1 = str(veh_Project_list[i]).strip().upper() + "_" + str(
                        parts_list[i]).strip().upper()

                    if v1 != v_part:

                        if str(aa).strip().upper() == v1.strip().upper():
                            if str(status_list[i]).strip().upper() == "OPEN":
                                worksheet.cell(
                                    row, col2).value = str(id_list[i]) + '\n'
                                worksheet.cell(row,
                                               col2).alignment = Alignment(
                                                   wrapText=True,
                                                   vertical='top')
                                v_part = v1.strip()
                                v_hp = ""
                                v_final = id_list[i]
                    else:
                        if str(aa).strip().upper() == v1.strip().upper():
                            if str(status_list[i]).strip().upper() == "OPEN":
                                v_hp += v_final + '\n' + id_list[i]
                                worksheet.cell(row, col2).value = v_hp
                                worksheet.cell(row,
                                               col2).alignment = Alignment(
                                                   wrapText=True,
                                                   vertical='top')
                                v_final = " "
                                # v_final = aaa[0] + ' , '

                row += 1
        piece_no_disponible = []
        piece_disponible = []
        self.progress_bar["value"] = 80
        time.sleep(1)
        root.update()
        for aa, bb, cc, dd, ff, gg in (expenses):
            piece_disponible.append(str(aa).upper().strip())
        for i in filter:
            if i not in piece_disponible:
                piece_no_disponible.append(i)
        # pour le message des piece non disponible
        l = ''
        for k in piece_no_disponible:
            if k != 'PART':
                l += ' , ' + str(k)

        li = 'The following parts ( ' + l + " ) are not available."

        if l != '':
            self.vremarque.set(li)

            self.lremarque['bg'] = '#FC4C4C'

        else:
            self.vremarque.set('Report created')
            self.lremarque['bg'] = '#CAE21E'

        #indice = len(expenses) + 25
        indice = len(filter) - len(piece_no_disponible) + 25
        ref = "A25:G" + str(indice)
        tab3 = Table(displayName="Table2", ref=ref)
        style = TableStyleInfo(name="TableStyleMedium9",
                               showFirstColumn=False,
                               showLastColumn=False,
                               showRowStripes=True,
                               showColumnStripes=False)
        tab3.tableStyleInfo = style
        worksheet.add_table(tab3)

        # Pour le graphe
        img = openpyxl.drawing.image.Image('fig.png')
        img.width = 750
        img.height = 370
        worksheet.add_image(img, 'A5')

        my_red = openpyxl.styles.colors.Color(rgb='00FF0000')
        my_green = openpyxl.styles.colors.Color(rgb='0000FF00')
        my_orange = openpyxl.styles.colors.Color(rgb='00FFA500')
        # Couleur colonne B
        for i in range(26, len(expenses) + 26):

            if str(worksheet.cell(i, 2).value).strip() == 'Not Passed' or str(
                    worksheet.cell(i, 2).value).strip() == 'Not passed':
                worksheet.cell(i, 2).value = 'Not passed'
                worksheet.cell(i, 2).fill = PatternFill(patternType='solid',
                                                        fgColor=my_red)
            if str(worksheet.cell(i, 2).value).strip() == 'Conditionally':
                worksheet.cell(i, 2).value = worksheet.cell(i, 2).value.strip()
                worksheet.cell(i, 2).fill = PatternFill(patternType='solid',
                                                        fgColor=my_orange)
            if str(worksheet.cell(i, 2).value).strip() == 'Passed' or str(
                    worksheet.cell(i, 2).value).strip() == 'passed':
                worksheet.cell(i, 2).value = 'Passed'
                worksheet.cell(i, 2).fill = PatternFill(patternType='solid',
                                                        fgColor=my_green)
            """v = str(worksheet.cell(i, 2).value)
            if v.upper().strip() == 'NOT PASSED':
                worksheet.cell(i, 2).value = 'Not passed'
                worksheet.cell(i, 2).fill = PatternFill(patternType='solid', fgColor=my_red)

            if str(worksheet.cell(i, 2).value).upper().strip() == 'CONDITIONALLY':
                worksheet.cell(i, 2).value = worksheet.cell(i, 2).value.strip()
                worksheet.cell(i, 2).fill = PatternFill(patternType='solid', fgColor=my_orange)

            if str(worksheet.cell(i, 2).value).upper().strip() == 'PASSED':
                worksheet.cell(i, 2).value = 'Passed'
                worksheet.cell(i, 2).fill = PatternFill(patternType='solid', fgColor=my_green)"""

        workbook.save(rapport_name)
        workbook.close()
        self.progress_bar["value"] = 95
        time.sleep(2)
        root.update()
        self.progress_bar["value"] = 100
        time.sleep(2)

        root.update()

        self.progress_bar.grid_forget()
        root.update()
Esempio n. 4
0
class MainInterface(Frame):
	"""Main interface in the heat transfer simulation window.
	Inherits from the Tkinter Frame widget.
	
	Widgets in the interface:
		
	self.root
		Class Tk. Root interface of the simulation. Contains the Menu.
		
	self.objectmenu
		Class Menu. Contains the commands to add of modify an object.
		
	self.settemp
		Classe DoubleVar. Variable of the field temperature entry.
		
	self.temp_label
		Class Label. Text indicating to write the field temperature.
	
	self.temp_entry
		Class Entry. Contains the variable representing the initial
		field temperature.
		
	self.settout
		Classe DoubleVar. Variable of the outside temperature entry.
		
	self.tout_label
		Class Label. Text indicating to write the outside temperature.
	
	self.tout_entry
		Class Entry. Contains the variable representing the outside
		temperature.
	
	self.startbutton
		Class Button. Command to start numerical simulation.
		When pressed, changes text and command to pause simulation.
		When paused, changes text and command to resume simulation.
		When simulation is ended, returns to its original function.
		
	self.endbutton
		Class Button. Command to end numerical simulation.
		Disabled until simulation is started.
		
	self.map
		Class PhotoImage. Contains the actual image for the heatmap
		when simulation is not running Used to initialize the map
		and to show objects before simulation starts.
	
	self.simulation
		Class Label. Contains the heatmap as an image.
		Is modified with the appropriate heatmap at each iteration.
		
	self.dimensions
		Label indicating the dimensions of heatmap.
	
	self.temperature
		Label indicating temperature scaling in heatmap, with the
		widgets self.red_image, seld.red, self.green_image,
		self.green, self.blue_image and self.blue.
		
	
	Other attributes:
	
	self.data
		Data instance which contains the temperature field.
		Initialized when simulation starts, deleted when it ends.
	
	self.heatmap
		Heatmap instance which contains the image where a color is
		calculated for each temperature value in the field.
		Initialized when simulation starts, deleted when it ends.
	
	self.isrunning
		Is True if simulation is running, is False otherwise.
		
	self.temp
		Initial temperature of the field. Is None until a suitable
		temperature is set.
		
	self.tout
		Outside temperature. Is None until a suitable temperature is set.
		
	self.wb
		Workbook object used to export data to an Excel file.
	
	self.ws
		Work sheet used to write data in an Excel file.
	
	self.points
		List of followed points. The temperature at each time incrementation
		will be kept in an Excel file if user used the "Export" command.
	

	Methods defined here:
	
	__init__(self, root):
		MainInterface class builder.
		
	_initmenu(self):
		Method to initialize the menu bar of the interface.
	
	_showerror(self, error):
		Used to show any error that the user may encounter.
	
	addobject(self):
		Command of the "Add Object" option in "Object" menu.
		Creates an Object object to support object creation.
		If created, object is shown on the heatmap.
		
	addpoint(self):	
		Asks user to choose a point that will be followed by
		an Excel worksheet.
		
	addwindow(self):
		Command of the "Add Window" option in "Window" menu.
		Creates a Window object to support window creation.
		
	delobject(self, name):
		Deletes an object and erases it from the heatmap.
	
	delwindow(self, name):
		Deletes a window and erases it from the heatmap.
	
	end(self):
		Command of self.endbutton. Used to end simulation.
		Heatmap will be reinitialized and cannot be recovered.
	
	export(self):
		Export the work sheet to an Excel file for further
		data manipulation.
		
	fieldtemp(self, *args):
		Callback method of the field temperature entry.
		Verifies if the entry can be treated as a number.
		
	fill(self, color):
		Fill image with a color = (r, b, g). Used at
		initialization of interface and at end of simulation.
		
	help(self):	
		Command of "Help" in the main menu. Opens a html 
		page with some guidelines on how to use this program.
		
	newpoint(self):
		Adds a new point to ths list of points for which the
		temperature values will be kept in an Excel work sheet.
		
	outsidetemp(self. *args):	
		Callback method of the outside temperature entry.
		Verifies if the entry can be treated as a number.
	
	pause(self):
		Command to pause simulation.
		
	quit(self):
		Method called when the user interface is closed.
	
	resume(self):
		Command to resume simulation.
		
	run(self):
		Iterate numerical data and update heatmap.
		
	showobject(self, object):	
		Show the object on the heatmap.
		
	showwindow(self, window):
		Show the window on the heatmap.
	
	start(self):	
		Default command of self.startbutton. Starts simulation.
		If no object or window is added, raises UniformFieldError.
		Otherwise, creates the data array and the initial heatmap.
		
	todelete_object(self):
		Allows user to choose the object he wants to delete.
		
	todelete_window(self):
		Allows user to choose the window he wants to delete.
	
	tomodify_object(self):
		Allows user to choose the object he wants to modify.
	
	tomodify_window(self):
		Allows user to choose the window he wants to modify.
		
	
	Exceptions handled:
	
	AttributeError
		When user attempts to start simulation without any value
		for the field temperature or the outside temperature.
		
	ValueError
		When the field temperature entry is either physically
		impossible or unsuitable for the simulation.
		
	UniformFieldError
		When user tries to start simulation with no object or window.
	"""
	
	def __init__(self, root):
		"""MainInterface class builder."""
		
		Frame.__init__(self, root)
		
		self.root = root
		self.root.title("Heat Transfer Simulation")
		
		self._initmenu()
		
		self.settemp = StringVar()
		self.temp_label = Label(self, text="Set Temperature in Field : ")
		self.temp_label.grid(row=0, column=1, columnspan=2, sticky=E)
		self.temp_entry = Entry(self, textvariable=self.settemp, width=10)
		self.temp_entry.grid(row=0, column=3, sticky=W)
		self.settemp.trace("w", self.fieldtemp)
		
		self.settout = StringVar()
		self.tout_label = Label(self, text="Set Temperature Outside : ")
		self.tout_label.grid(row=1, column=1, columnspan=2, sticky=E)
		self.tout_entry = Entry(self, textvariable=self.settout, width=10, state=DISABLED)
		self.tout_entry.grid(row=1, column=3, sticky=W)
		self.settout.trace("w", self.outsidetemp)
		
		self.startbutton = Button(self, text="Start Simulation", command=self.start, fg="green", width=13)
		self.startbutton.grid(row=2, column=1)
		self.endbutton = Button(self, text="End Simulation", command=self.end, fg="grey", width=13, state=DISABLED)
		self.endbutton.grid(row=2, column=2, columnspan=2)
		
		self.map = PhotoImage(width=Data.nb_x, height=Data.nb_y)
		self.fill(self.map, (255, 255, 255))
		self.simulation = Label(self, image=self.map)
		self.simulation.grid(row=3, rowspan=4, column=1, columnspan=3)
		
		self.dimensions = Label(self, text="""Dimensions:
		\nx-axis: {} m
		\ny-axis: {} m""".format(Data.dx * Data.nb_x, Data.dy * Data.nb_y))
		self.dimensions.grid(row=0, rowspan=3, column=4)
		
		self.temperature = Label(self, text="Temperature:")
		self.temperature.grid(row=3, column=4)
		self.red_image = PhotoImage(file="/Users/Guy/Desktop/Projets/Python/Heat Transfer Simulations/image/red.gif")
		self.red = Label(self, image=self.red_image, text=" = 0.0 K  ", compound=LEFT)
		self.red.grid(row=4, column=4)
		self.green_image = PhotoImage(file="/Users/Guy/Desktop/Projets/Python/Heat Transfer Simulations/image/green.gif")
		self.green = Label(self, image=self.green_image, text=" = 0.0 K  ", compound=LEFT)
		self.green.grid(row=5, column=4)
		self.blue_image = PhotoImage(file="/Users/Guy/Desktop/Projets/Python/Heat Transfer Simulations/image/blue.gif")
		self.blue = Label(self, image=self.blue_image, text=" = 0.0 K  ", compound=LEFT)
		self.blue.grid(row=6, column=4)
		
		self.grid(sticky=W+E+N+S)
		
		self.isrunnung = False
		self.temp = None
		self.tout = None
		
		self.wb = Workbook()
		self.ws = self.wb.add_sheet("Simulation1")
		self.nb_simulation = 1
		self.points = []
		
	def _initmenu(self):
		"""Method to initialize the menu bar of the interface."""
		
		menubar = Menu(self.root)
		self.root.config(menu=menubar)
		
		self.filemenu = Menu(menubar)
		self.filemenu.add_command(label="Follow Point", underline=7, command=self.addpoint)
		self.filemenu.add_command(label="Export Data", underline=0, command=self.export)
		
		self.objectmenu = Menu(menubar)
		self.objectmenu.add_command(label="Add Object", underline=0, command=self.addobject)
		
		self.windowmenu = Menu(menubar)
		self.windowmenu.add_command(label="Add Window", command=self.addwindow)
		
		menubar.add_cascade(label="File", underline=0, menu=self.filemenu)
		menubar.add_cascade(label="Object", underline=0, menu=self.objectmenu)
		menubar.add_cascade(label="Window", underline=0, menu=self.windowmenu)
		menubar.add_command(label="Help", underline=0, command=self.help)
		
	def _showerror(self, error):
		"""Used to show any error that the user may encounter."""
	
		top = Toplevel()
		top.title("Error")
		
		msg = Message(top, text=error, aspect=500, justify=CENTER)
		msg.grid()
		
		button = Button(top, text="OK", command=top.destroy)
		button.grid()
		
	def addobject(self):
		"""Command of the "Add Object" option in "Object" menu.
		Creates an Object object to support object creation.
		"""
		
		newobj = Object(self)
		newobj.config()
		
	def addpoint(self):
		"""Asks user to choose a point that will be followed.
		Each temperature value of this point will be written in
		an Excel worksheet that can be saved with the "Export"
		command.
		"""
		
		self.top = Toplevel()
		self.top.title("Follow Point")
		
		self.name = StringVar(value="Point{}".format(len(self.points) + 1))
		name_label = Label(self.top, text="Point name:", width=15)
		name_entry = Entry(self.top, textvariable=self.name, width=15)
		name_label.grid(row=0, column=0)
		name_entry.grid(row=0, column=1, columnspan=2)
		
		self.xpos = DoubleVar()
		xpos_label = Label(self.top, text="x-position:", width=20)
		xpos_entry = Entry(self.top, textvariable=self.xpos, width=10)
		xpos_label.grid(row=1, column=0, columnspan=2)
		xpos_entry.grid(row=1, column=2)

		self.ypos = DoubleVar()
		ypos_label = Label(self.top, text="y-position:", width=20)
		ypos_entry = Entry(self.top, textvariable=self.ypos, width=10)
		ypos_label.grid(row=2, column=0, columnspan=2)
		ypos_entry.grid(row=2, column=2)
		
		follow = Button(self.top, text="Follow Point", command=self.newpoint)
		follow.grid(row=3, column=2)
		
	def addwindow(self):
		"""Command of the "Add Window" option in "Window" menu.
		Creates a Window object to support window creation.
		"""
		
		newwindow = Window(self)
		newwindow.config()
		
	def delobject(self, name):
		"""Deletes an object and erases it from the heatmap."""
		
		for object in Object.objects:
			if object["name"] == name:
				i = object["left"]
				while i <= object["right"]:
					self.map.put("white", (i, object["bottom"]))
					self.map.put("white", (i, object["top"]))
					i += 1
				
				j = object["top"]
				while j <= object["bottom"]:
					self.map.put("white", (object["left"], j))
					self.map.put("white", (object["right"], j))
					j += 1
				
				self.simulation["image"] = self.map
				
		Object.objects = [object for object in Object.objects if object["name"] != name]
		
		if Object.objects == []:
			self.objectmenu.delete("Modify Object")
			self.objectmenu.delete("Delete Object")
			
	def delwindow(self, name):
		"""Deletes a window and erases it from the heatmap."""
		
		for window in Window.windows:
			if window["name"] == name:
				if window["side"].lower() == "left" or window["side"].lower() == "right":
					if window["side"].lower() == "left":
						i = 0
					elif window["side"].lower() == "right":
						i = Data.nb_x - 1
					j = window["min"]
					while j <= window["max"]:
						self.map.put("white", (i, j))
						self.map.put("white", (i, j))
						j += 1
			
				elif window["side"].lower() == "top" or window["side"].lower() == "bottom":
					if window["side"].lower() == "top":
						j = 0
					elif window["side"].lower() == "bottom":
						j = Data.nb_y - 1
					i = window["min"]
					while i <= window["max"]:
						self.map.put("blue", (i, j))
						self.map.put("blue", (i, j))
						i += 1
		
		Window.windows = [window for window in Window.windows if window["name"] != name]
		
		if Window.windows == []:
			self.windowmenu.delete("Modify Window")
			self.windowmenu.delete("Delete Window")
	
	def end(self):
		"""Command of self.endbutton. Used to end simulation.
		Heatmap will be reinitialized and cannot be recovered.
		"""
	
		self.isrunning = False
		self.points = []
		del self.data
		del self.heatmap

		self.nb_simulation += 1
		self.ws = self.wb.add_sheet("Simulation{}".format(self.nb_simulation))
		
		self.temp = None
		self.tout = None
		self.settemp.set("")
		self.settout.set("")
		self.temp_entry["state"] = NORMAL
		self.tout_entry["state"] = DISABLED
		self.endbutton.config(fg="grey", state=DISABLED)
		self.startbutton.config(text="Start Simulation", command=self.start, fg="green")
		self.objectmenu.entryconfig(1, state=NORMAL)
		self.windowmenu.entryconfig(1, state=NORMAL)
		
		try:
			self.objectmenu.delete("Modify Object")
			self.objectmenu.delete("Delete Object")
			self.windowmenu.delete("Modify Window")
			self.windowmenu.delete("Delete Window")
		except TclError:
			pass
		
		self.map = PhotoImage(width=Data.nb_x, height=Data.nb_y)
		self.fill(self.map, (255, 255, 255))
		self.simulation["image"] = self.map
		
	def export(self):
		"""Export the work sheet to an Excel file for further
		data manipulation."""
		
		filename = str(randint(1, 9999999999))
		self.wb.save(filename + ".xls")
		
	def fieldtemp(self, *args):
		"""Callback method of the field temperature entry.
		Verifies if the entry can be treated as a number.
		"""
		
		try:
			temp = float(self.settemp.get())
			assert temp >= 0
			
		except ValueError:
			if self.settemp.get() is "" or self.settemp.get() is "-":
				pass
			else:
				try:
					raise ValueError("Field temperature must be an integer or decimal number.")
				except ValueError as error:
					self._showerror(error)
		
		except AssertionError:
			try:
				raise ValueError("Field temperature must be in Kelvin and no less than absolute zero.")
			except ValueError as error:
				self._showerror(error)
		
		else:
			self.temp = temp
		
	def fill(self, image, color):
		"""Fill image with a color in (r, g, b) format. Used at
		initialization of interface and at end of simulation.
		"""
	
		r, g, b = color
		width = image.width()
		height = image.height()
		hexcode = "#%02x%02x%02x" % (r, g, b)
		horizontal_line = "{" + " ".join([hexcode] * width) + "}"
		image.put(" ".join([horizontal_line] * height))
		
	def help(self):
		"""Command of "Help" in the main menu. Opens a html 
		page with some guidelines on how to use this program.
		"""
		
		webbrowser.open("file://" + os.path.realpath("help.html"))
		
	def newpoint(self):
		"""Adds a new point to ths list of points for which the
		temperature values will be kept in an Excel work sheet.
		"""
		
		name = self.name.get()
		
		try:
			xpos = self.xpos.get()
			ypos = self.ypos.get()
			ipos = round(xpos/Data.dx)
			jpos = Data.nb_y - round(ypos/Data.dy)
			
			if not 0 <= ipos < Data.nb_x or not 0 <= jpos < Data.nb_y:
				raise HeatmapError("The point {} must be in the visible heatmap.".format(name))
					
		except TclError:
			try:
				raise ValueError("The x- and y- positions of point {} need to be integers or decimal numbers.".format(name))
			except ValueError as error:
				self._showerror(error)
				
		except HeatmapError as error:
			self._showerror(error)
			
		else:
			self.ws.write(0, len(self.points) + 1, name)
			self.points.append((ipos, jpos))
			self.top.destroy()
		
	def outsidetemp(self, *args):
		"""Callback method of the outside temperature entry.
		Verifies if the entry can be treated as a number.
		"""
		
		try:
			tout = float(self.settout.get())
			assert tout >= 0
			
		except ValueError:
			if self.settout.get() is "" or self.settout.get() is "-":
				pass
			else:
				try:
					raise ValueError("Outside temperature must be an integer or decimal number.")
				except ValueError as error:
					self._showerror(error)
		
		except AssertionError:
			try:
				raise ValueError("Outside temperature must be in Kelvin and no less than absolute zero.")
			except ValueError as error:
				self._showerror(error)
		
		else:
			self.tout = tout
	
	def pause(self):
		"""Command to pause simulation."""
		
		self.isrunning = False
		self.startbutton.config(text="Resume Simulation", command=self.resume, fg="green")
		
	def quit(self):
		"""Method called when the user interface is closed."""
		
		self.wb.close()
		Misc.quit(self)
		
	def resume(self):
		"""Command to resume simulation."""
	
		self.isrunning = True
		self.startbutton.config(text="Pause Simulation", command=self.pause, fg="red")
		self.run()
		
	def run(self):
		"""Iterate numerical data and update heatmap."""
		
		n = 1
		while self.isrunning:
			self.ws.write(n, 0, n * Data.dt)
			for i, p in enumerate(self.points):
				self.ws.write(n, i + 1, self.data.field[p[1]][p[0]])
			self.data.iterate()
			self.simulation["image"] = self.heatmap.get(self.data.field)
			self.update()
			n += 1
			
	def showobject(self, object):
		"""Show the object on the heatmap."""
		
		if Object.objects == []:
				self.objectmenu.add_command(label="Modify Object", underline=0, command=self.tomodify_object)
				self.objectmenu.add_command(label="Delete Object", underline=0, command=self.todelete_object)
			
		i = object["left"]
		while i <= object["right"]:
			self.map.put("red", (i, object["bottom"]))
			self.map.put("red", (i, object["top"]))
			i += 1
				
		j = object["top"]
		while j <= object["bottom"]:
			self.map.put("red", (object["left"], j))
			self.map.put("red", (object["right"], j))
			j += 1
				
		self.simulation["image"] = self.map
		
	def showwindow(self, window):
		"""Show the window on the heatmap."""
		
		if Window.windows == []:
				self.windowmenu.add_command(label="Modify Window", command=self.tomodify_window)
				self.windowmenu.add_command(label="Delete Window", command=self.todelete_window)
				self.tout_entry["state"] = NORMAL
			
		if window["side"].lower() == "left" or window["side"].lower() == "right":
			if window["side"].lower() == "left":
				i = 0
			elif window["side"].lower() == "right":
				i = Data.nb_x - 1
			j = window["min"]
			while j <= window["max"]:
				self.map.put("blue", (i, j))
				self.map.put("blue", (i, j))
				j += 1
			
		elif window["side"].lower() == "top" or window["side"].lower() == "bottom":
			if window["side"].lower() == "top":
				j = 0
			elif window["side"].lower() == "bottom":
				j = Data.nb_y - 1
			i = window["min"]
			while i <= window["max"]:
				self.map.put("blue", (i, j))
				self.map.put("blue", (i, j))
				i += 1
				
		self.simulation["image"] = self.map
	
	def start(self):
		"""Default command of self.startbutton. Starts simulation.
		If no object has been added by user, raises NoObjectError.
		Otherwise, creates the data array and the initial heatmap.
		"""
		
		if Object.objects == [] and Window.windows == []:
			try:
				raise UniformFieldError("At least one object or window must be added to start simulation.")
			except UniformFieldError as error:
				self._showerror(error)
		
		elif self.temp is None:
			try:
				raise AttributeError("Value not found for the field temperature.")
			except AttributeError as error:
				self._showerror(error)
		
		elif self.tout is None and Window.windows != []:
			try:
				raise AttributeError("Value not found for the outside temperature.")
			except AttributeError as error:
				self._showerror(error)
		
		else:
			self.objectmenu.entryconfig(1, state=DISABLED)
			self.objectmenu.entryconfig(2, state=DISABLED)
			self.objectmenu.entryconfig(3, state=DISABLED)
			self.windowmenu.entryconfig(1, state=DISABLED)
			self.windowmenu.entryconfig(2, state=DISABLED)
			self.windowmenu.entryconfig(3, state=DISABLED)
			self.temp_entry["state"] = "readonly"
			self.tout_entry["state"] = "readonly"
			
			if self.tout is None:
				self.tout = self.temp
			self.data = Data(self.temp, self.tout, Object.objects, Window.windows)
			Object.objects = []
			Window.windows = []
			self.heatmap = Heatmap(self.data.range, self.data.haswindow)
			self.simulation["image"] = self.heatmap.get(self.data.field)			
			
			self.red["text"] = " = {} K".format(str(round(self.heatmap.red))[:5])
			self.green["text"] = " = {} K".format(str(round(self.heatmap.green))[:5])
			self.blue["text"] = " = {} K".format(str(round(self.heatmap.blue))[:5])
			self.startbutton.config(text="Pause Simulation", command=self.pause, fg="red")
			self.endbutton.config(fg="red", state=NORMAL)
			
			self.ws.write(0,0, "t")
			self.isrunning = True
			self.update_idletasks()
			self.run()
			
	def todelete_object(self):
		"""Allows user to choose the object he wants to delete."""
		
		newobj = Object(self)
		
		newobj.top = Toplevel()
		newobj.top.title("Delete Object")
		
		text = Label(newobj.top, text="Which object do you want to delete?")
		text.pack()
		
		def choose():
			delete_button["state"] = NORMAL
		
		newobj.name = StringVar()
		for object in Object.objects:
			radio = Radiobutton(newobj.top, text=object["name"], variable=newobj.name, value=object["name"], command=choose)
			radio.pack()
		
		delete_button = Button(newobj.top, text="Delete Object", command=newobj.delete, state=DISABLED)
		delete_button.pack()
		
	def todelete_window(self):
		"""Allows user to choose the window he wants to delete."""
		
		newwindow = Window(self)
		
		newwindow.top = Toplevel()
		newwindow.top.title("Delete Window")
		
		text = Label(newwindow.top, text="Which window do you want to delete?")
		text.pack()
		
		def choose():
			modify_button["state"] = NORMAL
		
		newwindow.name = StringVar()
		for window in Window.windows:
			radio = Radiobutton(newwindow.top, text=window["name"], variable=newwindow.name, value=window["name"], command=choose)
			radio.pack()
		
		modify_button = Button(newwindow.top, text="Delete Window", command=newwindow.delete, state=DISABLED)
		modify_button.pack()
	
	def tomodify_object(self):
		"""Allows user to choose the object he wants to modify."""
		
		newobj = Object(self)
		
		newobj.top = Toplevel()
		newobj.top.title("Modify Object")
		
		text = Label(newobj.top, text="Which object do you want to modify?")
		text.pack()
		
		def choose():
			modify_button["state"] = NORMAL
		
		newobj.name = StringVar()
		for object in Object.objects:
			radio = Radiobutton(newobj.top, text=object["name"], variable=newobj.name, value=object["name"], command=choose)
			radio.pack()
		
		modify_button = Button(newobj.top, text="Modify Object", command=newobj.modify, state=DISABLED)
		modify_button.pack()
		
	def tomodify_window(self):
		"""Allows user to choose the window he wants to modify."""
		
		newwindow = Window(self)
		
		newwindow.top = Toplevel()
		newwindow.top.title("Modify Window")
		
		text = Label(newwindow.top, text="Which window do you want to modify?")
		text.pack()
		
		def choose():
			modify_button["state"] = NORMAL
		
		newwindow.name = StringVar()
		for window in Window.windows:
			radio = Radiobutton(newwindow.top, text=window["name"], variable=newwindow.name, value=window["name"], command=choose)
			radio.pack()
		
		modify_button = Button(newwindow.top, text="Modify Window", command=newwindow.modify, state=DISABLED)
		modify_button.pack()