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