def parse_print_options(self, element):
self.ws.print_options = PrintOptions.from_tree(element)
def parse_print_options(self, element):
self.ws.print_options = PrintOptions.from_tree(element)
def parse_print_options(self, element):
self.ws.print_options = PrintOptions(**element.attrib)
def clickGenTestButton(self):
print("genTest")
if ((not self.RB_English.isChecked() and not self.RB_Korean.isChecked() and not self.RB_EK.isChecked())
or not self.editFilePath.text() or not self.editFrom.text() or not self.editTo.text() or not self.editNumOfWord.text()):
QMessageBox.about(self, "Warning", "모든 설정을 해야합니다.")
else:
self.outputFilePath = str(Path(self._filePath).parent)+'/'+Path(self._filePath).name.replace(' ', '_').split('.')[0] + self.editFrom.text() + '-' + self.editTo.text() +'_Test.xlsx'
print('outputFilePath : ', self.outputFilePath)
tempList = []
for i in range(int(self.editFrom.text()), int(self.editTo.text())+1):
for j in range(len(self.all_values[i])) :
tempList.append(self.all_values[i][j])
# tempList = self.all_values[int(self.editFrom.text()):int(self.editTo.text())]
testWordList = random.sample(tempList, int(self.editNumOfWord.text()))
#print("testWordList: " , testWordList)
font_styles = styles.Font(bold=True)
thin_border = Border(left=Side(style='thin'),
right=Side(style='thin'),
top=Side(style='thin'),
bottom=Side(style='thin'))
# writer = pd.ExcelWriter(self._filePath, engine='openpyxl')
# writer.book = load_workbook(self._filePath)
# # if truncate_sheet and sheet_name in writer.book.sheetnames:
# # idx = writer.book.sheetnames.index(sheet_name)
# # writer.book.remove(writer.book.worksheets[idx])
# # writer.book.create_sheet(sheet_name, idx)
# # copy existing sheets
# writer.sheets = {ws.title:ws for ws in writer.book.worksheets}
write_wb = Workbook()
# 이름이 있는 시트를 생성
# write_ws = writer.book.create_sheet('시험지')
write_ws = write_wb.create_sheet('시험지')
del_ws = write_wb['Sheet']
write_wb.remove(del_ws)
write_ws['B3'] = '이름 : '
# write_ws['B3'].font = write_ws['B3'].font.copy(bold=True)
write_ws['B4'] = '범위 : '
# write_ws['B4'].font = write_ws['B4'].font.copy(font_styles)
write_ws['C4'] = self.editFrom.text() + " - " + self.editTo.text()
# write_ws['C4'].font = write_ws['C4'].font.copy(font_styles)
write_ws.column_dimensions['A'].width = 5
write_ws.column_dimensions['B'].width = 5
write_ws.column_dimensions['C'].width = 30
write_ws.column_dimensions['D'].width = 30
write_ws.column_dimensions['E'].width = 5
write_ws.column_dimensions['F'].width = 30
write_ws.column_dimensions['G'].width = 30
write_ws.column_dimensions['H'].width = 5
numOfWord = int(self.editNumOfWord.text())
halfNumOfWord = int((numOfWord+1)/2)
write_ws.row_dimensions[5].height = 30
for row in write_ws.iter_rows(min_row=1, max_col=7, max_row=5):
for cell in row:
cell.font = font_styles
for row in write_ws.iter_rows(min_row=6, min_col=2, max_col=7, max_row=halfNumOfWord + 5):
for cell in row:
cell.border = thin_border
if self.RB_English.isChecked() :
for i in range(halfNumOfWord):
write_ws.row_dimensions[i+6].height = 30
write_ws.cell(i+6, 2, i+1)
write_ws.cell(i+6, 3, testWordList[i][0])
write_ws.cell(i+6, 5, (i+halfNumOfWord+1))
if i+halfNumOfWord < numOfWord :
write_ws.cell(i+6, 6, testWordList[i+halfNumOfWord][0])
elif self.RB_Korean.isChecked() :
for i in range(halfNumOfWord):
write_ws.row_dimensions[i+6].height = 30
write_ws.cell(i+6, 2, i+1)
write_ws.cell(i+6, 3, testWordList[i][1])
if i+halfNumOfWord < numOfWord :
write_ws.cell(i+6, 6, testWordList[i+halfNumOfWord][1])
elif self.RB_EK.isChecked() :
twThree = int((numOfWord / 30) * 23)
seven = numOfWord - twThree
print("twThree : ", twThree)
print("seven : ", seven)
for i in range(twThree, twThree + seven) :
testWordList[i].reverse()
random.shuffle(testWordList)
for i in range(halfNumOfWord):
write_ws.row_dimensions[i+6].height = 30
write_ws.cell(i+6, 2, i+1)
write_ws.cell(i+6, 3, testWordList[i][0])
write_ws.cell(i+6, 5, (i+halfNumOfWord+1))
if i+halfNumOfWord < numOfWord :
write_ws.cell(i+6, 6, testWordList[i+halfNumOfWord][0])
write_ws.row_dimensions[halfNumOfWord+7].height = 30
write_ws.row_dimensions[halfNumOfWord+7].height = 30
write_ws.page_setup = PrintPageSetup(worksheet=write_ws, scale=50)
write_ws.print_options = PrintOptions(gridLinesSet=True)
# 답안지 생성
# 이름이 있는 시트를 생성
# write_ws = writer.book.create_sheet('시험지')
write_ws = write_wb.create_sheet('답안지')
write_ws['B3'] = '이름 : '
# write_ws['B3'].font = write_ws['B3'].font.copy(bold=True)
write_ws['B4'] = '범위 : '
# write_ws['B4'].font = write_ws['B4'].font.copy(font_styles)
write_ws['C4'] = self.editFrom.text() + " - " + self.editTo.text()
# write_ws['C4'].font = write_ws['C4'].font.copy(font_styles)
write_ws.column_dimensions['A'].width = 5
write_ws.column_dimensions['B'].width = 5
write_ws.column_dimensions['C'].width = 30
write_ws.column_dimensions['D'].width = 30
write_ws.column_dimensions['E'].width = 5
write_ws.column_dimensions['F'].width = 30
write_ws.column_dimensions['G'].width = 30
write_ws.column_dimensions['H'].width = 5
numOfWord = int(self.editNumOfWord.text())
halfNumOfWord = int((numOfWord+1)/2)
write_ws.row_dimensions[5].height = 30
for row in write_ws.iter_rows(min_row=1, max_col=7, max_row=5):
for cell in row:
cell.font = font_styles
for row in write_ws.iter_rows(min_row=6, min_col=2, max_col=7, max_row=halfNumOfWord + 5):
for cell in row:
cell.border = thin_border
for i in range(halfNumOfWord):
write_ws.row_dimensions[i+6].height = 30
write_ws.cell(i+6, 2, i+1)
write_ws.cell(i+6, 3, testWordList[i][0])
write_ws.cell(i+6, 4, testWordList[i][1])
write_ws.cell(i+6, 5, (i+halfNumOfWord+1))
if i+halfNumOfWord < numOfWord :
write_ws.cell(i+6, 6, testWordList[i+halfNumOfWord][0])
write_ws.cell(i+6, 7, testWordList[i+halfNumOfWord][1])
write_ws.row_dimensions[halfNumOfWord+7].height = 30
write_ws.row_dimensions[halfNumOfWord+7].height = 30
write_ws.page_setup = PrintPageSetup(worksheet=write_ws, scale=50)
write_ws.print_options = PrintOptions(gridLinesSet=True)
try:
write_wb.save(self.outputFilePath)
QMessageBox.about(self, "시험지 생성 성공", self.outputFilePath+"\n시험지가 생성되었습니다.")
except PermissionError:
QMessageBox.about(self, "Warning", self.outputFilePath+"\n파일을 사용중이거나, 권한이 없습니다.")
def add_ws(line: int, df: DataFrame, wb: Workbook):
df = df.iloc[line]
ws = wb.create_sheet()
ws.column_dimensions["A"].width = 40
ws.column_dimensions["B"].width = 27
ws.page_setup.fitToHeight = 1
ws.page_setup.fitToWidth = 1
wsprops = ws.sheet_properties
wsprops.pageSetUpPr = PageSetupProperties(fitToPage=True, autoPageBreaks=False)
ws.print_options = PrintOptions(horizontalCentered=True, gridLines=True)
skola = df.loc["skola"].split("(")[0]
cell = ws["A1"]
cell.value = skola
cell.font = Font(size=36)
ws.append([""])
osoba = df.pop(
"Vaše jméno a příjmení (kontaktní osoba pro účely této objednávky)"
)
telefon = df.pop(
"Vaše telefonní číslo (kontaktní osoba pro účely této objednávky)"
)
email = df.pop("Váš e-mail (kontaktní osoba pro účely této objednávky)")
poznamka = df.pop("Jakékoliv další poznámky k objednávce či dopravě")
cislo = df.pop("Číslo popisné")
ulice = df.pop("Ulice")
psc = df.pop("PSČ")
obec = df.pop(
"Obec (název obce nebo části obce případně městská část nebo městský obvod)"
)
ws.append([ulice, cislo])
ws.append([psc, obec])
ws.append(["Kontaktní osoba: ", osoba])
ws.append(["Tel.: ", telefon])
ws.append(["E-mail: ", email])
ws.append([""])
cell = ws["A8"]
cell.value = poznamka
cell.alignment = Alignment(wrap_text=True)
ws.merge_cells('A8:C8')
rd = ws.row_dimensions[8]
rd.height = 30
ws.append([""])
ws.append(["Název", "Autor", "ks"])
celkem = 0
for n in range(len(df)):
try:
ks = int(df[n])
except ValueError:
continue
except TypeError:
continue
celkem += ks
name = df.axes[0][n]
if "; " in name:
name = name.split("; ")[1]
author, book = name.split(": ", 1)
else:
book = name
author = ""
ws.append([book, author, ks])
ws.append([""])
ws.append(["", "CELKEM KS", celkem])
def getValue(self, name=''):
_dict_ = {
1: "RTL00000",
2: "RTL0000",
3: "RTL000",
4: "RTL00",
5: "RTL0",
6: "RTL",
}
temp = int(name)
if len(name) in _dict_:
for i in range(1, 14):
for j in range(1, 6):
self.sheet.cell(
i, j
).value = _dict_[len(name)] + str(temp) + self.rutilink
temp = temp + 1
word_wrap_string = Alignment(wrapText=True,
horizontal="center",
vertical='center')
double_border_side = Side(border_style='dotted')
square_border = Border(top=double_border_side,
right=double_border_side,
bottom=double_border_side,
left=double_border_side)
self.sheet.page_margins = PageMargins(left=self.margins_tblr,
right=self.margins_tblr,
top=self.margins_tblr,
bottom=self.margins_tblr)
self.sheet.sheet_properties.pageSetUpPr.fitToPage = True
self.sheet.print_area = "A1:E13"
self.sheet.page_setup = PrintPageSetup(
worksheet=self.sheet,
orientation='portrait',
paperSize=self.sheet.PAPERSIZE_A4,
fitToHeight=1,
fitToWidth=1,
scale=100,
horizontalDpi=300,
verticalDpi=300)
self.sheet.print_options = PrintOptions(horizontalCentered=True,
verticalCentered=True)
for i in range(1, 6):
for j in range(1, 14):
self.sheet.cell(j, i).border = square_border
self.sheet.cell(j, i).font = self.fontType
self.sheet.cell(j, i).alignment = word_wrap_string
for cols in self.colsVal:
for row in self.rowVal:
self.sheet.column_dimensions[cols].width = self.value[1]
self.sheet.row_dimensions[row].height = self.value[0]
self.wb.save(QDir.homePath() + '/Desktop/' + self.filename)
self.wb.close()