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()