Qt6 Python SQLServer 取引入力 QTableWidget
Qt6Creatorでプロジェクト inptorihikid を作成
inptorihikid.ui ui_inptorihikid を作成( pyside6-uic inptorihikid.ui -o ui_inptorihikid.py )
Qt6Creatorでプロジェクト kentorihiki を作成
kentorihikisaki.ui ui_kentorihikisaki を作成( pyside6-uic kentorihikisaki.ui -o ui_kentorihikisaki.py )
Qt6Creatorでプロジェクト inptorihiki を作成
inptorihiki.py を作成。 上記のui_inptorihikid、ui_kentorihikisaki をセット
# This Python file uses the following encoding: utf-8
import sys
from typing import Any, List
from datetime import date,datetime
from PySide6.QtCore import (QCoreApplication, QDate, QDateTime, QLocale,
QMetaObject, QObject, QPoint, QRect,
QSize, QTime, QUrl, Qt,QAbstractTableModel)
from PySide6.QtGui import (QBrush, QColor, QConicalGradient, QCursor,
QFont, QFontDatabase, QGradient, QIcon,
QImage, QKeySequence, QLinearGradient, QPainter,
QPalette, QPixmap, QRadialGradient, QTransform)
from PySide6.QtWidgets import (QApplication, QLineEdit, QMainWindow, QMenuBar,
QDialog, QPushButton, QSizePolicy, QStatusBar, QWidget,QMessageBox,QCheckBox,QHeaderView,QTableWidgetItem,QVBoxLayout,QHBoxLayout)
# Important:
# You need to run the following command to generate the ui_form.py file
# pyside6-uic form.ui -o ui_form.py, or
# pyside2-uic form.ui -o ui_form.py
from PyQt5 import QtCore, QtGui, QtWidgets
import ui_inptorihikid
from ui_kentorihikisaki import Ui_kentorihikisaki
import pymssql
server = '192.168.10.111'
username = 'sa'
password = 'xxxxxxxx'
database = 'kakei'
try:
conn = pymssql.connect( server , username , password, database )
except Exception as e:
print('接続エラー' + e.args)
else:
pass
class SimpleTableModel(QAbstractTableModel):
def __init__(self, list, headers = [], parent = None):
QAbstractTableModel.__init__(self, parent)
self.list = list
self.headers = headers
def rowCount(self, parent):
return len(self.list)
def columnCount(self, parent):
return len(self.list[0])
def flags(self, index):
return Qt.ItemIsEditable | Qt.ItemIsEnabled | Qt.ItemIsSelectable #Qt.ItemIsEditable | Qt.ItemIsEnabled | Qt.ItemIsSelectable
def data(self, index, role):
if role == Qt.EditRole:
row = index.row()
column = index.column()
return self.list[row][column]
elif role == Qt.DisplayRole:
row = index.row()
column = index.column()
value = self.list[row][column]
# Perform per-type checks and render accordingly.
if isinstance(value, datetime):
# Render time to YYY-MM-DD.
return value.strftime("%Y-%m-%d")
elif isinstance(value, int):
# Render int to 0 dp
return "%d" % value
elif isinstance(value, float):
# Render float to 2 dp
return "%.2f" % value
elif isinstance(value, str):
# Render strings with quotes
return "%s" % value
# Default (anything not captured above: e.g. int)
else:
return value
elif role == Qt.TextAlignmentRole:
row = index.row()
column = index.column()
value = self.list[row][column]
if isinstance(value, int) or isinstance(value, float) or column == 2 or column == 3 or column == 4 :#例外とした
# Align right, vertical middle.
return int(Qt.AlignRight) + int(Qt.AlignVCenter)#,Qt.AlignHCenter,Qt.AlignRight,Qt.AlignCenter,Qt.AlignTop,Qt.AlignVCenter,Qt.AlignBottom
else:
return int(Qt.AlignLeft) + int(Qt.AlignVCenter)
def setData(self, index, value, role= Qt.EditRole):
if role == Qt.EditRole:
row = index.row()
column = index.column()
self.list[row][column] = value
self.dataChanged.emit(index, index)
return True
return False
def headerData(self, section, orientation, role):
if role == Qt.DisplayRole:
if orientation == Qt.Horizontal:
if section < len(self.headers):
return self.headers[section]
else:
return "not implemented"
else:
return "%d" % (section + 1)
class inptorihiki(QMainWindow):
def __init__(self, parent=None):
super().__init__(parent)
self.ui = ui_inptorihikid.Ui_inptorihikid()
self.ui.setupUi(self)
self.ui.tableWidget.installEventFilter(self)
self.ui.lineEdit.returnPressed.connect(self.edit)
self.ui.lineEdit_2.returnPressed.connect(self.select)
self.ui.pushButton.clicked.connect(self.koshin)
self.ui.pushButton_2.clicked.connect(self.torikeshi)
self.ui.pushButton_3.clicked.connect(self.end)
self.ui.pushButton_4.clicked.connect(self.ctrl_c)
self.ui.pushButton_5.clicked.connect(self.ctrl_v)
self.ui.pushButton_6.clicked.connect(self.ctrl_s)
self.ui.pushButton_7.clicked.connect(self.sub_dlg)
self.ui.label.setStyleSheet("background-color: pink")
self.ui.label_2.setStyleSheet("background-color: violet")
self.ui.label_4.setStyleSheet("background-color: yellow")
self.ui.label_5.setStyleSheet("background-color: aqua")
self.ui.label_6.setStyleSheet("background-color: pink")
self.ui.pushButton.setStyleSheet("background-color: aqua")
self.ui.pushButton_2.setStyleSheet("background-color: yellow")
self.ui.pushButton_3.setStyleSheet("background-color: green")
self.ui.pushButton_4.setStyleSheet("background-color: yellow")
self.ui.pushButton_5.setStyleSheet("background-color: aqua")
self.ui.pushButton_6.setStyleSheet("background-color: pink")
self.ui.tableWidget.setRowCount(0)
self.ui.tableWidget.setColumnCount(13)
self.ui.tableWidget.setHorizontalHeaderItem(0, QTableWidgetItem(" 取引日 "))
self.ui.tableWidget.resizeColumnToContents(0)
self.ui.tableWidget.setHorizontalHeaderItem(1, QTableWidgetItem("連番"))
self.ui.tableWidget.resizeColumnToContents(1)
self.ui.tableWidget.setHorizontalHeaderItem(2, QTableWidgetItem(" 摘 要 "))
self.ui.tableWidget.resizeColumnToContents(2)
self.ui.tableWidget.setHorizontalHeaderItem(3, QTableWidgetItem(" 入金額 "))
self.ui.tableWidget.resizeColumnToContents(3)
self.ui.tableWidget.setHorizontalHeaderItem(4, QTableWidgetItem(" 出金額 "))
self.ui.tableWidget.resizeColumnToContents(4)
self.ui.tableWidget.setHorizontalHeaderItem(5, QTableWidgetItem("取引先"))
self.ui.tableWidget.resizeColumnToContents(5)
self.ui.tableWidget.setHorizontalHeaderItem(6, QTableWidgetItem(" 取引先名 "))
self.ui.tableWidget.resizeColumnToContents(6)
self.ui.tableWidget.setHorizontalHeaderItem(7, QTableWidgetItem("支払先"))
self.ui.tableWidget.resizeColumnToContents(7)
self.ui.tableWidget.setHorizontalHeaderItem(8, QTableWidgetItem(" 支払先名 "))
self.ui.tableWidget.resizeColumnToContents(8)
self.ui.tableWidget.setHorizontalHeaderItem(9, QTableWidgetItem(" 支払日 "))
self.ui.tableWidget.resizeColumnToContents(9)
self.ui.tableWidget.setHorizontalHeaderItem(10, QTableWidgetItem("区分"))
self.ui.tableWidget.resizeColumnToContents(10)
self.ui.tableWidget.setHorizontalHeaderItem(11, QTableWidgetItem(" 削除 "))
self.ui.tableWidget.resizeColumnToContents(11)
self.ui.tableWidget.setHorizontalHeaderItem(12, QTableWidgetItem("明細番号"))
self.ui.tableWidget.resizeColumnToContents(12)
self.ui.lineEdit.setFocus()
self.show()
def keyPressEvent(self, event):
super().keyPressEvent(event)
#tablewidget copy
if event.key() == Qt.Key_C and (event.modifiers() & Qt.ControlModifier):
self.ui.tableWidget.copied_cells = sorted(self.ui.tableWidget.selectedIndexes())
#tablewidget paste
elif event.key() == Qt.Key_V and (event.modifiers() & Qt.ControlModifier):
r = self.ui.tableWidget.currentRow() - self.ui.tableWidget.copied_cells[0].row()
c = self.ui.tableWidget.currentColumn() - self.ui.tableWidget.copied_cells[0].column()
for cell in self.ui.tableWidget.copied_cells:
item = QTableWidgetItem(cell.data())
if cell.column() == 2 or cell.column() == 6 or cell.column() == 8:
item.setTextAlignment(QtCore.Qt.AlignVCenter | QtCore.Qt.AlignLeft)
elif cell.column() == 10:
item.setTextAlignment(QtCore.Qt.AlignVCenter | QtCore.Qt.AlignHCenter)
else:
item.setTextAlignment(QtCore.Qt.AlignVCenter | QtCore.Qt.AlignRight)
self.ui.tableWidget.setItem(cell.row() + r, cell.column() + c, item)
#clipboard copy
elif event.key() == Qt.Key.Key_S and (event.modifiers() & Qt.KeyboardModifier.ControlModifier):
copied_cells = sorted(self.ui.tableWidget.selectedIndexes())
copy_text = ''
max_column = copied_cells[-1].column()
for c in copied_cells:
copy_text += self.ui.tableWidget.item(c.row(), c.column()).text()
if c.column() == max_column:
copy_text += '\n'
else:
copy_text += '\t'
QApplication.clipboard().setText(copy_text)
def ctrl_c(self):
#tablewidget copy
self.ui.tableWidget.copied_cells = sorted(self.ui.tableWidget.selectedIndexes())
def ctrl_v(self):
#tablewidget paste
r = self.ui.tableWidget.currentRow() - self.ui.tableWidget.copied_cells[0].row()
c = self.ui.tableWidget.currentColumn() - self.ui.tableWidget.copied_cells[0].column()
for cell in self.ui.tableWidget.copied_cells:
item = QTableWidgetItem(cell.data())
if cell.column() == 2 or cell.column() == 6 or cell.column() == 8:
item.setTextAlignment(QtCore.Qt.AlignVCenter | QtCore.Qt.AlignLeft)
elif cell.column() == 10:
item.setTextAlignment(QtCore.Qt.AlignVCenter | QtCore.Qt.AlignHCenter)
else:
item.setTextAlignment(QtCore.Qt.AlignVCenter | QtCore.Qt.AlignRight)
self.ui.tableWidget.setItem(cell.row() + r, cell.column() + c, item)
def ctrl_s(self):
#clipboard copy
copied_cells = sorted(self.ui.tableWidget.selectedIndexes())
copy_text = ''
max_column = copied_cells[-1].column()
for c in copied_cells:
copy_text += self.ui.tableWidget.item(c.row(), c.column()).text()
if c.column() == max_column:
copy_text += '\n'
else:
copy_text += '\t'
QApplication.clipboard().setText(copy_text)
def eventFilter(self, source, event):
if (event.type() == QtCore.QEvent.KeyPress and
event.key() in (QtCore.Qt.Key_Return, QtCore.Qt.Key_Enter)):
# ensure that the table receives the key event first
res = super().eventFilter(source, event)
current = self.ui.tableWidget.currentIndex()
if current.column() == 10:
nextIndex = current.sibling(current.row() + 1, 0)
else:
nextIndex = current.sibling(current.row() , current.column()+1)
if nextIndex.isValid():
self.ui.tableWidget.setCurrentIndex(nextIndex)
self.ui.tableWidget.edit(nextIndex)
return res
#return super().eventFilter(source, event)
if (event.type() == QtCore.QEvent.FocusOut):
# ensure that the table receives the key event first
res = super().eventFilter(source, event)
current = self.ui.tableWidget.currentIndex()
if current.column() == 1:
valstr=str(self.ui.tableWidget.item(current.row(),0).text())
x = self.hzkchk(valstr)
if x == 1:
nextIndex = current.sibling(current.row() , 0)
if nextIndex.isValid():
self.ui.tableWidget.setCurrentIndex(nextIndex)
self.ui.tableWidget.edit(nextIndex)
return res
if current.column() == 6:
try:
valint = int(self.ui.tableWidget.item(current.row(),5).text())
except:
valint = 0
else:
pass
x = self.readtorihikisaki(valint)
item6 = QTableWidgetItem(str(x[1]))
self.ui.tableWidget.setItem(current.row(), 6, item6)
self.ui.tableWidget.resizeColumnToContents(6)
nextIndex = current.sibling(current.row() , current.column()+1)
if nextIndex.isValid():
self.ui.tableWidget.setCurrentIndex(nextIndex)
self.ui.tableWidget.edit(nextIndex)
return res
if current.column() == 8:
try:
valint=int(self.ui.tableWidget.item(current.row(),7).text())
except:
valint = 0
else:
pass
x = self.readtorihikisaki(valint)
item8 = QTableWidgetItem(str(x[1]))
self.ui.tableWidget.setItem(current.row(), 8, item8)
self.ui.tableWidget.resizeColumnToContents(8)
nextIndex = current.sibling(current.row() , current.column()+1)
if nextIndex.isValid():
self.ui.tableWidget.setCurrentIndex(nextIndex)
self.ui.tableWidget.edit(nextIndex)
return res
if current.column() == 10:
valstr=str(self.ui.tableWidget.item(current.row(),9).text())
x = self.hzkchk(valstr)
if x == 1:
nextIndex = current.sibling(current.row() , 9)
if nextIndex.isValid():
self.ui.tableWidget.setCurrentIndex(nextIndex)
self.ui.tableWidget.edit(nextIndex)
return res
return super().eventFilter(source, event)
def sub_dlg(self):
dialog = Subkentorihikisaki()
if dialog.exec():
r = self.ui.tableWidget.currentRow()
c = self.ui.tableWidget.currentColumn()
item = QTableWidgetItem(dialog.dlg.lineEdit.text())
item.setTextAlignment(QtCore.Qt.AlignVCenter | QtCore.Qt.AlignRight)
self.ui.tableWidget.setItem(r,c, item)
self.ui.tableWidget.setFocus()
def hzkchk(self,hizuke):
if hizuke == "" or hizuke == "0":
err = 0
elif hizuke != "":
hizukestr = str(hizuke)
try:
dte = datetime.strptime(hizukestr, '%Y%m%d')
err = 0
except:
#QMessageBox.warning(self,'日付エラー','日付エラー')
err = 1
else:
pass
return err
def readtorihikisaki(self,code):
try:
cursor = conn.cursor(as_dict=True)
sql = "select * from 取引先マスター"
sql += " where 取引先コード = " + str(code)
cursor.execute(sql)
results = cursor.fetchall()
sonzai = 0
torihikisakimei = ""
for res in results:
torihikisakimei = str(res["取引先名"])
sonzai = 1
except Exception as e:
QMessageBox.warning(self,'エラー',sql + e.args)
else:
pass
cursor.close()
return (sonzai,torihikisakimei)
def end(self):
self.close()
def torikeshi(self):
self.ui.lineEdit.setText('')
self.ui.lineEdit_2.setText('')
self.ui.tableWidget.setRowCount(0)
self.ui.lineEdit.setFocus()
def edit(self):
x = self.hzkchk(self.ui.lineEdit.text())
if x == 1:
QMessageBox.warning(self,'エラー','開始日付エラー')
self.ui.lineEdit.setFocus
else:
self.ui.lineEdit.focusNextChild()
def select(self):
x = self.hzkchk(self.ui.lineEdit_2.text())
if x == 1:
QMessageBox.warning(self,'エラー','終了日付エラー')
self.ui.lineEdit_2.setFocus
return
else:
pass
codestt = self.ui.lineEdit.text()
codeend = self.ui.lineEdit_2.text()
try:
codesttint = int(codestt)
codeendint = int(codeend)
except:
QMessageBox.warning(self,'エラー','数値エラー')
self.ui.lineEdit.setFocus()
return
else:
if codesttint < 0 or codesttint > 99991231:
QMessageBox.warning(self,'エラー','開始日付エラー')
self.ui.lineEdit.setFocus()
return
if codeendint < 0 or codeendint > 99991231:
QMessageBox.warning(self,'エラー','終了日付エラー')
self.ui.lineEdit_2.setFocus()
return
if codesttint > codeendint:
QMessageBox.warning(self,'エラー','日付範囲エラー')
self.ui.lineEdit.setFocus()
return
try:
cursor = conn.cursor(as_dict=True)
sql = "select 取引明細.*,coalesce(T.取引先名,'') as 取引先名称,coalesce(S.取引先名,'') as 支払先名称 from 取引明細"
sql += " left outer join 取引先マスター as T on 取引明細.取引先=T.取引先コード"
sql += " left outer join 取引先マスター as S on 取引明細.支払先=S.取引先コード"
sql += " where 取引日 >= " + str(codesttint)
sql += " and 取引日 <= " + str(codeendint)
sql += " order by 取引日,連番"
cursor.execute(sql)
results = cursor.fetchall()
self.ui.tableWidget.setRowCount(cursor.rowcount+20)
row = 0
for res in results:
item0 = QTableWidgetItem(str(res["取引日"]))
item1 = QTableWidgetItem(str(res["連番"]))
item2 = QTableWidgetItem(str(res["摘要"]))
item3 = QTableWidgetItem(str(res["入金額"]))
item4 = QTableWidgetItem(str(res["出金額"]))
item5 = QTableWidgetItem(str(res["取引先"]))
item6 = QTableWidgetItem(str(res["取引先名称"]))
item7 = QTableWidgetItem(str(res["支払先"]))
item8 = QTableWidgetItem(str(res["支払先名称"]))
item9 = QTableWidgetItem(str(res["支払日"]))
item10 = QTableWidgetItem(str(res["取引区分"]))
widget = QWidget()
checkbox = QCheckBox("check")
widget.checkState = checkbox.checkState()
#checkbox.setCheckState(Qt.Checked)
playout = QHBoxLayout(widget)
playout.addWidget(checkbox)
playout.setAlignment(Qt.AlignCenter)
playout.setContentsMargins(0,0,0,0)
widget.setLayout(playout)
item12 = QTableWidgetItem(str(res["明細番号"]))
#item6.setFlags(Qt.ItemIsEnabled | Qt.ItemIsSelectable)
#item8.setFlags(Qt.ItemIsEnabled | Qt.ItemIsSelectable)
item12.setFlags(Qt.ItemIsEnabled | Qt.ItemIsSelectable)
#v AlignVCenter:80 Top:20 Bottom:40 Baseline:100
#h AlignHCenter:4 Left:1 Right:2 Justify:8
item0.setTextAlignment(QtCore.Qt.AlignVCenter | QtCore.Qt.AlignRight)
item1.setTextAlignment(QtCore.Qt.AlignVCenter | QtCore.Qt.AlignRight)
item2.setTextAlignment(QtCore.Qt.AlignVCenter | QtCore.Qt.AlignLeft)
item3.setTextAlignment(QtCore.Qt.AlignVCenter | QtCore.Qt.AlignRight)
item4.setTextAlignment(QtCore.Qt.AlignVCenter | QtCore.Qt.AlignRight)
item5.setTextAlignment(QtCore.Qt.AlignVCenter | QtCore.Qt.AlignRight)
item6.setTextAlignment(QtCore.Qt.AlignVCenter | QtCore.Qt.AlignLeft)
item7.setTextAlignment(QtCore.Qt.AlignVCenter | QtCore.Qt.AlignRight)
item8.setTextAlignment(QtCore.Qt.AlignVCenter | QtCore.Qt.AlignLeft)
item9.setTextAlignment(QtCore.Qt.AlignVCenter | QtCore.Qt.AlignRight)
item10.setTextAlignment(QtCore.Qt.AlignVCenter | QtCore.Qt.AlignHCenter)
item12.setTextAlignment(QtCore.Qt.AlignVCenter | QtCore.Qt.AlignRight)
self.ui.tableWidget.setItem(row, 0,item0)
self.ui.tableWidget.resizeColumnToContents(0)
self.ui.tableWidget.setItem(row, 1, item1)
self.ui.tableWidget.resizeColumnToContents(1)
self.ui.tableWidget.setItem(row, 2, item2)
self.ui.tableWidget.resizeColumnToContents(2)
self.ui.tableWidget.setItem(row, 3, item3)
self.ui.tableWidget.resizeColumnToContents(3)
self.ui.tableWidget.setItem(row, 4, item4)
self.ui.tableWidget.resizeColumnToContents(4)
self.ui.tableWidget.setItem(row, 5, item5)
self.ui.tableWidget.resizeColumnToContents(5)
self.ui.tableWidget.setItem(row, 6, item6)
self.ui.tableWidget.resizeColumnToContents(6)
self.ui.tableWidget.setItem(row, 7, item7)
self.ui.tableWidget.resizeColumnToContents(7)
self.ui.tableWidget.setItem(row, 8, item8)
self.ui.tableWidget.resizeColumnToContents(8)
self.ui.tableWidget.setItem(row, 9, item9)
self.ui.tableWidget.resizeColumnToContents(9)
self.ui.tableWidget.setItem(row, 10, item10)
self.ui.tableWidget.resizeColumnToContents(10)
self.ui.tableWidget.setCellWidget(row, 11, checkbox)
#self.ui.tableWidget.resizeColumnToContents(11)
self.ui.tableWidget.setItem(row, 12, item12)
self.ui.tableWidget.resizeColumnToContents(12)
row += 1
for i in range(20):
item0 = QTableWidgetItem("")
item1 = QTableWidgetItem("")
item2 = QTableWidgetItem("")
item3 = QTableWidgetItem("")
item4 = QTableWidgetItem("")
item5 = QTableWidgetItem("")
item6 = QTableWidgetItem("")
item7 = QTableWidgetItem("")
item8 = QTableWidgetItem("")
item9 = QTableWidgetItem("")
item10 = QTableWidgetItem("")
widget = QWidget()
checkbox = QCheckBox()
widget.checkState = checkbox.checkState
#checkbox.setCheckState(Qt.Checked)
playout = QHBoxLayout(widget)
playout.addWidget(checkbox)
playout.setAlignment(Qt.AlignCenter)
playout.setContentsMargins(0,0,0,0)
widget.setLayout(playout)
item12 = QTableWidgetItem("")
#item6.setFlags(Qt.ItemIsEnabled | Qt.ItemIsSelectable)
#item8.setFlags(Qt.ItemIsEnabled | Qt.ItemIsSelectable)
item12.setFlags(Qt.ItemIsEnabled | Qt.ItemIsSelectable)
#v AlignVCenter:80 Top:20 Bottom:40 Baseline:100
#h AlignHCenter:4 Left:1 Right:2 Justify:8
item0.setTextAlignment(QtCore.Qt.AlignVCenter | QtCore.Qt.AlignRight)
item1.setTextAlignment(QtCore.Qt.AlignVCenter | QtCore.Qt.AlignRight)
item2.setTextAlignment(QtCore.Qt.AlignVCenter | QtCore.Qt.AlignLeft)
item3.setTextAlignment(QtCore.Qt.AlignVCenter | QtCore.Qt.AlignRight)
item4.setTextAlignment(QtCore.Qt.AlignVCenter | QtCore.Qt.AlignRight)
item5.setTextAlignment(QtCore.Qt.AlignVCenter | QtCore.Qt.AlignRight)
item6.setTextAlignment(QtCore.Qt.AlignVCenter | QtCore.Qt.AlignLeft)
item7.setTextAlignment(QtCore.Qt.AlignVCenter | QtCore.Qt.AlignRight)
item8.setTextAlignment(QtCore.Qt.AlignVCenter | QtCore.Qt.AlignLeft)
item9.setTextAlignment(QtCore.Qt.AlignVCenter | QtCore.Qt.AlignRight)
item10.setTextAlignment(QtCore.Qt.AlignVCenter | QtCore.Qt.AlignHCenter)
item12.setTextAlignment(QtCore.Qt.AlignVCenter | QtCore.Qt.AlignRight)
self.ui.tableWidget.setItem(row, 0,item0)
self.ui.tableWidget.resizeColumnToContents(0)
self.ui.tableWidget.setItem(row, 1, item1)
self.ui.tableWidget.resizeColumnToContents(1)
self.ui.tableWidget.setItem(row, 2, item2)
self.ui.tableWidget.resizeColumnToContents(2)
self.ui.tableWidget.setItem(row, 3, item3)
self.ui.tableWidget.resizeColumnToContents(3)
self.ui.tableWidget.setItem(row, 4, item4)
self.ui.tableWidget.resizeColumnToContents(4)
self.ui.tableWidget.setItem(row, 5, item5)
self.ui.tableWidget.resizeColumnToContents(5)
self.ui.tableWidget.setItem(row, 6, item6)
self.ui.tableWidget.resizeColumnToContents(6)
self.ui.tableWidget.setItem(row, 7, item7)
self.ui.tableWidget.resizeColumnToContents(7)
self.ui.tableWidget.setItem(row, 8, item8)
self.ui.tableWidget.resizeColumnToContents(8)
self.ui.tableWidget.setItem(row, 9, item9)
self.ui.tableWidget.resizeColumnToContents(9)
self.ui.tableWidget.setItem(row, 10, item10)
self.ui.tableWidget.resizeColumnToContents(10)
self.ui.tableWidget.setCellWidget(row, 11, checkbox)
#self.ui.tableWidget.resizeColumnToContents(11)
self.ui.tableWidget.setItem(row, 12, item12)
self.ui.tableWidget.resizeColumnToContents(12)
row += 1
self.ui.tableWidget.setFocus()
except Exception as e:
QMessageBox.warning(self,'エラー',sql + e.args)
else:
pass
cursor.close()
return
def koshin(self):
row = self.ui.tableWidget.rowCount()
column = self.ui.tableWidget.columnCount()
results = []
for r in range(row):
A = []
for c in range(column):
if c != 11:
it = self.ui.tableWidget.item(r,c)
text = ""
if it:
text = it.text()
else:
print("!")
else:
if self.ui.tableWidget.cellWidget(r,c).checkState() == QtCore.Qt.Checked:
text = "true"
else:
text = "false"
A.append(text)
results.append(A,)
row=0
for res in results:
row += 1
if res[0] != "":
#print(res[0],res[1],res[2],res[3],res[4],res[5], res[6],res[7],res[8],res[9],res[10],res[11],res[12] )
f0 = res[0]
f1 = res[1]
f2 = res[2]
f3 = res[3]
f4 = res[4]
f5 = res[5]
f6 = res[6]
f7 = res[7]
f8 = res[8]
f9 = res[9]
f10 = res[10]
f11 = res[11]
f12 = res[12]
x = self.hzkchk(f0)
if x == 1:
QMessageBox.warning(self,'エラー','日付エラー' + str(row) + "行目")
return
if f1 == "":
f1 = "0"
if f3 == "":
f3 = "0"
if f4 == "":
f4 = "0"
if f5 == "":
f5 = "0"
if f7 == "":
f7 = "0"
if f9 == "":
f9 = "0"
if f10 == "":
f10 = "0"
if f12 == "":
f12 = "0"
try:
f0int = int(f0)
f1int = int(f1)
f3int = int(f3)
f4int = int(f4)
f5int = int(f5)
f7int = int(f7)
f9int = int(f9)
f10int = int(f10)
f12int = int(f12)
except:
QMessageBox.warning(self,'エラー','数値エラー' + str(row) + "行目")
else:
pass
row=0
for res in results:
row += 1
if res[0] != "":
#print(res[0],res[1],res[2],res[3],res[4],res[5], res[6],res[7],res[8],res[9],res[10],res[11],res[12] )
f0 = res[0]
f1 = res[1]
f2 = res[2]
f3 = res[3]
f4 = res[4]
f5 = res[5]
f6 = res[6]
f7 = res[7]
f8 = res[8]
f9 = res[9]
f10 = res[10]
f11 = res[11]
f12 = res[12]
x = self.hzkchk(f0)
if x == 1:
QMessageBox.warning(self,'エラー','日付エラー' + str(row) + "行目")
return
if f1 == "":
f1 = "0"
if f3 == "":
f3 = "0"
if f4 == "":
f4 = "0"
if f5 == "":
f5 = "0"
if f7 == "":
f7 = "0"
if f9 == "":
f9 = "0"
if f10 == "":
f10 = "0"
if f12 == "":
f12 = "0"
try:
f0int = int(f0)
f1int = int(f1)
f3int = int(f3)
f4int = int(f4)
f5int = int(f5)
f7int = int(f7)
f9int = int(f9)
f10int = int(f10)
f12int = int(f12)
except:
QMessageBox.warning(self,'エラー','数値エラー' + str(row) + "行目")
else:
pass
if f12int == 0:
#新規
sqlstr = " insert into 取引明細 "
sqlstr += " (明細番号,取引日,連番,摘要,入金額,出金額,取引先,支払先,支払日,取引区分) "
sqlstr += " values( " + "(select coalesce(max(明細番号),0)+1 from 取引明細)" + ","
sqlstr += f0 + "," + f1 + ",'" + f2 + "'," + f3 + "," + f4 + "," + f5 + ","
sqlstr += f7 + "," + f9 + "," + f10 + ")"
else:
#既存
if f11 == "false":
sqlstr = " update 取引明細 set "
sqlstr += " 取引日=" + f0
sqlstr += ",連番=" + f1
sqlstr += ",摘要='" + f2 + "'"
sqlstr += ",入金額=" + f3
sqlstr += ",出金額=" + f4
sqlstr += ",取引先=" + f5
sqlstr += ",支払先=" + f7
sqlstr += ",支払日=" + f9
sqlstr += ",取引区分=" + f10
sqlstr += " where 明細番号=" + f12
else:
sqlstr = " delete from 取引明細 "
sqlstr += " where 明細番号=" + f12
try:
cursor = conn.cursor(as_dict=True)
cursor.execute(sqlstr)
except:
QMessageBox.warning(self,'エラー',sqlstr)
return
else:
pass
conn.commit()
cursor.close()
QMessageBox.warning(self,'結 果','更新しました。')
self.torikeshi()
self.ui.lineEdit.setFocus()
class Subkentorihikisaki(QDialog):
def __init__(self, parent=None):
super().__init__(parent)
self.dlg = Ui_kentorihikisaki()
self.dlg.setupUi(self)
self.view()
self.dlg.tableView.clicked.connect(self.valset)
def valset(self):
selection = self.dlg.tableView.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()
self.dlg.lineEdit.setText(str(table[0][0]))
def view(self):
server = '192.168.10.111'
username = 'sa'
password = 'xxxxxxxx'
database = 'kakei'
try:
conn = pymssql.connect( server , username , password, database )
cursor = conn.cursor()
sqlstr = " select * from 取引先マスター "
sqlstr += " order by 取引先コード"
cursor.execute(sqlstr)
results = cursor.fetchall()
# データを表示
coldata = ['コード','取引先名','取引先区分']
rowdata = []
row = 0
for res in results:
i=0
rowone = []
while i <= 2:
try:
strint = int(str(res[i]))
strfloat = float(str(res[i]))
if strint == strfloat:
rowone.append(strint)
else:
rowone.append(strfloat)
except:
rowone.append(str(res[i]))
else:
pass
i += 1
row += 1
rowdata.append(rowone)
model = SimpleTableModel(rowdata, coldata)
self.dlg.tableView.setModel(model) # create model and set
self.dlg.tableView.horizontalHeader().setSectionResizeMode(QHeaderView.ResizeToContents) #列幅の自動調整 0:Interactive 1:Stretch 2:Fixed 3:ResizeToContents
cursor.close()
conn.close()
except Exception as e:
QMessageBox.warning(self,'エラー','接続エラー' + e.args)
else:
pass
if __name__ == "__main__":
app = QApplication(sys.argv)
w = inptorihiki()
w.show()
#sys.exit(app.exec_())
sys.exit(app.exec())
実行画面
検索ボタン押下、選択後OKボタン押下で値がセットされます。
登録ボタンで取引明細へ追加、更新、削除を行います。明細番号がある場合が、更新か削除の対象になります。削除のチェックボックスへチェックすると削除されます。
明細番号がない場合は、新規として追加されます。新規分は、２０行用意しています。不足の場合は、登録後再度日付指定します。２０行追加できます。
取引先と支払先は、コード入力で取引先マスターより名称を表示します。検索ボタンで検索画面からコードを取得できます。セル移動は、Ｅｎｔｅｒキーを使用します。
テーブル内の選択範囲のコピーは、Ctrl + c テーブルへの貼り付けは、Ctrl + v クリップボードへのコピーは、Ctrl + s で行います。右のボタンも同様。
