def data_test(stage): if (stage == 0) tflag = 0 tinc = 1 pwm = 1500 d = 0 t = time() log ['A'+str(i)] = "Stage 1 log start" i = i+1 log ['A'+str(i)] = "date/time" log ['B'+str(i)] = "Diff. PWM" log ['C'+str(i)] = "ang vel" i = i+1 while (tflag = 1): if d > 400: break d = d+30 while (abs(time () - t) < tinc): send_PWM(serthr , (pwm +d) , (pwm-d)) wb = Workbook() log = wb.active() log ['A'+str(i)] = datetime.datetime.now() log ['B'+str(i)] = d g = ahrs.gyro() log ['C'+str(i)] = g[2] log ['D'+str(i)] = ang_acc() i = i+1
def append_grade_values(self, filename, sheet_title): if not os.path.exists(filename): work_book = Workbook() work_sheet = work_book.active work_sheet.title = sheet_title else: work_book = load_workbook(filename) work_book.create_sheet(sheet_title) work_book.active = len(work_book.sheetnames) - 1 work_sheet = work_book.active work_sheet.title = sheet_title ws_stat_name_index = ["A1", "C1", "A2", "C2", "A3", "C3", "A4"] ws_status_index = ["B1", "D1", "B2", "D2", "B3", "D3", "B4"] counter_index = 0 for each_status_name in self.school_status_name: work_sheet[ws_stat_name_index[counter_index]] = each_status_name work_sheet[ws_stat_name_index[counter_index]].font = Font( bold=True) counter_index = counter_index + 1 counter_index = 0 for each_status in self.school_status: work_sheet[ws_status_index[counter_index]] = each_status work_sheet[ws_status_index[counter_index]].font = Font(bold=False) counter_index = counter_index + 1 col_letters = "ABCDEFGHIJKLMNOPQRSTUVWXYZ" work_sheet.append([]) work_sheet.append(self.table_columns) for cols in range(0, len(self.table_columns)): work_sheet["%s%s" % (col_letters[cols], 6)].font = Font(bold=True) for each_rows in self.table_rows: work_sheet.append(each_rows) work_book.save(filename)
def export_all_project_reports_as_one_csv_file(work_book: Workbook, writer: _writer) -> None: hours_column_setting: ColumnSettings = constants.HEADERS_TO_COLUMNS_SETTINGS_FOR_USER_IN_PROJECT.value[ constants.HOURS_HEADER_STR.value] for sheet_index in range(len(work_book.sheetnames)): work_book.active = sheet_index save_work_book_as_csv(writer, work_book, hours_column_setting)
def makeCurveChart(wb: xl.Workbook, data_refs: DataRefType, order: List[str], definition: dict) -> None: name = __get_new_sheetname(wb, definition[__NAME]) curve_sheet = wb.create_sheet(name) expanded_refs = __makeSummary(data_refs) __writeCurveChart(curve_sheet, expanded_refs, order, **definition[__DEFINITION]) wb.active = wb.index(wb[name])
def test_set_active_by_index(Workbook): wb = Workbook() names = ['Sheet', 'Sheet1', 'Sheet2',] for n in names: wb.create_sheet(n) for idx, name in enumerate(names): wb.active = idx assert wb.active == wb.worksheets[idx]
def makeBDBRMatrix(wb: xl.Workbook, data_refs: DataRefType, order: List[str], definition: dict) -> None: name = __get_new_sheetname(wb, definition[__NAME]) bdbrm_sheet = wb.create_sheet(name) expanded_refs = __makeSummary(data_refs, definition[__DEFINITION][__LAYERS]) __writeBDBRMatrix(bdbrm_sheet, expanded_refs, order, **definition[__DEFINITION]) wb.active = wb.index(wb[name])
def test_set_active_by_sheet(Workbook): wb = Workbook() names = ['Sheet', 'Sheet1', 'Sheet2',] for n in names: wb.create_sheet(n) for n in names: sheet = wb[n] wb.active = sheet assert wb.active == wb[n]
def create_heroes_list_worksheet(wb: Workbook, heroes_list): ws = wb.create_sheet("Heroes List") wb.active = ws for i, hero in enumerate(heroes_list): name_cell = ws['A' + str(i + 1)] name_cell.value = hero[0] name_cell.font = MODIFIABLE_CELLS_FONT role_cell = ws['B' + str(i + 1)] role_cell.value = hero[1] role_cell.font = MODIFIABLE_CELLS_FONT
def excel(): # Создаем Excel файл wb = Workbook() ws = wb.create_sheet('ФИО', 0) wb.active = 0 row = 1 wb.active["A" + str(row)] = 'Фамилия' wb.active["B" + str(row)] = 'Имя' wb.active["C" + str(row)] = 'Отчество' for x, y in dict_fio.items(): row += 1 wb.active["A" + str(row)] = y[0] wb.active["B" + str(row)] = y[1] wb.active["C" + str(row)] = y[2] wb.save('filename.xlsx')
def wikiTblToExcel(wikiTblList): tblCount=0 wb=Workbook() for tblInd,tbl in zip(range(len(wikiTblList)),wikiTblList): caption=tbl.caption if caption != None and caption != u"": shtName=captionToExcel(caption) else: shtName = "Sheet"+str(tblInd) ws=wb.create_sheet(tblInd, shtName) # set the new sheet as active wb.active=wb.get_index(ws) tblStyle=procStyle(tbl) for rowCount,row in zip(range(len(tbl.rows)),tbl.rows): trToExcel(ws,row,rowCount,tblStyle) return wb
def wikiTblToExcel(wikiTblList): tblCount = 0 wb = Workbook() for tblInd, tbl in zip(range(len(wikiTblList) + 1), wikiTblList): caption = tbl.caption if caption != None and caption != u"": shtName = captionToExcel(caption) else: shtName = "Sheet" + str(tblInd + 1) ws = wb.create_sheet(index=tblInd + 1, title=shtName) # set the new sheet as active wb.active = wb.get_index(ws) tblStyle = procStyle(tbl) for rowCount, row in zip(range(len(tbl.rows)), tbl.rows): trToExcel(ws, row, rowCount, tblStyle) return wb
def cvt_xls_to_xlsx(src_file_path, dst_file_path): book_xls = xlrd.open_workbook(src_file_path) book_xlsx = Workbook() sheet_names = book_xls.sheet_names() for sheet_index in range(0, len(sheet_names)): sheet_xls = book_xls.sheet_by_name(sheet_names[sheet_index]) if sheet_index == 0: sheet_xlsx = book_xlsx.active() sheet_xlsx.title = sheet_names[sheet_index] else: sheet_xlsx = book_xlsx.create_sheet(title=sheet_names[sheet_index]) for row in range(0, sheet_xls.nrows): for col in range(0, sheet_xls.ncols): sheet_xlsx.cell(row=row + 1, column=col + 1).value = sheet_xls.cell_value( row, col) book_xlsx.save(dst_file_path)
async def as_xlsx(max_rows=None, debug=False): """Export des données au format souhaité par la DGT. :max_rows: Max number of rows to process. :debug: Turn on debug to be able to read the generated Workbook """ print("Reading from DB") records = await db.declaration.completed() print("Flattening JSON") if max_rows: records = records[:max_rows] wb = Workbook(write_only=not debug) ws = wb.create_sheet() ws.title = "BDD REPONDANTS" wb.active = ws ws_ues = wb.create_sheet() ws_ues.title = "BDD UES détail entreprises" ws_ues.append([ "Annee_indicateurs", "Region", "Departement", "Adresse", "CP", "Commune", "Tranche_effectif", "Nom_UES", "Siren_entreprise_declarante", "Nom_entreprise_declarante", "Nom_entreprise", "Siren", ]) headers, columns = await get_headers_columns() ws.append(headers) bar = ProgressBar(prefix="Computing", total=len(records)) for record in bar.iter(records): data = record.data if not data: continue ues_data(ws_ues, data) data = prepare_record(data) data["modified_at"] = record["modified_at"] ws.append([clean_cell(fmt(data.get(c))) for c, fmt in columns]) return wb
def main(): ## 创建excel文件、sheet wb = Workbook() ws = wb.active() #默认创建的sheet ws1 = wb.create_sheet(0) ws = wb['worksheet1'] # 选中工作表 ## sheet属性操作 ws.title = "name" ## 单元操作 c = ws['A4'] ws['A4'] = 4 d = ws.cell(row=4, column=2) cell_range = ws['A1':'C2'] ## excel打开、保存 wb = load_workbook(filename="sample.xlsx") wb.save("output_sample.xlsx")
def BorsdataUpdateHistAndCurr(df, *, strategy): histPath = 'Data\Borsdata_' + strategy + '_Historik.xlsx' currPath = 'Data\Borsdata_' + strategy + '_Current.xlsx' curr_date = str(datetime.datetime.now().date()) try: wb = load_workbook(histPath) except FileNotFoundError: wb = Workbook() try: #Removes curr_date sheet if it exists otherwise creates a new one. wb.remove(wb[curr_date]) except Exception: print() finally: wb.create_sheet(curr_date) wb.active = wb[curr_date] ws = wb.active for r in dataframe_to_rows(df, index=False, header=True): ws.append(r) for cell in ws[1]: #Adds pandas style to first row (headers) cell.style = 'Pandas' for col in ws.columns: #Fixes column width to an appropriate size. max_length = 0 column = col[0].column_letter for cell in col: try: #Avoids error on empty cells. if len(str(cell.value)) > max_length: max_length = len(cell.value) except: pass adjusted_width = (max_length + 2) * 1.1 ws.column_dimensions[column].width = adjusted_width wb.save(histPath) wb_current = load_workbook(histPath) #Loads the historical data file, sheets = wb_current.sheetnames #removes all sheets except curr_date for s in sheets: #and saves it as Borsdata_strategy_Current.xlsx if s != curr_date: sheet = wb[s] wb.remove(sheet) wb.save(currPath) return
def guardar_informacion(datos_encontrados, organizacion): libro = Workbook() hoja = libro.create_sheet(organizacion) libro.save("Hunter" + organizacion + ".xlsx") libro.active = 1 excel = libro.active excel.append(('Correo Electronico', 'Tipo', 'Nombre', 'Apellido', 'Telefono', 'Linkedin', 'Twitter')) count = 2 emails = datos_encontrados['emails'] for x in emails: excel.cell(count, 1, x['value']) excel.cell(count, 2, x['type']) excel.cell(count, 3, x['first_name']) excel.cell(count, 4, x['last_name']) excel.cell(count, 5, x['phone_number']) excel.cell(count, 6, x['linkedin']) excel.cell(count, 7, x['twitter']) count += 1 libro.save("Hunter" + organizacion + ".xlsx")
def save_excel(self, sheet="工作表", file="list.xlsx"): if os.path.exists(file): print("文件存在,打开") else: print("文件不存在,新建") wb = Workbook() wb.save(file) wb = load_workbook(file) sheets = wb.sheetnames if sheet in sheets: wb.remove(wb[sheet]) wb.create_sheet(sheet) wb.active = wb[sheet] ws = wb.active ws.append(["名称", "内容", "评分", "一句话评语"]) for item in self.list: ws.append([item.name, item.content, item.score, item.say]) wb.save(file) print("文件已保存。")
def racadm(): filepath = "/root/racadm.xlsx" wb = Workbook() sheet = wb.active() if path.exists('/opt/dell/srvadmin/sbin/racadm') == False: print("racadm binary doesnt exists") with open('/root/racadm') as f: content = f.readlines() for line in content: sut = subprocess.Popen(line, shell=True, stdin=subprocess.PIPE, stdout=subprocess.PIPE) sut1 = sut.communicate() data = [(line, str(sut1))] for row in data: sheet.append(row) if sut.wait() != 0: print line, sut1 else: print "Success", line wb.save(filepath)
# Creating workbooks and worksheets wb = Workbook() # to create an empty Workbook object ws1 = wb.create_sheet('London') ws2 = wb.create_sheet( 'Madrid', 0) # creating a new sheet; parameters: sheet name + index # Worksheet operations wb.copy_worksheet(ws1) wb.create_sheet('Delete') ws = wb['Delete'] wb.remove(ws) # deleting a worksheet # Setting active worksheet wb.active = wb['Madrid'] ws = wb.active # Dummy data for i in range(1, 20): # looping through the first 19 rows ws.append( range(300)) # filling the first 300 column with values from 0 to 299 # Rows and columns ws.delete_rows(15, 2) # deleting 2 rows starting from 15 ws.delete_cols(17, 3) # deleting columns Q, R and S ws.insert_rows(15, 2) # inserting two rows at the 15th row ws.insert_cols(17, 3) # inserting three columns at the 17th column # Moving a range
def render_xls(context, **kwargs): """ """ ################################################################################ # # # Conversation with myself # # ------------------------ # # # # Oh snap, all this Python just to # # put a few values into a spreadsheet # # without running Excel? # # A thousand times yes. # # # # # # But don't you still have to run Excel # # to test your output? # # # # Soul-crushing pain # # only occurs with # # prolonged use of # # Excel. This house # # is clean. # # # # Here we go again. So you're going # # to write all this Python # # to avoid manually entering a few cells # # in Excel? # # # # My kingdom to avoid Excel! # # # # This is it! Finally nearing the end. # # Was it worth it? # # # # Weeping silent tears of joy, yes!! # # # # What have we learned? # # # # Python4life. # # # # No copies of Excel were harmed during the writing # # of this code, but many expletives were used. # # # # # ################################################################################ bold = Font(bold=True) border = Side(border_style="thin", color="000000") filename = kwargs.get("filename") item = context["item"] workbook = Workbook() ################################################################################ # Sheet 1 # ################################################################################ sheet1 = workbook.active sheet1.title = "Instructions" # https://stackoverflow.com/a/14450572 sheet1.append(["Instructions".upper()]) sheet1.column_dimensions["B"].width = 111.7 # Merge cells sheet1.merge_cells("A1:Z1") sheet1.append([ "", "This template is being provided as a tool to assist the acquisition workforce in developing an IGCE for a Firm Fixed Price Product or Service.", ]) sheet1.append([]) sheet1.append([ "1.", "The exact amount of a vendor’s quote must NOT be used as the basis for a program office’s IGCE.", ]) sheet1.append([ "2.", "The program office must conduct all necessary research to compile an accurate and complete IGCE, independent of the vendor’s pricing/cost information.", ]) sheet1.append([ "3.", "The program office should use the results of the market reaearch to substaniate the IGCE.", ]) sheet1.append([ "4.", "The program office provide a narrative to document the basis of the IGCE.", ]) ################################################################################ # Sheet 2 # ################################################################################ sheet2 = workbook.create_sheet(title="FFP IGCE") sheet2.column_dimensions["A"].width = 48 column_index = 2 letter_start = "B" entries = item.time_set.all() ######### # Row 1 # ######### sheet2.append(["FFP IGCE"]) ######### # Row 2 # ######### sheet2.append(["Title:".upper(), item.subject]) ######### # Row 3 # ######### sheet2.append(["Detailed Price Summary"]) # Bold cells, set border sheet2["A1"].font = bold sheet2["A2"].font = bold sheet2["A3"].font = bold sheet2["A3"].border = Border(bottom=border) ######### # Row 4 # ######### count = 1 row_4_col_data = [] time_set_count = item.time_set.count() for i in range(time_set_count): row_4_col_data.append("Estimate %s" % str(count)) row_4_col_data.append("") row_4_col_data.append("") row_4_col_data.append("") count += 1 row_4_col_data.insert(0, "Contract Line Item Description") sheet2.append(row_4_col_data) # Bold cell sheet2["A4"].font = bold # Fill cells row_4_col_num = len(row_4_col_data) - 1 for cell in range(row_4_col_num): # https://stackoverflow.com/a/50209914 sheet2[get_column_letter(column_index + cell) + str(sheet2.max_row)].fill = PatternFill(start_color="D3D3D3", end_color="D3D3D3", fill_type="solid") # Merge cells merge = [] for cell in range(row_4_col_num): if (column_index + cell) % 4 == 1: merge.append(letter_start + str(sheet2.max_row) + ":" + get_column_letter(column_index + cell) + str(sheet2.max_row)) letter_start = get_column_letter(column_index + cell + 1) for cells in merge: sheet2.merge_cells(cells) # Bold cells and set border sheet2[get_column_letter(column_index) + str(sheet2.max_row)].font = bold for cell in range(row_4_col_num): sheet2[get_column_letter(column_index + cell) + str(sheet2.max_row)].border = Border(top=border, bottom=border) sheet2[get_column_letter(column_index + cell) + str(sheet2.max_row)].alignment = Alignment(horizontal="center", vertical="center") if (column_index + cell) % 4 == 1: sheet2[get_column_letter(column_index + cell + 1) + str(sheet2.max_row)].font = bold ######### # Row 5 # ######### row_5_col_data = [] for i in range(time_set_count): row_5_col_data.append("Quantity") row_5_col_data.append("Unit") row_5_col_data.append("Unit Price") row_5_col_data.append("Total Price") row_5_col_data.insert(0, "") sheet2.append(row_5_col_data) # Bold and center and border row_5_col_num = len(row_5_col_data) - 1 for cell in range(row_5_col_num): sheet2[get_column_letter(column_index + cell) + str(sheet2.max_row)].font = bold sheet2[get_column_letter(column_index + cell) + str(sheet2.max_row)].alignment = Alignment(horizontal="center", vertical="center") sheet2[get_column_letter(column_index + cell) + str(sheet2.max_row)].border = Border(bottom=border, right=border) sheet2.column_dimensions[get_column_letter(column_index + cell)].width = 12 ######### # Row 6 # ######### row_6_col_data = [] # Blank line for i in range(time_set_count): row_6_col_data.append("") row_6_col_data.append("") row_6_col_data.append("") row_6_col_data.append("") row_6_col_data.insert(0, "") sheet2.append(row_6_col_data) # Bold cells row_6_col_num = len(row_6_col_data) - 1 sheet2[get_column_letter(column_index) + str(sheet2.max_row)].font = bold for cell in range(row_6_col_num): sheet2[get_column_letter(column_index + cell + 1) + str(sheet2.max_row)].font = bold sheet2[get_column_letter(column_index + cell) + str(sheet2.max_row)].border = Border(bottom=border, right=border) ######### # Row 7 # ######### row_7_col_data = [] for entry in entries: row_7_col_data.append(entry.quantity) row_7_col_data.append(entry.unit) row_7_col_data.append(entry.unit_price) row_7_col_data.append(entry.total_price) row_7_col_data.insert(0, item.subject) sheet2.append(row_7_col_data) # https://openpyxl.readthedocs.io/en/stable/usage.html#using-formulae row_7_col_num = len(row_7_col_data) - 1 column_total = [] for cell in range(row_7_col_num): if (column_index + cell) % 4 == 1: column_total.append( get_column_letter(column_index + cell) + str(sheet2.max_row)) # Currency + formula for cell in range(row_7_col_num): if (column_index + cell) % 4 == 1: sheet2[ get_column_letter(column_index + cell) + str(sheet2.max_row)].number_format = FORMAT_CURRENCY_USD_SIMPLE sheet2[ get_column_letter(column_index + cell - 1) + str(sheet2.max_row)].number_format = FORMAT_CURRENCY_USD_SIMPLE sheet2[get_column_letter(column_index + cell) + str(sheet2.max_row)] = ("=%s*%s" % ( get_column_letter(column_index + cell - 1) + str(sheet2.max_row), get_column_letter(column_index + cell - 3) + str(sheet2.max_row), )) # Fill cells for cell in range(row_7_col_num): if (column_index + cell) % 4 == 1: sheet2[get_column_letter(column_index + cell) + str(sheet2.max_row)].fill = PatternFill( start_color="D3D3D3", end_color="D3D3D3", fill_type="solid") sheet2[get_column_letter(column_index + cell) + str(sheet2.max_row)].border = Border(bottom=border, right=border) ############ # Row 8-15 # ############ for line in range(0, 8): # Blank lines row_x_col_data = [] for i in range(time_set_count): row_x_col_data.append("") row_x_col_data.append("") row_x_col_data.append("") row_x_col_data.append("") row_x_col_data.insert(0, "") sheet2.append(row_x_col_data) # Fill more cells and set border + formula + currency row_x_col_num = len(row_x_col_data) for count in range(0, 8): for cell in range(row_x_col_num - 1): if (column_index + cell) % 4 == 1: sheet2[get_column_letter(column_index + cell) + str(sheet2.max_row - count)].fill = PatternFill( start_color="D3D3D3", end_color="D3D3D3", fill_type="solid") sheet2[get_column_letter(column_index + cell) + str(sheet2.max_row - count)] = ("=%s*%s" % ( get_column_letter(column_index + cell - 1) + str(sheet2.max_row - count), get_column_letter(column_index + cell - 3) + str(sheet2.max_row - count), )) sheet2[get_column_letter(column_index + cell) + str(sheet2.max_row - count)].number_format = FORMAT_CURRENCY_USD_SIMPLE sheet2[get_column_letter(column_index + cell) + str(sheet2.max_row - count)].border = Border(bottom=border, right=border) ########## # Row 16 # ########## sheet2.append(["Line Item Subtotal"]) # Fill cells and set border + formula + currency for cell in range(row_x_col_num - 1): if (column_index + cell) % 4 == 1: line_item_subtotal_formula = "=SUM(" for count in range(0, 9): line_item_subtotal_formula += "%s+" % ( get_column_letter(column_index + cell) + str(sheet2.max_row - count - 1)) line_item_subtotal_formula = line_item_subtotal_formula[:-1] line_item_subtotal_formula += ")" sheet2[get_column_letter(column_index + cell) + str(sheet2.max_row)].fill = PatternFill( start_color="D3D3D3", end_color="D3D3D3", fill_type="solid") sheet2[get_column_letter(column_index + cell) + str(sheet2.max_row)] = line_item_subtotal_formula sheet2[ get_column_letter(column_index + cell) + str(sheet2.max_row)].number_format = FORMAT_CURRENCY_USD_SIMPLE else: sheet2[get_column_letter(column_index + cell) + str(sheet2.max_row)].fill = PatternFill( start_color="00008B", end_color="00008B", fill_type="solid") sheet2[get_column_letter(column_index + cell) + str(sheet2.max_row)].border = Border(bottom=border, right=border) ########## # Row 17 # ########## sheet2.append(["Total Estimated Amount"]) # Bold cell sheet2["A" + str(sheet2.max_row)].font = bold # Fill cells for cell in range(row_x_col_num - 1): if (column_index + cell) % 4 == 1: sheet2[get_column_letter(column_index + cell) + str(sheet2.max_row)].fill = PatternFill( start_color="D3D3D3", end_color="D3D3D3", fill_type="solid") sheet2[get_column_letter(column_index + cell) + str(sheet2.max_row)] = "=%s" % get_column_letter( column_index + cell) + str(sheet2.max_row - 1) sheet2[ get_column_letter(column_index + cell) + str(sheet2.max_row)].number_format = FORMAT_CURRENCY_USD_SIMPLE else: sheet2[get_column_letter(column_index + cell) + str(sheet2.max_row)].fill = PatternFill( start_color="00008B", end_color="00008B", fill_type="solid") # Border sheet2[get_column_letter(column_index + cell) + str(sheet2.max_row)].border = Border(bottom=border, right=border) ########## # Row 18 # ########## sheet2.append(["Total Combined Amount".upper()]) # Bold cell sheet2["A" + str(sheet2.max_row)].font = bold # Fill cells for cell in range(row_x_col_num - 1): sheet2[get_column_letter(column_index + cell) + str(sheet2.max_row)].fill = PatternFill(start_color="00FF00", end_color="00FF00", fill_type="solid") # Border sheet2[get_column_letter(column_index + cell) + str(sheet2.max_row)].border = Border(bottom=border, right=border) # https://openpyxl.readthedocs.io/en/stable/usage.html#using-formulae sheet2["B" + str(sheet2.max_row)] = "=SUM(%s)" % "+".join(column_total) sheet2["B" + str(sheet2.max_row)].number_format = FORMAT_CURRENCY_USD_SIMPLE # Merge cells letter_start = "B" merge = [] count = 0 for cell in range(row_x_col_num - 1): if (column_index + cell) % 4 == 1: count += 1 merge.append(letter_start + str(sheet2.max_row) + ":" + get_column_letter(column_index + cell) + str(sheet2.max_row)) letter_start = get_column_letter(column_index + cell + 1) for cells in merge: sheet2.merge_cells(cells) ########## # Row 19 # ########## sheet2.append(["Total Average Value".upper()]) # Bold cell sheet2["A" + str(sheet2.max_row)].font = bold # Fill cells for cell in range(row_x_col_num - 1): sheet2[get_column_letter(column_index + cell) + str(sheet2.max_row)].fill = PatternFill(start_color="00FF00", end_color="00FF00", fill_type="solid") # Border sheet2[get_column_letter(column_index + cell) + str(sheet2.max_row)].border = Border(bottom=border, right=border) sheet2["E" + str(sheet2.max_row)] = "=SUM((%s)/%s)" % ( "+".join(column_total), count, ) sheet2["E" + str(sheet2.max_row)].number_format = FORMAT_CURRENCY_USD_SIMPLE ########## # Row 20 # ########## sheet2.append(["Narrative:"]) # Bold cell sheet2["A" + str(sheet2.max_row)].font = bold ########## # Row 21 # ########## sheet2.append([""]) ########## # Row 22 # ########## notes = item.note.all() # Get narrative from first related note title if len(notes) > 0: sheet2.append([notes[0].title]) else: sheet2.append([ "The government estimates the cost of the Confocal Laser Scanning Microscope with the features essential to the programs needs is $26730.\n\nThe estimate is based upon the comparison the published commercial price for a Confocal Laser Scanning Microscope of similar features and functionality from three (3) major manufacturers." ]) ########## # Row 23 # ########## sheet2.append([""]) ############# # Row 24-31 # ############# count = 1 for entry in entries: sheet2.append(["Estimate %s—%s" % (str(count), entry.description)]) sheet2["A" + str(sheet2.max_row)].font = bold sheet2.append([item.subject]) sheet2.append([entry.total_price]) sheet2["A" + str(sheet2.max_row)].number_format = FORMAT_CURRENCY_USD_SIMPLE count += 1 sheet2["B2"].font = bold response = HttpResponse(content_type="xlsx") response["Content-Disposition"] = "attachment; filename=%s" % filename workbook.active = 1 workbook.save(response) return response
def get(collection_exercise_id, survey_id): output = io.BytesIO() wb = Workbook() ws = wb.active ws.title = "Response Chasing Report" # Set headers headers = { "A1": "Survey Status", "B1": "Reporting Unit Ref", "C1": "Reporting Unit Name", "D1": "Enrolment Status", "E1": "Respondent Name", "F1": "Respondent Telephone", "G1": "Respondent Email", "H1": "Respondent Account Status", } for cell, header in headers.items(): ws[cell] = header ws.column_dimensions[cell[0]].width = len(header) engine = app.db.engine collex_status = ( "WITH " "business_details AS " "(SELECT DISTINCT " "ba.collection_exercise As collection_exercise_uuid, " "b.business_ref AS sample_unit_ref, " "ba.business_id AS business_party_uuid, " "ba.attributes->> 'name' AS business_name " "FROM " "partysvc.business_attributes ba, partysvc.business b " "WHERE " f"ba.collection_exercise = '{collection_exercise_id}' and " "ba.business_id = b.party_uuid), " "case_details AS " "(SELECT " "cg.collection_exercise_id AS collection_exercise_uuid, cg.sample_unit_ref, " "cg.status AS case_status " "FROM casesvc.casegroup cg " f"WHERE cg.collection_exercise_id = '{collection_exercise_id}' " "ORDER BY cg.status, cg.sample_unit_ref), " "respondent_details AS " "(SELECT e.survey_id AS survey_uuid, e.business_id AS business_party_uuid, " "e.status AS enrolment_status, " "CONCAT(r.first_name, ' ', r.last_name) AS respondent_name, r.telephone, " "r.email_address, r.status AS respondent_status " "FROM partysvc.enrolment e " "LEFT JOIN partysvc.respondent r ON e.respondent_id = r.id " "WHERE " f"e.survey_id = '{survey_id}') " "SELECT cd.case_status, bd.sample_unit_ref, bd.business_name, " "rd.enrolment_status, rd.respondent_name, " "rd.telephone, rd.email_address, rd.respondent_status " "FROM " "case_details cd " "LEFT JOIN business_details bd ON bd.sample_unit_ref=cd.sample_unit_ref " "LEFT JOIN respondent_details rd ON bd.business_party_uuid = rd.business_party_uuid " "ORDER BY sample_unit_ref, case_status;") try: collex_details = engine.execute(text(collex_status)) except SQLAlchemyError: logger.exception("SQL Alchemy query failed") raise for row in collex_details: business = [ row[0], row[1], row[2], row[3], row[4], row[5], row[6], row[7] ] ws.append(business) wb.active = 1 wb.save(output) wb.close() response = make_response(output.getvalue(), 200) response.headers[ "Content-Disposition"] = f"attachment; filename=response_chasing_{collection_exercise_id}.xlsx" response.headers[ "Content-type"] = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet" return response
def test_set_hidden_sheet_as_active(Workbook): wb = Workbook() ws = wb.create_sheet() ws.sheet_state = 'hidden' with pytest.raises(ValueError): wb.active = ws
def test_set_invalid_child_as_active(Workbook): wb1 = Workbook() wb2 = Workbook() ws2 = wb2['Sheet'] with pytest.raises(ValueError): wb1.active = ws2
def test_set_invalid_sheet_by_name(Workbook): wb = Workbook() with pytest.raises(TypeError): wb.active = "Sheet"
def test_set_invalid_active_index(Workbook): wb = Workbook() with pytest.raises(ValueError): wb.active = 1
def write_bookings_to_excel_file(self, booking_path, shift): filepath = os.path.join(booking_path, 'bookings.xlsx') print('Writing to excel ...') # if os.path.exists(filepath) and self.fork != 1: # remove(filepath) # self.fork = 1 if not os.path.exists(filepath): workbook = Workbook() workbook.save(filepath) workbook.create_sheet("Spapshoot", 0) workbook.create_sheet("Display", 1) else: workbook = load_workbook(filepath) # fixme: delete the default sheet: if "Sheet" in workbook.sheetnames: std = workbook["Sheet"] workbook.remove(std) sheet = workbook.active self.set_stylesheet(sheet, self.shift) if shift != 1: # write snap sheet c = '1.374' i = 2 for row in self.data: cell_reference = sheet.cell(row=i, column=1) cell_reference.value = row[2] sheet['B{}'.format(i)] = '=PRODUCT(A{},{}'.format(i, c) sheet['C{}'.format(i)] = '=SUM(B{},-A{}'.format(i, i) cell_reference = sheet.cell(row=i, column=4) cell_reference.value = row[0] cell_reference = sheet.cell(row=i, column=5) cell_reference.value = row[1] cell_reference = sheet.cell(row=i, column=6) cell_reference.value = row[3] i += 1 else: # write turbo sheet c = '1.374' i = 3 for row in self.data: cell_reference = sheet.cell(row=i, column=1) update_code = t.code_builder(self.read_code()) self.write_code(update_code) cell_reference.value = update_code cell_reference = sheet.cell(row=i, column=2) cell_reference.value = row[2] # REF: # https://stackoverflow.com/questions/51044736/openpyxl-iterate-through-rows-and-apply-formula # fixme CODE: # for row_num in range(2, max_row_num): # sheet['E{}'.format(row_num)] = '=CLEAN(D{})'.format(row_num) sheet['C{}'.format(i)] = '=PRODUCT(B{},{}'.format(i, c) sheet['D{}'.format(i)] = '=SUM(C{},-B{}'.format(i, i) cell_reference = sheet.cell(row=i, column=5) cell_reference.value = self.cell_cc cell_reference = sheet.cell(row=i, column=6) cell_reference.value = self.cell_city cell_reference = sheet.cell(row=i, column=7) cell_reference.value = row[0] cell_reference = sheet.cell(row=i, column=8) cell_reference.value = row[1] cell_reference = sheet.cell(row=i, column=9) cell_reference.value = row[3] i += 1 workbook.active = 1 display_sheet = workbook.active # select target row # target = 1 # while sheet.cell(row=target, column=1).value is not None: # target += 1 c = '1.374' target = 3 while display_sheet.cell(row=target, column=6).value is not None: target += 1 booking = self.data[0] cell_reference = display_sheet.cell(row=target, column=1) update_code = t.code_builder(self.read_code()) self.write_code(update_code) cell_reference.value = update_code cell_reference = display_sheet.cell(row=target, column=2) cell_reference.value = booking[2] display_sheet['C{}'.format(target)] = '=PRODUCT(B{},{}'.format( target, c) display_sheet['D{}'.format(target)] = '=SUM(C{},-B{}'.format( target, target) cell_reference = display_sheet.cell(row=target, column=5) cell_reference.value = self.cell_cc cell_reference = display_sheet.cell(row=target, column=6) cell_reference.value = self.cell_city cell_reference = display_sheet.cell(row=target, column=7) cell_reference.value = booking[0] cell_reference = display_sheet.cell(row=target, column=8) cell_reference.value = booking[1] cell_reference = display_sheet.cell(row=target, column=9) cell_reference.value = booking[3] # switch sheet workbook.active = 0 sheet = workbook.active self.set_stylesheet(sheet, 1) self.set_stylesheet(display_sheet, 1) workbook.save(filepath) # save file if switch != 1: spreadsheet = '//home/jmartorell/Booking/bookings/bookings.xlsx' self.send_attachment(spreadsheet)
stringPattern = r'"(.*?)"' if __name__ == '__main__': # get reading file paths os.chdir( '/Users/bernardbussy/go/src/github.com/iot-my-world/brain/testing/tracker/reading/data' ) readingFilePaths = [ './raw/' + readingFile for readingFile in os.listdir('./raw') if readingFile.endswith('.rdat') ] # open and set up workbook to save readings in outputWorkbook = Workbook() journeyName = readingFilePaths[0].split('.')[0] outputWorkbook.worksheets[0].title = get_journey_name(readingFilePaths[0]) outputWorkbook.active = 0 # for every readings data file for filePathIdx, filePath in enumerate(readingFilePaths): # add new sheet for each journey if filePathIdx > 0: journeyName = get_journey_name(filePath) outputWorkbook.create_sheet(journeyName) outputWorkbook.active = filePathIdx startDateTime = random_start_date() journeyTime = 0 readings = [] # open the file and get the readings and journey time with open(filePath) as readingFile: for line in readingFile:
particion['uso_particion'] ]) # Identificar última celda ocupada y aplicar formato a toda la tabla ultima_celda = informe_particiones.cell( row=informe_particiones.max_row, column=informe_particiones.max_column).coordinate tabla_particiones = Table(displayName='TablaParticiones', ref='A1:{}'.format(ultima_celda)) estilo = TableStyleInfo(name='TableStyleMedium6', showRowStripes=True) tabla_particiones.tableStyleInfo = estilo informe_particiones.add_table(tabla_particiones) # Crear nueva pestaña y activarla informe_procesos = archivo_excel.create_sheet('Procesos') archivo_excel.active = informe_procesos # Títulos de columnas informe_procesos.append( ['PID', 'PPID', 'Proceso', '% Uso de memoria', 'Estado', 'Usuario']) # Agregar datos de cada proceso for proceso in info_procesos: informe_procesos.append([ proceso['pid'], proceso['ppid'], proceso['name'], proceso['memory_percent'], proceso['status'], proceso['username'] ]) # Identificar última celda ocupada y aplicar formato a toda la tabla ultima_celda = informe_procesos.cell( row=informe_procesos.max_row,
DATE = 2 RANK = 3 TEAM = 4 TOTAL_POINTS = 5 PREVIOUS_POINTS = 6 RANK_DIFF = 7 # table & sheet creation workbook = Workbook() worksheet = workbook.active worksheet.title = "cup_info" workbook.create_sheet("match_info") workbook.create_sheet("goal_info") workbook.create_sheet("rank_info") workbook.active = 1 worksheet.cell(cup_info_row, column=CUP.CUP_ID).value = "cup_id" worksheet.cell(cup_info_row, column=CUP.HOST).value = "host" worksheet.cell(cup_info_row, column=CUP.YEAR).value = "year" worksheet.cell(cup_info_row, column=CUP.FINAL_PHASE).value = "final_phase" worksheet.cell(cup_info_row, column=CUP.WINNER).value = "winner" worksheet = workbook["match_info"] worksheet.cell(match_info_row, column=MATCH.GAME_ID).value = "match_id" worksheet.cell(match_info_row, column=MATCH.CUP_ID).value = "cup_id" worksheet.cell(match_info_row, column=MATCH.VERSUS).value = "versus" worksheet.cell(match_info_row, column=MATCH.PHASE).value = "phase" worksheet.cell(match_info_row, column=MATCH.REFEREE).value = "referee" worksheet.cell(match_info_row, column=MATCH.REFEREE_NAC).value = "referee_nac" worksheet.cell(match_info_row, column=MATCH.STADIUM).value = "stadium"
from openpyxl import Workbook # 데이터 변수 선언 ------------------------------------------- filename = '../DATA/sample02.xlsx' names=['Name','Tom', 'Jane', 'Park', 'Kim'] ages=['Age', 14, 21, 19, 20] # 엑셀 파일 생성 -------------------------------------------- wb = Workbook() # 엑셀 파일 생성, Sheet1 자동 생성 ws = wb.active # 시트 활성화 ws.title = 'new sheet' # 시트명 변경 ws['A1'] = 'Language' # 시트 데이터 삽입 ws['B1'] = 'Create' #wb.remove_sheet(ws) # 1번째 시트 삭제 ws2=wb.create_sheet('data_sheet') # 2번째 시트 생성 """ ws2.cell(1,1).value='Name' ws2.cell(1,2).value='Age' ws2.cell(2,1).value='Tom' ws2.cell(2,2).value='010-111-2222' """ for r in range(len(names)): print(r) ws2.cell(r+1, 1).value = names[r] for r in range(len(ages)): ws2.cell(r+1,2).value = ages[r] wb.active=ws2 # 활성화 시트 설정 wb.save(filename = filename) # 엑셀 파일 생성
def cexcel_from_html_above_v1(self, cliente, html_codigo): # # DEPOIS JUNTAR ELA COM GINFESS_SCRAP import os import pyautogui as pygui from pyperclip import paste, copy from time import sleep from .retornot import RetidosNorRetidos, RnrSo1 from .ginfess_scrap import cria_site_v1 """ :param cliente: nome do cliente vindo do loop :param competencia: vindo do GINFESS_download na linha 37 :param site_cria: (lugar_salvar) :return: return_full_path for with_titlePATH.txt """ client_path = self.client_path # impossível ser None driver = self.driver qtd_nf = driver.find_element_by_class_name('x-paging-info') qtd_text = qtd_nf.text proc = qtd_text.index('of') pal = qtd_text[proc:].split() qtd_text = pal[1] prossigo = cria_site_v1(html_codigo, qtd_text) _prossigo = prossigo[0] len_tables = prossigo[1] # input(f'{prossigo}, {len_tables}, {_prossigo}') sleep(5) if _prossigo: arq = f'rnc-{cliente}.xlsx' if len(arq) > 32: arq = f'rnc-{cliente.split()[0]}.xlsx' x, y = pygui.position() arq = f'{client_path}/{arq}' if '/' in client_path else f'{client_path}\\{arq}' # not really necessary, but i want to try: wb = Workbook() sh_name = client_path.split('/')[-1] if '\\' not in client_path else client_path.split('\\')[-1] sh_name = sh_name[:10] # limitando wb.create_sheet(sh_name) wb.active = 1 wb.remove(wb['Sheet']) wb.save(arq) except FileExistsError: pass finally: # ########## ABRINDO EXCEL ####### # program = arq.split('_')[-1] """~~~~""" os.startfile(arq) """~~~~""" sleep(12) allin = pygui.getAllWindows() for e, l in enumerate(allin): if program in l.title.lower(): l.restore() l.activate() l.maximize() # ########## ABRINDO ######### sleep(6) if len_tables > 1: RetidosNorRetidos() # input('RETIDOS N RETIDOS') pygui.hotkey('alt', 'f4') sleep(5) pygui.hotkey('enter') # from RETIDOS_N_RETIDOS import save_after_changes else: RnrSo1() print(f'Testado, len tables = {len_tables}')