def set_cells_format(number_rows, worker): #Покраска зеленым print "Применение форматирования." worker.ReportProgress(94, u"Применение форматирования.") cell_properties = sdk.CellProperties() cell_properties.backgroundColor = sdk.ColorRGBA(146, 208, 80, 1) #cell_properties.verticalAlignment = sdk.VerticalAlignment_Center # Задаем диапозон B4:S - конечная строка # Применение форматирования B4:S - конечная строка worker.ReportProgress(95, u"Применение форматирования для диапозона B4:W"+ number_rows) cell_range = table_output_xlsx_11.getCellRange("B4:W" + number_rows) cell_range.setCellProperties(cell_properties) # Задаем вертикальное центрирование # для диапозона А4-А - конечная строка. worker.ReportProgress(96, u"Применение форматирования для диапозона А4:А" + number_rows) cell_properties_aligment = sdk.CellProperties() cell_properties_aligment.backgroundColor = sdk.ColorRGBA(0, 0, 0, 0) cell_properties_aligment.verticalAlignment = sdk.VerticalAlignment_Center cell_range_aligment = table_output_xlsx_11.getCellRange("A4:A" + number_rows) for c in cell_range_aligment: c.setCellProperties(cell_properties_aligment) #cell_range_aligment.setCellProperties(cell_properties_aligment) Баг-репорт # Формат Date для столбца E4 worker.ReportProgress(97, u"Формат Date для столбца E4") cell_range_date = table_output_xlsx_11.getCellRange("F4:F" + number_rows) for c in cell_range_date: c.setFormat(sdk.CellFormat_Date)
def error_data(data_error, worker): # Раскрашивает в выходном файле строки с ошибками worker.ReportProgress(98, u"Помечаем ошибки") for i in data_error: cell_properties = sdk.CellProperties() cell_properties.backgroundColor = sdk.ColorRGBA(255, 0, 0, 1) cell_properties.verticalAlignment = sdk.VerticalAlignment_Center cell_range = table_output_xlsx_11.getCellRange("B" + str(i+3)+":W"+str(i+3)) cell_range.setCellProperties(cell_properties)
def extract_txt_doc(path,folderName,mydirs_,i,lena): filename=os.path.basename(path) document_xls_input = application.loadDocument(path.encode('utf-8')) table_input = document_xls_input.getBlocks().getTable(0) last_name = table_input.getCell('C6').getRawValue() first_name = table_input.getCell('C8').getRawValue() middle_name = table_input.getCell('C10').getRawValue() date_birth = table_input.getCell('C12').getFormattedValue() country = table_input.getCell('C14').getRawValue() district = table_input.getCell('C15').getRawValue() post_index = table_input.getCell('C16').getRawValue() region = table_input.getCell('C18').getRawValue() city = table_input.getCell('C20').getRawValue() #address = table_input.getCell('C22').getRawValue() school = table_input.getCell('C22').getRawValue() school_address = table_input.getCell('C24').getRawValue() exp = table_input.getCell('C26').getRawValue() cert_1 = table_input.getCell('C27').getRawValue() cert_2 = table_input.getCell('C28').getRawValue() cert_3 = table_input.getCell('C29').getRawValue() phone = table_input.getCell('C30').getRawValue() email = table_input.getCell('C32').getRawValue() parent_fio = table_input.getCell('C34').getRawValue() parent_email = table_input.getCell('C38').getRawValue() parent_phone = table_input.getCell('C40').getRawValue() parent_work = table_input.getCell('C42').getRawValue() #Раскрашиваем ячейки в анкетах с ошибками dict_cells = { 'C6': last_name, 'C8': first_name, 'C10': middle_name, 'C12': date_birth, 'C14': country, 'C15': district, 'C16': post_index, 'C18': region, 'C20': city, 'C22': school, 'C24': school_address, 'C26': exp, 'C27': cert_1, 'C28': cert_2, 'C29': cert_3, 'C30': phone, 'C32': email, 'C34': parent_fio, 'C38': parent_email, 'C40': parent_phone, 'C42': parent_work, } cell_properties = table_input.getCell('C6').getCellProperties() cell_properties.backgroundColor = sdk.ColorRGBA(255, 0, 0, 1) is_changed = False is_first_error = True log = [] for key, val in dict_cells.iteritems(): if not val: # Помечаем ошибки красным цветом в входном файле table_input.getCell(key).setCellProperties(cell_properties) log = log_file(filename, key, is_first_error, log) is_changed = True is_first_error = False log_file_rec(log,folderName) if is_changed: #document_xls_input.saveAs((mydirs_[0]+"\\"+filename).encode('utf-8')) document_xls_input.saveAs((mydirs_[0] + "\\"+"№_"+str(lena + i)+"_"+last_name+"_"+first_name+"_Ошибка.xlsx").encode('utf-8')) os.remove(path) else: os.rename(path, os.path.dirname(path) + "\\"+"№_"+str(lena + i)+"_"+last_name+"_"+first_name+"_Обработан.xlsx") full_row_lst = [ last_name, first_name, middle_name, date_birth, country, district, post_index, region, city, school, school_address, exp, cert_1 + ", " + cert_2 + ", " +cert_3, phone, email, parent_fio, parent_email, parent_phone, parent_work] return full_row_lst
def extract_txt_doc(filename, path, folderName): document_xls_input = application.loadDocument(path) table_input = document_xls_input.getBlocks().getTable(0) last_name = table_input.getCell('C6').getFormattedValue() first_name = table_input.getCell('C8').getFormattedValue() middle_name = table_input.getCell('C10').getFormattedValue() date_birth = table_input.getCell('C12').getFormattedValue() district = table_input.getCell('C14').getFormattedValue() post_index = table_input.getCell('C16').getFormattedValue() region = table_input.getCell('C18').getFormattedValue() city = table_input.getCell('C20').getFormattedValue() #address = table_input.getCell('C22').getFormattedValue() school = table_input.getCell('C22').getFormattedValue() school_address = table_input.getCell('C24').getFormattedValue() exp = table_input.getCell('C26').getFormattedValue() cert = table_input.getCell('C28').getFormattedValue() phone = table_input.getCell('C30').getFormattedValue() email = table_input.getCell('C32').getFormattedValue() parent_fio = table_input.getCell('C34').getFormattedValue() parent_email = table_input.getCell('C38').getFormattedValue() parent_phone = table_input.getCell('C40').getFormattedValue() parent_work = table_input.getCell('C42').getFormattedValue() #Раскрашиваем ячейки в анкетах с ошибками dict_cells = { 'C6': last_name, 'C8': first_name, 'C10': middle_name, 'C12': date_birth, 'C14': district, 'C16': post_index, 'C18': region, 'C20': city, 'C22': school, 'C24': school_address, 'C26': exp, 'C28': cert, 'C30': phone, 'C32': email, 'C34': parent_fio, 'C38': parent_email, 'C40': parent_phone, 'C42': parent_work, } cell_properties = table_input.getCell('C6').getCellProperties() cell_properties.backgroundColor = sdk.ColorRGBA(255, 0, 0, 1) is_changed = False is_first_error = True log = [] for key, val in dict_cells.iteritems(): if not val: # Помечаем ошибки красным цветом в входном файле table_input.getCell(key).setCellProperties(cell_properties) log = log_file(filename, key, is_first_error, log) is_changed = True is_first_error = False log_file_rec(log, folderName) if is_changed: document_xls_input.saveAs( (folderName + u"\\Ошибки\\" + filename).encode('utf-8')) full_row_lst = [ last_name, first_name, middle_name, date_birth, district, post_index, region, city, school, school_address, exp, cert, phone, email, parent_fio, parent_phone, parent_email, parent_work ] return full_row_lst
def extract_txt_doc(path, folderName, mydirs_, lena, log, workstatuspanel): try: document_xlsinput = application.loadDocument(path.encode('utf-8')) except Exception as e: raise Exception(u"Невозможно открыть документ: " + os.path.basename(path)) table_input = document_xlsinput.getBlocks().getTable(0) last_name = table_input.getCell('C6').getRawValue() first_name = table_input.getCell('C8').getRawValue() middle_name = table_input.getCell('C10').getRawValue() date_birth = table_input.getCell('C12').getFormattedValue() country = table_input.getCell('C14').getRawValue() district = table_input.getCell('C15').getRawValue() post_index = table_input.getCell('C16').getRawValue() region = table_input.getCell('C18').getRawValue() city = table_input.getCell('C20').getRawValue() # address = table_input.getCell('C22').getRawValue() school = table_input.getCell('C22').getRawValue() school_address = table_input.getCell('C24').getRawValue() exp = table_input.getCell('C26').getRawValue() cert_1 = table_input.getCell('C28').getRawValue() cert_2 = table_input.getCell('C29').getRawValue() cert_3 = table_input.getCell('C30').getRawValue() cert_4 = table_input.getCell('C31').getRawValue() cert_5 = table_input.getCell('C32').getRawValue() cert = [cert_1, cert_2, cert_3, cert_4, cert_5] ball_list = [3, 5, 10, 5, 7] cert = [ re.sub(u'[Дд][Аа]', 'Да', i.decode('utf8')).encode('utf8') for i in cert ] cert = [ re.sub(u'[Нн][Ее][Тт]', 'Нет', i.decode('utf8')).encode('utf8') for i in cert ] diplom_list = [ "Диплом 1 степени", "Диплом 2 степени", "Диплом 3 степени", "Диплом Почтового комиссара", 'Диплом "Наставника"' ] cert_list = [ False if a == '' else a if a == "Нет" else b for a, b in zip(cert, diplom_list) ] #Для того чтобы написать в сводном # файле название диплома или нет certflag = True da_count = [i for i in cert if re.search("Да", i)] if len(da_count) > 3: certflag = False cert_ball = [(a == 'Да') * b for a, b in zip(cert, ball_list)] phone = table_input.getCell('C33').getRawValue() email = table_input.getCell('C35').getRawValue() parent_fio = table_input.getCell('C37').getRawValue() parent_phone = table_input.getCell('C41').getRawValue() parent_email = table_input.getCell('C43').getRawValue() parent_work = table_input.getCell('C45').getRawValue() regex_error = '№_\d+.*_Ошибка\.xlsx' regex_date = '^(0[1-9]|[12][0-9]|3[01])[- /.](0[1-9]|1[012])[- /.](19|20)\d\d$' # dd-mm-yyyy filename = str("№_" + str(lena) + "_" + last_name + "_" + first_name) # Раскрашиваем ячейки в анкетах с ошибками if not re.search(regex_date, str(date_birth)): date_birth = False dict_cells = { 'C6': last_name, 'C8': first_name, 'C10': middle_name, 'C12': date_birth, 'C14': country, 'C15': district, 'C16': post_index, 'C18': region, 'C20': city, 'C22': school, 'C24': school_address, 'C26': exp, 'C27': certflag, 'C28': cert_1, 'C29': cert_2, 'C30': cert_3, 'C31': cert_4, 'C32': cert_5, 'C33': phone, 'C35': email, 'C37': parent_fio, 'C41': parent_phone, 'C43': parent_email, 'C45': parent_work, } cell_properties = table_input.getCell('C6').getCellProperties() cell_properties.backgroundColor = sdk.ColorRGBA(255, 0, 0, 1) is_changed = False is_first_error = True error_count = 0 filename_err = str(filename + "_Ошибка.xlsx") for key, val in dict_cells.iteritems(): if not val: # Помечаем ошибки красным цветом в входном файле table_input.getCell(key).setCellProperties(cell_properties) log = log_file(filename_err, key, is_first_error, log) is_changed = True is_first_error = False error_count += 1 if is_changed: # Ошибка # document_xlsinput.saveAs((mydirs_[0]+"\\"+filename).encode('utf-8')) if not (os.path.exists(mydirs_[0])): try: os.mkdir(mydirs_[0], 0o777) # Папка Ошибки workstatuspanel.Text = u'Создана папка: ' + os.path.abspath( mydirs_[0]) except Exception: workstatuspanel.Text = u'Неудалось создать папку:' + os.path.basename( mydirs_[0]) raise Exception("Неудалось создать папку:" + os.path.basename(mydirs_[0])) try: document_xlsinput.saveAs( (mydirs_[0] + "\\" + filename_err).encode('utf-8')) workstatuspanel.Text = u'Сохраняю файл с ошибкой:' + filename_err except Exception as e: raise Exception(u"Невозможно сохранить документ: " + (mydirs_[0] + "\\" + filename_err)) os.remove(path) else: filename_new = str(filename + "_Обработан.xlsx") if re.search(regex_error, str(os.path.basename(path))): lena = int(os.path.basename(path).split("_")[1]) filename_new = str(os.path.basename(path)).replace( "Ошибка", "Обработан") os.rename(path, os.path.dirname(path) + "\\" + filename_new) full_row_lst = [ last_name, first_name, middle_name, date_birth, country, district, post_index, region, city, school, school_address, exp, str(cert_list[0]) + ", " + str(cert_list[1]) + ", " + str(cert_list[2]) + ", " + str(cert_list[3]) + ", " + str(cert_list[4]), phone, email, parent_fio, parent_phone, parent_email, parent_work, cert_ball, certflag, lena, error_count, ] return full_row_lst
# -*- coding: utf-8 -*- import os, time, itertools from datetime import datetime from MyOfficeSDKDocumentAPI import DocumentAPI as sdk from string import ascii_uppercase global application application = sdk.Application() cell_properties_win = sdk.CellProperties() cell_properties_win.backgroundColor = sdk.ColorRGBA(193, 242, 17, 255) cell_properties_lose = sdk.CellProperties() cell_properties_lose.backgroundColor = sdk.ColorRGBA(108, 122, 137, 255) def message(table_input, i, template, mydirs_): document = application.loadDocument(template) bookmarks = document.getBookmarks() last_name = table_input.getCell("B" + str(i)).getFormattedValue() first_name = table_input.getCell("C" + str(i)).getFormattedValue() bookmarks.getBookmarkRange('name').replaceText(last_name + ' ' + first_name) f_path = (mydirs_[17] + "\\" + '№' + str(i - 3) + ' ' + last_name + ' ' + first_name + ' ' + os.path.basename(template)).encode('utf-8') document.saveAs((f_path)) def iter_all_strings(): for size in itertools.count(1): for s in itertools.product(ascii_uppercase, repeat=size): yield "".join(s)
mydir9 = path_dirname_ + "\\" + u"Артек_Программа _Дверь синего цвета_ (жюри 2)_исправленный.xlsx" mydir10 = path_dirname_ + "\\" + u"Артек_Программа _Дверь синего цвета_ (жюри 3)_исправленный.xlsx" mydir11 = mydir + "\\" + os.path.basename(mydir7) mydir12_out = mydir + "\\" + u"Артек_Программа _Дверь синего цвета_ (жюри 1)_результаты.xlsx" mydir13_out = mydir + "\\" + u"Артек_Программа _Дверь синего цвета_ (жюри 2)_результаты.xlsx" mydir14_out = mydir + "\\" + u"Артек_Программа _Дверь синего цвета_ (жюри 3)_результаты.xlsx" mydirs_ = [ mydir0, mydir1, mydir2, mydir3, mydir4, mydir5, mydir6, mydir7, mydir8, mydir9, mydir10, mydir11, mydir12_out, mydir13_out, mydir14_out ] global application application = sdk.Application() cell_properties = sdk.CellProperties() cell_properties.backgroundColor = sdk.ColorRGBA(193, 242, 17, 255) def iter_all_strings(): for size in itertools.count(1): for s in itertools.product(ascii_uppercase, repeat=size): yield "".join(s) def list_xls(rang): lst_addr = [] for s in iter_all_strings(): lst_addr.append(s) if s == rang: break return lst_addr