def write_xlsx(data, file_name, params): """Method to write xls given data.""" try: report_type = params['report_region'] xltm_tmp = params['xltm'] if 'xltm' in params else 'case_load' # Define some style for borders row_start = 9 if xltm_tmp == 'case_load' else 2 border = Border(left=Side(style='thin'), right=Side(style='thin'), top=Side(style='thin'), bottom=Side(style='thin')) sheet_name = params['sheet'] if 'sheet' in params else 'Sheet' xls_tmp = '_orgs.xltm' if report_type == 4 else '.xltm' wb = load_workbook('%s/%s%s' % (DOC_ROOT, xltm_tmp, xls_tmp), data_only=True) ws = wb.active if row_start == 9 else wb.get_sheet_by_name(sheet_name) # Lets write some data to the file for i, value in enumerate(data): for c, stats in enumerate(value): ws.cell(row=i + row_start, column=c + 1).value = stats if row_start == 9: ws.cell(row=i + row_start, column=c + 1).border = border # Fill my placeholders with actual parameters if row_start == 9: for idx, row in enumerate(ws['A2:P5']): for cell in row: if cell.value and "{" in cell.value: cell.value = cell.value.format(**params) file_ext = '.xlsm' if row_start == 2 else '.xlsx' ws.title = sheet_name xls_name = '%s/xlsx/%s%s' % (MEDIA_ROOT, file_name, file_ext) wb.save(xls_name) except Exception, e: print "error writing excel - %s" % (str(e)) raise e
def formatCell(cell, border_top, border_right, border_bottom, border_left, fill): """ Applies border to cell. :param cell: Cell object; cell to work on """ cell.border = Border(top=Side(style=border_top), right=Side(style=border_right), bottom=Side(style=border_bottom), left=Side(style=border_left)) if fill: cell.fill = PatternFill(start_color=fill, end_color=fill, fill_type="solid") else: cell.fill = PatternFill(fill_type=None)
def apply_border_format(ws, row, column, style="thin"): thin_border = Border( left=Side(style=style), right=Side(style=style), top=Side(style=style), bottom=Side(style=style), ) ws.cell(row=row, column=column).border = thin_border
def range_border_sheet(ws, cell_range, border): top = Border(top=border.top) left = Border(left=border.left) right = Border(right=border.right) bottom = Border(bottom=border.bottom) rows = cell_range for cell in rows[0]: cell.top = cell.top + top for cell in rows[-1]: cell.bottom = cell.bottom + bottom for row in rows: l = row[0] r = row[-1] l.border = l.border + left r.border = r.border + right
def write_consistency_matrix(path, factor_list): wb = Workbook() sheet = wb.active sheet.title = "consistency matrix" len = get_projection_count(factor_list) # Writing the vertical projections row = 3 colum = 1 for factor in factor_list[1:]: for projection in factor[1:]: sheet.cell(row, colum).value = factor[0] sheet.cell(row, colum + 1).value = projection sheet.cell(row, colum + 1).border = thin_border_right row += 1 for i in range(1, len + 3): if sheet.cell(row - 1, i).border == Border(right=Side(style='thin')): sheet.cell(row - 1, i).border = Border(bottom=Side(style='thin'), right=Side(style='thin')) else: sheet.cell(row - 1, i).border = thin_border_bottom # Writing the horizontal projections row = 1 colum = 3 for factor in factor_list[1:]: for projection in factor[1:]: sheet.cell(row, colum).value = factor[0] sheet.cell(row + 1, colum).value = projection sheet.cell(row + 1, colum).border = thin_border_bottom colum += 1 for i in range(1, len + 3): if sheet.cell(i, colum - 1).border == Border(bottom=Side(style='thin')): sheet.cell(i, colum - 1).border = Border( bottom=Side(style='thin'), right=Side(style='thin')) else: sheet.cell(i, colum - 1).border = thin_border_right wb.save(filename=path)
def record_control_limit(self, uclx, clx, lclx, uclr, clr, lclr, control_sheet): has_filled = False year_input = self.year_cb.get() month_input = self.month_cb.get() product_input = self.product_cb.get() machine_input = self.machine_cb.get() report_input = self.peel_strength_name.get() record_by_input = self.record_entry.get() max_row = control_sheet.max_row min_row = 1 while min_row <= max_row: month_table = control_sheet.cell(row=min_row, column=2).value product_table = control_sheet.cell(row=min_row, column=3).value machine_table = control_sheet.cell(row=min_row, column=4).value report_name_table = control_sheet.cell(row=min_row, column=5).value update_condition = (month_input == month_table and product_input == product_table and machine_input == machine_table and report_name_table == report_input) if update_condition: has_filled = True control_sheet.cell(row=min_row, column=6).value = uclx control_sheet.cell(row=min_row, column=7).value = clx control_sheet.cell(row=min_row, column=8).value = lclx control_sheet.cell(row=min_row, column=9).value = uclr control_sheet.cell(row=min_row, column=10).value = clr control_sheet.cell(row=min_row, column=11).value = lclr break # Loop command min_row += 1 if has_filled == False: last_filled_row = int(control_sheet.max_row) + 1 list_filled = [ year_input, month_input, product_input, machine_input, report_input, uclx, clx, lclx, uclr, clr, lclr, record_by_input ] filled_col = 1 for item in list_filled: control_sheet.cell(row=last_filled_row, column=filled_col).value = item thin_border = Border(left=Side(style='thin'), right=Side(style='thin'), top=Side(style='thin'), bottom=Side(style='thin')) center_align = Alignment(horizontal='center') control_sheet.cell(row=last_filled_row, column=filled_col).alignment = center_align control_sheet.cell(row=last_filled_row, column=filled_col).border = thin_border filled_col += 1
def createsheet(filename, datum, offertnr): ws['J16'] = datum ws['J17'] = offertnr ws['A25'] = 'Buitenvloer' ws['B26'] = r'- Opmerkingen' ws['H25'] = 8 ws['I25'] = 'x' ws['J25'] = 5.25 first = ws['H25'] second = ws['J25'] ws['K25'] = first.value * second.value cell = ws['K25'] cell.number_format = '€ ##,##' cell = ws['J25'] cell.number_format = '€ ##,##' cell = ws['H25'] cell.number_format = '#,## u' #ws['G13'] = ws['A16'].value top_border = Border(top=Side(style='thick')) bottom_border = Border(bottom=Side(style='thick')) #ws['G13'].border = top_border #for i in range(items): # additem(i) '''sumarr is matrix met laatste drie rijen met:K47=SUM(K25:K29) K48=K47/6*100 K49=SUM(K47:K48)''' sumarr = [[[], [], [], [], []], [[], [], [], [], []], [[], [], [], [], []]] # 3x5 for i in range(5): # create array met totaalberekening, einde for b in range(3): if i == 0 or i == 4: sumarr[b][i] = ws[listsumlet[i] + str(b + 47)].value print(sumarr) '''invoegen image en opslaan workbook''' img = pxl.drawing.image.Image( r'C:\Users\Tom\Documents\Offerte\image001.jpg') ws.add_image(img, 'A3') wb.save(filename)
def setBorderMulti(self, range, style='thin', color='000000', place='all'): side = Side(style=style, color=color) if place == 'all': border = Border(top=side, bottom=side, left=side, right=side) elif place == 'top': border = Border(top=side) elif place == 'bottom': border = Border(bottom=side) elif place == 'left': border = Border(left=side) elif place == 'right': border = Border(right=side) startRange = range.split(':')[0] endRange = range.split(':')[1] for rows in self.sheet[startRange:endRange]: for cell in rows: self.sheet[cell.coordinate].border = border
def clearCell(cell, fill, border): """ Clears values from cells, removes/applies fill and removes borders (optional). :param cell: Cell object; cell to work on """ cell.value = None if fill: cell.fill = PatternFill(start_color=fill, end_color=fill, fill_type="solid") else: cell.fill = PatternFill(fill_type=None) if border: cell.border = Border(left=Side(style=None), right=Side(style=None), top=Side(style=None), bottom=Side(style=None))
def WriteIn(data, id): filepath = "HelloFlask/static/excel_history/RequestHistory_" + id + ".xlsx" wb = openpyxl.Workbook() ws = wb.active #массив с названиями столбцов mas_names = [ "Номер запроса", "Номер способа", "Бизнес, привлекающий трафик", "Бизнес конкурентов", "Радиус окружностей", "Адреса зафиксированных точек", "Координаты зафиксированных точек", "Время сделанного запроса(UTC+3:00)" ] #массив с индексами ячеек, где будут написаны названия столбцов mas_index = ["A1", "B1", "C1", "D1", "E1", "F1", "G1", "H1"] thin_border = Border(left=Side(style='thin'), right=Side(style='thin'), top=Side(style='thin'), bottom=Side(style='thin')) #цикл для заполнения/заливки названий столбцов for i in range(len(mas_names)): ws[mas_index[i]] = mas_names[i] ws[mas_index[i]].fill = PatternFill(start_color="FFDF59", fill_type="solid") #задаю конкретные длины столбцам ws.column_dimensions['A'].width = 15 ws.column_dimensions['B'].width = 15 ws.column_dimensions['C'].width = 29 ws.column_dimensions['D'].width = 19 ws.column_dimensions['E'].width = 20 ws.column_dimensions['F'].width = 31 ws.column_dimensions['G'].width = 35 ws.column_dimensions['H'].width = 35 #задаю ширину границам ячеек с наименованиями столбцов for i in range(1, 9): ws.cell(row=1, column=i).border = thin_border #цикл для конеченого заполнения файла историей запросов for i in range(len(data.keys())): for j in range(1, 9): ws.cell(row=i + 2, column=j).border = thin_border ws["A" + str(i + 2)] = i + 1 ws["B" + str(i + 2)] = int(data[str(i + 1)]["md_number"]) ws["C" + str(i + 2)] = data[str(i + 1)]["your_business"] ws["D" + str(i + 2)] = data[str(i + 1)]["conc_business"] if data[str(i + 1)]["radius"] != "": ws["E" + str(i + 2)] = int(data[str(i + 1)]["radius"]) ws["F" + str(i + 2)] = data[str(i + 1)]["stopped_points_adress"] ws["G" + str(i + 2)] = data[str(i + 1)]["stopped_points_coords"] ws["H" + str(i + 2)] = data[str(i + 1)]["time"] wb.save(filepath)
def thinBorders(): """ Crée les bordures des cases Return: un objet Border """ thin_border = Border(left=Side(style='thin'), right=Side(style='thin'), top=Side(style='thin'), bottom=Side(style='thin')) return thin_border
def set_style(self, cell): if self.color is not None: cell.fill = PatternFill(patternType='solid', fgColor=Color('FF%06x' % self.color)) if self.borders: kwa = { self.border_names[b]: Side(style='thick') for b in self.borders } cell.border = Border(**kwa)
def __init__(self): self.__workbook = Workbook() self.highlight_style = PatternFill("solid", fgColor="2DCCD3") self.shading_style = PatternFill("solid", fgColor="E7E6E6") self.__font_reg = Font(name='Arial', size=8) self.__font_bold = Font(name='Arial', size=8, bold=True) self.__center_align = Alignment(horizontal="left", vertical="center") self.__align = Alignment(horizontal="center", vertical="center") self.__report_header = PatternFill("solid", fgColor="1E262E") self.__table_liner = PatternFill("solid", fgColor="E7E6E6") self.__thin_bottom = Border(bottom=Side(style='thin')) self.__thick_bottom = Border(bottom=Side(style='thick')) self.__thin_top = Border(top=Side(style='thin')) self.__thin_left = Border(left=Side(style='thin')) # calculate the excel alphabet from A to ZZZ alphabet = [] for letter in range(65, 91): alphabet.append(chr(letter)) self.extend_alphabet = [] self.extend_alphabet.extend(alphabet) full_alphabet = [] double_alphabet = [] for letter in range(65, 91): full_alphabet.append(chr(letter)) index = 0 while index < len(full_alphabet): for letter in full_alphabet: double_letters = "%s%s" % (full_alphabet[index], letter) double_alphabet.append(double_letters) self.extend_alphabet.append(double_letters) index += 1 index = 0 while index < len(double_alphabet): for letter in full_alphabet: triple_letters = "%s%s" % (double_alphabet[index], letter) self.extend_alphabet.append(triple_letters) index += 1
def set_border(ws, cell_range): # rows = ws.range(cell_range) thin_border = Border(left=Side(style='medium'), right=Side(style='medium'), top=Side(style='medium'), bottom=Side(style='medium')) for rows in ws[cell_range]: for cell in rows: cell.border = thin_border
def style_range(ws, cell_range, border=Border(), fill=None, font=None, alignment=None): """ Apply styles to a range of cells as if they were a single cell. :param ws: Excel worksheet instance :param range: An excel range to style (e.g. A1:F20) :param border: An openpyxl Border :param fill: An openpyxl PatternFill or GradientFill :param font: An openpyxl Font object """ top = Border(top=border.top) left = Border(left=border.left) right = Border(right=border.right) bottom = Border(bottom=border.bottom) first_cell = ws[cell_range.split(":")[0]] if alignment: ws.merge_cells(cell_range) first_cell.alignment = alignment rows = ws[cell_range] if font: first_cell.font = font for cell in rows[0]: cell.border = cell.border + top for cell in rows[-1]: cell.border = cell.border + bottom for row in rows: l = row[0] r = row[-1] l.border = l.border + left r.border = r.border + right if fill: for c in row: c.fill = fill
def set_border(ws, cell_range): border = Border(left=Side(border_style='thin', color='000000'), right=Side(border_style='thin', color='000000'), top=Side(border_style='thin', color='000000'), bottom=Side(border_style='thin', color='000000')) rows = ws.iter_rows(cell_range) for row in rows: for cell in row: cell.border = border
def __export_to_xlsx_steps(work_book, features): """stylborder son los bordes de la tablita""" styleborder = Style(font=Font(bold=False), border=Border(top=Side(border_style='thin', color=colors.BLACK), left=Side(border_style='thin', color=colors.BLACK), bottom=Side(border_style='thin', color=colors.BLACK), right=Side(border_style='thin', color=colors.BLACK))) #tomamos los steps del .feature y demas steps = _gather_steps(features) #nos paramos en la pestana activa work_sheet = Workbook.get_active_sheet(work_book) # y le ponemos el titulo work_sheet.title = 'Execution Steps' #nos paramos en la primer celda row_index = 1 work_sheet['A1'].style = styleborder work_sheet['A1'].value = 'Step' work_sheet['B1'].style = styleborder work_sheet['B1'].value = 'Ocurrencias' work_sheet['C1'].style = styleborder work_sheet['C1'].value = 'Ejecuciones' work_sheet['D1'].style = styleborder work_sheet['D1'].value = 'Tiempo promedio' work_sheet['E1'].style = styleborder work_sheet['E1'].value = 'Tiempo total' work_sheet['F1'].style = styleborder work_sheet['F1'].value = 'Scenarios' #por cada columna le agrega los datos y steps, etc for step in sorted(steps): cell = work_sheet.cell(row=row_index + 1, column=1) cell.value = step cell.style = styleborder cell.offset(column=1).value = steps[step]['appearances'] cell.offset(column=1).style = styleborder cell.offset(column=2).value = steps[step]['quantity'] cell.offset(column=2).style = styleborder cell.offset(column=3).value = '%.2fs' % \ (steps[step]['total_duration'] / (steps[step]['quantity'] or 1)) cell.offset(column=3).style = styleborder cell.offset(column=4).value = '%.2fs' % steps[step]['total_duration'] # cell.offset(column=5).value = steps[step]['scenario'] # cell.offset(column=5).style = styleborder cell.offset(column=4).style = styleborder if len(step) > work_sheet.column_dimensions['A'].width: work_sheet.column_dimensions['A'].width = len(step) work_sheet.column_dimensions['B'].width = 10 work_sheet.column_dimensions['C'].width = 10 work_sheet.column_dimensions['D'].width = 10 work_sheet.column_dimensions['E'].width = 10 # work_sheet.column_dimensions['F'].width = 20 row_index += 1
def lambda_handler(event, context): url = ('https://newsapi.org/v2/top-headlines?' 'sources=bbc-news,al-jazeera-english,cnn&' 'apiKey=559f97a41d574b989609df347b5adfa8') cd = requests.get(url) logger.info(cd.text) logger.info("After output from lambda") headlines = json.loads(cd.text)['articles'] file_name_in_s3 = "Headlines_at_" + (str(dtt.now())) + ".csv" file_name = "Headlines_at_" + (str(dtt.now())) + ".csv" file_path = '/tmp/' + file_name thin_border = Border(left=Side(style='thin'), right=Side(style='thin'), top=Side(style='thin'), bottom=Side(style='thin')) alignment = Alignment(wrap_text=True) with open(file_path, 'wb') as file_name: book = Workbook() sheet = book.active ft = Font(color=colors.BLACK, bold=True) sheet.title = "Headlines" sheet['A1'] = "Source" sheet['A1'].font = ft sheet.column_dimensions['A'].width = 28 sheet['B1'] = "Timestamp" sheet['B1'].font = ft sheet.column_dimensions['B'].width = 28 sheet['C1'] = "PublishedAt" sheet['C1'].font = ft sheet.column_dimensions['C'].width = 28 sheet['D1'] = "Headline" sheet['D1'].font = ft sheet.column_dimensions['D'].width = 50 counter = 2 for length, value in enumerate(headlines, start=2): headline = [] headline.extend([ value['source']['name'], str(dtt.now()), value['publishedAt'], value['description'] ]) for i, j in enumerate(headline, start=1): sheet.cell(row=length, column=i).value = j sheet.cell(row=length, column=i).border = thin_border if headline[-1] == j: val = 'D' + str(counter) print(val) print("after val") sheet[val].alignment = alignment counter += 1 book.save(file_name) s3 = boto3.resource('s3') s3.meta.client.upload_file(file_path, 'neo-apps-procoure.ai', file_name_in_s3) logger.info("uploaded successfully")
def __init__(self): self.__sheet_names = [] self.__white_highlight = PatternFill("solid", fgColor="FFFFFF") self.__yellow_highlight = PatternFill("solid", fgColor="FFFF00") self.__font_reg = Font(name='Verdana', size=8) self.__font_title = Font(name="Calibri (Body)", size=11) self.__font_title_bold = Font(name='Verdana', size=8, bold=True) self.__font_hyper = Font(name="Arial", size=10, color="0563C1", underline='single') self.__center_align = Alignment(horizontal="center", vertical="center") self.__center_top = Alignment(horizontal="center", vertical="top") self.__all_align = Alignment(horizontal="left", vertical="top", wrapText=True) self.__thick_sides = Border(right=Side(style='thick'), left=Side(style='thick')) self.__thick_top = Border(top=Side(style='thick')) self.__thick_all = Border(top=Side(style='thick'), bottom=Side(style='thick'), right=Side(style='thick'), left=Side(style='thick')) # calculate the excel alphabet from A to ZZ alphabet = [] for letter in range(65, 91): alphabet.append(chr(letter)) self.extend_alphabet = [] self.extend_alphabet.extend(alphabet) full_alphabet = [] double_alphabet = [] for letter in range(65, 91): full_alphabet.append(chr(letter)) index = 0 while index < len(full_alphabet): for letter in full_alphabet: double_letters = "%s%s" % (full_alphabet[index], letter) double_alphabet.append(double_letters) self.extend_alphabet.append(double_letters) index += 1
def format_title(self, cell): cell.alignment = Alignment(wrap_text=True, horizontal="center", vertical="center") cell.font = Font(name="Times New Roman", size=10, bold=True) cell.border = Border( left=Side(style="thin"), right=Side(style="thin"), top=Side(style="thin"), bottom=Side(style="thin"), )
def style(ws, value, row, column, color): ws.cell(row=row, column=column).value = value ws.cell(row=row, column=column).font = Font(bold=True, size=12) ws.cell(row=row, column=column).fill = PatternFill("solid", fgColor=color) ws.cell(row=row, column=column).border = Border(left=Side(style='thin'), right=Side(style='thin'), top=Side(style='thin'), bottom=Side(style='thin')) ws.column_dimensions['B'].width = 20 ws.column_dimensions['C'].width = 20 ws.column_dimensions['D'].width = 20
def add_border(ws, row, column, left=None, right=None, bottom=None, top=None): old = ws.cell(row=row, column=column).border if left == None: left = old.left if right == None: right = old.right if bottom == None: bottom = old.bottom if top == None: top = old.top ws.cell(row=row, column=column).border = Border(left=left, right=right, bottom=bottom, top=top, diagonal=old.diagonal, diagonal_direction=old.diagonal_direction, vertical=old.vertical, horizontal=old.horizontal)
def borders(self, sheet): thin_border = Border(left=Side(style='thin'), right=Side(style='thin'), top=Side(style='thin'), bottom=Side(style='thin')) for row in sheet.iter_rows(min_row=sheet.min_row, min_col=sheet.min_column, max_row=sheet.max_row, max_col=sheet.max_column): for cell in row: cell.border = thin_border
def set_thin_border(workbook): ws = workbook.active a1 = ws['A1'] thin_border = Border(left=Side(style='thin'), right=Side(style='thin'), top=Side(style='thin'), bottom=Side(style='thin')) a1.border = thin_border return a1
def mark_seat(sheet, c, r): thin_border = Border(left=Side(border_style=BORDER_THIN, color='00000000'), right=Side(border_style=BORDER_THIN, color='00000000'), top=Side(border_style=BORDER_THIN, color='00000000'), bottom=Side(border_style=BORDER_THIN, color='00000000')) sheet.cell(row=11, column=10).border = thin_border print('Mark Seat @', c, r) sheet.cell(row=c, column=r, value='x') return
def formatBook(self): ''' Merges and aligns cells for presentation. ''' thickBorder = Border(left=Side(border_style=BORDER_THICK, color='00000000')) cellsForMerge = ['C2:D3', 'E2:E3', 'C7:D8', 'E7:E8',\ 'C12:D13', 'E12:E13', 'C17:D18', 'E17:E18'] for cellPair in cellsForMerge: self.workSheet.merge_cells(cellPair) for rowIndex in range(17): self.workSheet.cell(row=rowIndex + 2, column=3).border = thickBorder self.workSheet.cell(row=rowIndex + 2, column=6).border = thickBorder
def Form_t():# - Form T wages slip form_data = data.copy() # will be passed inside the func # load form formPath = os.path.join(tnfilespath,'Form T wages slip.xlsx') formfile = load_workbook(filename=formPath) # select sheet to write in and set page properties formsheet = formfile['Sheet1'] formsheet.sheet_properties.pageSetUpPr.fitToPage = True # for each employee create a new sheet for idx in form_data.index: empName = form_data.loc[idx, "Employee Name"] print("Employee:", empName) # create a new sheet for employee new = formfile.copy_worksheet(formsheet) new.title = empName # popoluate every cell with required employee information new.cell(row= 4, column=3, value=form_data.loc[idx, "Company Name"])# Name of company new.cell(row= 5, column=3, value=form_data.loc[idx, "Employee Name"]) #. name of employee new.cell(row= 6, column=3, value=form_data.loc[idx, "Father's Name"]) #. Fathers name new.cell(row= 7, column=3, value=form_data.loc[idx, "Designation"]) # employee designation C7 new.cell(row= 8, column=3, value=form_data.loc[idx, "Date Joined"]) # date joined C8 new.cell(row= 11, column=2, value=form_data.loc[idx, "Earned Basic"]) # earned basic B11 new.cell(row= 13, column=2, value=form_data.loc[idx, "HRA"])# House rent allowance B13 new.cell(row= 14, column=2, value=form_data.loc[idx, "Overtime"]) # overtime wages B14 new.cell(row= 16, column=2, value=form_data.loc[idx,"Other Allowance"]) # otehr allowance B16 new.cell(row= 17, column=2, value=form_data.loc[idx, "FIXED MONTHLY GROSS"]) # Gross wages B17 new.cell(row= 12, column=9, value=form_data.loc[idx, "Insurance"]) # employee state insurance I12 new.cell(row= 13, column=9, value=form_data.loc[idx, "Other Deduction"]) # other deductions I13 new.cell(row= 17, column=7, value=form_data.loc[idx, "Net Paid"]) # net paid #G17 # format table A1 - K19 for row_id in range(1,20): for col_id in range(1,12): new.cell(row=row_id, column=col_id).border = Border(left=Side(style='medium'), right=Side(style='medium'), top=Side(style='medium'), bottom=Side(style='medium')) # delete sheet1 sheet2, sheet3 formfile.remove(formfile['Sheet1']) formfile.remove(formfile['Sheet2']) formfile.remove(formfile['Sheet3']) # save file formIfinalfile = os.path.join(outputPath,'Form T wages slip.xlsx') formfile.save(filename=formIfinalfile)
def __format_ws_so__(ws, cell_range): border = Border(left=Side(border_style='thin', color='000000'), right=Side(border_style='thin', color='000000'), top=Side(border_style='thin', color='000000'), bottom=Side(border_style='thin', color='000000')) rows = ws_so[cell_range] for row in rows: for cell in row: cell.border = border
def set_border(filename): side = Side(style='thin', color='000000') border = Border(top=side, bottom=side, left=side, right=side) wb = xl.load_workbook(filename=filename) ws = wb.worksheets[0] for row in ws: for cell in row: ws[cell.coordinate].border = border wb.save(filename)
def createsheet(filename): ws['J16'] = datum ws['J17'] = offertnr ws['A25'] = 'Buitenvloer' ws['B26'] = r'- Opmerkingen' ws['H25'] = 8 ws['I25'] = 'x' ws['J25'] = 5 first = ws['H25'] second = ws['J25'] ws['K25'] = first.value * second.value cell = ws['K25'] cell.number_format = '€ ##,##' cell = ws['J25'] cell.number_format = '€ ##,##' cell = ws['H25'] cell.number_format = '#,## u' #ws['G13'] = ws['A16'].value top_border = Border(top=Side(style='thick')) bottom_border = Border(bottom=Side(style='thick')) #ws['G13'].border = top_border #for i in range(items): # additem(i) sumarr = [[[], [], [], [], []], [[], [], [], [], []], [[], [], [], [], []]] # 3x5 for i in range(5): # create array met totaalberekening, einde for b in range(3): if i == 0 or i == 4: sumarr[b][i] = ws[listsumlet[i] + str(b + 47)].value img = pxl.drawing.image.Image( r'C:\Users\Tom\Documents\Offerte\image001.jpg') ws.add_image(img, 'A3') wb.save(filename)