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