HYTOS

# coding: utf-8

"""

    This is code table dialog module

"""

import os

import sys, io

from PyQt5.QtCore import *

from PyQt5.QtGui import *

from PyQt5.QtWidgets import *

from AppDocData import AppDocData, MessageType

from App import App

from openpyxl import *

from openpyxl.styles import *

from PIL import Image

import tesseract_ocr_module as TOCR

import numpy as np

import CodeTable_UI

import FenceCommand

from GraphicsBoundingBoxItem import QGraphicsBoundingBoxItem

class QCodeTableDialog(QDialog):

    """ This code table dialog class """

    CODE_TABLES = (

    'Nominal Diameter', 'Fluid Code', 'Insulation Purpose', 'PnID Number', 'Piping Materials Class', 'Unit Number',

    'ValveOperCodes', 'EqpTagNames', 'ReservedWords', 'Dictionary')

    def __init__(self, parent, child_dialog=''):

        QDialog.__init__(self, parent)

        self.currentCode = {}

        self.code_area = None

        self.desc_area = None

        self.child_dialog = child_dialog

        if not child_dialog:

            self.ui = CodeTable_UI.Ui_CodeTableDialog()

            self.ui.setupUi(self)

            self.ui.spinBoxHeight.setValue(50)

            self.ui.tableWidgetNominalDiameter.setSortingEnabled(True)

            self.ui.tableWidgetFluidCode.setSortingEnabled(True)

            self.ui.tableWidgetInsulationPurpose.setSortingEnabled(True)

            self.ui.tableWidgetPnIDNumber.setSortingEnabled(True)

            self.ui.tableWidgetPipingMaterialsClass.setSortingEnabled(True)

            self.ui.tableWidgetUnitNumber.setSortingEnabled(True)

            self.ui.tableWidgetValveOperCodes.setSortingEnabled(True)

            self.ui.tableWidgetEqpTagNames.setSortingEnabled(True)

            self.ui.tableWidgetReservedWords.setSortingEnabled(True)

            # DB Table명 기준으로 작성

            for table in QCodeTableDialog.CODE_TABLES:

                self.settingTable(table)

            # connect signals

            self.ui.pushButtonImport.clicked.connect(self.import_code_table)

            self.ui.pushButtonExport.clicked.connect(self.export_code_table)

            self.ui.pushButtonRead.clicked.connect(self.read_from_legend)

        self.graphicsView = App.mainWnd().graphicsView

        self.fence_cmd = FenceCommand.FenceCommand(self.graphicsView)

        self.fence_cmd.onSuccess.connect(self.onAreaCreated)

    def getTabText(self):

        """ get current tab name text """

        if not self.child_dialog:

            _tabWidget = self.ui.tabWidget

            currentTabIndex = _tabWidget.currentIndex()

            tabText = self.replaceText(_tabWidget.tabText(currentTabIndex))

        else:

            tabText = self.child_dialog

        return tabText

    def read_from_legend(self):

        """ read code data from legend drawing """

        if self.ui.pushButtonRead.text() == 'Read from Legend':

            self.graphicsView.command = self.fence_cmd

            self.ui.pushButtonRead.setText('Draw Code Area')

        elif self.ui.pushButtonRead.text() == 'Read' and self.code_area and self.code_area.scene() and self.desc_area and self.desc_area.scene():

            # read ocr

            code_rect = self.code_area.sceneBoundingRect()

            desc_rect = self.desc_area.sceneBoundingRect()

            code_img = self.graphicsView.image().copy(code_rect.x(), code_rect.y(), code_rect.width(), code_rect.height())

            desc_img = self.graphicsView.image().copy(desc_rect.x(), desc_rect.y(), desc_rect.width(), desc_rect.height())

            code_texts = self.detectText(code_img, code_rect)

            desc_texts = self.detectText(desc_img, desc_rect)

            # check validate

            for code_text in code_texts:

                code_text.desc = []

            if desc_texts:

                for desc_index in reversed(range(len(desc_texts))):

                    for code_index in range(len(code_texts)):

                        if abs(desc_texts[desc_index].center[1] - code_texts[code_index].center[1]) < round(self.ui.spinBoxHeight.value() / 2):

                            code_texts[code_index].desc.append(desc_texts[desc_index])

                            desc_texts.pop(desc_index)

                            break

                for desc_index in reversed(range(len(desc_texts))):

                    min_distance = sys.maxsize

                    min_code = None

                    for code_index in range(len(code_texts)):

                        distance = desc_texts[desc_index].center[1] - code_texts[code_index].center[1]

                        if distance > 0 and distance < min_distance:

                            min_distance = distance

                            min_code = code_texts[code_index]

                    if min_code:

                        min_code.desc.append(desc_texts[desc_index])

                        desc_texts.pop(desc_index)

                #QMessageBox.warning(self, self.tr('Notice'), self.tr('Please check text area.'))

                #return  

            desc_texts = []

            for code_text in code_texts:

                desc = ' '.join([desc.getText() for desc in sorted(code_text.desc, key=lambda desc: desc.center[1])])

                desc_texts.append(desc)

            # fill table

            tabText = self.getTabText()

            if tabText == 'NominalDiameter':

                return

            table = self.findTableWidget(tabText)

            table.cellChanged.disconnect(self.cellValueChanged)

            past_count = table.rowCount()

            for row_index in range(past_count):

                for code_index in reversed(range(len(code_texts))):

                    if table.isRowHidden(row_index): continue

                    if table.item(row_index, 1) and table.item(row_index, 1).text() == code_texts[code_index].getText():

                        table.setItem(row_index, 2, QTableWidgetItem(desc_texts[code_index]))

                        code_texts.pop(code_index)

                        desc_texts.pop(code_index)

                        break

            table.setRowCount(past_count + len(code_texts))

            for code_index in range(len(code_texts)):

                table.setItem(past_count + code_index - 1, 0, QTableWidgetItem(''))

                table.setItem(past_count + code_index - 1, 1, QTableWidgetItem(code_texts[code_index].getText()))

                table.setItem(past_count + code_index - 1, 2, QTableWidgetItem(desc_texts[code_index]))

            last_empty_row = table.rowCount()

            table.setItem(last_empty_row - 1, 0, QTableWidgetItem(''))

            table.setItem(last_empty_row - 1, 1, QTableWidgetItem(''))

            table.setItem(last_empty_row - 1, 2, QTableWidgetItem(''))

            table.cellChanged.connect(self.cellValueChanged)

            if self.code_area:

                if self.code_area.scene():

                    self.graphicsView.scene.removeItem(self.code_area)

                self.code_area = None

            if self.desc_area:

                if self.desc_area.scene():

                    self.graphicsView.scene.removeItem(self.desc_area)

                self.desc_area = None

            self.ui.pushButtonRead.setText('Read from Legend')

        else:

            if self.code_area:

                if self.code_area.scene():

                    self.graphicsView.scene.removeItem(self.code_area)

                self.code_area = None

            if self.desc_area:

                if self.desc_area.scene():

                    self.graphicsView.scene.removeItem(self.desc_area)

                self.desc_area = None

            self.ui.pushButtonRead.setText('Read from Legend')

    def onAreaCreated(self, x, y , width, height):

        import uuid

        THICKNESS = 5

        if self.ui.pushButtonRead.text() == 'Draw Code Area':

            item = QGraphicsBoundingBoxItem(x, y, width, height)

            item.setPen(QPen(Qt.red, THICKNESS, Qt.SolidLine))

            self.graphicsView.scene.addItem(item)

            self.code_area = item

            self.ui.pushButtonRead.setText('Draw Description Area')

        elif self.ui.pushButtonRead.text() == 'Draw Description Area' and self.code_area and self.code_area.scene():

            item = QGraphicsBoundingBoxItem(x, y, width, height)

            item.setPen(QPen(Qt.blue, THICKNESS, Qt.SolidLine))

            self.graphicsView.scene.addItem(item)

            self.desc_area = item

            self.ui.pushButtonRead.setText('Read')

            self.graphicsView.command = None

    def detectText(self, image, rect):

        """ detect text from image, come from OcrResultDialog and modified """

        try:

            buffer = QBuffer()

            buffer.open(QBuffer.ReadWrite)

            image.save(buffer, "PNG")

            pyImage = Image.open(io.BytesIO(buffer.data()))

            img = np.array(pyImage)

            docData = AppDocData.instance()

            configs = docData.getConfigs('Text Recognition', 'OCR Data')

            ocr_data = configs[0].value if 1 == len(configs) else 'eng'

            whiteCharList = docData.getConfigs('Text Recognition', 'White Character List')

            if len(whiteCharList) is 0:

                textInfoList = TOCR.getTextInfo(img, (round(rect.x()), round(rect.y())), 0, language=ocr_data)

            else:

                textInfoList = TOCR.getTextInfo(img, (round(rect.x()), round(rect.y())), 0, language=ocr_data, conf = whiteCharList[0].value)

            if textInfoList is not None and len(textInfoList) > 0:

                return textInfoList

        except Exception as ex:

            from App import App 

            message = 'error occurred({}) in {}:{}'.format(ex, sys.exc_info()[-1].tb_frame.f_code.co_filename, sys.exc_info()[-1].tb_lineno)

            App.mainWnd().addMessage.emit(MessageType.Error, message)

    '''

        @brief      Setting Table

        @author     kyouho

        @date       2018.07.10

    '''

    def settingTable(self, tableName, symbol_attribute_uid=None, tableDatas=None):

        try:

            tableName = self.replaceText(tableName)

            docData = AppDocData.instance()

            table = self.findTableWidget(tableName)

            if table: table.horizontalHeader().setStretchLastSection(True)

            if tableName == "NominalDiameter":

                tableDatas = docData.getNomialPipeSizeData()

            elif tableName == "SymbolAttributeCodeTable":

                if tableDatas is None:

                    tableDatas = docData.getCodeTable(tableName, forCheckLineNumber=False, symbol_attribute_uid=symbol_attribute_uid)

                else:

                    pass

            else:

                tableDatas = docData.getCodeTable(tableName)

            if tableName == "NominalDiameter":

                self.fill_nominal_pipe_sizes(tableDatas)

                table.cellChanged.connect(self.cellValueChanged)

                self.checkRowAndAddRow(tableName, table)

            else:

                table.setColumnCount(4)

                table.setHorizontalHeaderLabels(['UID', 'Code', 'Desc.', 'Allowables'])

                table.hideColumn(0)

                self.fill_codes(table, tableDatas)

                table.horizontalHeaderItem(1).setSizeHint(QSize(30, 30))

                table.cellChanged.connect(self.cellValueChanged)

                self.checkRowAndAddRow(tableName, table)

                self.setCurrentCode(table, tableName)

        except Exception as ex:

            print('error occurred({}) in {}:{}'.format(ex, sys.exc_info()[-1].tb_frame.f_code.co_filename,

                                                       sys.exc_info()[-1].tb_lineno))

    '''

        @brief      Insert row NominalPipeSzie Tablewidget

        @author     kyouho

        @date       2018.07.10

    '''

    def fill_nominal_pipe_sizes(self, pipeSizes):

        try:

            self.ui.tableWidgetNominalDiameter.setColumnCount(8)

            self.ui.tableWidgetNominalDiameter.setHorizontalHeaderLabels(

                ['UID', 'Code', 'Metric', 'Inch', 'InchStr', 'Inch Allowables', 'MetricStr', 'Metric Allowables'])

            self.ui.tableWidgetNominalDiameter.hideColumn(0)

            self.ui.tableWidgetNominalDiameter.setRowCount(len(pipeSizes))

            row = 0

            for pipeSize in pipeSizes:

                item = QTableWidgetItem(pipeSize.uid)

                item.setData(Qt.UserRole, pipeSize)

                self.ui.tableWidgetNominalDiameter.setItem(row, 0, item)

                self.ui.tableWidgetNominalDiameter.setItem(row, 1, QTableWidgetItem(pipeSize.code))

                self.ui.tableWidgetNominalDiameter.setItem(row, 2, QTableWidgetItem(

                    '' if pipeSize.metric is None else str(pipeSize.metric)))

                self.ui.tableWidgetNominalDiameter.setItem(row, 3, QTableWidgetItem(

                    '' if pipeSize.inch is None else str(pipeSize.inch)))

                self.ui.tableWidgetNominalDiameter.setItem(row, 4, QTableWidgetItem(

                    '' if pipeSize.inchStr is None else pipeSize.inchStr))

                self.ui.tableWidgetNominalDiameter.setItem(row, 5, QTableWidgetItem(

                    '' if pipeSize.allowable_inch_str is None else pipeSize.allowable_inch_str))

                self.ui.tableWidgetNominalDiameter.setItem(row, 6, QTableWidgetItem(

                    '' if pipeSize.metricStr is None else pipeSize.metricStr))

                self.ui.tableWidgetNominalDiameter.setItem(row, 7, QTableWidgetItem(

                    '' if pipeSize.allowable_metric_str is None else pipeSize.allowable_metric_str))

                row += 1

            self.ui.tableWidgetNominalDiameter.horizontalHeaderItem(0).setSizeHint(QSize(30, 30))

        except Exception as ex:

            from App import App

            message = 'error occurred({}) in {}:{}'.format(ex, sys.exc_info()[-1].tb_frame.f_code.co_filename,

                                                           sys.exc_info()[-1].tb_lineno)

            App.mainWnd().addMessage.emit(MessageType.Error, message)

    '''

        @brief      Insert row Common Tablewidget

        @author     kyouho

        @date       2018.07.10

    '''

    def fill_codes(self, table, tableDatas):

        try:

            table.setRowCount(len(tableDatas))

            row = 0

            for tableData in tableDatas:

                table.setItem(row, 0, QTableWidgetItem(tableData[0]))  # UID

                table.setItem(row, 1, QTableWidgetItem(tableData[1]))  # Name

                table.setItem(row, 2, QTableWidgetItem(tableData[2]))  # Description

                table.setItem(row, 3, QTableWidgetItem(tableData[3] if type(tableData[3]) is str else ','.join(tableData[3])))  # Allowables

                row += 1

        except Exception as ex:

            from App import App

            message = 'error occurred({}) in {}:{}'.format(ex, sys.exc_info()[-1].tb_frame.f_code.co_filename,

                                                           sys.exc_info()[-1].tb_lineno)

            App.mainWnd().addMessage.emit(MessageType.Error, message)

    '''

        @brief      Find TableWidget with Name

        @author     kyouhokyouho

        @date       2018.07.10

    '''

    def findTableWidget(self, tableName):

        tableName = self.replaceText(tableName)

        return self.findChild(QTableWidget, 'tableWidget' + tableName)

    '''

        @brief      key press event

        @author     kyouho

        @date       2018.07.10

    '''

    def keyPressEvent(self, e):

        try:

            if e.key() == Qt.Key_Delete:

                tabText = self.getTabText()

                table = self.findTableWidget(tabText)

                if table:

                    selectedIndexes = table.selectedIndexes()

                    selectedRows = [item.row() for item in selectedIndexes]

                    model = table.model()

                    rowsIndex = []

                    for row in selectedRows:

                        rowsIndex.append(row)

                    # 중복 제거

                    rowsIndex = list(set(rowsIndex))

                    rowsIndex.reverse()

                    if tabText != "NominalDiameter":

                        for row in rowsIndex:

                            table.hideRow(row)

                            """

                            uid = table.item(row, 0).text()

                            self.removeUID[uid] = tabText

                            model.removeRow(row)

                            """

                        self.checkRowAndAddRow(tabText, table)

            elif (e.key() == Qt.Key_C) and (e.modifiers() & Qt.ControlModifier):

                tabText = self.getTabText()

                table = self.findTableWidget(tabText)

                if table:

                    self.copy_selection(table)

            elif (e.key() == Qt.Key_V) and (e.modifiers() & Qt.ControlModifier):

                tabText = self.getTabText()

                '''

                table = self.findTableWidget(tabText)

                tab = self.ui.tabWidget.widget(self.ui.tabWidget.currentIndex())

                table = tab.findChild(QTableWidget)

                '''

                table = self.findTableWidget(tabText)

                if table:

                    self.paste_selection(table)

        except Exception as ex:

            from App import App

            message = 'error occurred({}) in {}:{}'.format(repr(ex), sys.exc_info()[-1].tb_frame.f_code.co_filename,

                                                           sys.exc_info()[-1].tb_lineno)

            App.mainWnd().addMessage.emit(MessageType.Error, message)

    def copy_selection(self, table_widget):

        """copy selected text to clipboard"""

        import io

        import csv

        selection = table_widget.selectedIndexes()

        if selection:

            rows = sorted(index.row() for index in selection)

            columns = sorted(index.column() for index in selection)

            rowcount = rows[-1] - rows[0] + 1

            colcount = columns[-1] - columns[0] + 1

            table = [[''] * colcount for _ in range(rowcount)]

            for index in selection:

                row = index.row() - rows[0]

                column = index.column() - columns[0]

                table[row][column] = index.data()

            stream = io.StringIO()

            csv.writer(stream, delimiter='\t').writerows(table)

            QApplication.clipboard().setText(stream.getvalue())

    def paste_selection(self, table_widget):

        """paste text of clipboard to table widget"""

        import io

        import csv

        selection = table_widget.selectedIndexes()

        if selection:

            model = table_widget.model()

            buffer = QApplication.clipboard().text()

            rows = sorted(index.row() for index in selection)

            columns = sorted(index.column() for index in selection)

            reader = csv.reader(io.StringIO(buffer), delimiter='\t')

            if len(rows) == 1 and len(columns) == 1:

                for i, line in enumerate(reader):

                    for j, cell in enumerate(line):

                        model.setData(model.index(rows[0] + i, columns[0] + j), cell)

            else:

                arr = [[cell for cell in row] for row in reader]

                for index in selection:

                    row = index.row() - rows[0]

                    column = index.column() - columns[0]

                    model.setData(model.index(index.row(), index.column()), arr[row][column])

    '''

        @brief      Add new row

        @author     kyouho

        @date       2018.07.10

    '''

    def checkRowAndAddRow(self, tableName, table):

        try:

            rowCount = table.rowCount()

            result = True

            if tableName != "NominalDiameter":

                for row in range(rowCount):

                    if table.isRowHidden(row): continue

                    code = table.item(row, 1).text()

                    if not code:

                        result = False

                if result:

                    table.cellChanged.disconnect(self.cellValueChanged)

                    table.setRowCount(rowCount + 1)

                    table.setItem(rowCount, 0, QTableWidgetItem(''))

                    table.setItem(rowCount, 1, QTableWidgetItem(''))

                    table.setItem(rowCount, 2, QTableWidgetItem(''))

                    table.setItem(rowCount, 3, QTableWidgetItem(''))

                    table.cellChanged.connect(self.cellValueChanged)

            else:

                columnCount = table.columnCount()

                for row in range(rowCount):

                    if not result:

                        break

                    for columnIndex in range(columnCount):

                        if not table.item(row, columnIndex).text():

                            result = False

                            break

                if result:

                    table.setRowCount(rowCount + 1)

                    table.cellChanged.disconnect(self.cellValueChanged)

                    for columnIndex in range(columnCount):

                        table.setItem(rowCount, columnIndex, QTableWidgetItem(''))

                    table.cellChanged.connect(self.cellValueChanged)

        except Exception as ex:

            print('error occurred({}) in {}:{}'.format(ex, sys.exc_info()[-1].tb_frame.f_code.co_filename,

                                                       sys.exc_info()[-1].tb_lineno))

    '''

        @brief      cellValueChange event

        @author     kyouho

        @date       2018.07.10

    '''

    def cellValueChanged(self, row, column):

        try:

            tabText = self.getTabText()

            table = self.findTableWidget(tabText)

            if tabText != "NominalDiameter":

                item = table.item(row, 1)

                code = item.text()

                if column == 1:

                    result = self.isExistCode(table, code)

                    if result:

                        self.checkRowAndAddRow(tabText, table)

                        self.setCurrentCode(table, tabText)

                    else:

                        QMessageBox.warning(self, self.tr('Notice'),

                                            self.tr('The same code already exists in the table.'))

                        table.cellChanged.disconnect(self.cellValueChanged)

                        item.setText(self.currentCode[tabText][row])

                        table.cellChanged.connect(self.cellValueChanged)

                elif column == 2:

                    table.resizeColumnToContents(2)

                else:

                    table.resizeColumnToContents(3)

            else:

                self.checkRowAndAddRow(tabText, table)

        except Exception as ex:

            print('error occurred({}) in {}:{}'.format(ex, sys.exc_info()[-1].tb_frame.f_code.co_filename,

                                                       sys.exc_info()[-1].tb_lineno))

    '''

        @brief      Check Duplicate Code 

        @author     kyouho

        @date       2018.07.10

    '''

    def isExistCode(self, table, editCode):

        try:

            if not editCode:

                return False

            rowCount = table.rowCount()

            codes = []

            for row in range(rowCount):

                if table.isRowHidden(row): continue

                code = table.item(row, 1).text()

                codes.append(code)

            count = codes.count(editCode)

            if count >= 2:

                return False

            else:

                return True

        except Exception as ex:

            print('error occurred({}) in {}:{}'.format(ex, sys.exc_info()[-1].tb_frame.f_code.co_filename,

                                                       sys.exc_info()[-1].tb_lineno))

    '''

        @brief      save current Code (self.currentCode)

        @author     kyouho

        @date       2018.07.10

    '''

    def setCurrentCode(self, table, tabText):

        try:

            self.currentCode[tabText] = {}

            rowCount = table.rowCount()

            res = {}

            for row in range(rowCount):

                code = table.item(row, 1).text()

                res[row] = code

            self.currentCode[tabText] = res

        except Exception as ex:

            print('error occurred({}) in {}:{}'.format(ex, sys.exc_info()[-1].tb_frame.f_code.co_filename,

                                                       sys.exc_info()[-1].tb_lineno))

    '''

        @brief      replaceTextForCodeTable

        @author     kyouho

        @date       2018.07.12

    '''

    def replaceText(self, text):

        return text.replace(' ', '').replace('&&', 'n')

    def import_code_table(self):

        """import code table excel file"""

        options = QFileDialog.Options()

        options |= QFileDialog.DontUseNativeDialog

        file_name, _ = QFileDialog.getOpenFileName(self, "Import code table", os.getcwd(), "xlsx files(*.xlsx)",

                                                   options=options)

        if file_name:

            QApplication.setOverrideCursor(Qt.WaitCursor)

            try:

                app_doc_data = AppDocData.instance()

                book = load_workbook(file_name)

                for sheet in book.worksheets:

                    matches = [index for index in range(self.ui.tabWidget.count())

                               if sheet.title == self.ui.tabWidget.tabText(index)]

                    if matches:

                        table = self.ui.tabWidget.widget(matches[0]).findChild(QTableWidget)

                        self.fill_table_with_sheet(sheet, table)

            except Exception as ex:

                from App import App

                message = 'error occurred({}) in {}:{}'.format(repr(ex), sys.exc_info()[-1].tb_frame.f_code.co_filename,

                                                               sys.exc_info()[-1].tb_lineno)

                App.mainWnd().addMessage.emit(MessageType.Error, message)

            finally:

                QApplication.restoreOverrideCursor()

    def export_code_table(self):

        """export code table to excel file"""

        app_doc_data = AppDocData.instance()

        project = app_doc_data.getCurrentProject()

        options = QFileDialog.Options()

        options |= QFileDialog.DontUseNativeDialog

        file_name, _ = QFileDialog.getSaveFileName(self, "Export code table", project.path, "xlsx files(*.xlsx)",

                                                   options=options)

        if not file_name:

            return

        QApplication.setOverrideCursor(Qt.WaitCursor)

        try:

            wb = Workbook()

            wb.active.title = self.tr(self.ui.tabWidget.tabText(0))

            for index in range(1, self.ui.tabWidget.count()):

                wb.create_sheet(self.tr(self.ui.tabWidget.tabText(index)))

            for index in range(self.ui.tabWidget.count()):

                tab = self.ui.tabWidget.widget(index)

                table = tab.findChild(QTableWidget)

                if table:

                    sheet = wb.worksheets[index]

                    self.set_sheet_header(table, sheet)

                    self.fill_sheet_with_table(table, sheet)

                    self.auto_resize_columns(sheet)

            file_name, ext = os.path.splitext(file_name)

            save_file_name = file_name + ext if ext.upper() == '.XLSX' else file_name + '.xlsx'

            wb.save(save_file_name)

            QMessageBox.about(self, self.tr("Information"), self.tr('Successfully saved.'))

            os.startfile(save_file_name)

        except Exception as ex:

            from App import App

            from AppDocData import MessageType

            message = 'error occurred({}) in {}:{}'.format(repr(ex), sys.exc_info()[-1].tb_frame.f_code.co_filename,

                                                           sys.exc_info()[-1].tb_lineno)

            App.mainWnd().addMessage.emit(MessageType.Error, message)

        finally:

            QApplication.restoreOverrideCursor()

    def set_sheet_header(self, table, sheet):

        """ set list header """

        try:

            thin = Side(border_style='thin', color='000000')

            border = Border(left=thin, right=thin, top=thin, bottom=thin)

            _col = 1

            for col in range(table.columnCount()):

                logical_index = table.horizontalHeader().logicalIndex(col)

                col_name = table.horizontalHeaderItem(logical_index).text()

                sheet.cell(1, _col, col_name)

                sheet.cell(row=1, column=_col).alignment = Alignment(horizontal='center', vertical='center',

                                                                     wrapText=True)

                sheet.cell(row=1, column=_col).fill = PatternFill(patternType='solid', fill_type='solid',

                                                                  fgColor=Color('8DB4E2'))

                sheet.cell(row=1, column=_col).border = border

                _col += 1

        except Exception as ex:

            from App import App

            from AppDocData import MessageType

            message = 'error occurred({}) in {}:{}'.format(ex, sys.exc_info()[-1].tb_frame.f_code.co_filename,

                                                           sys.exc_info()[-1].tb_lineno)

            App.mainWnd().addMessage.emit(MessageType.Error, message)

    def fill_table_with_sheet(self, sheet, table):

        """fill table with sheet"""

        from NominalPipeSize import NominalPipeSize

        try:

            row_index = 0

            headers = {}

            if sheet.title == 'Nominal Diameter':

                pipe_sizes = []

                for row in sheet.rows:

                    if row_index == 0:

                        for col in range(sheet.max_column):

                            value = row[col].value

                            headers[col] = value

                    else:

                        pipe_size = NominalPipeSize(None, None, None, None, None, None, None, None)

                        for col in range(sheet.max_column):

                            value = row[col].value

                            if 'UID' == headers[col]:

                                pipe_size.uid = value

                            elif 'Code' == headers[col]:

                                pipe_size.code = value

                            elif 'Metric' == headers[col]:

                                pipe_size.metric = value

                            elif 'Inch' == headers[col]:

                                pipe_size.inch = value

                            elif 'InchStr' == headers[col]:

                                pipe_size.inchStr = value

                            elif 'Inch Allowables' == headers[col]:

                                pipe_size.allowable_inch_str = value

                            elif 'MetricStr' == headers[col]:

                                pipe_size.metricStr = value

                            elif 'Metric Allowables' == headers[col]:

                                pipe_size.allowable_metric_str = value

                        pipe_sizes.append(pipe_size)

                    row_index += 1

                self.fill_nominal_pipe_sizes(pipe_sizes)

            else:

                codes = []

                for row in sheet.rows:

                    if row_index == 0:

                        for col in range(sheet.max_column):

                            value = row[col].value

                            headers[col] = value

                    else:

                        code = [row[col].value for col in range(sheet.max_column)]

                        codes.append(code)

                    row_index += 1

                self.fill_codes(table, codes)

        except Exception as ex:

            from App import App

            from AppDocData import MessageType

            message = 'error occurred({}) in {}:{}'.format(ex, sys.exc_info()[-1].tb_frame.f_code.co_filename,

                                                           sys.exc_info()[-1].tb_lineno)

            App.mainWnd().addMessage.emit(MessageType.Error, message)

    def fill_sheet_with_table(self, table, sheet):

        """fill sheet with table"""

        try:

            thin = Side(border_style='thin', color='000000')

            border = Border(left=thin, right=thin, top=thin, bottom=thin)

            for col in range(table.columnCount()):

                for row in range(table.rowCount()):

                    try:

                        text = str(table.item(row, col).text())

                        sheet.cell(row + 2, col + 1, text)

                        sheet.cell(row=row + 2, column=col + 1).border = border

                    except AttributeError:

                        pass

        except Exception as ex:

            from App import App

            from AppDocData import MessageType

            message = 'error occurred({}) in {}:{}'.format(ex, sys.exc_info()[-1].tb_frame.f_code.co_filename,

                                                           sys.exc_info()[-1].tb_lineno)

            App.mainWnd().addMessage.emit(MessageType.Error, message)

    def auto_resize_columns(self, sheet):

        """ auto resize columns with contents """

        from openpyxl.utils import get_column_letter

        try:

            for col in sheet.columns:

                max_length = 0

                column = col[0].column  # Get the column name

                for cell in col:

                    try:  # Necessary to avoid error on empty cells

                        if len(str(cell.value)) > max_length:

                            max_length = len(cell.value)

                    except:

                        pass

                adjusted_width = (max_length + 2) * 1.2

                sheet.column_dimensions[

                    get_column_letter(column) if type(column) is int else column].width = adjusted_width

        except Exception as ex:

            from App import App

            from AppDocData import MessageType

            message = 'error occurred({}) in {}:{}'.format(repr(ex), sys.exc_info()[-1].tb_frame.f_code.co_filename,

                                                           sys.exc_info()[-1].tb_lineno)

            App.mainWnd().addMessage.emit(MessageType.Error, message)

    '''

        @brief      save codes

        @author     kyouho

        @date       2018.07.12

    '''

    def accept(self):

        from CodeTables import CodeTable

        for table in QCodeTableDialog.CODE_TABLES:

            if table != 'Nominal Diameter':

                self.saveCommonCodeData(table)

        self.saveNomialPipeSize()

        CodeTable.clearTables()

        QDialog.accept(self)

    '''

        @brief      save common code data

        @author     kyouho

        @date       2018.07.12

    '''

    def saveCommonCodeData(self, tableName):

        datas = []

        try:

            tableName = self.replaceText(tableName)

            table = self.findTableWidget(tableName)

            rowCount = table.rowCount()

            for row in range(rowCount):

                if table.isRowHidden(row):

                    uid, code, description, allowables = '-1', table.item(row, 1).text(), '', table.item(row, 0).text()

                elif table.item(row, 0):

                    uid = table.item(row, 0).text()

                    code = table.item(row, 1).text()

                    description = table.item(row, 2).text() if table.item(row, 2) is not None else ''

                    allowables = table.item(row, 3).text() if table.item(row, 3) is not None else ''

                if code:

                    datas.append((uid, code, description, allowables))

            docData = AppDocData.instance()

            docData.saveCommonCodeData(tableName.replace(' ', ''), datas)

        except Exception as ex:

            from App import App

            message = 'error occurred({}) in {}:{}'.format(ex, sys.exc_info()[-1].tb_frame.f_code.co_filename,

                                                           sys.exc_info()[-1].tb_lineno)

            App.mainWnd().addMessage.emit(MessageType.Error, message)

    '''

        @brief      save common code data

        @author     kyouho

        @date       2018.07.16

    '''

    def saveNomialPipeSize(self):

        pipeSizes = []

        try:

            docData = AppDocData.instance()

            from NominalPipeSize import NominalPipeSize

            table = self.ui.tableWidgetNominalDiameter

            rowCount = table.rowCount()

            for row in range(rowCount):

                pipe_size = table.item(row, 0).data(Qt.UserRole)

                pipe_size.code = table.item(row, 1).text()

                pipe_size.metric = float(table.item(row, 2).text()) if table.item(row, 2).text() != '' else None

                pipe_size.inch = float(table.item(row, 3).text()) if table.item(row, 3).text() != '' else None

                pipe_size.inchStr = table.item(row, 4).text()

                pipe_size.allowable_inch_str = table.item(row, 5).text()

                pipe_size.metricStr = table.item(row, 6).text()

                pipe_size.allowable_metric_str = table.item(row, 7).text()

                pipeSizes.append(pipe_size)

            docData.insertNomialPipeSize(pipeSizes)

        except Exception as ex:

            from App import App

            message = 'error occurred({}) in {}:{}'.format(ex, sys.exc_info()[-1].tb_frame.f_code.co_filename,

                                                           sys.exc_info()[-1].tb_lineno)

            App.mainWnd().addMessage.emit(MessageType.Error, message)