def __init__(self, pathLogFile=None, sheets=None):
ConnWorkBook.__init__(self, pathFile=pathLogFile)
self.pathLogFile = pathLogFile
self.workSheets = []
for sheet in sheets:
self.workSheets.append(self.modifyWorkSheet(sheetName=sheet))
self.more = PatternFill(patternType='solid', fgColor="80E1E1")
self.less = PatternFill(patternType="solid", fgColor="E18080")
def SolidFill(hex_color):
color = _color(hex_color)
fill = PatternFill(patternType=FILL_SOLID,
fgColor=color,
start_color=color,
end_color=color)
return fill
def make_excel(self, path):
img = Image.open(path)
pixels = img.load()
width, higth = img.size
excel_name, kind = path.split('\\')[-1].split('.')
wb = Workbook()
ws = wb['Sheet']
patterns = []
for row in range(higth):
for col in range(width):
if re.search('png', kind, re.I):
r, g, b, s = pixels[col, row]
else:
r, g, b = pixels[col, row]
pre_process = map(lambda x: hex(x), [r, g, b])
hex_pixel = list(
map(lambda x: x.split('x')[-1].zfill(2), pre_process))
hex_pixel.insert(0, '00')
hex_value = ''.join(hex_pixel)
#print('color', r, g, b, hex_value)
#sys.exit()
base_color = Color(rgb=hex_value)
color_pattern = PatternFill(patternType='solid',
fgColor=base_color,
bgColor=base_color)
patterns.append(color_pattern)
position = 0
for row in ws.iter_rows(min_row=1,
min_col=1,
max_row=higth,
max_col=width):
for cell in row:
cell.fill = patterns[position]
position += 1
wb.save('d:\\%s.xlsx' % excel_name)
def add_phase_label(phase_name: str, row: int,
worksheet: openpyxl.worksheet.worksheet.Worksheet):
phase_bg_fill = PatternFill(
patternType="solid",
fgColor=Color(type="rgb", rgb="FF305496"),
bgColor=Color(type="indexed", indexed=64),
)
phase_font = Font(
name="Calibri",
family=2,
sz=11,
scheme="minor",
vertAlign=None,
color=Color(theme=0, type="theme"),
)
worksheet.cell(row=row, column=1).fill = phase_bg_fill
worksheet.cell(row=row, column=1).font = phase_font
worksheet.cell(row=row, column=1).value = phase_name
for x in range(1, 17):
active_cell = worksheet.cell(row=row, column=x)
active_cell.fill = phase_bg_fill
return worksheet
def write_data_about_match_in_xlsx_file(url: url_type) -> None:
"""Write data about match in Match_Statistic.xlsx with styles."""
match_statistic_xlsx: Workbook = openpyxl.load_workbook(
'Match_Statistic.xlsx')
match_statistic: Worksheet = match_statistic_xlsx['Match statistic']
for link_on_match in get_list_of_links_to_matches(url):
data = get_data_about_match(link_on_match)
data['Название матча'], link_on_match = data['Название матча']
data['Название лиги'], link_on_league = data['Название лиги']
data_values = list(data.values())
match_statistic.append(data_values)
match_statistic[
f'C{match_statistic.max_row}'].hyperlink = link_on_match
match_statistic[
f'D{match_statistic.max_row}'].hyperlink = link_on_league
for coefficient in data_values[11:-1]:
if coefficient != '-':
if float(coefficient) == float(
match_statistic[f'AG{match_statistic.max_row}'].value):
match_statistic[(
f'{get_column_letter(list(data.values()).index(coefficient) + 1)}'
f'{match_statistic.max_row}')].fill = PatternFill(
'solid',
bgColor=Color('FFFF00'),
fgColor=Color('FFFF00'))
match_statistic_xlsx.save('Match_Statistic.xlsx')
print(
f'Данные о матче по ссылке {link_on_match} записаны в Match_Statistic.xlsx файл'
)
def format_components_tab_row_3(workbook: openpyxl.Workbook):
'''
Formats row 2 in the Components tab of the DCW
'''
sheet_name = 'Components'
util.check_toc_tab_exists(workbook=workbook, sheet_name=sheet_name)
worksheet = workbook[sheet_name]
field_bg_fill = PatternFill(patternType='solid',
fgColor=Color(rgb='FF48545D', type='rgb'),
bgColor=Color(indexed=64, type='indexed', theme=0))
field_font = Font(name='Calibri', family=2, sz=11, b=True,
scheme='minor', vertAlign=None, color=Color(theme=0, type='theme'))
for col in range(1, 17):
active_cell = worksheet.cell(row=3, column=col)
active_cell.fill = field_bg_fill
active_cell.font = field_font
active_cell.alignment = Alignment(wrapText=True)
util_borders.set_outside_borders(active_cell)
return workbook
def changeCellBackColorMulti(self, range, color, type='solid'):
startRange = range.split(':')[0]
endRange = range.split(':')[1]
fill = PatternFill(patternType=type,
start_color=color,
end_color=color)
for rows in self.sheet[startRange:endRange]:
for cell in rows:
self.sheet[cell.coordinate].fill = fill
def build_annex_overview():
workbook = Workbook()
workbook.create_sheet('Bilagsoversigt', 0)
sheet = workbook.active
sheet['A1'] = 'Bilag'
sheet['B1'] = 'Rapport'
sheet['C1'] = 'Sideantal'
sheet.column_dimensions['A'].width = 10
sheet.column_dimensions['B'].width = 80
sheet.column_dimensions['C'].width = 9
# Header layout
header = NamedStyle(name="header")
header.font = Font(bold=True)
header_row = sheet[1]
for cell in header_row:
cell.style = header
# Insert data from annex_list
for row in range(0, len(annex_list)):
sheet.cell(column=1, row=row + 2, value=annex_list[row].annex_number)
sheet.cell(column=2,
row=row + 2,
value=annex_list[row].base_filename.replace(
'.pdf',
'').replace('{' + annex_list[row].annex_number + '} - ',
''))
sheet.cell(column=3, row=row + 2, value=annex_list[row].num_pages)
# Conditional statement - Show doublets in "bilagsnumre"
red_fill = PatternFill(bgColor="FFC7CE")
dxf = DifferentialStyle(fill=red_fill)
duplicate_rule = Rule(type="duplicateValues", dxf=dxf, stopIfTrue=None)
sheet.conditional_formatting.add(f'A1:A{len(annex_list) + 1}',
duplicate_rule)
# Printsettings
sheet.page_setup.orientation = sheet.ORIENTATION_PORTRAIT
sheet.page_setup.paperSize = sheet.PAPERSIZE_A4
sheet.sheet_properties.pageSetUpPr.fitToPage = True
sheet.page_setup.fitToWidth = True
sheet.page_setup.fitToHeight = False
sheet.oddHeader.center.text = operation_titel
sheet.oddFooter.center.text = "Side &[Page] af &N"
sheet.print_area = 'A:C'
# Sorter bilagskolonnen
# sheet.auto_filter.add_sort_condition(f'A1:A{len(annex_list) + 1}')
# sheet.auto_filter.add_sort_condition('A:A')
global destination_folder
annex_overview_filename = destination_folder + f'/Bilagsoversigt {datetime.now().date()}.xlsx'
workbook.save(filename=annex_overview_filename)
def __init__(self, pathNADFile=None, sheetName="Sheet1"):
"""
:param pathNADFile:
:param sheetName:
"""
self.pathFileNAD = pathNADFile
ConnWorkBook.__init__(self, pathFile=pathNADFile)
self.selectWorkSheet(sheetName)
self.markColor = PatternFill(start_color='F0FF00',
end_color='F0FF00',
fill_type='solid')
class OpTypeColor:
CREATE = Color(
PatternFill(fill_type="solid",
start_color="FF0000",
end_color="FF0000"))
DELETE = Color(
PatternFill(fill_type="solid",
start_color="00FF00",
end_color="00FF00"))
UPDATE = Color(
PatternFill(fill_type="solid",
start_color="0000FF",
end_color="0000FF"))
UP_CONTENT = Color(
PatternFill(fill_type="solid",
start_color="663366",
end_color="663366"))
ENTITY_NOT_EXIST = Color(
PatternFill(fill_type="solid",
start_color="FFFF00",
end_color="FFFF00"))
def _fill_sheet(self, content_dict, target_worksheet, content_color):
for wellkey, welldata in content_dict.items():
_row, _column = return_plate_index(wellkey)
for n, label in enumerate(self.annotations):
row_offset = n * (self.Nrows + self.row_gap)
cell_ = target_worksheet.cell(
row = row_offset + self.origin[0] + _row,
column = self.origin[1] + _column
)
cell_.value = welldata[label.lower()]
fill_color = Color(rgb=content_color[wellkey])
cell_.fill = PatternFill("solid", fill_color)
def clean_wb(wb, ws, ws2):
clearFill = PatternFill(fill_type=None)
for row in ws['B2:D{0}'.format(ws.max_row)]:
for cell in row:
cell.value = None
for row in ws2['A1:{0}{1}'.format(get_column_letter(ws2.max_column),
ws2.max_row)]:
for cell in row:
cell.value = None
cell.fill = clearFill
wb.save("Ads_Crawler.xlsx")
def add_links_header(link: str,
col_start: int,
col_end: int,
worksheet: openpyxl.worksheet.worksheet.Worksheet,
output_file: str = None):
"""
Adds linked cells to the header for any tab on the PowerPlan DCW. The
output_file is needed so that the links will point to the different tabs
of the same file, and it needs the file name for some reason
"""
if output_file is None:
output_file = 'test_output.xlsx'
link_bg_fill = PatternFill(patternType='solid',
fgColor=Color(tint=-0.25, type='theme',
theme=0),
bgColor=Color(indexed=64,
type='indexed',
theme=0))
link_font = Font(name='Calibri',
family=2,
sz=11,
u='single',
scheme='minor',
vertAlign=None,
color=Color(theme=10, type='theme'))
link_alignment = Alignment(horizontal='center',
vertical='center',
wrap_text=True)
initial_cell = worksheet.cell(row=1, column=col_start)
initial_cell.value = link
initial_cell.hyperlink = '{}#\'{}\'!A1'.format(output_file, link)
initial_cell.style = 'Hyperlink'
for x in range(col_start, col_end + 1):
active_cell = worksheet.cell(row=1, column=x)
active_cell.font = link_font
active_cell.alignment = link_alignment
active_cell.fill = link_bg_fill
active_cell = util_borders.set_outside_borders(active_cell)
worksheet.merge_cells(start_row=1,
start_column=col_start,
end_row=1,
end_column=col_end)
return worksheet
def applyFmt(tblStyle, trStyle, tdStyle, cell, ws):
# resolve all the styles
finalStyle = deepcopy(tblStyle)
if finalStyle == None:
finalStyle = {}
for s in [trStyle, tdStyle]:
if s == None:
continue
for k, v in s.iteritems():
if v == False:
continue
finalStyle[k] = v
font = Font()
for k, v in finalStyle.iteritems():
if k == "italic" and v != False:
font.i = True
if k == "underline" and v != False:
font.u = Font.UNDERLINE_SINGLE
if k == "line-through" and v != False:
font.strikethrough = True
if k == "font_name" and v != False:
font.name = v
if k == "bold" and v == True:
font.bold = True
if k == 'width' and v != "" and v != False:
c, r = coordinate_from_string(cell.coordinate)
m = re.match("([\d\.]+)(\D+)", v)
if m != None:
w = m.group(1)
units = m.group(2)
if units == "in":
w = float(w) * 12
ws.column_dimensions[c].width = w
if k == "color" and v != False:
if v[1] == "#":
font.color = v[1:]
else:
try:
hxcol = webcolors.name_to_hex(v)
font.color = hxcol[1:]
except:
pass
if k == "background-color" and v != False:
c = Color(v[1:])
fill = PatternFill(patternType=fills.FILL_SOLID, fgColor=c)
cell.fill = fill
cell.font = font
def result(self, r, op_type, text):
op_types = {'CurrencyExchange': 'FFADFF2F',
'FlowIncome': 'FF7CFC00',
'FlowOutgo': 'FF00FF00',
'Withdrawal': 'FF32CD32',
'unknown': 'FFE9967A'
}
fill=PatternFill(start_color=op_types[op_type],
end_color=op_types[op_type],
fill_type='solid')
cc = self.ws.cell(row=r, column=1)
cc.comment = Comment(text, "py-bank-statements")
#cc = self.ws['A1']
#cc.fill = fill
cc.fill = fill
def style_range(worksheet, cell_range, bg_color=None, align=None):
"""
Apply styles to a range of cells
https://openpyxl.readthedocs.io/en/default/styles.html#applying-styles
:param worksheet: Excel worksheet instance
:param cell_range: An excel range to style (e.g. A1:F20)
:param bg_color: Background color in hex (e.g. ffffff or d7e4bc)
:param align: An openpyxl Alignment object
:return: None
"""
bg_color = bg_color or 'ffffff'
align = align or Alignment(
horizontal='left', vertical='center', wrapText=True)
bd = Side(style='thin', color='000000')
cell_border = Border(left=bd, right=bd, top=bd, bottom=bd)
for row in worksheet[cell_range]:
fill_color = PatternFill('solid', fgColor=bg_color)
for cell in row:
cell.border = cell_border
cell.fill = fill_color
cell.alignment = align
def prettify(self):
b = load_workbook(self.fname)
header_style = NamedStyle(name='table_header')
header_style.font = Font(bold=True)
header_style.fill = PatternFill(fill_type='solid',
start_color='00CCCCCC',
end_color='00CCCCCC')
header_style.alignment = Alignment(horizontal='center')
thin = Side(border_style='thin', color='000000')
double = Side(border_style="double", color="ff0000")
header_style.border = Border(top=double,
left=thin,
right=thin,
bottom=double)
b.add_named_style(header_style)
for ws in b.worksheets:
if ws.title in self.sheets_with_header:
has_header = True
for cell in ws[1]:
cell.style = header_style
else:
has_header = False
for x, c in enumerate(ws.columns, start=1):
column_width = 5
for y, cell in enumerate(c, start=1):
if cell.value is not None:
w = len(str(cell.value))
if has_header and (y == 1):
w = int(1.25 * w)
if w > column_width:
column_width = w
if column_width > 50:
column_width = 50
ws.column_dimensions[get_column_letter(
x)].width = column_width + 2
b.save(self.fname)
def cmd_export(self):
bkg_color = self.app.colors["color_3"].get().replace("#", "")
bkg_fill = PatternFill(patternType='solid', fgColor=Color(rgb=bkg_color))
head_color = self.app.colors["color_1"].get().replace("#", "")
head_fill = PatternFill(patternType='solid', fgColor=Color(rgb=head_color))
head_font_color = self.app.colors["font_color_1"].get().replace("#", "")
head_font = Font(bold=True, color=head_font_color)
body_font_color = self.app.colors["font_color_2"].get().replace("#", "")
body_font = Font(bold=False, color=body_font_color)
align_center = Alignment(horizontal="center", vertical="center")
align_right = Alignment(horizontal="right", vertical="center")
align_left = Alignment(horizontal="left", vertical="center")
row_offset = 1
col_offset = 1
work_book = Workbook()
work_sheet = work_book.active
work_sheet.title = "CS4_output"
for xl_row in range(1, 100):
for xl_col in range(1, 100):
active_cell = work_sheet.cell(column=xl_col, row=xl_row)
active_cell.fill = bkg_fill
for xl_row, res_row in enumerate(self.result_table):
for xl_col, res_col in enumerate(res_row):
active_cell = work_sheet.cell(column=xl_col+col_offset,
row=xl_row+row_offset,
value="{0}".format(res_col))
# Left column header
if not xl_col:
active_cell.fill = head_fill
active_cell.font = head_font
active_cell.alignment = align_right
# Top row header
elif not xl_row:
active_cell.fill = head_fill
active_cell.font = head_font
active_cell.alignment = align_center
# Data row at the bottom
elif xl_row >= len(self.result_table)-3:
active_cell.value = int(res_col)
# Style names
else:
active_cell.font = body_font
active_cell.alignment = align_left
# Create chart
values = Reference(work_sheet,
min_col=col_offset+1,
max_col=len(self.result_table[0])+col_offset-1,
min_row=len(self.result_table)+row_offset-1,
max_row=len(self.result_table)+row_offset-1)
chart = AreaChart()
chart.add_data(values, from_rows=True)
chart.legend = None
chart.title = "Core Sound analysis"
# set a pattern for the whole series
series = chart.series[0]
fill = PatternFillProperties()
fill.foreground = ColorChoice(prstClr="red")
fill.background = ColorChoice(prstClr="blue")
series.graphicalProperties.pattFill = fill
work_sheet.add_chart(chart, "E15")
work_book.save(filename=self.file_name)
self.root.destroy()
def erase_cell(current_cell):
current_cell.fill = PatternFill(patternType=FILL_NONE)
current_cell.border = BORDER_NONE
current_cell.value = None
from openpyxl import Workbook, load_workbook
from openpyxl.worksheet.worksheet import Worksheet
from openpyxl.styles.fills import PatternFill
wb = Workbook()
outputFile: Worksheet = wb.active
inputFile = load_workbook('INPUT.xlsx')
sheetInput = inputFile.get_sheet_by_name('Sheet1')
redFill = PatternFill(start_color='FFFF0000',
end_color='FFFF0000',
fill_type='solid')
orangeFill = PatternFill(start_color='ff8000',
end_color='ff8000',
fill_type='solid')
yellowFill = PatternFill(start_color='ffff00',
end_color='ffff00',
fill_type='solid')
keys = []
yellow = 0
orange = 0
red = 0
for i in range(0, 5):
col = i + 2
keys.append(outputFile.cell(row=3, column=col))
dict1 = {
def set_highlight(self, cell: Cell) -> None:
cell.fill = PatternFill(fill_type='solid', fgColor=Color(rgb='FFFFF200', type='rgb'),
bgColor=Color(rgb='FFFFFF00', type='rgb'))
def writeToXLSX(outfile, data):
# CELL に設定する値を変換する関数を返す
def getEntryConverter():
def converterPython3(value):
return value
def converterPython2(value):
return value.decode('shiftjis').encode('utf_8')
if sys.version_info.major >= 3:
return converterPython3
return converterPython2
try:
import openpyxl
from openpyxl.styles import colors
from openpyxl.styles import Font, Color
from openpyxl.styles.fills import PatternFill
wb = openpyxl.Workbook()
ws = wb.active
# 列幅に必要なサイズを保持する配列
maxWidths = []
# ヘッダ部分を設定する
y = 0
for x, item in enumerate(excelKeys):
cell = ws.cell(row=y + 1, column=x + 1)
cell.value = item
cell.fill = PatternFill(patternType='solid',
start_color=colors.YELLOW,
end_color=colors.YELLOW)
maxWidths.append(len(cell.value) + 1)
y = y + 1
# 各エントリーを設定するときのコンバーターを取得する (python 2/3 の違いを吸収するためのもの)
converter = getEntryConverter()
# エラーごとにまとめて表示するための情報
errorSummary = {}
# ログの解析結果を設定する
for entry in data:
# ファイルパスを取り除いた関連エラーメッセージをまとめる
message = converter(entry['message'])
message = re.sub(r'((\S*)\)\s*)?\[(.+?)\]', r'', message)
# エラータイプ
errType = entry['errType']
# エラーコード
code = entry['code']
# サマリーを管理するハッシュ用のキー
errorKey = ' '.join([errType, code, message])
if errorKey not in errorSummary:
errorSummary[errorKey] = {}
errorSummary[errorKey]["description"] = message
errorSummary[errorKey]["entries"] = []
errorSummary[errorKey]["errType"] = errType
errorSummary[errorKey]["code"] = code
errorSummary[errorKey]["entries"].append(entry)
for x, key in enumerate(excelKeys):
cell = ws.cell(row=y + 1, column=x + 1)
if key == "relpath":
val = entry['relpath'] + " line: " + entry['lineNumber']
cell.hyperlink = entry['blobURL']
cell.font = Font(u='single', color=colors.BLUE)
else:
entryKey = entry[key]
val = converter(entry[key])
# 列幅を設定するために必要なサイズを計算する
width = len(val) + 1
if maxWidths[x] < width:
maxWidths[x] = width
# セルに値を設定する
if val.isdigit():
cell.value = int(val)
else:
cell.value = val
# 行番号を更新する
y = y + 1
# 列幅を設定する
for x, item in enumerate(excelKeys):
ws.column_dimensions[openpyxl.utils.get_column_letter(
x + 1)].width = maxWidths[x]
# Excel の列にフィルタを設定する
start = openpyxl.utils.get_column_letter(1)
end = openpyxl.utils.get_column_letter(len(excelKeys))
ws.auto_filter.ref = start + ":" + end
# ウィンドウ枠を固定
ws.freeze_panes = 'F2'
#############################################################################
# エラーのサマリーシート用のコード
#############################################################################
worksheetIndex = 0
errorKeys = sorted(errorSummary.keys())
for errorKey in errorKeys:
worksheetIndex = worksheetIndex + 1
# 列幅に必要なサイズを保持する配列
maxWidths = []
message = errorSummary[errorKey]["description"]
entries = errorSummary[errorKey]["entries"]
errType = errorSummary[errorKey]["errType"]
code = errorSummary[errorKey]["code"]
wsError = wb.create_sheet()
wsError.title = str(worksheetIndex) + "(" + code + ")"
x = 0
y = 0
# エラーメッセージをセットする
if entries:
entry = entries[0]
cell = wsError.cell(row=y + 1, column=x + 1)
cell.value = message
y = y + 1
# 空行を入れる
y = y + 1
outputKeys = [
'path',
'lineNumber',
'blobURL',
]
# ヘッダ部分を設定
for x, key in enumerate(outputKeys):
cell = wsError.cell(row=y + 1, column=x + 1)
cell.value = key
cell.fill = PatternFill(patternType='solid',
start_color=colors.YELLOW,
end_color=colors.YELLOW)
maxWidths.append(len(cell.value) + 1)
y = y + 1
# 各エラー箇所を設定する
for entry in entries:
for x, key in enumerate(outputKeys):
cell = wsError.cell(row=y + 1, column=x + 1)
if key == "blobURL":
cell.hyperlink = entry[key]
cell.font = Font(u='single', color=colors.BLUE)
val = converter(entry[key])
if val.isdigit():
cell.value = int(val)
else:
cell.value = val
# 列幅を設定するために必要なサイズを計算する
width = len(val) + 1
if maxWidths[x] < width:
maxWidths[x] = width
y = y + 1
# 列幅を設定する
for x, item in enumerate(maxWidths):
wsError.column_dimensions[openpyxl.utils.get_column_letter(
x + 1)].width = item
wb.save(outfile)
print("wrote " + outfile)
except ImportError:
print(
"please run '<python root>\\Scripts\\pip install openpyxl --user'")
from datetime import timedelta, date
from openpyxl.styles import Border, Side, Alignment, Font
from openpyxl.styles.borders import BORDER_MEDIUM, BORDER_NONE
from openpyxl.styles.colors import Color
from openpyxl.styles.fills import PatternFill, FILL_NONE
from openpyxl.utils import get_column_letter
booking_fill = PatternFill(start_color='FFEC3223', end_color='FFEC3223', fill_type='solid')
expedia_fill = PatternFill(start_color='FFF39C38', end_color='FFF39C38', fill_type='solid')
airbnb_fill = PatternFill(start_color='FFF7C143', end_color='FFF7C143', fill_type='solid')
direct_fill = PatternFill(start_color='FFFFFD54', end_color='FFFFFD54', fill_type='solid')
no_show_fill = PatternFill(start_color='FF8797AE', end_color='FF8797AE', fill_type='solid')
penalty_fill = PatternFill(start_color='FFF092D4', end_color='FFF092D4', fill_type='solid')
blank_fill = PatternFill(patternType=FILL_NONE)
non_refundable_fill = PatternFill(start_color='FF4CAC5B', end_color='FF4CAC5B', fill_type='solid')
refundable_fill = PatternFill(start_color='FF48AFEA', end_color='FF48AFEA', fill_type='solid')
confirmed_reservation_fill = PatternFill(start_color='FF4BAC5B', end_color='FF4BAC5B', fill_type='solid')
canceled_reservation_fill = PatternFill(start_color='FFEC3323', end_color='FFEC3323', fill_type='solid')
no_show_reservation_fill = PatternFill(start_color='FFF39C38', end_color='FFF39C38', fill_type='solid')
RED = Color(rgb='FF0000')
def fill_first_cell(current_cell, channel, state=''):
if channel.lower() == 'booking':
current_cell.fill = booking_fill
elif channel.lower() == 'airbnb':
current_cell.fill = airbnb_fill
elif channel.lower() == 'expedia':
def auto_browse(fail_tuple,search_list):
# EXCEL
wb = load_workbook('Ads_Crawler.xlsx')
ws = wb['Input']
ws2 = wb['Output']
url_list = []
src = ""
html = ""
# clean_wb(wb,ws,ws2)
# wb = load_workbook('Ads_Crawler.xlsx')
# ws = wb['Sheet1']
# search_set = set()
# url_list = []
# Construct search_set
# for cell in ws['F']:
# if cell.value and cell.value != u'Search for:':
# search_set.add(cell.value)
# /EXCEL
# EXCEL
# for row in ws['A{}:A{}'.format(ws.min_row + 1, ws.max_row)]:
# for cell in row:
# url_list.append("http://{0}/ads.txt".format( (cell.value).encode('utf-8').strip() ))
# Add one to list length to accound for start=2 below
max_row = len(fail_tuple) - 1
# /EXCEL
# for row, url in enumerate(url_list, start=2):
for index, row_url in enumerate(fail_tuple):
row = row_url[0]
url = row_url[1]
print "____________________________"
print url
print ""
if index == 0:
# Set browser variable
browser = Browser('chrome', options=chrome_options)
# Create driver variable for selenium
# Set timeout limit
selenium_driver = browser.driver
selenium_driver.set_page_load_timeout(15)
# # Open chrome
# try:
# browser.visit(url)
#
# counter = 0
# # Test for "pre" element
# while browser.is_element_not_present_by_tag("pre",wait_time=1):
# counter = counter + 1
#
# if counter > 15:
# break
# else:
# continue
#
# except TimeoutException:
# pass
else:
# Close old tab
browser.windows[0].close()
# Switch current window to new tab
browser.windows.current = browser.windows[0]
# Visit URL
try:
browser.visit(url)
counter = 0
# Test for "pre" element
while browser.is_element_not_present_by_tag("pre",wait_time=1):
counter = counter + 1
if counter > 15:
break
else:
continue
except TimeoutException:
pass
if browser.is_element_present_by_tag("pre"):
# Remove manual check flag from Notes column
ws['{0}{1}'.format("D",row)] = ""
src = selenium_driver.page_source
# Loop through search_set from wb
for col, search_str in enumerate(search_list, start=2):
# Check for desired text
text_found = re.search(r'(\<|\,| ){0}(\>|\,| )'.format(search_str), src)
# Confirm if found and choose column accordingly
if text_found:
# Input tab
column = "B"
# Output tab
response = "Pass"
colorFill = PatternFill(start_color='00FF00',
end_color='00FF00',
fill_type='solid')
# Log response to terminal
print "{0} found!".format(search_str)
else:
# Input tab
column = "C"
# Output tab
response = "Fail"
colorFill = PatternFill(start_color='FF0000',
end_color='FF0000',
fill_type='solid')
# Log response to terminal
print "{0} not found!".format(search_str)
# Populate 'Output' tab body
ws2.cell(row=row,column=col).value = response
ws2.cell(row=row,column=col).fill= colorFill
# Populate spreadsheet using column above
if ws['{0}{1}'.format(column,row)].value:
current_val = ws['{0}{1}'.format(column,row)].value
ws['{0}{1}'.format(column,row)] = "{0}, {1}".format(current_val, search_str)
else:
ws['{0}{1}'.format(column,row)] = search_str
print "____________________________"
# Write confirmation to file
wb.save("Ads_Crawler.xlsx")
# Open new tab
selenium_driver.execute_script("window.open('');")
if index == max_row:
# Close browser
browser.quit()
def japanese(name, NewProductNum, ws):
title = []
split = name.split()
for i in split:
if i == "Slide":
title.append('シャワー')
elif i == 'Denim':
title.append('デニム')
elif i == "Flip-Flop":
title.append('ビーチサンダル')
elif i == 'Cardigan':
title.append('カーディガン')
elif i == "Sweater":
title.append('セーター')
elif i == "Hoodie":
title.append('パーカー')
elif i == "Jacket" or i == 'Coat' or i == 'Vest' or i == 'Overshirt':
title.append('ジャケット')
elif i == "Tee":
title.append('Tシャツ')
elif i == "Jersey":
title.append('ジャージ')
elif i == "Shirt":
title.append('シャツ')
elif i == "Sock":
title.append('ソックス/靴下')
elif i == "Boxer":
title.append('ボクサーパンツ')
elif i == "Brief":
title.append('ブリーフ')
elif i == "Sweatshirt":
title.append('スウェットシャツ')
elif i == "Jogger":
title.append('ジョガー')
elif i == "Pants":
title.append('パンツ')
elif i == "Skinny":
title.append('スキニー')
elif i == "Ankle":
title.append('アンクル')
elif i == "Shorts":
title.append('ショートパンツ')
elif i == "Hat":
title.append('キャップ')
elif i == "Flannel":
title.append('フランネル')
elif i == "Print":
title.append('プリント')
elif i == "Waffle":
title.append('ワッフル')
elif i == "Beanie":
title.append('ニット帽')
elif i == "Sweatpants":
title.append('スウェットパンツ')
elif i == "Gazelle":
title.append('ガゼル')
elif i == "Sneaker":
title.append('スニーカー')
elif i == "Superstar":
title.append('スーパースター')
elif i == "Sandals":
title.append('サンダル')
elif i == "Fur":
title.append('ファー')
elif i == "Leather":
title.append('レザー')
elif i == "Vintage":
title.append('ビンテージ')
elif i == "Suede":
title.append('スエード')
elif i == "Pastel":
title.append('パステル')
elif i == "Mule":
title.append('ミュール')
elif i == "Heel":
title.append('ハイヒール')
elif i == "Boot":
title.append('ブーツ')
elif i == "Sunglasses":
title.append('サングラス')
elif i == "Bag":
title.append('バッグ')
elif i == "Necklace" or i == "Bracelet":
title.append('ネックレス')
elif i == "Backpack":
title.append('バックパック')
elif i == "Robe":
title.append('ローブ')
elif i == "Watch":
title.append('時計')
elif i == "Ring":
title.append('リング')
elif i == "Tank":
title.append('タンク')
elif i == "Top":
title.append('トップ')
elif i == "Overall":
title.append('オーバーオール')
elif i == "Leggings":
title.append('レギンス')
elif i == "Track":
title.append('トラック')
elif i == "High-Rise":
title.append('ハイライズ')
elif i == "Active":
title.append('アクティブ')
elif i == "Mini":
title.append('ミニ')
elif i == "Twil":
title.append('ツイル')
elif i == "Bralette":
title.append('ブラ')
elif i == "Hipster":
title.append('ヒップスターショーツ')
elif i == "Bra":
title.append('ブラ')
elif i == "Undie":
title.append('下着パンツ')
elif i == "Cotton":
title.append('コットン')
elif i == "Thong":
title.append('下着パンツ')
elif i == "Tanga":
title.append('下着パンツ')
elif i == "Body":
title.append('ボディ')
elif i == "Bikini":
title.append('ビキニ')
elif i == "Top":
title.append('トップ')
elif i == "Bottom":
title.append('ボトムパンツ')
elif i == "One-Piece":
title.append('ワンピース')
elif i == "Swimsuit":
title.append('水着')
elif i == "Duffle":
title.append('ダッフル')
elif i == "Stripe":
title.append('ストライプ')
elif i == "Belt":
title.append('ベルト')
elif i == "Crossbody":
title.append('ショルダー')
elif i == "Tote":
title.append('トート')
elif i == "Canvas":
title.append('キャンバス')
elif i == "Wedge":
title.append('ウェッジ')
elif i == "Button-Down":
title.append('ボタン')
elif i == "Satin":
title.append('サテン')
elif i == "Fleece":
title.append('フリース')
elif i == "Logo":
title.append('ロゴ')
elif i == "Pocket":
title.append('ポケット')
elif i == "Bomber":
title.append('ボンバー')
elif i == "Hooded":
title.append('フード付き')
elif i == "Parka":
title.append('パーカー')
elif i == "Coach":
title.append('コーチ')
elif i == "Windbreaker":
title.append('ウィンドブレーカー')
elif i == "Popover":
title.append('ポップオーバー')
elif i == "Polo":
title.append('ポロ')
elif i == "Boxer":
title.append('ボクサー')
elif i == "Brief":
title.append('ブリーフ')
elif i == "Floral":
title.append('花柄')
elif i == "Full-Zip":
title.append('ジップアップ')
elif i == "Set":
title.append('セット')
elif i == "Sleep":
title.append('パジャマ')
elif i == "Flannele":
title.append('フランネル')
elif i == "Plaid":
title.append('チェック')
elif i == "Pocket":
title.append('ポケット')
elif i == "Checkerboard":
title.append('チェック')
elif i == "Patterned":
title.append('柄入り')
elif i == "Oxford ":
title.append('オックスフォード')
elif i == "Poplin ":
title.append('アイコン')
elif i == "Faux":
title.append('フォックス')
elif i == "Crop":
title.append('ショート丈')
elif i == "Knit":
title.append('ニット')
elif i == "Dad":
title.append('ダッド')
elif i == "Trunks":
title.append('トランクス')
elif i == "Swim":
title.append('水着')
elif i == "Boardshorts":
title.append('水着パンツ')
elif i == "Multipack":
title.append('マルチパック')
elif i == "Trunk":
title.append('トランクス')
elif i == "Lightweight":
title.append('軽量')
elif i == "Down":
title.append('ダウン')
elif i == "Sherpa-Lined":
title.append('裏ボア')
elif i == "Vest":
title.append('ベスト')
elif i == "Mockneck":
title.append('モックネック')
elif i == "Colorblock":
title.append('カラーブロック')
elif i == "Embroidered ":
title.append('刺繍入り')
elif i == "Trucker ":
title.append('トラッカー')
elif i == "Ripped":
title.append('ダメージ')
elif i == "Straight":
title.append('ストレート')
elif i == "Slim ":
title.append('スリム')
elif i == "Fleece":
title.append('フリース')
elif i == "Super":
title.append('スーパー')
elif i == "Crewneck":
title.append('クルーネック')
elif i == "Icon":
title.append('アイコン')
elif i == "Graphic":
title.append('グラフィック')
elif i == "V-Neck":
title.append('Vネック')
elif i == "T-Shirt":
title.append('Tシャツ')
elif i == "Henley":
title.append('ヘンリー')
elif i == "Crop":
title.append('ショート丈')
elif i == "Jeans":
title.append('ジーンズ')
elif i == "Nylon":
title.append('ナイロン')
elif i == "Short-Sleeve":
title.append('半袖')
elif i == "Polo":
title.append('ポロシャツ')
elif i == "Button-Front":
title.append('ボタン')
elif i == "Puffer":
title.append('ダウン')
elif i == "Cotton":
title.append('コットン')
else:
title.append(i)
title = ' '.join(title)
count = 0
for c in title:
if unicodedata.east_asian_width(c) in 'FWA':
count += 2
else:
count += 1
if count > 60:
ws['A' +
str(NewProductNum)].fill = PatternFill(patternType='lightGray')
return (title)
active_sheet.column_dimensions['C'].width = 15
active_sheet['D1'] = '備考'
active_sheet.column_dimensions['D'].width = 30
# 罫線(外枠)を設定
border = Border(top=Side(style='thin', color='000000'),
bottom=Side(style='thin', color='000000'),
left=Side(style='thin', color='000000'),
right=Side(style='thin', color='000000'))
# セルに罫線を設定
for row_num in range(1, 2):
for col_num in range(1, 5):
active_sheet.cell(row=row_num, column=col_num).border = border
#セルの塗りつぶし
active_sheet['A1'].fill = PatternFill(
patternType=FILL_SOLID, fgColor=openpyxl.styles.colors.Color(rgb=YELLOW))
active_sheet['B1'].fill = PatternFill(
patternType=FILL_SOLID, fgColor=openpyxl.styles.colors.Color(rgb=YELLOW))
active_sheet['C1'].fill = PatternFill(
patternType=FILL_SOLID, fgColor=openpyxl.styles.colors.Color(rgb=YELLOW))
active_sheet['D1'].fill = PatternFill(
patternType=FILL_SOLID, fgColor=openpyxl.styles.colors.Color(rgb=YELLOW))
book.save('管理表.xlsx')
#----------------------------------------------------------------------
##filelist.xlsxと管理表.xlsxを統合する
font_cell.font = Font(name='Arial',
size=18,
b=True,
i=True,
color=colors.COLOR_INDEX[12])
# openpyxl.styles.borders
borders = Border(left=Side(border_style=BORDER_THIN, color='00000000'),\
right=Side(border_style=BORDER_THICK, color='00000000'),\
top=Side(border_style=BORDER_DASHDOT, color='00000000'),\
bottom=Side(border_style=BORDER_DOUBLE, color='00000000'))
border_cell.border = borders
# openpyxl.styles.fills
red_color = colors.Color(rgb='00FF0000')
solid_red_fill = PatternFill(patternType='solid', fgColor=red_color)
fill_cell.fill = solid_red_fill
# openpyxl.styles.numbers
number_format_cell.number_format = FORMAT_PERCENTAGE_00
# Create a Workbook password and lock the structure
wb2 = Workbook()
wb2.security.workbookPassword = '******'
wb2.security.lockStructure = True
wb2.save("Cell formatting2.xlsx")
# Protect the sheet
ws1.protection.sheet = True
ws1.protection.password = '******'
ws1.protection.enable()
def export_excel(request, model, query_mark, filename,
sheet_name, sheet_head_data):
"""前端传递值：None;
(下载Excel表格);
:param model:-->model: 被导出excel模型;
:param query_mark:-->str: 模块名称;
:param filename: 客户端下载excel文件命名。（本接口使用query_mark固定值）;
:param sheet_name: excel "sheet"命名;
:param sheet_head_data:-->dict: "sheet"第一行数据，需要字段名，列宽，颜色等信息（可选）
:return: return FileResponse(filename) or "msg" if rc."key" is None;
"""
excel_abs_dir = os.path.join(BASE_DIR, 'media', 'excels', filename)
max_row_num = 100000 # 最大导出行数
# 从缓存加载数据
rc = redis.Redis(connection_pool=pool_result)
key = get_key(request, model, query_mark) # 生成组合"key"值
if rc.get(key) is None:
msg = '未查询到导出数据，请查询后重试！'
return msg
else:
data_rc = json.loads(rc.get(key)) # 获取redis缓存中数据
# excel填充数据
data = [[x for x in sheet_head_data.keys()]] # 列表解析加载"sheet"标题行
# queryset数据转化data = [['1', '邓平', '25'], ['2', '云云', '23']]
for sheet_num, data_dic in enumerate(data_rc):
data_lst = [sheet_num+1] # 序号num
for value in data_dic.values():
data_lst.append(value)
data.append(data_lst)
# 限制最大导出行数
if len(data) > max_row_num:
data = data[: max_row_num]
workbook = openpyxl.Workbook() # 创建文件对象
create_sheet = workbook.create_sheet(sheet_name, 0) # 初始位置创建sheet对象
# 设置表头背景色
fp = PatternFill(fill_type='solid', fgColor="77DDFF") # 浅蓝色
for index, width in enumerate([x for x in sheet_head_data.values()]):
create_sheet.cell(row=1, column=index+1).fill = fp # 第一行加浅蓝色
cm = get_column_letter(index+1) # 根据序列生成对应列标号，第一列
create_sheet.column_dimensions[cm].width = width # 对应字段名设置列宽
for row_index, row in enumerate(data): # 数据写入到单元格中
for col_index, col in enumerate(row):
# 对"sheet"cell数据写入
create_sheet.cell(row_index+1, col_index+1).value = col
# 字体设置，颜色等，样式
# sheet.cell(row_index+1, col_index+1).font = Font(
# color=colors.BLUE
# )
# 设置文字位置，对其方式=左对齐
create_sheet.cell(row_index+1, col_index+1).alignment = Alignment(
horizontal='left', # 水平：center left，right
vertical='center' # 垂直: top,bottom,center
)
del workbook['Sheet'] # 删除workbook中初始'Sheet'表名
workbook.save(excel_abs_dir) # 保存文件到本地, save调用了close
# 返回文件流于浏览器
file = open(excel_abs_dir, 'rb')
response = FileResponse(file)
response['Content-Type'] = 'application/octet-stream'
response['Content-Disposition'] = 'attachment;filename={}'. \
format(filename).encode('utf-8') # filename可以另外写一个函数动态生成
return response
sheet["B2"].font = bold_font
sheet["B3"].font = big_red_text
sheet["C4"].alignment = center_aligned_text
sheet["C5"].border = square_border
# work_book.save(filename="styled.xlsx")
sheet["B7"].alignment = center_aligned_text
sheet["B7"].font = big_red_text
sheet["B7"].border = square_border
# work_book.save(filename="styled.xlsx")
custom_style = NamedStyle(name="header")
custom_style.font = Font(bold=True)
custom_style.border = Border(bottom=Side(border_style="thin"))
custom_style.alignment = Alignment(horizontal="center", vertical="center")
header_row = sheet[1]
for cell in header_row:
cell.style = custom_style
# work_book.save(filename="styled.xlsx")
one_more_style = NamedStyle(name="highlight")
one_more_style.fill = PatternFill(fgColor=colors.Color("d7abcc"), patternType="lightHorizontal")
for cell in sheet["A"]:
cell.style = one_more_style
# work_book.save(filename="styled.xlsx")
# Loop through search_set from wb
for col, search_str in enumerate(search_list, start=2):
# for search_str in search_set:
# Check for desired text
text_found = re.search(r'(\<|\,| ){0}(\>|\,| )'.format(search_str),
src)
# Confirm if found and choose column accordingly
if text_found:
# Input tab
column = "B"
# Output tab
response = "Pass"
colorFill = PatternFill(start_color='00FF00',
end_color='00FF00',
fill_type='solid')
# Log response to terminal
print "{0} found!".format(search_str)
else:
# Input tab
column = "C"
# Output tab
response = "Fail"
colorFill = PatternFill(start_color='FF0000',
end_color='FF0000',
fill_type='solid')
# Log response to terminal
