def set_border(sheet, x, y, w, h, border):
rows = sheet["{}{}".format(get_column_letter(x), y):"{}{}".
format(get_column_letter(x + w - 1), y + h - 1)]
for row in rows:
row[0].border = Border(
left=border,
top=row[0].border.top,
bottom=row[0].border.bottom,
right=row[0].border.right,
)
row[-1].border = Border(
left=row[-1].border.left,
top=row[-1].border.top,
bottom=row[-1].border.bottom,
right=border,
)
for c in rows[0]:
c.border = Border(left=c.border.left,
top=border,
bottom=c.border.bottom,
right=c.border.right)
for c in rows[-1]:
c.border = Border(left=c.border.left,
top=c.border.top,
bottom=border,
right=c.border.right)
class Styles:
"""
Styles for file
"""
header_style = PatternFill(fgColor="b4bbc1", fill_type="solid")
names_style = PatternFill(fgColor="c9ccce", fill_type="solid")
footer_style = PatternFill(fgColor="55bbff", fill_type="solid")
l4_style = PatternFill(fgColor="80D332", fill_type="solid")
u_style = PatternFill(fgColor="FC33FF", fill_type="solid")
thin_border_style = Border(left=Side(style="thin"),
right=Side(style="thin"),
top=Side(style="thin"),
bottom=Side(style="thin"))
right_med_border_style = Border(left=Side(style="thin"),
right=Side(style="medium"),
top=Side(style="thin"),
bottom=Side(style="thin"))
right_bot_med_border_style = Border(left=Side(style="thin"),
right=Side(style="medium"),
top=Side(style="thin"),
bottom=Side(style="medium"))
bottom_med_border_style = Border(left=Side(style="thin"),
right=Side(style="thin"),
top=Side(style="thin"),
bottom=Side(style="medium"))
alignment_style = Alignment(horizontal="center",
vertical="center",
wrapText=True)
def test_named_styles(self, datadir, Stylesheet):
from openpyxl.styles.named_styles import NamedStyle
from openpyxl.styles.fonts import DEFAULT_FONT
from openpyxl.styles.fills import DEFAULT_EMPTY_FILL
from openpyxl.styles.borders import Border
datadir.chdir()
with open("complex-styles.xml") as src:
xml = src.read()
node = fromstring(xml)
stylesheet = Stylesheet.from_tree(node)
followed = stylesheet.named_styles['Followed Hyperlink']
assert followed.xfId == 2
assert followed.name == "Followed Hyperlink"
assert followed.font == stylesheet.fonts[2]
assert followed.fill == DEFAULT_EMPTY_FILL
assert followed.border == Border()
link = stylesheet.named_styles['Hyperlink']
assert link.name == "Hyperlink"
assert link.font == stylesheet.fonts[1]
assert link.fill == DEFAULT_EMPTY_FILL
assert link.border == Border()
normal = stylesheet.named_styles['Normal']
assert normal.name == "Normal"
assert normal.font == stylesheet.fonts[0]
assert normal.fill == DEFAULT_EMPTY_FILL
assert normal.border == Border()
def underlineRow(currentLine, start=1, end=14, grandTotal=False):
for i in range(start, end):
if grandTotal:
ws.cell(row=currentLine, column=i +
1).border = Border(top=Side(style='double'))
else:
ws.cell(row=currentLine, column=i +
1).border = Border(bottom=Side(style='thin'))
def __init__(self, models, report_location, number_of_rounds):
# create final workbook
self.models = models
self.report_location = report_location
self.rounds = int(number_of_rounds)
self.workbook = Workbook()
# font details
self.titles_style = Font(name='Calibri (Body)', size=11, bold=True)
self.general_style = Font(name='Calibri (Body)', size=11)
self.net_score_style = Font(name='Calibri (Body)',
size=11,
italic=True)
self.key_title_style = Font(name="Calibri (Body)", size=8, bold=True)
self.key_style = Font(name="Calibri (Body)", size=8)
self.net_style = Font(name='Calibri (Body)', size=8, italic=True)
# border details
self.all_border = Border(left=Side(style='thick'),
right=Side(style='thick'),
top=Side(style='thick'),
bottom=Side(style='thick'))
self.top_border = Border(left=Side(style='thick'),
right=Side(style='thick'),
top=Side(style='thick'))
self.middle_border = Border(left=Side(style='thick'),
right=Side(style='thick'))
self.bottom_border = Border(left=Side(style='thick'),
right=Side(style='thick'),
bottom=Side(style='thick'))
# fill/highlight details
self.grey = PatternFill("solid", fgColor="B7B7B7")
self.key_variables = PatternFill("solid", fgColor="FFC5C5")
self.key_models = PatternFill("solid", fgColor="D4D4D4")
# calculate the excel alphabet from D to ZZ
alphabet = []
for letter in range(68, 91):
alphabet.append(chr(letter))
self.extend_alphabet = []
self.extend_alphabet.extend(alphabet)
complete_alphabet = []
for letter in range(65, 91):
complete_alphabet.append(chr(letter))
index = 0
while index < len(complete_alphabet):
for letter in complete_alphabet:
double_letters = "%s%s" % (complete_alphabet[index], letter)
self.extend_alphabet.append(double_letters)
index += 1
# write report
self.build_sheets()
def range_border_internal(cell_range, external, internal):
"""
I completely have no idea why I have to use += instead of = for border assignment.
If you change += to =, it will no longer work! Weird!
:param cell_range: A range or cells
:param external: The side to be used as the external border
:param internal: The side to be used as the internal border
:return: None
"""
rows = list(cell_range)
num_rows = len(rows)
num_columns = len(rows[0])
# Set the for corners
rows[0][0].border += Border(top=external,
bottom=internal,
left=external,
right=internal)
rows[0][-1].border += Border(top=external,
bottom=internal,
left=internal,
right=external)
rows[-1][0].border += Border(top=internal,
bottom=external,
left=external,
right=internal)
rows[-1][-1].border += Border(top=internal,
bottom=external,
left=internal,
right=external)
# Set the four sides
for i in range(1, num_rows):
rows[i][0].border += Border(top=internal,
bottom=internal,
left=external,
right=internal)
rows[i][-1].border += Border(top=internal,
bottom=internal,
left=internal,
right=external)
for i in range(1, num_columns):
rows[0][i].border += Border(top=external,
bottom=internal,
left=internal,
right=internal)
rows[-1][i].border += Border(top=internal,
bottom=external,
left=internal,
right=internal)
# Set the internal cells
for i in range(1, num_rows):
for j in range(1, num_columns):
internal_border = Border(top=internal,
bottom=internal,
left=internal,
right=internal)
rows[i][j].border += internal_border
def uglyBorderSet(write_ws):
from openpyxl.styles.borders import Border, Side
blue_dot_border = Border(right=Side(style='thin', color= 'FF0059FF'), bottom=Side(style='hair', color = 'FF0059FF'))
blue_thin_border = Border(right=Side(style='thin', color= 'FF0059FF'))
red_dot_bt_border = Border(right=Side(style='thin', color= 'FFFF0000'), bottom=Side(style='hair', color = 'FFFF0000'))
red_dot_border = Border(right=Side(style='hair', color= 'FFFF0000'), bottom=Side(style='hair', color = 'FFFF0000'))
red_thin_border = Border(right=Side(style='thin', color= 'FFFF0000'))
red_hair_border = Border(right=Side(style='hair', color= 'FFFF0000'))
red_thin_bt_border = Border(right=Side(style='thin', color= 'FFFF0000'), bottom=Side(style='thin', color = 'FFFF0000'))
red_top_thin_right_hair = Border(right=Side(style='hair', color= 'FFFF0000'), top=Side(style='thin', color = 'FFFF0000'))
red_right_hair = Border(right=Side(style='hair', color= 'FFFF0000'))
write_ws['AB4'].border = blue_thin_border
write_ws['AB7'].border = blue_dot_border
write_ws['AB8'].border = blue_thin_border
write_ws['AB15'].border = blue_dot_border
write_ws['AB16'].border = blue_dot_border
write_ws['AB17'].border = blue_dot_border
write_ws['AB18'].border = blue_dot_border
write_ws['AB19'].border = blue_thin_border
write_ws['M25'].border = red_top_thin_right_hair
write_ws['S25'].border = red_top_thin_right_hair
write_ws['AA25'].border = red_top_thin_right_hair
write_ws['N26'].border = red_right_hair
write_ws['AB26'].border = red_thin_border
write_ws['AB29'].border = red_dot_bt_border
write_ws['AB30'].border = red_thin_bt_border
write_ws['AB31'].border = red_dot_bt_border
write_ws['AB32'].border = red_dot_bt_border
write_ws['AB33'].border = red_dot_bt_border
write_ws['AB34'].border = red_dot_bt_border
write_ws['AB35'].border = red_dot_bt_border
write_ws['AB36'].border = red_thin_bt_border
write_ws['AB37'].border = red_dot_bt_border
write_ws['AB38'].border = red_dot_bt_border
write_ws['AB39'].border = red_dot_bt_border
write_ws['AB40'].border = red_dot_bt_border
write_ws['AB41'].border = red_thin_bt_border
write_ws['G33'].border = red_dot_border
write_ws['G34'].border = red_dot_border
write_ws['G35'].border = red_dot_border
write_ws['G36'].border = red_hair_border
write_ws['G38'].border = red_dot_border
write_ws['G39'].border = red_dot_border
write_ws['G40'].border = red_dot_border
write_ws['G41'].border = red_hair_border
return write_ws
def setBorder(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)
self.sheet[range].border = border
def outside_borders(cell):
col_, row_ = get_cell_coordinates(cell.coordinate, numeric=True)
# Set Border for Top Left Corner
if (row_, col_) == (start_row, start_column):
cell.border = Border(left=Side(style=style),
top=Side(style=style))
# Set Border for Top Right Corner
elif (row_, col_) == (start_row, end_column):
cell.border = Border(right=Side(style=style),
top=Side(style=style))
# Set Border for Bottom Left Corner
elif (row_, col_) == (end_row, start_column):
cell.border = Border(left=Side(style=style),
bottom=Side(style=style))
# Set Border for Bottom Right Corner
elif (row_, col_) == (end_row, end_column):
cell.border = Border(right=Side(style=style),
bottom=Side(style=style))
# Set Border for Top Row
elif row_ == start_row:
cell.border = Border(top=Side(style=style))
# Set Border for Left Column
elif col_ == start_column:
cell.border = Border(left=Side(style=style))
# Set Border for Bottom Row
elif row_ == end_row:
cell.border = Border(bottom=Side(style=style))
# Set Border for Right Column
elif col_ == end_column:
cell.border = Border(right=Side(style=style))
# Skip setting Borders for the Cells in the Middle
else:
pass
def export_vers_dhc(self, l_vac):
#exporte la liste l_vac vers le fichier dhc du controleur trig et sauvegarde
thin_border = Border(left=Side(style='thin'),
right=Side(style='thin'),
top=Side(style='thin'),
bottom=Side(style='thin'))
medium_border = Border(left=Side(style='medium'),
right=Side(style='medium'),
top=Side(style='medium'),
bottom=Side(style='medium'))
l_row_mois = [
0, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5
] #crée une liste d'indice de lignes pour remplir chaque feuille mensuelle
l_col = ["", "A", "B", "C", "D", "E", "F", "G", "H", "I"]
for l in l_vac:
col = 1
mois = l[0].month
ws = self.wb[str(
mois
)] #sélectionne la feuille dont le nom est le mois considéré
for val in l:
v = ws.cell(
row=l_row_mois[mois], column=col,
value=val) #remplit les cellules avec les valeurs des vacs
v.border = thin_border
if col != 1 and col != 9: #sauf en colonne A et I
formule = "=SUM(" + l_col[col] + "5:" + l_col[col] + str(
l_row_mois[mois]) + ")"
v = ws.cell(
row=l_row_mois[mois] + 1, column=col, value=formule
) #en dessous de la ligne ajoute la formule pour calculer les totaux
v.border = medium_border
elif col == 1: #en colonne A
ws.cell(
row=l_row_mois[mois], column=col
).number_format = "dd/mm/yy" #affecte le format jour/mois/année à la cellule en colonne A
formule = "TOTAL"
v = ws.cell(row=l_row_mois[mois] + 1,
column=col,
value=formule)
v.border = medium_border
col += 1
l_row_mois[mois] += 1
ws = self.wb["Data"]
ws['C1'] = self.datefin #change la date de mise à jour
self.wb.save(self.nomfic) #sauvegarde
def or_border(ws1, ws2, row, col):
"""Sometimes the border's don't quite match as, for example, the left border is optional
if the right border of the prior cell is set - so account for that by copying the appropriate borders
if they are set."""
ws1_cell = ws1.cell(row, col)
ws2_cell = ws2.cell(row, col)
if row != ws1.min_row:
if not ws1_cell.border.top.style and ws2_cell.border.top.style and \
ws1.cell(row-1, col).border.bottom.style:
ws1_cell.border = Border(top=ws1.cell(row - 1, col).border.bottom,
left=ws1_cell.border.left,
right=ws1_cell.border.right,
bottom=ws1_cell.border.bottom)
elif not ws2_cell.border.top.style and ws1_cell.border.top.style and \
ws2.cell(row-1, col).border.bottom.style:
ws2_cell.border = Border(top=ws2.cell(row - 1, col).border.bottom,
left=ws2_cell.border.left,
right=ws2_cell.border.right,
bottom=ws2_cell.border.bottom)
if row != ws1.max_row:
if not ws1_cell.border.bottom.style and ws2_cell.border.bottom.style and \
ws1.cell(row+1, col).border.top.style:
ws1_cell.border = Border(bottom=ws1.cell(row + 1, col).border.top,
left=ws1_cell.border.left,
right=ws1_cell.border.right,
top=ws1_cell.border.top)
elif not ws2_cell.border.bottom.style and ws1_cell.border.bottom.style and \
ws2.cell(row+1, col).border.top.style:
ws2_cell.border = Border(bottom=ws2.cell(row + 1, col).border.top,
left=ws2_cell.border.left,
right=ws2_cell.border.right,
top=ws2_cell.border.top)
if col != ws1.min_column:
if not ws1_cell.border.left.style and ws2_cell.border.left.style and \
ws1.cell(row, col-1).border.right.style:
ws1_cell.border = Border(left=ws1.cell(row, col - 1).border.right,
right=ws1_cell.border.right,
bottom=ws1_cell.border.bottom,
top=ws1_cell.border.top)
elif not ws2_cell.border.left.style and ws1_cell.border.left.style and \
ws2.cell(row, col-1).border.right.style:
ws2_cell.border = Border(left=ws2.cell(row, col - 1).border.right,
right=ws2_cell.border.right,
bottom=ws2_cell.border.bottom,
top=ws2_cell.border.top)
if col != ws1.max_column:
if not ws1_cell.border.right.style and ws2_cell.border.right.style and \
ws1.cell(row, col+1).border.left.style:
ws1_cell.border = Border(right=ws1.cell(row, col + 1).border.left,
left=ws1_cell.border.left,
bottom=ws1_cell.border.bottom,
top=ws1_cell.border.top)
elif not ws2_cell.border.right.style and ws1_cell.border.right.style and \
ws2.cell(row, col+1).border.left.style:
ws2_cell.border = Border(right=ws2.cell(row, col + 1).border.left,
left=ws2_cell.border.left,
bottom=ws2_cell.border.bottom,
top=ws2_cell.border.top)
def write_xlsx(data, file_name, params):
"""Method to write xls given data."""
try:
report_type = params['report_region']
xltm_tmp = params['xltm'] if 'xltm' in params else 'case_load'
# Define some style for borders
row_start = 9 if xltm_tmp == 'case_load' else 2
border = Border(left=Side(style='thin'),
right=Side(style='thin'),
top=Side(style='thin'),
bottom=Side(style='thin'))
sheet_name = params['sheet'] if 'sheet' in params else 'Sheet'
xls_tmp = '_orgs.xltm' if report_type == 4 else '.xltm'
wb = load_workbook('%s/%s%s' % (DOC_ROOT, xltm_tmp, xls_tmp),
data_only=True)
ws = wb.active if row_start == 9 else wb.get_sheet_by_name(sheet_name)
# Lets write some data to the file
for i, value in enumerate(data):
for c, stats in enumerate(value):
ws.cell(row=i + row_start, column=c + 1).value = stats
if row_start == 9:
ws.cell(row=i + row_start, column=c + 1).border = border
# Fill my placeholders with actual parameters
if row_start == 9:
for idx, row in enumerate(ws['A2:P5']):
for cell in row:
if cell.value and "{" in cell.value:
cell.value = cell.value.format(**params)
file_ext = '.xlsm' if row_start == 2 else '.xlsx'
ws.title = sheet_name
xls_name = '%s/%s%s' % (MEDIA_ROOT, file_name, file_ext)
wb.save(xls_name)
except Exception, e:
print "error writing excel - %s" % (str(e))
raise e
def appendSSEStocks(self):
'''
向filePath指定表格中追加上交所上市公司信息
'''
wb = load_workbook(self.filePath)
sheet = wb.active
# print sheet.max_row
# 设置单元格所有框线
thin_border = Border(left=Side(style='thin'),
right=Side(style='thin'),
top=Side(style='thin'),
bottom=Side(style='thin'))
# # 写标题
# for i in range(self.__indexName.__len__()):
# _ = sheet.cell(column=i + 1, row=1, value=self.__indexName[i])
# 当前表格的信息行数
row = sheet.max_row
for i in self.getSSEAShareListCompanyCode():
a = AchieveSSEStockInfo(i)
if not a.getStatus():
continue
for j in range(len(a.methodList)):
m = a.methodList[j]
f = getattr(a, m)
_ = sheet.cell(column=j + 1, row=row, value="%s" % f())
sheet.cell(column=j + 1, row=row).border = thin_border
row = row + 1
if int(i) % 100 == 0:
wb.save(self.filePath)
wb.save(self.filePath)
def enterSheet(workOrder, wsDicty, workOrders):
ws = wsDicty[workOrder]
sX = 1
sY = 1
thin_border = Border(left=Side(style='thin'),
right=Side(style='thin'),
top=Side(style='thin'),
bottom=Side(style='thin'))
for ticket in workOrders[workOrder]:
sX = 1
ws.cell(row=sY, column=sX, value=ticket)
sY += 1
sX += 1
for response in workOrders[workOrder][ticket]:
for cell in response:
ws.cell(row=sY, column=sX, value=cell)
ws[returnCoordString(sX, sY)].alignment = Alignment(
wrap_text=True, horizontal='left', vertical='top')
ws[returnCoordString(sX, sY)].border = thin_border
sX += 1
sY += 1
sX = 2
def __write(self,
ws,
key: str,
value,
fill: PatternFill = PatternFill(),
border=Border(top=Side(style='thin', color='000000'),
bottom=Side(style='thin', color='000000'),
left=Side(style='thin', color='000000'),
right=Side(style='thin', color='000000')),
font_size: int = 9):
"""ワークシートの指定したキーにデータを書き込む
Arguments:
ws {} -- 対象のワークシート
key {str} -- キー
value {} -- 書き込む内容
Keyword Arguments:
fill {PatternFill} -- 色の指定 (default: {PatternFill()})
border {Border} -- 罫線情報(default: {Border(top=Side(style='thin', color='000000'), bottom=Side(style='thin', color='000000'), left=Side(style='thin', color='000000'), right=Side(style='thin', color='000000'))})
font_size {int} -- フォントサイズ (default: {9})
"""
ws[key] = value
ws[key].fill = fill
ws[key].border = border
ws[key].font = Font(size=font_size)
def writeExcelEntry(self, entry, row, col, lengthSection):
enterCell = self.ws.cell(row=row, column=col)
enterCell.value = entry
colLet = get_column_letter(col) # get column letter form number
beginCell = colLet + str(row)
testLen = col + lengthSection - 1
lengthofTest = col + lengthSection
testLet = get_column_letter(testLen) # get column letter form number
endCell = testLet + str(row)
self.ws.merge_cells(beginCell + ':' + endCell)
self.ws.cell(row, col).alignment = Alignment(horizontal='center',
vertical='center',
wrap_text=True)
if entry.find(':') != -1:
while col < lengthofTest:
bordercell = col
bordercell = get_column_letter(bordercell)
cellborder = self.ws[bordercell + str(row)]
cellborder.border = Border(top=self.thick_border,
left=self.thick_border,
right=self.thick_border,
bottom=self.thick_border)
col += 1
def __init__(self):
self.thin_border = Border(
left=Side(style='thin'),
right=Side(style='thin'),
top=Side(style='thin'),
bottom=Side(style='thin'))
def set_formatting(self, is_qualtrics):
# fill
if is_qualtrics:
self.__header_fill = PatternFill("solid", fgColor="1E262E")
else:
self.__header_fill = PatternFill("solid", fgColor="0F243E")
self.__table_fill = PatternFill("solid", fgColor="E7E6E6")
# font styles
self.__font_reg = Font(name='Arial', size=8)
self.__font_bold = Font(name='Arial', size=8, bold=True)
self.__font_back_hyperlink = Font(name='Arial', size=8, color="A2AAAD")
# alignments
self.__align_center = Alignment(horizontal="center",
vertical="center",
wrapText=True)
self.__align_left = Alignment(horizontal="left",
vertical="center",
wrapText=True)
# borders
self.__thin_bottom = Border(bottom=Side(style='thin'))
# logo adjustment
if is_qualtrics:
self.__row_height = 35
else:
self.__row_height = 52
def addSheet(wb, string_folder, filename, platform):
#reduce the sheetname length
new_sheetname = getSheetName(filename, platform)
string_path = getStringPath(string_folder, filename, platform)
#print 'create sheet for: ' + xml_path
ws = wb.create_sheet(new_sheetname)
thin_border = Border(left=Side(style='thin'),
right=Side(style='thin'),
top=Side(style='thin'),
bottom=Side(style='thin'))
if platform == "android":
fillSheetAndroid(string_path, ws)
else:
fillSheetIOS(string_path, ws)
# Create table title
ws['A1'] = "TEXT ID"
ws['B1'] = "Description"
ws['C1'] = "EN"
ws['D1'] = "VI"
ws['E1'] = "JA"
headerFill = PatternFill(start_color='FFFF5500',
end_color='FFFF5500',
fill_type='solid')
ws['A1'].fill = ws['B1'].fill = ws['C1'].fill = ws['D1'].fill = ws[
'E1'].fill = headerFill
ws['A1'].border = ws['B1'].border = ws['C1'].border = ws['D1'].border = ws[
'E1'].border = thin_border
ws.column_dimensions['A'].width = ws.column_dimensions[
'C'].width = ws.column_dimensions['D'].width = ws.column_dimensions[
'E'].width = 50
def format(self):
"""
Each cell of the merged cell is created as MergedCell if it does not
already exist.
The MergedCells at the edge of the merged cell gets its borders from
the upper left cell.
- The top MergedCells get the top border from the top left cell.
- The bottom MergedCells get the bottom border from the top left cell.
- The left MergedCells get the left border from the top left cell.
- The right MergedCells get the right border from the top left cell.
"""
names = ['top', 'left', 'right', 'bottom']
for name in names:
side = getattr(self.start_cell.border, name)
border = Border(**{name: side})
for coord in getattr(self, name):
cell = self.ws._cells.get(coord)
if cell is None:
row, col = coord
cell = MergedCell(self.ws, row=row, column=col)
self.ws._cells[(cell.row, cell.column)] = cell
cell.border += border
def add_data_to_single_cell(self,
sheet,
data,
row=1,
column=1,
font=None,
number_format='text',
border=True,
wrap_text=True,
align='left',
valign='top'):
thin_border = Border(left=Side(style='thin'),
right=Side(style='thin'),
top=Side(style='thin'),
bottom=Side(style='thin'))
cell = sheet.cell(row=row, column=column)
cell.value = data
if border:
cell.border = thin_border
cell.number_format = self.__cell_number_format[number_format]
if font:
cell.font = font
cell.alignment = Alignment(horizontal=align,
vertical=valign,
wrapText=wrap_text)
return sheet
def underline_border_cell(val, ws):
underline_border = Border(bottom=Side(style='thin'))
c = Cell(ws, value=val)
c.font = Font(size=11, bold=True)
c.border = underline_border
return c
def add_data_to_single_cell(self,
sheet,
data,
row=1,
column=1,
bold=False,
number_format=False,
border=True,
delimer=False,
wrap_text=True):
thin_border = Border(left=Side(style='thin'),
right=Side(style='thin'),
top=Side(style='thin'),
bottom=Side(style='thin'))
cell = sheet.cell(row=row, column=column)
cell.value = data
if border:
cell.border = thin_border
if number_format:
cell.number_format = '# ### ### ### ##0'
if delimer:
cell.number_format = '# ### ### ### ##0.##'
if bold:
cell.font = Font(bold=True)
cell.alignment = Alignment(horizontal='left',
vertical='top',
wrapText=wrap_text)
return sheet
def MakeFormat(self):
self.wb = openpyxl.Workbook()
self.ws = self.wb.active
side = Side(style='thin', color='000000')
border = Border(bottom=side)
width = [15, 7, 15, 15]
# 一行目の背景色と罫線の作成
for rows in self.ws['A1':'D1']:
i = 0
for cell in rows:
cell.fill = openpyxl.styles.PatternFill(patternType='solid',
fgColor='9FD9F6',
bgColor='9FD9F6')
cell.border = border
cell.value = self.header[i]
cell.alignment = Alignment(horizontal='center')
self.ws.column_dimensions[chr(i + 1 + 64)].width = width[i]
i = i + 1
# 二行目から五行目の背景色
for rows in self.ws['A2':'D5']:
for cell in rows:
cell.fill = openpyxl.styles.PatternFill(patternType='solid',
fgColor='D3EDFB',
bgColor='D3EDFB')
def __init__(self):
self.header_fill = openpyxl.styles.GradientFill(
stop=(self.header_background_color_start,
self.header_background_color_end))
self.header_font = openpyxl.styles.Font(name=self.font_name,
bold=True,
color=self.header_color)
self.org_fill = openpyxl.styles.GradientFill(
stop=(self.original_background_color_start,
self.original_background_color_end))
self.org_font = openpyxl.styles.Font(name=self.font_name,
color=self.original_color)
self.trans_fill = openpyxl.styles.GradientFill(
stop=(self.translation_background_color_start,
self.translation_background_color_end))
self.trans_font = openpyxl.styles.Font(name=self.font_name,
color=self.translation_color)
self.comment_fill = openpyxl.styles.GradientFill(
stop=(self.comment_background_color_start,
self.comment_background_color_end))
self.comment_font = openpyxl.styles.Font(name=self.font_name,
color=self.comment_color)
self.font = openpyxl.styles.Font(name=self.font_name)
self.border = Border(
left=Side(style='hair'),
right=Side(style='hair'),
top=Side(style='hair'),
bottom=Side(style='hair'),
)
def crear_reporte_pdf():
libro = openpyxl.Workbook(encoding="UTF-8", data_only=True)
sheet_libromayor = crear_hoja_libro(libro, 'Reporte Libro Mayor', 0, 0)
sheet_view = openpyxl.worksheet.SheetView()
sheet_view.zoomScale = "50"
sheet_view.zoomScaleNormal = "70"
sheet_libromayor.sheet_view = sheet_view
sheet_libromayor.zoomScale = "70"
ajustes_hoja(sheet_libromayor, 0, 'A', 20.00, 5.00)
alignment_title = Alignment(horizontal='center', vertical='center')
fuente_cabecera = Font(bold=True, size=15, name='calibri')
all_border = Border(left=Side(style='thin'),
right=Side(style='thin'),
top=Side(style='thin'),
bottom=Side(style='thin'))
dic = {
'fuente': fuente_cabecera,
'border': all_border,
'alineacion': alignment_title,
'sheet': sheet_libromayor,
'libro': libro
}
return dic
def save_xlsx(
filename,
df,
):
import openpyxl as xl
from openpyxl.utils.dataframe import dataframe_to_rows
from openpyxl.styles.borders import Border, Side
wb = xl.Workbook()
ws = wb.active
for r in dataframe_to_rows(df, index=True, header=True):
ws.append(r)
ws.delete_rows(2)
# set border (black thin line)
side = Side(style="thin", color="000000")
border = Border(top=side, bottom=side, left=side, right=side)
# write in sheet
for row in ws:
for cell in row:
ws[cell.coordinate].border = border
wb.save(filename)
def sheetwrite(numlist,datelist,fiolist,cellist,tinlist,toutlist,primlist):
wb = openpyxl.load_workbook(filename = filexls)
sheet = wb['Коленков'] #выбираем лист
sheet [('A'+str(len(numlist)+1))] = str(len(numlist))
sheet [('B'+str(len(numlist)+1))] = datelist
sheet [('C'+str(len(numlist)+1))] = fiolist
sheet [('D'+str(len(numlist)+1))] = cellist
sheet [('E'+str(len(numlist)+1))] = tinlist
sheet [('F'+str(len(numlist)+1))] = toutlist
sheet [('G'+str(len(numlist)+1))] = primlist
for s in ["A","C","E","F"]:
work_sheet = sheet [(s+str(len(numlist)+1))]
work_sheet.fill = PatternFill(fill_type='solid', start_color='b5e9ff', end_color='b5e9ff')#Данный код позволяет делать оформление цветом ячейки
for s in ["B","D","G"]:
work_sheet = sheet [(s+str(len(numlist)+1))]
work_sheet.fill = PatternFill(fill_type='solid', start_color='ffd88f', end_color='ffd88f')#Данный код позволяет делать оформление цветом ячейки
thin_border = Border(left=Side(style='thin'),
right=Side(style='thin'),
top=Side(style='thin'),
bottom=Side(style='thin'))
for s in ["A","B","C","D","E","F","G"]:
work_sheet = sheet [(s+str(len(numlist)+1))]
work_sheet.border = thin_border
try:
wb.save (filexls)
print ("Журнал посещений сохранен и заполнен.")
except:
wb.save (filexls+"_unsaved.xlsx")
text = "Не удалось сохранить журнал посещений, так как он уже используется. Копия сохранена в файл "+filexls+"_unsaved.xlsx"
ctypes.windll.user32.MessageBoxW(0, text, "Журнал учета посещений by Xottabb14", 0)
def insertCell(self):
logObj=LoggerObj('fileMerge')
log=logObj.createLog('fileMerge')
log.info('処理開始')
# basedata=pd.read_excel('基準データ.xlsx')
baseBook=pyxl.load_workbook(filename ='input/基準データ.xlsx')
baseSheet=baseBook['データ']
inputWorkbook=pd.ExcelFile('input/定義ファイル.xlsx')
addColumnSheet=inputWorkbook.parse('追加列')
side = Side(style='thin', color='000000')
blackBorder = Border(top=side,left=side,right=side,bottom=side)
# 列の追加を先に処理する
addColumnInfos=np.asarray(addColumnSheet)
for columninfo in addColumnInfos:
addColumnName=columninfo[1]
addColumnPosition=columninfo[2]
addColumnData=columninfo[3]
addColmunDatas=inputWorkbook.parse(addColumnData)
addDatas=np.asarray(addColmunDatas)
baseSheet.insert_cols(addColumnPosition,1)
baseSheet.cell(column=addColumnPosition,row=2,value=addColumnName).border=blackBorder
for i,data in enumerate (addDatas):
baseSheet.cell(column=addColumnPosition,row=i+3,value=data[1]).border=blackBorder
date=datetime.now().strftime("%Y%m%d%H%M%S")
outputDir='output/'+date
os.makedirs(outputDir,exist_ok=True)
baseBook.save(outputDir+'/addAfterSheet.xlsx')
return outputDir+'/addAfterSheet.xlsx'
def blackBorderLine(self):
border = Border(top=Side(style='thin', color='000000'),
bottom=Side(style='thin', color='000000'),
left=Side(style='thin', color='000000'),
right=Side(style='thin', color='000000'))
return border
