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