HYTOS

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

from openpyxl import *

from openpyxl.styles import *

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

    def on_save_excel(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 OPC Table", project.path, "xlsx files(*.xlsx)",

                                                   options=options)

        if not file_name:

            return

        QApplication.setOverrideCursor(Qt.WaitCursor)

        try:

            wb = Workbook()

            wb.active.title = 'OPC Relation'

            sheet = wb.worksheets[0]

            self.set_sheet_header(self.ui.tableWidgetSource, sheet)

            self.fill_sheet_with_table(self.ui.tableWidgetSource, 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_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)

   <item row="0" column="0">

    <layout class="QHBoxLayout" name="horizontalLayout">

     <property name="sizeConstraint">

      <enum>QLayout::SetFixedSize</enum>

     </property>

     <item>

      <widget class="QPushButton" name="pushButtonAutoFill">

       <property name="maximumSize">

        <size>

         <width>16777215</width>

         <height>20</height>

        </size>

       </property>

       <property name="text">

        <string>Auto Fill</string>

       </property>

      </widget>

     </item>

     <item>

      <widget class="QPushButton" name="pushButtonAutoFillClear">

       <property name="text">

        <string>Auto Fill with Clear</string>

       </property>

      </widget>

     </item>

     <item>

      <spacer name="horizontalSpacer">

       <property name="orientation">

        <enum>Qt::Horizontal</enum>

       </property>

       <property name="sizeHint" stdset="0">

        <size>

         <width>40</width>

         <height>20</height>

        </size>

       </property>

      </spacer>

     </item>

     <item>

      <widget class="QPushButton" name="pushButtonSymbol">

       <property name="text">

        <string>Symbol Setting</string>

       </property>

      </widget>

     </item>

     <item>

      <widget class="QPushButton" name="pushButtonLineNoAttr">

       <property name="text">

        <string>Line No. Key Setting</string>

       </property>

      </widget>

     </item>

    </layout>

   </item>

   <item row="3" column="0">

    <layout class="QHBoxLayout" name="horizontalLayout_2">

     <item>

      <widget class="QPushButton" name="pushButtonExport">

       <property name="text">

        <string>Export</string>

       </property>

      </widget>

     </item>

     <item>

      <spacer name="horizontalSpacer_2">

       <property name="orientation">

        <enum>Qt::Horizontal</enum>

       </property>

       <property name="sizeHint" stdset="0">

        <size>

         <width>40</width>

         <height>20</height>

        </size>

       </property>

      </spacer>

     </item>

     <item>

      <widget class="QDialogButtonBox" name="buttonBox">

       <property name="orientation">

        <enum>Qt::Horizontal</enum>

       </property>

       <property name="standardButtons">

        <set>QDialogButtonBox::Cancel|QDialogButtonBox::Ok</set>

       </property>

      </widget>

     </item>

    </layout>

   </item>

# Created by: PyQt5 UI code generator 5.11.3

        self.horizontalLayout = QtWidgets.QHBoxLayout()

        self.horizontalLayout.setSizeConstraint(QtWidgets.QLayout.SetFixedSize)

        self.horizontalLayout.setObjectName("horizontalLayout")

        self.pushButtonAutoFill = QtWidgets.QPushButton(OPCRelationDialog)

        self.pushButtonAutoFill.setMaximumSize(QtCore.QSize(16777215, 20))

        self.pushButtonAutoFill.setObjectName("pushButtonAutoFill")

        self.horizontalLayout.addWidget(self.pushButtonAutoFill)

        self.pushButtonAutoFillClear = QtWidgets.QPushButton(OPCRelationDialog)

        self.pushButtonAutoFillClear.setObjectName("pushButtonAutoFillClear")

        self.horizontalLayout.addWidget(self.pushButtonAutoFillClear)

        spacerItem = QtWidgets.QSpacerItem(40, 20, QtWidgets.QSizePolicy.Expanding, QtWidgets.QSizePolicy.Minimum)

        self.horizontalLayout.addItem(spacerItem)

        self.pushButtonSymbol = QtWidgets.QPushButton(OPCRelationDialog)

        self.pushButtonSymbol.setObjectName("pushButtonSymbol")

        self.horizontalLayout.addWidget(self.pushButtonSymbol)

        self.pushButtonLineNoAttr = QtWidgets.QPushButton(OPCRelationDialog)

        self.pushButtonLineNoAttr.setObjectName("pushButtonLineNoAttr")

        self.horizontalLayout.addWidget(self.pushButtonLineNoAttr)

        self.gridLayout.addLayout(self.horizontalLayout, 0, 0, 1, 1)

        self.horizontalLayout_2 = QtWidgets.QHBoxLayout()

        self.horizontalLayout_2.setObjectName("horizontalLayout_2")

        self.pushButtonExport = QtWidgets.QPushButton(OPCRelationDialog)

        self.pushButtonExport.setObjectName("pushButtonExport")

        self.horizontalLayout_2.addWidget(self.pushButtonExport)

        spacerItem1 = QtWidgets.QSpacerItem(40, 20, QtWidgets.QSizePolicy.Expanding, QtWidgets.QSizePolicy.Minimum)

        self.horizontalLayout_2.addItem(spacerItem1)

        self.buttonBox = QtWidgets.QDialogButtonBox(OPCRelationDialog)

        self.buttonBox.setOrientation(QtCore.Qt.Horizontal)

        self.buttonBox.setStandardButtons(QtWidgets.QDialogButtonBox.Cancel|QtWidgets.QDialogButtonBox.Ok)

        self.buttonBox.setObjectName("buttonBox")

        self.horizontalLayout_2.addWidget(self.buttonBox)

        self.gridLayout.addLayout(self.horizontalLayout_2, 3, 0, 1, 1)

        self.tableWidgetSource.setSortingEnabled(True)

        self.tableWidgetTarget.setSortingEnabled(True)

        self.pushButtonExport.setText(_translate("OPCRelationDialog", "Export"))