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/%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 cover_sheet(_self): ws = _self.wb.create_sheet("Instructions") ws["A1"] = "Instructions" ws["A1"].font = Font(bold=True, size=14) ws["A2"] = "Thank you for providing your data to AMCU! Capturing high quality data is vitally important to strengthening aid effectiveness in Liberia." ws["A2"].font = Font(size=14) ws["A2"].alignment = Alignment(wrap_text=True, vertical="top") ws.merge_cells("A2:K4") ws["A6"] = "Currency" ws["A6"].font = Font(bold=True, size=14) ws["A7"] = "Please provide your data in the currency stated above. Please contact AMCU if you would like this template in a different currency (your existing data will be exported in your desired currency to ensure consistency)." ws["A7"].font = Font(size=14) ws["A7"].alignment = Alignment(wrap_text=True, vertical="top") ws.merge_cells("A7:K10") ws["C6"] = _self.template_currency ws["C6"].font = Font(size=14) yellow_fill = PatternFill(start_color='FFFF00', end_color='FFFF00', fill_type='solid') thin_border = Border(left=Side(style='thin'), right=Side(style='thin'), top=Side(style='thin'), bottom=Side(style='thin')) ws["C6"].fill = yellow_fill ws["C6"].border = thin_border ws["A12"] = "How to fill in this template" ws["A12"].font = Font(bold=True, size=14) ws["A13"] = "On the next sheet, you will see a list of projects currently known to AMCU. Fill out the sheet as follows (if you have any further questions, contact AMCU):" ws["A13"].font = Font(size=14) ws["A13"].alignment = Alignment(wrap_text=True, vertical="top") ws.merge_cells("A13:K15") return ws
def _new_border(di, dj, w, h): left = Side(border_style='thick') if di == 0 else None right = Side(border_style='thick') if di == w - 1 else None top = Side(border_style='thick') if dj == 0 else None bottom = Side(border_style='thick') if dj == h - 1 else None border = Border(left=left, right=right, top=top, bottom=bottom) return border
def blackBorderLine(self): 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')) return border
def sheetwrite(numlist,datelist,fiolist,cellist,tinlist,toutlist,primlist): wb = openpyxl.load_workbook(filename = filexls) sheet = wb['Коленков'] #выбираем лист sheet [('A'+str(len(numlist)+1))] = str(len(numlist)) sheet [('B'+str(len(numlist)+1))] = datelist sheet [('C'+str(len(numlist)+1))] = fiolist sheet [('D'+str(len(numlist)+1))] = cellist sheet [('E'+str(len(numlist)+1))] = tinlist sheet [('F'+str(len(numlist)+1))] = toutlist sheet [('G'+str(len(numlist)+1))] = primlist for s in ["A","C","E","F"]: work_sheet = sheet [(s+str(len(numlist)+1))] work_sheet.fill = PatternFill(fill_type='solid', start_color='b5e9ff', end_color='b5e9ff')#Данный код позволяет делать оформление цветом ячейки for s in ["B","D","G"]: work_sheet = sheet [(s+str(len(numlist)+1))] work_sheet.fill = PatternFill(fill_type='solid', start_color='ffd88f', end_color='ffd88f')#Данный код позволяет делать оформление цветом ячейки thin_border = Border(left=Side(style='thin'), right=Side(style='thin'), top=Side(style='thin'), bottom=Side(style='thin')) for s in ["A","B","C","D","E","F","G"]: work_sheet = sheet [(s+str(len(numlist)+1))] work_sheet.border = thin_border try: wb.save (filexls) print ("Журнал посещений сохранен и заполнен.") except: wb.save (filexls+"_unsaved.xlsx") text = "Не удалось сохранить журнал посещений, так как он уже используется. Копия сохранена в файл "+filexls+"_unsaved.xlsx" ctypes.windll.user32.MessageBoxW(0, text, "Журнал учета посещений by Xottabb14", 0)
def enterSheet(workOrder, wsDicty, workOrders): ws = wsDicty[workOrder] sX = 1 sY = 1 thin_border = Border(left=Side(style='thin'), right=Side(style='thin'), top=Side(style='thin'), bottom=Side(style='thin')) for ticket in workOrders[workOrder]: sX = 1 ws.cell(row=sY, column=sX, value=ticket) sY += 1 sX += 1 for response in workOrders[workOrder][ticket]: for cell in response: ws.cell(row=sY, column=sX, value=cell) ws[returnCoordString(sX, sY)].alignment = Alignment( wrap_text=True, horizontal='left', vertical='top') ws[returnCoordString(sX, sY)].border = thin_border sX += 1 sY += 1 sX = 2
def __init__(self): self.header_fill = openpyxl.styles.GradientFill( stop=(self.header_background_color_start, self.header_background_color_end)) self.header_font = openpyxl.styles.Font(name=self.font_name, bold=True, color=self.header_color) self.org_fill = openpyxl.styles.GradientFill( stop=(self.original_background_color_start, self.original_background_color_end)) self.org_font = openpyxl.styles.Font(name=self.font_name, color=self.original_color) self.trans_fill = openpyxl.styles.GradientFill( stop=(self.translation_background_color_start, self.translation_background_color_end)) self.trans_font = openpyxl.styles.Font(name=self.font_name, color=self.translation_color) self.comment_fill = openpyxl.styles.GradientFill( stop=(self.comment_background_color_start, self.comment_background_color_end)) self.comment_font = openpyxl.styles.Font(name=self.font_name, color=self.comment_color) self.font = openpyxl.styles.Font(name=self.font_name) self.border = Border( left=Side(style='hair'), right=Side(style='hair'), top=Side(style='hair'), bottom=Side(style='hair'), )
def __write(self, ws, key: str, value, fill: PatternFill = PatternFill(), 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')), font_size: int = 9): """ワークシートの指定したキーにデータを書き込む Arguments: ws {} -- 対象のワークシート key {str} -- キー value {} -- 書き込む内容 Keyword Arguments: fill {PatternFill} -- 色の指定 (default: {PatternFill()}) border {Border} -- 罫線情報(default: {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'))}) font_size {int} -- フォントサイズ (default: {9}) """ ws[key] = value ws[key].fill = fill ws[key].border = border ws[key].font = Font(size=font_size)
def addSheet(wb, string_folder, filename, platform): #reduce the sheetname length new_sheetname = getSheetName(filename, platform) string_path = getStringPath(string_folder, filename, platform) #print 'create sheet for: ' + xml_path ws = wb.create_sheet(new_sheetname) thin_border = Border(left=Side(style='thin'), right=Side(style='thin'), top=Side(style='thin'), bottom=Side(style='thin')) if platform == "android": fillSheetAndroid(string_path, ws) else: fillSheetIOS(string_path, ws) # Create table title ws['A1'] = "TEXT ID" ws['B1'] = "Description" ws['C1'] = "EN" ws['D1'] = "VI" ws['E1'] = "JA" headerFill = PatternFill(start_color='FFFF5500', end_color='FFFF5500', fill_type='solid') ws['A1'].fill = ws['B1'].fill = ws['C1'].fill = ws['D1'].fill = ws[ 'E1'].fill = headerFill ws['A1'].border = ws['B1'].border = ws['C1'].border = ws['D1'].border = ws[ 'E1'].border = thin_border ws.column_dimensions['A'].width = ws.column_dimensions[ 'C'].width = ws.column_dimensions['D'].width = ws.column_dimensions[ 'E'].width = 50
def add_data_to_single_cell(self, sheet, data, row=1, column=1, font=None, number_format='text', border=True, wrap_text=True, align='left', valign='top'): thin_border = Border(left=Side(style='thin'), right=Side(style='thin'), top=Side(style='thin'), bottom=Side(style='thin')) cell = sheet.cell(row=row, column=column) cell.value = data if border: cell.border = thin_border cell.number_format = self.__cell_number_format[number_format] if font: cell.font = font cell.alignment = Alignment(horizontal=align, vertical=valign, wrapText=wrap_text) return sheet
def appendSSEStocks(self): ''' 向filePath指定表格中追加上交所上市公司信息 ''' wb = load_workbook(self.filePath) sheet = wb.active # print sheet.max_row # 设置单元格所有框线 thin_border = Border(left=Side(style='thin'), right=Side(style='thin'), top=Side(style='thin'), bottom=Side(style='thin')) # # 写标题 # for i in range(self.__indexName.__len__()): # _ = sheet.cell(column=i + 1, row=1, value=self.__indexName[i]) # 当前表格的信息行数 row = sheet.max_row for i in self.getSSEAShareListCompanyCode(): a = AchieveSSEStockInfo(i) if not a.getStatus(): continue for j in range(len(a.methodList)): m = a.methodList[j] f = getattr(a, m) _ = sheet.cell(column=j + 1, row=row, value="%s" % f()) sheet.cell(column=j + 1, row=row).border = thin_border row = row + 1 if int(i) % 100 == 0: wb.save(self.filePath) wb.save(self.filePath)
def __init__(self): self.thin_border = Border( left=Side(style='thin'), right=Side(style='thin'), top=Side(style='thin'), bottom=Side(style='thin'))
def createBorder(ws,rowind): for indx in range(2,maxcol + 1): for indy in range(4,rowind): ws.cell(row=indy,column=indx).border = Border(left=Side(border_style='thin',color='00000000'), right=Side(border_style='thin',color='00000000'), top=Side(border_style='thin',color='00000000'), bottom=Side(border_style='thin',color='00000000'))
def crear_reporte_pdf(): libro = openpyxl.Workbook(encoding="UTF-8", data_only=True) sheet_libromayor = crear_hoja_libro(libro, 'Reporte Libro Mayor', 0, 0) sheet_view = openpyxl.worksheet.SheetView() sheet_view.zoomScale = "50" sheet_view.zoomScaleNormal = "70" sheet_libromayor.sheet_view = sheet_view sheet_libromayor.zoomScale = "70" ajustes_hoja(sheet_libromayor, 0, 'A', 20.00, 5.00) alignment_title = Alignment(horizontal='center', vertical='center') fuente_cabecera = Font(bold=True, size=15, name='calibri') all_border = Border(left=Side(style='thin'), right=Side(style='thin'), top=Side(style='thin'), bottom=Side(style='thin')) dic = { 'fuente': fuente_cabecera, 'border': all_border, 'alineacion': alignment_title, 'sheet': sheet_libromayor, 'libro': libro } return dic
def add_data_to_single_cell(self, sheet, data, row=1, column=1, bold=False, number_format=False, border=True, delimer=False, wrap_text=True): thin_border = Border(left=Side(style='thin'), right=Side(style='thin'), top=Side(style='thin'), bottom=Side(style='thin')) cell = sheet.cell(row=row, column=column) cell.value = data if border: cell.border = thin_border if number_format: cell.number_format = '# ### ### ### ##0' if delimer: cell.number_format = '# ### ### ### ##0.##' if bold: cell.font = Font(bold=True) cell.alignment = Alignment(horizontal='left', vertical='top', wrapText=wrap_text) return sheet
def underlineRow(currentLine, start=1, end=14, grandTotal=False): for i in range(start, end): if grandTotal: ws.cell(row=currentLine, column=i + 1).border = Border(top=Side(style='double')) else: ws.cell(row=currentLine, column=i + 1).border = Border(bottom=Side(style='thin'))
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 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 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 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 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_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 __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 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 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 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