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}')
