def export_exam_points(data):
# объект
wb = Workbook()
ws = wb.active
data['courses'] = dict(data['courses'])
ws.title = data['group'].Name
row = 1
row = print_exam_headers(ws, data, row)
row += 1
row = print_exam_document_body(ws, data, row)
ws.print_area = 'A1:' + ws.cell(row=row-1, column=len(data['courses'])+3).coordinate
ws.page_setup = PrintPageSetup(worksheet=ws)
ws.page_setup.paperSize = '9'
ws.page_setup.orientation = ws.ORIENTATION_LANDSCAPE
ws.page_setup.fitToHeight = True
ws.page_setup.fitToWidth = True
ws.page_setup.fitToPage = True
ws.sheet_properties.pageSetUpPr.fitToPage = True
return wb
def exam_scores(exam_id):
# Читаем данные из базы данных
exam = Exam.objects.select_related('course').get(id=exam_id)
group = exam.course.group
exam_points = exam.exammarks_set.all().order_by('student__FIO')
semester = exam.course.discipline_detail.semester.name
if int(semester) % 2 == 1:
additional = int(semester) // 2
else:
additional = (int(semester) - 1) // 2
edu_period = EduPeriod.objects.get(begin_year__year=group.begin_year.year + additional)
# Открываем шаблон
app_dir = os.path.dirname(os.path.abspath(__file__))
template = os.path.join(app_dir, 'exam_scores.xlsx')
workbook = load_workbook(template)
ws = workbook.active
# Семестр
ws['A5'] = ws['L5'] = 'Семестр: {}, {} – {} уч.г.'.format(
semester,
edu_period.begin_year.year,
edu_period.end_year.year,
)
# Форма контроля
ws['A6'] = ws['L6'] = 'Форма контроля: {}, курс: {}, группа: {}'.format(
exam.get_controlType_display(),
group.year,
group.name,
)
# Дисциплина
ws['A7'] = ws['L7'] = 'Дисциплина: ' + exam.course.discipline_detail.discipline.name
# ФИО преподавателя
ws['A8'] = ws['L8'] = 'Фамилия, имя, отчество преподавателя: ' + exam.course.lecturer.FIO
# Дата
ws['A9'] = ws['L9'] = 'Дата проведения зачета/экзамена: {:%d.%m.%Y}'.format(exam.examDate)
# Таблица с баллами
summary = {ExamMarks.MARKS[0][0]: 0, ExamMarks.MARKS[1][0]: 0, ExamMarks.MARKS[2][0]: 0, ExamMarks.MARKS[3][0]: 0,
ExamMarks.MARKS[4][0]: 0, ExamMarks.MARKS[5][0]: 0, ExamMarks.MARKS[6][0]: 0, ExamMarks.MARKS[7][0]: 0,
ExamMarks.MARKS[8][0]: 0, ExamMarks.MARKS[9][0]: 0}
k = 0
for points in exam_points:
k += 1
ws.insert_rows(11 + k)
ws.row_dimensions[11 + k].height = 30
row = str(11 + k)
ws['A' + row] = ws['L' + row] = str(k)
ws['B' + row] = ws['M' + row] = points.student.FIO
ws['C' + row] = ws['N' + row] = points.student.student_id
ws['D' + row] = ws['O' + row] = points.inPoints + points.additional_points
ws['E' + row] = ws['P' + row] = points.examPoints
ws['F' + row] = ws['Q' + row] = points.total_points
ws['G' + row] = ws['R' + row] = points.get_mark_display()
ws['H' + row] = ws['S' + row] = points.mark_symbol
summary[points.mark]+=1
# Стиль для ячеек таблицы
solid_line = Side(style='thin', color='000000')
cell_style = NamedStyle(name='cell_style')
cell_style.alignment.horizontal = 'center'
cell_style.alignment.vertical = 'center'
cell_style.alignment.wrapText = Bool(True)
cell_style.border = Border(left=solid_line, right=solid_line, top=solid_line, bottom=solid_line)
cell_style.font = Font(name='Arial', size=9)
cell_style.number_format = '#.0'
# Применяем стили к таблице
for i in range(12, k + 12):
for j in range(1, 10):
ws.cell(row=i, column=j).style = ws.cell(row=i, column=j+11).style = cell_style
ws['C' + str(k + 14)] = ws['N' + str(k + 14)] = summary[6]
ws['C' + str(k + 15)] = ws['N' + str(k + 15)] = summary[7]
ws['C' + str(k + 16)] = ws['N' + str(k + 16)] = summary[8]
ws['C' + str(k + 17)] = ws['N' + str(k + 17)] = summary[5]
ws['C' + str(k + 18)] = ws['N' + str(k + 18)] = summary[4]
ws['C' + str(k + 19)] = ws['N' + str(k + 19)] = summary[3]
ws['C' + str(k + 20)] = ws['N' + str(k + 20)] = summary[2]
ws['C' + str(k + 21)] = ws['N' + str(k + 21)] = summary[0]
# Суммы баллов и буквенные эквиваленты оценки
for i in range(8):
row = str(14 + k + i)
ws.merge_cells('E' + row + ':F' + row)
ws.merge_cells('P' + row + ':Q' + row)
ws.merge_cells('G' + row + ':I' + row)
ws.merge_cells('R' + row + ':T' + row)
ws['E' + row].style = ws['F' + row].style = ws['G' + row].style = \
ws['H' + row].style = ws['I' + row].style = ws['P' + row].style = \
ws['Q' + row].style = ws['R' + row].style = ws['S' + row].style = \
ws['T' + row].style = cell_style
# Подпись директора
row = str(24 + k)
ws.merge_cells('A' + row + ':I' + row)
ws.merge_cells('L' + row + ':T' + row)
ws.print_area = 'A1:T' + str(row)
ws.page_setup = PrintPageSetup(worksheet=ws)
ws.page_setup.paperSize = '9'
ws.page_setup.orientation = ws.ORIENTATION_LANDSCAPE
ws.page_setup.fitToHeight = True
ws.page_setup.fitToWidth = True
ws.page_setup.fitToPage = True
ws.sheet_properties.pageSetUpPr.fitToPage = True
return workbook
def parse_page_setup(self, element):
self.ws.page_setup = PrintPageSetup.from_tree(element)
def parse_page_setup(self, element):
self.ws.page_setup = PrintPageSetup.from_tree(element)
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파일을 사용중이거나, 권한이 없습니다.")
#!/usr/bin/env python3
"""
Page setup
"""
from openpyxl import load_workbook
from openpyxl.worksheet.page import PrintPageSetup
if __name__ == "__main__":
# Create a workbook and sheets
filename = "Page_setup.xlsx"
wb = load_workbook(filename)
ws1 = wb["Sheet"]
# openpyxl.worksheet.page module
# Parameters from "Source code for openpyxl.worksheet.worksheet"
ws1.page_setup.paperSize = ws1.PAPERSIZE_A4
ws1.page_setup.orientation = ws1.ORIENTATION_LANDSCAPE
ws1.page_setup.fitToHeight = 0
ws1.page_setup.fitToWidth = 1
ws1.page_setup = PrintPageSetup(worksheet=None, orientation=ws1.ORIENTATION_PORTRAIT, paperSize=ws1.PAPERSIZE_LETTER,\
scale=None, fitToHeight=None, fitToWidth=None, firstPageNumber=None,\
useFirstPageNumber=None, paperHeight=None, paperWidth=None, pageOrder=None,\
usePrinterDefaults=None, blackAndWhite=None, draft=None, cellComments=None,\
errors=None, horizontalDpi=None, verticalDpi=None, copies=None, id=None)
wb.save(filename)
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()
def setup_print_page_setup(self, ws):
# fitToWidth isn't recognised on numbers. Hardcoding a scale is ghastly,
# but it works and will update if it's inappropriate
ws.page_setup = PrintPageSetup(orientation="landscape", scale=75)